Site Loader
Auckland, New Zealand
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. excel 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 gmail   Well you can download the full working source code from here That’s it !!! Happy coding. Karthik KK  

Post Author: Karthik kk

38 Replies to “Data Driven Testing in Selenium using JXL (Part 2)”

  1. 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

    1. 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

      1. Hi,
        Thank You for your video and notes. Can you add code to ExcelSheetDriver to write value in one of the column.

  2. 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&quot;);

    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();

    }

  3. 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.

  4. 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.

  5. 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 .

  6. //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..

    1. 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

  7. Hi Karthik,

    Could you please send me the XMLUtil code. Which will dynamically create testng.xml by taking values from Excel sheet.

  8. 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.

    1. Seems like the class is not instantiated, did you set the class and methods to be accessible as did in video ?

      1. I didn’t watch video. I have just implemented the code written in the example framework. And then tried running

  9. Hi Karthik,
    I need to read the data from excel by ignoring/skipping a particular column. so plz help me with this query……

  10. 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

  11. 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;
    }

  12. 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

  13. 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.

  14. 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)

  15. 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).

  16. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *