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 !

Solution 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?
    }
}

Solution 2

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

Solution 3

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.

Comments

[Rob Audenaerde]

Why not just check for null-ness using an if statement? (see also here: stackoverflow.com/questions/2586290/…)

[Henrik]

That will work to, and it is the accepted answer in the possibly duplicate question that was flagged..

[Malik]

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. ^^

[Matthias Seifert]

please avoid answers that only contain code. Try to describe what the code does and how it helps to solve the problem

[Dhiren Solanki]

This is what I am looking for