For Loop NullPointerException with Apache POI

Question

First of all, I apologize for my bad english, it's not my primary language. I'm having a problem with my code when it's reading an excel file (xlsx). The first time I used my code it works perfectly, but now I can't use it. The excel content is printed on the console, but next to it there is a NullPointerException that is caused by the for loop marked with the (-->>>). If anyone can help me, I would be very thankful, this code has given me several headaches.

package modleerjava;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class leerExcel {
public void readExcel(String rutaFile,String nombreFile, String sheetName ) 
 throws IOException {
    File file = new File ("C:/Users/Pablo/Desktop/prueba.xlsx"); 
    FileInputStream inputStream = new FileInputStream(file);  
    XSSFWorkbook excelWorkbook ; 
    excelWorkbook = new XSSFWorkbook(inputStream);
    Sheet excelSheet = excelWorkbook.getSheet(sheetName); 
    int filasCount = excelSheet.getLastRowNum()-excelSheet.getFirstRowNum(); 

    for (int i=0; i< filasCount+1 ; i++) { 
        Row filas;
        filas = excelSheet.getRow(i);

 -->>>  for (int j=0 ; j < filas.getLastCellNum(); j++) { 
            System.out.print(filas.getCell(j).getStringCellValue()+"|| ");
        }
        System.out.println();
    }       

}

}

See https://stackoverflow.com/questions/218384/what-is-a-null-pointer-exception-and-how-do-i-fix-it (sorry can't format the link) — StephaneM, Nov 14 '17 at 12:49
Please debug your code. It sounds like your `i` goes out of bounds for row numbers in the sheet, and it's caused by strange computation of `filasCount`. But the only way to make sure is to debug, and we don't have your data. — M. Prokhorov, Nov 14 '17 at 12:51

jmarkmurphy · Accepted Answer · 2017-11-14T14:14:38.337

Your sheet probably has missing rows or cells. The best way to iterate over a spreadsheet in poi is using the for each syntax like this.

package modleerjava;
import java.io.File;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

public class leerExcel {
public void readExcel(String rutaFile,String nombreFile, String sheetName ) 
    throws IOException {
    Workbook wb = WorkbookFactory.create(new File("C:/Users/Pablo/Desktop/prueba.xlsx"));
    Sheet excelSheet = wb.getSheet(sheetName); 

    for (Row filas: excelSheet) { 
        for (Cell cell: filas) {                
            System.out.print(cell.getStringCellValue()+"|| ");
        }
        System.out.println();
    }       

}

}

Many simple questions can be answered by looking at the quick guide on the poi website.

you just give me the perfect solution! thank you very much for your help, greetings — pablomrkite, Nov 14 '17 at 14:19

Arpit Agarwal · Answer 2 · 2017-11-14T14:09:47.207

By seeing the code, I think that NullPointerException will occur if the first row in the excel sheet is blank. So, I have created an Iterator on rows.

Try the below code:

package modleerjava;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class leerExcel {
public void readExcel(String rutaFile,String nombreFile, String sheetName ) 
 throws IOException {
    File file = new File ("C:/Users/Pablo/Desktop/prueba.xlsx"); 
    FileInputStream inputStream = new FileInputStream(file);  
    XSSFWorkbook excelWorkbook ; 
    excelWorkbook = new XSSFWorkbook(inputStream);
    inputStream.close();
    Sheet excelSheet = excelWorkbook.getSheet(sheetName); 
    //int filasCount = excelSheet.getLastRowNum()-excelSheet.getFirstRowNum(); 
    Iterator<Row> row = excelSheet.rowIterator();
    while(row.hasNext()) { 
        Row filas;
        filas = row.next();

        for (int j=0 ; j < filas.getLastCellNum(); j++) { 
            System.out.print(filas.getCell(j).getStringCellValue()+"|| ");
        }
        System.out.println();
    }       

}

 }

i have only one question about your fix , in "sheet.rowIterator()" it marks "sheet" and the suggestion says that i need to make a class named "sheet". — pablomrkite, Nov 14 '17 at 14:08

For Loop NullPointerException with Apache POI

2 Answers2