Number Format clashing with standard built-in format #506

New Issue

2016-11-21T15:44:23Z

asksahil commented

2016-11-21 15:44:23 +00:00

(Migrated from github.com)

Hi,

I have an excel file which I am trying to parse using sheetjs xlsx.js plugin.

Attached is the excel file.
Financial Data All Metrics 161121.xlsx

Below is the code which I am using to get the sheetname and worksheet objects.
Even though the sheet name firstSheetName is retrieved successfully, the worksheet object comes out as null.

reader.onload = function(e,f) {
var data = e.target.result;
var workbook
workbook = XLSX.read(data, {type : 'binary'});
var firstSheetName = workbook.SheetNames[0]
var worksheet = workbook.Sheets[firstSheetName]
};

I have tried to parse the file at http://oss.sheetjs.com/js-xlsx/ but there too it did not work.

Hi, I have an excel file which I am trying to parse using sheetjs xlsx.js plugin. Attached is the excel file. [Financial Data All Metrics 161121.xlsx](https://github.com/SheetJS/js-xlsx/files/604042/Financial.Data.All.Metrics.161121.xlsx) Below is the code which I am using to get the sheetname and worksheet objects. Even though the sheet name firstSheetName is retrieved successfully, the worksheet object comes out as null. reader.onload = function(e,f) { var data = e.target.result; var workbook workbook = XLSX.read(data, {type : 'binary'}); var firstSheetName = workbook.SheetNames[0] var worksheet = workbook.Sheets[firstSheetName] }; I have tried to parse the file at http://oss.sheetjs.com/js-xlsx/ but there too it did not work.

asksahil commented

2017-03-20 14:41:00 +00:00

(Migrated from github.com)

Hello,

Thank you for the fix. Though there are some issues still pending. Even though the excel is parsing now, its unable to parse the date fields correctly. Example, the date is parsed like a number "40909" instead of "3/31/2012"

Hello, Thank you for the fix. Though there are some issues still pending. Even though the excel is parsing now, its unable to parse the date fields correctly. Example, the date is parsed like a number "40909" instead of "3/31/2012"

SheetJSDev commented

2017-03-20 14:54:00 +00:00

(Migrated from github.com)

@asksahil It looks like this file is attempting to override the standard number formats. It is attempting to set number format 1 to a date:

<numFmts>
<numFmt numFmtId="0" formatCode="General"/>
<numFmt numFmtId="1" formatCode="MM/dd/yyyy"/>
<numFmt numFmtId="2" formatCode="$#,##0.00"/>
</numFmts>

However number format 1 is a no-decimal number. The standard number formats are listed in the table_fmt variable. The tool should be writing to the custom number format ranges:

https://github.com/SheetJS/js-xlsx/blob/master/bits/47_styxml.js#L225

Either way, since Excel isn't raising any issues with that, it's worth changing the assumption.

@asksahil It looks like this file is attempting to override the standard number formats. It is attempting to set number format 1 to a date: ```xml <numFmts> <numFmt numFmtId="0" formatCode="General"/> <numFmt numFmtId="1" formatCode="MM/dd/yyyy"/> <numFmt numFmtId="2" formatCode="$#,##0.00"/> </numFmts> ``` However number format 1 is a no-decimal number. The standard number formats are listed in the [`table_fmt` variable](https://github.com/SheetJS/js-xlsx/blob/master/bits/10_ssf.js#L26-L31). The tool should be writing to the custom number format ranges: ``` 5-8 23-26 41-44 63-66 164-392 ``` https://github.com/SheetJS/js-xlsx/blob/master/bits/47_styxml.js#L225 Either way, since Excel isn't raising any issues with that, it's worth changing the assumption.

Sign in to join this conversation.