Java POI - anyone been able to extract numbers from formula cells?

Question

I've been using Java POI for some time now, but have encountered a new problem, and I'm wondering if anyone has found a solution.

When you read a spreadsheet, you need to know the type of cell in order to use the proper read method.

So you get the cell type, then call the appropriate read method to get the cell's contents.

This works for all cells except for the FORMULA cell, where the value is a number. If it's text, you can read it just fine. But if the resulting value is a number, then all you get from the cell is a blank string.

I've been through the javadocs for POI, and am using the correct data type (HSSFRichTextString), but still no joy.

Anyone have a solution?

P.S. this behavior of POI does bug me as there should be a default cell.toString() method that would return the string representation of ANY cell type, say defaulting to the cell's value property. (sort of like the paste-special where you can choose "value").

PPS: As asked - I'm using Java 6 (1.6.0_06) and poi-3.0.2-FINAL-20080204.jar

Answer 1

 FileInputStream fis = new FileInputStream("c:/temp/test.xls");
    Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("c:/temp/test.xls")
    Sheet sheet = wb.getSheetAt(0);
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

    // suppose your formula is in B3
    CellReference cellReference = new CellReference("B3"); 
    Row row = sheet.getRow(cellReference.getRow());
    Cell cell = row.getCell(cellReference.getCol()); 

    CellValue cellValue = evaluator.evaluate(cell);

    switch (cellValue.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            System.out.println(cellValue.getBooleanValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            System.out.println(cellValue.getNumberValue());
            break;
        case Cell.CELL_TYPE_STRING:
            System.out.println(cellValue.getStringValue());
            break;
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_ERROR:
            break;

        // CELL_TYPE_FORMULA will never happen
        case Cell.CELL_TYPE_FORMULA: 
            break;
    }

Copied shamelessly from here

Answer 2

If POI doesn't work out for you, try Andy Khan's JExcel. I prefer it.

Answer 3

Are you able to get the answer for your question.. i have encountered the same problem.. Please share the solution..

Thankx..

Answer 4

Hi i am new to jxl when i am using the above code,wb.getCreationHelper().createFormulaEvaluator(); is not visible to our code.Please tell me which jar file has this method.I think i am including the wrong jar version.

Thanks in advance

Answer 5

Hi Ageek,

when i read any cell containing any formula,then i need its value rather than the formula. so plz suggest any code that cud help me to read value from cell rather than formula. i m using jdk1.5 n poi package. i'll b helpful to u if u can mail me the solution on my e-mail id : [email protected]