Bug 53320 - FormulaShifter doesn't take care to absolute references
Summary: FormulaShifter doesn't take care to absolute references
Status: REOPENED
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
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-05-29 14:43 UTC by j.brauge
Modified: 2016-06-15 10:51 UTC (History)
1 user (show)



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

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 :
=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.