Excel Export from Grid: need to specify format of column/cell

[Tyler]

Good day,

I am presenting data in a grid, either as a date, time or date/time. When we export to Excel, it is defaulting all to the same date format. I would like to override the format for the times when exporting. I have dug through the code and found that if I add a format: "h:mm" to the data object in the ExcelExportEvent.workbook.sheets.rows.cells it does work as I want.

The issue is that the ExcelExportEvent columns definition does not include the data needed to go back and look at my original data to look up the right format. I need the field name so i can look up the format to apply, and then line up the column indices, etc.

For example, in this image you see the columns only contain width and autoWidth. Of course some columns may be hidden, so I cannot use the indices directly.

I am hoping perhaps there is a way I can add properties to the Grid Column which are picked up by the Excel export.

Thank you for your time!

[Martin Bechev]

Hello Tyler,

Basically, when the Excel export functionality is configured, the Grid works with the data rather than the cell's content. Here is an example where I format a date column setting the cell.format option to the desired format before exporting:

https://stackblitz.com/edit/angular-y7jnwt-ndefoe

When the user clicks the Export to Excel command button, excelExport event is fired which returns an object from the type ExcelExportEvent. The object contains information about each row and its cell content. The developer can iterate each row and cell and manually customize the desired one. 

I hope this points you in the right direction.

Regards,
Martin
Progress Telerik

[Tyler]

This is great, thank you very much. I used the value (title) of the column to look up the matching field, where I have the Excel format ready, and then create an index -> format map to use for lookup in the data section.

It is possible two columns could have the same name/title, but I expect that not to happen.

Thank you so much for the sample!

[Dan]

Has a new solution been found? This solution does not work for me because my application is localized and the title of the column differs based on the language selected.

[Yanmario]

Hi Dan,

The Excel Export works with the data as is. When translating a page or a component, it doesn't change the underlying data that the component is using but rather the page itself.

This means that the developer would need to translate the Workbook data or translate the data to the specific locale before exporting to Excel.

I hope this helps.

Regards,
Yanmario
Progress Telerik

[Simon]

This works, but it's horrible. 

There must be a more generic way