java POI / Excel：以“相对”方式应用公式

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

提问by Nils

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

我正在使用 Apache 的 POI 用 Ja​​va 操作 Excel (.xls) 文件。

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".

• 单元格A1包含“1”，B1包含“2”，A2包含“3”，B2包含“4”。
• 单元格 A3 包含以下公式“=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.

如果我把公式复制到excel下的A4单元格，就变成"=A2+B2"：excel正在动态调整公式的内容。

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.

不幸的是，我无法以编程方式获得相同的结果。我找到的唯一解决方案是标记公式并自己做脏活，但我真的怀疑这是否应该以这种方式完成。我无法在指南或 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

尼尔斯

回答by julien.giband

In my sense, user2622016is right, except his solution manages only cellreferences, as opposed to areareferences (it won't work for =SUM(A1:B8)for instance).

在我看来，user2622016是对的，除了他的解决方案只管理单元格引用，而不是区域引用（例如，它不起作用=SUM(A1:B8)）。

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.

我仍然不知道我是否对所有单元格公式都正确，但它对我有用，快速可靠。

回答by jitter

I too think that there isn't an easy way to do this.

我也认为没有简单的方法可以做到这一点。

Even the HSSF and XSSD exampleson the POI site e.g. TimesheetDemodo the formula construction manually. e.g. around line 110

甚至POI 站点上的HSSF 和 XSSD 示例（例如TimesheetDemo）也是手动构建公式的。例如在第 110 行附近

String ref = (char)('A' + j) + "3:" + (char)('A' + j) + "12";
cell.setCellFormula("SUM(" + ref + ")");

回答by user2622016

I looked inside FormulaEvaluatorclass and found some POI internal classes that can do the work for us.

我查看了FormulaEvaluator类，发现了一些可以为我们完成工作的 POI 内部类。

FormulaParser, which parses String to array of "parse things":

FormulaParser，它将字符串解析为“解析事物”数组：

String formula = cell.getCellFormula();
XSSFEvaluationWorkbook workbookWrapper = 
             XSSFEvaluationWorkbook.create((XSSFWorkbook) workbook);  
/* parse formula */
Ptg[] ptgs = FormulaParser.parse(formula, workbookWrapper, 
             FormulaType.CELL, 0 /*sheet index*/ );

ptgs is now our formula in reverse polish notation. Now go through all elements and modify references one by one as you wish:

ptgs 现在是我们的反向波兰符号公式。现在遍历所有元素并根据需要一一修改引用：

/* re-calculate cell references */
for( Ptg ptg  : ptgs )
    if( ptg instanceof RefPtgBase )    //base class for cell reference "things"
    {
        RefPtgBase ref = (RefPtgBase)ptg;
        if( ref.isColRelative() )
            ref.setColumn( ref.getColumn() + 0 );
        if( ref.isRowRelative() )
            ref.setRow( ref.getRow() + 1 );
    }

And you're ready to render "parse things" back to String:

您已准备好将“解析事物”呈现回字符串：

formula = FormulaRenderer.toFormulaString(workbookWrapper, ptgs);
cell.setCellFormula( formula );

回答by jjcosare

Another way to copy formula relatively, tested with poi 3.12

另一种相对复制公式的方法，用poi 3.12测试

public static void copyCellFormula(Workbook workbook, int sheetIndex, int rowIndex, int sourceColumnIndex, int destinationColumnIndex){
    XSSFEvaluationWorkbook formulaParsingWorkbook = XSSFEvaluationWorkbook.create((XSSFWorkbook) workbook);
    SharedFormula sharedFormula = new SharedFormula(SpreadsheetVersion.EXCEL2007);
    Sheet sheet = workbook.getSheetAt(sheetIndex);
    Row lookupRow = sheet.getRow(rowIndex);
    Cell sourceCell = lookupRow.getCell(sourceColumnIndex);
    Ptg[] sharedFormulaPtg = FormulaParser.parse(sourceCell.getCellFormula(), formulaParsingWorkbook, FormulaType.CELL, sheetIndex);
    Ptg[] convertedFormulaPtg = sharedFormula.convertSharedFormulas(sharedFormulaPtg, 0, 1);
    Cell destinationCell = lookupRow.createCell(destinationColumnIndex);
    destinationCell.setCellFormula(FormulaRenderer.toFormulaString(formulaParsingWorkbook, convertedFormulaPtg));
}

Update shared formula as needed:

根据需要更新共享公式：

sharedFormula.convertSharedFormulas(sharedFormulaPtg, rowIndexOffset, columnIndexOffset);

As of poi 3.12, SharedFormula doesn't support cell reference/formula from other sheets (='Sheet1'!A1). Here's an update to SharedFormula:

从 poi 3.12 开始，SharedFormula 不支持来自其他工作表 (='Sheet1'!A1) 的单元格引用/公式。以下是 SharedFormula 的更新：

public class SharedFormula {

    private final int _columnWrappingMask;
    private final int _rowWrappingMask;

    public SharedFormula(SpreadsheetVersion ssVersion) {
        this._columnWrappingMask = ssVersion.getLastColumnIndex();
        this._rowWrappingMask = ssVersion.getLastRowIndex();
    }

    public Ptg[] convertSharedFormulas(Ptg[] ptgs, int formulaRow, int formulaColumn) {
        Ptg[] newPtgStack = new Ptg[ptgs.length];

        RefPtgBase areaNPtg = null;
        AreaPtgBase var9 = null;
        Object ptg = null;
        byte originalOperandClass = 0;
        for(int k = 0; k < ptgs.length; ++k) {
            ptg = ptgs[k];
            originalOperandClass = -1;
            if(!((Ptg)ptg).isBaseToken()) {
                originalOperandClass = ((Ptg)ptg).getPtgClass();
            }

            if(ptg instanceof RefPtgBase) {
                if(ptg instanceof Ref3DPxg) {
                    areaNPtg = (Ref3DPxg)ptg;
                    this.fixupRefRelativeRowAndColumn(areaNPtg, formulaRow, formulaColumn);
                    ptg = areaNPtg;
                }else if(ptg instanceof Ref3DPtg) {
                    areaNPtg = (Ref3DPtg)ptg;
                    this.fixupRefRelativeRowAndColumn(areaNPtg, formulaRow, formulaColumn);
                    ptg = areaNPtg;
                }else {
                    areaNPtg = (RefPtgBase)ptg;
                    ptg = new RefPtg(this.fixupRelativeRow(formulaRow, areaNPtg.getRow(), areaNPtg.isRowRelative()), this.fixupRelativeColumn(formulaColumn, areaNPtg.getColumn(), areaNPtg.isColRelative()), areaNPtg.isRowRelative(), areaNPtg.isColRelative());
                }
                ((Ptg)ptg).setClass(originalOperandClass);
            }else if(ptg instanceof AreaPtgBase) {
                if(ptg instanceof  Area3DPxg) {
                    var9 = (Area3DPxg)ptg;
                    this.fixupAreaRelativeRowAndColumn(var9, formulaRow, formulaColumn);
                    ptg = var9;
                }else if(ptg instanceof  Area3DPxg) {
                    var9 = (Area3DPtg)ptg;
                    this.fixupAreaRelativeRowAndColumn(var9, formulaRow, formulaColumn);
                    ptg = var9;
                }else {
                    var9 = (AreaPtgBase)ptg;
                    ptg = new AreaPtg(this.fixupRelativeRow(formulaRow, var9.getFirstRow(), var9.isFirstRowRelative()), this.fixupRelativeRow(formulaRow, var9.getLastRow(), var9.isLastRowRelative()), this.fixupRelativeColumn(formulaColumn, var9.getFirstColumn(), var9.isFirstColRelative()), this.fixupRelativeColumn(formulaColumn, var9.getLastColumn(), var9.isLastColRelative()), var9.isFirstRowRelative(), var9.isLastRowRelative(), var9.isFirstColRelative(), var9.isLastColRelative());
                }
                ((Ptg)ptg).setClass(originalOperandClass);
            }else if(ptg instanceof OperandPtg) {
                ptg = ((OperandPtg)ptg).copy();
            }

            newPtgStack[k] = (Ptg)ptg;
        }

        return newPtgStack;
    }

    protected void fixupRefRelativeRowAndColumn(RefPtgBase areaNPtg, int formulaRow, int formulaColumn){
        areaNPtg.setRow(this.fixupRelativeRow(formulaRow, areaNPtg.getRow(), areaNPtg.isRowRelative()));
        areaNPtg.setColumn(this.fixupRelativeColumn(formulaColumn, areaNPtg.getColumn(), areaNPtg.isColRelative()));
        areaNPtg.setRowRelative(areaNPtg.isRowRelative());
        areaNPtg.setColRelative(areaNPtg.isColRelative());
    }

    protected void fixupAreaRelativeRowAndColumn(AreaPtgBase var9, int formulaRow, int formulaColumn){
        var9.setFirstRow(this.fixupRelativeRow(formulaRow, var9.getFirstRow(), var9.isFirstRowRelative()));
        var9.setLastRow(this.fixupRelativeRow(formulaRow, var9.getLastRow(), var9.isLastRowRelative()));
        var9.setFirstColumn(this.fixupRelativeColumn(formulaColumn, var9.getFirstColumn(), var9.isFirstColRelative()));
        var9.setLastColumn(this.fixupRelativeColumn(formulaColumn, var9.getLastColumn(), var9.isLastColRelative()));
        var9.setFirstRowRelative(var9.isFirstRowRelative());
        var9.setLastRowRelative(var9.isLastRowRelative());
        var9.setFirstColRelative(var9.isFirstColRelative());
        var9.setLastColRelative(var9.isLastColRelative());
    }

    protected int fixupRelativeColumn(int currentcolumn, int column, boolean relative) {
        return relative?column + currentcolumn & this._columnWrappingMask:column;
    }

    protected int fixupRelativeRow(int currentrow, int row, boolean relative) {
        return relative?row + currentrow & this._rowWrappingMask:row;
    }

}

回答by Brian Agnew

I don't think there is. POI would have to parse the formula (taking into account A1 vs. $A$1 vs. $A1 etc.) and I don't believe it has that capacity. When I've done this in the past I've always had to manage this myself. Sorry - not the answer you hoped for!

我不认为有。POI 必须解析公式（考虑到 A1 与 $A$1 与 $A1 等），我不相信它具有这种能力。当我过去这样做时，我总是不得不自己管理。对不起 - 不是您希望的答案！

回答by liya

you can try some third party excel librarys,most of them can handle the relative/absolute range formulas.

您可以尝试一些第三方excel库，其中大多数可以处理相对/绝对范围公式。