Algorithm for adjusting formula excel from original cell to destination cell

Question

I am trying to parse formula in excel with 3 params: Formula, original cell address, destination cell address.

Example, with some rules:

Formula	Original Address	Destination Address	Result Expected	Note
=(A2+B2)	C2	C3	=(A3+B3)
=(A2+B2)	C2	D2	=(B2+C2)	Increase column by 1 (C->D)
=(A2+$B$2)	C2	D10	=(B10+$B$2)	The $ expression
=(SheetA2!A2+B2)	C2	C3	=(SheetA2!A3+B3)	The Sheet name(SheetA2) is invariant
=IF(A2=A3,A4,A5)	A6	C6	=IF(C2=C3,C4,C5)	Replace All variant

I know some library in C#, python ... did it, but now I want to do it in java. I need algorithm to parse formula to token and replace it, or a library do it fast.

Answer 1

There is a Java library Apache POI, which is able to do this.

This library provides formula evaluation and shifting cells. So it needs parsing formulas and adjusting formulas to new destination cells. To do this, it provides FormulaParser and FormulaRenderer.

Of course those classes are programmed to do their work in an existing workbook. But if only the adjustment of formula strings is needed, then an empty temporary dummy workbook can be used. I use XSSFWorkbook in my example as this is the representation of current *.xlsx, which provides greater row count and columns count than the older *.xls.

Complete example testing your given examples and one more:

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

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

public class ExcelGetAdjustedFormula {

 private static String getAdjustedFormula(String formula, String originalAddress, String destinationAddress) {
     
  CellAddress originalCellAddress = new CellAddress(originalAddress);
  CellAddress destinationCellAddress = new CellAddress(destinationAddress);
  int coldiff = destinationCellAddress.getColumn() - originalCellAddress.getColumn();
  int rowdiff = destinationCellAddress.getRow() - originalCellAddress.getRow();

  XSSFEvaluationWorkbook workbookWrapper = 
   XSSFEvaluationWorkbook.create(new XSSFWorkbook());
  Ptg[] ptgs = FormulaParser.parse(formula, workbookWrapper, FormulaType.CELL, 0);

  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) {
     
  String[][] examples = new String[][]{
   new String[]{"(A2+B2)", "C2", "C3"},
   new String[]{"(A2+B2)", "C2", "D2"},
   new String[]{"(A2+$B$2)", "C2", "D10"},
   new String[]{"(SheetA2!A2+B2)", "C2", "C3"},
   new String[]{"IF(A2=A3,A4,A5)", "A6", "C6"},
   new String[]{"VLOOKUP(A2,'Sheet Name'!$A$1:$G$1000,4,FALSE)", "F2", "F10"}
  };
     
  for (String[] example : examples) {
   String formula = example[0];
   String originalAddress = example[1];
   String destinationAddress = example[2];
  
   String newformula = getAdjustedFormula(formula, originalAddress, destinationAddress);
   System.out.println(newformula);
  }
  
 }
}