weird Text behaviour

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

weird Text behaviour

Hans Schevers
Hi all,

I found some weird behaviour when evaluating the cell formula  '=TEXT(2,45;"0,00")':

As expected, in Excel this results to the number 2,45 (Dutch Local so the decimal character is ',').

Using POI and the FormulaEvaluator().evaluate(...) it returns '0.02'. I expected 2.45.

Could this be a bug or do I need to set some extra Local properties?

thanks in advance.

cheers,
Hans









---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: weird Text behaviour

Dominik Stadler
Hi,

It seems to be caused by the format-pattern "0,00". In Europe, the "," is
the decimal separator, but in the US this is the thousands-separator, so it
seems the "0,02"  is resulting from seeing the pattern "0,00" the US-way.

It seems Excel somehow still interprets this as decimal-separator in this
case, maybe via some heuristic or when the locale is European.

In LibreOffice (sorry, no Excel here at the moment), if you enter "0.00" as
text-pattern instead, it will still display it correctly, but also Apache
POI will use the correct format.

By using something like Locale.setDefault(Locale.GERMAN); you can force the
European digit-formatting in the resulting text in Apache POI.

Dominik.

On Fri, Jan 17, 2020 at 7:55 PM Hans Schevers <[hidden email]>
wrote:

> Hi all,
>
> I found some weird behaviour when evaluating the cell formula
> '=TEXT(2,45;"0,00")':
>
> As expected, in Excel this results to the number 2,45 (Dutch Local so the
> decimal character is ',').
>
> Using POI and the FormulaEvaluator().evaluate(...) it returns '0.02'. I
> expected 2.45.
>
> Could this be a bug or do I need to set some extra Local properties?
>
> thanks in advance.
>
> cheers,
> Hans
>
>
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>