当前位置： 文江博客话题详情

POI / Excel：在“相对”关系中应用公式方式

发布于 2024-08-10 01:29:06 字数 485 浏览 2 评论 0原文

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

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

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

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

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

不幸的是我无法以编程方式得到相同的结果。我发现的唯一解决方案是将公式标记化并自己完成肮脏的工作，但我真的怀疑是否应该这样做。我无法在指南或 API 中找到我想要的内容。

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

最好的问候，

尼尔斯

原文

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（6）

绅刃 2024-08-17 01:29:06

在我看来，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);
}

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

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.

回复 原文
一曲爱恨情仇 2024-08-17 01:29:06

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

甚至 POI 网站上的 HSSF 和 XSSD 示例，例如 TimesheetDemo 进行公式构建手动。例如110号线附近

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

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

Even the HSSF and XSSD examples on the POI site e.g. TimesheetDemo do the formula construction manually. e.g. around line 110

String ref = (char)('A' + j) + "3:" + (char)('A' + j) + "12";
cell.setCellFormula("SUM(" + ref + ")");
回复 原文
似最初 2024-08-17 01:29:06

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

FormulaParser，它将 String 解析为“解析事物”的数组：

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

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

FormulaParser, which parses String to array of "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 is now our formula in reverse polish notation. Now go through all elements and modify references one by one as you wish:

/* 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 );
回复 原文
淡墨 2024-08-17 01:29:06

另一种相对复制公式的方法，使用 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;
    }

}

Another way to copy formula relatively, tested with 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:

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

}
回复 原文
穿透光 2024-08-17 01:29:06

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

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!

回复 原文
病毒体 2024-08-17 01:29:06

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

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

回复 原文
~没有更多了~

关于作者

许久

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

烙印

文章 0 评论 0

singlesman

文章 0 评论 0

给自己一个微笑

文章 0 评论 0

独孤求败

文章 0 评论 0

晨钟暮鼓

文章 0 评论 0

我是自愿种绣球花的

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文