[Bug 61638] New: Incorrect result from DataFormatter.formatCellValue for format 60#########

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 61638] New: Incorrect result from DataFormatter.formatCellValue for format 60#########

Bugzilla from bugzilla@apache.org
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: [hidden email]
          Reporter: [hidden email]
  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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61638] Incorrect result from DataFormatter.formatCellValue for format 60#########

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61638

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61638] Incorrect result from DataFormatter.formatCellValue for format 60#########

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61638

--- Comment #1 from PJ Fanning <[hidden email]> ---
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61638] Incorrect result from DataFormatter.formatCellValue for format 60#########

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61638

--- Comment #2 from Nick Burch <[hidden email]> ---
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61638] Incorrect result from DataFormatter.formatCellValue for format 60#########

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61638

--- Comment #3 from Wai Chun <[hidden email]> ---
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: [hidden email]
For additional commands, e-mail: [hidden email]