Bug 61638 - Incorrect result from DataFormatter.formatCellValue for format 60#########
Summary: Incorrect result from DataFormatter.formatCellValue for format 60#########
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.17-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-10-20 08:15 UTC by Wai Chun
Modified: 2017-11-09 09:20 UTC (History)
0 users



Attachments
Screenshot of data within the excel file (46.77 KB, image/png)
2017-10-20 08:15 UTC, Wai Chun
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Wai Chun 2017-10-20 08:15:20 UTC
Created attachment 35444 [details]
Screenshot of data within the excel file

Greetings, first of all I shall say thank you to show my appreciation for this great product, it really helped a lot on projects I've involved throughout my career.

However recently I've found an issue with the result returned from function DataFormatter.formatCellValue(Cell cell).

I have this excel sheet filled with "166609647" in one of the cell, and I applied format "60#########" on it. "60166609647" appears on my Excel program afterwards, as screenshot attached.

Using below code I'm trying to get what's appeared on the screen:

Cell cell = getTheCell();
DataFormatter df = new DataFormatter();
String displayValue = df.formatCellValue(cell);

What I captured in displayValue is only "166609647", the leading "60" is nowhere to be found.

If I change the format to "60000000000", the code above works fine, I can get "60166609647".

I'm already using latest library 3.17 and this issue still occurs.
Comment 1 PJ Fanning 2017-11-04 21:38:13 UTC
For this data format, POI is using the java.text.DecimalFormat class.
This class treats format "60#########" as illegal.
Formats like "AB########" work but the Java class does not like the attempt to prefix the number with another number.
This format "60#########" does work in Excel.
I still think that the Java behaviour is better than the Excel behaviour in the case. I think there are better ways to prepend 60 onto a number, eg using a formula like `=CONCATENATE("60", A1)`.
Comment 2 Nick Burch 2017-11-05 08:43:15 UTC
The Java DecimalFormat version of "60#########" is "'60'000000000"

Note that we have to escape the initial 60 to force it to always display, then swap the #s for 0s as the # in Java means "display if exists" while the 60 prefix means we always need to treat it as existing

Putting that translation in may not be that easy, as I believe the following monster is valid in excel...

[$-809][>=100][Red]60#########;[$-410][<-100][Green]92##,####,###;[Yellow]0,000

(Interestingly, OpenOffice gets it slightly wrong too - it shows the leading 60 but not the "missing" 0s)
Comment 3 Wai Chun 2017-11-09 09:20:16 UTC
One situation worth noting though, ### is not always the same as 000.

for example, for an input of 12345678

if the format is 60000000000, output will be 60012345678.
if the format is 60#########, output will be 6012345678.