Here is my utils class:

package utility;
import .io.FileInputStream;
import .io.FileOutputStream;
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 ExcelUtils {
    private static XSSFSheet ExcelWSheet;
    private static XSSFWorkbook ExcelWBook;
    private static XSSFCell cell;
    private static XSSFRow row;
    //This method is to set the File path and to open the Excel 
    file, Pass Excel Path and Sheetname as Arguments to this method
    public static void setExcelFile(String Path, String SheetName) throws Exception {
        try {
            // Open the Excel file
            FileInputStream ExcelFile = new FileInputStream(Path);
            // Access the required test data sheet
            ExcelWBook = new XSSFWorkbook(ExcelFile);
            ExcelWSheet = ExcelWBook.getSheet(SheetName);
        } catch (Exception e) {
            throw (e);
    //This method is to read the test data from the Excel cell, in this we are passing parameters as Row num and Col num
    public static String (int RowNum, int ColNum) throws Exception {
        try {
            String cellData = "";
            cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
            cellData = cell.getStringCellValue();
            return cellData;
        } catch (Exception e) {
            return "undefined";
    //This method is to write in the Excel cell, Row num and Col num are the parameters
    public static void setCellData(String Result, int RowNum, int ColNum) throws Exception {
        try {
            row = ExcelWSheet.getRow(RowNum);
            cell = row.getCell(ColNum, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
            if (cell == null) {
                cell = row.createCell(ColNum);
            } else {
            // Constant variables Test Data path and Test Data file name
            FileOutputStream fileOut = new FileOutputStream(Constants.Path_TestData + Constants.File_TestData);
        } catch (Exception e) {
            throw (e);

Here is the script where I’m the getCellData to get the from Excel:

String cellData = ExcelUtils.getCellData(1, 1);
System.out.println("CellData :" + cellData);

Here is the excel file format:

TestCaseName | Username | Password 
TC_01        | TestData |          


   Exception in thread "main" java.lang.NullPointerException
    at utility.ExcelUtils.getCellData(
    at testScripts.Category_creation.main(

Here is the excel I’am using. Not able to fetch data from excel file. I’m using Page object framework, hence the excel utils file contains only the code and in fetching the data in testScript by passing row and column number.

Source link


Please enter your comment!
Please enter your name here