Incorrect parsing of very large numbers in xlsx #2869

New Issue

handjunyoung · 2023-02-02T19:25:47Z

handjunyoung commented

2023-02-02 19:25:47 +00:00

Description:

I am using SheetJS to parse xlsx files, but I have noticed that very large numbers such as 10000000000000165 are being displayed as 10000000000000164. This is because JavaScript's internal 64-bit floating point data type has limited number range, causing accuracy issues with very large numbers.

Steps to Reproduce:

Use SheetJS to parse an xlsx file that contains a very large number, such as 10000000000000165.
Observe that the parsed data displays the number as 10000000000000164 instead of the correct value.

Expected Result:
The correct value of 10000000000000165 should be displayed in the parsed data.

Actual Result:
The number is displayed as 10000000000000164, which is incorrect.

Environment:

SheetJS version: 0.18.5
Node version: v16.15.0

Thank you for your attention to this matter. I look forward to your prompt resolution.

Description: I am using SheetJS to parse xlsx files, but I have noticed that very large numbers such as 10000000000000165 are being displayed as 10000000000000164. This is because JavaScript's internal 64-bit floating point data type has limited number range, causing accuracy issues with very large numbers. Steps to Reproduce: Use SheetJS to parse an xlsx file that contains a very large number, such as 10000000000000165. Observe that the parsed data displays the number as 10000000000000164 instead of the correct value. Expected Result: The correct value of 10000000000000165 should be displayed in the parsed data. Actual Result: The number is displayed as 10000000000000164, which is incorrect. Environment: SheetJS version: 0.18.5 Node version: v16.15.0 Thank you for your attention to this matter. I look forward to your prompt resolution.

sheetjs commented

2023-02-02 19:28:20 +00:00

Excel internally uses a subset of IEEE754 double (which is what JS specifies), so Excel will show the same issues.

Can you share an example file? Entering the exact value 10000000000000165 in the formula bar automatically changes the cell value to 10000000000000100

Excel internally uses a subset of IEEE754 double (which is what JS specifies), so Excel will show the same issues. Can you share an example file? Entering the exact value `10000000000000165` in the formula bar automatically changes the cell value to `10000000000000100`

handjunyoung commented

2023-02-03 05:16:56 +00:00

@sheetjs

Thanks for your attention.

As you said, when I focus on 10000000000000165 and then lose focus, it changes to 10000000000000100.

So is there any way to solve this problem?

If I change the cell's data type (t) to string, it only changes the type, but the value is the same, 10000000000000164.

Attached is the example file you requested!

Thank you.

@sheetjs Thanks for your attention. As you said, when I focus on 10000000000000165 and then lose focus, it changes to 10000000000000100. So is there any way to solve this problem? If I change the cell's data type (t) to string, it only changes the type, but the value is the same, 10000000000000164. Attached is the example file you requested! Thank you.

example#2869.xls

20 KiB

sheetjs commented

2023-02-03 09:47:30 +00:00

Neither Excel nor JavaScript supports the number 10000000000000165.

Both Excel and JavaScript use IEEE754 doubles, which have 53 bits of precision (1 sign bit, 11 exponent bits, 52 explicit precision bits + 1 implicit bit = 53 precision bits).

The maximum "safe integer" is 2**53 - 1 == 9007199254740991. Any integer values larger than that threshold may not be exactly represented (so rounding may occur) and that's what you are seeing here (10000000000000165 > 9007199254740991)

To verify this, in your JavaScript console, you can enter the numeric value 10000000000000165 and it will print 10000000000000164. You can try running 10000000000000164 + 1 and it will print 10000000000000164.

The only way to get 10000000000000165 in the sheet is to keep the entire flow as a string. That means whatever data source generates the data must return a string, and you must use the string when you add it to a SheetJS worksheet object.

{ t: "n", v:  10000000000000165  }; // bad:  JS will round the number
{ t: "s", v:  10000000000000165  }; // bad:  JS will round the number
{ t: "s", v: "10000000000000165" }; // good: JS string - Excel string

Live demo: https://jsfiddle.net/32dz4c0y/

Neither Excel nor JavaScript supports the number `10000000000000165`. Both Excel and JavaScript use IEEE754 doubles, which have 53 bits of precision (1 sign bit, 11 exponent bits, 52 explicit precision bits + 1 implicit bit = 53 precision bits). The maximum "safe integer" is `2**53 - 1 == 9007199254740991`. Any integer values larger than that threshold may not be exactly represented (so rounding may occur) and that's what you are seeing here (`10000000000000165 > 9007199254740991`) To verify this, in your JavaScript console, you can enter the numeric value `10000000000000165` and it will print `10000000000000164`. You can try running `10000000000000164 + 1` and it will print `10000000000000164`. The only way to get `10000000000000165` in the sheet is to keep the *entire flow* as a string. That means whatever data source generates the data must return a string, and you must use the string when you add it to a SheetJS worksheet object. ```js { t: "n", v: 10000000000000165 }; // bad: JS will round the number { t: "s", v: 10000000000000165 }; // bad: JS will round the number { t: "s", v: "10000000000000165" }; // good: JS string - Excel string ``` Live demo: https://jsfiddle.net/32dz4c0y/

sheetjs closed this issue

2023-02-03 09:47:30 +00:00

Sign in to join this conversation.