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

Question

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

    }

}

Sorry.. my bad. I am new to Stack Overflow. I have edited my post with the complete reproducible example.... Thanks! — Rajesh P, Aug 21 '20 at 10:29
I don't think anyone can reproduce this without having the `Test1.xlsx`. — Axel Richter, Aug 21 '20 at 17:35
The link to Test1.xlsx is https://drive.google.com/file/d/1OmFMlxMOVsWpbmrVqOmRbEFKAxfJfbdu/view?usp=sharing — Rajesh P, Aug 22 '20 at 04:13

score 1 · Accepted Answer · answered Aug 22 '20 at 04:40

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 {
                }
            }

        }
...

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

1 Answers1