Apache POI evaluate formula

问题:

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>

回答1:

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

回答2:

Perhaps XSSFFormulaEvaluator.evaluateAllFormulaCells(XSSFWorkbook wb) ?

(or more specifically: evaluateFormulaCell(Cell cell).)

If it doesn't work: what version of POI are you using? Formulas in XSSF are supported from v3.5.

Also, try to use XSSFCreationHelper to instantiate your Formula Evaluator as suggested by the POI docs.

回答3:

if you using HSSF try :

HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);

回答4:

You can use this.

 public static void triggerFormula(HSSFWorkbook workbook){      

                FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
                HSSFSheet sheet = workbook.getSheetAt(0);
                int lastRowNo=sheet.getLastRowNum();        

                for(int rownum=0;rownum<=lastRowNo;rownum++){
                Row row;
                 if (sheet.getRow(rownum)!=null){
                         row= sheet.getRow(rownum);

                      int lastCellNo=row.getLastCellNum();

                          for(int cellnum=0;cellnum<lastCellNo;cellnum++){  
                                  Cell cell;
                                  if(row.getCell(cellnum)!=null){
                                     cell = row.getCell(cellnum);   
                                    if(Cell.CELL_TYPE_FORMULA==cell.getCellType()){
                                    evaluator.evaluateFormulaCell(cell);
                                }
                            }
                         }
                 }
                }


            }