Excel export in EJ2 TypeScript Pivot Table component
17 Dec 202524 minutes to read
The Pivot Table component supports exporting pivot data to Excel and CSV file formats. This enables data sharing and analysis in spreadsheet applications such as Microsoft Excel, Google Sheets, and more. To enable the export functionality, inject the ExcelExport module into the Pivot Table and set the allowExcelExport property to true.
Export data to an Excel file
Pivot Table data can be exported to an Excel file (.xlsx format) while preserving all formatting and structure. This format is compatible with Microsoft Excel and other spreadsheet applications. To export the data to Excel, invoke the excelExport method.
import { PivotView, IDataSet, ExcelExport} from '@syncfusion/ej2-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { pivotData } from './datasource.ts';
PivotView.Inject(ExcelExport);
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
dataSource: pivotData as IDataSet[],
expandAll: false,
drilledMembers: [{ name: 'Country', items: ['France'] }],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
filters: []
},
allowExcelExport: true,
height: 320
});
pivotTableObj.appendTo('#PivotTable');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function () {
pivotTableObj.excelExport();
}<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/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'>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotTable'></div>
<br />
<div id='PivotTable2'></div>
</div>
</body>
</html>Export data to a CSV file
Pivot Table data can be exported to a plain text CSV file. The CSV format is lightweight and compatible with most spreadsheet and data analysis applications. To export the data to CSV, invoke the csvExport method.
import { PivotView, IDataSet, ExcelExport } from '@syncfusion/ej2-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { pivotData } from './datasource.ts';
PivotView.Inject(ExcelExport);
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
dataSource: pivotData as IDataSet[],
expandAll: false,
drilledMembers: [{ name: 'Country', items: ['France'] }],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
filters: []
},
allowExcelExport: true,
height: 320
});
pivotTableObj.appendTo('#PivotTable');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function () {
pivotTableObj.csvExport();
}<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/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'>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotTable'></div>
<br />
<div id='PivotTable2'></div>
</div>
</body>
</html>Exporting multiple pivot tables
Multiple Pivot Tables can be exported to a single Excel file, allowing for side-by-side comparison on the same or different worksheets. Each Pivot Table must be rendered with a unique HTML element ID. For example, PivotTable1 for the first table and PivotTable2 for the second. To export both Pivot Tables to a single Excel file, provide their IDs in the pivotTableIds property of the excelExportProperties.
Exporting to the same worksheet
Data from multiple Pivot Tables can be organized in a single view by exporting them to the same worksheet. Set the multipleExport.type property to AppendToSheet in the excelExportProperties, which will append each Pivot Table to the same sheet.
To add visual separation between Pivot Tables, use the multipleExport.blankRows property to specify the number of blank rows to insert between them. This helps maintain readability when multiple Pivot Tables are added in a single worksheet. After configuring these options, call the excelExport method with the isMultipleExport parameter set to true.
By default, the multipleExport.blankRows property is set to 5 blank rows.
import { PivotView, IDataSet, ExcelExport, ExcelExportProperties } from '@syncfusion/ej2-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { pivotData } from './datasource.ts';
PivotView.Inject(ExcelExport);
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
dataSource: pivotData as IDataSet[],
expandAll: false,
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
filters: []
},
allowExcelExport: true,
height: 320
});
pivotTableObj.appendTo('#PivotTable');
let pivotTableObj2: PivotView = new PivotView({
dataSourceSettings: {
dataSource: pivotData as IDataSet[],
expandAll: false,
rows: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Amount', caption: 'Sold Amount' }, { name: 'Sold', caption: 'Units Sold' }],
columns: [{ name: 'Country' }, { name: 'Products' }],
filters: []
},
allowExcelExport: true
});
pivotTableObj2.appendTo('#PivotTable2');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function () {
let excelExportProperties: ExcelExportProperties = {
multipleExport: { type: 'AppendToSheet', blankRows: 2 },
pivotTableIds: ['PivotTable', 'PivotTable2']
};
pivotTableObj.excelExport(excelExportProperties, true);
};<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/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'>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotTable'></div>
<br />
<div id='PivotTable2'></div>
</div>
</body>
</html>Exporting to a new worksheet
Multiple Pivot Tables can be organized into separate worksheets within a single Excel file for better structured data management. Set the multipleExport.type property to NewSheet in the excelExportProperties. Each Pivot Table will be exported to its own dedicated worksheet. After configuring these options, call the excelExport method with the isMultipleExport parameter set to true.
import { PivotView, IDataSet, ExcelExport, ExcelExportProperties } from '@syncfusion/ej2-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { pivotData } from './datasource.ts';
PivotView.Inject(ExcelExport);
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
dataSource: pivotData as IDataSet[],
enableSorting: true,
expandAll: false,
filters: [],
columns: [{ name: 'Year' }],
values: [
{ name: 'Sold', caption: 'Units Sold' },
{ name: 'Amount', caption: 'Sold Amount' },
],
rows: [{ name: 'Country' }, { name: 'Products' }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
},
allowExcelExport: true,
height: 320
});
pivotTableObj.appendTo('#PivotTable');
let pivotTableObj2: PivotView = new PivotView({
dataSourceSettings: {
dataSource: pivotData as IDataSet[],
expandAll: false,
rows: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Amount', caption: 'Sold Amount' }, { name: 'Sold', caption: 'Units Sold' }],
columns: [{ name: 'Country' }, { name: 'Products' }],
filters: []
},
allowExcelExport: true
});
pivotTableObj2.appendTo('#PivotTable2');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function () {
let excelExportProperties: ExcelExportProperties = {
multipleExport: { type: 'NewSheet' },
pivotTableIds: ['PivotTable', 'PivotTable2']
};
pivotTableObj.excelExport(excelExportProperties, true);
};<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/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'>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotTable'></div>
<br />
<div id='PivotTable2'></div>
</div>
</body>
</html>Customize the pivot report during export
Pivot Table report settings can be customized before exporting, such as applying filters, adding formatting, or performing drill-down and drill-up operations. These customizations are applied exclusively to the exported file and do not affect the Pivot Table UI. To customize the export behavior, use the beforeExport event, which is triggered before the export operation begins.
In the following example, the beforeExport event is used to expand all Pivot Table headers by setting the expandAll property to true. The generateGridData method is then called to obtain the updated pivotValues. The updated pivotValues are assigned to args.dataCollections for the export. Finally, expandAll is set to false again to restore the original state of the Pivot Table.
import { PivotView, IDataSet, ExcelExport, BeforeExportEventArgs } from '@syncfusion/ej2-pivotview';
import { pivotData } from './datasource.ts';
import { Button } from '@syncfusion/ej2-buttons';
//335 or 315
PivotView.Inject(ExcelExport);
let pivotGridObj: PivotView = new PivotView({
dataSourceSettings: {
dataSource: pivotData as IDataSet[],
enableSorting: true,
columns: [{ name: 'Year' }],
values: [
{ name: 'Sold', caption: 'Units Sold' },
{ name: 'Amount', caption: 'Sold Amount' },
],
rows: [{ name: 'Country' }, { name: 'Products' }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
expandAll: false,
filters: []
},
width: '100%',
height: 400,
allowExcelExport: true,
enableVirtualization: true,
beforeExport: function (args: BeforeExportEventArgs): void {
// Store the drilledMembers here(row/column headers that has been expanded).
let member = pivotGridObj.dataSourceSettings.drilledMembers;
// Then apply expandAll to the pivot table settings to get all the data including child members.
pivotGridObj.setProperties(
{ dataSourceSettings: { expandAll: true, drilledMembers: [] } },
true
);
pivotGridObj.engineModule.generateGridData(pivotGridObj.dataSourceSettings, true);
// Assign that retrieved data to the exporting here.
args.dataCollections = [pivotGridObj.engineModule.pivotValues];
// Then set false to expandAll and the stored members to the drilledMembers to the pivot table settings to get back to the normal state.
pivotGridObj.setProperties(
{ dataSourceSettings: { expandAll: false, drilledMembers: member } },
true
);
}
});
pivotGridObj.appendTo('#PivotView');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function () {
pivotGridObj.excelExport();
};<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/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'>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotView'></div>
</div>
</body>
</html>Export with custom aggregates
The Pivot Table supports exporting data with custom calculations beyond the default options such as Sum, Count, or Average. Custom aggregates enable advanced analytical scenarios where standard calculations are insufficient.
To add custom aggregates, follow these steps:
- Define custom aggregate names using the localization option. These names will appear in the Pivot Table’s aggregation menu.
- Add the custom aggregation types to the aggregate menu during Pivot Table initialization using the
dataBoundevent. - Use the
aggregateCellInfoevent to specify the calculation logic for each custom type. This event is triggered for every aggregate cell, allowing you to apply your custom formulas. - Finally, call the
excelExportmethod to export the Pivot Table with all custom aggregations applied.
For detailed information about adding custom aggregation types, refer to the custom aggregation documentation.
The following example demonstrates how to add two custom aggregate types to the aggregate menu: CustomAggregateType 1, which calculates a weighted average, and CustomAggregateType 2, which calculates the percentage of the total.
import { PivotView, ExcelExport, FieldList, IDataSet, AggregateTypes, AggregateEventArgs, SummaryTypes } from '@syncfusion/ej2-pivotview';
import { L10n } from '@syncfusion/ej2-base';
import { pivotData } from './datasource.ts';
import { Button } from '@syncfusion/ej2-buttons';
PivotView.Inject(FieldList, ExcelExport);
L10n.load({
'en-US': {
pivotview: {
CustomAggregateType1: 'Custom Aggregate Type 1',
CustomAggregateType2: 'Custom Aggregate Type 2'
},
pivotfieldlist: {
CustomAggregateType1: 'Custom Aggregate Type 1',
CustomAggregateType2: 'Custom Aggregate Type 2'
}
}
});
const SummaryType: string[] = [
'Sum',
'Count',
'DistinctCount',
'Avg',
'CustomAggregateType1',
'CustomAggregateType2'
];
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
expandAll: true,
dataSource: pivotData as IDataSet[],
columns: [{ name: 'Year' }],
values: [{ name: 'Sold', type: 'CustomAggregateType1' as SummaryTypes }, { name: 'Amount', type: 'CustomAggregateType2' as SummaryTypes }],
rows: [{ name: 'Country' }, { name: 'Products' }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
},
width: '100%',
height: 300,
showFieldList: true,
allowExcelExport: true,
dataBound: function (): void {
pivotTableObj.getAllSummaryType = function () {
return SummaryType as AggregateTypes[];
};
pivotTableObj.pivotFieldListModule.aggregateTypes = SummaryType as AggregateTypes[];
pivotTableObj.pivotFieldListModule.getAllSummaryType = function () {
return SummaryType as AggregateTypes[];
};
},
aggregateCellInfo: function (args: AggregateEventArgs): void {
if (args.aggregateType === 'CustomAggregateType1' as SummaryTypes) {
args.value = args.value * 100;
}
if (args.aggregateType === 'CustomAggregateType2' as SummaryTypes) {
args.value = args.value / 100;
}
}
});
pivotTableObj.appendTo('#PivotTable');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function (): void {
pivotTableObj.excelExport();
};<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-charts/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" />
<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'>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotTable'></div>
</div>
</body>
</html>Export with custom date format
The Pivot Table component allows applying custom date formatting to date-type fields added to the row and column axes. This formatting ensures consistency across both the rendered pivot table and the exported file. Custom date formatting can be applied by configuring the formatSettings property using the following steps:
- Set the
nameproperty to the target date field. - Set the
typeproperty to date to identify the field as a date type. - Set the
formatproperty to the desired date format pattern (for example,"EEE, MMM d, ''yy")
After configuration, call the excelExport method to export the Pivot Table with the applied formatting.
The following example demonstrates exporting a Pivot Table with a custom date format. The Date field uses the pattern "EEE, MMM d, ''yy", which displays dates in the format: day-of-the-week abbreviation, month abbreviation, day, and two-digit year (for example, Sun, May 8, ‘23).
import { PivotView, IDataSet, ExcelExport } from '@syncfusion/ej2-pivotview';
import { Group_Data } from './datasource.ts';
import { Button } from '@syncfusion/ej2-buttons';
PivotView.Inject(ExcelExport);
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
dataSource: Group_Data as IDataSet[],
expandAll: false,
enableSorting: true,
formatSettings: [{ name: 'Date', type: 'date', format: "EEE, MMM d, ''yy" }],
rows: [{ name: 'Date' }],
columns: [{ name: 'Product_Categories', caption: 'Product Categories' }],
values: [{ name: 'Sold', caption: 'Unit Sold' },
{ name: 'Amount', caption: 'Sold Amount' }],
},
allowExcelExport: true,
height: 500,
width: 1000
});
pivotTableObj.appendTo('#PivotTable');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function (): void {
pivotTableObj.excelExport();
};<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-charts/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" />
<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'>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotTable'></div>
</div>
</body>
</html>Remove row header during export
Row headers can be excluded from the exported Excel file when only values and column headers are required. To achieve this, use the beforeExport event to access pivot values through args.dataCollections and remove the row headers before exporting.
import { PivotView, ExcelExport, IDataSet, BeforeExportEventArgs, IAxisSet } from '@syncfusion/ej2-pivotview';
import { pivotData } from './datasource.ts';
import { Button } from '@syncfusion/ej2-buttons';
PivotView.Inject(ExcelExport);
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
expandAll: false,
dataSource: pivotData as IDataSet[],
columns: [{ name: 'Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold' }],
rows: [{ name: 'Country' }, { name: 'Products' }]
},
width: '100%',
height: 300,
showFieldList: true,
allowExcelExport: true,
beforeExport: function (args: BeforeExportEventArgs): void {
for (let i: number = 0; i < args.dataCollections.length; i++) {
const pivotValue: IAxisSet[] = args.dataCollections[i];
for (let j: number = 0; j < pivotValue.length; j++) {
const row: any = pivotValue[j];
pivotValue[j] = row.filter((item: any) => {
return item !== null && (item.axis !== 'row');
});
}
}
}
});
pivotTableObj.appendTo('#PivotTable');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function (): void {
pivotTableObj.excelExport();
};<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-charts/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" />
<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'>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotTable'></div>
</div>
</body>
</html>Exclude hidden columns during export
By default, all columns in the Pivot Table, including hidden ones, are exported. To exclude hidden columns, set the includeHiddenColumn property to false in excelExportProperties.
To hide a column, use the columnRender event in gridSettings to set the visible property of the target column to false. For more information, see the Hide Specific Columns in Pivot Table documentation.
After hiding the columns, set includeHiddenColumn to false in excelExportProperties to exclude them from the exported file. The exported file will then match the column structure shown in the Pivot Table UI.
import { PivotView, ExcelExport, IDataSet, ColumnRenderEventArgs, ExcelExportProperties } from '@syncfusion/ej2-pivotview';
import { pivotData } from './datasource.ts';
import { Button } from '@syncfusion/ej2-buttons';
PivotView.Inject(ExcelExport);
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
dataSource: pivotData as IDataSet[],
expandAll: false,
enableSorting: true,
columns: [{ name: 'Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' }]
},
width: '100%',
height: 300,
showFieldList: true,
allowExcelExport: true,
gridSettings: {
columnRender: function (args: ColumnRenderEventArgs) {
// Specific column(s) can be hidden by checking their level name and setting its visible property accordingly.
for (let i = 1; i < args.columns.length; i++) {
if (args.stackedColumns[i].customAttributes &&
(args.stackedColumns[i].customAttributes.cell as any).valueSort.levelName === 'FY 2016.Units Sold') {
args.stackedColumns[i].visible = false;
}
}
}
},
});
pivotTableObj.appendTo('#PivotTable');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function (): void {
let excelExportProperties: ExcelExportProperties = {
includeHiddenColumn: false
};
pivotTableObj.excelExport(excelExportProperties);
};<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-charts/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" />
<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'>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotTable'></div>
</div>
</body>
</html>Rotate cell text during export
The style of each cell in the exported file can be customized, including rotating text, changing background colors, and applying other visual modifications. This approach is useful for creating visually distinct Pivot Table and for fitting text within limited space.
To rotate text, use the following events:
-
excelHeaderQueryCellInfo: Triggered for column headers. This event is used to customize column header cell styles. -
excelQueryCellInfo: Triggered for row and value cells. This event is used to customize row header and value cell styles.
Within these events, set the rotation property in the style argument to rotate the text to the desired angle.
import { PivotView, ExcelExport, IDataSet } from '@syncfusion/ej2-pivotview';
import { ExcelHeaderQueryCellInfoEventArgs, ExcelQueryCellInfoEventArgs } from '@syncfusion/ej2-grids';
import { pivotData } from './datasource.ts';
import { Button } from '@syncfusion/ej2-buttons';
import { DropDownList } from '@syncfusion/ej2-dropdowns';
PivotView.Inject(ExcelExport);
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
expandAll: false,
dataSource: pivotData as IDataSet[],
columns: [{ name: 'Year' }],
values: [{ name: 'Sold' }],
rows: [{ name: 'Country' }, { name: 'Products' }]
},
width: '100%',
height: 300,
showFieldList: true,
allowExcelExport: true,
gridSettings: {
excelHeaderQueryCellInfo: function (args: ExcelHeaderQueryCellInfoEventArgs): void {
args.style = { rotation: dropDownListObject.value as number };
},
excelQueryCellInfo: function (args: ExcelQueryCellInfoEventArgs): void {
args.style = { rotation: dropDownListObject.value as number };
}
}
});
pivotTableObj.appendTo('#PivotTable');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
let degree = [90, 135, 180, 270];
let dropDownListObject: DropDownList = new DropDownList({
dataSource: degree,
placeholder: 'Select a degree',
width: 120
});
dropDownListObject.appendTo('#ddlelement');
document.getElementById('excel').onclick = function (): void {
pivotTableObj.excelExport();
};<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-charts/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" />
<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 class="container">
<input id="excel" type="button" value="Excel Export" name="Excel Export" />
<label style="margin-left: 100px; margin-right: 10px;">Select a degree:</label>
<input type="text" tabindex="1" id="ddlelement">
<div id="PivotTable" style="padding:20px 0px 0px 0px"></div>
</div>
</body>
</html>Apply custom styles based on specific conditions
When exporting Pivot Table data to Excel, custom styles can be applied to cells based on their values or other criteria. To apply custom styles, use the excelQueryCellInfo event. In this event, the cell information can be accessed through the args.cell property, and its style properties, such as backColor, fontName, and fontColor, can be customized.
The following example demonstrates how to apply conditional formatting to the Sold field values in the exported Excel document. Values below 700 units are highlighted in red, while values of 700 units or more are highlighted in green.
import { PivotView, ExcelExport, IDataSet } from '@syncfusion/ej2-pivotview';
import { ExcelQueryCellInfoEventArgs } from '@syncfusion/ej2-grids';
import { pivotData } from './datasource.ts';
import { Button } from '@syncfusion/ej2-buttons';
PivotView.Inject(ExcelExport);
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
dataSource: pivotData as IDataSet[],
expandAll: false,
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
},
width: '100%',
height: 300,
allowExcelExport: true,
gridSettings: {
excelQueryCellInfo: function (args: ExcelQueryCellInfoEventArgs): void {
if (args.cell && (args.cell as any).actualText === 'Sold') {
if ((args as any).value < 700) {
args.style = {
backColor: '#df3800',
fontName: 'Biome',
fontColor: '#FFFFFF',
borders: { color: '#8B1E00', lineStyle: 'thin' }
};
} else {
args.style = {
backColor: '#00A45A',
fontName: 'Nirmala UI',
fontColor: '#FFFFFF',
borders: { color: '#00663A', lineStyle: 'thin' }
};
}
}
}
}
});
pivotTableObj.appendTo('#PivotTable');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function (): void {
pivotTableObj.excelExport();
};<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-charts/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" />
<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'>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotTable'></div>
</div>
</body>
</html>Changing the pivot table style while exporting
The Excel export provides an option to change the colors of headers, captions, and records in a pivot table before exporting. To apply colors, define theme settings in excelExportProperties and pass it as a parameter to the excelExport method.
By default, the material theme is applied to the exported Excel document.
import { PivotView, IDataSet } from '@syncfusion/ej2-pivotview';
import { ExcelExportProperties } from '@syncfusion/ej2-grids';
import { Button } from '@syncfusion/ej2-buttons';
import { pivotData } from './datasource.ts';
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
dataSource: pivotData as IDataSet[],
expandAll: false,
drilledMembers: [{ name: 'Country', items: ['France'] }],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
filters: []
},
allowExcelExport: true,
height: 320
});
pivotTableObj.appendTo('#PivotTable');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function () {
let excelExportProperties: ExcelExportProperties = {
theme:
{
header: { fontName: 'Segoe UI', fontColor: '#666666' },
record: { fontName: 'Segoe UI', fontColor: '#666666' },
caption: { fontName: 'Segoe UI', fontColor: '#666666' }
}
};
pivotTableObj.excelExport(excelExportProperties);
};<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/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'>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotTable'></div>
<br />
<div id='PivotTable2'></div>
</div>
</body>
</html>Add header and footer while exporting
The Excel export provides an option to include header and footer content in the Excel document before exporting. To add a header and footer, define the header and footer properties in excelExportProperties and pass them as parameters to the excelExport method.
import { PivotView, IDataSet } from '@syncfusion/ej2-pivotview';
import { ExcelExportProperties } from '@syncfusion/ej2-grids';
import { Button } from '@syncfusion/ej2-buttons';
import { pivotData } from './datasource.ts';
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
dataSource: pivotData as IDataSet[],
expandAll: false,
drilledMembers: [{ name: 'Country', items: ['France'] }],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
filters: []
},
allowExcelExport: true,
height: 320
});
pivotTableObj.appendTo('#PivotTable');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function () {
let excelExportProperties: ExcelExportProperties = {
header: {
headerRows: 2,
rows: [
{ cells: [{ colSpan: 4, value: "Pivot Table", style: { fontColor: '#C67878', fontSize: 20, hAlign: 'Center', bold: true, underline: true } }] }
]
},
footer: {
footerRows: 4,
rows: [
{ cells: [{ colSpan: 4, value: "Thank you for your business!", style: { hAlign: 'Center', bold: true } }] },
{ cells: [{ colSpan: 4, value: "!Visit Again!", style: { hAlign: 'Center', bold: true } }] }
]
}
};
pivotTableObj.excelExport(excelExportProperties);
};<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/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'>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotTable'></div>
<br />
<div id='PivotTable2'></div>
</div>
</body>
</html>Changing the file name while exporting
This option provides flexibility to specify a custom file name for your exported Excel document, making it easier to organize and identify your exported data. The Excel export provides an option to change the file name of the document before exporting. To change the file name, define the fileName property in the excelExportProperties object and pass it as a parameter to the excelExport method.
import { PivotView, IDataSet } from '@syncfusion/ej2-pivotview';
import { ExcelExportProperties } from '@syncfusion/ej2-grids';
import { Button } from '@syncfusion/ej2-buttons';
import { pivotData } from './datasource.ts';
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
dataSource: pivotData as IDataSet[],
expandAll: false,
drilledMembers: [{ name: 'Country', items: ['France'] }],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
filters: []
},
allowExcelExport: true,
height: 320
});
pivotTableObj.appendTo('#PivotTable');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function () {
let excelExportProperties: ExcelExportProperties = {
fileName:'sample.xlsx'
};
pivotTableObj.excelExport(excelExportProperties);
};<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/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'>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotTable'></div>
<br />
<div id='PivotTable2'></div>
</div>
</body>
</html>Show spinner during export
When exporting data, displaying a spinner provides visual feedback to end users that the export process is in progress. To show a spinner, invoke the showWaitingPopup method in the button’s click event before calling the export method. After the export is complete, use the exportComplete event to trigger the hideWaitingPopup method, which will hide the spinner and indicate that the export has finished successfully.
import { PivotView, ExcelExport, IDataSet, VirtualScroll } from '@syncfusion/ej2-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
let names: string[] = ['TOM', 'Hawk', 'Jon', 'Chandler', 'Monica', 'Rachel', 'Phoebe', 'Gunther',
'Ross', 'Geller', 'Joey', 'Bing', 'Tribbiani', 'Janice', 'Bong', 'Perk', 'Green', 'Ken', 'Adams'];
let city: string[] = ['New York', 'Los Angeles', 'Chicago', 'Houston', 'Philadelphia', 'Phoenix', 'San Antonio', 'Austin',
'San Francisco', 'Columbus', 'Washington', 'Portland', 'Oklahoma', 'Las Vegas', 'Virginia', 'St. Louis', 'Birmingham']
let hours: number[] = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
let rating: number[] = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10];
let designation: string[] = ['Manager', 'Engineer 1', 'Engineer 2', 'Developer', 'Tester'];
let status: string[] = ['Completed', 'Open', 'In Progress', 'Review', 'Testing'];
let time: number = 0;
let data: Function = (count: number) => {
let result: Object[] = [];
for (let i: number = 0; i < count; i++) {
result.push({
TaskID: i + 1,
Engineer: names[Math.round(Math.random() * names.length)] || names[0],
City: names[Math.round(Math.random() * city.length)] || city[0],
Designation: designation[Math.round(Math.random() * designation.length)] || designation[0],
Estimation: hours[Math.round(Math.random() * hours.length)] || hours[0],
Rating: hours[Math.round(Math.random() * rating.length)] || rating[0],
Status: status[Math.round(Math.random() * status.length)] || status[0]
});
}
time = new Date().getTime();
return result;
};
PivotView.Inject(VirtualScroll, ExcelExport);
let pivotGridObj: PivotView = new PivotView({
dataSourceSettings: {
dataSource: data(10000) as IDataSet[],
expandAll: false,
rows: [{ name: 'TaskID' }, { name: 'Status' }],
columns: [{ name: 'Designation' }],
values: [{ name: 'Estimation' }, { name: 'Rating' }]
},
width: 1200,
height: 400,
enableVirtualization: true,
allowExcelExport: true,
exportComplete: function (): void {
pivotGridObj.hideWaitingPopup();
}
});
pivotGridObj.appendTo('#PivotTable');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function (): void {
pivotGridObj.showWaitingPopup();
setTimeout(() => {
pivotGridObj.excelExport();
});
};<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-charts/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" />
<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'>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotTable'></div>
</div>
</body>
</html>Export only the current page
By default, the Pivot Table exports all data records, which can result in larger file sizes when a large data source is assigned to the Pivot Table. To improve performance, export only the data records currently visible in the viewport by setting the exportAllPages property to false.
This option is applicable only when the virtualization or paging feature is enabled.
import { PivotView, VirtualScroll, IDataSet, ExcelExport } from '@syncfusion/ej2-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { pivotData } from './datasource.ts';
PivotView.Inject(VirtualScroll, ExcelExport);
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
dataSource: pivotData as IDataSet[],
expandAll: true,
drilledMembers: [{ name: 'Country', items: ['France'] }],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
rows: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
values: [{ name: 'Country' }, { name: 'Products' }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
filters: []
},
height: 320,
allowExcelExport: true,
exportAllPages: false,
enableVirtualization: true
});
pivotTableObj.appendTo('#PivotTable');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function () {
pivotTableObj.excelExport();
};<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/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'>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotTable'></div>
<br />
<div id='PivotTable2'></div>
</div>
</body>
</html>Events
ExcelQueryCellInfo
The excelQueryCellInfo event is triggered during the creation of each row and value cell while exporting data to Excel. This event offers options to change the content and style of individual cells in the exported Excel document, improving the flexibility and appearance of exported reports.
The event provides the following arguments:
-
value– Represents the value of the current cell in the exported Excel sheet. -
column– Provides details about the column to which the current cell belongs. -
data– Contains all data for the row that includes the current cell. -
style– Defines the style settings (such as font, color, borders) applied to the current cell.
import { PivotView, IDataSet, ExcelExport } from '@syncfusion/ej2-pivotview';
import { pivotData } from './datasource.ts';
import { Button } from '@syncfusion/ej2-buttons';
import { ExcelHeaderQueryCellInfoEventArgs, ExcelQueryCellInfoEventArgs } from '@syncfusion/ej2-grids';
PivotView.Inject(ExcelExport);
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
dataSource: pivotData as IDataSet[],
expandAll: false,
drilledMembers: [{ name: 'Country', items: ['France'] }],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
filters: []
},
gridSettings: {
excelQueryCellInfo: (args: ExcelQueryCellInfoEventArgs) => {
//triggers every time for value cell while exporting
}
},
allowExcelExport: true,
height: 350
});
pivotTableObj.appendTo('#PivotTable');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function () {
pivotTableObj.excelExport();
};<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-charts/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" />
<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>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotTable'></div>
</div>
</div>
</body>
</html>ExcelHeaderQueryCellInfo
The excelHeaderQueryCellInfo event provides the ability to modify header cell appearance and content during Excel export, ensuring exported documents match specific formatting requirements or business standards. This event triggers while processing each header cell during the Excel export operation. The event contains the following parameters:
-
cell– Contains the current cell information and properties. -
style– Contains the style properties that can be applied to the cell.
import { PivotView, IDataSet, ExcelExport } from '@syncfusion/ej2-pivotview';
import { pivotData } from './datasource.ts';
import { Button } from '@syncfusion/ej2-buttons';
import { ExcelHeaderQueryCellInfoEventArgs, ExcelQueryCellInfoEventArgs } from '@syncfusion/ej2-grids';
PivotView.Inject(ExcelExport);
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
dataSource: pivotData as IDataSet[],
expandAll: false,
drilledMembers: [{ name: 'Country', items: ['France'] }],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
filters: []
},
gridSettings: {
excelHeaderQueryCellInfo: (args: ExcelHeaderQueryCellInfoEventArgs) => {
//triggers every time for header cell while exporting
}
},
allowExcelExport: true,
height: 350
});
pivotTableObj.appendTo('#PivotTable');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function () {
pivotTableObj.excelExport();
};<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-charts/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" />
<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>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotTable'></div>
</div>
</div>
</body>
</html>ExportComplete
The exportComplete event triggers after the Pivot Table data exports to an Excel or CSV document. This event enables acquiring blob stream data for further processing and customization by setting the isBlob parameter to true when calling the excelExport method. The event includes the following parameters:
-
type– Specifies the current export format such as PDF, Excel, or CSV. -
promise– Contains the promise object that resolves with blob data for the exported file.
import { PivotView, VirtualScroll, IDataSet, ExcelExport } from '@syncfusion/ej2-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { pivotData } from './datasource.ts';
PivotView.Inject(ExcelExport, VirtualScroll);
let pivotTableObj: PivotView = new PivotView({
dataSourceSettings: {
dataSource: pivotData as IDataSet[],
expandAll: true,
drilledMembers: [{ name: 'Country', items: ['France'] }],
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
rows: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
values: [{ name: 'Country' }, { name: 'Products' }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
filters: []
},
height: 320,
allowExcelExport: true,
enableVirtualization: true,
exportComplete: (args: ExportCompleteEventArgs) => {
if (args.promise !== null) {
args.promise.then((e: { blobData: Blob }) => {
console.log(e.blobData);
});
}
}
});
pivotTableObj.appendTo('#PivotTable');
let exportBtn: Button = new Button({ isPrimary: true });
exportBtn.appendTo('#excel');
document.getElementById('excel').onclick = function () {
let excelExportProperties: ExcelExportProperties = {
fileName: 'excelexport.xlsx'
};
pivotTableObj.excelExport(excelExportProperties, false, null, true);
};<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Pivot Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Pivot Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/32.1.19/ej2-pivotview/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'>
<input id="excel" type="button" value="Export To Excel" name="Export To Excel" />
<div id='PivotTable'></div>
<br />
<div id='PivotTable2'></div>
</div>
</body>
</html>Limitation when exporting millions of records to Excel format
Understanding this limitation helps you choose the appropriate export format based on your data size requirements and ensures optimal performance for large datasets. By default, Microsoft Excel supports only 1,048,576 records in an Excel sheet. Therefore, it is not possible to export millions of records to Excel format. You can refer to the documentation link for more details on Microsoft Excel specifications and limits. For large datasets, it is recommended to export the data in CSV (Comma-Separated Values) or other formats that can handle large datasets more efficiently than Excel.