PivotGrid Customize Exported Excel File
Environment
Product | Progress® Kendo UI® PivotGrid for jQuery |
Description
I am playing around with the Excel export of the PivotGrid and would like to know how to:
- Add borders to the cells
- Change the background color
- Align the text of cells that span across several rows to the top
Solution
The PivotGrid triggers its excelExport
event that features the generated kendo.ooxml.Workbook
. You can use it to accomplich the desired outcome by following the steps below:
- Loop the sheet rows and columns
- Use the
cells.borderTop
,cells.borderBottom
,cells.borderRight
andcells.borderLeft
size
andcolor
properties to assign the desired border width and color - Use the
cells.background
property to assign the new color - Check if the cell has a
rowSpan
greater than 1 and use thecells.verticalAlign
property to align the text as needed
excelExport: function (e) {
var sheet = e.workbook.sheets[0];
for (var i = 0; i < sheet.rows.length; i++) {
for (var ci = 0; ci < sheet.rows[i].cells.length; ci++) {
var cell = sheet.rows[i].cells[ci];
// add borders
cell.borderTop = { color: "black", size: 1 };
cell.borderRight = { color: "black", size: 1 };
cell.borderBottom = { color: "black", size: 1 };
cell.borderLeft = { color: "black", size: 1 };
// change the cell background
cell.background == "#dfdfdf" ? cell.background = "#ffffff": cell.background = "#0080ff";
// align cells top
if(cell.rowSpan > 1){
cell.verticalAlign = "top";
}
}
}
},
<script src="https://unpkg.com/jszip/dist/jszip.min.js"></script>
<div id="example">
<button id="export" class="k-button k-button-icontext hidden-on-narrow"><span class="k-icon k-i-excel"></span>Export to Excel</button>
<div id="pivotgrid" class="hidden-on-narrow"></div>
<div class="responsive-message"></div>
<script>
$(document).ready(function () {
var pivotgrid = $("#pivotgrid").kendoPivotGrid({
excelExport: function (e) {
var sheet = e.workbook.sheets[0];
for (var i = 0; i < sheet.rows.length; i++) {
for (var ci = 0; ci < sheet.rows[i].cells.length; ci++) {
var cell = sheet.rows[i].cells[ci];
// add borders
cell.borderTop = { color: "black", size: 1 };
cell.borderRight = { color: "black", size: 1 };
cell.borderBottom = { color: "black", size: 1 };
cell.borderLeft = { color: "black", size: 1 };
// change cell background
cell.background == "#dfdfdf" ? cell.background = "#ffffff": cell.background = "#0080ff";
// align cells top
if(cell.rowSpan > 1){
cell.verticalAlign = "top";
}
}
}
},
excel: {
fileName: "Kendo UI PivotGrid Export.xlsx",
proxyURL: "https://demos.telerik.com/kendo-ui/service/export",
filterable: true
},
filterable: true,
sortable: true,
columnWidth: 200,
height: 580,
dataSource: {
type: "xmla",
columns: [{ name: "[Date].[Calendar]", expand: true }, { name: "[Product].[Category]" } ],
rows: [{ name: "[Geography].[City]", expand: true }],
measures: ["[Measures].[Reseller Freight Cost]"],
transport: {
connection: {
catalog: "Adventure Works DW 2008R2",
cube: "Adventure Works"
},
read: "https://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>
<style>
#export
{
margin: 0 0 10px 1px;
}
</style>
</div>