Apache POI 公式未评估 [英] Apache POI formulas not evaluating

问题描述

所以我在让 Apache POI 评估公式时遇到了一些问题.

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

仅供参考:grid 是一个二维 Object 数组，仅包含 double 和 String 类型的条目.

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

这是我要评估的公式:

"=G13 - H13"

"=G13 - H13"

"=STDEV.P(C1:L1)"

"=STDEV.P(C1:L1)"

"=I13/G13"

任何想法为什么当我在 Excel 中打开我的最终工作簿时没有计算公式?此外，当我单击未计算的字段并按 Enter 键时，Excel 将识别公式并对其进行评估.总的来说，这是不切实际的，但我相信它表明所使用的细胞是正确的类型.这可能与 String 类型的公式有关吗?

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?

好的，看来您应该明确告诉它您有一个公式单元格.这是我修改后的代码:

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:

原因:org.apache.poi.ss.formula.eval.NotImplementedFunctionException:STDEV.P

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

我想象这意味着 Excel 已经实现了标准偏差计算，但 POI 还没有赶上?

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

推荐答案

试试这个:

XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);

或者，如果您使用的是 xls

or, if you are using xls

HSSFFormulaEvaluator.evaluateAllFormulaCells(hssfWorkbook)

您可能想在保存之前调用它.

You probably want to call this just before saving.

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