Format Cell Values

Set Cell Format During Excel Export

Your project might require you to format the cell values of the Grid.

To achieve this behavior, set the format option of the cells. The page about creating a custom number format describes the formats, supported by Excel.

The following example demonstrates how to format cell values of the Grid while exporting it to Excel.

Example

<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 example below demonstrates how to set a custom format for date values.

Example

<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 = "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>

See Also

• JavaScript API Reference of the Grid
• How to Align Footer Cells
• How to Configure Color for Alternating Rows
• How to Export Checked Columns Only
• How to Export Detail Grids
• How to Export Multiple Grids
• How to Use Column Template

For more runnable examples on the Kendo UI Grid, browse its How To documentation folder.