Bug 53320 - FormulaShifter doesn't take care to absolute references
Summary: FormulaShifter doesn't take care to absolute references
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.8-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2012-05-29 14:43 UTC by j.brauge
Modified: 2016-06-15 10:51 UTC (History)
1 user (show)

Clone formula (5.94 KB, application/octet-stream)
2012-05-29 14:43 UTC, j.brauge
Template excel (8.07 KB, application/octet-stream)
2012-05-29 14:46 UTC, j.brauge

Note You need to log in before you can comment on or make changes to this bug.
Description j.brauge 2012-05-29 14:43:41 UTC
Created attachment 28856 [details]
Clone formula

Shift a formula that contains absolute cells references with method adjustFormula change abosulte rows references.
Comment 1 j.brauge 2012-05-29 14:46:50 UTC
Created attachment 28857 [details]
Template excel

Template excel.
Cell A1 contains formula :

After copy and shitfing formula from cell A1 to cell A2, we have:
instead of
Comment 2 j.brauge 2012-05-29 14:50:18 UTC
Adding test on isRowRelative() in rowMoveRefPtg (in FormulaShifter) seems toworks.

private Ptg rowMoveRefPtg(RefPtgBase rptg) {
		int refRow = rptg.getRow();
		if (_firstMovedIndex <= refRow && refRow <= _lastMovedIndex) {
			// Rows being moved completely enclose the ref.
			// - move the area ref along with the rows regardless of destination
            if (rptg.isRowRelative()) 
                rptg.setRow(refRow + _amountToMove); 
			return rptg;
Comment 3 Javen O'Neal 2015-10-29 09:18:55 UTC
I think this is fixed in POI 3.13, if not earlier.

I'm using XSSFSheet.shiftRows and formulas with absolute row references are getting adjusted, if my memory serves correctly.
Comment 4 Javen O'Neal 2016-04-12 02:25:07 UTC
This was fixed for row copy but not row move (Sheet.shiftrows)
Comment 5 Javen O'Neal 2016-06-15 10:51:09 UTC
This behavior may be by design. Shift rows isn't the same as insert rows. I'm not sure if it's the same as cut-paste, either. I'd be happy to discuss this on the dev mailing list.