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

Question

我正在使用Apache的POI来用Java操作Excel(.xls)文件。

我想要创建一个新单元格，其内容是公式的结果，就好像用户已复制/粘贴了公式（所谓的“相对”方式，与“绝对”相反）。

为了让自己更清楚，这里有一个简单的例子:

单元格A1包含"1"，B1包含"2"，A2包含"3"，B2包含"4"。 单元格A3包含以下公式“= A1 + B1”。 如果我在Excel中将公式复制到A4单元格中，它会变成“= A2 + B2”，Excel会动态地调整公式的内容。

不幸的是，我无法通过程序获得同样的结果。我找到的唯一解决方案是对公式进行标记化处理，自己完成具体工作，但我确实怀疑这是否是应该这样做。我在指南或API中没有找到我要找的内容。

是否有更简单的方法解决这个问题？如果可以的话，请指点我正确的方向。

最好的问候，

尼尔斯

- Nils

Answer 1

依我之见，user2622016是正确的，但他的解决方案仅适用于单元格引用，而不是区域引用（例如对于 =SUM(A1:B8) 就行不通）。

以下是我的修复方法：

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);
}

我仍然不确定是否所有单元格公式都是正确的，但对我来说它很快速可靠。

- julien.giband

---

两年后我仍在试探，但您是否知道是否有NPOI等效的setColumn（），setRow（），setFirstColumn（）和setLastColumn（）？或者它是否在最近版本的POI / NPOI中被停止使用了？ - justiceorjustus

@justiceorjustus：我知道我晚了6年，但在NPOI中，Row、Column、FirstRow和FirstColumn都有公共的getter和setter。因此，你可以编写ref.Row += shiftRows等代码... - duongntbk

Answer 2

我也认为没有简单的方法来实现这个。

即使在POI网站上的HSSF和XSSD示例，例如TimesheetDemo，也需要手动构建公式，例如在第110行附近。

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

- jitter

Answer 3

我查看了FormulaEvaluator类，并找到了一些POI内部类，可以为我们完成这项工作。

其中有一个名为FormulaParser的类，它将字符串解析为"parse things"数组。

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

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 );
    }

而且你已经准备好将“解析事物”返回为字符串：

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

- user2622016

---

这个对我很有帮助，而且似乎是最简单的方法。两点需要补充： 1）需要注意的是，代码使用了标记为POI内部的类（例如XSSFEvaluationWorkbook），因此该代码可能会在以后的版本中出现问题。 2）还应调整描述区域（例如A1:C3）的“AreaPtgBase”内容。该代码基于本答案中所示的RefPtgBase处理方式，非常直观。 - sven

我尝试了上面的代码，并在Apache POI 4.1.2上工作。我还没有处理使用单元格范围的MAX，MIN情况。谢谢@user2622016 - Xtraterrestrial

Answer 4

另一个相对复制公式的方法，已经在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));
}

根据需要更新共享公式：

sharedFormula.convertSharedFormulas(sharedFormulaPtg, rowIndexOffset, columnIndexOffset);

自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;
    }

}

- jjcosare

Answer 5

我不认为这是可能的。POI必须解析公式（考虑到A1 vs. $A$1 vs. $A1等），我不认为它具备这种能力。在过去，我总是需要自己处理这个问题。抱歉，这不是你想要的答案！

- Brian Agnew

Answer 6

你可以尝试一些第三方的Excel库，它们大多数都可以处理相对/绝对范围公式。

- liya

---

它并没有提供一个解决问题的具体库。 - sven