Unsupported format: 0,### #2896

New Issue

sarimarton · 2023-02-27T11:55:58Z

sarimarton commented

2023-02-27 11:55:58 +00:00

I wonder how this library achieved such a wide adoption (~2M weekly downloads on npmjs) with such basic bugs.

Last publish: 3 years ago

Not good signs...

I wonder how this library achieved such a wide adoption (~2M weekly downloads on npmjs) with such basic bugs. Last publish: 3 years ago Not good signs...

sheetjs commented

2023-02-27 17:54:46 +00:00

The format codes are based on the en-US interpretation: , is the thousands separator and . is the decimal separator.

If you're intending to format a number with no thousands separator and "up to 3" decimal places, the format is 0.###.

0,### is interpreted as follows: "sign" (- for negative) "thousands" (Math.floor(Math.abs(value) / 1000)) "comma" (always present) "residue" (Math.floor(value % 1000)). For example, 11.1 (111 divided by 10) is rendered as 0,11

Since that format is not working, we consider this a bug.

On a broader note, the SSF library exists to support conversions from spreadsheet formats like XLSX to plaintext formats like CSV. XLSX persists a format code like 0.### and values like 11.1, and file readers are expected to generate the formatted text.

Excel and other spreadsheet software present a localized format in the UI but actually write the US form in the file itself. This also applies to date format: de-DE Excel uses a date format like JJJJ-MM-TT and Excel translates that to YYYY-MM-DD under the hood.

The format codes are based on the `en-US` interpretation: `,` is the thousands separator and `.` is the decimal separator. If you're intending to format a number with no thousands separator and "up to 3" decimal places, the format is `0.###`. `0,###` is interpreted as follows: "sign" (`-` for negative) "thousands" (`Math.floor(Math.abs(value) / 1000)`) "comma" (always present) "residue" (`Math.floor(value % 1000)`). For example, `11.1` (111 divided by 10) is rendered as `0,11` Since that format is not working, we consider this a bug. --- On a broader note, the SSF library exists to support conversions from spreadsheet formats like XLSX to plaintext formats like CSV. XLSX persists a format code like `0.###` and values like `11.1`, and file readers are expected to generate the formatted text. Excel and other spreadsheet software present a localized format in the UI but actually write the US form in the file itself. This also applies to date format: `de-DE` Excel uses a date format like `JJJJ-MM-TT` and Excel translates that to `YYYY-MM-DD` under the hood.

sheetjs added the

SSF

label 2023-02-27 17:57:43 +00:00

Sign in to join this conversation.