Kendo UI Grid - Excel export with hidden columns and custom formatting
I'm trying to export to excel using the Grid component's built-in support, with custom cell formatting applied as shown in the following Telerik documentation:
http://docs.telerik.com/kendo-ui/controls/data-management/grid/how-to/excel/cell-format
There is a fairly obvious problem with the approach of using hardcoded row/cell indices in the export when exporting a grid showing previously hidden columns - the best way to reproduce is to quote this jsfiddle:
https://jsfiddle.net/3anqpnqt/1/
- run violin
- Click Export to Excel - Comply with Custom Number Formats
- Unhide the subcategory column (using the column menu)
- Click export to excel - note the custom number format on column 2 (now "Subcategory")
Refer to this code in the fiddle:
$("#grid").kendoGrid({
toolbar: ["excel"],
excel: {
fileName: "Grid.xlsx",
filterable: true
},
columns: [
{ field: "productName" },
{ field: "category" },
{ field: "subcategory", hidden: true },
{ field: "unitPrice"}
],
dataSource: [
{ productName: "Tea", category: "Beverages", subcategory: "Bev1", unitPrice: 1.5 },
{ productName: "Coffee", category: "Beverages", subcategory: "Bev2", unitPrice: 5.332 },
{ productName: "Ham", category: "Food", subcategory: "Food1", unitPrice: -2.3455 },
{ productName: "Bread", category: "Food", subcategory: "Food2", unitPrice: 6 }
],
columnMenu: true,
excelExport: function(e) {
var sheet = e.workbook.sheets[0];
for (var rowIndex = 0; rowIndex < sheet.rows.length; rowIndex++) {
var row = sheet.rows[rowIndex];
var numericFormat = "#,##0.00;[Red](#,##0.00);-";
for (var cellIndex = 0; cellIndex < row.cells.length; cellIndex++) {
var cell = row.cells[cellIndex];
if (row.type === "data") {
if (cellIndex == 2) { // how are we able to identify the column without using indexes?
cell.format = numericFormat;
cell.hAlign = "right";
}
}
}
}
}
});
All I need to do is identify the cell as 'unitPrice' and apply the formatting, but checking the object model in the excelExporthandler doesn't give me any way to make this link. In my actual application, I have several custom formats (percent, n0, n2, etc.) that can be applied, so it's not as simple as doing it $.isNumeric(cell.value).
renew
I also need to use the solution to handle columns/row groups, which generate additional header rows/columns in the Excel model.
It looks like row[0] is the header row, so you can try changing
if (cellIndex == 2) {
arrive
if (sheet.rows[0].cells[cellIndex].value == "unitPrice") {
edit:
Seems to work with column groups : https://jsfiddle.net/dwosrs0x/
renew:
The object model of the worksheet is not the clearest. The first row does seem to be the "main" header row in the various cases I've looked at. This seems to work if the unitPrice is not in the grouping. If the unitPrice is in the group, it may be more complicated to involve the group header (row[1]). The challenge is figuring out where the desired column will end up occupying.
var header = sheet.rows[0];
var upIndex = -1;
var upFound = false;
for (var cellIndex = 0; cellIndex < header.cells.length; cellIndex++) {
if ('colSpan' in header.cells[cellIndex])
upIndex = upIndex + header.cells[cellIndex].colSpan;
else
upIndex = upIndex + 1;
if (header.cells[cellIndex].value == "unitPrice") { // wot we want
upFound = true;
break;
}
}
for (var rowIndex = 0; rowIndex < sheet.rows.length; rowIndex++) {
var row = sheet.rows[rowIndex];
if (row.type === "data" && upFound) {
var cell = row.cells[upIndex];
cell.format = numericFormat;
cell.hAlign = "right";
}
}
Fiddle with the group - https://jsfiddle.net/dwosrs0x/4/
Fiddle with a simple grid (prove it still works) - https://jsfiddle.net/gde4nr0y/1/
It definitely has a "stiff" flavor to it.