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 :
=SI($C1=O$1;SI(STXT($F1;1;1)="6";$H1-$I1;$I1-$H1);0)

After copy and shitfing formula from cell A1 to cell A2, we have:
=SI($C2=O$2;SI(STXT($F2;1;1)="6";$H2-$I2;$I2-$H2);0)
instead of
=SI($C2=O$1;SI(STXT($F2;1;1)="6";$H2-$I2;$I2-$H2);0)
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)
https://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/FormulaShifter.java?revision=1722410&view=markup
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.