You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Raendl91 <wa...@gmx.at> on 2013/04/17 12:37:52 UTC
Parse intersecting name ranges
Hi.
I have problems to parse formulas which contain intersecting name ranges.
For example if I try to parse a formula like
"=SUM((Total_Cost!Jan):(Total_Cost!Apr_))" where Total_Cost, Jan and Apr_
are name ranges which overlap i receive the following exception:
Exception in thread "main" org.apache.poi.ss.formula.FormulaParseException:
Parse error near char 16 'J' in specified formula 'SUM((Total_Cost
Jan):(Total_Cost Apr_))'. Expected ')'
at org.apache.poi.ss.formula.FormulaParser.expected(FormulaParser.java:219)
at org.apache.poi.ss.formula.FormulaParser.Match(FormulaParser.java:251)
at
org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1108)
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:429)
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)
Is the Parser just not able to parse such formulas or am I doing something
wrong?
Rudi
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Parse-intersecting-name-ranges-tp5712565.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Parse intersecting name ranges
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Parse intersecting name ranges
Posted by Raendl91 <wa...@gmx.at>.
Thank you for your help.
Just to be clear:
If I type in the formula as "=SUM((Total_Cost!Jan):(Total_Cost!Apr_))" I get
the right result in Excel but an exception if i try to parse it wit POI.
With comma in stead of colon i receive an Err:508 in Excel.
if I only type "=Total_Cost!Jan" it works in Excel but the POI throws an
exception.
I can send you the file if you want...
(Excel in my case is LibreOffice Calculater on Ubuntu but I've tried it also
with MS Excel today -> same result.)
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Parse-intersecting-name-ranges-tp5712565p5712575.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Parse intersecting name ranges
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Let me have a play with some code today, but I am concerned that you speak of
an Err:508 as that suggests that even Excel is experiencing problems with
the formula.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Parse-intersecting-name-ranges-tp5712565p5712574.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Parse intersecting name ranges
Posted by Raendl91 <wa...@gmx.at>.
Exactly. Total_Cost!Jan and Total_Cost!Apr_ refer both to only one cell so it
should be equal to something like SUM(A1:A35).
Nevertheless I have tried your suggestion but it results in an Err:508. Even
if I type only "=Total_Cost!Jan" in a cell it results in a similar
exception:
Exception in thread "main" org.apache.poi.ss.formula.FormulaParseException:
Unused input [Jan] after attempting to parse the formula [Total_Cost Jan]
at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1573)
at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:176)
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Parse-intersecting-name-ranges-tp5712565p5712572.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Parse intersecting name ranges
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Can I just ask what you expect that SUM() formula to produce. Using the colon
separator seems to indicate that you are trying to sum the cells between one
named area and another one, an analogue for SUM(A1:A35), where the named
areas refer to single cells. If you want to sum the cells in two different
named areas, should not the formula look something like this
SUM(Sheet1!Area1, Sheet1!Area2) ?
That may not be the cause of the problem of course but I do wonder if the
formula parser is objecting to the use of the colon in this specific case.
Could you try replacing the colon with a comma in the example you gave to
see what the results of this are please?
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Parse-intersecting-name-ranges-tp5712565p5712571.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org