How to fix Null pointer exception while iterating through cells in xls file

Question

I have been trying to resolve the Null pointer exception. I have tried try and catch but it is not working.
Debug shows the line where the iterator starts, and it is referring to some null value, throwing null pointer exception but I couldn't find it.

I have tried all but couldn't solve it.
The error is

java.lang.NullPointerException at ReadDataUsingIterator.main(ReadDataUsingIterator.java:37)

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import java.util.Iterator;
import java.util.NoSuchElementException;
import java.util.logging.Logger;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadDataUsingIterator {

    public  static void main(String[] args) {
        try {
    File file = new File("C:\\Users\\Ashma\\Desktop\\Automation\\testdata.xlsx");

    try {
        @SuppressWarnings("unused")
        FileInputStream fis = new FileInputStream(file);
    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    XSSFWorkbook workbook = new XSSFWorkbook();

    String sheetName = ("user_testdata");
    Sheet sheet = workbook.getSheet(sheetName);
    //XSSFSheet sheet = workbook.getSheetAt(0);
    //iterate thru rows one by one

    java.util.Iterator<Row> rowIterator = sheet.iterator();
    while (rowIterator.hasNext()) 
    {
        Row row = rowIterator.next();
        java.util.Iterator<Cell> cellIterator = row.cellIterator();

        while(cellIterator.hasNext()) 
        {
            Cell cell = cellIterator.next();
            //check cell type and format
            if(cell !=null) {
            switch(cell.getCellType()) 
            {
            case Cell.CELL_TYPE_NUMERIC:
                System.out.print(cell.getNumericCellValue() + "t");
            break;
            case Cell.CELL_TYPE_STRING:
                System.out.print(cell.getStringCellValue() + "t");
            break;
            }

            }}
    System.out.println(" ");
    } 

    } 
        catch (NullPointerException ne)
        {
        ne.printStackTrace();
    }
    }
}

Possible duplicate of [What is a NullPointerException, and how do I fix it?](https://stackoverflow.com/questions/218384/what-is-a-nullpointerexception-and-how-do-i-fix-it) — MWB, Jan 12 '19 at 12:59

score 0 · Answer 1 · answered Jan 12 '19 at 13:03

you are not reading the Excel file. You do not pass the FileInputStream to anything, you just create an empty workbook. This empty workbook has no sheet called user_testdata, hence your sheet object is null. You should create your workbook from the file, something like

Workbook workbook = WorkbookFactory.create(new File("C:\\Users\\Ashma\\Desktop\\Automation\\testdata.xlsx"));

How to fix Null pointer exception while iterating through cells in xls file

1 Answers1