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

Learning Learning · Accepted Answer · 2009-02-09T05:03:13

 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

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

4 Answers