| Summary: | Error occurred at FormulaShifter#rowMoveAreaPtg while shift rows upward. | ||
|---|---|---|---|
| Product: | POI | Reporter: | rhys.wang <shovuu.wang> |
| Component: | POI Overall | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | greeson1, stasdat |
| Priority: | P2 | ||
| Version: | 3.9-FINAL | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | All | ||
| Attachments: | material about the problem | ||
The source I modified in FormulaShifter.java is at line 325. I should say that solution rhys.wang provided is working (tested in production for 2 months). Should be fixed in next release via commit r1517676, thanks for the patch. 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 4.0.4.2 behavior (probably the same as OpenOffice) A3 -> formula=`=SUM(A2:A3)`, cachedValue=Err:522 (circular reference) |
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.