How to read an Excel file in java with DataProvider

I have trouble reading the information in an Excel file to introduce and test with selenium. But the result always NullPoInterException. I tried to change the link in the Excel file. Here’s the 2-part code.

public class TestProviderII {

public static void main(String[] args) {
    String url = ".\\src\\test\\resources\\dataProvider.xlsx";
    testData(url, "SheetOne");
}

public static void testData(String url, String sheet) {
    ExcelUtils excelUtils = new ExcelUtils(url, sheet);
    int rowCount = excelUtils.getRowCount();
    int colCount = excelUtils.getColCount();
    for (int i = 1; i < rowCount; i++) {
        for (int j = 0; j < colCount; j++) {
            String cellData = excelUtils.getCellDataString(i, j);
            System.out.println("Data : " + cellData);
        }
    }
  }
}


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

public class ExcelUtils {

static XSSFWorkbook workbook;
static XSSFSheet sheet;

public ExcelUtils(String workBook, String sheetName) {
    try {
        workbook = new XSSFWorkbook(workBook);
        sheet = workbook.getSheet(sheetName);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

public static int getRowCount() {
    int rowCount = 0;
    try {
        rowCount = sheet.getPhysicalNumberOfRows();
        System.out.println("No of Row : " + rowCount);
    } catch (Exception e) {
        System.out.println(e.getMessage());
        System.out.println(e.getCause());
        e.printStackTrace();
    }
    return rowCount;
}

public static int getColCount() {
    int colCount = 0;
    try {
        colCount = sheet.getRow(0).getPhysicalNumberOfCells();
        System.out.println("No of Column" + colCount);
    } catch (Exception e) {
        System.out.println(e.getMessage());
        System.out.println(e.getCause());
        e.printStackTrace();
    }
    return colCount;
}

public static String getCellDataString(int rowNum, int colNum) {
    String cellData = "";
    try {
        cellData = sheet.getRow(rowNum).getCell(colNum).getStringCellValue();
        System.out.println("Data Cell : " + cellData);
    } catch (Exception e) {
        System.out.println(e.getMessage());
        System.out.println(e.getCause());
        e.printStackTrace();
    }
    return cellData;
}

public static Double getCellDataNumber(int rowNum, int colNum) {
    double cellData = 0;
    try {
        cellData = sheet.getRow(rowNum).getCell(colNum).getNumericCellValue();
        System.out.println(cellData);
    } catch (Exception e) {
        System.out.println(e.getMessage());
        System.out.println(e.getCause());
        e.printStackTrace();
    }
    return cellData;
  }

}

And the result is NullPointerException

null
null
null
null
java.lang.NullPointerException
at test.ExcelUtils.getRowCount(ExcelUtils.java:23)

at test.TestProviderII.testData(TestProviderII.java:12)
at test.TestProviderII.main(TestProviderII.java:7)
java.lang.NullPointerException
at test.ExcelUtils.getColCount(ExcelUtils.java:36)
at test.TestProviderII.testData(TestProviderII.java:13)
at test.TestProviderII.main(TestProviderII.java:7)

Process finished with exit code 0

Why the result nullPointerException despite I did exactly what said? And I changed the Excel file link and the result is the same.

String url = ".\\src\\test\\resources\\dataProvider.xlsx";
String url = "src\\test\\resources\\dataProvider.xlsx";
String url = "C:\\Selenuim\\demoApplication\\src\\test\\resources\\dataProvider.xlsx

Change below line:

testData(url, "SheetOne");

to:

testData(url, "Sheet1");

Assuming the sheet looks like below in your excel file:

Also ensure, there is some data in your excel sheet. I ran your code by just changing to Sheet1. And it worked see below:

Excel sheet:

Console Output:

No of Column3
Data Cell : d
Data : d
Data Cell : e
Data : e
Data Cell : f
Data : f