|Summary:||problem adjusting cell reference in formula to zeroth row|
|Product:||POI||Reporter:||Dragan Jovanović <drjovanovic>|
|Component:||SS Common||Assignee:||POI Developers List <dev>|
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.