Formatting Cells When Exporting the Grid to Excel

Environment

Product Version	2022.2.621
Product	Telerik UI for ASP.NET Core Grid

Description

How can I format the cell values during the export of the Grid to Excel?

Solution

Use the format option of the Workbook cell to set the format of the cell value.

1. Handle the ExcelExport event of the Grid.
2. Get the Workbook sheet and loop through the array of the sheet rows.
3. Specify the required cell format.

Index.cshtml

    @(Html.Kendo().Grid<Kendo.Mvc.Examples.Models.ProductViewModel>()
        .Name("grid")
        .Columns(columns =>
        {
            columns.Bound(p => p.ProductName);
            columns.Bound(p => p.UnitPrice);
            columns.Bound(p => p.UnitsOnOrder);
            columns.Bound(p => p.UnitsInStock);
        })
        .Events(e => e.ExcelExport("excelExport"))
        .ToolBar(tools => tools.Excel())
        .Pageable()
        .Sortable()
        .Scrollable()
        .Groupable()
        .Excel(excel => excel
            .FileName("Kendo UI Grid Export.xlsx")
            .Filterable(true)
        )
        .Reorderable(r => r.Columns(true))
        .Resizable(r => r.Columns(true))
        .ColumnMenu()
        .DataSource(dataSource => dataSource
            .Ajax()
            .PageSize(20)
            .Read(read => read.Action("Excel_Export_Read", "Grid"))
        )
    )

JavaScript

    <script>
        function excelExport(e) {
            var sheet = e.workbook.sheets[0]; //Workbook sheet.
            for (var rowIndex = 1; rowIndex < sheet.rows.length; rowIndex++) {
                var row = sheet.rows[rowIndex];
                row.cells[1].format = "[Red](#,##0.0);0.0;" //Format the data in the second column.
            }
        }
    </script>

For a runnable example based on the code above, see the REPL project on formatting cells when exporting the Grid to Excel.

More ASP.NET Core Grid Resources

• ASP.NET Core Grid Documentation

• ASP.NET Core Grid Demos

• ASP.NET Core DataGrid Product Page

• Telerik UI for ASP.NET Core Video Onboarding Course (Free for trial users and license holders)

• Telerik UI for ASP.NET Core Forums

See Also

• Telerik REPL: Formatting Cells When Exporting the Grid to Excel
• Exporting to Excel (Overview)
• Client-Side API Reference of the Grid for ASP.NET Core
• Server-Side API Reference of the Grid for ASP.NET Core
• Telerik UI for ASP.NET Core Breaking Changes
• Telerik UI for ASP.NET Core Knowledge Base