Reading improper cell value #3173

New Issue

Jordon · 2024-08-08T14:44:41Z

Jordon commented

2024-08-08 14:44:41 +00:00

I have encountered an issue reading a very specific cell value using xlsx. If I have a xlsx file with a cell value of "50363670.115766", for some reason the library will read this as follows.

mySheet["cellPosition"].v = "50363670.115766004"

If I change the number to "50363670.115765" or "50363670.115767", I don't get this issue. Sometimes changing the portion of the number before the decimal place gives me this issue, sometimes it doesn't. So for example, "40363670.115766" gives me the same problem (it adds 004 to the end), but "503636703.115766" is read as expected.

This problem exists on version 0.20.2 and 0.20.3, using Yarn to install the NodeJS package. I am using this library in Typescript for React. The xlsx file is converted into a workbook using "XLSXRead(arrayBuffer, { cellDates: true });".

I have encountered an issue reading a very specific cell value using xlsx. If I have a xlsx file with a cell value of "50363670.115766", for some reason the library will read this as follows. mySheet["cellPosition"].v = "50363670.115766004" If I change the number to "50363670.11576**5**" or "50363670.11576**7**", I don't get this issue. Sometimes changing the portion of the number before the decimal place gives me this issue, sometimes it doesn't. So for example, "**4**0363670.115766" gives me the same problem (it adds 004 to the end), but "50363670**3**.115766" is read as expected. This problem exists on version 0.20.2 and 0.20.3, using Yarn to install the NodeJS package. I am using this library in Typescript for React. The xlsx file is converted into a workbook using "XLSXRead(arrayBuffer, { cellDates: true });". <img width="582" alt="image" src="/attachments/b0559c5c-f08b-4af4-abec-3ae3f25ab858">

image.png

14 KiB

sheetjs commented

2024-08-09 06:58:10 +00:00

https://docs.sheetjs.com/docs/csf/cell discusses this in more detail.

What you are seeing is an artifact of Excel.

The attached i3173.xlsx file was created by setting A1 the value 50363670.115766 and saving in Excel 365.

Excel actually stores the value you see:

<v>50363670.115766004</v>

In general there are three relevant values:

A) "what is shown in the cell"

B) "what the file actually holds"

C) "what Excel displays in the formula bar"

Only (B) exists in the file. (A) and (C) must be computed from (B).

(B) is the raw value, stored in the v field of the cell object.

(A) is used in CSV and HTML rendering. The formatted text is stored in the w field.

(C) only exists in Excel. There is no specification for this behavior and it has changed many times over the decades. Since it does not exist in the file and is not relevant for CSV or any other file format, the library does not attempt to calculate this value.

https://docs.sheetjs.com/docs/csf/cell discusses this in more detail. What you are seeing is an artifact of Excel. The attached `i3173.xlsx` file was created by setting A1 the value `50363670.115766` and saving in Excel 365. Excel actually stores the value you see: ```xml <v>50363670.115766004</v> ``` --- In general there are three relevant values: A) "what is shown in the cell" B) "what the file actually holds" C) "what Excel displays in the formula bar" Only (B) exists in the file. (A) and (C) must be computed from (B). (B) is the raw value, stored in the `v` field of the cell object. (A) is used in CSV and HTML rendering. The formatted text is stored in the `w` field. (C) only exists in Excel. There is no specification for this behavior and it has changed many times over the decades. Since it does not exist in the file and is not relevant for CSV or any other file format, the library does not attempt to calculate this value.

i3173.xlsx

8.3 KiB

sheetjs closed this issue

2024-08-09 06:58:10 +00:00

Sign in to join this conversation.