61874
2017-12-08 14:56:38 +0000
OperandResolver.parseDouble(String) does not does not work for numeric Strings using a comma as decimal separator.
2018-01-11 11:55:49 +0000
1
1
1
Unclassified
POI
SS Common
3.17-FINAL
PC
All
NEW
P2
normal
---
1
carl.salaets
dev
0
oldest_to_newest
202638
0
carl.salaets
2017-12-08 14:56:38 +0000
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
202724
1
apache
2017-12-11 19:17:35 +0000
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)
202751
2
carl.salaets
2017-12-13 10:17:30 +0000
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
------------------------------------------------------------------