Format Grid Cell Values in Exported Excel Files
Environment
| Product | Progress® Kendo UI® for Angular Grid |
Description
I use a Grid cell template to replace raw values with display labels, such as ACTIVE with Active, but the Excel export contains the original value. How can I export the display label and apply Excel-specific formats, such as currencies or dates?
This KB also answers the following questions:
- Why does Excel export ignore the Grid cell template?
- How do I export a display label instead of a raw value from the Grid?
- How do I apply numeric, currency, or date formats to Grid cells in an Excel export?
Solution
Excel export works with the underlying Grid data and does not evaluate cell or column templates. Use the excelExport event to update the generated workbook before the Grid saves it.
The following example demonstrates how to replace raw values with display labels and apply currency and date formats in the exported workbook.
To replace a raw value with a display label, find the column by its header value and update the matching workbook cells:
public onExcelExport(e: ExcelExportEvent): void {
const rows = e.workbook.sheets[0].rows;
const header = rows.find((row) => row.type === 'header');
const columnIndex = header.cells.findIndex((cell) => cell.value === 'Funding Method');
rows.filter((row) => row.type === 'data').forEach((row) => {
const value = row.cells[columnIndex].value as string;
row.cells[columnIndex].value = this.findContributionMethod(value)?.text;
});
}Use the cell format property to apply numeric, currency, date, or other Excel-specific formats while preserving the underlying cell value. Excel applies these formats with the user's Excel locale, so the decimal and group separators can differ from the Grid display.
const amountIndex = header.cells.findIndex((cell) => cell.value === 'Amount');
const dateIndex = header.cells.findIndex((cell) => cell.value === 'Received On');
rows.filter((row) => row.type === 'data').forEach((row) => {
row.cells[amountIndex].format = '$#,##0.00';
row.cells[dateIndex].format = 'yyyy-mm-dd';
});Use fetchData when you need to transform the complete export data set before the workbook is generated.