CODEWITHSUNDEEP
javaapache-poi
user1312312
user1312312

Reputation: 635

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

    }
}

Upvotes: 55

Views: 177604

Answers (12)

Gagravarr
Gagravarr

Reputation: 48326

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!

Upvotes: 82

Alex IP
Alex IP

Reputation: 21

Row.MissingCellPolicy.CREATE_NULL_AS_BLANK is work in my case.

total_colume = myRow.getLastCellNum();
int current_colume = 0;
HSSFCell ReadInCellValue;

while (current_colume <= total_colume) {
   ReadInCellValue = myRow.getCell(current_colume, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);//if cell is empty, return black
   if (ReadInCellValue.toString=="") Log.d("empty cell", "colume=" + String.valuOf(current_colume));
   current_colume++;
}

Upvotes: 1

softwareplay
softwareplay

Reputation: 1409

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

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

Upvotes: 4

Ashwini
Ashwini

Reputation: 851

Try below code:

String empty = "-";
if (row.getCell(3) == null || row.getCell(3).getCellType() == Cell.CELL_TYPE_BLANK) {
    upld.setValue(empty);
} else {
    upld.setValue(row.getCell(3).getStringCellValue());
}

Upvotes: 0

prashant kute
prashant kute

Reputation: 343

You can also use switch case like 

                    String columndata2 = "";
                    if (cell.getColumnIndex() == 1) {// To match column index

                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_BLANK:
                                columndata2 = "";
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                columndata2 = "" + cell.getNumericCellValue();
                                break;
                            case Cell.CELL_TYPE_STRING:
                                columndata2 = cell.getStringCellValue();
                                break;
                        }

                    }
                    System.out.println("Cell Value "+ columndata2);

Upvotes: 0

Tarek Badr
Tarek Badr

Reputation: 887

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

Upvotes: 2

winklerrr
winklerrr

Reputation: 14717

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

Upvotes: 34

Sandro
Sandro

Reputation: 1835

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.

Upvotes: 2

Yuva C
Yuva C

Reputation: 61

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
}

Upvotes: 0

gil.fernandes
gil.fernandes

Reputation: 14591

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

Upvotes: 6

El Tall yobro
El Tall yobro

Reputation: 1

.getCellType() != Cell.CELL_TYPE_BLANK

Upvotes: 0

Prashant Gautam
Prashant Gautam

Reputation: 609

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

Upvotes: 0

Related Questions