Format Cell Values in the Grid
Environment
| Product | Progress Kendo UI Grid for jQuery |
| Operating System | Windows 10 64bit |
| Preferred Language | JavaScript |
Description
How can I format the cell values of the Kendo UI Grid?
Solution
Set the format option of the cells. For more information on the formats that are supported by Excel, refer to the page on creating a custom number format.
The following example demonstrates how to format cell values of the Grid while exporting it to Excel.
<div id="grid"></div>
<script>
$("#grid").kendoGrid({
toolbar: ["excel"],
excelExport: function(e) {
var sheet = e.workbook.sheets[0];
for (var rowIndex = 1; rowIndex < sheet.rows.length; rowIndex++) {
var row = sheet.rows[rowIndex];
for (var cellIndex = 0; cellIndex < row.cells.length; cellIndex ++) {
row.cells[cellIndex].format = "[Blue]#,##0.0_);[Red](#,##0.0);0.0;"
}
}
},
dataSource: {
data: [
{ text: "Positive", value: 10.5 },
{ text: "Negative", value: -10.5 },
{ text: "Zero", value: 0 }
]
}
});
</script>
The following example demonstrates how to set a custom format for date values.
<div id="grid"></div>
<script>
$("#grid").kendoGrid({
toolbar: ["excel"],
excelExport: function(e) {
var sheet = e.workbook.sheets[0];
for (var rowIndex = 1; rowIndex < sheet.rows.length; rowIndex++) {
var row = sheet.rows[rowIndex];
for (var cellIndex = 0; cellIndex < row.cells.length; cellIndex ++) {
//check if the cell value is of type date
if(row.cells[cellIndex].value instanceof Date) {
row.cells[cellIndex].format = "yy-MM-dd hh:mm:ss"
}
}
}
},
columns: [
{ field: "name" },
{ field: "age" },
{ field: "date" }
],
dataSource: {
data: [
{ name: "Jane Doe", age: 30, date: new Date() },
{ name: "John Doe", age: 33, date: new Date() }
],
schema: {
model : {
fields: {
date: { type: "date" },
name: { type: "string" },
age: { type: "number" }
}
}
}
}
});
$("#export").click(function(e) {
var grid = $("#grid").data("kendoGrid");
grid.saveAsExcel();
});
</script>