Apache POI 评估公式 [英] Apache POI evaluate formula

问题描述

我的工作表的单元格中有一些公式，我想在插入一些值后评估它们.例如:

我的公式是=SUM(B1,B2)

值前插入B1值为1，B2值为3，公式结果为4

现在插入值后 B1 的值为 5，B2 的值为 2 但公式仍然产生 4，我如何评估/trigger this 进行计算?

自然地，在我点击公式单元格上的返回按钮后，新值 7 被计算出来，有没有办法在没有手动交互的情况下触发它?

我使用的是 excel 2007 所以 XSSFWorkbook

编辑/更新:

在他发布之前我已经使用了 Gabors 解决方案，但我将其用作参考，发生的情况如下:

 线程main"中的异常 java.lang.NoSuchMethodError: org.apache.poi.ss.formula.WorkbookEvaluator.<init>(Lorg/apache/poi/ss/formula/EvaluationWorkbook;Lorg/apache/poi/ss/formula/IStabilityClassifier;Lorg/apache/poi/hssf/record/formula/udf/UDFFinder;)V在 org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.(XSSFFormulaEvaluator.java:64)在 org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.(XSSFFormulaEvaluator.java:51)…………………………………………

这是相关代码的一部分:

public XSSFFormulaEvaluator getEvaluator(){如果(评估器 == 空){评估器 = 新 XSSFFormulaEvaluator(wb);}返回评估员；}

实际调用评估器:

//其中index为int，mycell为introw = (XSSFRow) sheet.getRow(index);单元格 = row.createCell(mycell);getEvaluator().evaluateFormulaCell(cell);

我正在寻找使用过这个并且成功的人，而不是那些在没有真正尝试的情况下使用谷歌解决方案的人，至少我已经在谷歌上搜索了很多东西.

根据 Gagravar 的建议，我的类路径上确实有 2 个兴趣点:

 <依赖><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.8-beta1</version></依赖><依赖><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.7</version></依赖>

我虽然需要 3.7 版本的 XSSF 工作簿等.

解决方案:

<依赖><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.8-beta2</version></依赖><依赖><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.8-beta2</version></依赖>

解决方案

将评论提升为答案...

您首先需要确保主 jar 和 OOXML 部分使用相同版本的 POI.您的 maven 代码段显示一个为 3.7，另一个为 3.8 beta 1.你需要确保它们都是一样的.(您甚至可能想使用刚刚发布的 3.8-beta2).

然后，使用:

FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();evaluateFormulaCell(cell);

或:

XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

查看http://poi.apache.org/spreadsheet/eval.html了解更多详情>

I have some formulas in cells of my sheet, and I want to evaluate them after I insert some values. Ex :

My formula is =SUM(B1,B2)

Before values insertion B1 value was 1, and B2 value was 3, and the formula result is 4

After insertion of values now B1 has value 5, and B2 has value 2 but the formula still produces 4, how can I evaluate/trigger this to be calculated?

Naturally after I hit the return button on the formula cell the new value 7 is calculated, is there a way to trigger this without manual interaction?

I'm using excel 2007 so XSSFWorkbook

EDIT/UPDATE :

I've used Gabors solution before he posted it but I'm using it as a reference, here is what happens :

 Exception in thread "main" java.lang.NoSuchMethodError: org.apache.poi.ss.formula.WorkbookEvaluator.<init>(Lorg/apache/poi/ss/formula/EvaluationWorkbook;Lorg/apache/poi/ss/formula/IStabilityClassifier;Lorg/apache/poi/hssf/record/formula/udf/UDFFinder;)V
        at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.<init>(XSSFFormulaEvaluator.java:64)
        at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.<init>(XSSFFormulaEvaluator.java:51)
...............
...............

Here is a part of the relevant code :

public XSSFFormulaEvaluator getEvaluator(){
        if(evaluator == null){
            evaluator = new XSSFFormulaEvaluator(wb);
        }
        return evaluator;
    }

actually invoking evaluator :

//where index is int, and mycell is int
row = (XSSFRow) sheet.getRow(index);
cell = row.createCell(mycell);
getEvaluator().evaluateFormulaCell(cell);

I'm looking for someone used this and was successful, not those who google solution without really trying it, I've been googling a lot to say at least.

Per Gagravar suggestion I do have 2 POIs on my classpath :

        <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.8-beta1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.7</version>
    </dependency>

I though I need 3.7 version for XSSF workbooks etc.

SOLUTION :

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.8-beta2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.8-beta2</version>
        </dependency>

解决方案

To promote a comment to an answer...

You firstly need to ensure you're using the same version of POI for the main jar, and the OOXML part. Your maven snippet was showing 3.7 for one, and 3.8 beta 1 for the other. You need to make sure they're both the same. (You might even want to use 3.8-beta2 which is just out).

Then, use either:

FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateFormulaCell(cell);

or:

XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

See http://poi.apache.org/spreadsheet/eval.html for more details

这篇关于Apache POI 评估公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！