3

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.

Luke
  • 1,633
  • 3
  • 23
  • 37

2 Answers2

10

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:

enter image description here

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) { // up to apache poi version 3
    if (cell.getCellType() == CellType.FORMULA) { // since apache poi version 4
     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)

Updated String copyFormula(Sheet sheet, String formula, int coldiff, int rowdiff) method which works for SS that is for HSSFas well as for XSSF:

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

  Workbook workbook = sheet.getWorkbook();
  EvaluationWorkbook evaluationWorkbook = null;
  if (workbook instanceof HSSFWorkbook) {
   evaluationWorkbook = HSSFEvaluationWorkbook.create((HSSFWorkbook) workbook);
  } else if (workbook instanceof XSSFWorkbook) {
   evaluationWorkbook = XSSFEvaluationWorkbook.create((XSSFWorkbook) workbook);
  }

  Ptg[] ptgs = FormulaParser.parse(formula, (FormulaParsingWorkbook)evaluationWorkbook, 
   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((FormulaRenderingWorkbook)evaluationWorkbook, ptgs);
  return formula;
 }
Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • @Axel Richter, what is coldiff and rowdiff here? – Nirmal Prajapat Aug 19 '19 at 15:51
  • 1
    @Nirmal Prajapat: `coldiff = columnOfNewFormulaCell - columnOfOldFormulaCell` and `rowdiff = rowOfNewFormulaCell - rowOfOldFormulaCell`. – Axel Richter Aug 19 '19 at 16:10
  • Tried on Apache POI 4.1.2. Worked! – Xtraterrestrial Dec 19 '20 at 12:13
  • Doesn't work for me if I have ranges like this `INDIRECT({"I5:I5";"K5:M5";"P5:S5"})`, is it posible? –  Jan 12 '21 at 09:28
  • @Martinez: Your formula does not contain any cell references but only an array of strings. So there is not something what could be adapted to a new position. `Excel`'s GUI also wouldn't adapt something in that formula if that formula cell gets copied into a nwe position. – Axel Richter Jan 12 '21 at 09:47
  • So I should have a new `COUNTIF` for each range I have in this `INDIRECT` function right? I have something like this `COUNTIF(INDIRECT({"I6:I6";"K6:M6";"P6:S6"});">="&TODAY())` –  Jan 12 '21 at 09:49
  • 1
    @Martinez: No, this is a problem of `INDIRECT` since `INDIRECT` dont uses direct cell references but text strings which then are interpreted indirect. That's why the name. And, as said, `Excel`'s GUI also wouldn't adapt something in such `INDIRECT` formulas. – Axel Richter Jan 12 '21 at 10:01
0

Just if you want the answer for the NPOI, I have created the C# version from the @AxelRichter updated answer:

public static string CopyFormula(ISheet sheet, string formula, int coldiff, int rowdiff)
{

    var workbook = sheet.Workbook;
    IFormulaParsingWorkbook evaluationWorkbook = null;
    if (sheet is XSSFWorkbook)
    {
        evaluationWorkbook = XSSFEvaluationWorkbook.Create(workbook);
    }
    else if (sheet is HSSFWorkbook)
    {
        evaluationWorkbook = HSSFEvaluationWorkbook.Create(workbook);
    }
    else if (sheet is SXSSFWorkbook)
    {
        evaluationWorkbook = SXSSFEvaluationWorkbook.Create((SXSSFWorkbook)workbook);
    }


    var ptgs = FormulaParser.Parse(formula, evaluationWorkbook,FormulaType.Cell, sheet.Workbook.GetSheetIndex(sheet));

    for (int i = 0; i < ptgs.Length; i++)
    {
        if (ptgs[i] is RefPtgBase) { 
            RefPtgBase ref2 = (RefPtgBase)ptgs[i];
            if (ref2.IsColRelative)
                ref2.Column = ref2.Column + coldiff;
            if (ref2.IsRowRelative)
                ref2.Row = ref2.Row + rowdiff;
        }
        else if (ptgs[i] is AreaPtgBase) { 
            AreaPtgBase ref2 = (AreaPtgBase)ptgs[i];
            if (ref2.IsFirstColRelative)
                ref2.FirstColumn += coldiff;
            if (ref2.IsLastColRelative)
                ref2.LastColumn += coldiff;
            if (ref2.IsFirstRowRelative)
                ref2.FirstRow += rowdiff;
            if (ref2.IsLastRowRelative)
                ref2.LastRow += rowdiff;
        }
    }

    formula = FormulaRenderer.ToFormulaString((IFormulaRenderingWorkbook)evaluationWorkbook, ptgs);
    return formula;
}
mehdi.loa
  • 579
  • 1
  • 5
  • 23