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

DevExtreme Team Blog
10 June 2019

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.

Advanced Document Customization

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");
});

Headers And Footers

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() { ... }

Custom Cell Formats

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'
);
});
});

Two Grids Into One Worksheet

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.

Images In Worksheets

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.

Load Panel

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!

10 comment(s)
Santiago Moscoso
Santiago Moscoso

Just beautiful

10 June, 2019
KANDRU NAGALAKSHMI
KANDRU NAGALAKSHMI

Excellent this what we are expecting

10 June, 2019
Jim Foye
Jim Foye

Looks very cool!

10 June, 2019
DmitryGr
DmitryGr

Thanks for extension.

How about exporting PDF files for a print version?

10 June, 2019
Oliver Sturm (DevExpress)
Oliver Sturm (DevExpress)

@DmitryGr - we don't have immediate plans to tackle this, but we are counting your vote for PDF export. Thanks for your interest!

11 June, 2019
Sara Radmaneshs
Sara Radmaneshs

Perfect...Thanks.

11 June, 2019
SistemasCaltic
SistemasCaltic

I vote for pdf export too, other doubt with this new functions do you have plans to export master details grid to excel automatically, or include a sample to export master details grids?

14 June, 2019
Dan (DevExpress)
Dan (DevExpress)

Thank you for your feedback.

Currently, our client-side DataGrid widget does not export master-detail items. We will see how to improve this situation, though I cannot provide you with any ETA on this.

17 June, 2019
Customer53657
Customer53657

Good afternoon,

Is there any example of sum of lines Example at line 7 add line 5 + 6

16 August, 2019
Dan (DevExpress)
Dan (DevExpress)

Thank you for your question.

I created a separate ticket in our Support Center to demonstrate this scenario: T807978 - DataGrid.ExcelJS CTP export - How to use Excel formula to show a total value in an Excel cell when exporting DataGrid into Excel using the exportDataGrid function

See Formula ValueExcelJS: StylesExcelJS: Number Formats and dxDataGrid - ExcelJS export with formula in total summary.

Don't hesitate to create a new ticket if I misunderstand your requirements.

20 August, 2019

Please login or register to post comments.