Bug 58471

Summary: [PATCH] Format numbers more like Excel
Product: POI Reporter: Chris Boyle <cmb-apache>
Component: SS CommonAssignee: POI Developers List <dev>
Severity: normal    
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: Linux   
Attachments: Patch to format numbers more like Excel
Format numbers more like Excel does, v2

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