DevExtreme DataGrid – Excel Data Export Customization Enhancements (v18.2)

We've extended the DevExtreme DataGrid's API so you can control the appearance of exported data more effectively.

A new export.customizeExcelCell configuration option allows you to modify the formatting of Excel cells: background color, fill pattern, font, value, number format, and alignment:

So, if at runtime you've customized the cells in the DataGrid as shown:

DevExtreme DataGrid - Excel Export

Then, when you export that grid to Excel, you can maintain the same appearance:

DevExtreme DataGrid - Excel Export

Note: This feature is available in DevExtreme for Angular, Vue, React, jQuery, and ASP.NET MVC and ASP.NET Core.

Customize Excel Cell

Here are some examples of what you can now do with the customization of exported cells:

Alignment and Word wrap

Adjust the cell alignment with options like right, bottom, etc. You can also set the wrapTextEnabled option for word wrap:

$("#dataGrid").dxDataGrid({
    // …
    export: {
        enabled: true,
        customizeExcelCell: e => {
            if(e.gridCell.rowType === "data") {
                e.horizontalAlignment = "right";
                e.verticalAlignment = "bottom";
                e.wrapTextEnabled = true;
            }
        }
    }
});

Background

Adjust the background, for example, the #FFBB00 background color value will make cell’s background yellow:

$("#dataGrid").dxDataGrid({
    // …
    export: {
        enabled: true,
        customizeExcelCell: e => {
            e.backgroundColor = "#FFBB00";
        }
    }
})

Font

Adjust the font, for example, setting the font colot to #AAAAAA will make the text gray:

$("#dataGrid").dxDataGrid({
    // …
    export: {
        enabled: true,
        customizeExcelCell: e => {
            e.font.color = "#AAAAAA";
        }
    }
})

Number Format

Adjust the number format. By setting the format to "$#,##0.00", the value 4321 will be displayed as $4,321.00:

$("#dataGrid").dxDataGrid({
    // …
    export: {
        enabled: true,
        customizeExcelCell: e => {
            e.numberFormat = "$#,##0.00";
        }
    }
})

Value

Directly set the value:

$("#dataGrid").dxDataGrid({
    // …
    export: {
        enabled: true,
        customizeExcelCell: e => {
            if(e.gridCell.column.dataField === "count") {
                e.value = 42;
            }
        }
    }
})

Conditional Behavior

You can also add conditional behavior depending on the DataGrid cell's context. For example, in the code below, we'll change font to bold for the 'Employee' cell if the 'SaleAmount' is greater than 15000:

$("#dataGrid").dxDataGrid({
    // …
    export: {
        enabled: true,
        customizeExcelCell: e => {
            if(e.gridCell.rowType === "data" &&
                e.gridCell.data.SaleAmount > 15000 &&
                e.gridCell.column.dataField === "Employee") {
                    e.font.bold = true;
            }
        }
    }
}

Limitations

There are a few limitations with this new cell customization feature. These include:

  • The DevExtreme DataGrid's export.customizeExcelCell does not provide as many options as the XLSX format or Microsoft Excel’s own Format Cells dialog box. Please take a look at the documentation for a list of currently supported options. If you are interested in additional options then please let us know.
  • The PivotGrid doesn’t support the export.customizeExcelCell option yet. However, we plan to add it in a future release.
  • The TreeList control does not support the ‘Export to Excel’ feature yet.
  • The 'custom header/footer' functionality is not included yet but we're working on bringing it to you in a future release.

Help drive the priority of the above features by answering the one question survey below please.

Test It Now (and provide feedback)

Test-drive the public beta now. Use the npm pre-release package:

npm install --save devextreme@18.2-unstable

Then let us know your experience with the DataGrid's Excel export feature.

Note: After the final v18.2 release, please remove the -unstable suffix

Join the Webinar

Sign up for the upcoming "New in v18.2 - DevExtreme HTML / JS Controls" webinar where:

  • you'll see all the new features/tools/components in v18.2 and
  • you can ask questions and interact with our devs

Register today: https://attendee.gotowebinar.com/register/1534319392881688322

Your Feedback Counts

We’d like to hear from you about your development plans and needs. Feel free to leave comments below, open Support Center tickets for detailed discussions or take part in conversations in our GitHub repository.

For some quick feedback, please submit your responses to this short survey:

8 comment(s)
Jim Foye

There is no such thing as too much integration with Excel. Thanks!

7 November, 2018
Mehul Harry (DevExpress)

@Jim Foye  :-)

7 November, 2018
SistemasCaltic

could you consider that the export take the colors and style of the grid? I want to add the code in two places in the grid and in the excel

9 November, 2018
Alex B (DevExpress)

@SistemasCaltic Thank you for your suggestion. We are considering the WYSIWYG export to Excel. There are some technical limitations of this approach, so we need additional research on what can be done within this feature. In the meantime, you can implement your own solution to customize the appearance of DataGrid Excel and DOM cells in a unified way. Feel free to open a ticket in our Support Center if you need any further assistance on this topic.

12 November, 2018
Customer33992

Hi Team,

In my project we are referring DevExpress Version 7.1.And if we are upgrade to higher version ,whether any code change is required to the existing DevExpress functions.The older functions which is avilable in DevExpress 7.1 ,will support in new version 18.2 as well?

13 November, 2018
Alex B (DevExpress)

> And if we are upgrade to higher version, whether any code change is required to the existing DevExpress functions.

We have introduced a lot of breaking changes since 7.1. You can review them on our version history page www.devexpress.com/.../VersionHistory, but this is quite a huge list.

If you have any questions regarding the update, feel free to open a corresponding ticket in our Support Center.

13 November, 2018
Customer33992

We have the DevExpress 7.1 Licence in the current server. If we are upgrading the server to higher version do need to buy a new licence of DevExpress 7.1 ?

Could you please let us know your contact number as well so that we can discuss the points.

15 November, 2018
Mehul Harry (DevExpress)

@Customer33992, You can call +1-818-844-3383 and talk to our client services team. You can also contact them via chat on devexpress.com or email them at info@devexpress.com. Thanks.

15 November, 2018

Please login or register to post comments.