I am getting a java.lang.NullPointerException when using Apache POI

Question

When I run my code, I am getting an exception saying that sheet is null even though when I open the sheet in excel, there is clearly values in those cells.

Click for image of excel file

Here is the exception: ERROR StatusLogger Log4j2 could not find a logging implementation. Please add log4j-core to the classpath. Using SimpleLogger to log to the console... Exception in thread "main" java.lang.NullPointerException: Cannot invoke "org.apache.poi.xssf.usermodel.XSSFSheet.getLastRowNum()" because "sheet" is null at excelOperations.readExcel.main(readExcel.java:20) PS C:\Users\noobe\Downloads\VSCODE\ReadExcelFile>

package excelOperations;

import java.io.FileInputStream;
import java.io.FileNotFoundException;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class readExcel {
    public static void main(String[] args) throws FileNotFoundException{

    String excelFilePath = "C://Users//noobe//Downloads//test.xlsx";
    FileInputStream inputStream = new FileInputStream(excelFilePath);

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.getSheetAt(0);
    
    int rows=sheet.getLastRowNum();
    int cols =sheet.getRow(1).getLastCellNum();

    for(int r=0 ; r<rows;r++){
        XSSFRow row = sheet.getRow(r);
        for(int c=0; c<=cols;c++){
            XSSFCell cell=row.getCell(c);
            switch(cell.getCellType()){
                case STRING: System.out.print(cell.getStringCellValue()); break;
                case NUMERIC: System.out.print(cell.getNumericCellValue());break;
                case BOOLEAN: System.out.print(cell.getBooleanCellValue());break;

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

You have only created a new `XSSFWorkbook`, you haven't loaded an existing one (the inputStream is completely ignored), so it won't have any data at all, and that is why you get the error. Change `XSSFWorkbook workbook = new XSSFWorkbook();` to be `XSSFWorkbook workbook = new XSSFWorkbook(inputStream);` I also recommend you do null checks any time you want to use the get methods for sheet, row, column, cell etc — sorifiend, Aug 01 '22 at 02:33
@sorifriend If you put your solution in the "answer" box, then people will be able to vote on it. — Dawood ibn Kareem, Aug 01 '22 at 02:54
Hello, thank you for this. I fixed that part of the code and my program is now printing the first row, but after printing the first row the program then throws another exception and doesnt print the second row Heres the exception: testing | testing | testing |Exception in thread "main" java.lang.NullPointerException: Cannot invoke "org.apache.poi.xssf.usermodel.XSSFCell.getCellType()" because "cell" is null at excelOperations.readExcel.main(readExcel.java:28) it prints the first row "testing | testing | testing |" and then stops. I assume the program does not go to the second row — Cutie Beans, Aug 01 '22 at 04:04
@CutieBeans See my answer below, note the comment at the end, and the link showing how to check for null values, you need to do this check before you even attempt to interact with a cell. You are currently trying to perform an action on a cell that does not exist or has null content. — sorifiend, Aug 01 '22 at 04:04
@CutieBeans The actual issue is that you are using `c<=cols` in your loop, which will try and read the cell in the 4th column (which is empty/null). Change it to `c — sorifiend, Aug 01 '22 at 04:06
alright, I will fix the columns. I checked out the link on null checks and will try the code there too. Ill tell you how it goes — Cutie Beans, Aug 01 '22 at 04:12
@sorifiend Okay, I fixed the columns issue. I also added this null check: if (cell == null || cell.getCellType() == CellType.BLANK) { System.out.println(""); } Would this null check work? — Cutie Beans, Aug 01 '22 at 04:13
@CutieBeans Using `if (cell == null || cell.getCellType() == CellType.BLANK) {System.out.println("");}` will not fix the issue, it only prints, and the cell will still be null. You should instead ignore the cell and move to the next iteration of the loop it by using `continue;`, or wrap the rest of the code in the method inside an else block `if (cell == null || cell.getCellType() == CellType.BLANK) {System.out.println("BLANK CELL");} else{...yourCodeHere...}` — sorifiend, Aug 01 '22 at 04:16
Okay, I added the null check you sent and tested it by putting blank cells in the excel file. It works! It prints BLANK CELL for every null value. Thank you so much. — Cutie Beans, Aug 01 '22 at 04:20

score 1 · Accepted Answer · answered Aug 01 '22 at 03:57

A NullPointerException is thrown when you try and perform an action with a variable or object that has not yet been created. You can find some additional info here: What is a NullPointerException, and how do I fix it?

Specifically in your case you have created a new XSSFWorkbook() but you haven't loaded a file into it (the inputStream you declare is completely ignored), so it won't have any data/content to read from, and that is why you get the error.

The easy solution is to change your existing line:

XSSFWorkbook workbook = new XSSFWorkbook();

And instead use one of the constructors shown in the documentation that accepts the document that you want to load as an input. You are in luck and can pass the FileInputStream directly into the constructor as follows:

XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

I also recommend you do null checks any time you want to use the get methods for sheet, row, cell etc, because typically a blank cell for example will give a null result but it can be easily checked for.

I am getting a java.lang.NullPointerException when using Apache POI

1 Answers1