How To Add а Column While Exporting Grid to Excel
Environment
Product | Progress® Kendo UI® Grid for jQuery |
Description
When export the Grid to Excel I would like to add an empty column on a specific position. How can I do that?
Solution
- In the
excelExport
event handler you can use the JavaScriptsplice
method to add rows/columns to the exported data.
<div id="grid"></div>
<script>
$("#grid").kendoGrid({
toolbar: ["excel"],
excelExport: function(e) {
var sheet = e.workbook.sheets[0];
sheet.columns.splice(1, 0, {width: 10, autoWidth: true})
for (var rowIndex = 0; rowIndex < sheet.rows.length; rowIndex++) {
var row = sheet.rows[rowIndex];
//add a column on specific position
row.cells.splice(1, 0, {value: ''})
}
for (var rowIndex = 0; rowIndex < sheet.rows.length; rowIndex++) {
var row = sheet.rows[rowIndex];
for (var cellIndex = 0; cellIndex < row.cells.length; cellIndex ++) {
var isHeaderCellColor = rowIndex == 0 ? '#545454' : "#efefef"
//change the color if the cell is part of the header
row.cells[cellIndex].background = isHeaderCellColor;
var vvalue = String(row.cells[cellIndex].value)
//decode content if it is html
var cellV = vvalue.includes('<')
if(cellV){
row.cells[cellIndex].value = simpleHtmlDecode(row.cells[cellIndex].value);
}
}
}
},
dataSource: [
{"ProductName": "<span class='encode-class'><i>Milk</i></span>", UnitsInStock: 18},
{"ProductName": "<span class='encode-class'><b>Strawberry</b></span>", UnitsInStock: 15},
{"ProductName": "Sugar", UnitsInStock: 25}
],
pageable: true,
columns: [
{ width: 300, field: "ProductName", title: "Product Name", encoded: false },
{ field: "UnitsInStock", title: "Units" }
]
});
function simpleHtmlDecode(encodedStr) {
return $(encodedStr).text();
}
</script>