Rounding does not always match Excel's display values

[tom-groves]

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

[SheetJSDev]

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

[tom-groves]

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

[tom-groves]

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

[SheetJSDev]

(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.

[tom-groves]

@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]

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]

@SheetJSDev can you take a look at SheetJS/ssf#34 please? I believe this may have fixed the issue

[yfengBTI]

Adding another case where rounding doesn't match up to Excel:

Excel rounds halves away from zero:

• 0.5 rounds to 1
• -0.5 rounds to -1

Whereas SheetJS rounds halves up:

• 0.5 rounds to 1
• -0.5 rounds to 0