Edit this page

Export Grid to Excel with New Lines in the Data

Environment

Product Grid for ProgressĀ® Kendo UIĀ®
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>
Is this article helpful? Yes / No
Thank you for your feedback!

Give article feedback

Tell us how we can improve this article

close
Dummy