I am new to using the Kendo UI Grid and have multiple pages with grids containing numerous rows of data. Each row has drill-down data (child data). When I use the built-in Kendo excel export method, it only exports the master data and not the child data. I have been researching different methods for customizing the export to include the child data, but so far, I haven’t had any success. I’ve read various documentations that mentions how the Kendo libraries and the amount of data play a role in this, so I’m uncertain if it’s even possible. Also, Kendo have provided an example, which I have applied to my example but seems to not work or be unresponsive. If anyone has experience with this and knows a method that could work with my structure, I would greatly appreciate the help.
Here is the current layout of the grid on my webpage:
Kendo’s Example:
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8"/>
<title>Kendo UI Snippet</title>
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/themes/8.0.1/default/default-ocean-blue.css"/>
<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
<script src="https://unpkg.com/jszip/dist/jszip.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2024.2.514/js/kendo.all.min.js"></script>
</head>
<body>
<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"
});
// When using jsZip version 3.x use the following as the synchronous methods were deprecated
//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 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>
</body>
</html>
My current example (using Kendo’s built in export method):
this.UserAbsence = () => {
return {
height: '55em',
selectable: 'row',
allowCopy: true,
scrollable: true,
sortable: true,
resizable: true,
groupable: false,
filterable: false,
pageable: this.pageableOptions,
noRecords: true,
messages: { noRecords: this.messageNoRecords },
dataBound: this.ResetScroll,
dataSource: {
transport: {
read: {
type: 'POST',
dataType: 'json',
url: this.Url,
data: this.ReadData,
},
},
schema: {
data: 'Data',
total: 'Total',
errors: 'Errors',
},
sort: [
{ field: 'LastName', dir: 'asc' },
],
},
columns: [
{ field: 'LastName', title: 'Employee Last Name', width: 150 },
{ field: 'FirstName', title: 'Employee First Name', width: 150 },
{ field: 'MidInit', title: 'Mid Init', width: 40 },
{ field: 'Calendar', title: 'Calendar', width: 175 },
],
detailTemplate: '<div class="detail-grid" style="width: 1177px"></div>',
detailInit: (e) => {
let pkEmployee = e.data.get('PK_Employee');
let detailGrid = e.detailRow.find('.detail-grid');
detailGrid.kendoGrid({
sortable: true,
resizable: true,
scrollable: false,
dataSource: {
transport: {
read: {
type: 'GET',
dataType: 'json',
url: this.ReadUrl + 'Balances',
data: { fiscalYear: this.fiscalYear, pkEmployee: pkEmployee, districtNumber: this.disNumber },
},
},
schema: {
data: 'Data',
total: 'Total',
errors: 'Errors',
},
sort: [
{ field: 'AbsenceCode', dir: 'asc' },
],
},
columns: [
{ field: 'AbsenceCode', title: 'Absence Code', width: 130 },
{ field: 'Description', title: 'Description', width: 300 },
Object.assign({ field: 'CarryoverBalance', title: 'Carryover Balance', format: '{0
}', width: 120 }, this.colTextEnd),
Object.assign({ field: 'AvailableBalance', title: 'Available Balance', format: '{0
}', width: 120 }, this.colTextEnd),
],
detailTemplate: '<div class="detail-grid"></div>',
detailInit: (e) => {
let pkEmployee = e.data.get('PK_Employee');
let absenceCode = e.data.get('AbsenceCode');
let detailGrid = e.detailRow.find('.detail-grid');
detailGrid.kendoGrid({
sortable: false,
resizable: true,
scrollable: false,
dataSource: {
transport: {
read: {
type: 'GET',
dataType: 'json',
url: this.ReadUrl + 'AbsenceCodeLog',
data: { fiscalYear: this.fiscalYear, pkEmployee: pkEmployee, absenceCode: absenceCode, disNumber: this.disNumber },
},
},
schema: {
data: 'Data',
total: 'Total',
errors: 'Errors',
model: {
fields: {
FromDate: { type: 'date' },
ToDate: { type: 'date' },
},
},
},
},
columns: [
{ field: 'Description', title: 'Description', width: 300 },
Object.assign({ field: 'RunningBalance', title: 'Running Balance', format: '{0
}', width: 100 }, this.colTextEnd),
],
});
},
});
},
toolbar: ['excel', 'pdf'],
excel: { allPages: true, fileName: this.GetFileName('xlsx') },//Need to update this export method
pdf: { allPages: true, fileName: this.GetFileName('pdf'), title: this.reportTitle, author: 'User1' },
};
};