Bug 46700 - FormulaParseException thrown, when XSSFRow.shift(...) is called.
Summary: FormulaParseException thrown, when XSSFRow.shift(...) is called.
Status: RESOLVED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.5-dev
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-02-11 22:06 UTC by Matthew
Modified: 2009-02-26 00:21 UTC (History)
0 users



Attachments
excel file for test (10.56 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2009-02-11 22:06 UTC, Matthew
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Matthew 2009-02-11 22:06:03 UTC
Created attachment 23249 [details]
excel file for test

I use POI to open the attached "test.xlsx", call XSSFRow.shift(...) to shift 1 row. But if failed and throw FormulaParseException, it may be caused by the named ranged specified in the formula.

Sample Code:
--

Workbook wb = new XSSFWorkbook("test.xlsx");
Sheet sheet = wb.getSheet("Test");
sheet.shiftRows(4, sheet.getLastRowNum(), 1, true, true);
wb.write(new FileOutputStream("output.xlsx"));


Exception Details:
--
org.apache.poi.ss.formula.FormulaParser$FormulaParseException: Specified named range 'HSI' does not exist in the current workbook.
	at org.apache.poi.ss.formula.FormulaParser.parseNameOrCellRef(FormulaParser.java:440)
	at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:336)
	at org.apache.poi.ss.formula.FormulaParser.parseFunctionReferenceOrName(FormulaParser.java:332)
	at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:733)
	at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:693)
	at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:680)
	at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:982)
	at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1083)
	at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1067)
	at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1024)
	at org.apache.poi.ss.formula.FormulaParser.Arguments(FormulaParser.java:665)
	at org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:562)
	at org.apache.poi.ss.formula.FormulaParser.parseFunctionReferenceOrName(FormulaParser.java:324)
	at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:733)
	at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:693)
	at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:680)
	at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:982)
	at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1083)
	at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1067)
	at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1024)
	at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1003)
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1125)
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:192)
	at org.apache.poi.xssf.usermodel.XSSFRow.shiftFormula(XSSFRow.java:454)
	at org.apache.poi.xssf.usermodel.XSSFRow.shift(XSSFRow.java:426)
	at org.apache.poi.xssf.usermodel.XSSFSheet.shiftRows(XSSFSheet.java:1457)
	at com.dttn.excel.connector.TestPOI.main(TestPOI.java:98)
Comment 1 Yegor Kozlov 2009-02-26 00:21:11 UTC
shiftRows fails because of an unknown name UOM. This undefined name is used in formulas in the cells H2 and H3, and also in the data validation rule for J3.

The formula in H2 that can't be parsed looks as follows:
IF(ISERROR(VLOOKUP("",UOM,2,FALSE)),"",VLOOKUP("",UOM,2,FALSE))

Shifting a formula in XSSF includes the following operations:
 - parse formula into an array of tokens (Ptgs)
 - update column and row references of the parsed tokens according to the shifting rule
 - serialize the updated Ptgs into string
 - update the formula value in the cell

To parse a formula all its parts must be defined. If FormulaParser fails to resolve a token it throws FormulaParseException as in your case. 

Please note, that FormulaParser has been recently updated to handle names with backslashes. With this change the exception message is different from reported. Current code in trunk throws org.apache.poi.ss.formula.FormulaParser$FormulaParseException: Specified named range 'UOM' does not exist in the current workbook.

Yegor