Java 如何使用 Apache POI 在单元格中设置公式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2339238/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set formulas in cells using Apache POI?
提问by vamsi
I am currently using Apache POIfor Java to set formulas in cells.
我目前正在使用Apache POIfor Java 在单元格中设置公式。
But after I run the program and open the Excel file that I created and processed, the cells with the formula include the formula as a string, rather than the value the formula should have returned.
但是在我运行程序并打开我创建和处理的 Excel 文件后，带有公式的单元格将公式作为字符串包含，而不是公式应该返回的值。
回答by vamsi
The HSSFCellobject has methods .setCellTypeand .setCellFormulawhich you need to call like this:
该HSSFCell对象有方法.setCellType和.setCellFormula您需要这样的电话：
// "cell" object previously created or looked up
String strFormula= "SUM(A1:A10)";
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula(strFormula);
回答by filsa
Apache POI does not support user-defined functions.
Apache POI 不支持用户自定义函数。
From the documentation:
从文档：
Note that user-defined functions are not supported, and is not likely to done any time soon... at least, not till there is a VB implementation in Java!
请注意，不支持用户定义的函数，并且不太可能在短期内完成……至少，在 Java 中有 VB 实现之前不会！
回答by user1901203
The below code worked fine for me, hope this could be useful to someone.
下面的代码对我来说很好用，希望这对某人有用。
cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUM(C70:C76)");
回答by Jinu P C
Cell Constants are deprecated and will be removed from version 4.0 instead of Cell Use
不推荐使用细胞常数，并将从 4.0 版中删除，而不是从细胞使用中删除
CellType.FORMULA
细胞类型公式
String formula= "SUM(B4:B20)";
cell.setCellType(CellType.FORMULA);
cell.setCellFormula(formula);
回答by Killer
I was having the similar problem and "SUM(B4:B20)"does not work for me directly as the sheet was generated dynamically. The problem was with the cell reference.
我遇到了类似的问题， "SUM(B4:B20)"并且不能直接为我工作，因为工作表是动态生成的。问题在于单元格引用。
On the basis of https://stackoverflow.com/a/2339262/2437655and https://stackoverflow.com/a/33098060/2437655I was able to generate the actual formula. e.g.
在https://stackoverflow.com/a/2339262/2437655和https://stackoverflow.com/a/33098060/2437655的基础上，我能够生成实际的公式。例如
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})"
Hope that help.
希望有所帮助。
回答by Diogo Vilela
You could use this to evaluate the formulas in the workbook:
您可以使用它来评估工作簿中的公式：
// Evaluate all formulas in the sheet, to update their value
FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
formulaEvaluator.evaluateAll();
回答by pkm
Here is one suggested way:
这是一种建议的方法：
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 + ")");
Where E and G are the columns like A1,B1,C1....E1...G1
and i represent a number in the above Column.
其中 E 和 G 是 A1,B1,C1....E1...G1 之类的列
，我代表上述列中的一个数字。
L++ is simply an incremental counter.
L++ 只是一个增量计数器。
