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

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

    }
}

Answers


How about:

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

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

Regards winklerrr


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.


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) { ... }

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

.getCellType() != Cell.CELL_TYPE_BLANK

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

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


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

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
}

Need Your Help

Android: toggle text color of ToggleButton

android android-xml togglebutton

To create a custom ToggleButton, I've defined a new style in /res/values/styles.xml:

Mouse cursor is not showing in windows store game developing using monogame

xna monogame windows-store

I am developing a game for windows store (desktops) using monogame framework. everything else is working excellent except the Mouse cursor which is working well but not showing at time of playing g...