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.
/*
* Author : Karthik KK
* Description: Reusable Library file to perform Excel related operations
* Date : 01/28/2012
*/
package DataDriver;
import java.io.File;
import java.io.IOException;
import java.util.Hashtable;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class ExcelSheetDriver {
static Sheet wrksheet;
static Workbook wrkbook =null;
static Hashtable dict= new Hashtable();
//Create a Constructor
public ExcelSheetDriver(String ExcelSheetPath) throws BiffException, IOException
{
//Initialize
wrkbook = Workbook.getWorkbook(new File(ExcelSheetPath));
//For Demo purpose the excel sheet path is hardcoded, but not recommended :)
wrksheet = wrkbook.getSheet("Sheet1");
}
//Returns the Number of Rows
public static int RowCount()
{
return wrksheet.getRows();
}
//Returns the Cell value by taking row and Column values as argument
public static String ReadCell(int column,int row)
{
return wrksheet.getCell(column,row).getContents();
}
//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);
}
}
//Read Column Names
public static int GetCell(String colName)
{
try {
int value;
value = ((Integer) dict.get(colName)).intValue();
return value;
} catch (NullPointerException e) {
return (0);
}
}
}
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
package DataDriver;
/*
* Author : Karthik KK
* Description: To perform Gmail Login using Data driven approach
* Date : 01/28/2012
*/
import java.io.IOException;
import jxl.read.biff.BiffException;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.ie.InternetExplorerDriver;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;
public class ReadDataTest {
//Global initialization of Variables
static ExcelSheetDriver xlsUtil;
WebDriver driver = new InternetExplorerDriver();
//Constructor to initialze Excel for Data source
public ReadDataTest() throws BiffException, IOException
{
//Let's assume we have only one Excel File which holds all Testcases. weird :) Just for Demo !!!
xlsUtil = new ExcelSheetDriver("D:\\Data.xls");
//Load the Excel Sheet Col in to Dictionary for Further use in our Test cases.
xlsUtil.ColumnDictionary();
}
@BeforeTest
public void EnvironmentalSetup()
{
driver.get("http://www.gmail.com");
}
@Test
/*
* Author : Karthik KK
* Description : To Perform login operation in Gmail
*/
public void GmailLoginPage() throws InterruptedException {
//Create a for loop.. for iterate through our Excel sheet for all the test cases.
for(int rowCnt = 1;rowCnt < xlsUtil.RowCount();rowCnt++)
{
//Enter User Name by reading data from Excel
WebElement userName = driver.findElement(By.name("Email"));
userName.clear();
userName.sendKeys(xlsUtil.ReadCell(xlsUtil.GetCell("EmailUserName"), rowCnt));
//Enter Password
WebElement password = driver.findElement(By.name("Passwd"));
password.clear();
password.sendKeys(xlsUtil.ReadCell(xlsUtil.GetCell("Emailpassword"), rowCnt));
//Click on the Sign In Button
WebElement signin = driver.findElement(By.name("signIn"));
signin.click();
//Sleep for some time,so that we can see things in action @ Screen :)
Thread.sleep(2000);
}
}
}
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.
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
Well you can download the full working source code from
here
That’s it !!!
Happy coding.
Karthik KK
Thanks for sharing the useful information on Testing.
i need flex testing tools name
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
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
Hi,
Thank You for your video and notes. Can you add code to ExcelSheetDriver to write value in one of the column.
Data driven model with example snapshot is really good and its really helpful to understand.
Link for the working source code not going
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");
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();
}
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.
Hi,
I am not able to get Blank username and Blank Password from the above code. Could you Please help me in this.
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.
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 .
You mean retrieving data from Excel to Specflow ?
If so, then here is the video https://www.youtube.com/watch?v=5vYUYozBaqM&list=PL6tu16kXT9Pp3wrsaYyNRnK1QkvVv6qdI&index=16
Thanks,
//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..
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
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); } }Let me know how it works for you now !!!
Thanks,
Karthik KK
Hi karthik
Can you give your email id please ..
regards
Chethan
Hi Karthik,
Could you please send me the XMLUtil code. Which will dynamically create testng.xml by taking values from Excel sheet.
Hi Karthik can u plz share me the code Regarding fetching the data from webtable and to store in the Excel.
Hi
Can you please provide me the way to do the same using C#.
Check out here https://www.youtube.com/watch?v=dMai7HW-J4U
I could not run url through excel using data driven loop why? in test complete
Very Nice Blog.
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.
Seems like the class is not instantiated, did you set the class and methods to be accessible as did in video ?
I didn’t watch video. I have just implemented the code written in the example framework. And then tried running
Hi Karthik,
I need to read the data from excel by ignoring/skipping a particular column. so plz help me with this query……
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
Hi Karthik
i need the code for three test class catch read and write the result in the same excel sheet using testng framework please send the code
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;
}
Thanks Ewantha ,
These changes work for me .
Cheers !
hi karthik,
Can you please tell me how to copy data from one excel sheet and paste it into another excel sheet.
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
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.
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)
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).
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?
this is not working bro
“Well you can download the full working source code from here”
Thanks for sharing the information and explaining very much