New to Kendo UI for jQuery? Download free 30-day trial

Export Detail Grids

Environment

Product Progress® Kendo UI® Grid for jQuery
Operating System Windows 10 64bit
Preferred Language JavaScript

Description

How can I export master and detail Kendo UI Grids to Excel?

Solution

The following examples demonstrate how to export detail Grids to Excel and merge their workbooks with the master Grid workbook.

To get the workbook of the detail Grids, the demos use the excelExport event. This event is prevented to avoid the saving of an Excel file for each detail Grid. For more information on how Excel documents work, refer to the introductory help topic on Excel.

The following example demonstrates how to export a detail Grid to Excel including its all pages and details.

<div id="grid"></div>
<script>
  // Used to wait for the children to finish the async export.
  var detailExportPromises = [];


  var dataSource = new kendo.data.DataSource({
    type: "odata",
    transport: {
      read: "https://demos.telerik.com/kendo-ui/service/Northwind.svc/Orders"
    }
  });

  dataSource.read();

  $("#grid").kendoGrid({
    toolbar: ["excel"],
    dataSource: {
      type: "odata",
      transport: {
        read: "https://demos.telerik.com/kendo-ui/service/Northwind.svc/Employees"
      },
      pageSize: 6,
      serverPaging: true
    },
    height: 600,
    pageable: true,
    detailInit: detailInit,
    excel: {
      allPages: true
    },
    dataBound: function() {
      detailExportPromises = [];
      this.expandRow(this.tbody.find("tr.k-master-row").first());
    },
    excelExport: function(e) {
      e.preventDefault();

      var workbook = e.workbook;

      detailExportPromises = [];

      var masterData = e.data;

      for (var rowIndex = 0; rowIndex < masterData.length; rowIndex++) {
        exportChildData(masterData[rowIndex].EmployeeID, rowIndex);
      }

      $.when.apply(null, detailExportPromises)
      .then(function() {
        // Get the export results.
        var detailExports = $.makeArray(arguments);

        // Sort by masterRowIndex.
        detailExports.sort(function(a, b) {
          return a.masterRowIndex - b.masterRowIndex;
        });

        // Add an empty column.
        workbook.sheets[0].columns.unshift({
          width: 30
        });

        // Prepend an empty cell to each row.
        for (var i = 0; i < workbook.sheets[0].rows.length; i++) {
          workbook.sheets[0].rows[i].cells.unshift({});
        }

        // Merge the detail export sheet rows with the master sheet rows.
        // Loop backwards so the masterRowIndex does not need to be updated.
        for (var i = detailExports.length - 1; i >= 0; i--) {
          var masterRowIndex = detailExports[i].masterRowIndex + 1; // compensate for the header row

          var sheet = detailExports[i].sheet;

          // Prepend an empty cell to each row.
          for (var ci = 0; ci < sheet.rows.length; ci++) {
            if (sheet.rows[ci].cells[0].value) {
              sheet.rows[ci].cells.unshift({});
            }
          }

          // Insert the detail sheet rows after the master row.
          [].splice.apply(workbook.sheets[0].rows, [masterRowIndex + 1, 0].concat(sheet.rows));
        }

        // Save the workbook.
        kendo.saveAs({
          dataURI: new kendo.ooxml.Workbook(workbook).toDataURL(),
          fileName: "Export.xlsx"
        });


      });
    },
    columns: [
      { field: "FirstName", title: "First Name", width: "110px" },
      { field: "LastName", title: "Last Name", width: "110px" },
      { field: "Country", width: "110px" },
      { field: "City", width: "110px" },
      { field: "Title" }
    ]
  });

  function exportChildData(EmployeeID, rowIndex) {
    var deferred = $.Deferred();

    detailExportPromises.push(deferred);

    var rows = [{
      cells: [
        // First cell.
        { value: "OrderID" },
        // Second cell.
        { value: "Freight" },
        // Third cell.
        { value: "ShipName" },
        // Fourth cell.
        { value: "OrderDate" },
        // Fifth cell.
        { value: "ShipCity" }
      ]
    }];

    dataSource.filter({ field: "EmployeeID", operator: "eq", value: EmployeeID});

    var exporter = new kendo.ExcelExporter({
      columns: [{
        field: "OrderID"
      }, {
        field: "Freight"
      }, {
        field: "ShipName"
      }, {
        field: "ShipCity"
      }],
      dataSource: dataSource
    });

    exporter.workbook().then(function(book, data) {
      deferred.resolve({
        masterRowIndex: rowIndex,
        sheet: book.sheets[0]
      });
    });
  }

  function detailInit(e) {
    // Initiallize a new jQuery Deferred https://api.jquery.com/jQuery.Deferred/
    // var deferred = $.Deferred();

    // Get the index of the master row
    // var masterRowIndex = e.masterRow.index(".k-master-row");

    // Add the deferred to the list of promises
    // detailExportPromises.push(deferred);

    $("<div/>").appendTo(e.detailCell).kendoGrid({
      dataSource: {
        type: "odata",
        transport: {
          read: "https://demos.telerik.com/kendo-ui/service/Northwind.svc/Orders"
        },
        serverPaging: true,
        serverSorting: true,
        serverFiltering: true,
        pageSize: 10,
        filter: { field: "EmployeeID", operator: "eq", value: e.data.EmployeeID }
      },
      excelExport: function (e) {
        // Prevent the saving of the file.
        e.preventDefault();

        // Resolve the deferred
        // deferred.resolve({
        //  masterRowIndex: masterRowIndex,
        //  sheet: e.workbook.sheets[0]
        //});
      },
      scrollable: false,
      pageable: true,
      columns: [
        { field: "OrderID", width: "70px" },
        { field: "ShipCountry", title:"Ship Country", width: "110px" },
        { field: "ShipAddress", title:"Ship Address" },
        { field: "ShipName", title: "Ship Name", width: "300px" }
      ]
    });
  }
</script>

The following example demonstrates how to export a detail Grid to Excel including the current page only.

<div id="grid"></div>
<script>
  // Used to wait for the children to finish the async export.
  var detailExportPromises = [];

  $("#grid").kendoGrid({
    toolbar: ["excel"],
    dataSource: {
      type: "odata",
      transport: {
        read: "https://demos.telerik.com/kendo-ui/service/Northwind.svc/Employees"
      },
      pageSize: 6,
      serverPaging: true
    },
    height: 600,
    pageable: true,
    detailInit: detailInit,
    dataBound: function() {
      detailExportPromises = [];
      this.expandRow(this.tbody.find("tr.k-master-row").first());
    },
    excelExport: function(e) {
      // Prevent saving the file because you will update the workbook.
      e.preventDefault();

      var workbook = e.workbook;

      // Export all detail grids.
      $("#grid [data-role=grid]").each(function() {
        $(this).data("kendoGrid").saveAsExcel();
      });

      // Wait for all detail grids to finish exporting.
      $.when.apply(null, detailExportPromises)
      .then(function() {
        // Get the export results.
        var detailExports = $.makeArray(arguments);

        // Sort by masterRowIndex.
        detailExports.sort(function(a, b) {
          return a.masterRowIndex - b.masterRowIndex;
        });

        // Add an empty column.
        workbook.sheets[0].columns.unshift( { width: 30 } );

        // Prepend an empty cell to each row.
        for (var i = 0; i < workbook.sheets[0].rows.length; i++) {
          workbook.sheets[0].rows[i].cells.unshift( { });
        }

        // Merge the detail export sheet rows with the master sheet rows.
        // Loop backwards so the masterRowIndex does not need to be updated.
        for (var i = detailExports.length - 1; i >= 0; i--) {
          var masterRowIndex = detailExports[i].masterRowIndex + 1; // Compensate for the header row.

          var sheet = detailExports[i].sheet;

          // prepend an empty cell to each row
          for (var ci = 0; ci < sheet.rows.length; ci++) {
            if (sheet.rows[ci].cells[0].value) {
              sheet.rows[ci].cells.unshift( { } );
            }
          }

          // Insert the detail sheet rows after the master row.
          [].splice.apply(workbook.sheets[0].rows, [masterRowIndex + 1, 0].concat(sheet.rows));
        }

        // Save the workbook.
        new kendo.ooxml.Workbook(workbook).toDataURLAsync().then(function(data) {
            kendo.saveAs($.extend({
              dataURI: data,
              fileName: "Export.xlsx"
            }));
        });
      });
    },
    columns: [
      { field: "FirstName", title: "First Name", width: "110px" },
      { field: "LastName", title: "Last Name", width: "110px" },
      { field: "Country", width: "110px" },
      { field: "City", width: "110px" },
      { field: "Title" }
    ]
  });

  function detailInit(e) {
    // Initialize a new jQuery Deferred https://api.jquery.com/jQuery.Deferred/
    var deferred = $.Deferred();

    // Get the index of the master row.
    var masterRowIndex = e.masterRow.index(".k-master-row");

    // Add the deferred to the list of promises.
    detailExportPromises.push(deferred);

    $("<div/>").appendTo(e.detailCell).kendoGrid({
      dataSource: {
        type: "odata",
        transport: {
          read: "https://demos.telerik.com/kendo-ui/service/Northwind.svc/Orders"
        },
        serverPaging: true,
        serverSorting: true,
        serverFiltering: true,
        pageSize: 10,
        filter: { field: "EmployeeID", operator: "eq", value: e.data.EmployeeID }
      },
      excelExport: function (e) {
        // Prevent the saving of the file.
        e.preventDefault();

        // Resolve the deferred.
        deferred.resolve({
          masterRowIndex: masterRowIndex,
          sheet: e.workbook.sheets[0]
        });
      },
      scrollable: false,
      pageable: true,
      columns: [
        { field: "OrderID", width: "70px" },
        { field: "ShipCountry", title:"Ship Country", width: "110px" },
        { field: "ShipAddress", title:"Ship Address" },
        { field: "ShipName", title: "Ship Name", width: "300px" }
      ]
    });
  }
</script>

See Also

In this article
Related articles
Not finding the help you need? Improve this article