Search code examples
excelkendo-uiexport-to-excelnankendospreadsheet

Kendo SaveAsExcel nan

I'm generating a spreadsheet, where sometimes the data comes out as NaN. Not a problem

However, when I "SaveAsExcel", and go to open the spreadsheet in Excel 2016 I get "We found a problem with some content in 'reportname'. Do you want us to try and recover as much as we can? If you trust the source of this workbook, click yes" Clicking yes and I get that Excel was able to open the file with the following:

"Repaired Records: Cell information from /xl/worksheets/sheet1.xml part"

and a link to a log file which shows... Nothing

error072200_01.xmlErrors were detected in file MyFileName.xlsx'Repaired Records: Cell information from /xl/worksheets/sheet1.xml part

Now if I delete the NaNs on the sheet, I have NO issues. Anyone have a clue how I can fix this?

Solution

  • I've recreated the problem and also suggested a solution - Go over all the cells of the sheet and check if the cell's value is NaN and validated as a number, if so, change the value to "".

    You can change the solution to something that will fit your needs better of course.

    <!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8"/>
    <title>Kendo UI Snippet</title>

    <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.2.621/styles/kendo.common.min.css"/>
    <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.2.621/styles/kendo.rtl.min.css"/>
    <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.2.621/styles/kendo.silver.min.css"/>
    <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2017.2.621/styles/kendo.mobile.all.min.css"/>

    <script src="http://code.jquery.com/jquery-1.12.4.min.js"></script>
    <script src="http://kendo.cdn.telerik.com/2017.2.621/js/jszip.min.js"></script>
    <script src="http://kendo.cdn.telerik.com/2017.2.621/js/kendo.all.min.js"></script>
</head>
<body>
  
    <button id="export">Export to Excel</button>
    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                name: "Food Order",
                mergedCells: [
                    "A1:G1"
                ],
                rows: [{
                    height: 70,
                    cells: [{
                        value: "My Company", fontSize: 32, textAlign: "center"
                    }]
                }, {
                  cells: [{
                    value: NaN,
                    textAlign: "center",
                    validation: {
                      from: "1",
                      to: "2",
                      comparerType: "between",
                      dataType: "number",
                      messageTemplate: "Number should match the validation."
                    }
                  }]
                }],
            }],
          	excelExport: function(e) {
              	e.workbook.sheets[0].rows.forEach(function(row) {
                		row.cells.forEach(function(cell) {
                    		if (isNaN(cell.value) && cell.validation && cell.validation.dataType === "number") {
                            cell.value = "";
                        }
                    });
                });
              	console.log(e.workbook.sheets[0]);
            }
        });
        $("#export").click(function(e) {
            var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
            spreadsheet.saveAsExcel();
        });
    </script>

    <!-- Load JSZIP library to enable Excel export -->
    <script src="http://kendo.cdn.telerik.com/2017.2.621/js/jszip.min.js"></script>
</body>
</html>