{"id":496,"date":"2014-08-18T12:36:13","date_gmt":"2014-08-18T12:36:13","guid":{"rendered":"http:\/\/guviblogs.wordpress.com\/?p=496"},"modified":"2025-12-23T15:15:55","modified_gmt":"2025-12-23T09:45:55","slug":"data-driven-testing-in-selenium-using-jxl-part-2","status":"publish","type":"post","link":"https:\/\/www.guvi.in\/blog\/data-driven-testing-in-selenium-using-jxl-part-2\/","title":{"rendered":"Data Driven Selenium Automation Testing using JXL (Part 2)"},"content":{"rendered":"\n

We learned what is Data-driven Selenium Automation testing and how we can use JXL to read data from Excel Sheets in Part 1 of our previous post. But the question that still needs to be addressed is, how to use JXL for data-driven testing? How to use this in the Selenium framework that already exists? What are the steps which have to be taken care of to make the framework a data-driven framework?<\/p>\n\n\n\n

Well, for this let us not walk through the typical theoretical content that you can always find some way or other. But let’s do it with the real working code.<\/p>\n\n\n\n

To perform data-driven selenium automation testing, all we need to do is create a reusable library file just for Excel using JXL. The library file needs to have the following basic functionality in hand.<\/p>\n\n\n\n

How to use JXL for Selenium Automation Testing?<\/strong><\/h2>\n\n\n
\n
\"Services<\/figure><\/div>\n\n\n

Step 1<\/strong><\/h3>\n\n\n\n

Create a library file with all the below-mentioned functionality<\/strong><\/p>\n\n\n\n

1. Open Excel Sheet<\/p>\n\n\n\n

2. Read Excel Sheet Row Count<\/p>\n\n\n\n

3. Read Cell value from a specified location<\/p>\n\n\n\n

4. Create a Dictionary to store Excel Sheet Column name<\/p>\n\n\n\n

5. Create a function to read from the Dictionary<\/p>\n\n\n\n

The Source code for selenium automation testing looks like this.<\/p>\n\n\n\n

\/*\n * Author : Karthik KK\n * Description: Reusable Library file to perform Excel related operations<\/pre>\n\n\n\n
 * Date : 01\/28\/2012\n *\/\npackage DataDriver;\nimport java.io.File;\nimport java.io.IOException;\nimport java.util.Hashtable;\n\nimport jxl.Cell;\nimport jxl.Sheet;\nimport jxl.Workbook;\nimport jxl.read.biff.BiffException;\n\npublic class ExcelSheetDriver {\n\n\tstatic Sheet wrksheet;\n\tstatic Workbook wrkbook =null;\n\tstatic Hashtable dict= new Hashtable();\n\t\/\/Create a Constructor\n\tpublic ExcelSheetDriver(String ExcelSheetPath) throws BiffException, IOException\n\t{\n\t\t\/\/Initialize\n\t\twrkbook = Workbook.getWorkbook(new File(ExcelSheetPath));\n\t\t\/\/For Demo purpose the excel sheet path is hardcoded, but not recommended :)\n\t\twrksheet = wrkbook.getSheet(\"Sheet1\");\n\t}\n\n\t\/\/Returns the Number of Rows\n\tpublic static int RowCount()\n\t{\n\t\treturn wrksheet.getRows();\n\t}\n\n\t\/\/Returns the Cell value by taking row and Column values as argument\n\tpublic static String ReadCell(int column,int row)\n\t{\n\t\treturn wrksheet.getCell(column,row).getContents();\n\t}\n\n\t\/\/Create Column Dictionary to hold all the Column Names\n\tpublic static void ColumnDictionary()\n\t{\n\t\t\/\/Iterate through all the columns in the Excel sheet and store the value in Hashtable\n\t\tfor(int col=0;col < wrksheet.getColumns();col++)\n\t\t{\n\t\t\tdict.put(ReadCell(col,0), col);\n\t\t}\n\t}\n\n\t\/\/Read Column Names\n\tpublic static int GetCell(String colName)\n\t{\n\t\ttry {\n\t\t\tint value;\n\t\t\tvalue = ((Integer) dict.get(colName)).intValue();\n\t\t\treturn value;\n\t\t} catch (NullPointerException e) {\n\t\t\treturn (0);\n\n\t\t}\n\t}\n\n}<\/pre>\n\n\n\n
Next, we are going to create an actual test file that is going to perform the intended operation, here we are going to perform Gmail login functionality.<\/p>\n\n\n\n
Step 2<\/strong><\/h3>\n\n\n\n
Create a TestNG Class file to perform Gmail Login<\/strong><\/p>\n\n\n\n
This TestNG class file should include<\/p>\n\n\n\n
1. Opening a browser with Gmail<\/p>\n\n\n\n
2. Perform User Name and password entry with different combinations of value by reading from the Excel sheet<\/p>\n\n\n\n
Then, check the source code. It should look like this.<\/p>\n\n\n\n
package DataDriver;\n\n\/*\n * Author : Karthik KK\n * Description: To perform Gmail Login using Data driven approach\n * Date : 01\/28\/2012\n *\/\n\nimport java.io.IOException;\nimport jxl.read.biff.BiffException;\n\nimport org.openqa.selenium.By;\nimport org.openqa.selenium.WebDriver;\nimport org.openqa.selenium.WebElement;\nimport org.openqa.selenium.ie.InternetExplorerDriver;\nimport org.testng.annotations.BeforeTest;\nimport org.testng.annotations.Test;\n\npublic class ReadDataTest {\n\n  \/\/Global initialization of Variables\n  static ExcelSheetDriver xlsUtil;\n  WebDriver driver = new InternetExplorerDriver();\n\n  \/\/Constructor to initialze Excel for Data source\n  public ReadDataTest() throws BiffException, IOException\n  {\n\t\t\/\/Let's assume we have only one Excel File which holds all Testcases. weird :) Just for Demo !!!\n\t    xlsUtil = new ExcelSheetDriver(\"D:\\Data.xls\");\n\t    \/\/Load the Excel Sheet Col in to Dictionary for Further use in our Test cases.\n\t    xlsUtil.ColumnDictionary();\n  }\n\n  @BeforeTest\n  public void EnvironmentalSetup()\n  {\n\t  driver.get(\"http:\/\/www.gmail.com\");\n  }\n\n  @Test\n  \/*\n   * Author : Karthik KK\n   * Description : To Perform login operation in Gmail\n   *\/\n  public void GmailLoginPage() throws InterruptedException {\n\n\t  \/\/Create a for loop.. for iterate through our Excel sheet for all the test cases.\n\t  for(int rowCnt = 1;rowCnt < xlsUtil.RowCount();rowCnt++)\n\t  {\n\n\t\t  \/\/Enter User Name by reading data from Excel\n\t\t  WebElement userName = driver.findElement(By.name(\"Email\"));\n\t\t  userName.clear();\n\t\t  userName.sendKeys(xlsUtil.ReadCell(xlsUtil.GetCell(\"EmailUserName\"), rowCnt));\n\n\t\t  \/\/Enter Password\n\t\t  WebElement password = driver.findElement(By.name(\"Passwd\"));\n\t\t  password.clear();\n\t\t  password.sendKeys(xlsUtil.ReadCell(xlsUtil.GetCell(\"Emailpassword\"), rowCnt));\n\n\t\t  \/\/Click on the Sign In Button\n\t\t  WebElement signin = driver.findElement(By.name(\"signIn\"));\n\t\t  signin.click();\n\n\t\t  \/\/Sleep for some time,so that we can see things in action @ Screen :)\n\t\t  Thread.sleep(2000);\n\t  }\n  }\n\n}<\/pre>\n\n\n\n
Step 3<\/strong><\/h3>\n\n\n\n
Create an actual Excel Sheet which holds the data to be supplied in the TestNG class in the above step<\/strong><\/p>\n\n\n\n
Just create an Excel sheet, which looks something like this.<\/p>\n\n\n
\n
\"excel<\/a><\/figure><\/div>\n\n\n
Step 4<\/strong><\/h3>\n\n\n\n
Try executing the code<\/strong><\/p>\n\n\n\n
So, it’s time now that you try executing the code. Note in the above code we have placed an excel sheet in D: drive. You can place it anywhere in the machine and change the same in code.<\/p>\n\n\n\n
Then, your Gmail screen will look like this at the end of the test by executing all the above test data.<\/p>\n\n\n\n
Wish to fast-forward your Automation Testing skills<\/a>? Explore here.<\/a><\/p>\n\n\n\n
Isn’t it intriguing? If you look forward to taking your Selenium Automation Testing knowledge one step ahead, then you can get started with this Selenium Automation Testing Course. It is a complete course on Automation Testing that will assist you to get into professional, quality Product & Process development with hands-on software automation skills.  So, why not try this course? <\/p>\n\n\n\n
<\/span>\"selenium
\n
Automation Testing Using Selenium<\/mark><\/a><\/p>\n<\/div><\/div>\n\n\n\n
Get started here and accelerate your automation career NOW! <\/p>\n\n\n\n
If you still find confused on whether Automation Testing using Selenium is what you wish to do, then you can just leave your contact and let our Experts get back to you for further clarifications. <\/p>\n\n\n
Submit a form.<\/a><\/div>","protected":false},"excerpt":{"rendered":"
We learned what is Data-driven Selenium Automation testing and how we can use JXL to read data from Excel Sheets in Part 1 of our previous post. But the question that still needs to be addressed is, how to use JXL for data-driven testing? How to use this in the Selenium framework that already exists? […]<\/p>\n","protected":false},"author":1,"featured_media":4998,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[706,40],"tags":[],"views":"8455","authorinfo":{"name":"GUVI Geek","url":"https:\/\/www.guvi.in\/blog\/author\/admin\/"},"thumbnailURL":"https:\/\/www.guvi.in\/blog\/wp-content\/uploads\/2014\/08\/Blog-6-300x157.png","jetpack_featured_media_url":"https:\/\/www.guvi.in\/blog\/wp-content\/uploads\/2014\/08\/Blog-6.png","_links":{"self":[{"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/posts\/496"}],"collection":[{"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/comments?post=496"}],"version-history":[{"count":22,"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/posts\/496\/revisions"}],"predecessor-version":[{"id":97523,"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/posts\/496\/revisions\/97523"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/media\/4998"}],"wp:attachment":[{"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/media?parent=496"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/categories?post=496"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.guvi.in\/blog\/wp-json\/wp\/v2\/tags?post=496"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}