Apache POI xlsx read, cell with # value - error unexpected cell type(5)

Question

Could you hepl me with such issue.

I need to read each cell as String value. In this case I am using appache poi lib. and such method for normilizing each cell:

String getNormilizedCell(Cell cell){
return new DataFormatter().formatCellValue(cell);}

But when In .xlsx file I faced with such value:

|#N/A|#N/A|...|...|...

I am getting error [Unexpected Cell type (5)] and I don't know how to handle this. In google I can't find necessary information.

Answer 1

The DataFormatter class only handles CELL_TYPE_FORMULA, CELL_TYPE_NUMERIC, CELL_TYPE_STRING, CELL_TYPE_BOOLEAN, and CELL_TYPE_BLANK. It doesn't handle CELL_TYPE_ERROR, which is 5.

You'll have to work around this by first detecting the error cell type and then handling it specially, referring to the error cell value codes:

if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
    byte errorValue = cell.getErrorCellValue();
    switch(errorValue) {
    case ERROR_DIV_0:
        return "#DIV/0!";
    case ERROR_NA:
        return "#N/A";
    case ERROR_NAME:
        return "#NAME?";
    case ERROR_NULL:
        return "#NULL!";
    case ERROR_NUM:
        return "#NUM!";
    case ERROR_REF:
        return "#REF!";
    case ERROR_VALUE:
        return "#VALUE!";
    default:
        return "Unknown error value: " + errorValue + "!";
    }
} else {
    return new DataFormatter().formatCellValue(cell);
}