Data Driven Testing in Selenium WebDriver Using jxl

Data Driven Testing through Selenium WebDriver using jxl
Prerequisite 

1- Download jxl jar file and add it in to path
2- Junit jar file
3- Selenium-server-standalone-2.x.jar
Add these three jar file in build path and to read more about adding jar file read my last post Configuring Selenium Webdriver in Eclipse with Testng plugin installation .




In general when we say Data Driven then only thing that should come in to mind is that input is going to be read from some xls file, xml,csv or some other table oriented file and might be output would also be written in xls,xml or csx file. All read data from various files are stored in variables and finally used by scripts to run the test cases.
Data Driven testing is mainly divided in two part
1- Reading data and storing in to variable
2- Using data stored in variable in to some generic script.

Reading Data and storing in to variable
Since Webdriver don’t have structure like other automation tools like QTP to have its own Data table to store data to run tests in Data Driven framework. So we normally two jar file(Binaries) JXL(Java Excel API) and Apache POI to make Data Driven test framework for  WebDriver.
I am using JXL binary in this example. so for reading data from xls file we need to follow these step

1- Opening Excel file , so to open excel file we would use these two line. I have created one excel file r.xls and now we would write code to reach to this file and to open this excel sheet.
import java.io.FileInputStream;
import jxl.Workbook;

FileInputStream fi = new FileInputStream(“C:\Users\kaushal\Desktop\r.xls”);

Workbook w = Workbook.getWorkbook(fi);

In above code
FileInputStream obtains input bytes from a file in a file system

2- Opening worksheet

import jxl.Sheet;

Sheet sh = w.getSheet(0); or w.getSheet(Sheetnumber)

Here 0 as argument states about firest sheet, if we want to read second sheet then we may use 1 in place of 0

3- Reading data
code used is 

String variable1 = s.getCell(column, row).getContents();

These are 3 steps that is used in reading data from excel sheet

In my example or code I am going to write code to test login to Gmail for various user and Username and Password is save in excel sheet and we will keep reading this data by using loop and we would store these data in two variable username and password



excelsheet-sample1-300x62

42 comments

  • Hi,
    When i run this above code for Data Driven Testing in WebDriver Using jxl ,i am getting NullPointerException in this line for (int row = 1; row <= s.getRows(); row++).Please tell me how to solve this.

    • could you share more detail regarding to this.
      Normally Null pointer exception appears when we have initialize a variable and haven’t address it with some value or might be the case of wrong referencing of variable so please paste you code as it is, so that i could find the root of this through debugging.

  • even i am getting the same error

    • Check your code once again and make sure
      you have written this line
      Sheet sh = w.getSheet(0);
      might be you would have written above line like this

      Sheet sh = w.getSheet(“0”);
      In this case you would get Nullpointer exception
      or check where you have just referenced some variable wrongly.

      So please share you trace log. So that we could reach on the conclusion why this null pointer exception is coming.

  • even i am getting same error ” java.lang.NullPointerException
    at script.Data.searchGoogle(Data.java:25)
    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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

    line no 25 is ” for(int row=1; row<=s.getRows(); row++) "

    • sakthi vignesh

      Try this Dude…

      @Test
      public void f() throws Throwable, IOException {
      FileInputStream fi = new FileInputStream(“D:\\Selenium.xls”);
      WebDriver driver = new FirefoxDriver();
      Workbook wb = Workbook.getWorkbook(fi);
      Sheet sh = wb.getSheet(0);

      System.out.println(sh.getRows());
      System.out.println(sh.getColumns());
      for(int i=0;i<=sh.getRows();i++)
      {

      System.out.println(sh.getCell(0, i).getContents());
      String username = sh.getCell(0,i).getContents();
      String password = sh.getCell(1, i).getContents();
      driver.get("http://www.gmail.com&quot;);
      driver.findElement(By.name("Email")).sendKeys(username);
      driver.findElement(By.name("Passwd")).sendKeys(password);
      }

      }

  • Enter the following code out of the loop and then try..
    FileInputStream fi = new FileInputStream(“C:\Users\kaushal\Desktop\r.xls”);
    Workbook w = Workbook.getWorkbook(fi);
    s = w.getSheet(0);

  • Worthy data … i am not sure on why you have not used @Parameterized feature in Junit to repeat the test for multiple data.I have a plan for data driven frame work. I need your suggestion on achieving the same.
    I have multiple number of tests.The first sheet in xl contains the tests and a column execute says ..weather the tests need to be executed or not.and the next sheets contains the test data for the test cases in first sheet. How can i code such that junit can only consider the Tests that has Yes mode in test execution column.

  • Hi
    When i run this code i am getting this Error
    java.lang.ArrayIndexOutOfBoundsException: 2
    at jxl.read.biff.SheetImpl.getCell(SheetImpl.java:297)
    at testcases.Add_Emp.searchGoogle(Add_Emp.java:32)
    Please tell me how to solve this.

  • @Navneet:use the following code:
    for(int row=1; row <s.getRows();row++)

  • it is fine ……but if value are correct so we will navigate inside and if it is wrong so error message will display so what should we do to handle it …………

  • hi , very useful article!!! could please tell me , how to read the data from xml file instead of csv file.

  • Hi ,
    Its a very nice post and I learned so many things from this blog. Now I am trying to use TestNG dataProvider. I am using Excel_Handler (a extra class) for reading excel sheets and below is my code for DataProvider which I have kept in a separate package:-
    @DataProvider
    public static Object[][] ValidnameProvider(ITestContext context) throws Exception
    {
    String ExcelPath = context.getCurrentXmlTest().getParameter(“path”);
    File file = new File(ExcelPath);
    ExcelHandler Excel = new ExcelHandler(file);
    //ExcelHandler Excel=new ExcelHandler(new File(Excelpath));
    Excel.selectSheet(“Registration”);
    String Name = Excel.getColumn(4, true).get(11).getContents();
    return new Object[][]{{Name}};
    }

    Here I am fetching only one row data for one field from the excel. But now my question is how can I get the username/password simultaneously from the excel sheet.

    kindly help me here to get data for two fields. Your help would be really appreciated.

    Thanks

  • Hey Your code is perfect …!
    It works nicely
    Thankyu so much Dwarika Dhish Mishra …… 🙂

  • Hi,
    Thanks for the reply. I am using excel sheet already. My question is if i have 100 test cases in framework then would it be better to go with Dataprovider to get data from excel sheet or do i need to use any other approach. Is Data provide a best approach with large test cases.

    Thanks
    Amit

  • Hi,
    Thanks for your reply. Actually I was trying to implement a data driven test here fetching data from excel.
    OK will try as per your comments and if facing any issue will let you know…
    Again thanks for your help….

    Thanks

  • Hi,
    I have created a method to select a dropdown value automatically. Then i am calling this method in main function but its not selecting the value in the drop down. Can you help e here that why its not selecting the value from the drop down. Am I doing anything wrong here.
    This is the method for selecting the dropdown value:-
    public static void setComboBoxValue(WebElement selectElement, String listName){
    List optionlist = selectElement.findElements(By.tagName(“option”));
    for(WebElement optionelement : optionlist){
    if(optionelement.getText().equalsIgnoreCase(listName)){
    optionelement.click();
    break;
    }
    }
    }

    ———————–
    Below is the main method where I am calling the above dropdown selection method:-
    public static void main(String[] args) throws InterruptedException{
    String val = null;
    driver = new FirefoxDriver();
    driver.get(“any site”);
    driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
    WebElement drpVal = driver.findElement(By.id(“Type_Loan”));
    setComboBoxValue(drpVal, val);
    }

    But its not selecting the value from the dropdown.

    Kindly help

    • go to this link might be it will help you in understanding how to handle dropdown

      http://abodeqa.com/2013/02/28/how-to-handle-drop-down-in-webdriver/

      • Hi,
        THanks for your feedback. I have already done this for selecting a dropdown and I am able to select a value from dropdown using this.
        But here I want to create a generic method for selecting a value from dropdown. Now weherever I need to select a value from dropdown, I will call this method instead of writting the code again and again.
        But the problem is that I am unable to select the value from drop down when I am calling that method.
        Kindly suggest where is the mistake I am doing or if my code is wrong.

        Thanks

        • Ok so your function could be like this

          public void select(String name_of_Option)
          {
          new Select(driver.findElement(By.xpath/id/name/class)).slectByVisibleText(name_of_Option);
          }

          One more thing fetch all the values of option of dropdown and pass the value to the select function and by doing so you would be able to select the option as per your need

          Hope fully this will help you!!

  • How to mention the end of the row(of the excel i.e. gmail credentials last row ) in your above mentioned code.Please tell me.

  • Hi,
    Its a nice blog and I have learn lot of things from this bog. Its a really a good blog. I am creating framework and I have a doubt in framework.

    My page objects are below:-
    loginpageObjects.class, homepageObjects.class, editpostObjects.class and updateProfileObjects.class
    Here loginpageObjects.class is extending homepageObjects.class and homepageObjects.class is extending updateProfileObjects.class and updateProfileObjects.class is extending Actiondriver.class (which is in separate package hving all the actions defined like clicking button typing in text boxes etc…)
    Now in homepageObjects.class the below code is:-
    package pageObjects;

    //public class homepageObjects extends actionsDriver{
    public class homepageObjects extends updateprofileObjects{
    public homepageObjects(WebDriver driver) {
    super(driver);
    }
    homepageElements homepageelements_Obj = new homepageElements();
    public homepageObjects clickSignoutBtn(){
    click(homepageelements_Obj.clickSignOut());
    return new homepageObjects(driver);
    }
    public updateprofileObjects click_UpdateMyProfile_Link(){
    //homepageelements_Obj.click_updateMyProfile();
    click(homepageelements_Obj.click_updateMyProfile());
    return new updateprofileObjects(driver);
    }
    }

    Here “homepageObjects” is extending “updateprofileObjects.class” and hence on clicking “updateprofile_link” methiod, I have returned updateprofileObjects.
    On the same home page, there is another link (EditPost link) to goto my posted adds (it’s a new page to which I will land where I can edit my created add) and for this I have created a new pageobject called “editinforObjects.class”.
    Now my question is that how can I create a method for clicking above “EditPost” link of Homepage and return this “EditInfoObjects.class” object.
    DO I need to extend the class “editinfoObjects.class” in “homepageObjects.class”. But “homepageObjects.class” is already extending “updateprofileObjects.class”. SO kindly let me know how can I achieve this.
    It would be really very helpful.

    Thanks
    Amit

  • Hi Amit,

    Thanks for your post related to this Article.
    I am executing this code on my mac but I got the below error Trace.

    java.io.FileNotFoundException: UsersanilkumarvuppalaDocumentsr.xlsx (No such file or directory)
    at java.io.FileInputStream.open(Native Method)
    at java.io.FileInputStream.(FileInputStream.java:120)
    at java.io.FileInputStream.(FileInputStream.java:79)
    at Data.searchGoogle(Data.java:24)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

    Can you please help me to resolve this issue ASAP.

    Thanks,
    Padma Vuppala

    • Hi

      JXL api only supports .xls extension and as i can see you are using .xlsx. Due to this it is throwing IO exception in ur case.
      So please change the extension to .xls by going to Save as –> take 97-2003 option and it will help you to save ur .xlsx extension to .xls and then try once again.

      Hopefully after this code will work for you.

  • Thanks a lot Dwarika for the post this is very useful for me and it solved a major headache for me, thanks buddy

  • Hi dwarika, can you provide the code for reading data using POI api’s. The reason being that some of the methods used here are not present as part of the POI list of methods.

    Thanks
    Modi

  • Hi,
    It is very helpful blog.Thnks Dawarka.

    But what if we have drop down list and we have to make dataDriven framework. how to achieve that
    please guide on that.

    thanks
    ravi

  • package NewsPortal;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.util.concurrent.TimeUnit;
    import jxl.JXLException;
    import jxl.Sheet;
    import jxl.Workbook;
    import org.junit.After;
    import org.junit.Before;
    import org.junit.Test;
    import org.openqa.selenium.By;
    import org.openqa.selenium.WebDriver;
    import org.openqa.selenium.firefox.FirefoxDriver;

    public class contact_excel {
    private WebDriver driver;
    private String baseUrl;
    Sheet s;
    @Before
    public void setup(){
    WebDriver driver = new FirefoxDriver();
    baseUrl = “http://localhost/”;
    driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
    }
    @Test
    public void contactExcel() throws JXLException, IOException{
    driver.get(baseUrl + “/wordpress/”);
    driver.findElement(By.xpath(“.//*[@id=’menu-item-125′]/a”)).click();
    FileInputStream fi = new FileInputStream(“G:\\contact_us.xls”);
    Workbook W = Workbook.getWorkbook(fi);
    s = W.getSheet(0);
    for(int row = 0;row < s.getRows();row++)
    {

    String Name = s.getCell(0,row).getContents();
    System.out.println("Name" +Name);
    driver.findElement(By.id("cntctfrm_contact_name")).sendKeys(Name);

    String Emailaddress = s.getCell(1,row).getContents();
    System.out.println("Emailaddress" +Emailaddress);
    driver.findElement(By.id("cntctfrm_contact_email")).sendKeys(Emailaddress);

    String Subject = s.getCell(2,row).getContents();
    System.out.println("Subject"+ Subject);
    driver.findElement(By.id("cntctfrm_contact_subject")).sendKeys(Subject);

    String Message= s.getCell(2,row).getContents();
    System.out.println("Message"+ Message);
    driver.findElement(By.id("cntctfrm_contact_message")).sendKeys(Message);
    }}
    @After

    public void quitcontact(){
    driver.quit();
    }
    }

    Not is not running itis showing a white window only.

  • hi me 2 also getting same error while running the code and have same error like java null exception error,could you assist me for getting websites and checking their inner sites using data driven from excel

  • Hi,

    i need to get the all the values from this table on ui, and validate against excel colomn ?

  • hi Dwarika Dhish Mishra,

    I wriring a script for my project but I got stuck in this issue can u please help me in it

    1. I am testing a website where I have to give inputs from excel sheet but that inputs I have to give on different different pages so if I am using for loop than that will not work with my requirement
    . I have loginpage where I have to give username password and clicking on login button and on second page I hane to give credit card details again from ecxelsheet and on third page I have to give some other information using excel so for this what should I do

  • 1.what is data driven?funtion of work?one example program?
    2.what is hybrid framework?funtion of work?one example program?

    can u pls send me the answer for this question.pls send me the answer to my mail id

    Thanks
    Ananth.k

  • How we test multiple set data

    example :: i ve 2 usersname nd password
    how can we do that

  • How to Read/Write”.ods” file (OpenOffice.org spreadsheet),Using Selenium Webdriver ,and what are the maven dependencies for open office…pls help

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.