Testing suggests that the problem occurs when a name is specified following a
sheet name.
With a workbook that contains named areas called One, Two, Three and Four,
the formula SUM(One, Two) will be parsed successfully. The names Three and
Four refer to contiguous areas of cells and the formula
SUM(Sheet2!$A$4:$E$4, Sheet2!$A$5:$E$5), where A4:E4 is the range named as
Three and A5 to E5 is the range named Four, will also be parsed
successfully. However, replacing the cell addresses with the range names
causes a parse exception to be thrown. By this, I mean that this version of
the SUM() formula - SUM(Sheet2!Three, Sheet2!Four) - will cause a
FormulaParseException to be thrown. In this case, the error message is a
little different;
Exception in thread "main" org.apache.poi.ss.formula.FormulaParseException:
Cell reference expected after sheet name at index 12.
at
org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:425)
at
org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:268)
at
org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1119)
at
org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1079)
at
org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1066)
at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1426)
at
org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1526)
at
org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1510)
at
org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1467)
at
org.apache.poi.ss.formula.FormulaParser.Arguments(FormulaParser.java:1051)
at
org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:936)
at
org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:558)
at
org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:517)
at
org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:268)
at
org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1119)
at
org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1079)
at
org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1066)
at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1426)
at
org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1526)
at
org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1510)
at
org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1467)
at
org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1447)
at
org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1568)
at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:176)
at
org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getFormulaTokens(XSSFEvaluationWorkbook.java:148)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:286)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:230)
at
org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:264)
at
org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluate(XSSFFormulaEvaluator.java:117)
at poieventexample.TestSumFormula.<init>(TestSumFormula.java:58)
at
poieventexample.ExampleEventUserModel.main(ExampleEventUserModel.java:296)
and it does seem to suggest that the parser does not support names if they
appear in a formulas agruments and are preceded by the name of a sheet.
My advice would be to log a bug through Bugzilla. When you do this, include
a test case - ideally using JUnit - that fails. Include an Excel workbook
that contains sheets the test code can use to illustrate the problem.
Remember that everyone who works on POI is a volunteer and it is unlikely
that someone will be able to address the problem immediately. It would be
ideal if you could look into the issue yourself, identify the problem and
create a patch that can be used to fix it. You can ask for help on the dev
list and I am confident that someone will be able to point you in the right
direction.
Yours
Mark B
PS In all cases, Excel is perfectly capable of working with the SUM()
formula and named ranges.
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/Parse-intersecting-name-ranges-tp5712565p5712576.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
