Bug 61874

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 CommonAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: normal    
Priority: P2    
Version: 3.17-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description Carl Salaets 2017-12-08 14:56:38 UTC
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
Comment 1 Nick Burch 2017-12-11 19:17:35 UTC
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)
Comment 2 Carl Salaets 2017-12-13 10:17:30 UTC
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

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