Export the Entire Grid Data to Excel

Environment

Product

Progress® Kendo UI® Grid for jQuery

Description

How can I export to Excel the complete (unfiltered) content of a filtered Grid instead of exporting only the displayed (filtered) data?

Solution

Use either of the following approaches:

• The built-in behavior of the Grid is designed to export only the its current state—filtered, sorted, paged, and grouped. This means that to export all the data (without the query), you need to intercept the excelExport event and modify the created workbook.

  js

  excelExport: function(e){
    var sheet = e.workbook.sheets[0];
    var data = grid.dataSource.data();
    var gridColumns = grid.columns;
    var columns = gridColumns.map(function(col){
        return {
          value: col.title ? col.title : col.field,
          autoWidth:true,
          background: "#7a7a7a",
          color: "#fff"
        };
    });
  
    var rows = [{cells:columns, type: "header"}];
  
    for (var i = 0; i < data.length; i++){
      var rowCells = [];
      for(var j=0;j < gridColumns.length;j++){
        var cellValue = data[i][gridColumns[j].field];
        rowCells.push({value: cellValue});
      }
      rows.push({cells: rowCells, type: "data"});
    }
    sheet.rows = rows;
  }

  The following example demonstrates the implementation of the approach.

  <div id="example">
        <div id="grid"></div>
  
        <script>
          $(document).ready(function () {
            var dataSource = new kendo.data.DataSource({
              transport: {
                read:  {
                  url: "https://demos.telerik.com/service/v2/core/Products"
                },
                parameterMap: function(options, operation) {
                  if (operation !== "read" && options.models) {
                    return kendo.stringify(options.models);
                  }
                }
              },
              pageSize: 20,
              schema: {
                model: {
                  id: "ProductID",
                  fields: {
                    ProductID: { editable: false, nullable: true },
                    ProductName: { validation: { required: true } },
                    UnitPrice: { type: "number", validation: { required: true, min: 1} },
                    Discontinued: { type: "boolean" },
                    UnitsInStock: { type: "number", validation: { min: 0, required: true } }
                  }
                }
              }
            });
  
            var grid = $("#grid").kendoGrid({
              dataSource: dataSource,
              excel:{
                allPages:true
              },
              pageable: true,
              height: 550,
              toolbar:["excel"],
              columns: [
                "ProductName",
                { field: "UnitPrice", title: "Unit Price", format: "{0:c}", width: "120px" },
                { field: "UnitsInStock", title:"Units In Stock", width: "120px" },
                { field: "Discontinued", width: "120px" }],
              filterable: true,
              excelExport: function(e){
                var sheet = e.workbook.sheets[0];
                var data = grid.dataSource.data();
                var gridColumns = grid.columns;
                var columns = gridColumns.map(function(col){
                    return {
                      value: col.title ? col.title : col.field,
                      autoWidth:true,
                      background: "#7a7a7a",
                      color: "#fff"
                    };
                });
  
                var rows = [{cells:columns, type: "header"}];
  
                for (var i = 0; i < data.length; i++){
                  var rowCells = [];
                  for(var j=0;j < gridColumns.length;j++){
                    var cellValue = data[i][gridColumns[j].field];
                    rowCells.push({value: cellValue});
                  }
                  rows.push({cells: rowCells, type: "data"});
                }
                sheet.rows = rows;
              }
            }).data("kendoGrid");
          });
        </script>
      </div>

• Alternatively, create another hidden Grid with the same data source and with its autoBind set to false. Then, prevent the excelExport event of the filtered Grid and trigger the export of the "hidden" Grid instead of using the saveAsExcel() method. The code looks similar to:

  js

  excelExport: function(e){
    e.preventDefault();
    hiddenGrid.dataSource.read().then(function(){
      hiddenGrid.saveAsExcel();
    });
  }

  The following example demonstrates the implementation of the approach.

  <div id="gridOne"></div>
  <div id="gridTwo"></div>
  <script>
    var ds = new kendo.data.DataSource({
      data: [
        { name: "Jane Doe", age: 30 },
        { name: "John Doe", age: 33 },
        { name: "Tim Doe", age: 13 },
        { name: "Mary Doe", age: 23 },
        { name: "August Doe", age: 34 },
        { name: "Andrew Doe", age: 44 }
    	],
      filter: [{field:"name", operator:"contains", value:"a"}]
    })
  $("#gridOne").kendoGrid({
    filterable:true,
    toolbar:["excel"],
    columns: [
      { field: "name" },
      { field: "age" }
    ],
    dataSource: ds,
    excelExport: function(e){
      e.preventDefault();
      hiddenGrid.dataSource.read().then(function(){
        hiddenGrid.saveAsExcel();
      });
    }
  });
  
  var hiddenGrid = $("#gridTwo").kendoGrid({
        autoBind:false,
    		excel: {allPages:true},
        dataSource:ds.data()
      }).data("kendoGrid");
  </script>
    <style>
      #gridTwo{
        display:none;
      }
    </style>