1

I am trying to parse an xlsx file with numbers of the format "7242.1661445". On inspecting in Microsoft excel, I found that the cell is of custom number format "#,###.0000000".

I am parsing this file using xlsx library https://www.npmjs.com/package/xlsx. My runtime is Node.js.

However, the library throws me an error, unsupported format |#,###.0000000|

I tried a couple of other libraries, but still throws error. Can I pass any parameter as options while parsing ? What am I doing wrong ? Is this a complete dependency issue ? What options do I have here ? Is support for excel file parsing not good enough for javascript platform?

I am running the below code : 

var workbook = XLSX.readFile('filePath',{raw:true,WTF:true});
var worksheet = workbook.Sheets[workbook.SheetNames[0]];
console.log('Cell A1 value', worksheet['A1']);
Piyush Upadhyay
  • 177
  • 1
  • 12

1 Answers1

0

If you omit the WTF parameter, that cell will parse the value without throwing an error.

Now you would need to verify that the values are being converted to numbers correctly.

e.g. 

var workbook = XLSX.readFile('customformat.xlsx', {raw: true })
var worksheet = workbook.Sheets[workbook.SheetNames[0]];
console.log('Cell A1 value', worksheet['A1']);

From the XLSX documentation:

WTF is mainly for development. By default, the parser will suppress read errors on single worksheets, allowing you to read from the worksheets that do parse properly. Setting WTF:1 forces those errors to be thrown.

Terry Lennox
  • 29,471
  • 5
  • 28
  • 40
  • 1
    Hi, I tried this, If i do this "workbook.Sheets" comes out as an empty object for the sheet, i.e. the library skips parsing that sheet without throwing error. – Piyush Upadhyay Apr 21 '20 at 06:31
  • Wow, weird, I've created a simple reproduction of this issue using the format #,###.0000000. I do get the error thrown if WTF is set to true, however the Sheets object is populated if I set WTF to false. I wonder what the difference is? Could there be another issue? I might suggest doing the same, e.g. create a trivial sheet and setting the format to the problem and seeing what happens! – Terry Lennox Apr 21 '20 at 07:16