Kendo SaveAsExcel nan

Question

Kendo SaveAsExcel nan

967 views Asked by Charles Gallo At 23 June 2017 at 18:00

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?

Original Q&A

There are 1 answers

**Shai** · Accepted Answer · 2017-06-26T08:37:49+00:00

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>

TechQA.

Kendo SaveAsExcel nan

There are 1 answers

Related Questions in EXCEL

Related Questions in KENDO-UI

Related Questions in EXPORT-TO-EXCEL

Related Questions in NAN

Related Questions in KENDOSPREADSHEET

Popular Questions

Popular Tags

Trending Questions