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.

Are you getting any error or is it just not displaying the cell content? — usmanhaq
– usmanhaq, Commented Oct 19, 2018 at 7:17
@usmanhaq: No, I'm not getting any error on the console as such. But while I print the cell content, its just not displaying any content. — Kaustubh
– Kaustubh, Commented Oct 19, 2018 at 8:42
I have tested your code on my pc and it is running correctly, maybe the error is not in the excel fetching part, it is in the sending part, and as a note are you checking the cell B2, as the getCellData(1, 1) is referring to B2 — usmanhaq
– usmanhaq, Commented Oct 19, 2018 at 9:07
@usmanhaq: Not sure. I have tried putting data in B2 and still, I get the blank data. Can you share your excel which is working for you, I will try replacing the same and try at my end. — Kaustubh
– Kaustubh, Commented Oct 19, 2018 at 10:08
@usmanhaq: Thanks a lot! I got my mistake. I was not setting the excel file path in my test script which is why its not returning the blank data. Thanks a lot once again! Cheers! — Kaustubh
– Kaustubh, Commented Oct 19, 2018 at 11:12

usmanhaq · Accepted Answer · 2018-10-19 11:38:43Z

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);

}

answered Oct 19, 2018 at 11:38

usmanhaq

1,5771 gold badge9 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting Empty Data while reading from Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related