You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2017/10/20 08:15:20 UTC
[Bug 61638] New: Incorrect result from DataFormatter.formatCellValue
for format 60#########
https://bz.apache.org/bugzilla/show_bug.cgi?id=61638
Bug ID: 61638
Summary: Incorrect result from DataFormatter.formatCellValue
for format 60#########
Product: POI
Version: 3.17-FINAL
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: SS Common
Assignee: dev@poi.apache.org
Reporter: wcchor89@gmail.com
Target Milestone: ---
Created attachment 35444
--> https://bz.apache.org/bugzilla/attachment.cgi?id=35444&action=edit
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61638] Incorrect result from DataFormatter.formatCellValue for
format 60#########
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61638
--- Comment #1 from PJ Fanning <fa...@yahoo.com> ---
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)`.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61638] Incorrect result from DataFormatter.formatCellValue for
format 60#########
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61638
--- Comment #3 from Wai Chun <wc...@gmail.com> ---
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61638] Incorrect result from DataFormatter.formatCellValue for format 60#########
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61638
PJ Fanning <fa...@yahoo.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Resolution|--- |FIXED
Status|NEW |RESOLVED
--- Comment #5 from PJ Fanning <fa...@yahoo.com> ---
https://github.com/apache/poi/pull/1424
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61638] Incorrect result from DataFormatter.formatCellValue for
format 60#########
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61638
--- Comment #4 from mattgregory33@gmail.com ---
I voted for this as I'm running into the same underlying issue. I'm using
Apache Tika to parse spreadsheets of varying layouts. Format strings such as
"0##" cause IllegalArgumentException to be caught in
DataFormatter.createNumberFormat() and the default formatter to be used in
place. In my case I cannot predict the incoming layout nor the value of such a
format to the creator of the spreadsheet. I simply must parse it as intended.
It seems POI should parse these the same way Excel would.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61638] Incorrect result from DataFormatter.formatCellValue for
format 60#########
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61638
--- Comment #2 from Nick Burch <ap...@gagravarr.org> ---
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)
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 61638] Incorrect result from DataFormatter.formatCellValue for
format 60#########
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=61638
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |All
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org