POI/Excel:在“相对"中应用公式道路 [英] POI / Excel : applying formulas in a "relative" way

问题描述

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

我正在尝试创建一个新单元格，其内容是公式的结果，就好像用户复制/粘贴了公式一样(我称之为相对"方式，与绝对"相反).

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

• 单元格 A1 包含1"，B1 包含2"，A2 包含3"，B2 包含4".
• 单元格 A3 包含以下公式=A1+B1".

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

不幸的是，我无法以编程方式获得相同的结果.我找到的唯一解决方案是标记公式并自己做脏活，但我真的怀疑这是否应该以这种方式完成.我无法在指南或 API 中找到我要查找的内容.

有没有更简单的方法来解决这个问题?如果是这样的话，你能指出我正确的方向吗?

最好的问候，

尼尔斯

解决方案

在我看来，user2622016 是对的，除了他的解决方案只管理 cell 引用，而不是区域 引用(例如，它不适用于 =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)返回;如果 (org.isPartOfArrayFormulaGroup())返回;字符串公式 = 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)//单元格引用的基类{RefPtgBase ref = (RefPtgBase) ptg;如果 (ref.isColRelative())ref.setColumn(ref.getColumn() + shiftCols);如果 (ref.isRowRelative())ref.setRow(ref.getRow() + shiftRows);} else if (ptg instanceof AreaPtg)//范围引用的基类{AreaPtg ref = (AreaPtg) ptg;如果 (ref.isFirstColRelative())ref.setFirstColumn(ref.getFirstColumn() + shiftCols);如果 (ref.isLastColRelative())ref.setLastColumn(ref.getLastColumn() + shiftCols);如果 (ref.isFirstRowRelative())ref.setFirstRow(ref.getFirstRow() + shiftRows);如果 (ref.isLastRowRelative())ref.setLastRow(ref.getLastRow() + shiftRows);}}公式 = FormulaRenderer.toFormulaString(workbookWrapper, ptgs);dest.setCellFormula(公式);}

我仍然不知道它是否适用于所有单元格公式，但它对我有用，快速且可靠.

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

解决方案

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.

这篇关于POI/Excel:在“相对"中应用公式道路的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！