Exporting Detail Grids and Merging their Workbooks with the Parent Grid Workbook.
Environment
| Product Version | 2022.1.412 |
| Product | Grid for ASP.NET Core |
Description
How can I export a parent Grid and all detail Grids to Excel when working with Telerik UI for ASP.NET Core?
Solution
The example below extends the Grid Hierarchy Demo to demonstrate how to export to Excel the data from all detail Grids and merge their workbooks with the parent Grid workbook.
The example relies on the following key steps:
- Add a Toolbar with an Export to Excel Button.
- Add an event handler to the client-side
ExcelExportevent. The event is then prevented to avoid exporting only the data in the parent grid. - Fetch the data for all detail Grids and append to the corresponding parent row.
@(Html.Kendo().Grid<Kendo.Mvc.Examples.Models.EmployeeViewModel>()
.Name("grid")
.ToolBar(t=>t.Excel())
.Events(events => events.ExcelExport("excelExport"))
...// omitted for brevity
)
<script>
// used to wait for the children to finish async export
var detailExportPromises = [];
function excelExport(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);
}
// 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 doesn't need to be updated
for (var i = detailExports.length - 1; i >= 0; i--) {
var masterRowIndex = detailExports[i].masterRowIndex + 1;
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"
});
});
}
function exportChildData(EmployeeID, rowIndex) {
var deferred = $.Deferred();
detailExportPromises.push(deferred);
var rows = [{
cells: [
{ value: "OrderID" },
{ value: "ShipCountry" },
{ value: "ShipAddress" },
{ value: "ShipName" }
]
}];
var baseUrl = "@Url.Action("HierarchyBinding_Orders", "Grid")";
baseUrl+= "?employeeID="+EmployeeID
var exporter = new kendo.ExcelExporter({
columns: [
{ field: "OrderID" },
{ field: "ShipCountry" },
{ field: "ShipAddress" },
{ field: "ShipName" }
],
dataSource: {
type: "aspnetmvc-ajax",
transport: {
read: {
url: baseUrl
}
},
schema: {
data: "Data",
total: "Total",
error: "Errors"
}
}
});
exporter.workbook().then(function (book, data) {
deferred.resolve({
masterRowIndex: rowIndex,
sheet: book.sheets[0]
});
});
}
</script>
For a runnable example with the code above, refer to the REPL project on exporting a parent Grid and all detail Grids to Excel.