Excel export options in EJ2 TypeScript Treegrid control

16 Sep 202314 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.

index.ts
index.html
import { TreeGrid, Toolbar, ExcelExport, Page, ExcelExportProperties } from '@syncfusion/ej2-treegrid';
import { sampleData } from './datasource.ts';
TreeGrid.Inject(Toolbar, ExcelExport, Page);

let treeGridObj: TreeGrid = new TreeGrid({
    dataSource: sampleData,
    childMapping: 'subtasks',
    allowExcelExport: true,
    allowPaging: true,
    height: 220,
    pageSettings: {pageSize: 7},
    toolbar: ['ExcelExport'],
    treeColumnIndex: 1,
    columns: [
            { field: 'taskID', headerText: 'Task ID', width: 90, textAlign: 'Right' },
            { field: 'taskName', headerText: 'Task Name', width: 180, textAlign: 'Left' },
            {
                field: 'startDate', headerText: 'Start Date', width: 90, textAlign: 'Right', type: 'date', format: 'yMd'
            },
            { field: 'duration', headerText: 'Duration', visible: false, width: 80, textAlign: 'Right' }
    ]
});
treeGridObj.toolbarClick = (args: Object) => {
    if (args['item'].text === 'Excel Export') {
        let excelExportProperties: ExcelExportProperties = {
            includeHiddenColumn: true
        };
        treeGridObj.excelExport(excelExportProperties);
    }
}
treeGridObj.appendTo('#TreeGrid');
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-treegrid/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-calendars/styles/material.css" rel="stylesheet" />
    
    
    
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
    
    
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <script src="systemjs.config.js"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>
<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div id='TreeGrid'></div>   
    </div>
</body>
</html>

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.

index.ts
index.html
import { TreeGrid, Page, Toolbar, ExcelExport, ExcelExportProperties } from '@syncfusion/ej2-treegrid';
import { Column }  from '@syncfusion/ej2-grids';
import { sampleData } from './datasource.ts';

TreeGrid.Inject(Page, Toolbar, ExcelExport);

let treeGridObj: TreeGrid = new TreeGrid({
    dataSource: sampleData,
    childMapping: 'subtasks',
    allowExcelExport: true,
    allowPaging: true,
    height: 220,
    pageSettings: {pageSize: 7},
    toolbar: ['ExcelExport'],
    treeColumnIndex: 1,
    columns: [
            { field: 'taskID', headerText: 'Task ID', width: 90, textAlign: 'Right' },
            { field: 'taskName', headerText: 'Task Name', width: 180, textAlign: 'Left' },
            {
                field: 'startDate', headerText: 'Start Date', width: 90, textAlign: 'Right', type: 'date', format: 'yMd'
            },
            { field: 'duration', headerText: 'Duration', visible: false, width: 80, textAlign: 'Right' }
    ]
});
treeGridObj.toolbarClick = (args: Object) => {
if (args['item'].text === 'Excel Export') {
        let cols: Column[] = treeGridObj.grid.columns;
        cols[2].visible = false;
        cols[3].visible = true;
        treeGridObj.excelExport();
    }
}
treeGridObj.excelExportComplete = () => {
    let cols: Column[] = treeGridObj.grid.columns;
    cols[3].visible = false;
    cols[2].visible = true;
}
treeGridObj.appendTo('#TreeGrid');
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-treegrid/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-calendars/styles/material.css" rel="stylesheet" />
    
    
    
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
    
    
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <script src="systemjs.config.js"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>
<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div id='TreeGrid'></div>   
    </div>
</body>
</html>

File name for exported document

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

index.ts
index.html
import { TreeGrid, Toolbar, ExcelExport, ExcelExportProperties, Page } from '@syncfusion/ej2-treegrid';
import { sampleData } from './datasource.ts';

TreeGrid.Inject(Toolbar, ExcelExport, Page);

let treeGridObj: TreeGrid = new TreeGrid({
    dataSource: sampleData,
    childMapping: 'subtasks',
    allowExcelExport: true,
    allowPaging: true,
    height: 220,
    pageSettings: {pageSize: 7},
    toolbar: ['ExcelExport'],
    treeColumnIndex: 1,
    columns: [
            { field: 'taskID', headerText: 'Task ID', width: 90, textAlign: 'Right' },
            { field: 'taskName', headerText: 'Task Name', width: 180, textAlign: 'Left' },
            {
                field: 'startDate', headerText: 'Start Date', width: 90, textAlign: 'Right', type: 'date', format: 'yMd'
            },
            { field: 'duration', headerText: 'Duration', width: 80, textAlign: 'Right' }
    ]
});
treeGridObj.toolbarClick = (args: Object) => {
    if (args['item'].text === 'Excel Export') {
        let excelExportProperties: ExcelExportProperties = {
            fileName:"new.xlsx"
        };
        treeGridObj.excelExport(excelExportProperties);

    }
}
treeGridObj.appendTo('#TreeGrid');
<!DOCTYPE html>
<html lang="en">

<head>
    <title>EJ2 Grid</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta name="description" content="Typescript Grid Control" />
    <meta name="author" content="Syncfusion" />
    <link href="index.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-treegrid/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-calendars/styles/material.css" rel="stylesheet" />
    
    
    
    <link href="https://cdn.syncfusion.com/ej2/28.1.33/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
    
    
    <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
    <script src="systemjs.config.js"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>
<body>
    <div id='loader'>Loading....</div>
    <div id='container'>
        <div id='TreeGrid'></div>   
    </div>
</body>
</html>