公式细胞没有得到评估,直到公式是集中和确认在Excel中(XLSX)

Question

所以我使用Apache POI(最新稳定发布版本5.0.0中的POI -ooxml)并打开现有的用于编辑的Excel (XSLX)文件(它基本上是一个用于填充其他数据的模板文件)。我添加了多行新的数据，并再次导出Excel。一切正常，只要我只添加常规内容单元格。

现在，我有一个列，我想在其中添加一个公式单元格，我使用以下代码(为本例进行了简化，您可以放心，通常它会编译/运行并在最后生成一个填充的Excel文件)来完成此操作:

File excelFileToRead = new File(<some filename here>);
InputStream inp = new FileInputStream(excelFileToRead);
Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
Row dateRangeRow = sheet.getRow(0);
// fill first cell with some date
Cell cell = row.getCell(0);
if(cell == null) row.createCell(0)
Date someDate = new Date();
cell.setCellValue(someDate);
// add formula to second cell to display the week number
Cell formCell = row.getCell(1);
if(formCell == null) row.createCell(1);
cell.setCellFormula("WEEKNUM(A1)");
// evaluate all formula fields before saving
XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
//some routine to save as a file follows here, not exactly relevant here

一般,这没问题。第一个单元格被创建并填充为今天的日期，第二个单元格也被创建为公式单元格。

现在问题来了:当我打开Excel电子表格时，我可以看到数据，而在公式单元格中，我只能看到"#WERT"(使用德文Excel，我假设在英文版本中它会显示类似"#VALUE")。

当我简单地单击Excel中的公式编辑器并再次删除焦点时，它会正确地计算公式，单元格会显示正确的周数。

我以前的一些问题与现有公式Excel我读,他们没有当我添加数据表更新,但这可能与调用XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);固定由于某些原因，它不影响我自定义创建的公式单元格。

我还尝试在创建后单独评估新创建的公式单元格:

FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
formulaEvaluator.evaluateFormulaCell(formCell);

这里也没有变化。

你知道我的代码或方法有什么问题吗?

我使用Excel版本16.53 (Excel for Mac)顺便说一下，但我真的希望它与确切的Excel版本无关:-)

注意:我发现了一个旧线程(在POI 5.0.0发布之前的方式)，似乎讨论了同样的问题，但在较旧的POI版本中，也如上所述，我遵循了在保存之前调用evaluateAllFormulaCells(…)的一般做法，甚至在每个公式单元创建后调用evaluateFormulaCell(cell):

Answer 1

这是由于apache poi在计算WEEKNUM函数时的一个错误造成的。

如果省略[return_type]，则ist总是计算为#VALUE错误。但是，即使你设置了[return_type]，它的计算结果也不总是正确的。

你可以看到如果你:

...
FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
CellValue cellValue = formulaEvaluator.evaluate(formCell);
System.out.println(cellValue);
...

如果A1包含日期9/27/2021,B1包含公式=WEEKNUM(A1)，则apache poiFormulaEvaluator将其计算为#VALUE。如果B1包含公式=WEEKNUM(A1,1)，则apache poiFormulaEvaluator将其求值为39，但Excel将其求值为40。

要解决这个错误，可以在打开文件时强制Excel计算所有公式。这可以使用wb.setForceFormulaRecalculation(true);来完成。然后Excel计算公式，因此结果是正确的。

重现问题的完整示例:

import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.formula.BaseFormulaEvaluator;
import java.util.GregorianCalendar;
class CreateExcelFormulaWEEKNUM {
public static void main(String[] args) throws Exception {
try (
//Workbook wb  = WorkbookFactory.create(new FileInputStream("./ExcelIn.xls")); FileOutputStream fileout = new FileOutputStream("./ExcelOut.xls");
Workbook wb  = WorkbookFactory.create(new FileInputStream("./ExcelIn.xlsx")); FileOutputStream fileout = new FileOutputStream("./ExcelOut.xlsx");
) {
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(0); if (row == null) row = sheet.createRow(0);
Cell cell = row.getCell(0); if (cell == null) cell = row.createCell(0);
cell.setCellValue(new GregorianCalendar(2021, 8, 27));
CellReference cellReference = new CellReference(cell);

Cell formCell = row.getCell(1); if(formCell == null) formCell = row.createCell(1);
formCell.setCellFormula("WEEKNUM(" + cellReference.formatAsString() + ")"); // FormulaEvaluator evaluates to #VALUE because of [return_type] is not set
//formCell.setCellFormula("WEEKNUM(" + cellReference.formatAsString() + ", 1)"); // FormulaEvaluator evaluates to 39 which is wrong as Excel evaluates to 40

FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
CellValue cellValue = formulaEvaluator.evaluate(formCell);
System.out.println(cellValue);

BaseFormulaEvaluator.evaluateAllFormulaCells(wb);

wb.setForceFormulaRecalculation(true);
wb.write(fileout);
}
}
}