New to Kendo UI for jQuery? Download free 30-day trial

Creating Formatted Column in Workbook Sheet During Grid Excel Export

Environment

Product Version 2023.1.425
Product Grid for Progress® Kendo UI®

Description

How can a new workbook's sheet be added with a single column of formatted cells when exporting to Excel from a Kendo UI Grid?

Solution

A column can be added with cell formatting during the excelExport event by defining an array for the rows, and configuring the cells appearance.

  //set empty rows array
  var rows = [];

  for (var i = 0; i < 5; i++) {
    //Define each array item with the cell properties
    rows[i] = {
      cells: [
        {
          value: "Value " + i,
          bold: true,
          background: "#0000ff",
          color: "#ffffff",
        },
      ],
    };
  }

Then, push a new workbook sheet, and set the workbook.sheets.columns width for the specific column:

  //Add new Sheet
  e.workbook.sheets.push({
    name: "Columns Sheet",

    //sets autoWidth for column
    columns: [{ autoWidth: true }],

    //Rows added to sheet
    rows: rows,
  });
  <div id="grid"></div>
  <script>
    $("#grid").kendoGrid({
      toolbar: ["excel"],
      excel: {
        fileName: "Grid.xlsx"
      },
      columns: [
        { field: "name" }
      ],
      dataSource: [
        { name: "Jane Doe"},
        { name: "John Doe"}
      ],
      excelExport: function(e) {

        //set empty rows array
        var rows = [];

        for(var i=0;i<5;i++){

          //Define each array item with the cell properties
          rows[i] = {
            cells: [
              {
                value: "Value " + i,
                bold: true,
                background: "#0000ff",
                color: "#ffffff",
              }
            ]
          };
        }


        //Add new Sheet
        e.workbook.sheets.push({
          name: "Columns Sheet",

          //sets autoWidth for column
          columns: [
            { autoWidth: true },
          ],

          //Rows added to sheet
          rows: rows
        });

      }
    });
  </script>

See Also

In this article