Hi POI, 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): StackTrace: 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)
Created attachment 32508 [details] Excel-File (POI_BUG.xlsx) to trigger a FormulaParseException. And Fix for org.apache.poi.ss.formula.FormulaParser.parseSheetName()
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?
Hi POI, i just tried with the version 3.12 beta 2. The problem still occurs.
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!
This patch is years out of date and code has improved over many POI releases. Try with latest POI release and reopen if you can provide a reproducible test case.