[Bug 61874] New: OperandResolver.parseDouble(String) does not does not work for numeric Strings using a comma as decimal separator.

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

[Bug 61874] New: OperandResolver.parseDouble(String) does not does not work for numeric Strings using a comma as decimal separator.

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

            Bug ID: 61874
           Summary: OperandResolver.parseDouble(String) does not does not
                    work for numeric Strings using a comma as decimal
                    separator.
           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: ---

OperandResolver::parseDouble does not take into account the locale and instead
always uses a decimal dot.

In Excel for example, this formula =TEXT("1234,68"; "#.##0,00") shows the value
"1.234,68" while POI returns "#VALUE!" because it cannot evaluate it.
Internally, the (POI) TEXT function uses OperandResolver::parseDouble which can
only convert numeric String using a dot as the decimal separator.
Excel itself takes into account the locale, so it knows that my numbers use a
decimal comma instead.

This inconsistent behaviour between Excel and POI is confusing and difficult.
Can POI also take into account the locale when parsing numeric String values to
Double values?

https://stackoverflow.com/questions/47712773/how-to-get-poi-to-evaluate-and-format-a-cell-exactly-as-excel-does

--
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 61874] OperandResolver.parseDouble(String) does not does not work for numeric Strings using a comma as decimal separator.

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

Nick Burch <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |NEEDINFO
                 OS|                            |All

--- Comment #1 from Nick Burch <[hidden email]> ---
What happens if you save a file with a formula like that, then open it on a
machine in a dot-non-comma locale like EN-GB (UK) or EN-US? Does Excel still
manage to process it properly? Or does the formula start failing?

(I'm wondering if Excel is really using a Locale from the file, or is just
fixing things based on the Locale at runtime. Knowing that helps us decide on
the "least surprising" way to fix this in POI)

--
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 61874] OperandResolver.parseDouble(String) does not does not work for numeric Strings using a comma as decimal separator.

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=61874

--- Comment #2 from Carl Salaets <[hidden email]> ---
After changing the locale, reopening the xlsx, recalculating the formula, the
result changes. Excel always tries to give a result, which may be unexpected.
Here is an overview:

------------------------------------------------------------------

Locale with (1,234.68)
- Thousand Separator = ,
- Decimal Separator  = .

         Value           Format          =TEXT(Value;Format)
                                         Excel           POI
        1234,68         #.##0,00        1234,68         #VALUE!
        1234,68         #,##0.00        1234,68         #VALUE!
        1234.68         #.##0,00        1234.68000      1234.68
        1234.68         #,##0.00        1,234.68        1,234.68

------------------------------------------------------------------

Locale with (1.234,68)
- Thousand Separator = .
- Decimal Separator  = ,

         Value           Format          =TEXT(Value;Format)
                                         Excel           POI
        1234,68         #.##0,00        1.234,68        #VALUE!
        1234,68         #,##0.00        1234,68000      #VALUE!
        1234.68         #.##0,00        1234.68         1234.68
        1234.68         #,##0.00        1234.68         1,234.68

------------------------------------------------------------------

--
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 61874] OperandResolver.parseDouble(String) does not does not work for numeric Strings using a comma as decimal separator.

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=61874

Carl Salaets <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEEDINFO                    |NEW

--
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]