Kendo excel export - how do I export columns with a custom template?

Question

I have a kendo grid that I define declaritively. I enable the excel export toolbar via data-toolbar='["excel"]'

The problem is that this only exports the fields that do not have a template defined. (the first 3 in the grid below: Checkpoint, Location, Patrolled By), the other columns show up in the excel document, but the cells of those columns are all empty.

How can I get the values to show up in the excel export? I'm guessing it will require pre-processing of some sort before the excel gets exported, as the excel export function doesn't interpret my custom field html templates.

<div id="Checkpoints">
    <div 
        ...
        data-toolbar='["excel"]'
        data-excel='{ "fileName": "CheckpointExceptionExport.xlsx", "allPages": "true" }'
        ...
        data-columns='[
            {
                "field": "checkpoint_name", 
                "title": "Checkpoint", 
                "filterable": { "cell": { "operator": "contains"}}},
            {
                "field": "location_name", 
                "title": "Location", 
                "filterable": { "cell": { "operator": "contains"}}
            },
            {
                "field": "patrolled_by", 
                "title": "Patrolled By", 
                "filterable": { "cell": { "operator": "contains"}}
            },
            {
                "field": "geotag",
                "title": "GeoTag", 
                "template": kendo.template($("#geotagTemplate").html()) 
            },
            {
                "field": "geofence",
                "title": "GeoFence",   
                "template": kendo.template($("#geofenceTemplate").html())
            },
            {
                "field": "completed",
                "title": "Completed",
                "template": kendo.template($("#completedTemplate").html())
            },
            {
                "field": "gps",
                "title": "GPS", 
                "template": kendo.template($("#gpsTemplate").html())
            }
        ]'>
    </div>
</div>

I've came across this snippet for handling the excel export event however I don't see a way to use this event handler in the way that I've defined the grid.

 <script>
      $("#grid").kendoGrid({
        excelExport: function(e) {
          ...
        },

      });
    </script>

Solution 1

Check http://docs.telerik.com/kendo-ui/controls/data-management/grid/excel-export#limitations, which explains why this happens and shows how to proceed.

The Grid does not use column templates during the Excel export—it exports only the data. The reason for this behavior is that a column template might contain arbitrary HTML which cannot be converted to Excel column values. For more information on how to use a column template that does not contain HTML, refer to this column template example.

Update

In order to attach a Kendo UI event handler when using declarative widget initialization, use the data-bind HTML attribute and event binding:

<div
    data-role="grid"
    data-bind="events: { excelExport: yourExcelExportHandler }">
</div>

Check the Kendo UI Grid MVVM demo for a similar example.

yourExcelExportHandler should be a function defined in the viewModel, similar to onSave in the above example.

The excelExport event can also be attached after widget initialization.

Solution 2

I found this great answer by Telerik on their website: https://docs.telerik.com/kendo-ui/knowledge-base/grid-export-arbitrary-column-templates. Their helper function exports to excel with the exact template text.

$(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,
      serverPaging: true
    },
    height: 550,
    toolbar: ["excel"],
    excel: {
      allPages: true
    },
    excelExport: exportGridWithTemplatesContent,
    pageable: true,
    columns: [{
        field: "Freight",
        hidden: true
      },
      {
        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>"
      }
    ],
    columnMenu: true
  });
});

function exportGridWithTemplatesContent(e) {
  var data = e.data;
  var gridColumns = e.sender.columns;
  var sheet = e.workbook.sheets[0];
  var visibleGridColumns = [];
  var columnTemplates = [];
  var dataItem;
  // Create element to generate templates in.
  var elem = document.createElement('div');

  // Get a list of visible columns
  for (var i = 0; i < gridColumns.length; i++) {
    if (!gridColumns[i].hidden) {
      visibleGridColumns.push(gridColumns[i]);
    }
  }

  // Create a collection of the column templates, together with the current column index
  for (var i = 0; i < visibleGridColumns.length; i++) {
    if (visibleGridColumns[i].template) {
      columnTemplates.push({
        cellIndex: i,
        template: kendo.template(visibleGridColumns[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 = data[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 || "";
    }
  }
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2022.1.119/js/kendo.all.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2022.1.119/js/jszip.min.js"></script>
<link href="https://kendo.cdn.telerik.com/2022.1.119/styles/kendo.default-v2.min.css" rel="stylesheet" />

<div id="grid"></div>

Comments

[James Wierzba]

If you look at the bottom portion of my answer I've referenced the same solution mentioned in that article, and that I don't know how it can be implemented given the way I've declared the grid (via markup instead of scripting)

[dimodi]

OK, so the question is not "how to export column templates to Excel", but "how to attach event handlers with declarative Grid initialization". See my updated reply.

[James Wierzba]

Where do I define the yourExcelExportHandler function? I can't get the function to be called...

[James Wierzba]

I noticed just now that you provided the way to define the function. I'm having trouble accessing the template of the individual columns so that I can apply the template to the value and place into the spreadsheet. The referenced articles are assuming the function is defined inside of the same context that defines the columns, so they call this.columns[i].template, I assume I need to find the grid and access the columns such as $("#Checkpoints").kendoGrid().columns but this does not work... how can I access the grid column for the template?

[dimodi]

The excelExport event provides access to the Grid instance via this or e.sender. Normally you don't have to obtain the Grid instance again, but in case you really have to, don't use kendoGrid() as this will create a new Grid instance. Instead, use $("selector").data("kendoGrid"). "selector" should point to the Grid <div>. Judging by your code, this is NOT "#Checkpoints", but its child.

[James Wierzba]

Thank you I selected the grid and can see the columns and can obtain references to the template functions. Now, I see that I can read the excel data via e.workbook.sheets[0].rows[i].cells[j].value but the values are blank for the template column values, which makes sense...how do I directly access the data-source fields, like I do inside the templates. e.g. via ${field1} ${field2} ...

[James Wierzba]

I've figured the previous comment's question out it was e.data.<datasourceobjectname>[i]

[KeyOfJ]

This works as long as your aren't using grouping. Unfortunately my grid uses grouping so still trying to figure this out.