Formatting Column Cell Values in Exported Excel Files

Environment

Product

Progress® Kendo UI for Angular Grid

Description

I am able to format the column cell values (for example, $0.00), but in the exported Excel file no format is applied. How can I apply the custom format when the Grid is exported to Excel?

Solution

The Excel export functionality is configured to work with the Grid data rather than with the content of the cell. That is why the values in the exported sheet do not apply the custom format.

You have to manually apply the format in the excelExport event handler by iterating the Grid cells and setting their format property accordingly.

The following example demonstrates how to apply a currency format to a numeric column.

ts

public colIndex: number;
public onExcelExport(e: any): void {
    const rows = e.workbook.sheets[0].rows;
    rows.forEach((row) => {
        // Store the price column index
        if (row.type === 'header') {
            row.cells.forEach((cell, index) => {
                if (cell.value === 'Unit Price') {
                    this.colIndex = index;
                    return;
                }
            });
        }
        // Use the column index to format the price cell values
        if (row.type === 'data') {
            row.cells.forEach((cell, index) => {
                if (index === this.colIndex) {
                    cell.format = '$#.00';
                }
            });
        }
    });
}

The following example demonstrates the full implementation of the suggested approach.

Change Theme

Theme

Loading ...