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

FYI:grid是2D 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"

"=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屋！