使用Apache POI无法读取Excel数值类型单元格问题求助

Hey there! That error you're hitting happens because Apache POI enforces strict cell type checking — when you try to call getStringCellValue() on a numeric-formatted cell (like integers or decimals), it throws that IllegalStateException instead of doing an implicit conversion.

Since you need to read mixed cell types (strings + numerics) into your String[][] array, the fix is to check the cell's type first and handle each case appropriately, converting everything to a string in the end.

Step 1: Add a helper method for cell type conversion #

First, create a small utility method that takes a Cell and returns its value as a String, no matter what the original cell type is:

private String getCellValueAsString(Cell cell) {
    if (cell == null) {
        return ""; // Handle empty cells cleanly
    }
    
    switch (cell.getCellType()) {
        case STRING:
            return cell.getStringCellValue();
        case NUMERIC:
            // Excel stores dates as numeric values, so we need to detect those
            if (DateUtil.isCellDateFormatted(cell)) {
                // Adjust the date format to match your needs
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                return sdf.format(cell.getDateCellValue());
            } else {
                // Convert numbers to string (avoids scientific notation for large values)
                return String.valueOf(cell.getNumericCellValue());
            }
        case BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue());
        case FORMULA:
            // For formula cells, grab the calculated result
            switch (cell.getCachedFormulaResultType()) {
                case STRING:
                    return cell.getRichStringCellValue().getString();
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        return sdf.format(cell.getDateCellValue());
                    } else {
                        return String.valueOf(cell.getNumericCellValue());
                    }
                case BOOLEAN:
                    return String.valueOf(cell.getBooleanCellValue());
                default:
                    return "";
            }
        case BLANK:
            return "";
        default:
            return "";
    }
}

Step 2: Update your main reading code to use the helper #

In your row/cell iteration loop, replace the line listUsers[r][c] = cell.getStringCellValue(); with a call to the helper method:

while (cellIterator.hasNext()) {
    Cell cell = cellIterator.next();
    // Use the helper to safely get cell value as string
    listUsers[r][c] = getCellValueAsString(cell);
    c = c + 1;
}

Key Details to Note: #

• Date Handling: Excel stores dates as numeric values, so we use DateUtil.isCellDateFormatted() to detect and format them into readable strings. Tweak the SimpleDateFormat pattern to match your data's date format.
• Empty/Null Cells: The helper returns an empty string for blank or null cells, preventing NullPointerException and keeping your array consistent.
• Formula Cells: We handle formula cells by checking their cached calculated value, so you get the result of the formula instead of the formula text itself.

Why this works: #

This approach covers all common Excel cell types, converting each to a String that fits your String[][] array. Now your code will seamlessly read both string and numeric cells (plus dates, booleans, and formulas!) across all 6 of your worksheets.

内容的提问来源于stack exchange，提问作者Tarun Dabbs