DevExtreme Pivot Grid - New Excel Export API (RTM in v20.2)

DevExtreme Team Blog
29 December 2020

Our improved Excel Export API (first available for the DevExtreme DataGrid) is now part of our PivotGrid component - and available for Angular, Vue, React, and jQuery.

What’s New

As you may already know, our new Excel Export API is based on the open source ExcelJS library. This library allows you to customize exported Excel documents as your business requirements dictate. Let’s take a closer look at what you can expect with our new PivotGrid Excel Export API.

Exported UI Configuration Options

The new PivotGrid Export to Excel API (for Angular, Vue, React, jQuery) can now automatically apply an expanded list of PivotGrid UI configuration options for the exported Excel document. This set includes the following:

  • Column band settings (multi-level column headers);
  • Column width settings;
  • Cell formatting.

Excel Cell Customization

The new customizeCell callback gives you total control over exported cell values, display format and associated pivot table appearance within Excel. Though most will opt for WYSIWYG export, our API allows you to customize the exported document as needed.

Headers and Footers

Headers and footers are an essential part of many business documents. With our new Excel Export API, you are not limited to what can be displayed withing headers and footers. Our new implementation calculates the cell range occupied by the exported PivotGrid and passes it to you in a callback function. You can use this range to position your custom header or footer within the document at a location relative to specific PivotGrid data cells.

Export Progress Indication

If you’ve exported large datasets, you already know that some web browser may ‘freeze’ until the export process is complete. To indicate that export is in progress, our Pivot Grid for Angular, Vue, React and jQuery automatically displays a Load Panel. You can use the loadPanel option to customize its display characteristics as needed.

Advanced Document Generation

Since we offer you full control over the exported Excel workbook, a few important use cases can now be implemented within your web app (v20.2):

  • Create additional worksheets or modify exported worksheets.
  • Place the exported PivotGrid at any position within the target Excel Worksheet using the new topLeftCell option.
  • Export as many PivotGrids to a single Excel workbook or worksheet as required.
  • Export your PivotGrids side-by-side with DataGrids.
  • Export any custom content such as images or text.

This new Excel Export API offers tons of lexibility – we certainly hope it meets all your business requirements. Should you have a specific use-case it does not address, feel free to comment below.

How It Works

To export a PivotGrid, use the new ExportPivotGrid method (it accepts an object with the following fields):

  • component: PivotGrid, // A PivotGrid instance;
  • worksheet: Object, // The Excel worksheet into which the PivotGrid will be exported;
  • customizeCell: Function, // A callback to customize an Excel cell during export;
  • topLeftCell: Object | String, // An Excel cell that defines initial position for export;
  • loadPanel: Object // A Load Panel configuration object;
  • keepColumnWidths: Boolean // An option to retain column width in the exported Excel document.

Below is a simple example and a short explanation of how this all works:

Angular:

<dx-pivot-grid (onExporting)="onExporting($event)" ...>... </dx-pivot-grid>

React:

<PivotGrid onExporting={onExporting} ...>...</PivotGrid>

Vue

<DxPivotGrid @exporting="onExporting" ...>...</DxPivotGrid>

JQuery

$("#pivotgrid").dxPivotGrid({ onExporting: onExporting, ...});

A standard event handler:

onExporting: (e) => { 
  var workbook = new ExcelJS.Workbook(); 
  var worksheet = workbook.addWorksheet('Sheet name'); 
  
  DevExpress.excelExporter 
     .exportPivotGrid({ 
       worksheet: worksheet, 
       component: e.component 
     }) 
     .then(function() { 
       workbook.xlsx.writeBuffer().then(function(buffer) { 
         saveAs( 
           new Blob([buffer], { type: 'application/octet-stream' }), 
           'PivotGrid.xlsx' 
         ); 
       }); 
     }); 

   e.cancel = true; 
};

In this example we execute the following steps:

  • Subscribe to the PivotGrid’s onExporting event and provide a handler function. This event is raised when a user clicks PivotGrid’s built-in Export button.
  • Prevent use of our old built-in Export by setting ‘e.cancel’ to ‘true’ (our old PivotGrid export engine was preserved for backward compatibility only).
  • Use ExcelJS to create a new workbook with a single worksheet.
  • Call the ‘excelExporter.exportPivotGrid’ method to initiate PivotGrid export to the newly generated worksheet.
  • Use the ‘saveAs’ function from FileSaver.js to download the exported Excel document.

Try It

To try the new Excel Export API, please follow our step-by-step guide. You can also explore our new technical demos: Export To Excel.

Feedback

As always, we welcome your feedback. Please comment below and let us know what you think of our new PivotGrid export feature.

Before we let you go - please respond to the following question and help us prioritize future development plans as they relate to Excel:

Showcase Your Apps on DevExpress.com

Highlight your business app and share your development experiences with the DevExpress community. To include your app in our upcoming App Showcase, please forward an application screenshot to clientservices@devexpress.com and tell us which DevExpress products you currently use within your organization.
Jim Foye
Jim Foye

I have used this control with a SSAS database on the backend, and it worked wonderfully. In the future, I may need to use it against AWS Redshift, if there is an example of that somewhere, please share (maybe it's not that hard, and an example is not really needed).

On the new Excel stuff, as I like to say, "There is no such thing as too much integration with Excel". Looks awesome!

29 December 2020
Maxim (DevExpress)
Maxim (DevExpress)
Hi Jim,

Thank you for your feedback. At the moment, we don't have an integration example with AWS Redshift. We will keep this idea in mind and will consider creating such an example. Should you encounter any issues with integration, feel free to contact us in our support center. We'll be happy to help.
30 December 2020

Please login or register to post comments.