| Summary: | problem adjusting cell reference in formula to zeroth row | ||
|---|---|---|---|
| Product: | POI | Reporter: | Dragan Jovanović <drjovanovic> |
| Component: | SS Common | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| 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 |
||
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. Created attachment 35334 [details]
minimized version of test case
Created attachment 35335 [details]
test file, for manual work in Excel
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. doh, sorry, must have been asleep already to mix up "A" vs. row. |
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.)