What causes 'NullPointerException' in this piece of code, reading Excel cells?

Question

Hi Intellects,

I'm using Apache POI for reading Excel. I've been asked to store the cells into MySQL 5.1. While I'm reading Excel cells, I've been thrown NullPointerException. This is my code snippet :

  int rows = sheet.getPhysicalNumberOfRows();

  for(int r = 1; r<rows; r++) {
            HSSFRow row = sheet.getRow(r);
            int cols = row.getLastCellNum();

            for(int c=0; c < cols; c++) {
                HSSFCell cell = row.getCell(c, Row.CREATE_NULL_AS_BLANK);

                switch(cell.getColumnIndex())    {

                   case 0:
                       ....
                       ....

                   case 1:
                       ....
                       ....


                   case ...:
                       ....
                       ....

                }
            }
  }

  // Here comes the code to insert into DB

If anyone can identify where it arises, post asap. If I left some logic you need just ask me...

Answer 1

I guess cell may be null, but you should use debugger to find out the exception place.

Answer 2

From the code given, either sheet, row or cell will be null. The exception stack trace itself should tell you exactly what line is causing the problem.

First thoughts are that you're getting the physical number of rows in the sheet but getRow() gets you a logical row. You may want to look into using getFirstRowNum() and getLastRowNum() which appear to return logical rows.

But that just my thoughts. More information is needed before I can say for sure.

---

To get a stack trace given your exception npe, use:

npe.printStackTrace();

or:

StringWriter sw = new StringWriter();
PrintWriter pw = new PrintWriter(sw, true);
npe.printStackTrace(pw);
pw.flush();
sw.flush();
// sw.toString(); to get a string for putting into a text box or something else.

Answer 3

Is it possible that your array offset is off by one (for the row loop)?

for(int r = 1; r<rows; r++) {
  HSSFRow row = sheet.getRow(r);

instead of starting at offset 1, start at 0. This makes more sense (and matches your column loop).

In this case the result of HSSFRow row = sheet.getRow(r); could be null (which incidentally you should check for).

Edit#1: For example:

  if (sheet == null) {
        return;
  }

  int rows = sheet.getPhysicalNumberOfRows();
  for(int r = 1; r<rows; r++) {
        HSSFRow row = sheet.getRow(r);
        if (row != null) {
             int cols = row.getLastCellNum();

             for(int c=0; c < cols; c++) {
                 HSSFCell cell = row.getCell(c, Row.CREATE_NULL_AS_BLANK);

                 if (cell != null) {
                    switch(cell.getColumnIndex())    {
                         case 0:
                         case 1:
                         case ...:

                     }
                 }
             }
      }
 }

If you look at the JavaDoc this is important

public int getPhysicalNumberOfRows()  {
  return _rows.size();
}

Returns the number of physically defined rows (NOT the number of rows in the sheet)

Edit#2: For the sake of further debugging please place the following try/catch around your for loop.

try {
     // Your for loop code here
} catch (Exception x) {
     x.printStackTrace();
}

Please paste a comment to this answer or update your original answer with the result of this output.

Answer 4

I don't explain your NPE, but propose you an other way to iterate over workbook, have you tried this ?

for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
    final Sheet sheet = workbook.getSheetAt(i);
    final Iterator<Row> rows = sheet.rowIterator();
    if (rows.hasNext()) {
       final Row header = rows.next();
       ....
    }
}