Bug 61840

Summary: Shifting rows loses formula references
Product: POI Reporter: Luca <miaposta21_>
Component: SS CommonAssignee: POI Developers List <dev>
Status: RESOLVED WORKSFORME    
Severity: normal    
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description Luca 2017-12-01 11:41:14 UTC
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!").
Comment 1 Nick Burch 2017-12-01 12:26:11 UTC
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)
Comment 2 Luca 2017-12-01 14:23:40 UTC
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.
Comment 3 Javen O'Neal 2017-12-01 17:27:02 UTC
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());
}
Comment 4 Javen O'Neal 2017-12-01 17:34:04 UTC
(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.
Comment 5 Javen O'Neal 2017-12-01 17:48:16 UTC
Added a unit test in r1816892.