[Bug 61840] New: Shifting rows loses formula references

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

[Bug 61840] New: Shifting rows loses formula references

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

            Bug ID: 61840
           Summary: Shifting rows loses formula references
           Product: POI
           Version: unspecified
          Hardware: PC
            Status: NEW
          Severity: normal
          Priority: P2
         Component: SS Common
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

If you have a cell with a formula with some references (eg. "=(B5-C5)/B5") and
you shift the row with Sheet.shiftRows(int,int,int) (eg.
sheet.shiftRows(4,4,-1)) you lose the references and get
"=(#REF!-#REF!)/#REF!").

--
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 61840] Shifting rows loses formula references

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

Nick Burch <[hidden email]> changed:

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

--- Comment #1 from Nick Burch <[hidden email]> ---
What version of Apache POI are you using? If it isn't the latest, what happens
when you upgrade? (We've done quite a few fixes in this area lately)

--
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 61840] Shifting rows loses formula references

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=61840

Luca <[hidden email]> changed:

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

--- Comment #2 from Luca <[hidden email]> ---
I'm using 3.17. It's the latest if I'm not mistaken. While I'm here I add the
following issue: please make sure that references contained in the formula are
updated when rows are shifted (same as when you copy paste formulas in Excel)
and also when cells are copied. Because now, if you copy paste a formula from a
cell into another cell with cell.setCellType(CellType.FORMULA);
setCellFormula(anotherCell.getCellFormula()); formula is treated as a mere
string and references are not updated.

--
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 61840] Shifting rows loses formula references

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=61840

--- Comment #3 from Javen O'Neal <[hidden email]> ---
This works for me in the latest version of POI (and probably also 3.17).
You will get #REF! errors if you shift references above the 1st row in the
workbook, which is the same behavior as Excel.

You are getting #REF! errors likely from one of two scenarios:
1) Formula: "(B1-C1)/B1", shiftRows(x, x, -1)
2) Formula: "(B5-C5)/B5", shiftRows(x, x, -5)

I wrote this unit test to test POI's functionality:
@Test
public void test61840_shifting_rows_up_does_not_produce_REF_errors() throws
Exception {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sheet = wb.createSheet();
    Cell cell = sheet.createRow(4).createCell(0);
    cell.setCellFormula("(B5-C5)/B5");
    sheet.shiftRows(4, 4, -1);
    Cell shiftedCell = sheet.getRow(3).getCell(0);
    assertNotNull(shiftedCell);
    assertEquals("(B4-C4)/B4", shiftedCell.getCellFormula());
}

--
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 61840] Shifting rows loses formula references

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=61840

--- Comment #4 from Javen O'Neal <[hidden email]> ---
(In reply to Luca from comment #2)
> setCellFormula(anotherCell.getCellFormula()); formula is treated as a mere
> string and references are not updated.

setCellFormula intentionally does not shift the formula that is pasted into it.
It sets the cell formula as is.
Perhaps what you're looking for is a function that copies a cell value from
another cell, and if the source cell contains a formula, to shift that formula.
Currently POI only contains methods to copy entire rows (Sheet.copyRows).
Adding capability for copying just a cell is a good idea, and should be
captured on a new bug.

--
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 61840] Shifting rows loses formula references

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=61840

Javen O'Neal <[hidden email]> changed:

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

--- Comment #5 from Javen O'Neal <[hidden email]> ---
Added a unit test in r1816892.

--
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]