在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);
String columnName = CellReference.convertNumToColString(cell);
cell.setCellFormula("WEEKNUM(" + columnName + (cell.getRowIndex()+1) + ")");

// evaluate all formula fields before saving
XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

//some routine to save as a file follows here, not exactly relevant here

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

以前我读过的Excel中的已有公式有一些问题，当我将数据添加到工作表中时，它们没有更新，但这可以通过调用xssfformulaevaluator.evaluateAllFormulacells(wb)来解决；由于某种原因，它不会影响我自定义创建的公式单元格。

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

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

这在这里也没有产生任何变化。

注意：我发现了一个旧的线程（在POI5.0.0发布之前），它似乎讨论了同样的问题，但是对于一个旧的POI版本，也如上面所述，我遵循了调用evaluateAllFormulaCells（...）的一般实践在保存之前，甚至在每次公式单元格创建之后调用evaluateFormulaCell（cell）：Apache POI公式不求值

Answer 1

这是由于Apache POI在计算WEEKNUM函数时出现了一个bug造成的。

如果省略[return_type]，则ist总是计算为#valueerror。但是，即使您设置了[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 POI将其计算为39，但Excel将其计算为40。

要解决此bug，可以在打开文件时强制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);
  }

 }
}