Bug 49457 - Exception while parsing SUM formula
Summary: Exception while parsing SUM formula
Status: RESOLVED DUPLICATE of bug 45909
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.6-FINAL
Hardware: PC Windows Vista
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-06-17 12:11 UTC by Eduard Dudar
Modified: 2010-06-17 13:50 UTC (History)
0 users


Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Eduard Dudar 2010-06-17 12:11:04 UTC 
Exception fails when we try to write HSSF workbook: "org.apache.poi.ss.formula.FormulaParseException: Parse error near char 7 ';' in specified formula 'SUM(D19;D23)'. Expected ',' or ')'". When I made proposed change and replaced 'SUM(D19;D23)' with 'SUM(D19,D23)' exception disappeared. However, 'SUM(D19;D23)' is valid formula in Excel and 'SUM(D19,D23)' is not.

Complete stacktrace is here:
	org.apache.poi.ss.formula.FormulaParser.expected(FormulaParser.java:217)
	org.apache.poi.ss.formula.FormulaParser.Arguments(FormulaParser.java:1049)
	org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:929)
	org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:551)
	org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:423)
	org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:266)
	org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1112)
	org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1072)
	org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1059)
	org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1410)
	org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1510)
	org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1494)
	org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1451)
	org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1431)
	org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1552)
	org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:174)
	org.apache.poi.hssf.model.HSSFFormulaParser.parse(HSSFFormulaParser.java:72)
	org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:588)
Comment 1 Josh Micich 2010-06-17 13:50:53 UTC 
Excel encodes formulas into a representation independent of the characters visible in the human readable formula.  In some regions, Excel displays (and parses) commas for argument delimiters, and in others, semicolons.  POI currently does not support internationalisation for formula rendering/parsing, and  effectively uses a default region of 'United States'.

The work around is simple - use comma instead of semicolon (as you mentioned already).  Unfortunately, there will be small differences between the way POI and Excel render/parse formulas in your region.

Contributions are always welcome.  To fix this will involve changes to the FormulaParser class.  You'd need to deal with more than just the argument delimiter: the decimal separator, and array element delimiter are probably also region specific.  I am not sure what other elements of Excel formulas are region specific.

*** This bug has been marked as a duplicate of bug 45909 ***