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
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.
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) */