Excel export in EJ2 TypeScript Treegrid control

1 Sep 202522 minutes to read

The excel export allows exporting TreeGrid data to Excel document. You need to use the excelExport method for exporting. To enable Excel export in the treegrid, set the allowExcelExport as true.

To use excel export, You need to inject the ExcelExport module in treegrid.

index.ts
index.html
import { TreeGrid, Toolbar, ExcelExport, 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') {
        treeGridObj.excelExport();
    }
}
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/31.1.17/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-treegrid/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-calendars/styles/material.css" rel="stylesheet" />
    
    
    
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/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>

Persist collapsed state

You can persist the collapsed state in the exported document by defining isCollapsedStatePersist property as true in TreeGridExcelExportProperties parameter of excelExport method.

index.ts
index.html
import { TreeGrid, Toolbar, ExcelExport, TreeGridExcelExportProperties, 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: TreeGridExcelExportProperties = {
            isCollapsedStatePersist: 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/31.1.17/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-treegrid/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-calendars/styles/material.css" rel="stylesheet" />
    
    
    
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/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>

Custom data source

The excel export provides an option to define datasource dynamically before exporting. To export data dynamically, define the dataSource in exportProperties.

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 = {
            dataSource: sampleData
        };
        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/31.1.17/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-treegrid/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-calendars/styles/material.css" rel="stylesheet" />
    
    
    
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/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>

Exporting custom aggregates in TreeGrid

The TreeGrid enables exporting custom aggregates, which summarize column data, to an Excel document using the excelAggregateQueryCellInfo event.

In the provided example, the customAggregateFn function computes the item count for a selected category, while the excelAggregateQueryCellInfo event customizes the exported cell values in the Excel document.

index.ts
index.html
import { TreeGrid, Page, Aggregate, Toolbar, PdfExport, ExcelExport } from '@syncfusion/ej2-treegrid';
import { DropDownList } from '@syncfusion/ej2-dropdowns';
import { summaryData } from './datasource.ts';
import { getObject, CustomSummaryType, AggregateQueryCellInfoEventArgs } from '@syncfusion/ej2-grids';

TreeGrid.Inject(Page, Aggregate, Toolbar, PdfExport, ExcelExport);

let selectedCategory: string = 'Seafood';
let categoryDropdown: DropDownList | null = null;
const categoryOptions: { [key: string]: Object; }[] = [
  { food: 'Seafood' },
  { food: 'Dairy' },
  { food: 'Edible' },
  { food: 'Crystal' },
];

//Custom aggregate function to calculate the count of items for the selected category.
const customAggregateFn: CustomSummaryType = (data: object): number => {
  const records: any = (data as any).result ? getObject('result', data) : data;
  return records.reduce((count: number, item: Object) => {
    const category = getObject('category', item) as string;
    return category === selectedCategory ? count + 1 : count;
  }, 0);
};

//Handles the 'excelAggregateQueryCellInfo' event to customize aggregate cells during Excel export.
const formatExcelAggregateCell = (args: AggregateQueryCellInfoEventArgs): void => {
  if ((args.cell as any).column.headerText === 'Category') {
    (args.style as any).value = `Count of ${selectedCategory} : ${(args.row as any).data.category.Custom}`;
  }
};

//Initializes a DropDownList in the footer for category selection.
const onDataBound: any = () => {
  if (categoryDropdown && categoryDropdown.element && categoryDropdown.element.classList.contains('e-' + categoryDropdown.getModuleName())) {
    categoryDropdown.destroy();
  }
  categoryDropdown = new DropDownList({
    dataSource: categoryOptions,
    fields: { value: 'food' },
    placeholder: 'Select a Category',
    width: '110px',
    value: selectedCategory,
    change: () => {
      setTimeout(() => {
        if (categoryDropdown && categoryDropdown.value) {
          selectedCategory = categoryDropdown.value.toString();
          treeGrid.refresh();
        }
      }, 300);
    }
  });
  categoryDropdown.appendTo('#customers');
}

const treeGrid = new TreeGrid({
  dataSource: summaryData,
  childMapping: 'subtasks',
  width: 'auto',
  height: 400,
  treeColumnIndex: 1,
  gridLines: 'Both',
  allowExcelExport: true,
  excelAggregateQueryCellInfo: formatExcelAggregateCell,
  toolbar: ['ExcelExport', 'CsvExport'],
  dataBound: onDataBound,
  columns: [
    { field: 'ID', headerText: 'Order ID', width: 115, textAlign: 'Left' },
    { field: 'Name', headerText: 'Shipment Name', width: 230, clipMode: 'EllipsisWithTooltip' },
    { field: 'shipmentDate', headerText: 'Shipment Date', width: 135, type: 'date', format: 'yMd', textAlign: 'Right' },
    { field: 'category', headerText: 'Category', width: 220, minWidth: 210 },
    { field: 'units', headerText: 'Total Units', width: 90, type: 'number', textAlign: 'Right' },
    { field: 'unitPrice', headerText: 'Unit Price($)', width: 100, type: 'number', format: 'C2', textAlign: 'Right' },
    { field: 'price', headerText: 'Price($)', width: 140, format: 'C0', textAlign: 'Right', type: 'number' },
  ],
  aggregates: [{
    showChildSummary: false,
    columns: [
      {
        type: 'Custom',
        customAggregate: customAggregateFn,
        columnName: 'category',
        format: 'C2',
        footerTemplate: (data: any) => {
          return `<span>Count of <input type="text" id="customers" /> : ${data.Custom}</span>`;
        }
      },
    ]
  }]
});

treeGrid.appendTo('#TreeGrid');

treeGrid.toolbarClick = (args: any) => {
  switch (args.item.text) {
    case 'Excel Export':
      treeGrid.excelExport();
      break;
    case 'CSV Export':
      treeGrid.csvExport();
      break;
  }
};
<!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/31.1.17/ej2-base/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-grids/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-treegrid/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-buttons/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-popups/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-navigations/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-dropdowns/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-lists/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-inputs/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/ej2-calendars/styles/material.css" rel="stylesheet" />
    <link href="https://cdn.syncfusion.com/ej2/31.1.17/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>

You can refer to our JavaScript Tree Grid feature tour page for its groundbreaking feature representations. You can also explore our JavaScript Tree Grid example JavaScript Tree Grid example to knows how to present and manipulate data.

Search docs

Ask HelpBot