Getting Empty Data while reading from Excel

Question

I am trying to retrieve data from excel in my selenium test scripts. I have one excelData file where I have my code to read and set excel data and I have another class file where I'm fetching data from the excel. When I run my test script, it didn't return any data.

My Excel Code:

import java.io.FileInputStream;

import java.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;

import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;

    public class ExcelUtils {

                private static XSSFSheet ExcelWSheet;

                private static XSSFWorkbook ExcelWBook;

                private static XSSFCell Cell;

                private static XSSFRow row;

                private static MissingCellPolicy xRow;

            //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 getCellData(int RowNum, int ColNum) throws Exception{

                try{

                    Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);

                    String CellData = Cell.getStringCellValue();

                    return CellData;

                    }catch (Exception e){

                        return"";

                    }

            }

            //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, MissingCellPolicy.RETURN_BLANK_AS_NULL);

                    if (Cell == null) {

                        Cell = row.createCell(ColNum);

                        Cell.setCellValue(Result);

                        } else {

                            Cell.setCellValue(Result);

                        }

          // Constant variables Test Data path and Test Data file name

                        FileOutputStream fileOut = new FileOutputStream(Constants.Path_TestData + Constants.File_TestData);

                        ExcelWBook.write(fileOut);

                        fileOut.flush();

                        fileOut.close();

                        }catch(Exception e){

                            throw (e);

                    }

                }

    }

My test Script:

import org.openqa.selenium.By;
import org.testng.annotations.Test;
import config.config;
import utility.ExcelUtils;
    public class ExcelData extends config {

        @Test(priority=0)
         public void SignIn() throws Exception {

            driver.get("https://www.google.com/");

            String SearchData = ExcelUtils.getCellData(1, 1);

            driver.findElement(By.name("q")).sendKeys(SearchData);


        }
}

Here is the Constant file:

public class Constants {


    public static final String Chrome_Driver = "~/Eclipserelatedfiles/chromedriver_linux64/chromedriver";

    public static final String Path_TestData = "~/Documents/eclipse-workspace/Automation/testdata/";

    public static final String File_TestData = "TestData.xlsx";

}

Excel File:

Data
-----------
seleniumhq

I'm not getting what is going wrong as I have the data present in the excel at the particular cell which I'm trying to fetch. Any help / suggestions would be helpful.

Answer 1

I have tested your code using the below console log and it is working..

public static void main(String[] args) throws Exception{


    ExcelUtils.setExcelFile("D:/Book1.xlsx","sheet1");

    String SearchData = ExcelUtils.getCellData(1, 1);

    System.out.println("Data in cell B2  = " + SearchData);

}