Apache POI .getCell() error, java.lang.NullPointerException Cannot Read Cell if blank

Question

I have been having trouble recently with Apache POI's .getCell() method for Excel files. If I try to implement Cell newCell = sheet.getRow(rowNumber).getCell(columnNumber) or something similar, I always get the error

Exception in thread "main" java.lang.NullPointerException
at ExcelWriter.getWorkbook(ExcelWriter.java:80)
at Gui2.<init>(Gui2.java:93)
at Main.main(Main.java:24)

which points to the line I am implementing the .getCell() method in if the cell is null. I have code that is supposed to determine if the cell is null and then print something if that is true, but it seems as though the cell being blank causes an error with the program. This only happens in some cases however, and I have found that if I create a sheet and make a for loop to create a new cell in every row, I can read a blank cell and determine it is blank. However, if I then type a value into any cell in that sheet, and then try to read a different null cell, I get the error message again. I do not understand why I get this error only when I try to read null cells, but I need to loop through them in my program. Here is a snippet of my code and what is causing the error.

public void getWorkbook(String fileName)
{
    try 
    {
        existingFile = new FileInputStream(new File(fileName));
        workbook = new XSSFWorkbook(existingFile);
        Sheet newSheet = workbook.getSheet("Test3");
        //createSheet("Test3", workbook);

        Cell newCell = newSheet.getRow(1).getCell(1);
        if(newCell == null)
        {
            System.out.println("Null");
        }
        else if(newCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
        {
            System.out.println("Number");
        }
        //System.out.println(newCell.getStringCellValue());
    } 
    catch (FileNotFoundException e) //Couldn't find file
    {
        e.printStackTrace();
    } 
    catch (IOException e) //Couldn't create workbook
    {
        e.printStackTrace();
    }
}

Just to be clear, the cell B2, which is referenced in the code, is blank. However, A1 is not, but when I read A1 the system prints "Number" as it is supposed to.

Solution 1

As explained in the Apache POI Javadocs, Sheet.getRow(num) can return null, if the row has never been used and therefore hasn't been written to the file

As covered in the Apache POI docs on iterating over rows and cells, if you want to loop over all rows, then fetch one specific column, you need to do something like:

// Decide which rows to process
int rowStart = Math.min(15, sheet.getFirstRowNum());
int rowEnd = Math.max(1400, sheet.getLastRowNum());

for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
   Row r = sheet.getRow(rowNum);
   if (r == null) {
      // No entries in this row
      // Handle empty
      continue;
   }

   int cn = 1;
   Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
   if (c == null) {
      // The spreadsheet is empty in this cell
   } else {
      // Do something useful with the cell's contents
   }
}

Solution 2

I have tried the below code.. worked for me. If it throws NullPointerException, I have created a new cell along with the row at the same location of the sheet.

try {
    row = sheet.getRow(rowNum);
    if(row == null) {
        row = sheet.createRow(rowNum);
    }
    Cell cell = row.getCell(colNum); 
        
    if(cell == null) {
        cell=row.createCell(colNum);
    }
    cell.setCellValue(String);
             
} catch (Exception e) {
    System.out.println(e);
}