How to check if an excel cell is empty using Apache POI?

Question

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());      } } 

Answer 1

If you're using Apache POI 4.x, you can do that with:

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

For older Apache POI 3.x versions, which predate the move to the CellType enum, it's:

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

Don't forget to check if the Row is null though - if the row has never been used with no cells ever used or styled, the row itself might be null!

Answer 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 was edited and then not cleared properly (for how to clear a cell properly, 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 clearCell(final XSSFCell cell) {     cell.setCellType(Cell.CELL_TYPE_BLANK); }  // way 2 public static void clearCell(final XSSFCell cell) {     String nullString = null;     cell.setCellValue(nullString);  } 

Why way 1? Explicit is better than implicit (thanks, Python)

Way 1: sets the cell type explicitly back to blank.
Way 2: sets the cell type implicitly back to blank due to a side effect when setting a cell value to a null String.

---

Useful sources

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

Regards winklerrr