Rounding does not always match Excel's display values #1105

New Issue

sheetjs · 2018-05-14T15:05:08Z

tom-groves commented

2018-05-14 15:05:08 +00:00

(Migrated from github.com)

Hi there

For certain cell values the xlsx package yields different formatted display text than Excel.

A full example with a file is available at https://github.com/feverpitch-tom/xlsx-rounding-demo.

For manual reproduction:

Create a spreadsheet with the following three values:

1.225
100.665
656.685

These should be formatted to display at two decimal places, and consequently are displayed as follows in Excel:

1.23
100.67
652.69

When parsed by the xlsx package the w property for each cell, which represents the formatted value, is as follows:

1.23
100.67
652.68

The last value (C1 in the sheet) is 0.01 less than is displayed in Excel.

Thanks

Tom

Hi there For certain cell values the xlsx package yields different formatted display text than Excel. A full example with a file is available at https://github.com/feverpitch-tom/xlsx-rounding-demo. For manual reproduction: Create a spreadsheet with the following three values: - 1.225 - 100.665 - 656.685 These should be formatted to display at two decimal places, and consequently are displayed as follows in Excel: - 1.23 - 100.67 - 652.69 When parsed by the xlsx package the w property for each cell, which represents the formatted value, is as follows: - 1.23 - 100.67 - 652.68 The last value (C1 in the sheet) is 0.01 less than is displayed in Excel. Thanks Tom

👍 1

SheetJSDev commented

2018-05-15 03:50:49 +00:00

(Migrated from github.com)

Thanks for reporting! Excel is storing the IEEE754 value 652.68499999999995, just shy of traditionally rounding to 652.69 😞

Thanks for reporting! Excel is storing the IEEE754 value 652.68499999999995, just shy of traditionally rounding to 652.69 😞 ![597d1386-439d-4c05-b4d6-9fd2b201d00e](https://user-images.githubusercontent.com/6070939/40035579-5b600d5c-57d0-11e8-8782-754c465efc80.png)

tom-groves commented

2018-05-15 09:39:19 +00:00

(Migrated from github.com)

I've submitted a PR with a potential fix - it'd be good to get your feedback @SheetJSDev

tom-groves commented

2018-05-17 10:57:32 +00:00

(Migrated from github.com)

@SheetJSDev did you get a chance to look through that PR? It'd be great to hear your views 😄

SheetJSDev commented

2018-05-17 11:23:20 +00:00

(Migrated from github.com)

(was in the middle of writing a reply 😆)

This breaks values which JS would naturally stringify as exponential (like 1.23456789e-7), but this is fairly easy to handle with a regular expression against the string value.

Note: there's actually a separate repo for the formatting library https://github.com/sheetjs/ssf with some tests.

Curious about how the formats represent the number in question, here are some test files: issue1105.zip. XLSB and standard BIFF8 XLS stores the number as an "Xnum" (IEEE754 double), but older versions of Excel (so-called "BIFF5", like in Excel 95 and Excel 5.0) use a truncated form where the lower 34 bits of the double is zero and one bit is allocated for a 100 modifier. The file issue1105_50.xls uses that truncated form with bytes 0x91 0xde 0xef 0x40, which represents the value 65268.5 / 100. As you can imagine, 65268.5 correctly rounds, so it may suffice to multiply by 100.

(was in the middle of writing a reply 😆) This breaks values which JS would naturally stringify as exponential (like 1.23456789e-7), but this is fairly easy to handle with a regular expression against the string value. Note: there's actually a separate repo for the formatting library https://github.com/sheetjs/ssf with some tests. Curious about how the formats represent the number in question, here are some test files: [issue1105.zip](https://github.com/SheetJS/js-xlsx/files/2012882/issue1105.zip). XLSB and standard BIFF8 XLS stores the number as an "Xnum" (IEEE754 double), but older versions of Excel (so-called "BIFF5", like in Excel 95 and Excel 5.0) use a [truncated form where the lower 34 bits of the double is zero and one bit is allocated for a 100 modifier](https://msdn.microsoft.com/en-us/library/dd943386.aspx). The file `issue1105_50.xls` uses that truncated form with bytes `0x91 0xde 0xef 0x40`, which represents the value 65268.5 / 100. As you can imagine, 65268.5 correctly rounds, so it may suffice to multiply by 100.

tom-groves commented

2018-05-23 14:44:55 +00:00

(Migrated from github.com)

@SheetJSDev it's all going a bit over my head :-D

Looking at those sheets and the ssf library i'm not sure there's anything we can do to resolve this :-( . If Excel is storing the number incorrectly in the first place then is there any solution that you can think of?

@SheetJSDev it's all going a bit over my head :-D Looking at those sheets and the `ssf` library i'm not sure there's anything we can do to resolve this :-( . If Excel is storing the number incorrectly in the first place then is there any solution that you can think of?

tom-groves commented

2018-05-23 17:11:24 +00:00

(Migrated from github.com)

Looks like this describes the issue well: https://gordonlesti.com/inaccurate-rounding-with-decimal-digits/

That solution is tailored for 2 DPs so I'm going to look at expanding on it tomorrow.

Looks like this describes the issue well: https://gordonlesti.com/inaccurate-rounding-with-decimal-digits/ That solution is tailored for 2 DPs so I'm going to look at expanding on it tomorrow.

tom-groves commented

2018-05-24 11:24:48 +00:00

(Migrated from github.com)

@SheetJSDev can you take a look at https://github.com/SheetJS/ssf/pull/34 please? I believe this may have fixed the issue

sheetjs referenced this issue

2022-11-21 10:07:02 +00:00

Numbers Rounding #2824

Sign in to join this conversation.