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?

I can't see any code where you set the formula cells - can you post that in case that's your mistake? — Gagravarr
Set formula cells? I feel like this might be what I'm missing. Do I need to explicitly tell it "if you have a string value and start with an equals sign you are a formula cell"? Because that's pretty dumb if that's the case but I can see the distinction being necessary. — Justin Flowers
Formula cells are stored in the file in rather a different way to string cells, so you need to be explicit — Gagravarr
Hey Gagravarr, I'm seeing your posts everywhere about POI. Could you give me some more advice in messages? Also, how should I message you? I have more questions and tripwires I keep hitting but I'm not sure I should keep going here! SO doesn't have private messages somehow in the same way America doesn't have total healthcare coverage. Anyhow, now I've added custom user defined functions for STDEV and I stopped getting exceptions when I run the program. However all the UDF cells come up "#NAME?". Any ideas what commonly leads to that? — Justin Flowers
Your best bet is to post on the Apache POI user mailing list - plenty of people there, not just me! — Gagravarr

Robert Robert · Accepted Answer · 2017-02-23T18:01:41

Try this:

XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);

or, if you are using xls

HSSFFormulaEvaluator.evaluateAllFormulaCells(hssfWorkbook)

You probably want to call this just before saving.

Apache POI formulas not evaluating

1 Answers