Apache POI update formula references when copying

Question

Is there a way to update formula references when copying a formula in Apache POI?

Say in Excel you have in row 1 the formula =A1/B1. If you copy-paste it, say in row 5, the formula becomes =A5/B5.

In Apache POI if you run the lines

r5.getCell(2).setCellType(CellType.FORMULA);
r5.getCell(2).setCellFormula(r1.getCell(2).getCellFormula());

the formula remains =A1/B1.

Answer 1

Your code is not copy/pasting something but gets the formula string from one cell and sets exactly this formula string to another cell. This will not changing the formula string. How even should it do?

So the need is to get the the formula string from one cell and then adjust this formula string to the target cell.

Since apache poi is able to evaluate formulas, it must also be able to parse formulas. The parsing classes are in the packages org.apache.poi.ss.formula and org.apache.poi.ss.formula.ptg.

So we can use those classes to adjust the formula string to the target cell.

Example:

Following Excel workbook:

and following code:

import java.io.FileInputStream;

import org.apache.poi.ss.formula.*;
import org.apache.poi.ss.formula.ptg.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.ss.util.CellAddress;

public class ExcelCopyFormula {

 private static String copyFormula(XSSFSheet sheet, String formula, int coldiff, int rowdiff) {

  XSSFEvaluationWorkbook workbookWrapper = 
   XSSFEvaluationWorkbook.create((XSSFWorkbook) sheet.getWorkbook());
  Ptg[] ptgs = FormulaParser.parse(formula, workbookWrapper, FormulaType.CELL
   , sheet.getWorkbook().getSheetIndex(sheet));

  for (int i = 0; i < ptgs.length; i++) {
   if (ptgs[i] instanceof RefPtgBase) { // base class for cell references
    RefPtgBase ref = (RefPtgBase) ptgs[i];
    if (ref.isColRelative())
     ref.setColumn(ref.getColumn() + coldiff);
    if (ref.isRowRelative())
     ref.setRow(ref.getRow() + rowdiff);
   }
   else if (ptgs[i] instanceof AreaPtgBase) { // base class for range references
    AreaPtgBase ref = (AreaPtgBase) ptgs[i];
    if (ref.isFirstColRelative())
     ref.setFirstColumn(ref.getFirstColumn() + coldiff);
    if (ref.isLastColRelative())
     ref.setLastColumn(ref.getLastColumn() + coldiff);
    if (ref.isFirstRowRelative())
     ref.setFirstRow(ref.getFirstRow() + rowdiff);
    if (ref.isLastRowRelative())
     ref.setLastRow(ref.getLastRow() + rowdiff);
   }
  }

  formula = FormulaRenderer.toFormulaString(workbookWrapper, ptgs);
  return formula;
 }

 public static void main(String[] args) throws Exception {

  XSSFWorkbook workbook = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("test.xlsx"));  
  XSSFSheet sheet = workbook.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) {
    if (cell.getCellTypeEnum() == CellType.FORMULA) {
     CellAddress source = cell.getAddress();
     String formula = cell.getCellFormula();
     System.out.print(source + "=" + formula);
     int rowdiff = 3;
     int coldiff = -2;
     CellAddress target = new CellAddress(source.getRow() + rowdiff, source.getColumn() + coldiff);
     String newformula = copyFormula(sheet, formula, coldiff, rowdiff);
     System.out.println("->" + target + "=" + newformula);
    }
   }
  }

  workbook.close();
 }
}

leads to following output:

E3=C3/D3->C6=A6/B6
E4=$C4/D$4->C7=$C7/B$4
E5=SUM(C3:D5)->C8=SUM(A6:B8)
E6=SUM(C$3:$D6)->C9=SUM(A$3:$D9)
E7=C3+SUM(C3:D7)->C10=A6+SUM(A6:B10)
E8=C$3+SUM($C3:D$8)->C11=A$3+SUM($C6:B$8)