[Bug 61516] New: problem adjusting cell reference in formula to zeroth row

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 61516] New: problem adjusting cell reference in formula to zeroth row

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61516

            Bug ID: 61516
           Summary: problem adjusting cell reference in formula to zeroth
                    row
           Product: POI
           Version: 3.15-FINAL
          Hardware: PC
            Status: NEW
          Severity: normal
          Priority: P2
         Component: SS Common
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

Created attachment 35324
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=35324&action=edit
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.)

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61516] problem adjusting cell reference in formula to zeroth row

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61516

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61516] problem adjusting cell reference in formula to zeroth row

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61516

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |NEEDINFO

--- Comment #1 from Dominik Stadler <[hidden email]> ---
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.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61516] problem adjusting cell reference in formula to zeroth row

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61516

--- Comment #2 from Dragan Jovanović <[hidden email]> ---
Created attachment 35334
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=35334&action=edit
minimized version of test case

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61516] problem adjusting cell reference in formula to zeroth row

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61516

--- Comment #3 from Dragan Jovanović <[hidden email]> ---
Created attachment 35335
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=35335&action=edit
test file, for manual work in Excel

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61516] problem adjusting cell reference in formula to zeroth row

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61516

Dragan Jovanović <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEEDINFO                    |NEW

--- Comment #4 from Dragan Jovanović <[hidden email]> ---
Ok, I have simplified test case a bit (see  attachment 35334), 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) 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) which should do the
same thing, you'll get result “A” in variable shiftedFmla.

I hope it is more clear now.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61516] problem adjusting cell reference in formula to zeroth row

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61516

--- Comment #5 from Dominik Stadler <[hidden email]> ---
doh, sorry, must have been asleep already to mix up "A" vs. row.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61516] problem adjusting cell reference in formula to zeroth row

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61516

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
         Resolution|---                         |FIXED

--- Comment #6 from Dominik Stadler <[hidden email]> ---
Thanks, now it was much easier to reproduce and thus fix, see r1809967 for the
actual changes.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]