How do I know if a cell returns a null value while reading an Excel Sheet?

Question

I am reading values from some specific cells in an excel sheet. Those cells generally contain String but sometimes they may be blank (thus may return a blank or a null). I am using the following code to get the cell data:

Cell cellValue = row.getCell(10, Row.RETURN_NULL_AND_BLANK);
List.add(cellValue.getStringCellValue());

However, when I run this code I get a NullPointerException. Can anybody point out what is wrong and should be added?

Note: I am deliberately using Row.RETURN_NULL_AND_BLANK and not MissingCellPolicy.RETURN_NULL_AND_BLANK because using the latter was giving me an error.

dan · Accepted Answer · 2013-02-21T23:50:05.353

You need to test the cellValue if it is not null, since using Row.RETURN_NULL_AND_BLANK will return null in cases where you are trying to access a missing cell.

You are getting a NullPointerException because the 10-th column on a specified row is missing.

If you need to handle differently the missing vs. blank cases you can use the following:

Cell cellValue = row.getCell(10, Row.RETURN_NULL_AND_BLANK);
if (cellValue == null)
    List.add("CELL NOT FOUND");
else if("".equals(cellValue.getStringCellValue().trim()))
    List.add("MISSING CONTENT");
else 
    List.add(cellValue.getStringCellValue());

score 1 · Answer 2 · answered Feb 21 '13 at 23:42

1

@dan - Thanks for your reply. That indeed helped. I modified my code as follows and I am not getting that exception now:

Cell cellValue = row.getCell(10, Row.CREATE_NULL_AS_BLANK);
if(cellValue.getStringCellValue().equals("")){
    List.add("NOT FOUND");
}
else {
    List.add(cellValue.getStringCellValue());
}

answered Feb 21 '13 at 23:42

TheRookierLearner

3,643
8
35
53

1

You're welcome. But note that now you are handling the empty cells and the missing ones the same. See my updated answer. – dan Feb 21 '13 at 23:47

Mohamed Makthum · Answer 3 · 2013-02-22T01:34:55.327

0

hey there , why cant you add the null pointer check like

if(null!=cellValue)

    {

    List.add(cellValue.getStringCellValue());
}

hope this helps if the cell value is null then you dont add the cell value to your list.

edited Feb 22 '13 at 01:34

answered Feb 21 '13 at 23:40

Mohamed Makthum

96
3

How do I know if a cell returns a null value while reading an Excel Sheet?

3 Answers3