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

Include a Column with Formulas When Exporting Grid to Excel

Environment

Product Progress Kendo UI Grid
Created with version 2019.3.917

Description

I have a requirement to export an Excel sheet with tabular data and equations within. Is it possible to add formulas or equations which would be calculated in Excel when opening?

Solution

You can alter the generated workbook to include a column with formulas by following the steps below:

  1. Add an excelExport event handler function to loop over the workbook.
  2. Add a header cell for the formula column.
  3. Generate the formula for all the next cells and add it as the sheets.rows.cells.formula.
    excelExport: function(e) {
        var workbook = e.workbook;
        var sheet = workbook.sheets[0];
        sheet.rows[0].cells.push({
          value: "Sum",
          background:"#60b5ff",
          color:"#ffffff"

        });

        for (var i = 1; i < sheet.rows.length; i++) {
          var formula = kendo.format("=SUM(B{0}:C{1})", i+1, i+1);
          sheet.rows[i].cells.push({ formula: formula });
        }
    }
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.4.0/jszip.min.js"></script>

    <div id="grid" ></div>
    <script>
      $("#grid").kendoGrid({
        toolbar: ["excel"],
        excel: {
          allPages: true
        },
        dataSource: {
          type: "odata",
          transport: {
            read: "https://demos.telerik.com/kendo-ui/service/Northwind.svc/Products"
          },
          pageSize: 7
        },
        excelExport: function(e) {
          var workbook = e.workbook;
          var sheet = workbook.sheets[0];
          sheet.rows[0].cells.push({
            value: "Sum",
            background:"#60b5ff",
            color:"#ffffff"

          })
          for (var i = 1; i < sheet.rows.length; i++) {
            var formula = kendo.format("=SUM(B{0}:C{1})", i+1, i+1);
            sheet.rows[i].cells.push({ formula: formula });
          }
        },
        pageable: true,
        columns: [
          { width: 300, field: "ProductName", title: "Product Name" },
          { field: "UnitsOnOrder", title: "Units On Order" },
          { field: "UnitsInStock", title: "Units In Stock" }
        ]
      });
    </script>
In this article
Not finding the help you need? Improve this article