Bug 58471

Summary:	[PATCH] Format numbers more like Excel
Product:	POI	Reporter:	Chris Boyle <cmb-apache>
Component:	SS Common	Assignee:	POI Developers List <dev>
Status:	RESOLVED FIXED
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