Excel export in Angular Pivot Table component
18 Dec 202524 minutes to read
The Pivot Table component supports exporting pivot data to Excel and CSV file formats. This feature enables data sharing and analysis in spreadsheet applications such as Microsoft Excel, Google Sheets, and others. To enable the export functionality, inject the ExcelExportService module into the Pivot Table using the @NgModule.providers section, 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 { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule, ExcelExportService } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings allowExcelExport='true' [width]=width></ejs-pivotview></div>
<div class="col-md-2"><button ej-button id='export'>Export</button></div>`,
providers: [ExcelExportService],
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
public button?: Button;
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
ngOnInit(): void {
this.dataSourceSettings = {
dataSource: Pivot_Data 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: [],
valueSortSettings: { headerText: 'FY 2015##Q1##Amount', headerDelimiter: '##', sortOrder: 'Descending' }
};
this.width = '100%';
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
this.pivotGridObj?.excelExport();
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));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 { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule, ExcelExportService } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { ExcelExportProperties } from '@syncfusion/ej2-grids';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings allowExcelExport='true' [width]=width></ejs-pivotview></div>
<div class="col-md-2"><button ej-button id='export'>Export</button></div>`,
providers: [ExcelExportService],
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
public button?: Button;
public excelExportProperties?: ExcelExportProperties;
public firstGridExport?: Promise<any>;
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
ngOnInit(): void {
this.dataSourceSettings = {
dataSource: Pivot_Data 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: [],
valueSortSettings: { headerText: 'FY 2015##Q1##Amount', headerDelimiter: '##', sortOrder: 'Descending' }
};
this.width = '100%';
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
this.pivotGridObj?.csvExport();
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));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 { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule, ExcelExportService, ExcelExportProperties } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { pivotData } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotTable1' height='350' [dataSourceSettings]=dataSourceSettings allowExcelExport='true' [width]=width></ejs-pivotview>
<br />
<ejs-pivotview #pivotview1 id='PivotTable2' height='350' [dataSourceSettings]=dataSourceSettings1 allowExcelExport='true' [width]=width></ejs-pivotview></div>
<div class="col-md-2"><button ej-button id='export'>Export</button></div>`,
providers: [ExcelExportService],
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
public dataSourceSettings1?: DataSourceSettingsModel;
public button?: Button;
public excelExportProperties?: ExcelExportProperties;
public firstGridExport?: Promise<any>;
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
@ViewChild('pivotview1')
public pivotGridObj1?: PivotView;
ngOnInit(): void {
this.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: []
};
this.dataSourceSettings1 = {
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: []
};
this.width = '100%';
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
this.excelExportProperties = {
multipleExport: { type: 'AppendToSheet', blankRows: 2 },
pivotTableIds: ['PivotTable1', 'PivotTable2']
};
this.pivotGridObj?.excelExport(this.excelExportProperties, true);
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));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 { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule, ExcelExportService, ExcelExportProperties } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { pivotData } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotTable1' height='350' [dataSourceSettings]=dataSourceSettings allowExcelExport='true' [width]=width></ejs-pivotview>
<br />
<ejs-pivotview #pivotview1 id='PivotTable2' height='350' [dataSourceSettings]=dataSourceSettings1 allowExcelExport='true' [width]=width></ejs-pivotview></div>
<div class="col-md-2"><button ej-button id='export'>Export</button></div>`,
providers: [ExcelExportService],
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
public dataSourceSettings1?: DataSourceSettingsModel;
public button?: Button;
public excelExportProperties?: ExcelExportProperties;
public firstGridExport?: Promise<any>;
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
@ViewChild('pivotview1')
public pivotGridObj1?: PivotView;
ngOnInit(): void {
this.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: []
};
this.dataSourceSettings1 = {
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: []
};
this.width = '100%';
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
this.excelExportProperties = {
multipleExport: { type: 'NewSheet' },
pivotTableIds: ['PivotTable1', 'PivotTable2']
};
this.pivotGridObj?.excelExport(this.excelExportProperties, true);
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));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 { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule, ExcelExportService, BeforeExportEventArgs } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { pivotData } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotTable' height='350' [dataSourceSettings]=dataSourceSettings
allowExcelExport='true' [width]=width (beforeExport)='beforeExport($event)'></ejs-pivotview>
</div>
<div class="col-md-2"><button ej-button id='export'>Export</button></div>`,
providers: [ExcelExportService],
})
export class AppComponent implements OnInit {
public width?: string;
public button?: Button;
public dataSourceSettings?: DataSourceSettingsModel;
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
beforeExport(args: BeforeExportEventArgs) {
// Store the drilledMembers here(row/column headers that has been expanded).
let member = this.pivotGridObj?.dataSourceSettings.drilledMembers;
// Then apply expandAll to the pivot table settings to get all the data including child members.
this.pivotGridObj?.setProperties(
{ dataSourceSettings: { expandAll: true, drilledMembers: [] } },
true
);
this.pivotGridObj?.engineModule.generateGridData(this.pivotGridObj.dataSourceSettings, true);
// Assign that retrieved data to the exporting here.
args.dataCollections = [(this.pivotGridObj as any).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.
this.pivotGridObj?.setProperties(
{ dataSourceSettings: { expandAll: false, drilledMembers: member } },
true
);
}
ngOnInit(): void {
this.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: []
};
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
this.pivotGridObj?.excelExport();
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));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 { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule, ExcelExportService, AggregateTypes, FieldListService, SummaryTypes } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { pivotData } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import { L10n } from '@syncfusion/ej2-base';
let SummaryType: string[] = [
'Sum',
'Count',
'DistinctCount',
'Avg',
'CustomAggregateType1',
'CustomAggregateType2',
];
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotTable' height='350' [dataSourceSettings]=dataSourceSettings
allowExcelExport='true' [width]=width (aggregateCellInfo)='aggregateCellInfo($event)'
showFieldList='true' (dataBound)="dataBound()">
</ejs-pivotview>
</div>
<div class="col-md-2"><button ej-button id='export'>Export</button></div>`,
providers: [ExcelExportService, FieldListService],
})
export class AppComponent implements OnInit {
public width?: string;
public button?: Button;
public dataSourceSettings?: DataSourceSettingsModel;
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
aggregateCellInfo(args: any) {
if (args.aggregateType === 'CustomAggregateType1') {
args.value = args.value * 100;
}
if (args.aggregateType === 'CustomAggregateType2') {
args.value = args.value / 100;
}
}
dataBound(): void {
(this.pivotGridObj as any).getAllSummaryType = function () {
return SummaryType as AggregateTypes[];
};
(this.pivotGridObj as any).pivotFieldListModule.aggregateTypes = SummaryType as AggregateTypes[];
(this.pivotGridObj as any).pivotFieldListModule.getAllSummaryType = function () {
return SummaryType as AggregateTypes[];
};
}
ngOnInit(): void {
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',
}
}
});
this.dataSourceSettings = {
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' }],
};
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
this.pivotGridObj?.excelExport();
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));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 { PivotViewAllModule, PivotFieldListAllModule, ExcelExportService, FieldListService } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { Group_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotTable' height='350' [dataSourceSettings]=dataSourceSettings
allowExcelExport='true' [width]=width showFieldList='true'>
</ejs-pivotview>
</div>
<div class="col-md-2"><button ej-button id='export'>Export</button></div>`,
providers: [ExcelExportService, FieldListService],
})
export class AppComponent implements OnInit {
public width?: string;
public button?: Button;
public dataSourceSettings?: DataSourceSettingsModel;
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
ngOnInit(): void {
this.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' }],
};
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
this.pivotGridObj?.excelExport();
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));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 { PivotViewAllModule, PivotFieldListAllModule, ExcelExportService, IAxisSet } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { pivotData } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotTable' height='350' [dataSourceSettings]=dataSourceSettings
allowExcelExport='true' [width]=width (beforeExport)='beforeExport($event)'></ejs-pivotview>
</div>
<div class="col-md-2"><button ej-button id='export'>Export</button></div>`,
providers: [ExcelExportService],
})
export class AppComponent implements OnInit {
public width?: string;
public button?: Button;
public dataSourceSettings?: DataSourceSettingsModel;
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
beforeExport(args: any) {
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');
});
}
}
}
ngOnInit(): void {
this.dataSourceSettings = {
expandAll: false,
dataSource: pivotData as IDataSet[],
columns: [{ name: 'Year' }, { name: 'Quarter' }],
values: [{ name: 'Sold' }],
rows: [{ name: 'Country' }, { name: 'Products' }]
};
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
this.pivotGridObj?.excelExport();
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));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 { PivotViewAllModule, PivotFieldListAllModule, ExcelExportService, ExcelExportProperties } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { pivotData } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import { GridSettings } from '@syncfusion/ej2-pivotview/src/pivotview/model/gridsettings';
import { Observable } from 'rxjs';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotTable' height='350' [dataSourceSettings]=dataSourceSettings
allowExcelExport='true' [width]=width [gridSettings]=gridSettings></ejs-pivotview>
</div>
<div class="col-md-2"><button ej-button id='export'>Export</button></div>`,
providers: [ExcelExportService],
})
export class AppComponent implements OnInit {
public width?: string;
public button?: Button;
public dataSourceSettings?: DataSourceSettingsModel;
public gridSettings?: GridSettings;
public observable = new Observable();
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
ngOnInit(): void {
this.gridSettings = {
columnRender: this.observable.subscribe((args: any) => {
for (let i = 1; i < args.columns.length; i++) {
if (args.stackedColumns[i].customAttributes &&
args.stackedColumns[i].customAttributes.cell.valueSort.levelName === 'FY 2016.Units Sold') {
args.stackedColumns[i].visible = false;
}
}
}) as any
} as GridSettings;
this.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' }]
};
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
let excelExportProperties: ExcelExportProperties = {
includeHiddenColumn: false
};
this.pivotGridObj?.excelExport(excelExportProperties);
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));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 { PivotViewAllModule, PivotFieldListAllModule, ExcelExportService } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { pivotData } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import { DropDownListModule } from '@syncfusion/ej2-angular-dropdowns';
import { GridSettings } from '@syncfusion/ej2-pivotview/src/pivotview/model/gridsettings';
import { Observable } from 'rxjs';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule,
DropDownListModule
],
standalone: true,
selector: 'app-container',
template: `
<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotTable' height='350' [dataSourceSettings]=dataSourceSettings
allowExcelExport='true' [width]=width [gridSettings]=gridSettings></ejs-pivotview>
</div>
<div class="col-md-2">
<div id="export-btn">
<button ej-button id='export'>Export</button>
</div>
<div id="dropdownlist" style="display: flex; align-items: center; margin-top: 25px;">
<label>Select a degree:</label>
<ejs-dropdownlist id='ddlelement' #ddlelement placeholder='Select a degree' [dataSource]='degree'>
</ejs-dropdownlist>
</div>
</div>`,
providers: [ExcelExportService],
})
export class AppComponent implements OnInit {
public width?: string;
public button?: Button;
public dataSourceSettings?: DataSourceSettingsModel;
public gridSettings?: GridSettings;
public observable = new Observable();
public degree?: number[];
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
@ViewChild('ddlelement')
public dropDownListObject: any;
ngOnInit(): void {
this.degree = [90, 135, 180, 270];
this.gridSettings = {
excelHeaderQueryCellInfo: this.observable.subscribe((args: any) => {
args.style = { rotation: this.dropDownListObject.value as number };
}) as any,
excelQueryCellInfo: this.observable.subscribe((args: any) => {
args.style = { rotation: this.dropDownListObject.value as number };
}) as any,
} as GridSettings;
this.dataSourceSettings = {
expandAll: false,
dataSource: pivotData as IDataSet[],
columns: [{ name: 'Year' }],
values: [{ name: 'Sold' }],
rows: [{ name: 'Country' }, { name: 'Products' }]
};
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
this.pivotGridObj?.excelExport();
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));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 { PivotViewAllModule, PivotFieldListAllModule, ExcelExportService } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { pivotData } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import { GridSettings } from '@syncfusion/ej2-pivotview/src/pivotview/model/gridsettings';
import { Observable } from 'rxjs';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotTable' height='350' [dataSourceSettings]=dataSourceSettings
allowExcelExport='true' [width]=width [gridSettings]=gridSettings></ejs-pivotview>
</div>
<div class="col-md-2"><button ej-button id='export'>Export</button></div>`,
providers: [ExcelExportService],
})
export class AppComponent implements OnInit {
public width?: string;
public button?: Button;
public dataSourceSettings?: DataSourceSettingsModel;
public gridSettings?: GridSettings;
public observable = new Observable();
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
ngOnInit(): void {
this.gridSettings = {
excelQueryCellInfo: this.observable.subscribe((args: any) => {
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' }
};
}
}
}) as any,
} as GridSettings;
this.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' }]
};
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
this.pivotGridObj?.excelExport();
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));Changing the pivot table style while exporting
The Excel export provides an option to change colors for headers, caption and records in pivot table before exporting. To apply colors, define theme settings in excelExportProperties object and pass it as a parameter to the excelExport method.
By default, material theme is applied to exported Excel document.
import { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule, ExcelExportService } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { ExcelExportProperties } from '@syncfusion/ej2-grids';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings allowExcelExport='true' [width]=width></ejs-pivotview></div>
<div class="col-md-2"><button ej-button id='export'>Export</button></div>`,
providers: [ExcelExportService],
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
public button?: Button;
public excelExportProperties?: ExcelExportProperties;
public firstGridExport?: Promise<any>;
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
ngOnInit(): void {
this.dataSourceSettings = {
dataSource: Pivot_Data 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: [],
valueSortSettings: { headerText: 'FY 2015##Q1##Amount', headerDelimiter: '##', sortOrder: 'Descending' }
};
this.width = '100%';
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
this.excelExportProperties = {
theme:
{
header: { fontName: 'Segoe UI', fontColor: '#666666' },
record: { fontName: 'Segoe UI', fontColor: '#666666' },
caption: { fontName: 'Segoe UI', fontColor: '#666666' }
}
};
this.pivotGridObj?.excelExport(this.excelExportProperties);
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));Add header and footer while exporting
The Excel export provides an option to include header and footer content for the excel document before exporting. To add header and footer, define header and footer properties in excelExportProperties object and pass it as a parameter to the excelExport method.
import { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule, ExcelExportService } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { ExcelExportProperties } from '@syncfusion/ej2-grids';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings allowExcelExport='true' [width]=width></ejs-pivotview></div>
<div class="col-md-2"><button ej-button id='export'>Export</button></div>`,
providers: [ExcelExportService],
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
public button?: Button;
public excelExportProperties?: ExcelExportProperties;
public firstGridExport?: Promise<any>;
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
ngOnInit(): void {
this.dataSourceSettings = {
dataSource: Pivot_Data 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: [],
valueSortSettings: { headerText: 'FY 2015##Q1##Amount', headerDelimiter: '##', sortOrder: 'Descending' }
};
this.width = '100%';
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
this.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 } }] }
]
}
};
this.pivotGridObj?.excelExport(this.excelExportProperties);
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));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 files. 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 { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule, ExcelExportService } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { ExcelExportProperties } from '@syncfusion/ej2-grids';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings allowExcelExport='true' [width]=width></ejs-pivotview></div>
<div class="col-md-2"><button ej-button id='export'>Export</button></div>`,
providers: [ExcelExportService],
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
public button?: Button;
public excelExportProperties?: ExcelExportProperties;
public firstGridExport?: Promise<any>;
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
ngOnInit(): void {
this.dataSourceSettings = {
dataSource: Pivot_Data 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: [],
valueSortSettings: { headerText: 'FY 2015##Q1##Amount', headerDelimiter: '##', sortOrder: 'Descending' }
};
this.width = '100%';
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
this.excelExportProperties = {
fileName: 'sample.xlsx'
};
this.pivotGridObj?.excelExport(this.excelExportProperties);
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));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 { PivotViewAllModule, PivotFieldListAllModule, ExcelExportService, VirtualScrollService, ExportCompleteEventArgs } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotView' height='350' enableVirtualization='true' [dataSourceSettings]=dataSourceSettings
allowExcelExport='true' [width]=width (exportComplete)='exportComplete($event)'></ejs-pivotview></div>
<div class="col-md-2"><button ej-button id='export'>Export</button></div>`,
providers: [ExcelExportService, VirtualScrollService],
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
public button?: Button;
public customername: string[] = ['TOM', 'Hawk', 'Jon', 'Chandler', 'Monica', 'Rachel', 'Phoebe', 'Gunther',
'Ross', 'Geller', 'Joey', 'Bing', 'Tribbiani', 'Janice', 'Bong', 'Perk', 'Green', 'Ken', 'Adams'];
public 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'];
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
exportComplete(args: ExportCompleteEventArgs | any): void {
this.pivotGridObj?.hideWaitingPopup();
}
data(count: number): Object[] {
let result: Object[] = [];
let dt: number = 0;
for (let i: number = 1; i < (count + 1); i++) {
dt++;
let round: string;
let toString: string = i.toString();
if (toString.length === 1) {
round = '0000' + (i);
}
else if (toString.length === 2) {
round = '000' + i;
}
else if (toString.length === 3) {
round = '00' + i;
} else if (toString.length === 4) {
round = '0' + i;
} else {
round = toString;
}
result.push({
ProductID: 'PRO-' + round,
City: this.city[Math.round(Math.random() * this.city.length)] || this.city[0],
Year: "FY " + (dt + 2023),
CustomerName: this.customername[Math.round(Math.random() * this.customername.length)] || this.customername[0],
Price: Math.round(Math.random() * 5000) + 5000,
Sold: Math.round(Math.random() * 80) + 10,
});
if (dt / 2 == 1) {
dt = 0;
}
}
return result;
};
ngOnInit(): void {
this.dataSourceSettings = {
dataSource: this.data(10000) as IDataSet[],
enableSorting: false,
expandAll: true,
formatSettings: [{ name: 'Price', format: 'C0' }],
rows: [{ name: 'ProductID' }],
columns: [{ name: 'Year' }],
values: [{ name: 'Price', caption: 'Unit Price' }, { name: 'Sold', caption: 'Unit Sold' }]
};
this.width = '100%';
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
this.pivotGridObj?.showWaitingPopup();
setTimeout(() => {
this.pivotGridObj?.excelExport();
});
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));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 { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView, VirtualScrollService } from '@syncfusion/ej2-angular-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
providers: [VirtualScrollService],
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings allowExcelExport='true' exportAllPages='false' enableVirtualization='true' [width]=width></ejs-pivotview></div>
<div class="col-md-2"><button ej-button id='export'>Export</button></div>`
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
public button?: Button;
@ViewChild('pivotview', {static: false})
public pivotGridObj?: PivotView;
ngOnInit(): void {
this.dataSourceSettings = {
dataSource: Pivot_Data as IDataSet[],
expandAll: true,
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: []
};
this.width = '100%';
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
this.pivotGridObj?.excelExport();
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));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 { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule, ExcelExportService } from '@syncfusion/ej2-angular-pivotview'
import { Button } from '@syncfusion/ej2-buttons';
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { GridSettings } from '@syncfusion/ej2-pivotview/src/pivotview/model/gridsettings';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import { Observable } from 'rxjs';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
// specifies the template string for the pivot table component
template: `<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]='dataSourceSettings'
[gridSettings]='gridSettings' [width]=width allowExcelExport='true'></ejs-pivotview><div class="col-md-2"><button ej-button id='export'>Export</button></div>`,
providers: [ExcelExportService],
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
public gridSettings?: GridSettings;
public columnGrandTotalIndex?: number;
public rowGrandTotalIndex?: number;
public button?: Button;
public observable = new Observable();
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
ngOnInit(): void {
this.width = '100%';
this.dataSourceSettings = {
dataSource: Pivot_Data 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: []
};
this.gridSettings = {
columnWidth: 140,
excelQueryCellInfo: this.observable.subscribe((args: any) => {
// triggers every time for header cell while rendering
}) as any,
} as GridSettings;
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
this.pivotGridObj?.excelExport();
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));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 { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule, ExcelExportService } from '@syncfusion/ej2-angular-pivotview'
import { Button } from '@syncfusion/ej2-buttons';
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView } from '@syncfusion/ej2-angular-pivotview';
import { GridSettings } from '@syncfusion/ej2-pivotview/src/pivotview/model/gridsettings';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
import { Observable } from 'rxjs';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
// specifies the template string for the pivot table component
template: `<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]='dataSourceSettings'
[gridSettings]='gridSettings' [width]=width allowExcelExport='true'></ejs-pivotview><div class="col-md-2"><button ej-button id='export'>Export</button></div>`,
providers: [ExcelExportService],
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
public gridSettings?: GridSettings;
public columnGrandTotalIndex?: number;
public rowGrandTotalIndex?: number;
public button?: Button;
public observable = new Observable();
@ViewChild('pivotview', { static: false })
public pivotGridObj?: PivotView;
ngOnInit(): void {
this.width = '100%';
this.dataSourceSettings = {
dataSource: Pivot_Data 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: []
};
this.gridSettings = {
columnWidth: 140,
excelHeaderQueryCellInfo: this.observable.subscribe((args: any) => {
// triggers every time for header cell while rendering
}) as any,
} as GridSettings;
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
this.pivotGridObj?.excelExport();
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));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 { NgModule } from '@angular/core'
import { BrowserModule } from '@angular/platform-browser'
import { PivotViewAllModule, PivotFieldListAllModule } from '@syncfusion/ej2-angular-pivotview'
import { Component, OnInit, ViewChild } from '@angular/core';
import { IDataSet, PivotView, VirtualScrollService, ExportCompleteEventArgs, ExcelExportService } from '@syncfusion/ej2-angular-pivotview';
import { Button } from '@syncfusion/ej2-buttons';
import { ExcelExportProperties } from '@syncfusion/ej2-grids';
import { Pivot_Data } from './datasource';
import { DataSourceSettingsModel } from '@syncfusion/ej2-pivotview/src/model/datasourcesettings-model';
@Component({
imports: [
PivotViewAllModule,
PivotFieldListAllModule
],
standalone: true,
selector: 'app-container',
providers: [VirtualScrollService, ExcelExportService],
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings allowExcelExport='true' enableVirtualization='true' (exportComplete)='exportComplete($event)' [width]=width></ejs-pivotview></div>
<div class="col-md-2"><button ej-button id='export'>Export</button></div>`
})
export class AppComponent implements OnInit {
public width?: string;
public dataSourceSettings?: DataSourceSettingsModel;
public button?: Button;
@ViewChild('pivotview', {static: false})
public pivotGridObj?: PivotView;
exportComplete(args: ExportCompleteEventArgs | any): void {
args.promise.then((e: { blobData: Blob }) => {
console.log(e.blobData);
});
}
ngOnInit(): void {
this.dataSourceSettings = {
dataSource: Pivot_Data as IDataSet[],
expandAll: true,
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: []
};
this.width = '100%';
this.button = new Button({ isPrimary: true });
this.button.appendTo('#export');
this.button.element.onclick = (): void => {
let excelExportProperties: ExcelExportProperties = { };
this.pivotGridObj?.excelExport(excelExportProperties, false, null, true);
};
}
}import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));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.
