Logo Questions Linux Laravel Mysql Ubuntu Git Menu

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

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)
        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)
        else if(newCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
    catch (FileNotFoundException e) //Couldn't find file
    catch (IOException e) //Couldn't create workbook

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.

like image 570
Zman147 Avatar asked Mar 15 '23 13:03


1 Answers

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

   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
like image 176
Gagravarr Avatar answered Apr 07 '23 08:04