Bug 63228 - Method copyCellFrom (org.apache.poi.xssf.usermodel.XSSFCell) doesn't update formula reference
Summary: Method copyCellFrom (org.apache.poi.xssf.usermodel.XSSFCell) doesn't update ...
Status: RESOLVED INFORMATIONPROVIDED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.17-FINAL
Hardware: PC All
: P3 normal with 4 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-03-04 10:28 UTC by Nikoloz Gochiashvili
Modified: 2021-10-08 18:43 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Nikoloz Gochiashvili 2019-03-04 10:28:40 UTC
When i copy formula with reference to other cell, the new formula has old reference. 

I am using org.apache.poi.xssf.usermodel.XSSFCell copyCellFrom method, with copyCellFormula=true.

Example:

B5 = B2+1  after copy to C5 = B2+1 instead of C5 = C2+1
Comment 1 FINA 2019-03-06 04:57:59 UTC
There is a lot of cases when you may need such feature when for instance you are summing 3 items on column B ( i.e B1=B10+B11+B12) and after copying cell from B1 to C1 you expect formula C1=C10+C11+C12, but instead of that you see same formula as it was in B1   ( C1= B10+B11+B12 which is wrong). 

Any idea how and/or when to correct it?   
Or maybe someone knows the workaround ?

Thank you in advance for any response or advise.
Comment 2 PJ Fanning 2021-10-08 18:43:02 UTC
copyCellFrom javadoc says that it does not shift references in formulas - the XSSFRow copyRowFrom does update formulas but the cell copy doesn't

    /**
     * Copy cell value, formula and style, from srcCell per cell copy policy
     * If srcCell is null, clears the cell value and cell style per cell copy policy
     *
     * This does not shift references in formulas. Use {@link org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter} to shift references in formulas.
     *
     * @param srcCell The cell to take value, formula and style from
     * @param policy The policy for copying the information, see {@link CellCopyPolicy}
     * @throws IllegalArgumentException if copy cell style and srcCell is from a different workbook
     * @see CellUtil#copyCell(Cell, Cell, CellCopyPolicy, CellCopyContext)
     */