Bug 62073 - FormulaParser does not work internationally
Summary: FormulaParser does not work internationally
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: unspecified
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2018-02-02 12:36 UTC by Dragan Jovanović
Modified: 2018-02-07 07:03 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Dragan Jovanović 2018-02-02 12:36:59 UTC
Imagine following scenario :
User of my spreadsheet-editing app enters a formula =6,6*A1 under his/her (for example) German culture. User obviously wants to multiply A1 value by 6.6 . But , inside FormulaParser comma sign has a fixed meaning described as 
 * <function> ::= <functionName> ([expression [, expression]*])
, so it can not recognise the string 6,6 as number. 

Did I miss something ? Is there a way to get over this problem using FormulaParser ? Or should I somehow pre-process this string before I pass it to FormulaParser ? If so, I also need to know what is general purpose of comma sign in those formulae.

I may also try to alter FormulaParser class in a way that - for some locales - it interprets comma as decimal separator, and uses some other character (; for example) for expression separator.
Comment 1 Nick Burch 2018-02-02 15:27:21 UTC
Excel formulas are always stored in US format. (To check, type a German formula into a spreadsheet, save as .xlsx, unzip the .xlsx and look at the formula stored in the sheet1.xml file)

Because Excel files always use US format for storage internally, and that's what you'll get back from POI if you read a german-set formula, POI also uses US format for all evaluations
Comment 2 Dragan Jovanović 2018-02-07 07:03:53 UTC
I have copied a few POI classes to my project, and made some changes in them, so now my project is able to parse and render formula strings in format different than US one. These are differences between formats which I implemented :
//			US      EU
//decimal separator       .       ,
//argument separator      ,       ;
//array item separators   , ;     . ;
So if some-when you want to integrate that functionality into POI, write me an email.