Edit this page

Export to Excel Grids with Multiple Column Templates and Arbitrary Template Content

Environment

Product Progress Kendo UI Grid

Description

I am exporting a Grid with one and two template columns by using the approach from the column template article. However, I have Grids with dynamic columns and multiple template columns and to use hard-coded field names and indexes is not possible. Additionally, each template column contains different type of templates—from HTML strings to special character values.

How can I export my Kendo UI Grid with multiple template columns with arbitrary HTML template content to Excel?

Solution

  1. Traverse the exported content of the Grid in the excelExport event.
  2. Replace the cell content for template columns.

The following example produces text-only content in the exported Excel file. The Grid is able to export only data values to Excel.

    <div id="example">
      <div id="grid"></div>
      <script>
        $(document).ready(function() {
          $("#grid").kendoGrid({
            dataSource: {
              type: "odata",
              transport: {
                read: "https://demos.telerik.com/kendo-ui/service/Northwind.svc/Orders"
              },
              schema: {
                model : {
                  fields: {
                    OrderDate: {type: "date"}
                  }
                }
              },
              pageSize: 20
            },
            height: 550,
            toolbar: ["excel"],
            excel: {
              allPages: true
            },
            excelExport: function (e) {
              var dataSource = e.sender.dataSource;
              var gridColumns = e.sender.columns;
              var sheet = e.workbook.sheets[0];
              var columnTemplates = [];
              var dataItem;
              // Create element to generate templates in.
              var elem = document.createElement('div');

              // Create a collection of the column templates, together with the current column index
              for (var i = 0; i < gridColumns.length; i++) {
                if (gridColumns[i].template) {
                  columnTemplates.push({ cellIndex: i, template: kendo.template(gridColumns[i].template) });
                }
              }

              // Traverse all exported rows.
              for (var i = 1; i < sheet.rows.length; i++) {
                var row = sheet.rows[i];
                // Traverse the column templates and apply them for each row at the stored column position.

                // Get the data item corresponding to the current row.
                var dataItem = dataSource.at(i - 1);
                for (var j = 0; j < columnTemplates.length; j++) {
                  var columnTemplate = columnTemplates[j];
                  // Generate the template content for the current cell.
                  elem.innerHTML = columnTemplate.template(dataItem);
                  if (row.cells[columnTemplate.cellIndex] != undefined)
                    // Output the text content of the templated cell into the exported cell.
                    row.cells[columnTemplate.cellIndex].value = elem.textContent || elem.innerText || "";
                }
              }
            },
            pageable: true,
            columns: [
              {
                field:"OrderID",
                filterable: false
              },
              {
                field: "OrderDate",
                title: "Order Date",
                template: "<em>#:kendo.toString(OrderDate, 'd')#</em>"
              }, {
                field: "ShipName",
                title: "Ship Name",
                template: "#:ShipName.toUpperCase()#"
              }, {
                field: "ShipCity",
                title: "Ship City",
                template: "<span style='color: green'>#:ShipCity#, #:ShipCountry#</span>"
              }
            ]
          });
        });
      </script>
    </div>

See Also

Is this article helpful? Yes / No
Thank you for your feedback!

Give article feedback

Tell us how we can improve this article

close
Dummy