Bug 61516

Summary: problem adjusting cell reference in formula to zeroth row
Product: POI Reporter: Dragan Jovanović <drjovanovic>
Component: SS CommonAssignee: POI Developers List <dev>
Severity: normal    
Priority: P2    
Version: 3.15-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: test cases which demonstrate issue
minimized version of test case
test file, for manual work in Excel

Description Dragan Jovanović 2017-09-13 11:23:41 UTC
Created attachment 35324 [details]
test cases which demonstrate issue

I am trying to implement cell copying algorithm for spreadsheet editor, and I ran into a problem with a test case.
Consider following scenario : there is formula "A1-A2" in cell C3. Algorithm has to copy C3 to C2. Formula should become A0-A1, that is #REF!-A1.

I use FormulaShifter class for adjusting of cell references, and I initialise it with createForRowCopy() method, setting both firstMovedRowIndex and lastMovedRowIndex parameters to 2, since that's index of row where C3 resides. Method adjustFormula() converts A1 to just A.

I have tried a few other possibilities for initialisation of FormulaShifter instance (since there is no documentation, and I am not really sure how those parameters affect FormulaShifter), but all of them fail.

So, if this is not a bug in FormulaShifter, please tell me what is correct way to initialise it. And also what is generally idea with ShiftMode.RowCopy value, that is, in what use cases should I use it.

(Source code resides in attached file.)
Comment 1 Dominik Stadler 2017-09-18 19:42:31 UTC
Not sure if I understand the testcase correctly, but it looks like you have a formula "A1-A2", i.e. row 0, cell 1 and 2 and you are shifting row 2 up by one row, i.e. row 2 (C) onto row 1 (B). Then it seems correct to me that the formula for row 0 (A) is kept in-tact, or?

Maybe you can reduce your test down to the minimum, see the information at https://stackoverflow.com/help/mcve for some hints about that.
Comment 2 Dragan Jovanović 2017-09-19 10:16:24 UTC
Created attachment 35334 [details]
minimized version of test case
Comment 3 Dragan Jovanović 2017-09-19 10:18:57 UTC
Created attachment 35335 [details]
test file, for manual work in Excel
Comment 4 Dragan Jovanović 2017-09-19 10:25:58 UTC
Ok, I have simplified test case a bit (see  attachment 35334 [details]), now it does not include variations on parameter values, and formula is now as trivial as it can be.

"A" is reference to column, not row. A1 is [row0, cell0], as you can see in my source code :
sheet.createRow(0).createCell(0).setCellValue(1); // A1

I am copying from row2 to row1, but these are not C and B (again, letters stand for column references), these are rows marked as 3 and 2 in common spreadsheet editors.

Further, that means that cell reference from formula should shift one row up, that is, A1 becomes A0 (which is undefined, and excel writes it as "#REF!"). You can check this if you open attached xlsx file (attachment 35335 [details]) in spreadsheet editor, and copy cell C3 to C2.

To resume :
- if you open atached file rowShiftExample.xlsx in Excel, and manually copy C3 to C2, you'll get value #REF! In C2;
- if you execute attached application (attachment 35334 [details]) which should do the same thing, you'll get result “A” in variable shiftedFmla.

I hope it is more clear now.
Comment 5 Dominik Stadler 2017-09-19 16:38:30 UTC
doh, sorry, must have been asleep already to mix up "A" vs. row.
Comment 6 Dominik Stadler 2017-09-28 09:57:25 UTC
Thanks, now it was much easier to reproduce and thus fix, see r1809967 for the actual changes.