Custom percentage formatting unable to parse workbook #3232

New Issue

pj-r · 2024-10-03T07:22:35Z

pj-r commented

2024-10-03 07:22:35 +00:00

Hi! And thank you for this awesome library!

We have used the library as viewer in combination with canvas-grid. Lately we started getting excel files(.xlsx) that has custom percentage formatting that sheetjs is unable to parse.

Excel says the cell number format is: "0.0 %" and the error message from sheetjs on parsing is "unrecognized character % in 0.0\ %".

I did confirm it works if I change the formatting to standard percentage formatting but as the files are coming from 3rd party I'm unable to make them to use different format.

Hi! And thank you for this awesome library! We have used the library as viewer in combination with canvas-grid. Lately we started getting excel files(.xlsx) that has custom percentage formatting that sheetjs is unable to parse. Excel says the cell number format is: "0.0 %" and the error message from sheetjs on parsing is "unrecognized character % in 0.0\ %". I did confirm it works if I change the formatting to standard percentage formatting but as the files are coming from 3rd party I'm unable to make them to use different format.

pj-r changed title from ~~Custom percentage formatting~~ to Custom percentage formatting unable to parse workbook

2024-10-03 07:28:50 +00:00

sheetjs commented

2024-10-03 14:59:10 +00:00

Thanks for reporting! We will revisit number formatting after the next release.

Please share any other affected formats. To simplify the search, open the demo in https://docs.sheetjs.com/docs/csf/features/nf#number-format-strings and select your file. For each unique number format used in your file, it will print the format, a JSON-encoded version of the format string, and a cell address you can inspect.

Thanks for reporting! We will revisit number formatting after the next release. Please share any other affected formats. To simplify the search, open the demo in https://docs.sheetjs.com/docs/csf/features/nf#number-format-strings and select your file. For each unique number format used in your file, it will print the format, a JSON-encoded version of the format string, and a cell address you can inspect.

pj-r commented

2024-10-03 18:14:02 +00:00

I used the tool to fetch list of the formats in that actual workbook. I needed to change "0.0\ %" to "0.0%" to get parsing to work.

Format	JSON
--------------
0		"0"
General	"General"
0.0		"0.0"
d-mmm	"d-mmm"
m/d/yy	"m/d/yy"
@		"@"
0.00	"0.00"
0%		"0%"
0.0\ %	"0.0\\ %"
_-* #,##0.00\ "€"_-;\-* #,##0.00\ "€"_-;_-* "-"??\ "€"_-;_-@_-	"_-* #,##0.00\\ \"€\"_-;\\-* #,##0.00\\ \"€\"_-;_-* \"-\"??\\ \"€\"_-;_-@_-"
#,##0.00\ "€"	"#,##0.00\\ \"€\""
_-* #,##0.00\ _€_-;\-* #,##0.00\ _€_-;_-* "-"??\ _€_-;_-@_-	"_-* #,##0.00\\ _€_-;\\-* #,##0.00\\ _€_-;_-* \"-\"??\\ _€_-;_-@_-"

Wondering if its enough to check the "\ " in this line: xlsx.js#L1061

I used the tool to fetch list of the formats in that actual workbook. I needed to change "0.0\\ %" to "0.0%" to get parsing to work. ``` Format JSON -------------- 0 "0" General "General" 0.0 "0.0" d-mmm "d-mmm" m/d/yy "m/d/yy" @ "@" 0.00 "0.00" 0% "0%" 0.0\ % "0.0\\ %" _-* #,##0.00\ "€"_-;\-* #,##0.00\ "€"_-;_-* "-"??\ "€"_-;_-@_- "_-* #,##0.00\\ \"€\"_-;\\-* #,##0.00\\ \"€\"_-;_-* \"-\"??\\ \"€\"_-;_-@_-" #,##0.00\ "€" "#,##0.00\\ \"€\"" _-* #,##0.00\ _€_-;\-* #,##0.00\ _€_-;_-* "-"??\ _€_-;_-@_- "_-* #,##0.00\\ _€_-;\\-* #,##0.00\\ _€_-;_-* \"-\"??\\ _€_-;_-@_-" ``` Wondering if its enough to check the "\ " in this line: [xlsx.js#L1061](https://git.sheetjs.com/sheetjs/sheetjs/src/commit/2d6c821261b3639c4f1aaa120844642e8f6f094e/xlsx.js#L1061)

Sign in to join this conversation.