Modify the Excel Files Exported from the PivotGrid
Environment
| Product | Progress® Kendo UI® PivotGrid for jQuery |
| Operating System | Windows 10 64bit |
| Browser | Google Chrome |
| Browser Version | 61.0.3163.100 |
Description
How can I apply different style and value changes to a Kendo UI PivotGrid?
Solution
You can apply different style and value changes to a Kendo UI PivtoGrid exported to Excel by modifying the generated Workbook object.
The following example demonstrates how to achieve this behavior.
<div id="example">
<button id="export">Export</button>
<div id="pivotgrid"></div>
<script>
$(document).ready(function () {
var pivotgrid = $("#pivotgrid").kendoPivotGrid({
excel: {
fileName: "Kendo UI PivotGrid Export.xlsx",
proxyURL: "//demos.telerik.com/kendo-ui/service/export",
filterable: true
},
excelExport: function(e) {
var sheet = e.workbook.sheets[0];
var rows = sheet.rows;
var rowIdx, colIdx, cells, cell;
for (rowIdx = 0; rowIdx < rows.length; rowIdx++) {
if (rows[rowIdx].type === "data") {
cells = rows[rowIdx].cells;
for (colIdx = sheet.freezePane.colSplit; colIdx < cells.length; colIdx++) {
cell = cells[colIdx];
cell.background = "#aabbcc";
cell.value = kendo.toString(cell.value, "c");
}
}
}
},
dataCellTemplate: $("dataCellTemplate").html(),
filterable: true,
sortable: true,
columnWidth: 200,
height: 580,
dataSource: {
type: "xmla",
columns: [{ name: "[Date].[Calendar]", expand: true }, { name: "[Product].[Category]" } ],
rows: [{ name: "[Geography].[City]" }],
measures: ["[Measures].[Reseller Freight Cost]"],
transport: {
connection: {
catalog: "Adventure Works DW 2008R2",
cube: "Adventure Works"
},
read: "//demos.telerik.com/olap/msmdpump.dll"
},
schema: {
type: "xmla"
},
error: function (e) {
alert("error: " + kendo.stringify(e.errors[0]));
}
}
}).data("kendoPivotGrid");
$("#export").click(function() {
pivotgrid.saveAsExcel();
});
});
</script>
</div>