getCachedFormulaResultType() of Apache POI returns

问题:

I am reading an Excel sheet. Initially (till rowid 30) I am getting the correct (CELL_TYPE_STRING) result type for the formula cell, but from row id 31 onwards I receive the incorrect (CELL_TYPE_NUMERIC) result type.

Please see the logs below: I have used ==== as delimiter for separating logs for each row. Column number 1 is the point to focus, where for rowID=30 I get type=String whereas for rowId=31 I get type=Numeric, but column 1 for each row is of type string therefore I am missing how to get a string value.

Please see the Excel sheet content for the row corresponding to the log.

        switch (cell.getCellType()) {

        case Cell.CELL_TYPE_FORMULA:
            cellWrapper.setCellValueFarmula(true);
            switch (cell.getCachedFormulaResultType()) {
            case Cell.CELL_TYPE_NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                logged.append("CELL_TYPE_FORMULA->CELL_TYPE_NUMERIC");
                break;
            case Cell.CELL_TYPE_STRING:
                cellValue = "" + cell.getRichStringCellValue();
                logged.append("CELL_TYPE_FORMULA->CELL_TYPE_STRING");
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cellValue = "" + cell.getBooleanCellValue();
                logged.append("CELL_TYPE_FORMULA->CELL_TYPE_BOOLEAN");
                break;
            case Cell.CELL_TYPE_ERROR:
                cellValue = "error! ";
                logged.append("CELL_TYPE_FORMULA->CELL_TYPE_ERROR");
                break;

            default:
                cellValue = null;
                logged.append("case not captured" + " type fetched = " + cell.getCellType());
                break;
            }
            break;
        case Cell.CELL_TYPE_BLANK:
            logged.append("CELL_TYPE_BLANK");
            cellValue = null;
            break;
        case Cell.CELL_TYPE_ERROR:
            cellValue = "error!";
            logged.append("CELL_TYPE_ERROR");
            break;
... some for cases below

Excel content

   Col1                                                    Col2                                   Col3                 Col4
[ACTIVE] - 21 - 1A - CALDRA - 305     .  {Chitra Subramaniam - 9538924280}, {[e-mail]}    12               Rs. 1,000
[ACTIVE] - 22 - 1A - CALDRA - 306     .  {Parasad Parigi - 9538924280}, {[e-mail]}        25               Rs. 1,000
[SEALED] - 23 - 1A - CALDRA - 401     .  {Vivek - 9538924280}, {[e-mail]}                 <empty cell>     Rs. 1,075

log

==================================================================================================
Cell @ row=30col=1   type = CELL_TYPE_FORMULA->CELL_TYPE_STRING  Value=[ACTIVE] - 21 - 1A - CALDRA - 305     .
Cell @ row=30col=2   type = CELL_TYPE_FORMULA->CELL_TYPE_STRING  Value= {Chitra Subramaniam - 9538924280}, {[e-mail]}
Cell @ row=30col=3   type = CELL_TYPE_NUMERIC    Value=12.0
Cell @ row=30col=4   type = CELL_TYPE_STRING     Value=Rs. 1,000
==================================================================================================
Cell @ row=31col=1   type = CELL_TYPE_FORMULA->CELL_TYPE_NUMERIC     Value=0.0
Cell @ row=31col=2   type = CELL_TYPE_FORMULA->CELL_TYPE_STRING  Value= {Parasad Parigi - 9538924280}, {[e-mail]}
Cell @ row=31col=3   type = CELL_TYPE_NUMERIC    Value=25.0
Cell @ row=31col=4   type = CELL_TYPE_STRING     Value=Rs. 1,000
==================================================================================================
Cell @ row=32col=1   type = CELL_TYPE_FORMULA->CELL_TYPE_NUMERIC     Value=0.0
Cell @ row=32col=2   type = CELL_TYPE_FORMULA->CELL_TYPE_STRING  Value= {Vivek - 9538924280}, {[e-mail]}
Cell @ row=32col=3   type = CELL_TYPE_BLANK  Value=null
Cell @ row=32col=4   type = CELL_TYPE_STRING     Value=Rs. 1,075
==================================================================================================

So how can I retrieve the cell result value, even if the value is numeric? Is there a generic method that will give the result value in string form irrespective of what type of cell is?

回答1:

Cells in Excel can have a number of different types. POI gives you back the cell value as Excel stored it, which may well not be how Excel displays it. (Excel can be a bit mysterious that way!).

If you want POI to try to format the cell to look like it does in Excel, no matter the type, then the class you're looking for is DataFormatter. Your code would be something like:

 DataFormatter fmt = new DataFormatter();
 for (Row r : sheet) {
    for (Cell c : r) {
       CellReference cr = new CellRefence(c);
       System.out.println("Cell " + cr.formatAsString() + " is " + 
                          fmt.formatCellValue(c) );
    }
 }