Java – Apache POI xlsx read, cell with # value – wrong unexpected cell type (5)

Can you solve this problem?

I need to read each cell as a string value In this case, I use Apache POI lib And ways to normalize each cell:

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

But in In the xlsx file, I encountered such value:

|#N / A |#N / A | … | … | …

I received an error [unexpected cell type (5)]. I don't know how to deal with this problem I can't find the necessary information in Google

Solution

The dataformatter class only processes cells_ TYPE_ FORMULA,CELL_ TYPE_ NUMERIC,CELL_ TYPE_ STRING,CELL_ TYPE_ Boolean and cell_ TYPE_ BLANK. It does not handle cell_ TYPE_ Error, i.e. 5

You must first detect the wrong cell type, and then deal with it specifically. Refer to 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);
}