Excel export in Angular Pivot Table component
8 Sep 202524 minutes to read
The Excel export feature allows you to export Pivot Table data as an Excel document for offline analysis and reporting. To enable Excel export functionality in the pivot table, set the allowExcelExport property to true. Once enabled, use the excelExport
method to perform the Excel export operation.
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 } 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>`
})
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));
Multiple pivot table exporting
Excel exporting supports exporting multiple pivot tables into a single Excel file, allowing you to combine and organize data from different pivot tables for a unified view.
Same WorkSheet
The Excel export provides support to export multiple pivot tables in the same sheet. To export in the same sheet, define multipleExport.type
as AppendToSheet
in excelExportProperties
object. It has an option to provide blank rows between pivot tables and these blank row(s) count can be defined using the multipleExport.blankRows
property.
By default,
multipleExport.blankRows
value is 5 between pivot tables within the same sheet.
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 } 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>
<ejs-pivotview #pivotview1 id='PivotView1' height='350' [dataSourceSettings]=dataSourceSettings1 allowExcelExport='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 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: 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.dataSourceSettings1 = {
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 = {
multipleExport: { type: 'AppendToSheet', blankRows: 2 }
};
this.firstGridExport = this.pivotGridObj?.grid.excelExport(this.excelExportProperties, true);
this.firstGridExport?.then((fData: any) => {
this.pivotGridObj1!.excelExport(this.excelExportProperties, false, fData);
});
};
}
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));
New WorkSheet
Excel export provides support to export multiple pivot tables into new sheets. To export in new sheets, define multipleExport.type
as NewSheet
in excelExportProperties
object.
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 } 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>
<ejs-pivotview #pivotview1 id='PivotView1' height='350' [dataSourceSettings]=dataSourceSettings1 allowExcelExport='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 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: 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.dataSourceSettings1 = {
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 = {
multipleExport: { type: 'NewSheet' }
};
this.firstGridExport = this.pivotGridObj?.grid.excelExport(this.excelExportProperties, true);
this.firstGridExport?.then((fData: any) => {
(this.pivotGridObj1 as PivotView).excelExport(this.excelExportProperties, false, fData);
});
};
}
}
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 } 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>`
})
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 } 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>`
})
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 } 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>`
})
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));
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.
CSV Export
The CSV export option allows you to export Pivot Table data as a plain text CSV file, making it easy to use the data with other spreadsheet or data analysis applications. To export the Pivot Table as a CSV file, ensure that the allowExcelExport
property is set to true. Then, use the csvExport
method to perform the CSV export operation.
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 } 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>`
})
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));
Virtual Scroll Data
Exporting virtual scroll data lets you generate a complete Excel or CSV document containing all Pivot Table data without performance issues, even with large datasets. This approach uses PivotEngine export to handle extensive data efficiently. To enable PivotEngine export in the Pivot Table, set the allowExcelExport
property and use either the excelExport
or csvExport
method.
To use PivotEngine export, inject the
ExcelExportService
module in the Pivot Table.
PivotEngine export will be performed when virtual scrolling is enabled by default.
Virtual Scroll Data Excel Export
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, ExcelExportService, 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: [ExcelExportService, VirtualScrollService],
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings allowExcelExport='true' 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: 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));
Virtual Scroll Data CSV Export
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, ExcelExportService, 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: [ExcelExportService, VirtualScrollService],
template: `<div class="col-md-8">
<ejs-pivotview #pivotview id='PivotView' height='350' [dataSourceSettings]=dataSourceSettings allowExcelExport='true' 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: 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 => {
let excelExportProperties = {
fileName: 'csvexport.csv',
};
this.pivotGridObj?.csvExport(excelExportProperties);
};
}
}
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component';
import 'zone.js';
bootstrapApplication(AppComponent).catch((err) => console.error(err));
Export all pages
This option gives flexibility to export either the entire dataset rendered by the pivot table (all pages) or just the data currently visible in the viewport. To export the entire pivot table data, ensure the exportAllPages
property is set to true. Set it to false to export only the visible records. This setting applies to both Excel and CSV exports.
By default, the pivot engine export will be performed while virtual scrolling 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 } 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>`
})
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 } 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>`
})
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));