Exception: Cannot invoke getLastRowNum()" as "sheet" is null

Question

I'm getting the following exception:

java.lang.RuntimeException: java.lang.NullPointerException: Cannot invoke "org.apache.poi.xssf.usermodel.XSSFSheet.getLastRowNum()" as "sheet" is null

public static Object[][] getTestDataFromExcel(String sheetName) {
    File excelFile = new File("C:\\Users\\abhis\\seleniumHybridFramework\\seleniumHybridFramework\\src\\main\\java\\com\\seleniumHybridFrameworkProject\\qa\\testdata\\seleniumHybridFrameworkProjectTestData.xlsx");
    XSSFWorkbook workbook = null;
    try {
        FileInputStream fisExcel = new FileInputStream(excelFile);
        workbook = new XSSFWorkbook(fisExcel);
    } catch (Throwable e) {
        e.printStackTrace();
    }

    XSSFSheet sheet = workbook.getSheet(sheetName);

    int rows = sheet.getLastRowNum();
    int cols = sheet.getRow(0).getLastCellNum();

    Object[][] data1 = new Object[rows][cols];

    for (int i = 0; i < rows; i++) {
        XSSFRow row = sheet.getRow(i + 1);
        
        for (int j = 0; j < cols; j++) {
            XSSFCell cell = row.getCell(j);
            CellType cellType = cell.getCellType();

            switch (cellType) {
                case STRING:
                    data1[i][j] = cell.getStringCellValue();
                    break;
                case NUMERIC:
                    data1[i][j] = Integer.toString((int) cell.getNumericCellValue());
                    break;
                case BOOLEAN:
                    data1[i][j] = cell.getBooleanCellValue();
                    break;
            }

        }

    }
    return data1;
}

Here in XSSFSheet sheet = workbook.getSheet(sheetName), getSheet is underlined and showing suggestion that this can produce Nullpointer Exception becoz sheet may be is null.

I have poi version 5.2.2 poi-ooxml version 5.2.2 poi-ooxml-schemas 4.1.0

I don't know why it is not recognizing the sheet in a sheet path and workbook is null.

I have tried File excelFile = new File(System.getProperty("user.dir")+"\\src\\main\\java\\com\\seleniumHybridFrameworkProject\\qa\\testdata\\seleniumHybridFrameworkProjectTestData.xlsx").

Please look at it.

Answer 1

The error

java.lang.RuntimeException: java.lang.NullPointerException: Cannot invoke "org.apache.poi.xssf.usermodel.XSSFSheet.getLastRowNum()" as "sheet" is null

tells that sheet is null.

That's possible because XSSFWorkbook.getSheet either returns the XSSFSheet with the name provided or null if it does not exist. So in your code after XSSFSheet sheet = workbook.getSheet(sheetName); sheet might be null if there is not a sheet having name given in sheetName in the workbook.

As always for NullPointerException you have to check whether you got an object and not null.

...
XSSFSheet sheet = workbook.getSheet(sheetName);
if (sheet != null) { 
 int rows = sheet.getLastRowNum();
 //... do what to do with sheet
}
...

Btw.: poi-ooxml-schemas 4.1.0 does not fit to poi-ooxml version 5.2.2 as mixing Apache POI versions is not supported and will lead to unexpected errors. See faq-N10204.

See Apache POI - Component Overview. The poi-ooxml of version 5.2.2 needs either poi-ooxml-lite of same version 5.2.2 or poi-ooxml-full of same version 5.2.2 but nothing of version 4.1.0.