weird Text behaviour

[Hans Schevers <ha...@gmail.com>]

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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: weird Text behaviour

[Dominik Stadler <do...@gmx.at>]

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 <ha...@gmail.com>
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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>