如何使用Apache POI在单元格中设置公式？

Question

我目前正在使用Java的Apache POI来设置单元格中的公式。

但是在我运行程序并打开已创建和处理过的Excel文件后，包含公式的单元格会将公式作为字符串显示，而不是公式应该返回的值。

- vamsi

---

你是否正在使用 setCellFormula (http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFCell.html#setCellFormula%28java.lang.String%29)？ - Matthew Flaschen

是的。基本上我的公式是用户自定义的，不被Excel默认支持。它大致是这样的："=IQLink|yhoo!change"。它所做的就是通过股票程序获取股票的当前价格。但重点是这个公式不被Excel默认支持，这与此有关吗？ - vamsi

如果您告诉Excel手动重新计算所有公式（http://office.microsoft.com/en-us/excel/HP100541491033.aspx#Recalculate%20a%20worksheet%20or%20workbook%20manually%20by%20using%20keyboard%20shortcuts），会发生什么？ - Matthew Flaschen

当我进入Excel并手动重新计算时，它们按预期工作...我不知道为什么POI不能做同样的事情。 - vamsi

我不相信poi可以进行任何公式评估。 - mP.

Answer 1

HSSFCell 对象有 .setCellType 和 .setCellFormula 方法，您需要像这样调用：

// "cell" object previously created or looked up
String strFormula= "SUM(A1:A10)";
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula(strFormula);

- user257111

---

不行，那个没起作用。基本上我正在使用一种自定义公式，这种公式不是 Excel 默认支持的。使用用户定义的公式会有任何问题吗？ - vamsi

我之前没有尝试过，所以我认为不行。但我看不出来有什么不行的理由；这应该只是用 =strFormula 填充单元格，但也许它还做了其他事情。 - user257111

这篇文章中使用的方法已经过时。@Jinu P C提供了一个适用于更近期版本的解决方案。 - Tim

如何根据行号和列号重写公式？我是通过编程生成电子表格，所以不确定A、B、C术语的含义。 - Shubham AgaRwal

此答案已经过时。 - ATNPGO

Answer 2

单元格常量已被弃用，并将在4.0版本中移除。请使用下列方法代替：

CellType.FORMULA

String formula= "SUM(B4:B20)"; cell.setCellType(CellType.FORMULA); cell.setCellFormula(formula);

更新

setCellType() :根据@fenix的评论，该方法已被弃用并将在POI 5.0中被移除。使用明确的setCellFormula(String)、setCellValue(...)或setBlank()方法来获取所需结果。

String formula= "SUM(B4:B20)";
cell.setCellFormula(formula);

- Jinu P C

---

有没有办法我可以添加自定义公式到这个表格中？例如：=DATEDIF(A2,NOW(),"y")。使用上述方法将会对任何自定义公式抛出异常。 - Tim

Cell.setCellType(CellType)也已经被弃用（将在Apache POI 5.0中删除）。使用显式的Cell.setCellFormula(String)应该就足够了。 - Fenix

Answer 3

以下代码对我很有效，希望这能对其他人有所帮助。

cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUM(C70:C76)");

- user1901203

Answer 4

Apache POI不支持用户定义的函数。

从文档中可以看到：

请注意，不支持用户定义的函数，也不太可能在短时间内实现...至少，在Java中有VB实现之前不会实现！

- filsa

Answer 5

您可以使用此功能来计算工作簿中的公式：

        // Evaluate all formulas in the sheet, to update their value
    FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
    formulaEvaluator.evaluateAll();

- Diogo Vilela

Answer 6

我遇到了类似的问题，"SUM(B4:B20)" 直接不能起作用，因为表格是动态生成的。问题出在单元格引用上。

参考 https://dev59.com/ZnE95IYBdhLWcg3wXsyR#2339262 和 https://dev59.com/4pDea4cB1Zd3GeqPWAeg#33098060，我能够生成实际公式，例如：

 val totalProductQtyPerUserCell = userDetailsRow.createCell(products.size + 1)
 totalProductQtyPerUserCell.cellType = HSSFCell.CELL_TYPE_FORMULA
 totalProductQtyPerUserCell.cellFormula = "SUM(${CellReference.convertNumToColString(1)}${totalProductQtyPerUserCell.row.rowNum + 1}:${CellReference.convertNumToColString(products.size)}${totalProductQtyPerUserCell.row.rowNum + 1})"

希望能够帮到您。

- Shubham AgaRwal

Answer 7

这里是一种建议的方法：

Workbook workbook1 = new SXSSFWorkbook(-1);
Sheet newSheet = workbook1.createSheet("Valuations");
Row newRow = newSheet.createRow(i-1);
newRow.createCell(L++).setCellFormula("AVERAGE(" + "E" + i + ":" + "G" + i + ")");

E和G是类似于A1、B1、C1……E1……G1的列。
而i代表上述列中的一个数字。

L++仅仅是一个递增的计数器。

- prashant.kr.mod