JavaScript/jQuery PivotGrid - Export

You can export PivotGrid data to Excel. Follow the steps outlined in this help topic to complete the process. For PivotGrid export demos, see:

View Demo

Install Dependencies

To export PivotGrid data to Excel, you first need to install the following third-party libraries:

jQuery
index.html
<html>
    <head>
        <!-- ... -->
        <script src="https://cdnjs.cloudflare.com/ajax/libs/babel-polyfill/7.4.0/polyfill.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/exceljs/4.4.0/exceljs.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.2/FileSaver.min.js"></script>

        <!-- DevExtreme scripts are referenced here -->
        <!-- ... -->
    </head>
</html>
Angular
Installation command
tsconfig.app.json
npm install --save exceljs file-saver
{
    // ...
    "compilerOptions": {
        // ...
        "paths": {
            // ...
            "exceljs": [
                "node_modules/exceljs/dist/exceljs.min.js"
            ]
        }
    },
    // ...
}
Vue
Installation command
npm install --save exceljs file-saver
React
Installation command
npm install --save exceljs file-saver
IMPORTANT
For details about potential vulnerabilities when using an older ExcelJS version and applying CSP rules, refer to the ExcelJS CSP Treats section.

Enable the Export Button

PivotGrid has a built-in "Export" button. To enable this button, set the export.enabled property to true.

jQuery
index.js
$(function() {
    $("#pivotGrid").dxPivotGrid({
        export: {
            enabled: true
        }
    });
});
Angular
app.component.html
<dx-pivot-grid ... >
    <dxo-export [enabled]="true"></dxo-export>
</dx-pivot-grid>
Vue
App.vue
<template>
    <DxPivotGrid ... >
        <DxExport :enabled="true" />
    </DxPivotGrid>
</template>

<script>
//...
</script>
React
App.tsx
//...

function App() {
    // ...
    return (
        <PivotGrid ... >
            <Export enabled={true} />
        </PivotGrid>
    );
}

export default App;
NOTE
When users click the "Export" button in PivotGrid, the onExporting event handler is executed.

Export to XLSX

All export operations use the exportPivotGrid(options) method from the excelExporter module.

Before starting with exportPivotGrid, obtain:

  • A PivotGrid instance.
  • A target Excel worksheet.

An instance can be obtained through an event handler like onExporting, while the worksheet needs pre-creation:

JavaScript
const workbook = new ExcelJS.Workbook(); 
const worksheet = workbook.addWorksheet('Main sheet');

The following code snippet exports PivotGrid data as a blob that is then saved to an XLSX file.

jQuery
DevExpress.excelExporter.exportPivotGrid({
    component: pivotGridInstance,
    worksheet: worksheet
}).then(function() {
    workbook.xlsx.writeBuffer().then(function(buffer) {
        saveAs(new Blob([buffer], { type: 'application/octet-stream' }), 'Sales.xlsx');
    });
});
Angular
exportPivotGrid({ 
    worksheet: worksheet, 
    component: pivotGridInstance
}).then(function() {
    workbook.xlsx.writeBuffer().then(function(buffer: BlobPart) { 
        saveAs(new Blob([buffer], { type: "application/octet-stream" }), "Sales.xlsx"); 
    }); 
});
Vue
exportPivotGrid({ 
    worksheet: worksheet, 
    component: pivotGridInstance
}).then(function() {
    workbook.xlsx.writeBuffer().then(function(buffer: BlobPart) { 
        saveAs(new Blob([buffer], { type: "application/octet-stream" }), "Sales.xlsx"); 
    }); 
});
React
exportPivotGrid({ 
    worksheet: worksheet, 
    component: pivotGridInstance
}).then(function() {
    workbook.xlsx.writeBuffer().then(function(buffer: BlobPart) { 
        saveAs(new Blob([buffer], { type: "application/octet-stream" }), "Sales.xlsx"); 
    }); 
});
NOTE
You can pass a PivotGrid instance, a worksheet, and other properties to exportPivotGrid to customize your Excel sheet. For a full list, refer to PivotGridExportOptions.

Export to CSV

IMPORTANT
Refer to the CSV Injection section for information about CSV Injection Attacks.

You can also export PivotGrid to CSV. To do this, call the exportPivotGrid(options) method as follows:

jQuery
JavaScript
$(function(){
    const pivotGrid = $('#gridContainer').dxPivotGrid({
        onExporting: function(e) {
            const workbook = new ExcelJS.Workbook();
            const worksheet = workbook.addWorksheet('Employees');
            DevExpress.excelExporter.exportPivotGrid({
                component: e.component,
                worksheet: worksheet
            }).then(function() {
                workbook.csv.writeBuffer().then(function(buffer) {
                    saveAs(new Blob([buffer], { type: "application/octet-stream" }), "Report.csv");
                });
            });
        },
    }).dxPivotGrid('instance');
});
Angular
app.component.ts
app.component.html
import { Component } from '@angular/core';
import { Workbook } from 'exceljs';
import { saveAs } from 'file-saver-es';
import { exportPivotGrid } from 'devextreme/excel_exporter';

@Component({
    selector: 'app-root',
    templateUrl: './app.component.html',
    styleUrls: ['./app.component.css']
})
export class AppComponent {
    @ViewChild(DxPivotGridComponent, { static: false }) pivotGrid: DxPivotGridComponent;
    onExporting: function(e) {
        const workbook = new Workbook();
        const worksheet = workbook.addWorksheet('Employees');
        exportPivotGrid({
            component: e.component,
            worksheet: worksheet
        }).then(function() {
            workbook.csv.writeBuffer().then(function(buffer) {
                saveAs(new Blob([buffer], { type: "application/octet-stream" }), "Report.csv");
            });
        });
    }
}
<dx-pivot-grid ... 
    (onExporting)="onExporting($event)"
>
</dx-pivot-grid>
Vue
App.vue
<template>
    <div>
        <DxPivotGrid ...
            @exporting="onExporting"
        />
    </div>
</template>

<script setup lang="ts">
import 'devextreme/dist/css/dx.light.css';
import { DxPivotGrid, DxPivotGridTypes } from 'devextreme-vue/pivot-grid';
import { Workbook } from 'exceljs';
import { saveAs } from 'file-saver-es';
import { exportPivotGrid } from 'devextreme/excel_exporter';

function onExporting(e: DxPivotGridTypes.ExportingEvent) {
    const workbook = new Workbook();
    const worksheet = workbook.addWorksheet('Employees');
    exportPivotGrid({
        component: e.component,
        worksheet: worksheet
    }).then(function() {
        workbook.csv.writeBuffer().then(function(buffer) {
            saveAs(new Blob([buffer], { type: "application/octet-stream" }), "Report.csv");
        });
    });
}
</script>
React
App.tsx
import React from 'react';
import 'devextreme/dist/css/dx.light.css';
import PivotGrid, { PivotGridTypes } from 'devextreme-react/pivot-grid';
import { Workbook } from 'exceljs';
import { saveAs } from 'file-saver-es';
import { exportPivotGrid } from 'devextreme/excel_exporter';

const onExporting = (e: PivotGridTypes.ExportingEvent) => {
    const workbook = new Workbook();
    const worksheet = workbook.addWorksheet('Employees');
    exportPivotGrid({
        component: e.component,
        worksheet: worksheet
    }).then(function() {
        workbook.csv.writeBuffer().then(function(buffer) {
            saveAs(new Blob([buffer], { type: "application/octet-stream" }), "Report.csv");
        });
    });
};

export default function App() {
    return (
        <PivotGrid ...
            onExporting={onExporting}
        />
    );
}

Export Field Panel

PivotGrid does not export field panel headers. To enable their export, pass the following properties to exportPivotGrid:

View Demo

Customize Cells

Use the customizeCell function inside the exportPivotGrid method to customize exported cells.

For instance, to outline (group) data in an Excel worksheet, define customizeCell as shown below:

jQuery
JavaScript
$(function() {
    $("#pivotGridContainer").dxPivotGrid({
        // ...
        onExporting(e) {
            const workbook = new ExcelJS.Workbook();
            const worksheet = workbook.addWorksheet('Companies');

            DevExpress.excelExporter.exportPivotGrid({
                component: e.component,
                worksheet: worksheet,
                topLeftCell: { row: 2, column: 2 },
                customizeCell(e) {
                    if (e.pivotCell.area === 'row' && e.pivotCell.path?.length > 0) {
                        worksheet.getRow(e.pivotCell.rowIndex + 1).outlineLevel = e.pivotCell.path.length - 1;
                    }
                }
            }).then(function() {
                workbook.xlsx.writeBuffer().then(function(buffer) {
                    saveAs(new Blob([buffer], { type: "application/octet-stream" }), "Companies.xlsx");
                });
            });
        }
    });
});
Angular
app.component.ts
app.component.html
import { Component } from '@angular/core';
import { exportPivotGrid } from 'devextreme/excel_exporter';
import { Workbook } from 'exceljs';
import saveAs from 'file-saver';

@Component({
    selector: 'app-root',
    templateUrl: './app.component.html',
    styleUrls: ['./app.component.css']
})
export class AppComponent {
    onExporting(e) {
        const workbook = new Workbook();
        const worksheet = workbook.addWorksheet('Companies');
        exportPivotGrid({
            component: e.component,
            worksheet: worksheet,
            customizeCell(e) {
                if (e.pivotCell.area === 'row' && e.pivotCell.path?.length > 0) {
                    worksheet.getRow(e.pivotCell.rowIndex + 1).outlineLevel = e.pivotCell.path.length - 1;
                }
            }
        }).then(function() {
            workbook.xlsx.writeBuffer().then(function(buffer: BlobPart) {
                saveAs(new Blob([buffer], { type: "application/octet-stream" }), "Companies.xlsx");
            });
        });
    }
}
<dx-pivot-grid ...
    (onExporting)="onExporting($event)">
</dx-pivot-grid>
Vue
App.vue
<template>
    <DxPivotGrid ... @exporting="onExporting" />
</template>

<script setup lang="ts">
import 'devextreme/dist/css/dx.light.css';
import { DxPivotGrid, DxPivotGridTypes } from 'devextreme-vue/pivot-grid';
import { Workbook } from 'exceljs';
import { saveAs } from 'file-saver-es';
import { exportPivotGrid } from 'devextreme/excel_exporter';

function onExporting(e: DxPivotGridTypes.ExportingEvent) {
    const workbook = new Workbook();
    const worksheet = workbook.addWorksheet('Companies');
    exportPivotGrid({
        component: e.component,
        worksheet: worksheet,
        customizeCell(e) {
            if (e.pivotCell.area === 'row' && e.pivotCell.path?.length > 0) {
                worksheet.getRow(e.pivotCell.rowIndex + 1).outlineLevel = e.pivotCell.path.length - 1;
            }
        }
    }).then(function() {
        workbook.xlsx.writeBuffer().then(function(buffer: BlobPart) {
            saveAs(new Blob([buffer], { type: "application/octet-stream" }), "Companies.xlsx");
        });
    });
}
</script>
React
App.js
import React from 'react';
import 'devextreme/dist/css/dx.light.css';
import PivotGrid, { PivotGridTypes } from 'devextreme-react/pivot-grid';
import { Workbook } from 'exceljs';
import { saveAs } from 'file-saver-es';
import { exportPivotGrid } from 'devextreme/excel_exporter';

const onExporting = (e: PivotGridTypes.ExportingEvent) => {
    const workbook = new Workbook();
    const worksheet = workbook.addWorksheet('Companies');
    exportPivotGrid({
        component: e.component,
        worksheet: worksheet,
        customizeCell(e) {
            if (e.pivotCell.area === 'row' && e.pivotCell.path?.length > 0) {
                worksheet.getRow(e.pivotCell.rowIndex + 1).outlineLevel = e.pivotCell.path.length - 1;
            }
        }
    }).then(function() {
        workbook.xlsx.writeBuffer().then(function(buffer: BlobPart) {
            saveAs(new Blob([buffer], { type: "application/octet-stream" }), "Companies.xlsx");
        });
    });
};

export default function App() {
    return (
        <PivotGrid ...
            onExporting={onExporting}
        />
    );
}

For another example of customizing your exported PivotGrid, see the following demo:

View Demo