Kendo Grid Export To Excel Currency formatting

问题:

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??

回答1:

I don't think template should have any effect on the data being exported (as the excelExport is based on the dataSource).

Here is a jsFiddle of a working example of excelExport which changes the formatting of each cell.

Note the difference between the excelExport code:

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 (row.type === "data") {
            //if (cellIndex == 2) { 
            if (sheet.rows[0].cells[cellIndex].value == "unitPrice") {// like this
                cell.format = "number";
                cell.background = "#0070C0"
                cell.hAlign = "right";
            }
        }
    }      
  }

回答2:

I ended up solving it similarly to Sandman's suggestion. I based my output format on the field name, from each column (input).

I also append the grid's column headers to the Excel column header row. It's a little sad Kendo doesn't already do this.

excelExport: function (e) {  
    var sheet = e.workbook.sheets[0];
    var row = sheet.rows[0];

    //Excel output - create a header row
    $("#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
    });

    //Excel output - column formatting
    var formatMask = null;
    for (var rowIndex = 1; rowIndex < sheet.rows.length; rowIndex++) { //begin on first row (skip header)
        row = sheet.rows[rowIndex];
        for (var cellIndex = 0; cellIndex < row.cells.length; cellIndex++) { //for each row, check each cell by field name
            switch (this.columns[cellIndex].field) {
                case "Field1":
                    formatMask = "###,##0";
                    break;
                case "Field2":
                    formatMask = "$#,##0";
                    break;
                case "Field3":
                    formatMask = "$#,##0";
                    break;
                case "Field4":
                    formatMask = "###,##0";
                    break;
                case "Field5":
                    formatMask = "###,##0";
                    break;
                case "Field6":
                    formatMask = "###,##0";
                    break;
                case "Field7":
                    formatMask = null;
                    row.cells[cellIndex].value = row.cells[cellIndex].value;
                    break;
                case "Field8":
                    formatMask = "###,##0";
                    break;
                case "Field9":
                    formatMask = "###,##0";
                    break;
                case "Field10":
                    formatMask = "###,##0";
                    break;
                case "Field11":
                    formatMask = "###,##0";
                    break;
                case "Field12":
                    if (row.cells[cellIndex].value === false) {
                        row.cells[cellIndex].value = "No";
                    } else {
                        row.cells[cellIndex].value = "Yes";
                    }
                    break;
                default:
                    break;
            }
            row.cells[cellIndex].format = formatMask; //apply column format mask
        }

    }
},