Bug 64517 - The formula in the cell cannot be modified correctly
Summary: The formula in the cell cannot be modified correctly
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 4.1.1-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2020-06-11 06:12 UTC by 蒋勇兵
Modified: 2020-08-13 09:33 UTC (History)
0 users

The file used to reproduce the bug (22.00 KB, application/vnd.ms-excel)
2020-06-11 06:12 UTC, 蒋勇兵

Note You need to log in before you can comment on or make changes to this 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:
Expected value:
Actual value:

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());
        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

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.