Bug 58471 - [PATCH] Format numbers more like Excel
Summary: [PATCH] Format numbers more like Excel
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: unspecified
Hardware: PC Linux
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2015-10-02 10:57 UTC by Chris Boyle
Modified: 2015-10-06 09:57 UTC (History)
0 users

Patch to format numbers more like Excel (12.65 KB, patch)
2015-10-02 10:57 UTC, Chris Boyle
Details | Diff
Format numbers more like Excel does, v2 (12.81 KB, patch)
2015-10-05 13:11 UTC, Chris Boyle
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Chris Boyle 2015-10-02 10:57:16 UTC
Created attachment 33155 [details]
Patch to format numbers more like Excel

The current DataFormatter differs slightly from Excel's formatting, which is documented at https://support.microsoft.com/en-us/kb/65903 (Summary, 3rd sentence onward) regarding when to use scientific notation and counting the decimal point towards the character limit. It also differs on rounding mode, which in Excel appears to be HALF_UP (this does not appear to be documented).

I attach a patch adding and using a new ExcelGeneralNumberFormat which matches Excel in a variety of test cases. This is useful for users who want to construct an error message quoting the cell value as it would be shown in Excel.

Note that this all matches the longest string Excel will display given a sufficiently wide cell. In the default cell width, it uses a smaller number of characters, and I don't know if that number and/or the cell width vary with display settings/DPI, accessibility settings, available fonts, etc., so the wide-cell value seemed the best thing to target.
Comment 1 Chris Boyle 2015-10-02 11:52:44 UTC
I've belatedly noticed that POI does already use HALF_UP in at least some circumstances, but I think the rest stands.
Comment 2 Chris Boyle 2015-10-05 10:56:16 UTC
Hold fire on this, my forward-port and tidying-up missed that test methods now need the @Test annotation, so TestDataFormatter.testLargeNumbersAndENotation() will not actually get run in the currently attached patch, and it fails. I will supply a replacement patch shortly.
Comment 3 Chris Boyle 2015-10-05 13:11:49 UTC
Created attachment 33165 [details]
Format numbers more like Excel does, v2

Replacement patch attached. Compared to the previous patch, it:

* Adds @Test so the new test actually gets run.
* Chooses better test decimals that are representable as ending in 5 as intended not 49999[...].
* Adds missing invocation of DataFormatter.setExcelStyleRoundingMode() on the new formatter's child DecimalFormats.
Comment 4 David North 2015-10-06 09:57:51 UTC
Looks like an improvement to me

Committed in r1706971, release notes in r1706972