Bug 53320

Summary: FormulaShifter doesn't take care to absolute references
Product: POI Reporter: j.brauge
Component: XSSFAssignee: POI Developers List <dev>
Status: REOPENED ---    
Severity: normal CC: onealj
Priority: P2    
Version: 3.8-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: Clone formula
Template excel

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.