DEV Community

iainrough
iainrough

Posted on

Telerik Grid Love and Hate: Exporting Client Templates to Excel.

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:


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

Enter fullscreen mode Exit fullscreen mode

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


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

        public string Value { get; set; }
    }

Enter fullscreen mode Exit fullscreen mode

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

So lets add the excel export:


@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))

Enter fullscreen mode Exit fullscreen mode

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 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 of the Jquery version of the grid using a custom block of 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 || "";
          }
        }
      }


Enter fullscreen mode Exit fullscreen mode

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

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

Enter fullscreen mode Exit fullscreen mode

Tested again and bingo it works.

Complete grid code:


@(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);
        });
    }))
Enter fullscreen mode Exit fullscreen mode

Top comments (0)