How to get a value that returns from an Excel formula cell using Apache POI?

When I tried to get a value like system date from an excel’s sheet which has formula applied , it returns the cached value . Not the latest value , unless I reopen and close the excel sheet .

So is there anyway to get the latest value from an excel cell using Apache POI?

For this we can use FormulaEvaluator class of Apache POI. Below are the required maven dependencies.

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>

Sample code

package com.webdriver.blogs;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.sl.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcelFormulaValue {
private static final CellType NUMERIC = null;
 public static void main(String[] args) {
  try {
   readFormula();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 public static void readFormula() throws IOException {
     FileInputStream fis = new FileInputStream("C:\\sam-webdbriver-diaries\\resources\\user.xls");
     org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(fis);
     org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
CellReference cellReference = new CellReference("G2"); // pass the cell which contains the formula
     Row row = sheet.getRow(cellReference.getRow());
     Cell cell = row.getCell(cellReference.getCol());
CellValue cellValue = evaluator.evaluate(cell);
     System.out.println("Cell type month  is "+cellValue.getCellTypeEnum());
     System.out.println("getNumberValue month is  "+cellValue.getNumberValue());     
      //  System.out.println("getStringValue "+cellValue.getStringValue());
        
     
        cellReference = new CellReference("H2"); // pass the cell which contains the formula
      row = sheet.getRow(cellReference.getRow());
      cell = row.getCell(cellReference.getCol());
cellValue = evaluator.evaluate(cell);
     System.out.println("getNumberValue DAY is  "+cellValue.getNumberValue());
}
}

This will return below output.

Cell type month  is NUMERIC
getNumberValue month is  5.0
getNumberValue DAY is  11.0