Data Driven Testing in Selenium using JXL (Part 2)

We have learned what is Data driven testing and how we can use JXL to read data from Excel Sheets in Part 1 of our previous post. But the greatest question of the day is, how to use JXL for data driven testing ?. How can I use this in my Selenium framework which I have? What are the steps which has to be taken care to make my framework a data driven framework?

Well, for this I don’t want to bore you guys with lot of theoretical contents which you can always find some way or other, but I would like to show the real working code.

In order to perform data driven testing, all we need to do is create a reusable library file just for Excel using JXL. The library file need to have following basic functionality in hand, let say

Step 1 : Create a library file with all the below mentioned functionality

1. Open Excel Sheet

2. Read Excel Sheet Row Count

3. Read Cell value from a specified location

4. Create a Dictionary to store Excel Sheet Column name

5. Create a function to read from the Dictionary

The Source code looks like this.

 

Next we are going to create actual test file which is going to perform intended operation, here we are going to perform Gmail login functionality.

Step 2: Create a TestNG Class file to perform Gmail Login

This TestNG class file should include

1. Opening a browser with Gmail

2. Perform User Name and password entry with different combinations of value by reading from Excel sheet

Source Code looks like this

 

Step 3: Create a actual Excel Sheet which holds the data to be supplied in TestNG class in above step

Just create a Excel sheet, which looks something like this.

excel

Step 4 : Try executing the code

Just try executing the code, Note in the above code I have place excel sheet in my D:\ drive, you can place it anywhere in machine and change the same in code.

Your Gmail screen looks like this at the end of test by executing all the above test data’s

gmail

 

Well you can download the full working source code from here

That’s it !!!

Happy coding.

Karthik KK

 

35 comments

  1. Yashwanth N says:

    Hi,

    Thanks for giving some idea on POM and fetching data from excel using Jxl. I have few doubts.
    Whenever i tried to read column by giving “Column name” It is throwing error.
    public static int GetCell(String colName)
    {
    try {
    int value;
    value = ((Integer) dict.get(colName)).intValue();
    return value;
    } catch (NullPointerException e) {
    return (0);

    }
    }

    It always throws NullPointerException and reads 1st column, As it returns “0”. It is able to read first column header because of this. when we try to read next column i.e., 1, this time it fails to read next and returns “0” which in turn points to first column

    • karthik KK says:

      Hy Yashwanth,

      Did you tried to first call the dictionary method to populate all the column names from your excel sheet.

      If not, the code will throw you error as you are getting now.

      Thanks,
      Karthik KK

      • Kabir says:

        Hi,
        Thank You for your video and notes. Can you add code to ExcelSheetDriver to write value in one of the column.

  2. tarikul khan says:

    public class ExcelLib {

    static Workbook workbook = null;
    static Sheet sheet ;
    static Hashtable dict = new Hashtable();

    public ExcelLib(String val) {

    try {
    workbook= Workbook.getWorkbook(new File(val));
    } catch (BiffException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    } catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }

    sheet=workbook.getSheet(“Sheet2”);
    }

    //returns the number of rows
    public static int rowCount(){
    return sheet.getRows();
    }

    //return the cell value by reading cell and column
    public static String readCell(int column, int row){
    return sheet.getCell(column, row).getContents();
    }

    public static void ColumnDist() {
    for(int col=0; col<sheet.getColumns(); col++){
    dict.put(readCell(col, 0), col);
    }

    }

    public static int GetCell(String colName) {

    try {
    int value;
    value = ((Integer) dict.get(colName)).intValue();
    return value;
    } catch (NullPointerException e) {
    return (0);

    }
    }

    }

    ————————————————————————-

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

    ExcelLib gg = new ExcelLib("C:\\Users\\tarek\\Desktop\\Book1.xls");

    System.setProperty("webdriver.chrome.driver", "C:\\Users\\tarek\\workspace\\POM\\chromedriver.exe");
    WebDriver driver = new ChromeDriver();
    driver.navigate().to("http://www.google.com&quot;);

    GoogleHomePageObject page = new GoogleHomePageObject(driver);
    page.searchGoogle(gg.readCell(gg.GetCell("TestWord"), 1));
    Thread.sleep(1000);

    page.linkTest();
    Thread.sleep(5000);
    SeleniumPage sp = new SeleniumPage(driver);
    sp.clickDownload();
    Thread.sleep(5000);
    sp.returnHome();

    //SeleniumPage sp = page.linkTest();
    //sp.clickDownload();
    //Thread.sleep(5000);
    //sp.returnHome();

    }

  3. Salesforce Training says:

    This information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic.

  4. Harini says:

    very nice blogs!!! i have to learning for lot of information for this sites…Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.

  5. bapu23 says:

    Hi Karthik,

    I have one scenario where i need to copy some data from the excel sheet and paste it in the UI can it be done by selenium .Please let me know .

  6. chethan says:

    //Create Column Dictionary to hold all the Column Names
    public static void ColumnDictionary()
    {
    //Iterate through all the columns in the Excel sheet and store the value in Hashtable
    for(int col=0;col < wrksheet.getColumns();col++)
    {
    dict.put(ReadCell(col,0), col);
    }
    }

    I pasted your code and run means i am getting error in for loop so please
    Multiple markers at this line
    – lt cannot be resolved to a
    variable
    – Syntax error on token “;”, ,
    expected
    Give solution for me..

    • Karthik kk says:

      Hi Chethan,

      If you see the video, the code might be very clear to you, sometime, the webpage behave crazy with rendering and you are getting this error, sorry about that.

      Here the the code

      Let me know how it works for you now !!!

      Thanks,
      Karthik KK

  7. Premkumar says:

    Hi Karthik,

    Could you please send me the XMLUtil code. Which will dynamically create testng.xml by taking values from Excel sheet.

  8. Mathan says:

    Hi Karthik,

    Really beautiful explanation about Data driven framework. However i have tried the code given in the example. When i tried running the test, it throws me an error as
    “Cannot instantiate class DataDriver.ReadDataTest
    at org.testng.internal.ObjectFactoryImpl.newInstance(ObjectFactoryImpl.java:40)
    at org.testng.internal.ClassHelper.createInstance1(ClassHelper.java:377)
    at org.testng.internal.ClassHelper.createInstance(ClassHelper.java:290)
    at org.testng.internal.ClassImpl.getDefaultInstance(ClassImpl.java:125)…….

    Please let me know where i need to modify the code. I hope the webdriver initialization need to be updated. But not sure how to do it.

    • Karthik kk says:

      Seems like the class is not instantiated, did you set the class and methods to be accessible as did in video ?

      • Mathan says:

        I didn’t watch video. I have just implemented the code written in the example framework. And then tried running

  9. shil says:

    Hi Karthik,
    I need to read the data from excel by ignoring/skipping a particular column. so plz help me with this query……

  10. Anoop says:

    Hi Karthik,

    Got following error.
    Exception in thread “main” org.openqa.selenium.ElementNotVisibleException: Element is not currently visible and so may not be interacted with
    Command duration or timeout: 14 milliseconds
    Build info: version: ‘2.53.1’, revision: ‘a36b8b1’, time: ‘2016-06-30 17:32:46’
    System info: host: ‘Ishaan’, ip: ‘192.168.1.34’, os.name: ‘Windows 8’, os.arch: ‘amd64’, os.version: ‘6.2’, java.version: ‘1.8.0_91’
    Driver info: org.openqa.selenium.firefox.FirefoxDriver
    Capabilities [{applicationCacheEnabled=true, rotatable=false, handlesAlerts=true, databaseEnabled=true, version=33.0, platform=WINDOWS, nativeEvents=false, acceptSslCerts=true, webStorageEnabled=true, locationContextEnabled=true, browserName=firefox, takesScreenshot=true, javascriptEnabled=true, cssSelectorsEnabled=true}]
    Session ID: ac92d169-61e2-4283-9f75-2862cdd97e68
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at org.openqa.selenium.remote.ErrorHandler.createThrowable(ErrorHandler.java:206)
    at org.openqa.selenium.remote.ErrorHandler.throwIfResponseFailed(ErrorHandler.java:158)
    at org.openqa.selenium.remote.RemoteWebDriver.execute(RemoteWebDriver.java:678)
    at org.openqa.selenium.remote.RemoteWebElement.execute(RemoteWebElement.java:327)
    at org.openqa.selenium.remote.RemoteWebElement.click(RemoteWebElement.java:85)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.openqa.selenium.support.pagefactory.internal.LocatingElementHandler.invoke(LocatingElementHandler.java:51)
    at com.sun.proxy.$Proxy2.click(Unknown Source)
    at or.pom.GoogleHomePageObjects.SearchGoogle(GoogleHomePageObjects.java:32)
    at or.pom.GoogleSearchTest.main(GoogleSearchTest.java:28)
    Caused by: org.openqa.selenium.ElementNotVisibleException: Element is not currently visible and so may not be interacted with
    Build info: version: ‘2.53.1’, revision: ‘a36b8b1’, time: ‘2016-06-30 17:32:46’
    System info: host: ‘Ishaan’, ip: ‘192.168.1.34’, os.name: ‘Windows 8’, os.arch: ‘amd64’, os.version: ‘6.2’, java.version: ‘1.8.0_91’
    Driver info: driver.version: unknown

  11. Ewantha says:

    Hi Karthik,

    I referred your video, and it is a superb one. However I found one bug in your code

    in the following method:

    public static int GetCell(String colName)
    {
    try {
    int value;
    value = ((Integer) dict.get(colName)).intValue();
    return value;
    } catch (NullPointerException e) {
    return (0);

    }
    }

    you are calling “dict.get(colName)” but “dict” does not have any values. hence it always returns 0 from nullpointer exception catch.

    Ideally “ColumnDictionary()” should return the hashtable “dict” in order to use the dictionary in other methods.

    I did slight modifications to your code and now it’s working as expected:

    //Create Column Dictionary to hold all the Column Names
    public Hashtable ColumnDictionary()
    {
    //Iterate through all the columns in the Excel sheet and store the value in Hashtable
    for(int col=0;col<worksheet.getColumns();col++)
    {
    dict.put(ReadCell(col,0), col);
    }
    return dict;
    }

    //Read Column Names
    public int GetCell(String colName)
    {
    Hashtable columns = new Hashtable();
    columns = this.ColumnDictionary();
    int value = 100;

    try {
    value = ((Integer) columns.get(colName)).intValue();
    } catch (NullPointerException e) {
    System.out.println("Unable to find the specified colum name: "+colName);
    }
    return value;
    }

  12. Sowjanya says:

    File not found (404 error) on clicking on ‘here’ link to find the complete coding.Can u please help us finding the script or it will be more helpful if you can share us the whole project

  13. Shailaja says:

    There is error while reading SearchTerm Column from Excelsheet:
    Exception in thread “main” org.openqa.selenium.ElementNotVisibleException: Element is not currently visible and so may not be interacted with
    Command duration or timeout: 17 milliseconds
    Build info: version: ‘2.51.0’, revision: ‘1af067d’, time: ‘2016-02-05 19:11:55’

    i have put data in column number second but it is not able to read the data using SearchTerm name:

    Please let me know what is the problem?
    Code is same as per your video but when i put data in column 0 then it will read but when i move to other column then it shows error.

    And one more part error in your video is:
    SeleniumPageObject selpage=page.clickSelenium();
    after this line we have to click on download link but it will not click on download link. same problem i observe in video that you got error in console for few second and error was:
    Exception in thread “main” java.lang.NullPointerException
    at executeAutomation.SeleniumPageObject.Downloadlink(SeleniumPageObject.java:26)
    at executeAutomation.GoogleSearch.main(GoogleSearch.java:42)

    Please Correct it. As i am stuck here from last day.

  14. uma says:

    Thanks for very good explanation.
    I want to know how to compare the excel data with GUI data .( I have to do an validation between excel data and UI data and provide the status)

  15. Bala says:

    I need the code for login with the multiple user in same application by passing the value through excel sheet one by one continuously by login to logout (Without TestNG).

  16. Milo says:

    Hi Krathik

    Is there a way to iterate through the exel row passing one value into test, then the next value after that test has completed.. Continue until all the values have passed sequentially?

Leave a Reply

Your email address will not be published. Required fields are marked *