Bug 45909

Summary: isArgumentDelimiter does not recognise semi-colon as delimiter
Product: POI Reporter: Torben W <torben.wolm>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED WONTFIX    
Severity: normal CC: eduard.dudar
Priority: P2    
Version: 3.1-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows Vista   

Description Torben W 2008-09-29 04:26:59 UTC
When opening a spreadsheet that contains formulas which use ';' as argument delimiter, I get a FormulaParseException.

For example:

org.apache.poi.hssf.model.FormulaParser$FormulaParseException: Parse error near char 19 ';' in specified formula 'ROUND((K1171/X1171);1)'. Expected ',' or ')'
	at org.apache.poi.hssf.model.FormulaParser.expected(FormulaParser.java:139)
	at org.apache.poi.hssf.model.FormulaParser.Arguments(FormulaParser.java:410)
	at org.apache.poi.hssf.model.FormulaParser.function(FormulaParser.java:308)


This was not the case in version 2.5 of POI. Is there a particular reason that "isArgumentDelimiter" does not accept ";"?

    private static boolean isArgumentDelimiter(char ch) {
        return ch ==  ',' || ch == ')';
    }

It is quite a big task if we have to run through all our template XLS-files to check for formulas and update the delimiters...

Kind regards
Torben
Comment 1 Josh Micich 2008-09-29 14:25:55 UTC
POI consistently treats formula argument delimiters as commas. Depending on internationalisation settings Excel will use ',' or ';' (but never both).  In other words, you can enter a formula with POI using commas, and it *may* display in Excel with semicolons. Conversely, a formula entered in Excel with semi-colons will render with commas in POI.

The choice of comma or semi-colon is not encoded in the excel files at all.  On windows systems, it is found in the 'Regional and Language Options' of Control Panel.  (So you don't need to worry about converting any XLS files).

The only way this exception can occur is if you supply a formula to POI using semicolons.  An easy work around is to make sure you use commas as argument delimiters in any formulas you manipulate with java code.  Were you doing something else?  If so, can you re-open and supply more details (full POI stack trace, sample code, sample files)?

Use of semicolons in formulas was already mentioned in bug 45025.  Adding an internationalision feature to POI is possible, but probably not worth the effort.
Comment 2 Josh Micich 2010-06-17 13:50:53 UTC
*** Bug 49457 has been marked as a duplicate of this bug. ***