POI / Excel : applying formulas in a "relative" way

Question

I'm using Apache's POI to manipulate Excel (.xls) files with Java.

I'm trying to create a new cell whom content is the result of a formula as if the user had copied/pasted the formula (what i call the "relative" way, as opposite to "absolute").

To make myself clearer, here is a simple example:

• Cell A1 contains "1",B1 contains "2", A2 contains "3", B2 contains "4".
• Cell A3 contains the following formula "=A1+B1".

If I copy the formula to the A4 cell under excel, it becomes "=A2+B2" : excel is adapting the content of the formula dynamically.

Unfortunately I cannot get the same result programatically. The only solution I found is to tokenize the formula and do the dirty work myself, but I really doubt that this is supposed to be done that way. I was not able to find what I'm looking for in the guides or in the API.

Is there an easier way to solve this problem ? If it's the case, can you please point me in the right direction ?

Best regards,

Nils

Answer 1

In my sense, user2622016 is right, except his solution manages only cell references, as opposed to area references (it won't work for =SUM(A1:B8) for instance).

Here's how I fixed this :

private void copyFormula(Sheet sheet, Cell org, Cell dest) {
    if (org == null || dest == null || sheet == null 
            || org.getCellType() != Cell.CELL_TYPE_FORMULA)
        return;
    if (org.isPartOfArrayFormulaGroup())
        return;
    String formula = org.getCellFormula();
    int shiftRows = dest.getRowIndex() - org.getRowIndex();
    int shiftCols = dest.getColumnIndex() - org.getColumnIndex();
    XSSFEvaluationWorkbook workbookWrapper = 
            XSSFEvaluationWorkbook.create((XSSFWorkbook) sheet.getWorkbook());
    Ptg[] ptgs = FormulaParser.parse(formula, workbookWrapper, FormulaType.CELL
            , sheet.getWorkbook().getSheetIndex(sheet));
    for (Ptg ptg : ptgs) {
        if (ptg instanceof RefPtgBase) // base class for cell references
        {
            RefPtgBase ref = (RefPtgBase) ptg;
            if (ref.isColRelative())
                ref.setColumn(ref.getColumn() + shiftCols);
            if (ref.isRowRelative())
                ref.setRow(ref.getRow() + shiftRows);
        } else if (ptg instanceof AreaPtg) // base class for range references
        {
            AreaPtg ref = (AreaPtg) ptg;
            if (ref.isFirstColRelative())
                ref.setFirstColumn(ref.getFirstColumn() + shiftCols);
            if (ref.isLastColRelative())
                ref.setLastColumn(ref.getLastColumn() + shiftCols);
            if (ref.isFirstRowRelative())
                ref.setFirstRow(ref.getFirstRow() + shiftRows);
            if (ref.isLastRowRelative())
                ref.setLastRow(ref.getLastRow() + shiftRows);
        }
    }
    formula = FormulaRenderer.toFormulaString(workbookWrapper, ptgs);
    dest.setCellFormula(formula);
}

I still don't know if I had it correct for all cell formulas, but it works for me, fast and reliable.