java - NullPointerException when accessing a cell with Apache POI

Question

I am trying to read an Excel file with Apache POI. My goal is to get the cells 2 & 3 from every row and place them into an array. In some rows cell number 3 is empty (I am not talking about EMPTY, NULL or BLANK). Just empty if you check them in Excel.

Some of those empty cells (not all) cause the NullPonterException: Exception in thread "main" java.lang.NullPointerException. It happens when trying to get the cell value or the cell type. I have the impression that there is no cell at that location.

At the same time if I do row.createCell(2).setCellType(1); for that specific row the program continues until next cell of this type. That is the only workaround (that I could find) to continue reading cells from next rows.

Could you please help me understand how to identify these cells (with code) and put my workaround in place so I can continue reading till the end of the file?

This is the code:

static ArrayList<String> getFirstFile() throws IOException
{
    FileInputStream fileIn = null;

    ArrayList <String> namesFirstFile = new ArrayList <String>();
    String folderPath = "C:";
    String indivList1 = "filename.xls";

    fileIn = new FileInputStream(folderPath+indivList1);
    POIFSFileSystem fs = new POIFSFileSystem(fileIn);
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    int rowNumber = 6; //skipping the first 6 rows as irrelevant
    HSSFRow row = sheet.getRow(rowNumber);
    String firstName = "";
    String lastName = "";

    while(row.getCell(0).getCellType() == 0) {
        lastName = row.getCell(1).toString();
        firstName = row.getCell(2).toString();

        namesFirstFile.add((rowNumber-6), (lastName + " " + firstName));
        rowNumber++;
        row = sheet.getRow(rowNumber);          
    }
}

Answer 1

You have to check the presence of that cell. Remember if a row is present doesn't mean that all the cells are present. If that particular cell is not present doing get operations will throw you null pointer error, but create cell will not throw an error because it creates that cell in that particular row. You can do a dot operator on that cell object only if it exists.

For Example:

If the cell at 3rd position is present this will work:

row1.getCell(2).getStringCellValue(); // if the value is of string type

else you have check using if and create that cell if needed.

// This checks if the cell is present or it'll return null if the cell
// is not present (it won't check the value in cell)
if (row1.getCell(2) != null)
{
   row1.createCell(2);
}

If that cell is not present and your only objective is to get the value, you can set a default value in your array.

These 2 lines won't work because it'll convert the cell object to string but not the content of it:

lastName = row.getCell(1).toString();
firstName = row.getCell(2).toString();

You have to change it to:

lastName = row.getCell(1).getStringCellValue();
firstName = row.getCell(2).getStringCellValue();

You need not do this when you don't have any value in that cell:

if (row.getCell(2) != null)
{
    row.createCell(2);
}
firstName = row.getCell(2).toString();

change this to:

if (row.getCell(2) != null)
{
    firstName = row.getCell(2); // your other operations
} else {
    firstname = "default value"; // your other operations
}

There's no need of doing a get cell value just when you have created a cell without setting a value.

You have to use row.createCell(2); only when you are setting a value to the cell. Here you don't have a value in it.