How to save excel with formula in Apache Poi?

Question

If there is a cell with a formula and save the file, then after manually closing the file, the excel will be asked to Save the changes? How to make a full save of file from the program because without this saving, I cannot get the correct value by reading the file.

Current file saving:

        File file = new File("Test.xlsx");
        FileOutputStream out;
        try {
            out = new FileOutputStream(file);
            workbook.write(out);
            workbook.close();
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

Formula update:

        XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
        workbook.setForceFormulaRecalculation(true);

Formula:

XSSFCell irrCell = row1.createCell(0);
irrCell.setCellFormula("IFERROR(IRR(A2:C2),0)");

File reading:

        try {
            workbook = new XSSFWorkbook(file);
            System.out.println(workbook.getSheetAt(0).getRow(0).getCell(0).getNumericCellValue());
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }

Cell values: -20000000, -1000, 2399000

Programm formula cell value: 0, file formula cell value: -0,653687014

Axel Richter · Accepted Answer · 2020-06-30T12:13:52.920

This is one of the cases where Microsoft does not follow their own rules. IRR function tells:

Microsoft Excel uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can't find a result that works after 20 tries, the #NUM! error value is returned.

In most cases you do not need to provide guess for the IRR calculation. If guess is omitted, it is assumed to be 0.1 (10 percent).

This is exactly what apache poi has implemented for IRR function. And that's why it gets the #NUM! error for your data sample. But Microsoft seems to do something different.

Using the formula IRR(A2:C2,-0.7), which has a given guess of -0.7, also apache poi is able to evaluate that IRR formula.

To get around this, one could do what IRR function description also tells:

If IRR gives the #NUM! error value, or if the result is not close to what you expected, try again with a different value for guess.

So try a guess from -1.0 to 1.0 in steps of 0.1 until you get a value instead of the `#NUM!' error.

Example:

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;

class ExcelEvaluateIRRFunction {
 
 public static void main(String[] args) throws Exception {
  
  Workbook workbook = WorkbookFactory.create(new FileInputStream("./Excel.xlsx")); String filePath = "./ExcelNew.xlsx";
  FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

  Sheet sheet = workbook.getSheetAt(0);
  Row row = sheet.getRow(1); if (row == null) row = sheet.createRow(1);
  row.createCell(0).setCellValue(-20000000d);
  row.createCell(1).setCellValue(-1000d);
  row.createCell(2).setCellValue(2399000d);

  row = sheet.getRow(0); if (row == null) row = sheet.createRow(0);
  Cell cell = row.createCell(0);

  CellValue evaluatedValue = null;
  for (int i = -10; i < 10; i++) {
   double guess = i/10d;
   System.out.println(guess);
   cell.setCellFormula("IRR(A2:C2," + guess + ")");
   evaluator.clearAllCachedResultValues();
   evaluatedValue = evaluator.evaluate(cell);
   System.out.println(evaluatedValue);
   if (evaluatedValue.getCellType() != CellType.ERROR) break;
  }

  evaluator.evaluateFormulaCell(cell);
  System.out.println(cell.getNumericCellValue());

  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  out.close() ;
  workbook.close();
 }
}

See also IRR in poi return NaN but correct value in excel for another example of the same problem.

How to save excel with formula in Apache Poi?

1 Answers1