Stop decimal rounding in generated excel file #3003

New Issue

AmilaRanganath · 2023-10-09T05:53:27Z

AmilaRanganath commented

2023-10-09 05:53:27 +00:00

Hi,

I generate the .xlsx file using 'XLSX.write' function. This is used in the React application. This is the write function code snippet.

const wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: true, type: 'binary' });

That wb object include some of the data like SS below.

After I generated the .xlsx file the D2 cell = 7581185.560000000 and D3 cell = 12005729.810000000. I need to get the exact value in the generated .xlsx file, which I pass through the wb object and it should be the Number type.

How can I accomplish this requirement ?

Thanks

Hi, I generate the .xlsx file using 'XLSX.write' function. This is used in the React application. This is the write function code snippet. `const wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: true, type: 'binary' });` That wb object include some of the data like SS below. ![Screenshot 2023-10-09 105655.jpg](/attachments/73214a44-6fba-40a2-9753-e8c03ea134c7) After I generated the .xlsx file the **D2 cell = 7581185.560000000** and **D3 cell = 12005729.810000000**. I need to get the exact value in the generated .xlsx file, which I pass through the wb object and it should be the Number type. How can I accomplish this requirement ? Thanks

Screenshot 2023-10-09 105655.jpg

17 KiB

sheetjs commented

2023-10-09 11:44:31 +00:00

Excel itself is rounding the value. To confirm this, try:

var ws = {
    "!ref": "A1:B1",
    "A1": { t:"n", v:7581185.559999999, z: "0.00000000" },
    "B1": { t:"n", v:12005729.809999997, z: "0.00000000" },
}
var wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
XLSX.writeFile(wb, "issue3003.xlsx");

The attached file was generated by running the lines in the JavaScript console in a browser window.

To see what the file contains, go to https://docs.sheetjs.com/docs/#preview-a-workbook-on-your-device . In the live codeblock, within the "Import Button" block, add a line to log the worksheet:

Using the file picker in the example, if you pick the file, the contents look like:

If you manually try entering 7581185.559999999 in Excel, it automatically rounds to 7581185.55999999 (one less digit) and the value stored in the file is 7581185.5599999903

Excel will show what you expect if you use the exact value Excel stores:

var ws = {
    "!ref": "A1:B1",
    "A1": { t:"n", v:7581185.5599999903, z: "0.00000000" },
    "B1": { t:"n", v:12005729.809999997, z: "0.00000000" },
}
var wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
XLSX.writeFile(wb, "issue3003.xlsx");

This will be added to the documentation

Excel itself is rounding the value. To confirm this, try: ```js var ws = { "!ref": "A1:B1", "A1": { t:"n", v:7581185.559999999, z: "0.00000000" }, "B1": { t:"n", v:12005729.809999997, z: "0.00000000" }, } var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, "issue3003.xlsx"); ``` The attached file was generated by running the lines in the JavaScript console in a browser window. To see what the file contains, go to https://docs.sheetjs.com/docs/#preview-a-workbook-on-your-device . In the live codeblock, within the "Import Button" block, add a line to log the worksheet: ![adding `console.log`](/attachments/c7c18045-b0b1-4311-a484-81493c7a77b9) Using the file picker in the example, if you pick the file, the contents look like: ![worksheet contents preserved](/attachments/0d80765d-26c0-4663-8b7f-03877f56ae88) --- If you manually try entering `7581185.559999999` in Excel, it automatically rounds to `7581185.55999999` (one less digit) and the value stored in the file is `7581185.5599999903` Excel will show what you expect if you use the exact value Excel stores: ```js var ws = { "!ref": "A1:B1", "A1": { t:"n", v:7581185.5599999903, z: "0.00000000" }, "B1": { t:"n", v:12005729.809999997, z: "0.00000000" }, } var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, "issue3003.xlsx"); ``` This will be added to the documentation

issue3003.xlsx

16 KiB

log-ws.png

127 KiB

contents.png

56 KiB

AmilaRanganath commented

2023-10-10 07:37:51 +00:00

Yes, It's rounding up the value Excel itself.

Excel can only display numbers up to 15 digits in a cell, and any number longer than this will show 0 instead of the number after the 15th digit.
Reference: [https://support.microsoft.com/en-gb/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3]

Thanks

Yes, It's rounding up the value Excel itself. Excel can only display numbers up to 15 digits in a cell, and any number longer than this will show 0 instead of the number after the 15th digit. Reference: [https://support.microsoft.com/en-gb/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3] Thanks

sheetjs commented

2023-10-10 08:41:43 +00:00

https://docs.sheetjs.com/docs/miscellany/errors/#some-decimal-values-are-rounded let us know if we should include anything else in that note

sheetjs closed this issue

2023-10-10 08:41:43 +00:00

AmilaRanganath commented

2023-10-13 10:05:15 +00:00

Hi @sheetjs,

If I enter manually 7581185.559999999 value as a number with 9 decimal points in Excel it's become 7581185.559999990(It's Excel behavior). But If I generate through the XLSX passing that value it's gives 7581185.560000000. It's behave differently. Is there any reason for that one?

Hi @sheetjs, If I enter manually 7581185.559999999 value as a number with 9 decimal points in Excel it's become 7581185.559999990(It's Excel behavior). But If I generate through the XLSX passing that value it's gives 7581185.560000000. It's behave differently. Is there any reason for that one?

AmilaRanganath reopened this issue

2023-10-13 10:05:24 +00:00

sheetjs commented

2023-10-13 10:48:35 +00:00

https://docs.sheetjs.com/docs/miscellany/errors#some-decimal-values-are-rounded

It is suspected that the XLSX parser handles 15 decimal digits of precision

7581185.55999999 is "15 decimal digits of precision".

"7581185.55999999".length == 15 + 1; // +1 for the decimal point

7581185.559999999 is "16 decimal digits of precision":

"7581185.559999999".length == 16 + 1; // +1 for the decimal point

IEEE754 and JS supports values in this range with an additional digit of precision:

7581185.559999991
7581185.559999992
7581185.559999993
7581185.559999994
7581185.559999995
7581185.559999996
7581185.559999997
7581185.559999998
7581185.559999999

You can calculate this range by digging into the bit-level representation of these values -- feel free to join our chat at https://sheetjs.com/chat if you want to discuss this further.

Each value will either be "rounded down" to 7581185.55999999 or "rounded up" to the next supported value 7581185.56000000.

If you would like to see a test: https://jsfiddle.net/234wopks/

var aoa = [51,52,53,54,55,56,57,58,59,60,61].map(n => [(n-51)%10,new Float64Array(new Uint8Array([n, 10, 215, 99, 128, 235, 92, 65]).buffer)[0]])
var ws = XLSX.utils.aoa_to_sheet([["Value", "last digit"]]);
XLSX.utils.sheet_add_aoa(ws, aoa, {origin:-1})
for(var R=2;R<=12; ++R) ws["B" + R].z = "0.0000000000";
ws["!cols"] = [, {wch: 25}]
var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
XLSX.writeFile(wb, "i3003.xlsx");

Each JS value is either rounded up or rounded down.

https://docs.sheetjs.com/docs/miscellany/errors#some-decimal-values-are-rounded > **It is suspected that the XLSX parser handles 15 decimal digits of precision** `7581185.55999999` is "15 decimal digits of precision". ```js "7581185.55999999".length == 15 + 1; // +1 for the decimal point ``` 7581185.559999999 is "16 decimal digits of precision": ```js "7581185.559999999".length == 16 + 1; // +1 for the decimal point ``` IEEE754 and JS supports values in this range with an additional digit of precision: ``` 7581185.559999991 7581185.559999992 7581185.559999993 7581185.559999994 7581185.559999995 7581185.559999996 7581185.559999997 7581185.559999998 7581185.559999999 ``` You can calculate this range by digging into the bit-level representation of these values -- feel free to join our chat at https://sheetjs.com/chat if you want to discuss this further. Each value will either be "rounded down" to `7581185.55999999` or "rounded up" to the next supported value `7581185.56000000`. If you would like to see a test: https://jsfiddle.net/234wopks/ ```js var aoa = [51,52,53,54,55,56,57,58,59,60,61].map(n => [(n-51)%10,new Float64Array(new Uint8Array([n, 10, 215, 99, 128, 235, 92, 65]).buffer)[0]]) var ws = XLSX.utils.aoa_to_sheet([["Value", "last digit"]]); XLSX.utils.sheet_add_aoa(ws, aoa, {origin:-1}) for(var R=2;R<=12; ++R) ws["B" + R].z = "0.0000000000"; ws["!cols"] = [, {wch: 25}] var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, "i3003.xlsx"); ``` Each JS value is either rounded up or rounded down.

sheetjs referenced this issue

2023-10-24 01:29:49 +00:00

ssf - time rounding for seconds #3006

Sign in to join this conversation.