Export customize data to excel in kendo Grid

Question

I am using kendo grid plugin to show data. Have used the inbuilt functionality export to excel of kendo grid,to export grid data. http://demos.telerik.com/kendo-ui/grid/excel-export But I want to remove 2 columns before exporting the data to excel.

Please let me know how can I acheive it

Thanks, Nupur

Answer 1

you can try the excelExport event of grid options.

excelExport: function(e) {
    var sheet = e.workbook.sheets[0];
    for (var i = 0; i < sheet.rows.length; i++) {
        sheet.rows[i].cells.reverse();
        for (var ci = 0; ci < sheet.rows[i].cells.length; ci++) {
            sheet.rows[i].cells[ci].hAlign = "right";
        }
    }
},

http://docs.telerik.com/kendo-ui/controls/data-management/grid/excel-export#what-is-exported

Answer 2

Please find the fiddle here, hope it will help to solve your issue. You can read the documentation here.

<div id="grid" style="width: 900px"></div>
<script>
    $("#grid").kendoGrid({
        toolbar: ["Export"],
        dataSource: {
            type: "odata",
            transport: {
                read: "http://demos.telerik.com/kendo-ui/service/Northwind.svc/Products"
            },
            schema:{
                model: {
                    fields: {
                        UnitsInStock: { type: "number" },
                        ProductName: { type: "string" },
                        UnitPrice: { type: "number" },
                        UnitsOnOrder: { type: "number" },
                        UnitsInStock: { type: "number" }
                    }
                }
            },
            pageSize: 7,
            group: {
                field: "UnitsInStock", aggregates: [
                    { field: "ProductName", aggregate: "count" },
                    { field: "UnitPrice", aggregate: "sum"},
                    { field: "UnitsOnOrder", aggregate: "average" },
                    { field: "UnitsInStock", aggregate: "count" }
                ]
            },
            aggregate: [
                { field: "ProductName", aggregate: "count" },
                { field: "UnitPrice", aggregate: "sum" },
                { field: "UnitsOnOrder", aggregate: "average" },
                { field: "UnitsInStock", aggregate: "min" },
                { field: "UnitsInStock", aggregate: "max" }
            ]
        },
        sortable: true,
        pageable: true,
        groupable: true,
        filterable: true,
        columnMenu: true,
        reorderable: true,
        resizable: true,
        columns: [
            { width: 300, locked: true, field: "ProductName", title: "Product Name", aggregates: ["count"], footerTemplate: "Total Count: #=count#", groupFooterTemplate: "Count: #=count#" },
            { width: 300, field: "UnitPrice", title: "Unit Price", aggregates: ["sum"] },
            { width: 300, field: "UnitsOnOrder", title: "Units On Order", aggregates: ["average"], footerTemplate: "Average: #=average#",
                groupFooterTemplate: "Average: #=average#" },
            { width: 300, field: "UnitsInStock", title: "Units In Stock", aggregates: ["min", "max", "count"], footerTemplate: "Min: #= min # Max: #= max #",
            groupHeaderTemplate: "Units In Stock: #= value # (Count: #= count#)" }
        ]
    });

   $(".k-grid-Export").on('click', function(e){
    var grid = $("#grid").getKendoGrid();
    var rows = [{
      cells: [
        { value: "ProductName" },
        { value: "UnitPrice" }
      ]
    }];
    var trs = $("#grid").find('tr');
    for (var i = 0; i < trs.length; i++) {

        var dataItem = grid.dataItem(trs[i]);
        rows.push({
          cells: [
            { value: dataItem.ProductName },
            { value: dataItem.UnitPrice }
          ]
        })

    }
    excelExport(rows);
     e.preventDefault();
  });

  function excelExport(rows) {
    var workbook = new kendo.ooxml.Workbook({
      sheets: [
        {
          columns: [
            { autoWidth: true },
            { autoWidth: true }
          ],
          title: "Orders",
          rows: rows
        }
      ]
    });
    kendo.saveAs({dataURI: workbook.toDataURL(), fileName: "Test.xlsx"});

  }

</script>

[UPDATE] To Export only filtered rows then use the following code. Refer the fiddle for working example

 <div id="example">
<div id="grid" style="width: 900px"></div>
<script>
    $("#grid").kendoGrid({
        toolbar: ["Export"],
        dataSource: {
            type: "odata",
            transport: {
                read: "http://demos.telerik.com/kendo-ui/service/Northwind.svc/Products"
            },
            schema:{
                model: {
                    fields: {
                        UnitsInStock: { type: "number" },
                        ProductName: { type: "string" },
                        UnitPrice: { type: "number" },
                        UnitsOnOrder: { type: "number" },
                        UnitsInStock: { type: "number" }
                    }
                }
            },
            pageSize: 7,
            group: {
                field: "UnitsInStock", aggregates: [
                    { field: "ProductName", aggregate: "count" },
                    { field: "UnitPrice", aggregate: "sum"},
                    { field: "UnitsOnOrder", aggregate: "average" },
                    { field: "UnitsInStock", aggregate: "count" }
                ]
            },
            aggregate: [
                { field: "ProductName", aggregate: "count" },
                { field: "UnitPrice", aggregate: "sum" },
                { field: "UnitsOnOrder", aggregate: "average" },
                { field: "UnitsInStock", aggregate: "min" },
                { field: "UnitsInStock", aggregate: "max" }
            ]
        },
        sortable: true,
        pageable: true,
        groupable: true,
        filterable: true,
        columnMenu: true,
        reorderable: true,
        resizable: true,
        columns: [
            { width: 300, locked: true, field: "ProductName", title: "Product Name", aggregates: ["count"], footerTemplate: "Total Count: #=count#", groupFooterTemplate: "Count: #=count#" },
            { width: 300, field: "UnitPrice", title: "Unit Price", aggregates: ["sum"] },
            { width: 300, field: "UnitsOnOrder", title: "Units On Order", aggregates: ["average"], footerTemplate: "Average: #=average#",
                groupFooterTemplate: "Average: #=average#" },
            { width: 300, field: "UnitsInStock", title: "Units In Stock", aggregates: ["min", "max", "count"], footerTemplate: "Min: #= min # Max: #= max #",
            groupHeaderTemplate: "Units In Stock: #= value # (Count: #= count#)" }
        ]
    });

   $(".k-grid-Export").on('click', function(e){
    var grid = $("#grid").getKendoGrid();
    var rows = [{
      cells: [
        { value: "ProductName" },
        { value: "UnitPrice" }
      ]
    }];
    //var trs = $("#grid").find('tr');

     var trs = grid.dataSource;

    var filteredDataSource = new kendo.data.DataSource({
        data: trs.data(),
        filter: trs.filter()
    });

    filteredDataSource.read();
    var data = filteredDataSource.view();

    for (var i = 0; i < data.length; i++) {

        var dataItem =  data[i];

        rows.push({
          cells: [
            { value: dataItem.ProductName },
            { value: dataItem.UnitPrice }
          ]
        })

    }
    excelExport(rows);
     e.preventDefault();
  });

  function excelExport(rows) {
    var workbook = new kendo.ooxml.Workbook({
      sheets: [
        {
          columns: [
            { autoWidth: true },
            { autoWidth: true }
          ],
          title: "Orders",
          rows: rows
        }
      ]
    });
    kendo.saveAs({dataURI: workbook.toDataURL(), fileName: "Test.xlsx"});

  }

</script>