使用Java POI读取Excel时空白单元格返回Null问题咨询

Hey there! Let's figure out why you're getting null values for blank Excel cells when using Apache POI, and how to fix this for your data display needs.

Apache POI is built to be memory-efficient, so it only creates Cell objects for cells that are actually "present" in the Excel sheet. A cell counts as present if:

• It contains text, numbers, formulas, or any other value
• It has custom formatting (like changed font, background color, or cell borders)
• It was ever edited and then cleared (even if it’s now empty)

Completely untouched blank cells—those that were never clicked, edited, or formatted in any way—don’t get a Cell object created at all. So when you call row.getCell(cellIndex) on these positions, you’ll get null because there’s no object to return.

Additionally, if a cell was edited and then cleared, POI might keep the Cell object but set its value to null or mark its type as CellType.BLANK. Here, getCell() returns the object, but reading its value could give you an empty string or null depending on the method you use.

Here are practical ways to deal with this issue so you can display your Excel data correctly:

1. Use a Missing Cell Policy to Avoid null #

POI has a built-in way to handle missing cells by specifying a policy when retrieving cells. The CREATE_NULL_AS_BLANK policy returns a blank Cell object instead of null for positions with no existing cell. This lets you handle all cells uniformly:

// Retrieve cell at index 3, create a blank cell if it doesn't exist
Cell cell = row.getCell(3, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

// Check if the cell is blank and handle accordingly
if (cell.getCellType() == CellType.BLANK) {
    // Use a default value or mark it as empty in your display
    System.out.println("Displaying default value for blank cell");
} else {
    // Process the cell's value normally
    String cellValue = cell.getStringCellValue();
    // ... your display logic
}

2. Iterate Through All Columns (Including Blank Positions) #

If you need to process every column in a row (even if some are blank), first get the row’s last column index, then loop through each index with the missing cell policy:

int lastColumnIndex = row.getLastCellNum();

// Loop through each column from 0 to lastColumnIndex
for (int colIndex = 0; colIndex < lastColumnIndex; colIndex++) {
    Cell cell = row.getCell(colIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
    
    // Handle different cell types
    switch (cell.getCellType()) {
        case BLANK:
            System.out.print("| [Empty] ");
            break;
        case STRING:
            System.out.print("| " + cell.getStringCellValue() + " ");
            break;
        case NUMERIC:
            System.out.print("| " + cell.getNumericCellValue() + " ");
            break;
        // Add cases for other types like FORMULA, BOOLEAN as needed
        default:
            System.out.print("| [Unsupported Type] ");
    }
}
System.out.println("|");

3. Differentiate Between "Never Edited" and "Cleared" Cells #

If you need to tell apart cells that were never touched vs. cells that were edited and then cleared, first check if the cell is null, then check its type if it exists:

Cell cell = row.getCell(2);

if (cell == null) {
    // This cell was never created/edited in Excel
    System.out.println("Cell was never touched—true blank");
} else if (cell.getCellType() == CellType.BLANK) {
    // This cell was edited and then cleared
    System.out.println("Cell was cleared after being edited");
} else {
    // Cell has a value—process it
    // ... your logic
}

POI returns null for completely untouched blank cells to save memory. By using the CREATE_NULL_AS_BLANK policy or iterating through all column indices, you can ensure you handle every cell position consistently for your display needs.

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