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

1 Answer 1845 Views
Excel Export Grid
Tyler
Top achievements
Rank 1
Iron
Tyler asked on 02 Jun 2021, 04:31 PM

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!

1 Answer, 1 is accepted

Sort by
0
Accepted
Martin
Telerik team
answered on 07 Jun 2021, 07:50 AM

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

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

Tyler
Top achievements
Rank 1
Iron
commented on 07 Jun 2021, 04:31 PM

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
Top achievements
Rank 1
Iron
Iron
Veteran
commented on 06 Sep 2023, 08:16 AM

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
Telerik team
commented on 11 Sep 2023, 07:08 AM

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

Tags
Excel Export Grid
Asked by
Tyler
Top achievements
Rank 1
Iron
Answers by
Martin
Telerik team
Share this question
or