Export Master and Detail Grids to Excel in Separate Sheets
Environment
Product | Progress® Kendo UI® Grid for jQuery | Progress® Kendo UI® Excel Export for jQuery |
Description
I implemented the Excel export of a master-detail Grid based on the URL by following this example. However, users want me to change the way the data is exported for each summary row and how the details are injected because this behavior prevents them from instantly checking the pivots and making a detailed analysis.
How can I have separate sheets for the rows of the master Grid and for all detailed records of each row?
Solution
- Make a single call to read all the data.
- Obtain only the sheet configuration of the generated result and assign it to a variable.
- Obtain only the sheet from the event data of the
excelExpot
event (by default,e.workbook
is the master Grid workbook) and assign it to a variable. - Create a new workbook and pass the sheets in the
sheet
configuration property as an array with the variables from the two previous steps.
<div id="grid"></div>
<script>
// used to wait for the children to finish 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
},
excelExport: function(e) {
e.preventDefault();
var workbook = e.workbook;
detailExportPromises = [];
var masterData = e.data;
exportChildData();
$.when.apply(null, detailExportPromises)
.then(function() {
// get the export results
var detailExports = $.makeArray(arguments);
var sheets = [workbook.sheets[0], detailExports[0].sheet ];
//add sheets name
sheets[0].title = "Employees";
sheets[1].title = "Orders";
var headerColumnsCount = sheets[1].rows[0].cells.length - 1;
// add filter for second sheet
sheets[1].filter = {
from: 0,
to: headerColumnsCount
};
// save the workbook
kendo.saveAs({
dataURI: new kendo.ooxml.Workbook({
sheets: sheets
}).toDataURL(),
fileName: "Employees and Orders.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() {
var deferred = $.Deferred();
detailExportPromises.push(deferred);
var exporter = new kendo.ExcelExporter({
columns: [{
field: "EmployeeID",
width: "100px"
}, {
field: "OrderID",
width: "100px"
}, {
field: "ShipCountry",
width: "100px"
}, {
field: "ShipAddress"
},{
field: "ShipName"
}],
dataSource: dataSource
});
exporter.workbook().then(function(book, data) {
deferred.resolve({
sheet: book.sheets[0]
});
});
}
function detailInit(e) {
$("<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 }
},
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>