DevExtreme - Data Grid - New Excel Export API (CTP in v19.1)

Our DevExtreme Data Grid has supported data exports to Excel for a long time now. This functionality works well and is sufficient for many use cases. However, there are not many customization points and certain export scenarios are difficult to support on this basis. We received requests to improve the export feature set, including these:

• Add headers and footers
• Export multiple widgets into one Excel document
• Export to different sheets
• Start exporting to a specific cell
• Include images and hyperlinks in exports

We planned and attempted some modifications and extensions to our own document generator for the XLSX format, but we realized that the required changes would be substantial. We decided to go a different way and provide an adapter to the feature-rich ExcelJS library.

The resulting solution is now available in v19.1 as a CTP. We added the section Advanced Document Customization to our Widget Gallery demo, and you can see there how the new functionality works with all our usual supported platforms, including Angular, Vue, React, jQuery and ASP.NET MVC and ASP.NET Core.

Exports Using ExcelJS

It is easy to use the new API. Here is a basic code snippet:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

onExporting: e => {
  var workbook = new ExcelJS.Workbook();
  var worksheet = workbook.addWorksheet('Main sheet');

  DevExpress.excelExporter
    .exportDataGrid({
      worksheet: worksheet,
      component: e.component
    })
    .then(function() {
      workbook.xlsx.writeBuffer().then(function(buffer) {
        saveAs(
          new Blob([buffer], { type: 'application/octet-stream' }),
          'DataGrid.xlsx'
        );
      });
    });
  e.cancel = true;
};

These are the steps implemented by this short sample:

• The trigger point is an event handler for the onExporting event. Technically you can run the export functionality at any point, but if you use this event you can take advantage of the standard Export button supported by the Data Grid. Note that standard processing is deactivated at the end of the code snippet by setting e.cancel = true.
• Using ExcelJS API calls, a new workbook/worksheet combination is created
• Using our new API, the Data Grid is exported to the given worksheet
• To save the document to a file, the saveAs function from FileSaver.js is called.

You are free to customize this approach, for example by referring to any existing worksheet, or by post-processing the document after the export has taken place.

Here is a link to the basic export sample on CodePen.

Advanced Processing

We prepared several additional CodePen samples to cover common use cases. The ExcelJS API is flexible and allows you to modify worksheets directly, or target them multiple times to combine exports.

Headers And Footers

This code sample adds a step between initial export and saving. In this step, the worksheet range used by the exported Data Grid is available, so you can use it to calculate relative cell coordinates. Note that the topLeftCell property is also used here to influence the location of the Data Grid export in the target worksheet.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

DevExpress.excelExporter
  .exportDataGrid({
    component: e.component,
    worksheet,
    topLeftCell: { row: 5, column: 1 }
  }).then(function(dataGridRange) {
    Object.assign(
      worksheet.getRow(2).getCell(2),
      { value: "My Header", font: { bold: true, size: 16, underline: 'double' } }
    );
    Object.assign(
      worksheet.getRow(dataGridRange.to.row + 2).getCell(2),
      { value: "My Footer", font: { bold: true, size: 16, underline: 'double' } }
    );
  }).then(function() {
    workbook.xlsx.writeBuffer().then(function(buffer) {
      saveAs(new Blob([buffer], { type: "application/octet-stream" }),
        "DataGrid.xlsx");
  });

The full sample is available in CodePen.

Custom Cell Formats

Call the exporter and pass a function to the option customizeCell in order to influence the format on the basis of grid information. In this example, cells in a grouped Data Grid are colored depending on their group levels.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

DevExpress.excelExporter
  .exportDataGrid({
    component: e.component,
    worksheet: worksheet,
    customizeCell: function(options) {
      var gridCell = options.gridCell;
      var excelCell = options.cell;

      var nodeColors = [ 'FFBEDFE6', 'FFC9ECD7'];
      var color = gridCell.groupIndex !== undefined ?
        nodeColors[gridCell.groupIndex] : 'FFDDDDDD';
      Object.assign(excelCell, {
        font: { bold: true },
        fill: {
          type: 'pattern',
          pattern:'solid',
          fgColor: { argb: color },
          bgColor: { argb: color }
        }
      });
    }
  }).then(function() { ... }

The CodePen sample is available here.

Multiple Grids

If necessary, you can call the exporter multiple times. Here is an example that targets different locations in the same worksheet:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

DevExpress.excelExporter
  .exportDataGrid({
    worksheet,
    component: dataGrid1,
    topLeftCell: { row: 4, column: 2 }
  })
  .then(function(dataGridRange) {
    return DevExpress.excelExporter.exportDataGrid({
      worksheet,
      component: dataGrid2,
      topLeftCell: { row: 4, column: dataGridRange.to.column + 2 }
    });
  })
  .then(function() {
    workbook.xlsx.writeBuffer().then(function(buffer) {
      saveAs(
        new Blob([buffer], { type: 'application/octet-stream' }),
        'DataGrid.xlsx'
      );
    });
  });

Here is the full sample. We have also prepared a similar sample to export two grids into separate worksheets.

Images

ExcelJS has good support for images in workbooks, and these can be combined with Data Grid exports.

Here is the complete CodePen sample.

Show A Load Panel

As a final sample, we prepared a demo that shows a DevExtreme Load Panel while the export is running. This can be useful for large Data Grid setups where the process takes a little while to complete.

The full CodePen sample is here.

What Do You Think?

If you have any feedback on our implementation, or if you feel that your own export scenarios are not completely covered yet, please don’t hesitate to get back to us. You can leave comments below, open Support Center tickets or take part in the discussion on GitHub. We are always interested in your thoughts!

Free DevExpress Products - Get Your Copy Today