| Summary: | OperandResolver.parseDouble(String) does not does not work for numeric Strings using a comma as decimal separator. | ||
|---|---|---|---|
| Product: | POI | Reporter: | Carl Salaets <carl.salaets> |
| Component: | SS Common | Assignee: | POI Developers List <dev> |
| Status: | NEW --- | ||
| Severity: | normal | ||
| Priority: | P2 | ||
| Version: | 3.17-FINAL | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | All | ||
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) 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 ------------------------------------------------------------------ |
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