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>

Answer 1

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>