We 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
guvi blogs 