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.
- Handle the
ExcelExportevent of the Grid. - Get the Workbook sheet and loop through the array of the sheet rows.
- Specify the required cell format.
@(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"))
)
)
<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.