npm xlsx library Error : unsupported format |#,###.0000000| - Excel custom number format parsing error

Question

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']);

Answer 1

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.