Apache POI formulas not evaluating

Question

So I'm having some issues getting Apache POI to evaluate formulas.

Here's the code I call to evaluate formulas before writing:

complete.getCreationHelper().createFormulaEvaluator().evaluateAll();
complete.write(fileOut);

Here's the code I call to write to the cells being used (proving they're numbers):

try{
   cell.setCellValue((Double)grid[i][j]);
}
catch(Exception e){
   cell.setCellValue((String)grid[i][j]);
}

FYI: grid is a 2D Object array containing only entries of the type double and String.

Here's the formulas I'm trying to evaluate:

"=G13 - H13"

"=STDEV.P(C1:L1)"

"=I13/G13"

Any ideas why when I open up my final workbook in Excel the formulas arn't evaluated? Also, when I click on an unevaluated field and hit enter Excel will recognize the formula and evaluate it. In bulk this isn't practical, but I believe it demonstrates that the cells being used are the correct type. Could this be related to the formulas being of the String type?

EDIT:

OK, so looks like you're supposed to explicitly tell it you have a formula cell. Here's my modified code to do that:

try{
   cell.setCellValue((Double)grid[i][j]);
}
catch(Exception e){
   String val = (String) grid[i][j];
   if (val != null && val.startsWith("=")){
      val = val.replaceAll("=", "");
      cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
      cell.setCellFormula(val);
   }
   else{
      cell.setCellValue(val);
   }
}

Unfortunately you need to remove the equals sign (which is dumb) to pass formulas and then force it to reevaluate before saving (which is dumb). After trying to get it to reevaluate formulas it complained, however, saying:

Caused by: org.apache.poi.ss.formula.eval.NotImplementedFunctionException: STDEV.P

I'm imagining that this means Excel has implemented standard deviation calculations but POI hasn't caught up yet?

Answer 1

Try this:

XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);

or, if you are using xls

HSSFFormulaEvaluator.evaluateAllFormulaCells(hssfWorkbook)

You probably want to call this just before saving.