Telerik Grid Love and Hate: Exporting Client Templates to Excel.
Tags: dotnet, telerik, csharp, aspdotnet
Published: 2023-05-26 07:54:53

In todays entry in the ongoing saga of love and hate between me and the Kendo Grid. 

## Exporting client template values to excel.

**Version**: `Telerik UI For Asp.Net Core 2020.2.617`

So we have a basic Kendo Grid with one minor difference, a column with a client template:

```csharp

.Columns(columns =>
    {
        columns.Bound(c => c.Sku);
        columns.Bound(c => c.Name);
        columns.Bound(c => c.StockStatus)
            .ClientTemplate("#=StockStatus.Value#");
    })

```

Stock status is nothing special a simple class with an `int` Id and a `string` value:

```csharp

    public class InStockStatusViewModel
    {
        public int Id { get; set; }

        public string Value { get; set; }
    }

```

` .ClientTemplate("#=StockStatus.Value#");` changes the display to show the `Value` from the object.


So lets add the excel export:

```csharp

@section Scripts
{
    <script src="/js/jszip.min.js"></script>
}

.ToolBar(t =>
    {
        t.Search().Text("Search SKU");
        t.Excel();
    })
    .Excel(excel => excel.FileName("StockStatus.xlsx").AllPages(true))

```

Nice an simple, except when we test the functionality the third column in the export excel is blank.

After some googling and frustration from broken links on the [Telerik Forums](https://www.telerik.com/forums/exporting-grid-to-excel-but-including-client-templates-possible-) we find out that as the client template could generate HTML, the excel export functionality excludes client templates for safety.

Further down the forum post it links to a [dojo example](http://dojo.telerik.com/@tsveti/EfoSUNim) of the Jquery version of the grid using a custom block of JS

```js

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 || "";
          }
        }
      }


```

Okay that works, now how do we hook it up to the MVC grid...

Seems there is a nice event called `ExcelExport` that we can pass the name of the JS function provided from the Dojo

```csharp
.Events(e => e.ExcelExport("exportGridWithTemplatesContent"))

```

Tested again and bingo it works.

Complete grid code:

```csharp

@(Html.Kendo().Grid<ProductDataStockViewModel>()
    .Name("StockGrid")
    .Columns(columns =>
    {
        columns.Bound(c => c.Sku);
        columns.Bound(c => c.Name);
        columns.Bound(c => c.StockStatus)
            .ClientTemplate("#=StockStatus.Value#");
    })
    .Editable(editable =>
    {
        editable.Mode(GridEditMode.InCell);
    })
    .ToolBar(t =>
    {
        t.Search().Text("Search SKU");
        t.Excel();
    })
    .Excel(excel => excel.FileName("StockStatus.xlsx").AllPages(true))
    .Events(e => e.ExcelExport("exportGridWithTemplatesContent"))
    .Search(search => { search.Field(f => f.Sku); })
    .Pageable()
    .Filterable()
    .Scrollable()
    .ColumnMenu()
    .DataSource(dataSource =>
    {
        dataSource.Ajax();
        dataSource.Ajax().Batch(false);
        dataSource.Ajax().AutoSync(true);
        dataSource.Ajax().PageSize(20);
        dataSource.Ajax().Sort(sort =>
        {
            sort.Add("Sku").Ascending();
        });
        dataSource.Ajax().Events(dataSourceJsEvent =>
        {
            dataSourceJsEvent.Error(JsFunctionName.DISPLAY_ERROR);
        });
        dataSource.Ajax().Model(model =>
        {
            model.Id(p => p.Id);
            model.Field(f => f.Sku).Editable(false);
            model.Field(f => f.Name).Editable(false);
            model.Field(p => p.InStockStatus);
        });
        dataSource.Ajax().Read(read =>
        {
            read.Url("?handler=Read");
            read.Type(HttpVerbs.Post);
            read.Data(JsFunctionName.GET_FORGERY_TOKEN);
        });
        dataSource.Ajax().Update(update =>
        {
            update.Url("?handler=Update");
            update.Type(HttpVerbs.Post);
            update.Data(JsFunctionName.GET_FORGERY_TOKEN);
        });
    }))
```