Bug 57620 (GYO) - The formula-parts are not correctly parsed by the method org.apache.poi.ss.formula.FormulaParser.parseSheetName() and that leads to the FormulaParseException
Summary: The formula-parts are not correctly parsed by the method org.apache.poi.ss.fo...
Alias: GYO
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.10-FINAL
Hardware: PC All
: P2 critical (vote)
Target Milestone: ---
Assignee: POI Developers List
Keywords: APIBug
Depends on:
Reported: 2015-02-23 11:03 UTC by GYO
Modified: 2015-02-25 10:09 UTC (History)
1 user (show)

Excel-File (POI_BUG.xlsx) to trigger a FormulaParseException. And Fix for org.apache.poi.ss.formula.FormulaParser.parseSheetName() (8.55 KB, application/zip)
2015-02-23 11:04 UTC, GYO

Note You need to log in before you can comment on or make changes to this bug.
Description GYO 2015-02-23 11:03:19 UTC

if we try to call the method org.apache.poi.ss.usermodel.Sheet.shiftRows() processing the attached excel-file, we get a FormulaParseException. The reason for that is that the api generates a prefix at the beginning of the formula that cannot be parsed later by the method org.apache.poi.ss.formula.FormulaParser.parseSheetName(): the generated prefix will be '[1]' for the concrete situation shown in the attached excel-file (POI_BUG.xlsx).

You can reproduce the problem by loading the attached excel-file (POI_BUG.xlsx) and try to shift any row by calling org.apache.poi.ss.usermodel.Sheet.shiftRows(). You will get a StackTrace like the one shown below.

Mind: the source of the problem is, like mentioned above, the method org.apache.poi.ss.formula.FormulaParser.parseSheetName(): The formula-parts are not correctly parsed and that leads to the FormulaParseException. We have developed a fix that we would like to suggest to you. see attached txt-file (FIX_parseSheetname.txt):


org.apache.poi.ss.formula.FormulaParseException: Parse error near char 0 '[' in specified formula '[1]!lucanet("fin","ist","Rohdaten",C10,$B10,"m",F$4)'. Expected number, string, or defined name
	at org.apache.poi.ss.formula.FormulaParser.expected(FormulaParser.java:219)
	at org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:549)
	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)
	at org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter.shiftFormula(XSSFRowShifter.java:189)
	at org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter.updateRowFormulas(XSSFRowShifter.java:153)
	at org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter.updateSheetFormulas(XSSFRowShifter.java:140)
	at org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter.updateFormulas(XSSFRowShifter.java:127)
	at org.apache.poi.xssf.usermodel.XSSFSheet.shiftRows(XSSFSheet.java:2465)
Comment 1 GYO 2015-02-23 11:04:26 UTC
Created attachment 32508 [details]
Excel-File (POI_BUG.xlsx) to trigger a FormulaParseException. And Fix for org.apache.poi.ss.formula.FormulaParser.parseSheetName()
Comment 2 Nick Burch 2015-02-23 14:01:30 UTC
3.10 is a little old, and there have been quite a few Formula Parser fixes since then

Any chance you could try with the 3.12 beta 1 release candidate, and see if that's already fixed?
Comment 3 GYO 2015-02-25 09:58:34 UTC

i just tried with the version 3.12 beta 2. 
The problem still occurs.
Comment 4 Nick Burch 2015-02-25 10:09:34 UTC
Any chance you could re-format your proposed fix as a patch? http://poi.apache.org/guidelines.html#SubmittingPatches has some advice on that

Also, if you could write a small unit test based on your test file, which shows the problem + verifies that your fix corrects it, that'd be most helpful!