Error while trying to get the formula from an Excel workbook with Apache POI

Question

I'm using Apache POI to alter some values in an Excel document and then read the result from different cells. Everything went ok until I got a test document from the client. After testing it I found out that I have two issues:

1) When I try to get the value from a cell that has a formula but returns the result formatted as currency with the € sign as a prefix I get an error.

2) When I try to get the value from a cell that references another cell that is a formula (eg.: cell B20 from Sheet 3 has the value of "=Sheet 2!A20" where A20 in Sheet 2 is a SUM() formula.), I get an error.

The error is: Exception in thread "main" java.lang.IllegalStateException: Cannot get a numeric value from a error formula cell.

The document name, input columns (where the values are altered) and output columns (from where the values are read) are taken from the command line.

You can find my code bellow:

package poitest;

import java.util.List;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.*;

public class ReadExcel {
    public static void main(String[] args) throws FileNotFoundException, IOException {
        // Will contain cell name / value pair for input cells          
        Map<String, String> inputCellsMap = new HashMap<String, String>();

        // Will contain cell name for output cells
        List<String> outputCells = new ArrayList<String>();

        // Open the Excel file
        FileInputStream file = new FileInputStream(new File(args[0]));

        // Get the current workbook
        HSSFWorkbook workbook = new HSSFWorkbook(file);         

        // Get the input cells that need to be modified and
        // store their name and value in the inputCellsMap
        for (String element : args[1].split(";")) {
            inputCellsMap.put(element.split("=")[0], element.split("=")[1]);
        }

        // Get the output cells that will be accessed for resulting values
        for (String element : args[2].split(";")) {
            outputCells.add(element);           
        }

        // Loop through the cells that need to be modified and 
        // set the new value in the Excel document
        Iterator<Entry<String,String>> inputIterator = inputCellsMap.entrySet().iterator();
        while (inputIterator.hasNext()) {
            Map.Entry<String,String> inputEntry = (Map.Entry<String,String>) inputIterator.next();

            CellReference cellReferenceInput = new CellReference(inputEntry.getKey());
            int cellReferenceInputRow = cellReferenceInput.getRow();
            int cellReferenceInputColumn = cellReferenceInput.getCol();

            // Get sheet name for each input cell
            HSSFSheet inputSheet = workbook.getSheet(inputEntry.getKey().split("!")[0]);

            Row rowInput = inputSheet.getRow(cellReferenceInputRow);
            if (rowInput == null)
                rowInput = inputSheet.createRow(cellReferenceInputRow);
            Cell cellInput = rowInput.getCell(cellReferenceInputColumn, Row.CREATE_NULL_AS_BLANK);              
            cellInput.setCellValue(Integer.parseInt(inputEntry.getValue()));        
        }

        // Apply all formulas after altering cell values        
        workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();        

        // Get the results from the output cells
        for (int i = 0; i < outputCells.size(); i++) {
            CellReference cellReferenceOutput = new CellReference(outputCells.get(i));
            int cellReferenceOutputRow = cellReferenceOutput.getRow();
            int cellReferenceOutputColumn = cellReferenceOutput.getCol();

            // Get sheet name for each output cell
            HSSFSheet outputSheet = workbook.getSheet(outputCells.get(i).split("!")[0]);

            Row rowOutput = outputSheet.getRow(cellReferenceOutputRow);
            Cell cellOutput = rowOutput.getCell(cellReferenceOutputColumn, Row.CREATE_NULL_AS_BLANK);

            // Display results
            switch (cellOutput.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.println(cellOutput.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    System.out.println(cellOutput.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_STRING:
                    System.out.println(cellOutput.getStringCellValue());
                    break;
                case Cell.CELL_TYPE_BLANK:
                    break;              
                case Cell.CELL_TYPE_FORMULA:                            
                    switch (cellOutput.getCachedFormulaResultType()) {
                        case Cell.CELL_TYPE_STRING:
                            System.out.println(cellOutput.getRichStringCellValue());                            
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            HSSFCellStyle style = (HSSFCellStyle) cellOutput.getCellStyle();
                            if (style == null) {
                                System.out.println(cellOutput.getNumericCellValue());
                            } else {
                                DataFormatter formatter = new DataFormatter();
                                System.out.println(formatter.
                                        formatRawCellContents(
                                                cellOutput.getNumericCellValue(), 
                                                style.getDataFormat(),
                                                style.getDataFormatString())
                                        );
                            }
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                            System.out.println(cellOutput.getBooleanCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_ERROR:
                            System.out.println(ErrorEval.getText(cellOutput.getErrorCellValue()));                          
                            break;
                    }

                    break;
            }                           
        }           

        workbook.close();       
    }
}

Answer 1

I would advice to do

evaluator.clearAllCachedResultValues()

before any operation with evaluator if not created just now.

Javadoc:

/**
 * Should be called whenever there are changes to input cells in the evaluated workbook.
 * Failure to call this method after changing cell values will cause incorrect behaviour
 * of the evaluate~ methods of this class
 */

Answer 2

Your exception is pretty clear:

Exception in thread "main" java.lang.IllegalStateException: Cannot get a numeric value from a error formula cell.

You have a formula cell which evaluated to an error. Therefore, you can't fetch the numeric value of it, as there isn't one, just the error value

Since what you seem to be doing is printing out the cell's value, the easy option is just to use DataFormatter to get the Cell formatted into a String. That takes care of all the fiddly bits around cell types, styles etc. Just use DataFormatter.formatCellValue(Cell) and it'll take care of it for you

If not, you need to check both the cell's type, and the evaluated type, eg

if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
   switch(cell.getCachedFormulaResultType()) {
     case Cell.CELL_TYPE_ERROR:
        System.out.println(cell.getErrorCellValue());
        break;
     case Cell.CELL_TYPE_NUMERIC:
        // Prints unstyled, use DataFormatter to style it
        System.out.println(cell.getNumericCellValue());
        break;
     // TODO Remaining cell types

Answer 3

case Cell.CELL_TYPE_FORMULA:                    
    System.out.println(cellOutput.getNumericCellValue());
    break;

should actually be:

case Cell.CELL_TYPE_FORMULA:                    
        System.out.println(cellOutput.getCellFormula());
        break;

After that you can do:

case Cell.CELL_TYPE_FORMULA:                    
    System.out.println(cellOutput.getCellFormula());
    switch(cellOutput.getCachedFormulaResultType()) {
        case Cell.CELL_TYPE_NUMERIC:
        System.out.println(cellOutput.getNumericCellValue());
        break;

See the docs for more