Export Grid to Excel with New Lines in the Data
Environment
Product | Progress® Kendo UI® Grid for jQuery |
Operating System | All |
Browser | All |
Browser Version | All |
Description
I have new lines in the header titles of my Grid. How can I keep the line breaks in the exported Excel file?
Solution
The line breaks in the HTML cannot be handled by Excel as new lines. That is why you have to replace the break line tag by using an \n
within the excelExport
event of the Grid.
<div id="example">
<div id="grid"></div>
<script>
$("#grid").kendoGrid({
toolbar: ["excel"],
excel: {
fileName: "Kendo UI Grid Export.xlsx",
proxyURL: "https://demos.telerik.com/kendo-ui/service/export",
filterable: true
},
dataSource: {
type: "odata",
transport: {
read: "https://demos.telerik.com/kendo-ui/service/Northwind.svc/Products"
},
schema:{
model: {
fields: {
UnitsInStock: { type: "number" },
ProductName: { type: "string" },
UnitPrice: { type: "number" },
UnitsOnOrder: { type: "number" },
UnitsInStock: { type: "number" }
}
}
},
pageSize: 7,
group: {
field: "UnitsInStock", aggregates: [
{ field: "ProductName", aggregate: "count" },
{ field: "UnitPrice", aggregate: "sum"},
{ field: "UnitsOnOrder", aggregate: "average" },
{ field: "UnitsInStock", aggregate: "count" }
]
},
aggregate: [
{ field: "ProductName", aggregate: "count" },
{ field: "UnitPrice", aggregate: "sum" },
{ field: "UnitsOnOrder", aggregate: "average" },
{ field: "UnitsInStock", aggregate: "min" },
{ field: "UnitsInStock", aggregate: "max" }
]
},
sortable: true,
pageable: true,
groupable: true,
filterable: true,
columnMenu: true,
reorderable: true,
excelExport: function(e) {
var sheet = e.workbook.sheets[0];
for (var rowIndex = 0; rowIndex < sheet.rows.length; rowIndex++) {
var row = sheet.rows[rowIndex];
for (var cellIndex = 0; cellIndex < row.cells.length; cellIndex ++) {
var cell =row.cells[cellIndex];
if(cell.value && cell.value.toString().indexOf("<br />") >= 0){
cell.value = cell.value.replace("<br />", "\n");
cell.wrap = true;
}
}
}
},
resizable: true,
columns: [
{ field: "ProductName", title: "Product <br />Name", aggregates: ["count"], footerTemplate: "Total Count: #=count#", groupFooterTemplate: "Count: #=count#" },
{ field: "UnitPrice", title: "Unit Price", aggregates: ["sum"] },
{ field: "UnitsOnOrder", title: "Units On Order", aggregates: ["average"], footerTemplate: "Average: #=average#",
groupFooterTemplate: "Average: #=average#" },
{ field: "UnitsInStock", title: "Units In Stock", aggregates: ["min", "max", "count"], footerTemplate: "Min: #= min # Max: #= max #",
groupHeaderTemplate: "Units In Stock: #= value # (Count: #= count#)" }
]
});
</script>
</div>