Bug 54524 - Error occurred at FormulaShifter#rowMoveAreaPtg while shift rows upward.
Summary: Error occurred at FormulaShifter#rowMoveAreaPtg while shift rows upward.
Alias: None
Product: POI
Classification: Unclassified
Component: POI Overall (show other bugs)
Version: 3.9-FINAL
Hardware: PC All
: P2 normal with 1 vote (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2013-02-05 09:18 UTC by rhys.wang
Modified: 2015-08-26 20:40 UTC (History)
2 users (show)

material about the problem (14.71 KB, application/octet-stream)
2013-02-05 09:18 UTC, rhys.wang

Note You need to log in before you can comment on or make changes to this bug.
Description rhys.wang 2013-02-05 09:18:52 UTC
Created attachment 29919 [details]
material about the problem

While shift rows upward, the reference like "A2:A3" in a formula can't be updated normally, an error occurred instead.

Exception stacks as blew:
Exception in thread "main" java.lang.IllegalStateException: Situation not covered: (3, 5, -1, 1, 2)
	at org.apache.poi.ss.formula.FormulaShifter.rowMoveAreaPtg(FormulaShifter.java:331)
	at org.apache.poi.ss.formula.FormulaShifter.adjustPtgDueToRowMove(FormulaShifter.java:152)
	at org.apache.poi.ss.formula.FormulaShifter.adjustPtg(FormulaShifter.java:119)
	at org.apache.poi.ss.formula.FormulaShifter.adjustFormula(FormulaShifter.java:107)
	at org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter.shiftFormula(XSSFRowShifter.java:188)
	at org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter.updateRowFormulas(XSSFRowShifter.java:156)
	at org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter.updateSheetFormulas(XSSFRowShifter.java:143)
	at org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter.updateFormulas(XSSFRowShifter.java:130)
	at org.apache.poi.xssf.usermodel.XSSFSheet.shiftRows(XSSFSheet.java:2364)
	at org.apache.poi.xssf.usermodel.XSSFSheet.shiftRows(XSSFSheet.java:2306)
	at Main.main(Main.java:15)

There are two parts in the attachment uploaded.
1. A sample with a excel file(.xlsx format) and a java source.
2. A modified source(org.apache.poi.ss.formula.FormulaShifter.java) to resolve the problem.

It will be helpful if whether the problem is truely existing and whether the modified is OK are confirmed ASAP.
Thank you.
Comment 1 rhys.wang 2013-02-05 09:21:39 UTC
The source I modified in FormulaShifter.java is at line 325.
Comment 2 Stas 2013-06-26 07:58:28 UTC
I should say that solution rhys.wang provided is working (tested in production for 2 months).
Comment 3 Dominik Stadler 2013-08-26 20:09:51 UTC
Should be fixed in next release via commit r1517676, thanks for the patch.
Comment 4 Javen O'Neal 2015-08-26 20:40:18 UTC
For the record, this fix makes POI consistent with Microsoft Office. Note that other spreadsheets applications may have different behavior.

After sheet.shiftRows(3, 5, -1):

Microsoft Office 2013 behavior:
A3 -> formula=`=SUM(A2:A2)`, cachedValue=1

LibreOffice Calc behavior (probably the same as OpenOffice)
A3 -> formula=`=SUM(A2:A3)`, cachedValue=Err:522 (circular reference)