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

admin管理员组

文章数量:1289761

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": "pleted",
                "title": "Completed",
                "template": kendo.template($("#pletedTemplate").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>

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": "pleted",
                "title": "Completed",
                "template": kendo.template($("#pletedTemplate").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>

• javascript
• html
• excel
• kendo-ui
• kendo-grid

Share Improve this question Follow edited Sep 6, 2016 at 20:14 James Wierzba asked Sep 6, 2016 at 18:39 James WierzbaJames Wierzba 17.6k1515 gold badges8989 silver badges135135 bronze badges

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 4

I found this great answer by Telerik on their website: https://docs.telerik./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./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./ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://kendo.cdn.telerik./2022.1.119/js/kendo.all.min.js"></script>
<script src="https://kendo.cdn.telerik./2022.1.119/js/jszip.min.js"></script>
<link href="https://kendo.cdn.telerik./2022.1.119/styles/kendo.default-v2.min.css" rel="stylesheet" />

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

Check http://docs.telerik./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.

本文标签： javascriptKendo excel exporthow do I export columns with a custom templateStack Overflow