Search results

Excel Cell Style Customization in React TreeGrid component

02 Feb 2023 / 3 minutes to read

Conditional cell formatting

TreeGrid cells in the exported Excel can be customized or formatted using excelQueryCellInfo event. In this event, we can format the treegrid cells of exported PDF document based on the column cell value.

In the below sample, we have set the background color for Duration column in the exported excel by args.cell and backgroundColor property.

Source
Preview
App.jsx
App.tsx
Copied to clipboard
import { getValue } from '@syncfusion/ej2-base';
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') {
            treegrid.current.excelExport();
        }
    };
    const excelQueryCellInfo = (args) => {
        if (args.column.field === 'duration') {
            if (getValue('data.duration', args) === 0) {
                args.style = { backColor: '#336c12' };
            }
            else if (getValue('data.duration', args) < 3) {
                args.style = { backColor: '#7b2b1d' };
            }
        }
    };
    const queryCellInfo = (args) => {
        if (args.column.field === 'duration') {
            if (getValue('data.duration', args) === 0) {
                args.cell.style.background = '#336c12';
            }
            else if (getValue('data.duration', args) < 3) {
                args.cell.style.background = '#7b2b1d';
            }
        }
    };
    return <TreeGridComponent dataSource={sampleData} treeColumnIndex={1} childMapping='subtasks' allowPaging={true} pageSettings={pageSettings} allowExcelExport={true} height='220' toolbarClick={toolbarClick} ref={g => treegrid = g} toolbar={toolbarOptions} queryCellInfo={queryCellInfo} excelQueryCellInfo={excelQueryCellInfo}>
        <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' headerText='Duration' width='80' textAlign='Right'/>
        </ColumnsDirective>
        <Inject services={[Page, Toolbar, ExcelExport]}/>
    </TreeGridComponent>;
}
;
export default App;
Copied to clipboard
import { getValue } from '@syncfusion/ej2-base';
import { Column, ExcelQueryCellInfoEventArgs, QueryCellInfoEventArgs } 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') {
            (treegrid.current as any).excelExport();
        }
    }
    const excelQueryCellInfo = (args: ExcelQueryCellInfoEventArgs): void => {
        if (args.column.field === 'duration') {
            if (getValue('data.duration', args) === 0) {
                args.style = {backColor: '#336c12'};
            }
            else if (getValue('data.duration', args) < 3) {
                args.style = {backColor: '#7b2b1d'};
            }
        }
    }

    const queryCellInfo = (args: QueryCellInfoEventArgs): void => {
        if ((args.column as Column).field === 'duration') {
            if (getValue('data.duration', args) === 0) {
                (args.cell as HTMLElement).style.background= '#336c12';
            } else if (getValue('data.duration', args) < 3) {
                (args.cell as HTMLElement).style.background= '#7b2b1d';
            }
        }
    }
    return <TreeGridComponent dataSource={sampleData} treeColumnIndex={1} childMapping='subtasks'
        allowPaging={true} pageSettings={pageSettings} allowExcelExport={true} height='220'
        toolbarClick={toolbarClick} ref={g => treegrid = g} toolbar={toolbarOptions} queryCellInfo={queryCellInfo} excelQueryCellInfo={excelQueryCellInfo}>
        <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' headerText='Duration' width='80' textAlign='Right' />
        </ColumnsDirective>
        <Inject services={[Page, Toolbar, ExcelExport]}/>
    </TreeGridComponent>
};
export default App;

Theme

The excel export provides an option to include theme for exported excel document.

To apply theme in exported Excel, define the theme in ExcelExportProperties.

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 exportProperties = {
                theme: {
                    caption: { fontName: 'Segoe UI', fontColor: '#666666' },
                    header: { fontName: 'Segoe UI', fontColor: '#666666' },
                    record: { fontName: 'Segoe UI', fontColor: '#666666' }
                }
            };
            treegrid.current.excelExport(exportProperties);
        }
    };
    return <TreeGridComponent dataSource={sampleData} treeColumnIndex={1} childMapping='subtasks' allowPaging={true} pageSettings={pageSettings} allowExcelExport={true} height='220' toolbarClick={toolbarClick} ref={treegrid} 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' 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 exportProperties: ExcelExportProperties = {
                theme:
                    {
                        caption: { fontName: 'Segoe UI', fontColor: '#666666' },
                        header: { fontName: 'Segoe UI', fontColor: '#666666' },
                        record: { fontName: 'Segoe UI', fontColor: '#666666' }
                    }
                };
            (treegrid.current as any).excelExport(exportProperties);
        }
    }
    return <TreeGridComponent dataSource={sampleData} treeColumnIndex={1} childMapping='subtasks'
        allowPaging={true} pageSettings={pageSettings} allowExcelExport={true} height='220'
    toolbarClick={toolbarClick} ref={treegrid}
    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' headerText='Duration' width='80' textAlign='Right' />
        </ColumnsDirective>
        <Inject services={[Page, Toolbar, ExcelExport]}/>
    </TreeGridComponent>
};
export default App;

By default, material theme is applied to exported excel document.