Bug 61516 - problem adjusting cell reference in formula to zeroth row
Summary: problem adjusting cell reference in formula to zeroth row
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.15-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2017-09-13 11:23 UTC by Dragan Jovanović
Modified: 2017-09-28 09:57 UTC (History)
0 users

test cases which demonstrate issue (2.32 KB, text/plain)
2017-09-13 11:23 UTC, Dragan Jovanović
minimized version of test case (1.98 KB, text/plain)
2017-09-19 10:16 UTC, Dragan Jovanović
test file, for manual work in Excel (6.42 KB, application/zip)
2017-09-19 10:18 UTC, Dragan Jovanović

Note You need to log in before you can comment on or make changes to this bug.
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.