Excel export in React Pivotview component
8 Aug 202324 minutes to read
The Excel export allows Pivot Table data to be exported as Excel document. To enable Excel export in the pivot table, set the allowExcelExport
property in PivotView
to true. Once the API is set, user needs to call the excelExport
method for exporting on external button click.
The pivot table component can be exported to Excel format using options available in the toolbar. For more details
refer
here.
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData,
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick() {
pivotObj.excelExport();
}
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { IDataOptions, IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
}
let pivotObj: PivotViewComponent;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick(): void {
pivotObj.excelExport();
}
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
Multiple pivot table exporting
The Excel export provides an option to export multiple pivot table data in the same Excel file.
Same WorkSheet
The Excel export provides support to export multiple pivot tables in same sheet. To export in same sheet, define multipleExport.type
as AppendToSheet
in excelExportProperties
. It has an option to provide blank rows between pivot tables and these blank row(s) count can be defined using themultipleExport.blankRows
property.
By default,
multipleExport.blankRows
value is 5 between pivot tables within the same sheet.
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData,
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj;
let dataSourceSettings1 = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData,
expandAll: true,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj1;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className="col-md-9"> <PivotViewComponent ref={d => pivotObj1 = d} id='PivotView1' height={350} dataSourceSettings={dataSourceSettings1} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick() {
let excelExportProperties = {
multipleExport: { type: 'AppendToSheet', blankRows: 2 }
};
let firstGridExport = pivotObj.grid.excelExport(excelExportProperties, true);
firstGridExport.then((fData) => {
pivotObj1.excelExport(excelExportProperties, false, fData);
});
}
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { IDataOptions, IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
import { ExcelExportProperties } from '@syncfusion/ej2-grids';
function App() {
let dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
}
let pivotObj: PivotViewComponent;
let dataSourceSettings1: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: true,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
}
let pivotObj1: PivotViewComponent;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className="col-md-9"> <PivotViewComponent ref={d => pivotObj1 = d} id='PivotView1' height={350} dataSourceSettings={dataSourceSettings1} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick(): void {
let excelExportProperties: ExcelExportProperties = {
multipleExport: { type: 'AppendToSheet', blankRows: 2 }
};
let firstGridExport: Promise<any> = pivotObj.grid.excelExport(excelExportProperties, true);
firstGridExport.then((fData: any) => {
pivotObj1.excelExport(excelExportProperties, false, fData);
});
}
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
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
.
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData,
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj;
let dataSourceSettings1 = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData,
expandAll: true,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj1;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className="col-md-9"> <PivotViewComponent ref={d => pivotObj1 = d} id='PivotView1' height={350} dataSourceSettings={dataSourceSettings1} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick() {
let excelExportProperties = {
multipleExport: { type: 'NewSheet' }
};
let firstGridExport = pivotObj.grid.excelExport(excelExportProperties, true);
firstGridExport.then((fData) => {
pivotObj1.excelExport(excelExportProperties, false, fData);
});
}
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { IDataOptions, IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
import { ExcelExportProperties } from '@syncfusion/ej2-grids';
function App() {
let dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
}
let pivotObj: PivotViewComponent;
let dataSourceSettings1: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: true,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
}
let pivotObj1: PivotViewComponent;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className="col-md-9"> <PivotViewComponent ref={d => pivotObj1 = d} id='PivotView1' height={350} dataSourceSettings={dataSourceSettings1} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick(): void {
let excelExportProperties: ExcelExportProperties = {
multipleExport: { type: 'NewSheet' }
};
let firstGridExport: Promise<any> = pivotObj.grid.excelExport(excelExportProperties, true);
firstGridExport.then((fData: any) => {
pivotObj1.excelExport(excelExportProperties, false, fData);
});
}
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
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. In-order to apply colors, define theme settings in excelExportProperties object and pass it as a parameter to the excelExport
method.
By default, material theme will be applied to the pivot table during Excel exporting.
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData,
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick() {
let excelExportProperties = {
theme: {
header: { fontName: 'Segoe UI', fontColor: '#666666' },
record: { fontName: 'Segoe UI', fontColor: '#666666' },
caption: { fontName: 'Segoe UI', fontColor: '#666666' }
}
};
pivotObj.grid.excelExport(excelExportProperties);
}
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { IDataOptions, IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
import { ExcelExportProperties } from '@syncfusion/ej2-grids';
function App() {
let dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
}
let pivotObj: PivotViewComponent;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick(): void {
let excelExportProperties: ExcelExportProperties = {
theme:
{
header: { fontName: 'Segoe UI', fontColor: '#666666' },
record: { fontName: 'Segoe UI', fontColor: '#666666' },
caption: { fontName: 'Segoe UI', fontColor: '#666666' }
}
};
pivotObj.grid.excelExport(excelExportProperties);
}
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
Add header and footer while exporting
The Excel export provides an option to include header and footer content for the excel document before exporting. In-order to add header and footer, define header and footer properties in excelExportProperties object and pass it as a parameter to the excelExport
method.
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData,
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick() {
let 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 } }] }
]
}
};
pivotObj.grid.excelExport(excelExportProperties);
}
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { IDataOptions, IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
import { ExcelExportProperties } from '@syncfusion/ej2-grids';
function App() {
let dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
}
let pivotObj: PivotViewComponent;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick(): void {
let excelExportProperties: ExcelExportProperties = {
header: {
headerRows: 2,
rows: [
{ cells: [{ colSpan: 4, value: "Pivot Table", style: { fontColor: '#C67878', fontSize: 20, hAlign: 'Center', bold: true, underline: true } }] }
]
},
footer: {
footerRows: 4,
rows: [
{ cells: [{ colSpan: 4, value: "Thank you for your business!", style: { hAlign: 'Center', bold: true } }] },
{ cells: [{ colSpan: 4, value: "!Visit Again!", style: { hAlign: 'Center', bold: true } }] }
]
}
};
pivotObj.grid.excelExport(excelExportProperties);
}
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
Changing the file name while exporting
The Excel export provides an option to change file name of the document before exporting. In-order to change the file name, define fileName property in excelExportProperties object and pass it as a parameter to the excelExport
method.
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData,
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick() {
let excelExportProperties = {
fileName: 'sample.xlsx'
};
pivotObj.grid.excelExport(excelExportProperties);
}
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { IDataOptions, IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
import { ExcelExportProperties } from '@syncfusion/ej2-grids';
function App() {
let dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
}
let pivotObj: PivotViewComponent;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick(): void {
let excelExportProperties: ExcelExportProperties = {
fileName: 'sample.xlsx'
};
pivotObj.grid.excelExport(excelExportProperties);
}
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
CSV Export
The Excel export allows pivot table data to be exported in CSV file format as well. To enable CSV export in the pivot table, set the allowExcelExport
property in PivotView
as true. Once the API is set, user needs to call the csvExport
method for exporting on external button click.
The pivot table component can be exported to CSV format using options available in the toolbar. For more details
refer
here.
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData,
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick() {
pivotObj.csvExport();
}
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { IDataOptions, IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
}
let pivotObj: PivotViewComponent;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick(): void {
pivotObj.csvExport();
}
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
Virtual Scroll Data
You can export the pivot table virtual scroll data as Excel/CSV document by using PivotEngine export without any performance degradation. To enable PivotEngine export in the pivot table, set the allowExcelExport
as true. You need to use the exportToExcel
method for PivotEngine export.
To use PivotEngine export, You need to inject the
ExcelExport
module in pivot table.
PivotEngine export will be performed while enabling virtual scrolling by default.
Virtual Scroll Data Excel Export
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { PivotViewComponent, Inject, ExcelExport } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData,
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}><Inject services={[ExcelExport]}/></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick() {
pivotObj.excelExportModule.exportToExcel('Excel');
}
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { IDataOptions, IDataSet, PivotViewComponent, Inject, ExcelExport } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
}
let pivotObj: PivotViewComponent;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}><Inject services={[ExcelExport]} /></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick(): void {
pivotObj.excelExportModule.exportToExcel('Excel');
}
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
Virtual Scroll Data CSV Export
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { PivotViewComponent, Inject, ExcelExport } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData,
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}><Inject services={[ExcelExport]}/></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick() {
let excelExportProperties = {
fileName: 'csvexport.csv',
};
pivotObj.csvExport(excelExportProperties);
}
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { IDataOptions, IDataSet, PivotViewComponent, Inject, ExcelExport } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: false,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
}
let pivotObj: PivotViewComponent;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} dataSourceSettings={dataSourceSettings} allowExcelExport={true}><Inject services={[ExcelExport]} /></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick(): void {
let excelExportProperties = {
fileName: 'csvexport.csv',
};
pivotObj.csvExport(excelExportProperties);
}
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
Export all pages
The pivot engine exports the entire virtual data of the pivot table (i.e. the data that contains all of the records used to render the complete pivot table) as an Excel/CSV document. To export just the current viewport of the pivot table, set the exportAllPages
property to false. To use the pivot engine export, add the ExcelExport
module into the pivot table.
By default, the pivot engine export will be performed while virtual scrolling is enabled.
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { PivotViewComponent, VirtualScroll, Inject } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData,
expandAll: true,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
values: [{ name: 'Country' }, { name: 'Products' }],
rows: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} exportAllPages='false' allowExcelExport={true} enableVirtualization={true} dataSourceSettings={dataSourceSettings}><Inject services={[VirtualScroll]}/></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick() {
pivotObj.excelExport();
}
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { IDataOptions, IDataSet, PivotViewComponent, VirtualScroll, Inject } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: true,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
values: [{ name: 'Country' }, { name: 'Products' }],
rows: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
}
let pivotObj: PivotViewComponent;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} exportAllPages='false' allowExcelExport={true} enableVirtualization={true} dataSourceSettings={dataSourceSettings}><Inject services={[VirtualScroll]}/></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick(): void {
pivotObj.excelExport();
}
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
Events
ExcelQueryCellInfo
The event excelQueryCellInfo
triggers while framing each row and value cell during Excel export. It allows the user to customize the cell value, style etc. of the current cell. It has the following parameters:
-
value
- It holds the cell value. -
column
- It holds column information for the current cell. -
data
- It holds the entire row data across the current cell. -
style
- It holds the style properties for the cell.
import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let gridSettings = {
columnWidth: 140,
excelQueryCellInfo: excelQueryCellInfo.bind(this)
};
let dataSourceSettings = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData,
expandAll: false,
filters: [],
drilledMembers: [{ name: 'Country', items: ['France'] }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj;
function excelQueryCellInfo(args) {
//triggers every time for header cell while rendering
}
return <div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} gridSettings={gridSettings} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>;
function btnClick() {
pivotObj.excelExport();
}
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { IDataOptions, IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
import { GridSettings } from '@syncfusion/ej2-pivotview/src/pivotview/model/gridsettings';
function App() {
let gridSettings: GridSettings = {
columnWidth: 140,
excelQueryCellInfo: excelQueryCellInfo.bind(this)
} as GridSettings;
let dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: false,
filters: [],
drilledMembers: [{ name: 'Country', items: ['France'] }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
}
let pivotObj: PivotViewComponent;
function excelQueryCellInfo(args: ExcelQueryCellInfoEventArgs): void {
//triggers every time for header cell while rendering
}
return <div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} gridSettings={gridSettings} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>
function btnClick(): void {
pivotObj.excelExport();
}
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
ExcelHeaderQueryCellInfo
The event excelHeaderQueryCellInfo
triggers on framing each header cell during Excel export. It allows the user to customize the cell value, style etc. of the current cell. It has the following parameters:
-
cell
- It holds the current cell information. -
style
- It holds the style properties for the cell.
import { PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let gridSettings = {
columnWidth: 140,
excelHeaderQueryCellInfo: excelHeaderQueryCellInfo.bind(this)
};
let dataSourceSettings = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData,
expandAll: false,
filters: [],
drilledMembers: [{ name: 'Country', items: ['France'] }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj;
function excelHeaderQueryCellInfo(args) {
//triggers every time for header cell while rendering
}
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} gridSettings={gridSettings} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick() {
pivotObj.excelExport();
}
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { IDataOptions, IDataSet, PivotViewComponent } from '@syncfusion/ej2-react-pivotview';
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
import { GridSettings } from '@syncfusion/ej2-pivotview/src/pivotview/model/gridsettings';
function App() {
let gridSettings: GridSettings = {
columnWidth: 140,
excelHeaderQueryCellInfo: excelHeaderQueryCellInfo.bind(this)
} as GridSettings;
let dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: false,
filters: [],
drilledMembers: [{ name: 'Country', items: ['France'] }],
formatSettings: [{ name: 'Amount', format: 'C0' }],
rows: [{ name: 'Country' }, { name: 'Products' }],
values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
}
let pivotObj: PivotViewComponent;
function excelHeaderQueryCellInfo(args: ExcelHeaderQueryCellInfoEventArgs): void {
//triggers every time for header cell while rendering
}
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} gridSettings={gridSettings} dataSourceSettings={dataSourceSettings} allowExcelExport={true}></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function btnClick(): void {
pivotObj.excelExport();
}
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
ExportComplete
The event exportComplete
is triggered after the pivot table data has been exported to a Excel/CSV document. You can use this event to acquire blob stream data for further customization and processing at your end by passing the isBlob
parameter as true when using the excelExport
method. It has the following parameters:
-
type
- It holds the current export type such as PDF, Excel, and CSV. -
promise
- It holds the promise object for blob data.
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { PivotViewComponent, VirtualScroll, Inject, ExcelExport } from '@syncfusion/ej2-react-pivotview';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData,
expandAll: true,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
values: [{ name: 'Country' }, { name: 'Products' }],
rows: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
};
let pivotObj;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} allowExcelExport={true} enableVirtualization={true} dataSourceSettings={dataSourceSettings} exportComplete={exportComplete.bind(this)}><Inject services={[VirtualScroll, ExcelExport]}/></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function exportComplete(args) {
args.promise.then((e) => {
console.log(e.blobData);
});
}
function btnClick() {
let excelExportProperties = {
fileName: 'excelexport.xlsx'
};
pivotObj.excelExport(excelExportProperties, false, null, true);
}
}
;
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));
import { ButtonComponent } from '@syncfusion/ej2-react-buttons';
import { IDataOptions, IDataSet, PivotViewComponent, VirtualScroll, Inject, ExportCompleteEventArgs, ExcelExport } from '@syncfusion/ej2-react-pivotview';
import { ExcelExportProperties } from '@syncfusion/ej2-grids';
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import { pivotData } from './datasource';
function App() {
let dataSourceSettings: IDataOptions = {
columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
dataSource: pivotData as IDataSet[],
expandAll: true,
filters: [],
formatSettings: [{ name: 'Amount', format: 'C0' }],
values: [{ name: 'Country' }, { name: 'Products' }],
rows: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }]
}
let pivotObj: PivotViewComponent;
return (<div><div className="col-md-9"> <PivotViewComponent ref={d => pivotObj = d} id='PivotView' height={350} allowExcelExport={true} enableVirtualization={true} dataSourceSettings={dataSourceSettings} exportComplete={exportComplete.bind(this)}><Inject services={[VirtualScroll, ExcelExport]}/></PivotViewComponent></div>
<div className='col-lg-3 property-section'><ButtonComponent cssClass='e-primary' onClick={btnClick.bind(this)}>Export</ButtonComponent></div></div>);
function exportComplete(args: ExportCompleteEventArgs): void {
args.promise.then((e: { blobData: Blob }) => {
console.log(e.blobData);
});
}
function btnClick(): void {
let excelExportProperties: ExcelExportProperties = {
fileName: 'excelexport.xlsx'
};
pivotObj.excelExport(excelExportProperties, false, null, true);
}
};
export default App;
ReactDOM.render(<App />, document.getElementById('sample'));