Bug 58440 - Error while trying to get the formula from an Excel workbook
Summary: Error while trying to get the formula from an Excel workbook
Status: RESOLVED WONTFIX
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.12-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-09-21 21:02 UTC by Cosmin
Modified: 2019-03-10 20:08 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Cosmin 2015-09-21 21:02:27 UTC
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();       
    }
}
Comment 1 Cosmin 2015-09-21 21:04:15 UTC
I forgot to mention that instead of getting the result of the formula, I get the string "#VALUE!".
Comment 2 Dominik Stadler 2015-10-30 17:58:25 UTC
Can you attach a sample-file which will show the problem as it will be hard to reproduce this without the matching file.
Comment 3 Dominik Stadler 2019-03-10 20:08:36 UTC
Did not get back enough information to reproduce/fix this, so we cannot do anything here for now. Maybe the problem was already fixed in the meantime as there were numerous fixes applied in recent versions.

Please report a bug with enough information to reproduce the problem if this is still an issue for you.