New to Kendo UI for jQuery? Download free 30-day trial

Insert New Column When Exporting Grid to Excel

Product Progress® Kendo UI® Grid for jQuery

Description

How can I implement a functionality for inserting a new column when exporting the Grid to Excel?

Solution

  • In the excelExport event handler, you can use the JavaScript splice() method for inserting rows/columns to the exported Grid data:
excelExport: function(e) {
    var sheet = e.workbook.sheets[0];
    sheet.columns.splice(1, 0, {width: 10, autoWidth: true})  //add a new column

    for (var rowIndex = 0; rowIndex < sheet.rows.length; rowIndex++) {
        var row = sheet.rows[rowIndex]; 
        row.cells.splice(1, 0, {value: ''}) //add cells to the new column
    }

    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" //set different background to the header cells
            row.cells[cellIndex].background = isHeaderCellColor;  
            var vvalue = String(row.cells[cellIndex].value)
            var test = vvalue.includes('<')

            if(test){
                row.cells[cellIndex].value = simpleHtmlDecode(row.cells[cellIndex].value);
            }  
        }
    }
}

The following example demonstrates a full implementation of the described approach:

<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]; 
                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"
                    row.cells[cellIndex].background = isHeaderCellColor;  
                    var vvalue = String(row.cells[cellIndex].value)
                    var test = vvalue.includes('<')               

                    if(test){
                        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>

See Also

In this article