How to avoid NullPointerException with an empty cell using POI?

Question

I'm trying to get some values in my java program from an excel .xlsx file using Apache POI, but I'm having trouble because my loop encounters an empty cell sometimes, then I get a NullPointerException. How can I "test" the cell before even reading it ? Here's a piece of my code :

FileInputStream file = new FileInputStream(new File(file));
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
int rows;
rows = sheet.getPhysicalNumberOfRows();
for (int i=1;i<rows;i++){
    Row row = sheet.getRow(i);
    Cell cell = row.getCell(2); // Here is the NullPointerException
    String cellString = cell.getStringCellValue();
    myArrayList.add(cellString);
}

Which brings me to :

java.lang.NullPointerException
at analyse.Analyser.getExcelWords3(Analyser.java:73)
at analyse.Analyser.main(Analyser.java:21)

I want to know if there's a possibility to check if the cell is empty before trying to read it, then I won't get the NPE. Thank you in advance !

Possible duplicate - http://stackoverflow.com/questions/18072847/null-pointer-exception-apache-poi — mbsingh, Apr 14 '15 at 08:35
Ary you sure that the cell is empty? It looks like that the row is null! — Jens, Apr 14 '15 at 08:36
I tested with if, but it didn't work. @Jens gave the solution : cells were not the **only one** problem, there was also null rows. I didn't even think about it because of the `sheet.getPhysicalNumberOfRows()` method... Anyway, thank you very much ! ^^ — Malik, Apr 14 '15 at 08:42
Did you try reading the [Apache POI docs on fetching rows and cells](http://poi.apache.org/spreadsheet/quick-guide.html#Iterator)? — Gagravarr, Apr 14 '15 at 21:48

score 5 · Answer 1 · answered May 02 '18 at 19:25

5

To avoid NullPointerException add this Row.MissingCellPolicy.CREATE_NULL_AS_BLANK

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

This will create a blank cell instead of giving you NPE

answered May 02 '18 at 19:25

Tarek Badr

847
9
12

This is what I am looking for – Dhiren Solanki Aug 20 '20 at 11:39

score 1 · Accepted Answer · answered Apr 14 '15 at 08:37

1

wrap your code in a try / catch statement that is what it's there for.. https://docs.oracle.com/javase/tutorial/essential/exceptions/catch.html

some untested code below to give you the idea:

for (int i=1;i<rows;i++){
    try{
        Row row = sheet.getRow(i);
        Cell cell = row.getCell(2); // Here is the NullPointerException
        String cellString = cell.getStringCellValue();
        myArrayList.add(cellString);
    }catch(NullPointerException NPE)
    {
        //what to do when the exception occurs?
    }
}

answered Apr 14 '15 at 08:37

Henrik

2,180
16
29

1

Why not just check for `null`-ness using an if statement? (see also here: http://stackoverflow.com/questions/2586290/is-catching-a-null-pointer-exception-a-code-smell) – Rob Audenaerde Apr 14 '15 at 08:38
That will work to, and it is the accepted answer in the possibly duplicate question that was flagged.. – Henrik Apr 14 '15 at 08:40
That would have soled my problem also. The problem is that I thought about cells but not about rows. I accept this answer then, thank you. ^^ – Malik Apr 14 '15 at 08:44

score 1 · Answer 3 · answered Apr 14 '15 at 08:41

Look at this method:

/**
 * Returns the cell at the given (0 based) index, with the specified {@link org.apache.poi.ss.usermodel.Row.MissingCellPolicy}
 *
 * @return the cell at the given (0 based) index
 * @throws IllegalArgumentException if cellnum < 0 or the specified MissingCellPolicy is invalid
 * @see Row#RETURN_NULL_AND_BLANK
 * @see Row#RETURN_BLANK_AS_NULL
 * @see Row#CREATE_NULL_AS_BLANK
 */
public XSSFCell getCell(int cellnum, MissingCellPolicy policy) {

It should help you.

pragyan · Answer 4 · 2017-10-26T09:46:03.807

`       FileInputStream file = new FileInputStream(new File(file));
     XSSFWorkbook workbook = new XSSFWorkbook(file);
     XSSFSheet sheet = workbook.getSheetAt(0);
     int rows;
     String cellString = null;
     rows = sheet.getPhysicalNumberOfRows();
    for (int i=1;i<rows;i++){
    Row row = sheet.getRow(i);
    if(row.getCell(2) == null)
    {
    cellString = null;
    }
    else
    {
    Cell cell = row.getCell(2); //if 3rd column of excelsheet then     getCell(2)..
    cellString = cell.getStringCellValue();
    }
    myArrayList.add(cellString);
    }
      if excel cell contain empty/blank then it will be stored as null`

please avoid answers that only contain code. Try to describe what the code does and how it helps to solve the problem — Matthias Seifert, Oct 26 '17 at 09:31

How to avoid NullPointerException with an empty cell using POI?

4 Answers4