I'm trying to use POI XSSF to evaluate some Excel formulas. The values do not have to be saved, and I may have to calculate many formulas, so I'm trying to do it all in the same cell.
我想用POI XSSF来计算一些Excel公式。这些值不需要保存，我可能需要计算很多公式，所以我试着在同一个单元格中做这些。
The problem is that the cell value seems to get stuck on the first formula entered even after I recalculate
问题是，即使在我重新计算之后，单元格值似乎仍然停留在输入的第一个公式上
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
XSSFCell formulaCell = row.createCell(6);
formulaCell.setCellFormula("Date(2011,10,6)");
CellValue cellValue = evaluator.evaluate(formulaCell);
System.out.println(cellValue.getNumberValue());
formulaCell.setCellFormula("Date(1911,3,4)");
cellValue = evaluator.evaluate(formulaCell);
System.out.println(cellValue.getNumberValue());
This outputs 40822.0 40822.0 (excel equivalent of 10/6/2011) both times instead of reevaluating to the new formula.
输出40822.0 40822.0 (excel等效于10/6/2011)，而不是重新计算新公式。
3 个解决方案
#1
5
If you use the formulaEvaluator more than once, you need this line in between uses, or else it uses the same result each time.
如果您多次使用公式求值器，您需要在两次使用之间使用这一行，否则每次都会使用相同的结果。
formulaEvaluator.clearAllCachedResultValues()
#2
2
The FormulaEvaluator caches cell calculated values to speed up processing. If you perform cell updates after creating the evaluator, then you need to tell it!
公式计算程序缓存计算值以加速处理。如果您在创建了评估器之后执行单元更新，那么您需要告诉它!
See the FormulaEvaluator documentation for more details. For you case, try:
更多细节请参见公式评价者文档。对于你情况,试一试:
formulaCell.setCellFormula("Date(1911,3,4)");
evaluator.notifySetFormula(formulaCell);
cellValue = evaluator.evaluate(formulaCell);
System.out.println(cellValue.getNumberValue());
#3
0
You can use following steps to get your work done. These are two solutions out of which you can make use of any one function. It evaluates the complete workbook so whatever formula you use would get evaluated. Hope this helps.
您可以使用以下步骤来完成您的工作。这是两个你可以利用任何一个函数的解。它对完整的工作簿进行评估，因此无论您使用什么公式都可以得到评估。希望这个有帮助。
1) evaluator.evaluateAll(); 2) XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
1)evaluator.evaluateAll();2)XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
#1
5
If you use the formulaEvaluator more than once, you need this line in between uses, or else it uses the same result each time.
如果您多次使用公式求值器，您需要在两次使用之间使用这一行，否则每次都会使用相同的结果。
formulaEvaluator.clearAllCachedResultValues()
#2
2
The FormulaEvaluator caches cell calculated values to speed up processing. If you perform cell updates after creating the evaluator, then you need to tell it!
公式计算程序缓存计算值以加速处理。如果您在创建了评估器之后执行单元更新，那么您需要告诉它!
See the FormulaEvaluator documentation for more details. For you case, try:
更多细节请参见公式评价者文档。对于你情况,试一试:
formulaCell.setCellFormula("Date(1911,3,4)");
evaluator.notifySetFormula(formulaCell);
cellValue = evaluator.evaluate(formulaCell);
System.out.println(cellValue.getNumberValue());
#3
0
You can use following steps to get your work done. These are two solutions out of which you can make use of any one function. It evaluates the complete workbook so whatever formula you use would get evaluated. Hope this helps.
您可以使用以下步骤来完成您的工作。这是两个你可以利用任何一个函数的解。它对完整的工作簿进行评估，因此无论您使用什么公式都可以得到评估。希望这个有帮助。
1) evaluator.evaluateAll(); 2) XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
1)evaluator.evaluateAll();2)XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);