CODEWITHSUNDEEP
javaexcelapachenullpointerexceptionapache-poi
Zman147
Zman147

Reputation: 71

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)
{
    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.

Upvotes: 7

Views: 22290

Answers (3)

rejdrouin
rejdrouin

Reputation: 89

Using "myExcelRow == null" does not help for me. As soon as the code hits an empty cell, I get the java.lang.NullPointerException It occurs even in rows and columns that are not last of the worksheet.

I am working with one specific workbook. Just changing the format of the cell, for example: font color, avoids the NullPointerException.

Upvotes: 0

Pratyush
Pratyush

Reputation: 31

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);
}

Upvotes: 3

Gagravarr
Gagravarr

Reputation: 48326

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

Upvotes: 7

Related Questions