Apache Poi - Value in a cell having error in existing workbook is not recalculated

I have an existing Excel workbook in which two "output" cells (with range name "rate" and "premium") have formulas based on values in other "input" cells (i.e. range names "loamamount", "loanterm", "age" and "smoker").

If, in the Excel workbook, I make all the input cells empty then the formulae evaluate to "#N/A". However when I populate the correct values in the input cells through poi and use the command

XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

The value in the two cells is not recalculated and remains "#N/A".

Can anyone help me understand what I am doing wrong? The code is as follows:

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFName;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class GetDataFromExcel {
public static void main(String[] args) throws IOException {
getData();
}
public static void getData() throws IOException {
String s, cCellName, cString;
XSSFName namedCell;
AreaReference aref;
CellReference[] crefs;
XSSFRow r;
XSSFCell c;
XSSFSheet sheet = null;
File directory = new File("./");
s = directory.getAbsolutePath();
s = s.substring(0, s.length() - 2);
InputStream ExcelFileToRead = new FileInputStream(s + "/src/main/java/Excel/Test1.xlsx");
XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
// Search for the Range names and set the input values
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("loanamount", 2000000);
map.put("age", 25);
map.put("smoker", "Y");
map.put("loanterm", 23);
for (HashMap.Entry<String, Object> entry : map.entrySet()) {
cCellName = entry.getKey();
namedCell = wb.getName(cCellName);
// Retrieve the cell at the named range and test its contents
aref = new AreaReference(namedCell.getRefersToFormula(), null);
crefs = aref.getAllReferencedCells();
for (int i = 0; i < crefs.length; i++) {
sheet = wb.getSheet(crefs[i].getSheetName());
r = sheet.getRow(crefs[i].getRow());
c = r.getCell(crefs[i].getCol(), Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
// extract the cell contents based on cell type etc.
cString = entry.getValue().toString(); // Add missing cell check
if (c.getCellType() == CellType.STRING) {
c.setCellValue(cString);
} else if (c.getCellType() == CellType.NUMERIC) {
c.setCellValue(Double.parseDouble(cString));
} else if (c.getCellType() == CellType.BOOLEAN) {
c.setCellValue(cString);
} else {
}
}
}
sheet = wb.getSheetAt(0);
XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
// wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
// Get all the output values requested
Iterator<String> i;
ArrayList<String> outputValues = new ArrayList<String>();
outputValues.add("rate");
outputValues.add("premium");
i = outputValues.iterator();
while (i.hasNext()) {
cCellName = i.next();
namedCell = wb.getName(cCellName);
// retrieve the cell at the named range and test its contents
aref = new AreaReference(namedCell.getRefersToFormula(), null);
crefs = aref.getAllReferencedCells();
for (int j = 0; j < crefs.length; j++) {
sheet = wb.getSheet(crefs[j].getSheetName());
r = sheet.getRow(crefs[j].getRow());
c = r.getCell(crefs[j].getCol(), Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);/////
// extract the cell contents based on cell type etc.
System.out.println(cCellName +" : "+c.getRawValue());
}
}
wb.close();
}
}

Your code part which should set values into cells does not set values into cells. So because there are no cell values set and the cells remain empty, the formulas will result in #N/A error.

This is because blank cells will not be of any cell type you are testing in your code. A blank cell is of CellType.BLANK and not CellType.STRING nor CellType.NUMERIC nor CellType.BOOLEAN. So none of your if... will be true and so no value will be set into the cells.

Rather than trying to set cell values dependent of cell types which cannot exist for blank cells, you should put cell values dependent on entry value type.

Example:

...
for (HashMap.Entry<String, Object> entry : map.entrySet()) {
cCellName = entry.getKey();
namedCell = wb.getName(cCellName);
// Retrieve the cell at the named range and test its contents
aref = new AreaReference(namedCell.getRefersToFormula(), null);
crefs = aref.getAllReferencedCells();
for (int i = 0; i < crefs.length; i++) {
sheet = wb.getSheet(crefs[i].getSheetName());
r = sheet.getRow(crefs[i].getRow());
c = r.getCell(crefs[i].getCol(), Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
// put cell values dependent on entry value type
Object o = entry.getValue();
if (o instanceof String) {
c.setCellValue((String)o);
} else if (o instanceof Number) {
c.setCellValue(((Number)o).doubleValue());
} else if (o instanceof Boolean) {
c.setCellValue((Boolean)o);
} else {
}
}
}
...