Bug 64517

Summary: The formula in the cell cannot be modified correctly
Product: POI Reporter: 蒋勇兵 <253684597>
Component: HSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: normal    
Priority: P2    
Version: 4.1.1-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: The file used to reproduce the bug

Description 蒋勇兵 2020-06-11 06:12:12 UTC
Created attachment 37304 [details]
The file used to reproduce the bug

When I use HSSFCell#setCellFormula to modify the formula in a cell, if I just adjust some cells in the formula, e.g.for AVERAGE(A1:F1) to AVERAGE(A2:F1), HSSFCell#setCellFormula will be wrong modify the row of the second cell to the row of the first cell, but the first cell will not be modified!

before fixing:
AVERAGE(A1:F1)
Expected value:
AVERAGE(A2:F1)
Actual value:
AVERAGE(A1:F2)

Below is the test code to reproduce this problem:
    public static void main(String[] args) throws IOException {
        Workbook workbook = WorkbookFactory.create(new File("E:\\Average.xls"));
        Row row = workbook.getSheetAt(0).getRow(0);
        Cell cell = row.getCell(6);
        System.out.println("formula before modify:" + cell.getCellFormula());
        cell.setCellFormula("B2:F1");
        System.out.println("formula after modify:" + cell.getCellFormula());
    }

The Average.xls in the attached file.
Comment 1 Miłosz Rembisz 2020-08-13 09:33:53 UTC
Hi,

I wonder if this is really a bug, and not an expected behavior in Office.

When you input range "A2:F1", you mean "a smallest rectangle containing both A2 and F1 cells", and having such rectangle it is further referred by top-left and bottom-right corners - which in your case would be A1 and F2 (A2 and F1 which were provided are actually bottom-left and top-right corners).

To confirm that I just tried to input range "A2:F1" directly in LibreOffice and formula was switched to "A1:F2" also.