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

    }
}

`if(cell.getStringCellValue() != "")` is wrong. It should be `if(!cell.getStringCellValue().equals(""))`. — Lion, Apr 03 '13 at 05:38

Gagravarr · Answer 1 · 2020-07-20T07:37:32.107

79

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!

edited Jul 20 '20 at 07:37

answered Apr 03 '13 at 05:21

Gagravarr

47,320
10
111
156

1

this is giving me NPE in some of the cases – tanvi Jan 07 '15 at 09:32
3

You need to ensure that the row isn't null - the code here will work fine for a non-null row as it already contains a cell null check! – Gagravarr Jan 07 '15 at 10:42
Thanks Gagravarr. That explains. But how do i check if the row is null in an if/while condition? I couldn't find a function for that. – tanvi Jan 08 '15 at 08:35
3

Umm, what about `if (row == null) { // Do something for an empty row }` ? – Gagravarr Jan 08 '15 at 10:39
Your code won't work if someone assumes that an excel cell is also empty if it contains an empty string (""). See my answer for the additional code check. – winklerrr Nov 16 '15 at 08:21
1

@winklerrr Excel should write an empty string as a blank cell, so that shouldn't happen – Gagravarr Nov 16 '15 at 10:12
@Gagravarr It's true that Excel does this. But it doesn't work if the content of a cell is changed and then checked for emptiness in the same Java program. If you want to, I can provide a unit test to make my case more clear. – winklerrr Nov 16 '15 at 10:32
c.getCellType() == Cell.CELL_TYPE_BLANK now depricated – irJvV Feb 22 '18 at 08:58
1

@irJvV You just need to wait for Apache POI 4.0, then it won't be, it's part of the shuffle from int to enum! – Gagravarr Feb 22 '18 at 15:19
It would be great if all participants in stackoverflow would understand how important package names are. – ka3ak Mar 23 '23 at 08:57

winklerrr · Answer 2 · 2019-01-17T14:49:41.893

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

Regards winklerrr

How is it with numbers? Can cell be of type NUMERIC and still be empty? — Jan Zyka, Apr 15 '16 at 09:05
@JanZyka No, a numeric cell would have at least a value of `0`. So you could see that value in the sheet. A numeric cell can't be "empty" in that way because you can't set the cell's value to a `null` double value. The setter for double values only accepts the primitive double type as you can see [here](https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFCell.html#setCellValue-double-). In contrast to that, the setter for the string value also accepts `null` values which will lead to an automatic conversion of the cell's type to `CellType.BLANK`. — winklerrr, Oct 09 '18 at 06:30
@Sandro thanks for your comment about the `trim()` method. Forgot this case. I edited my answer accordingly. — winklerrr, Nov 30 '18 at 13:19
I've just deleted my answer as it doesn't seem to be possible to format multi line code snippets well. I've created an answer instead. — Sandro, Nov 30 '18 at 14:21

score 6 · Answer 3 · answered Apr 24 '18 at 17:04

6

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

answered Apr 24 '18 at 17:04

gil.fernandes

12,978
5
63
76

score 4 · Answer 4 · edited May 11 '21 at 14:56

4

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

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

edited May 11 '21 at 14:56

Edu Castrillon

527
1
12
28

answered Feb 19 '18 at 09:35

softwareplay

1,379
4
28
64

score 2 · Answer 5 · edited Oct 07 '19 at 07:05

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

Sandro · Answer 6 · 2018-12-04T09:49:53.013

2

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.

edited Dec 04 '18 at 09:49

answered Nov 30 '18 at 13:10

Sandro

1,757
1
19
29

1

Pay attention: the deprecated that method in POI Apache 3.15, but as of Version 4 it's called `getCellType()` again. – winklerrr Nov 30 '18 at 13:44

Alex IP · Answer 7 · 2021-05-19T00:37:38.687

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++;
}

score 0 · Answer 8 · answered Sep 22 '15 at 04:22

0

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

answered Sep 22 '15 at 04:22

Prashant Gautam

589
8
10

Where is the check if the currently iterated cell is empty or not? – winklerrr Nov 16 '15 at 07:55

score 0 · Answer 9 · edited Dec 15 '17 at 18:49

0

.getCellType() != Cell.CELL_TYPE_BLANK

edited Dec 15 '17 at 18:49

Paul Roub

36,322
27
84
93

answered Dec 15 '17 at 18:45

El Tall yobro

1

4

Please note that code only answers are discouraged! – GhostCat Dec 15 '17 at 19:03

score 0 · Answer 10 · edited Sep 04 '18 at 00:52

0

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
}

edited Sep 04 '18 at 00:52

Stephen Rauch

47,830
31
106
135

answered Sep 04 '18 at 00:31

Yuva C

61
1
4

Pay attention: they deprecated that method in POI Apache 3.15, but as of Version 4 it's called `getCellType()` again. – winklerrr Nov 30 '18 at 13:44

score 0 · Answer 11 · answered Feb 21 '20 at 13:10

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

score 0 · Answer 12 · edited Jul 07 '20 at 13:45

0

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

edited Jul 07 '20 at 13:45

Shalu T D

3,921
2
26
37

answered Jul 07 '20 at 11:07

Ashwini

771
10
5

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

12 Answers12

Check if an excel cell is empty

Example

In addition: Clear a cell properly

Useful sources

Linked

Related