Formula cell does not get evaluated until formula is focussed and confirmed in Excel (XLSX)-CodePudding

So I use Apache POI (poi-ooxml in the latest stable release version 5.0.0) and open an existing Excel (XSLX) file for editing (it is basically a template file to populate with additional data). I add multiple new rows of data and export the Excel again. All works fine, as long as I only add regular content cells.

Now, I have one column where I want to add a formula cell, I use the following (simplified for this example, you can be assured that in general it compiles/runs and generates a populated Excel file at the end) code to do so:

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

In general, this works fine. The first cell is created and populated with today's date, the second cell also gets created as a formula cell.

Now here comes the problem: When I open the Excel spreadsheet, I can see the data and in the formula cell I only see "#WERT" (using German Excel, I assume in the English version it would show something like "#VALUE").

When I simply click into the fomula editor in Excel and remove the focus again, it evaluates the formula correctly and the cell shows the correct week number.

I had some issues before with pre-existing formulas in the Excel I read, that they got not updated when I added data to the sheet, but that could be fixed with the call to XSSFFormulaEvaluator.evaluateAllFormulaCells(wb); For some reason, it does not affect my custom created formula cells.

I also tried to individually evaluate the newly created formula cells after creation:

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

This produced no changes here either.

Any idea what is wrong in my code or approach in general?

I use Excel version 16.53 (Excel for Mac) by the way, but I really hope it is not related to the exact Excel version :-)

Note: I found an old thread (way before POI 5.0.0 has been released) that seemed to discuss the very same issue, but with an older POI Version and also as stated above, I followed the general practice of calling evaluateAllFormulaCells(...) before saving and even called evaluateFormulaCell(cell) after each formula cell creation: Apache POI formulas not evaluating

CodePudding user response：

This results from a bug in apache poi while evaluating WEEKNUM function.

If [return_type] is omitted then ist always evaluates to #VALUE error. But even if you set [return_type] then it evaluates not always correct.

You can see this if you do:

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

If A1 contains the date 9/27/2021 and B1 contains the formula =WEEKNUM(A1) then apache poi FormulaEvaluator evaluates this to #VALUE. If B1 contains the formula =WEEKNUM(A1,1) then apache poi FormulaEvaluator evaluates this to 39, but Excel evaluates this to 40.

To work around this bug, one can force Excel to calculate all formulas while opening the file. This can be done using wb.setForceFormulaRecalculation(true);. Then Excel evaluates the formulas and so the results are correct.

Complete Example to reproduce the issue:

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);
  }

 }
}