Search results

Excel Export Options in React TreeGrid component

25 Jan 2023 / 4 minutes to read

To customize excel export

The excel export provides an option to customize mapping of the treegrid to excel document.

Export hidden columns

The excel export provides an option to export hidden columns of treegrid by defining includeHiddenColumn as true.

Source
Preview
App.jsx
App.tsx
Copied to clipboard
import { ColumnDirective, ColumnsDirective, Page, TreeGridComponent } from '@syncfusion/ej2-react-treegrid';
import { ExcelExport, Inject, Toolbar } from '@syncfusion/ej2-react-treegrid';
import * as React from 'react';
import './App.css';
import { sampleData } from './datasource';
function App() {
    const toolbarOptions = ['ExcelExport'];
    const pageSettings = { pageSize: 7 };
    const treegrid = React.useRef(null);
    const toolbarClick = (args) => {
        if (treegrid && args.item.text === 'Excel Export') {
            const excelExportProperties = {
                includeHiddenColumn: true
            };
            treegrid.current.excelExport(excelExportProperties);
        }
    };
    return <TreeGridComponent dataSource={sampleData} treeColumnIndex={1} childMapping='subtasks' allowPaging={true} pageSettings={pageSettings} allowExcelExport={true} height='220' toolbarClick={toolbarClick} ref={g => treegrid = g} toolbar={toolbarOptions}>
        <ColumnsDirective>
            <ColumnDirective field='taskID' headerText='Task ID' width='90' textAlign='Right'/>
            <ColumnDirective field='taskName' headerText='Task Name' width='180'/>
            <ColumnDirective field='startDate' headerText='Start Date' width='90' format='yMd' textAlign='Right' type='date'/>
            <ColumnDirective field='duration' visible={false} headerText='Duration' width='80' textAlign='Right'/>
        </ColumnsDirective>
        <Inject services={[Page, Toolbar, ExcelExport]}/>
    </TreeGridComponent>;
}
;
export default App;
Copied to clipboard
import { ExcelExportProperties } from '@syncfusion/ej2-grids';
import { ClickEventArgs } from '@syncfusion/ej2-navigations';
import { ColumnDirective, ColumnsDirective, Page, PageSettingsModel, TreeGridComponent } from '@syncfusion/ej2-react-treegrid';
import { ExcelExport, Inject, Toolbar, ToolbarItems, TreeGrid } from '@syncfusion/ej2-react-treegrid';
import * as React from 'react';
import './App.css';
import { sampleData } from './datasource';

function App() {
    const toolbarOptions: ToolbarItems[] = ['ExcelExport'];
    const pageSettings: PageSettingsModel = { pageSize: 7 };
    const treegrid = React.useRef(null);

    const toolbarClick = (args: ClickEventArgs): void => {
        if (treegrid && args.item.text === 'Excel Export') {
            const excelExportProperties: ExcelExportProperties = {
                includeHiddenColumn: true
            };
            (treegrid.current as any).excelExport(excelExportProperties);
        }
    }

    return <TreeGridComponent dataSource={sampleData} treeColumnIndex={1} childMapping='subtasks'
        allowPaging={true} pageSettings={pageSettings} allowExcelExport={true} height='220'
        toolbarClick={toolbarClick} ref={g => treegrid = g} toolbar={toolbarOptions}>
        <ColumnsDirective>
            <ColumnDirective field='taskID' headerText='Task ID' width='90' textAlign='Right'/>
            <ColumnDirective field='taskName' headerText='Task Name' width='180'/>
            <ColumnDirective field='startDate' headerText='Start Date' width='90' format='yMd' textAlign='Right' type='date'/>
            <ColumnDirective field='duration'  visible={false} headerText='Duration' width='80' textAlign='Right'/>
        </ColumnsDirective>
        <Inject services={[Page, Toolbar, ExcelExport]}/>
    </TreeGridComponent>
};
export default App;

Show or hide columns on exported excel

You can show a hidden column or hide a visible column while printing the treegrid using toolbarClick and excelExportComplete events.

In the toolbarClick event, based on args.item.text as Excel Export. We can show or hide columns by setting column.visible property to true or false respectively.

In the excelExportComplete event, We have reversed the state back to the previous state.

In the below example, we have Duration as a hidden column in the treegrid. While exporting, we have changed Duration to visible column and StartDate as hidden column.

Source
Preview
App.jsx
App.tsx
Copied to clipboard
import { ColumnDirective, ColumnsDirective, Page, TreeGridComponent } from '@syncfusion/ej2-react-treegrid';
import { ExcelExport, Inject, Toolbar } from '@syncfusion/ej2-react-treegrid';
import * as React from 'react';
import './App.css';
import { sampleData } from './datasource';
function App() {
    const toolbarOptions = ['ExcelExport'];
    const pageSettings = { pageSize: 7 };
    let treegrid;
    const toolbarClick = (args) => {
        if (treegrid && args.item.text === 'Excel Export') {
            const cols = treegrid.grid.columns;
            cols[2].visible = false;
            cols[3].visible = true;
            treegrid.excelExport();
        }
    };
    const excelExportComplete = () => {
        if (treegrid) {
            const cols = treegrid.grid.columns;
            cols[3].visible = false;
            cols[2].visible = true;
        }
    };
    return <TreeGridComponent dataSource={sampleData} treeColumnIndex={1} childMapping='subtasks' allowPaging={true} pageSettings={pageSettings} allowExcelExport={true} height='220' toolbarClick={toolbarClick} ref={g => treegrid = g} toolbar={toolbarOptions}>
        <ColumnsDirective>
            <ColumnDirective field='taskID' headerText='Task ID' width='90' textAlign='Right'/>
            <ColumnDirective field='taskName' headerText='Task Name' width='180'/>
            <ColumnDirective field='startDate' headerText='Start Date' width='90' format='yMd' textAlign='Right' type='date'/>
            <ColumnDirective field='duration' visible={false} headerText='Duration' width='80' textAlign='Right'/>
        </ColumnsDirective>
        <Inject services={[Page, Toolbar, ExcelExport]}/>
    </TreeGridComponent>;
}
;
export default App;
Copied to clipboard
import { ClickEventArgs } from '@syncfusion/ej2-navigations';
import { ColumnDirective, ColumnsDirective, Page, PageSettingsModel, TreeGridComponent } from '@syncfusion/ej2-react-treegrid';
import { Column, ExcelExport, Inject, Toolbar, ToolbarItems, TreeGrid } from '@syncfusion/ej2-react-treegrid';
import * as React from 'react';
import './App.css';
import { sampleData } from './datasource';

function App() {
    const toolbarOptions: ToolbarItems[] = ['ExcelExport'];
    const pageSettings: PageSettingsModel = { pageSize: 7 };
    let treegrid: TreeGridComponent | null;

    const toolbarClick = (args: ClickEventArgs): void => {
        if (treegrid && args.item.text === 'Excel Export') {
            const cols: Column[] = treegrid.grid.columns as Column[];
            cols[2].visible = false;
            cols[3].visible = true;
            treegrid.excelExport();
        }
    }
    const excelExportComplete = (): void => {
        if (treegrid) {
            const cols: Column[] = treegrid.grid.columns as Column[];
            cols[3].visible = false;
            cols[2].visible = true;
        }
    }
    return <TreeGridComponent dataSource={sampleData} treeColumnIndex={1} childMapping='subtasks'
    allowPaging={true} pageSettings={pageSettings} allowExcelExport={true} height='220'
    toolbarClick={toolbarClick} ref={g => treegrid = g}
    toolbar={toolbarOptions}>
        <ColumnsDirective>
            <ColumnDirective field='taskID' headerText='Task ID' width='90' textAlign='Right'/>
            <ColumnDirective field='taskName' headerText='Task Name' width='180'/>
            <ColumnDirective field='startDate' headerText='Start Date' width='90' format='yMd' textAlign='Right' type='date' />
            <ColumnDirective field='duration'  visible={false} headerText='Duration' width='80' textAlign='Right' />
        </ColumnsDirective>
        <Inject services={[Page, Toolbar, ExcelExport]}/>
    </TreeGridComponent>
};
export default App;

File name for exported document

You can assign the file name for the exported document by defining fileName property in ExcelExportProperties.

Source
Preview
App.jsx
App.tsx
Copied to clipboard
import * as React from 'react';
import { TreeGridComponent, ColumnsDirective, ColumnDirective, Page, Toolbar, ExcelExport, Inject } from '@syncfusion/ej2-react-treegrid';
import { sampleData } from './datasource';
function App() {
    const toolbarOptions = ['ExcelExport'];
    const treegrid = React.useRef(null);
    const toolbarClick = (args) => {
        if (args.item.text === 'Excel Export') {
            let excelExportProperties = {
                fileName: "new.xlsx"
            };
            treegrid.current.excelExport(excelExportProperties);
        }
    };
    return <TreeGridComponent dataSource={sampleData} treeColumnIndex={1} childMapping='subtasks' allowPaging={true} pageSettings={{ pageSize: 7 }} allowExcelExport={true} height='220' toolbarClick={toolbarClick} ref={treegrid} toolbar={toolbarOptions}>
        <ColumnsDirective>
            <ColumnDirective field='taskID' headerText='Task ID' width='90' textAlign='Right'></ColumnDirective>
            <ColumnDirective field='taskName' headerText='Task Name' width='180'></ColumnDirective>
            <ColumnDirective field='startDate' headerText='Start Date' width='90' format='yMd' textAlign='Right' type='date'/>
            <ColumnDirective field='duration' headerText='Duration' width='80' textAlign='Right'/>
        </ColumnsDirective>
        <Inject services={[Page, Toolbar, ExcelExport]}/>
    </TreeGridComponent>;
}
;
export default App;
Copied to clipboard
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { TreeGridComponent, ColumnsDirective, ColumnDirective, Page, Toolbar, ExcelExport, ExcelExportProperties, Inject } from '@syncfusion/ej2-react-treegrid';
import { ClickEventArgs } from '@syncfusion/ej2-navigations';
import { sampleData } from './datasource';

function App() {

    const toolbarOptions: any = ['ExcelExport'];

    const treegrid = React.useRef(null);

    const toolbarClick = (args: ClickEventArgs): void =>{
        if (args.item.text === 'Excel Export') {
            let excelExportProperties: ExcelExportProperties = {
                fileName:"new.xlsx"
            };
            (treegrid.current as any).excelExport(excelExportProperties);
        }
    }

    return <TreeGridComponent dataSource={sampleData} treeColumnIndex={1} childMapping='subtasks' allowPaging={true} pageSettings={{ pageSize: 7 }} allowExcelExport={true} height='220'
    toolbarClick={toolbarClick} ref={treegrid}
    toolbar={toolbarOptions}>
        <ColumnsDirective>
            <ColumnDirective field='taskID' headerText='Task ID' width='90' textAlign='Right'></ColumnDirective>
            <ColumnDirective field='taskName' headerText='Task Name' width='180'></ColumnDirective>
            <ColumnDirective field='startDate' headerText='Start Date' width='90' format='yMd' textAlign='Right' type='date' />
            <ColumnDirective field='duration' headerText='Duration' width='80' textAlign='Right' />
        </ColumnsDirective>
        <Inject services={[Page, Toolbar, ExcelExport]}/>
    </TreeGridComponent>
};
export default App;