Data Driven Testing In Selenium WebDriver, is one piece of task which we find little bit tedious but In this post, we will be talking about reading the data from excel file which further can be used within selenium script.
But before moving ahead we need to look in to this list of pre-requisite because this is first step in this data driven testing in selenium webdriver.
Prerequisite
1- Download jxl jar file and add it in to path
2- Junit jar file
3- Selenium-server-standalone-2.x.jar | We need to download the latest version of Selenium Java zip
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
package com.testng;
import java.io.FileInputStream;
//import java.io.IOException;
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 Data{
Sheet s;
WebDriver driver;
@Before
public void setUp()
{
driver = new FirefoxDriver();
}
@Test
public void searchGoogle() throws Exception
{
FileInputStream fi = new FileInputStream("C:\Users\kaushal\Desktop\r.xls");
Workbook w = Workbook.getWorkbook(fi);
s = w.getSheet(0);
for(int row=1; row <=s.getRows();row++)
{
String username = s.getCell(0, row).getContents();
System.out.println("Username "+username);
driver.get("http://www.gmail.com");
driver.findElement(By.name("Email")).sendKeys(username);
String password= s.getCell(1, row).getContents();
System.out.println("Password "+password);
driver.findElement(By.name("Passwd")).sendKeys(password);
driver.findElement(By.name("signIn")).click();
}
}
@After
public void tearDown()
{
driver.close();
driver.quit();
}
}
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++) "
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");
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.
http://docs.oracle.com/javase/tutorial/jaxp/dom/readingXML.html
this link would help u in reading data from xml
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
I got the answer………..:)
Hey Your code is perfect …!
It works nicely
Thankyu so much Dwarika Dhish Mishra …… 🙂
HI,
Thanks for your reply. If i have 100 test cases in my framework, then is it fine to use DataProvider for getting data from excel sheet or I need to use any other method.
Kindly suggest.
THanks
Both could be used, But i would suggest Excel sheet to use
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
Why you are running behind Data provider just use the variables and take the value of each cell from Excel by using poi or Jxl binaries and put it directly to the required field
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 Venkatesh,
What you want to do here exactly….let me know..may be I can help you in this…
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
Sure i am working on it and very soon will post all about poi api
Be in touch
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
if you will go through this post probably you would be able to understand how to work with data set.. If still you need help then we are always there to help you out.
How to Read/Write”.ods” file (OpenOffice.org spreadsheet),Using Selenium Webdriver ,and what are the maven dependencies for open office…pls help