Kendo Grid Export To Excel Currency formatting

user_5900683 0
Viewed 7523

I am trying to export my Kendo grid to excel. It works fine except the formatting is missing. I think it is because I am using a template.

The Telerik documentation explicitly states:

To format the cell values during the export of the Grid to Excel, set the format option of the cells.

I have tried this and it is not working:

columns: [
    {
        field: "EntryWage",
        headerTemplate: entryLevelWageColumnHeading + "<span name='EntryWage' class='k-icon k-i-close remove' style='float: right;'></span>",
        width: 125,
        attributes: { style: "text-align:right;" },
        format: "{0:c}",
        template: "#= (EntryWage != null) ? kendo.toString(EntryWage, 'C') : 'N/A' #"
    }];

I also have this function (for excel grid defintiion):

    excelExport: function (e) {
        var sheet = e.workbook.sheets[0];
        var row = sheet.rows[0];
        $("#grid .k-grid-header .k-link").each(function (index) { //for each column header in the grid...
            row.cells[index].value = $(this).text(); //set cell text from grid column text
            row.cells[index].background = "#0070C0"; //set cell to "blue" color
        });
    },

Do I need to parse each cell here? Am I doing something wrong? I would think this would be really simple, since the whole Export to Excel is straightforward??

kendo-grid kendo-ui jquery c#
3 Answers

We needed a bit more flexibility so we created an excelTemplate property for some columns and during the excelExport use that to update the cell values.

{
    field: 'AppId',
    title: 'Application',
    width: '99px',
    template: function(data) {
        return _applications()[data.AppId].Name;
    },
    excelTemplate: function(data) {
        return _applications()[data].Name;
    },
    groupHeaderTemplate: function(data) {
        return _applications()[data.value].Name;
    },
},
  1. Look though the columns for the excelTemplate property
  2. Find the header row and then match the column title to the header row title to build an associative array
  3. Update the templated cell values for all the rows.
function(e) {
    var templatesByTitle = {};
    var columnTemplates = [];
    var sheet = e.workbook.sheets[0];

    this.columns.forEach(function(column) {
        if (column.excelTemplate) {
            templatesByTitle[column.title] = column.excelTemplate;
        }
    })

    for (var r = 0; r < sheet.rows.length; r++) {
        var row = sheet.rows[r];
        if (row.type === "header") {
            row.cells.forEach(function(headerCell, index) {
                var template = templatesByTitle[headerCell.value];
                if (template !== undefined) {
                    columnTemplates.push({
                        column: index,
                        template: template
                    });
                }
            })
            break;
        }
    }

    sheet.rows.forEach(function(row) {
        if (row.type === "data") {
            columnTemplates.forEach(function(columnTemplate) {
                var cell = row.cells[columnTemplate.column];
                if (cell.value !== null) {
                    cell.value = columnTemplate.template(cell.value);
                }
            });
        }
    });
}
user_55308000
Related