How to check if an excel cell is empty using Apach

问题:

I am taking input from an excel sheet using Poi.jar and wanted to know how to check if a cell is empty or not.

Right now I m using the below code.

cell = myRow.getCell(3);
if (cell != null) {
    cell.setCellType(Cell.CELL_TYPE_STRING);

    //System.out.print(cell.getStringCellValue() + "\t\t");
    if (cell.getStringCellValue() != "")
        depend[p] = Integer.parseInt(cell.getStringCellValue());

    }
}

回答1:

How about:

 Cell c = row.getCell(3);
 if (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK) {
    // This cell is empty
 }

回答2:

Gagravarr's answer is quite good!

---

Check if an excel cell is empty

But if you assume that a cell is also empty if it contains an empty String (""), you need some additional code. This can happen, if a cell wasn't cleared properly (for how to do that, see further below).

I wrote myself a helper to check if an XSSFCell is empty (including an empty String).

 /**
 * Checks if the value of a given {@link XSSFCell} is empty.
 * 
 * @param cell
 *            The {@link XSSFCell}.
 * @return {@code true} if the {@link XSSFCell} is empty. {@code false}
 *         otherwise.
 */
public static boolean isCellEmpty(final XSSFCell cell) {
    if (cell == null) { // use row.getCell(x, Row.CREATE_NULL_AS_BLANK) to avoid null cells
        return true;
    }

    if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        return true;
    }

    if (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().trim().isEmpty()) {
        return true;
    }

    return false;
}

Pay attention for newer POI Version

They first changed getCellType() to getCellTypeEnum() as of Version 3.15 Beta 3 and then moved back to getCellType() as of Version 4.0.

• Version >= 3.15 Beta 3:

  • Use CellType.BLANK and CellType.STRING instead of Cell.CELL_TYPE_BLANK and Cell.CELL_TYPE_STRING

• Version >= 3.15 Beta 3 && Version < 4.0

  • Use Cell.getCellTypeEnum() instead of Cell.getCellType()

But better double check yourself, because they planned to change it back in future releases.

---

Example

This JUnit test shows the case in which the additional empty check is needed.

Scenario: the content of a cell is changed within a Java program. Later on, in the same Java program, the cell is checked for emptiness. The test will fail if the isCellEmpty(XSSFCell cell) function doesn't check for empty Strings.

@Test
public void testIsCellEmpty_CellHasEmptyString_ReturnTrue() {
    // Arrange
    XSSFCell cell = new XSSFWorkbook().createSheet().createRow(0).createCell(0);

    boolean expectedValue = true;
    boolean actualValue;

    // Act
    cell.setCellValue("foo");
    cell.setCellValue("bar");
    cell.setCellValue(" ");
    actualValue = isCellEmpty(cell);

    // Assert
    Assert.assertEquals(expectedValue, actualValue);
}

---

In addition: Clear a cell properly

Just in case if someone wants to know, how to clear the content of a cell properly. There are two ways to archive that (I would recommend way 1).

// way 1
public static void emptyCell(final XSSFCell cell) {
    cell.setCellType(Cell.CELL_TYPE_BLANK);
}

// way 2
public static void emptyCell(final XSSFCell cell) {
    String nullString = null;
    cell.setCellValue(nullString);
}

---

Useful sources

• Official Documentation
  • CellTypes
  • XSSFCell.setCellValue(String)
  • Cell.setCellType(CellType)

Regards winklerrr

回答3:

As of Apache POI 3.17 you will have to check if the cell is empty using enumerations:

import org.apache.poi.ss.usermodel.CellType;

if(cell == null || cell.getCellTypeEnum() == CellType.BLANK) { ... }

回答4:

This is the safest and most concise way I see as of POI 3.1.7 up to POI 4:

boolean isBlankCell = CellType.BLANK == cell.getCellTypeEnum();
boolean isEmptyStringCell = CellType.STRING == cell.getCellTypeEnum() && cell.getStringCellValue().trim().isEmpty(); 

if (isBlankCell || isEmptyStringCell) {
    ...

As of POI 4 getCellTypeEnum() will be deprecated if favor of getCellType() but the return type should stay the same.

回答5:

There is one other option also .

row=(Row) sheet.getRow(i);
        if (row == null || isEmptyRow(row)) {
            return;
        }
Iterator<Cell> cells = row.cellIterator();
    while (cells.hasNext())
     {}

回答6:

.getCellType() != Cell.CELL_TYPE_BLANK

回答7:

Cell cell = row.getCell(x, Row.CREATE_NULL_AS_BLANK);

This trick helped me a lot, see if it's useful for you

回答8:

First to avoid NullPointerException you have to add this Row.MissingCellPolicy.CREATE_NULL_AS_BLANK this will create a blank cell instead of giving you NPE then you can check to make sure nothing went wrong just like what @Gagravarr have said.

Cell cell = row.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
    if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) 
        // do what you want

回答9:

Cell.getCellType() is deprecated in the latest POI API. If you are using POI API version 3.17, use the below code:

if (Cell.getCellTypeEnum() == CellType.BLANK) {
    //do your stuff here
}