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!