Excel Export

The KendoReact TreeList provides options for exporting its data to Excel in hierarchical tree format.

The TreeList component is part of KendoReact, a professionally built commercial UI library. To try out this component you need to sign up for a 30-day trial, which gives you access to the full KendoReact library.

Getting Started

To enable the Excel export:

  1. Install kendo-react-excel-export package.

    npm install @progress/kendo-react-excel-export
  2. Import the ExcelExport component in your React Application.

    import { ExcelExport } from '@progress/kendo-react-excel-export';
  3. Wrap the TreeList inside an ExcelExport component.

  4. Set the hierarchy prop of the ExcelExport to true.

  5. Transform the data to a flat array with a level for each data record, using the treeToFlat function provided by the TreeList package.

  6. Pass the flat array and columns that have to be exported to the save function.

The following example demonstrates the basic implementation of the Excel export functionality of the TreeList.

import React from 'react';
import ReactDOM from 'react-dom';
import {
    TreeList, orderBy, filterBy, mapTree, extendDataItem,
    treeToFlat, TreeListToolbar
} from '@progress/kendo-react-treelist';
import { ExcelExport } from '@progress/kendo-react-excel-export';
import employees from './data';

const subItemsField = 'employees';
const expandField = 'expanded';
const columns = [
    { field: 'name', title: 'Name', width: 250, expandable: true },
    { field: 'hireDate', title: 'Hire Date', width: 200, format: '{0:d}' },
    { field: 'timeInPosition', title: 'Year(s) in Position', width: 200 },
    { field: 'fullTime', title: 'Full Time' }
];

class App extends React.Component {
    state = {
        data: [...employees],
        dataState: {
            sort: [
                { field: 'name', dir: 'asc' }
            ],
            filter: []
        },
        expanded: [1, 2, 32]
    }

    onExpandChange = (e) => {
        this.setState({
            expanded: e.value ?
                this.state.expanded.filter(id => id !== e.dataItem.id) :
                [...this.state.expanded, e.dataItem.id]
        });
    }

    handleDataStateChange = (event) => {
        this.setState({
            dataState: event.data
        })
    }

    addExpandField = (dataTree) => {
        const expanded = this.state.expanded;
        return mapTree(dataTree, subItemsField, (item) =>
            extendDataItem(item, subItemsField, {
                [expandField]: expanded.includes(item.id)
            })
        );
    }

    processData = () => {
        let { data, dataState } = this.state;
        let filteredData = filterBy(data, dataState.filter, subItemsField)
        let sortedData = orderBy(filteredData, dataState.sort, subItemsField)
        return this.addExpandField(sortedData);
    }

    exportToExcel = () =>{
        this._export.save(
            treeToFlat(this.processData(), expandField, subItemsField),
            columns
        );
    }

    render() {
        return (
            <ExcelExport
                ref={exporter => this._export = exporter}
                hierarchy={true}
            >
                <TreeList
                    style={{ maxHeight: '510px', overflow: 'auto' }}
                    expandField={expandField}
                    subItemsField={subItemsField}
                    onExpandChange={this.onExpandChange}
                    sortable={{ mode: 'multiple' }}
                    {...this.state.dataState}
                    data={this.processData()}
                    onDataStateChange={this.handleDataStateChange}
                    columns={columns}
                    toolbar={
                        <TreeListToolbar>
                            <button
                                title="Excel export"
                                className="k-button k-primary"
                                onClick={this.exportToExcel}
                            >
                                Export to Excel
                            </button>
                        </TreeListToolbar>
                    }
                />
            </ExcelExport>
        );
    }
}

ReactDOM.render(
    <App />,
    document.querySelector('my-app')
);

Configuration

You can entirely control the Excel export configuration through the arguments that are passed to the save function of the KendoReact Excel Export component.

The ExcelExport enables you to:

Exporting Specific Data

To export specific data, pass the data to the save function of the ExcelExport component. For example, if the TreeList has its paging enabled but you need to export only the current page, pass the paged data to the save function.

import React from 'react';
import ReactDOM from 'react-dom';
import {
    TreeList, mapTree, extendDataItem,
    treeToFlat, TreeListToolbar
} from '@progress/kendo-react-treelist';
import { ExcelExport } from '@progress/kendo-react-excel-export';
import employees from './data';

import { TreeListPager } from './pager.jsx';

const subItemsField = 'employees';
const expandField = 'expanded';
const columns = [
    { field: 'name', title: 'Name', width: 250, expandable: true },
    { field: 'hireDate', title: 'Hire Date', width: 200, format: '{0:d}' },
    { field: 'timeInPosition', title: 'Year(s) in Position', width: 200 },
    { field: 'fullTime', title: 'Full Time' }
];

class App extends React.Component {
    state = {
        data: [...employees],
        skip: 0,
        take: 8,
        expanded: [1, 2, 3, 32]
    }

    onExpandChange = (e) => {
        this.setState({
            expanded: e.value ?
                this.state.expanded.filter(id => id !== e.dataItem.id) :
                [...this.state.expanded, e.dataItem.id]
        });
    }

    addExpandField = (dataTree) => {
        const expanded = this.state.expanded;
        return mapTree(dataTree, subItemsField, (item) =>
            extendDataItem(item, subItemsField, {
                [expandField]: expanded.includes(item.id)
            })
        );
    }

    onPageChange = (event) => {
        const { skip, take } = event;
        this.setState({ skip, take });
    }

    exportToExcel = () => {
        const { skip, take } = this.state;
        let data = this.addExpandField(this.state.data)
        let flatData = treeToFlat(data, expandField, subItemsField);
        this._export.save(
            flatData.slice(skip,take),
            columns
        );
    }

    render() {
        return (
            <ExcelExport
                ref={exporter => this._export = exporter}
                hierarchy={true}
            >
                <TreeList
                    style={{ maxHeight: '610px', overflow: 'auto' }}
                    expandField={expandField}
                    subItemsField={subItemsField}
                    onExpandChange={this.onExpandChange}
                    data={this.addExpandField(this.state.data)}
                    columns={columns}
                    pager={TreeListPager}
                    skip={this.state.skip}
                    take={this.state.take}
                    onPageChange={this.onPageChange}
                    toolbar={
                        <TreeListToolbar>
                            <button
                                title="Excel export"
                                className="k-button k-primary"
                                onClick={this.exportToExcel}
                            >
                                Export to Excel
                            </button>
                        </TreeListToolbar>
                    }
                />
            </ExcelExport>
        );
    }
}

ReactDOM.render(
    <App />,
    document.querySelector('my-app')
);

Customizing Exported Workbook

When exporting the excel file we provide an option to customize the generated workbook. This can be used to modify the values, the appearance or the sheets in the document. The following example demonstrates how to add a different background color on the alt rows.

import React from 'react';
import ReactDOM from 'react-dom';
import {
    TreeList, orderBy, filterBy, mapTree, extendDataItem,
    treeToFlat, TreeListToolbar
} from '@progress/kendo-react-treelist';
import { ExcelExport } from '@progress/kendo-react-excel-export';
import employees from './data';

const subItemsField = 'employees';
const expandField = 'expanded';
const columns = [
    { field: 'name', title: 'Name', width: 250, expandable: true },
    { field: 'hireDate', title: 'Hire Date', width: 200, format: '{0:d}' },
    { field: 'timeInPosition', title: 'Year(s) in Position', width: 200 },
    { field: 'fullTime', title: 'Full Time' }
];

class App extends React.Component {
    state = {
        data: [...employees],
        dataState: {
            sort: [
                { field: 'name', dir: 'asc' }
            ],
            filter: []
        },
        expanded: [1, 2, 32]
    }

    onExpandChange = (e) => {
        this.setState({
            expanded: e.value ?
                this.state.expanded.filter(id => id !== e.dataItem.id) :
                [...this.state.expanded, e.dataItem.id]
        });
    }

    handleDataStateChange = (event) => {
        this.setState({
            dataState: event.data
        })
    }

    addExpandField = (dataTree) => {
        const expanded = this.state.expanded;
        return mapTree(dataTree, subItemsField, (item) =>
            extendDataItem(item, subItemsField, {
                [expandField]: expanded.includes(item.id)
            })
        );
    }

    processData = () => {
        let { data, dataState } = this.state;
        let filteredData = filterBy(data, dataState.filter, subItemsField)
        let sortedData = orderBy(filteredData, dataState.sort, subItemsField)
        return this.addExpandField(sortedData);
    }

    exportToExcel = () =>{
        const data = treeToFlat(this.processData(), expandField, subItemsField);
        const options = this._export.workbookOptions(data, columns);
        const rows = options.sheets[0].rows;

        let altIdx = 0;
        rows.forEach((row) => {
            if (row.type === 'data') {
                if (altIdx % 2 !== 0) {
                    row.cells.forEach((cell) => {
                        cell.background = '#aabbcc';
                    });
                }
                altIdx++;
            }
        });
        console.log(options)
        this._export.save(options);
    }

    render() {
        return (
            <ExcelExport
                ref={exporter => this._export = exporter}
                hierarchy={true}
            >
                <TreeList
                    style={{ maxHeight: '510px', overflow: 'auto' }}
                    expandField={expandField}
                    subItemsField={subItemsField}
                    onExpandChange={this.onExpandChange}
                    sortable={{ mode: 'multiple' }}
                    {...this.state.dataState}
                    data={this.processData()}
                    onDataStateChange={this.handleDataStateChange}
                    columns={columns}
                    toolbar={
                        <TreeListToolbar>
                            <button
                                title="Excel export"
                                className="k-button k-primary"
                                onClick={this.exportToExcel}
                            >
                                Export to Excel
                            </button>
                        </TreeListToolbar>
                    }
                />
            </ExcelExport>
        );
    }
}

ReactDOM.render(
    <App />,
    document.querySelector('my-app')
);

Known Limitations

  • During the export to Excel, the TreeList does not use column formats. Column formats are incompatible with Excel. For more information, refer to the page on the Excel-supported formats.
  • The maximum size of the exported file to Excel has a system-specific limit. For large data sets, it is highly recommended that you use a server-side solution.
  • When you use the ExcelExport in older browsers, such as Internet Explorer 9 and Safari, you have to implement a server proxy. For more information, refer to the proxyUrl configuration of the ExcelExport component.