Data driven testing In Selenium using JXL

We saw all these post and many friends who are reading these posts found really interesting and said they are really simple and useful.
But in all the post we just saw the very basic stuff on Selenium, but once our test becomes complex, we need to deal with some real codes. Many of my friends who are dealing with manual QA see coding as the weird part in automation, all they want is Click record button, perform some operation, stop recording and replay the same. That’s fine for Selenium IDE, but moving forward we need to make our code more robust to withstand complex applications.
There are lots of limitations in using Selenium IDE, some are

• Using Conditional Statements
• Using External Data
• Logging
• Exception handling
• Reporting etc

In this topic we are going to deal with one of the most important yet very useful stuff for automation in Selenium with is “Data Driven Testing”. We can drive Selenium testing using data sources like XML, Excel sheets, Database Tables etc.
But wait, what if I just need to store some of the application properties like Object Name, Application Window name, global properties etc, do I need to use the data sources you are talking about ?
Well, the answer is Yes or No, depending upon the complexity of your application, if your application has very few objects and their properties to be identified are very minimal, you can use “Property file” instead.
Here is the sample code to read data from Sample file in JAVA.

public void ReadProperty() throws IOException {
   System.out.println("In Read Property....");
   //Create Property Object
   Properties p = new Properties();
            //Load the Property file available in same package
   p.load(getClass().getResourceAsStream("objects.properties"));
            //Give the propery name to access
   String propName = p.getProperty("name");
            //Output the property value
   System.out.println(propName);
  }

Now if your application becomes very complex and you rely each and every data to be driven from external data sources, then you SHOULD use data sources like Excel, Database table etc.
Here we are going to talk about driving data from Excel sheet using JXL (JAVA EXCEL API). You can read more about JXL from the site, but for now, let’s consider JXL is an API to read and write data from Excel Sheets. We are going to use JXL’s Excel as our Dataprovider to read data from external data source. There are some other API available to read and write data from excel sheet like POI-HSSF and POI-XSSF , but we are going to deal only with JXL here.
Before getting started with JXL coding, let’s get to know some basic steps of how to read data from Excel sheet, below is the diagrammatic representation.

Well, here is how the code to start working with JXL.
Lets go with the diagrammatic representation.
Step 1

import java.io.File;
import java.util.Date;
import jxl.*; 
 
Workbook workbook = Workbook.getWorkbook(new File("C:\\data.xls"));

Step 2
Open worksheet

Sheet sheet = workbook.getSheet("sheet1");

We can also give the index of sheet as

Sheet sheet = workbook.getSheet(0);

Step 3
Read the data from Excel Cell

String value = sheets.getCell(Column, Row).getContents();

Here Column is the column number and row is the row number from the cell which you are interested to read data.
Step 4
Now close the worksheet

workbook.close();

That’s it !!!

Data Driven Testing in Selenium using JXL (Part 2)

Rating: 4.7/5 (12 votes cast)

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 !!!