Parameterization of Selenium Tests with Microsoft Excel

by seetaram on June 2, 2011

Parameterizing a test from external sources such as Microsoft Excel is always recommended in order to handle large amount of test data.  To read data from Excel, we need APIs which support opening file, reading data, and writing data into Excel.  We should know various classes and methods which support above mentioned operations.  In this post, let us try to figure out which is the API that supports all the activities we need to do during execution of a test.

Jxl.jar is an open source Java API which supports read Excel spreadsheets and to write into Excel spreadsheets.  Below are some of the operations that we can handle with this API.

  1. Read data from Excel spreadsheet
  2. Read and write formulas into spreadsheets
  3. Generate spreadsheets
  4. Supports formatting of font, number, and date
  5. Supports coloring of cells

 

To access the methods and classes provided by this API inside Eclipse we need to add this JAR file to the Java Build Path.  (I have explained steps to add external Jar files to Java Build Path in previous posts)

Download the jxl.jar from “http://jexcelapi.sourceforge.net/

Add the JAR file to Java Build Path

Add import statements to the .java file as below to read from an Excel spreadsheet

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

Create a TestData.xls file as below (Same Excel spreadsheet is created at -  http://tinyurl.com/3wnnpaq ) and save the file as “TestData.xls” (Change the file path in TestData class appropriately)

 

TestMethod param1 param2 param3
testAdvancedSearch http://www.google.co.in/ selftechy,selenium,eclipse #
END

 

Then we need to write appropriate “Java method” to read from an Excel Spreadsheet.

Create a new Java class – -> TestData.java and copy the following code into it

package com.selftechy.parameterization;

/*
*This class is written for Excel Test Data
*Get Method reads data from Excel Spreadsheet
*Author - Seetaram
*/
import java.util.Vector;
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class TestData {
	
	private int testMethodCount;
	private int paramCount;
	private String testDataFilePath;

	private Vector  paramData=new Vector();
	private Vector  testMethod=new Vector();
	
	public TestData(String filepath) {
		testDataFilePath=filepath;
		testMethodCount = 0;
		paramCount = 0;
	}
	
    
	public void getDatafromXL()throws IOException {
		int startrow=1;
		boolean exitvar=false;
		File inputWorkbook = new File(testDataFilePath);
		Workbook w;
		try {
			w = Workbook.getWorkbook(inputWorkbook);
			// Get the first sheet
			Sheet sheet = w.getSheet(0);
			for (int i=startrow;i<sheet.getRows();i++){
				for (int j=0;j<sheet.getColumns();j++){
					Cell cell=sheet.getCell(j,i);
					if (cell.getContents().equalsIgnoreCase("END")){
						exitvar=true;
						break;
					}
					if (j==0){
					this.testMethod.add(cell.getContents());
					testMethodCount++;
					}else {
						if ((cell.getContents().equalsIgnoreCase("##"))||(cell.getContents().equalsIgnoreCase("#")))break;
						this.paramData.add(cell.getContents());
						paramCount++;
					}
				}// end for j loop
				if (exitvar)break;
			}// end for i loop
		} catch (BiffException e) {
			e.printStackTrace();
		}
	}//End parseXLTestCase
	
	public int getTestMethodCount(){
		return testMethodCount;
	}
	
	public int getParamCount(){
		return paramCount;
	}
	
	public Vector <String> getParamData(){
		return paramData;
	}

	public Vector <String> getTestMethods(){
		return testMethod;
	}


}

 

 

Now create JUnit test – -> AdvancedSearch.java and copy the following code into it

package com.selftechy.parameterization;

import java.util.Vector;

import com.thoughtworks.selenium.*;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.openqa.selenium.server.SeleniumServer;

public class AdvancedSearch extends SeleneseTestCase {
	private static SeleniumServer seleniumServer;
	public static Vector  paramData=new Vector();
	public static Vector  testMethod=new Vector();

	TestData td = new TestData("D:\\XLTestData.xls");
	@Before
	public void setUp() throws Exception {
	td.getDatafromXL();
	paramData=td.getParamData();
	testMethod=td.getTestMethods();
	selenium = new DefaultSelenium("localhost", 4444, "*chrome", "http://www.google.co.in/");
        seleniumServer = new SeleniumServer();
        seleniumServer.start();
		selenium.start();
	}

	@Test
	public void testAdvancedSearch() throws Exception {
		selenium.open(paramData.get(0));
		selenium.click("link=Advanced search");
		selenium.waitForPageToLoad("30000");
		selenium.type("as_q", paramData.get(1));
		selenium.select("num", "label=20 results");
		selenium.click("//input[@value='Advanced Search']");
		selenium.waitForPageToLoad("30000");
	}

	@After
	public void tearDown() throws Exception {
		selenium.stop();
		seleniumServer.stop();
	}
}

Run the AdvancedSearch.java as JUnit test.  Methods paramData.get(0) & paramData.get(1) read the data from Excel . We can definitely enhance the code written in TestData.java but I hope this post gives a basic idea of how we can utilize the Excel for parameterization of Selenium tests.

 

{ 68 comments… read them below or add one }

ronen June 14, 2011 at 1:51 pm

Well done, this is useful and clearly detailed/explained.
But how much simpler could it be if we were reading from a comma delimited txt file? Do you have an example of this for Selenium/JUnit?

Thanks,

Reply

seetaram June 19, 2011 at 7:39 am

@ronen

Thanks you,

I will try to post an example for this

Reply

Arun June 15, 2011 at 7:55 am

Hi everyone,

Hi how to handle the alert messages for duplicate data while fetching large amount of data from MS-Excel.

Thanks,
Arun

Reply

seetaram June 19, 2011 at 7:38 am

@Arun,

Could you please elaborate!!

Reply

Bharath June 26, 2011 at 6:12 pm

Good post on Selenium parameterization.
Can you tell us how to update the same excel file with the test results.
For each row, I would require to update Pass/Fail column based on my test results.

-Bharath

Reply

seetaram June 29, 2011 at 5:12 am

Hello Bharath,

Thank you.

Updating the same excel file can be achieved by keeping the results in respective vectors and update them by the end of the execution. But I think if you write the results to an XML that would be even better. Coz from XML you can generate even HTML results or you can import that into Excel and analyze the results

Reply

hitesh August 23, 2011 at 7:21 am

After execution test cases, results get stored in testoutput folder as HTML. Can we import into excel sheet.

Reply

seetaram August 23, 2011 at 11:33 am

Actually the execution creates even the Result in XML format.

XML file can be imported into Excel sheet.

Reply

suresh September 27, 2011 at 7:53 am

Seetaram,
Thanks for your effort and time for this tutorials.

With XLS, unlike QTP (usedrange.rowcount), with selenium – ‘getRow’ (jexcel) not returning the rows which are used (have some data). It returns some random value like – 57. The real data exist only on 30 rows.

Please advise.

Reply

suresh September 27, 2011 at 8:00 am

In fact, while developing my framework, I stuck at this point (getRows – not working properly with Jxl api.

Regards,
Suresh

Reply

suresh September 27, 2011 at 9:41 am

With jxl.API (to access xls from selenium). .getrows() function returns 57 rows when there are actually 13 rows filled. Please advise.

Reply

seetaram September 27, 2011 at 12:54 pm

If you are designing a framework then it is better to go ahead and use Apache’s POI package to access the Excel from Java / Selenium. Use the following links for documentation and the download:

http://poi.apache.org/download.html

http://poi.apache.org/spreadsheet/quick-guide.html

This option you can utilize if you are good in Java. Otherwise, what you can do is use the same code what I have provided and use a delimiter such as # / , / $ etc to identify whether the number of rows / columns have ended with data.

Reply

suresh September 27, 2011 at 2:38 pm

Thanks for your time Seetaram.

So, on the last row of my test data, I have to put the #$ or so. My logic should interpret that, whenever selenium sees the ‘#$’, that is the end of the test data. Am I right?

I will fetch the data from xls (in 2 arrays). I want to use the enums and use it with switch statement (I get test data from these 2 arrays).

public enum Keywords {username,pw,addr,ph;};

Switch (keywordval)

case username:
fill the username editbox.
case pw:
fill the pw editbox.
default:
do something…

Also, I want to know which is the best/easier one – jexcl or poi.

Need your advice.

Reply

seetaram September 28, 2011 at 5:09 am

Yes.. you are right.. take either # or $..or even ##.. or in a row, if you want to skip some column you can specify “XX”..

When compared, both POI and Jxl do the same job. Since, POI is from apache you can expect updates and community support. Using POI is better than Jexcel. But I actually started using JXL first and continued with it.

Reply

suresh September 27, 2011 at 2:44 pm

In QTP, to fill arrays values I can use code like this:
For i=0 to Step 2

Next.

So, here, if cell 1 is filled, then the 3rd cell will be filled with test data.
Is there anything in Java like this, so I can fill all test data in a single array (field names in – 1,3,5 cells) and field values in 2-4-6 cells of the same array. (or should I use 2dimensional array or 2 different arrays)

Any advice?

Regards,
Suresh

Reply

suresh September 28, 2011 at 8:08 am

Thanks Seetaram, for your help

Reply

Prasad November 14, 2011 at 6:22 am

Hi Seetaram,

First of all Thank you for such good Articles.

I tried to run the ab0ve parameterization example but i am getting error
“Test Class not found in selected project”.
I have associated Jexcelapi (jxl.jar) , junit 4.10 and Selenium Jars(Server and Client driver) to the project.

Please let me know any corrects required so that i can run this class.

Reply

seetaram November 14, 2011 at 10:36 am

Thanks Prasad.

It will be helpful, if you publish the code.

The issue you are facing from my initial understanding is – you might be missing @Test annotation and trying to run as JUnit Test

Reply

Prasad November 15, 2011 at 9:12 am

Hi Seetaram,

I am starting selenium server through cmd prompt.

Below are two class :
package com.core;

import java.util.Vector;
import com.thoughtworks.selenium.*;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

@SuppressWarnings(“deprecation”)
public class AdvancedSearch extends SeleneseTestCase {

@SuppressWarnings(“rawtypes”)
public static Vector paramData=new Vector();
@SuppressWarnings(“rawtypes”)
public static Vector testMethod=new Vector();

TestData td = new TestData(“D:\\XLTestData.xls”);
@Before
public void setUp() throws Exception {
td.getDatafromXL();
paramData=td.getParamData();
testMethod=td.getTestMethods();
selenium = new DefaultSelenium(“localhost”, 4444, “*firefox”, “http://www.google.co.in/”);
selenium.start();
}

@Test
public void Search() throws Exception {
selenium.open((String) paramData.get(0));
selenium.click(“link=Advanced search”);
selenium.waitForPageToLoad(“30000″);
selenium.type(“as_q”, (String) paramData.get(1));
selenium.select(“num”, “label=20 results”);
selenium.click(“//input[@value='Advanced Search']“);
selenium.waitForPageToLoad(“30000″);
}

@After
public void tearDown() throws Exception {
selenium.stop();
}
}

TestData.java

package com.core;

import java.util.Vector;

import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class TestData {

private int testMethodCount;
private int paramCount;
private String testDataFilePath;

@SuppressWarnings(“rawtypes”)
private Vector paramData=new Vector();
@SuppressWarnings(“rawtypes”)
private Vector testMethod=new Vector();

public TestData(String filepath) {
testDataFilePath=filepath;
testMethodCount = 0;
paramCount = 0;
}

@SuppressWarnings(“unchecked”)
public void getDatafromXL()throws IOException {
int startrow=1;
boolean exitvar=false;
File inputWorkbook = new File(testDataFilePath);
Workbook w;
try {
w = Workbook.getWorkbook(inputWorkbook);
// Get the first sheet
Sheet sheet = w.getSheet(0);
for (int i=startrow;i<sheet.getRows();i++){
for (int j=0;j<sheet.getColumns();j++){
Cell cell=sheet.getCell(j,i);
if (cell.getContents().equalsIgnoreCase("END")){
exitvar=true;
break;
}
if (j==0){
this.testMethod.add(cell.getContents());
testMethodCount++;
}else {
if ((cell.getContents().equalsIgnoreCase("##"))||(cell.getContents().equalsIgnoreCase("#")))break;
this.paramData.add(cell.getContents());
paramCount++;
}
}// end for j loop
if (exitvar)break;
}// end for i loop
} catch (BiffException e) {
e.printStackTrace();
}
}//End parseXLTestCase

public int getTestMethodCount(){
return testMethodCount;
}

public int getParamCount(){
return paramCount;
}

@SuppressWarnings("unchecked")
public Vector getParamData(){
return paramData;
}

@SuppressWarnings(“unchecked”)
public Vector getTestMethods(){
return testMethod;
}

}

Note:
I have add Jnit4 and Selenium(2.5.0 version).

Reply

babu November 24, 2011 at 5:36 am

hi seetaram.,
can u give on exampl ecode to do a parametrization ,with webdriver,for gmail login..because am very much confused with above code,whether the above code is for rc or webdriver..it will be grateful if u give one example code for login with gmail

Thanks and regards.,
babu.m

Reply

manasadesina January 5, 2012 at 3:12 pm

Hi seetaram,
This is manasa and iam working on selenium c# with webdriver.
can u give on sample code to do a parametrization with webdriver in c#.

Please help me.
Thanks in advance.
D.Manasa

Reply

manasadesina January 5, 2012 at 3:15 pm

Can u post atleast what are the dll files to be imported retrive data from excelsheet.

Reply

seetaram January 10, 2012 at 4:57 am

Hello manasa,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Office = Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Diagnostics;

Regards,
Seetaram

Reply

seetaram January 10, 2012 at 5:15 am

Moreover, I need to know which is the IDE you are using for development of C# code for Test Automation with WebDriver

Reply

jagadeeshwaran February 28, 2012 at 2:56 pm

seetaram,

i have query in Dropdown box

My registration form contain dropdown box for date of birth , for other fields i have used to pass data with MSExcel which is you are posted .

can you please tell me how can pass random value to dropdown box and with MSExcel.
thanks in advance can you please.

regards
jagadeesh

Reply

jagadeeshwaran February 29, 2012 at 8:12 am

hi,

how can i click dynamic text in the platform and store and retrieve data. using seleniumRC junit.
Ex: Gmail >> inbox mail list the first mail always dynamically change.

please help me.

thanks in advance
jagadeesh

Reply

Manivannan March 6, 2012 at 4:09 am

Hi Seetaram,

As Babu mentioned in this thread i am working on Selenium webdriver with TestNG framework can you pls help me in doing parametrization with webdriver java.

Looking forward for your response. Thanks in advance.

Regards
Manivannan.C

Reply

seetaram March 24, 2012 at 9:35 am

you can utilize the annotations provided by TestNG.. or you can write your own class to read the data from Excel / xml data sheet

Reply

Mani March 27, 2012 at 8:54 am

Hi,while I tried to parameterize my Selenium Tests with Microsoft Excel as you explained,
It works fine…if I use more than one row of records for a testmethod,its not getting me the 2nd row values..pls help

Reply

seetaram April 1, 2012 at 3:27 pm

For that purpose you need to tweak the code accordingly..

Reply

Sangamesh March 28, 2012 at 2:28 pm

Hi All,

By using above code i am getting the following error
Error: jxl.read.biff.BiffException: Unable to recognize OLE stream
jxl.read.biff.BiffException: Unable to recognize OLE stream
at jxl.read.biff.CompoundFile.(CompoundFile.java:116)
at jxl.read.biff.File.(File.java:127)
at jxl.Workbook.getWorkbook(Workbook.java:221)
at jxl.Workbook.getWorkbook(Workbook.java:198)
at excelwork.mindtree.Excel.getDatafromXL(Excel.java:42)
at excelwork.mindtree.main.main(main.java:14)

Any body can help me

Regards
Sangamesh Gaded

Reply

seetaram April 1, 2012 at 3:25 pm

I think, you are trying to read .xls but the extension of the file may be .xlsx, just check and try to read again

Reply

sreekumar April 5, 2012 at 2:13 pm

My requirement:
I have few test cases in different files. I want to pass the test data to the testcases through excel.
The above program read the data from excel i.e fine. But how to get the test data of a specific test case in an Arraylist or hashmap and pass this parameters to the function.

Ex:
Loginpage.java
-TC1 Login TC(String U,String P)
– TC2 Logout Tc(String user,String pass)
Inboxpage.java
-TC3 ComposemailTC(String To,String CC,String BCC, intNumberOfusers)
-TC4 ReplymailTC(String To,String CC,String BCC, intNumberOfusers)

Excel sheet having test data related to above 4 TC’s
1. TC1 xyz abc
2. TC2 pqr cde
3. TC3 x@yahoo.com…etc
4.TC4 y@gmail.com…etc

In the above case i want to pass this test data before calling a each function or TC…
i don’t want to read entire data from excel i want to read specific TC data and store in an arraylist pass to method before executing method description…

Reply

seetaram April 16, 2012 at 12:33 pm

you need to prepare the test data sheet in such a way that one of the column should be unique and there you need to mention the test case ID / test method name. Build a logic to read xl depending on the unique column …

Reply

Tarnnum April 30, 2012 at 6:01 pm

Hi,
The above code is not working for me. Please suggest something through which i can retrieve data from excel file using selenium web driver.

Thanks.

Reply

seetaram May 1, 2012 at 6:28 am

Could you please elaborate.. if you can provide the exception details then I can go through it and provide you the solution…

Reply

Muzzamil June 15, 2012 at 8:58 am

plz provide me code for read OpenOffice work book by using Testng/Junit selenium Webdriver in java. and API i need to use? Plz it is Urgent

Thanks in Advance n Nice article man

Reply

seetaram June 29, 2012 at 4:09 pm

For this you need to use the APIs provided by the OpenOffice to get access through Java..

Reply

Shanky June 28, 2012 at 11:41 am

Could you provide the code using testNG instead of junit.

Reply

seetaram June 29, 2012 at 3:57 pm

Go through the blog.. To some extent I have provided the code for TestNG

Reply

Siva September 18, 2012 at 4:02 pm

Hi Seetaram,

First thanks for your post. Its really nice.
Can you share the exact link of your post which uses selenium + TestNG for Parametrization with MS-Excel.

Reply

seetaram October 2, 2012 at 6:40 pm

First configure the TestNG with eclipse, then write the test which uses TestNG annotations. After that use the code which uses the parametrization with Excel so that will fulfill your requirements. Do not expect me to give all the combination of code that you might in need of and you can get only code snippets not the entire code for a project.

Reply

Elangomani July 11, 2012 at 1:30 pm

Hi Seetaram,
I created the project as you mentioned in above. In this I have created TestData class and AdvancedSearch.java JUnit test case as you mentioned. Also I have created XLTestData.xls file in my local D:\\ drive. But in AdvancedSearch.java, in @Test , the open and type function shows error.While pressing F2 on it, It shows in order,For

open – The method open(String) in the type Selenium is not applicable for the arguments (Object)
&For
type – The method type(String, String) in the type Selenium is not applicable for the arguments (String, Object)

and also while executing the console window shows like ” 18:55:27.000 WARN – Failed to start: SocketListener0@0.0.0.0:4444 ”
I already activate the server too. So Kindly Help me.. What should I do to read the excel data.
Kindly I request you all friendz.

Thanks in advance friendz…

Reply

Siva September 18, 2012 at 4:03 pm

Hi Seetaram,

I am also getting the same error. Can you please suggest the solution.

Reply

Ashfaq August 14, 2012 at 8:42 am

Hi SEETARAM, can u share me the code for writing the test results into excel file i.e. pass or fail.
I’m able to read the data from excel file, but not able to write. I appreciate your efforts.

Thanks
Ashfaq

Reply

Rashmi October 24, 2012 at 5:48 am

I am new in Selenium Web driver trying Parametrization in log in screen
I know how to read excel but don’t know how to use that code for parametrisation.
The code is following:
public class readExcel
{

public static String filename=”/home/pankaj/report.xls”;
public static String sheetname=”sheet6″;

public static void main(String[] args)
{
readExcel mn= new readExcel();
mn.read();
}

public void read()
{

HSSFWorkbook workbook=null;
HSSFSheet sheet=null;
int count=0,i=0;
InputStream myxls=null;
try
{

myxls = new FileInputStream(filename);
workbook = new HSSFWorkbook(myxls);

sheet=workbook.getSheet(sheetname);
count=sheet.getLastRowNum();
System.out.println(“Total Number of row”+count);
while(i<=count)
{
int j=0;
HSSFRow row=sheet.getRow(i);
int column_count=row.getLastCellNum();
System.out.println("Total number of column in "+i+" is "+column_count);
while(j> “+name);
j++;
}
i++;
}
}
catch(Exception ex)
{
System.out.println(ex.getMessage());

}
}

}

I want to use the code in the following login screen code

public class Loginparametrisation {
private WebDriver driver;
private String baseUrl;
private StringBuffer verificationErrors = new StringBuffer();
@Before
public void setUp() throws Exception {
driver = new FirefoxDriver();
baseUrl = “http://localhost:81/”;
driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
}

@Test
public void testLoginparametrisation() throws Exception {
driver.get(baseUrl + “/”);
driver.findElement(By.id(“ctl00_loginStatus”)).click();
driver.findElement(By.id(“LoginCtrl_UserName”)).clear();
driver.findElement(By.id(“LoginCtrl_UserName”)).sendKeys(“admin”);
driver.findElement(By.id(“LoginCtrl_Password”)).clear();
driver.findElement(By.id(“LoginCtrl_Password”)).sendKeys(“foretel”);
driver.findElement(By.id(“LoginCtrl_LoginButton”)).click();
driver.findElement(By.id(“ctl00_loginStatus”)).click();
}

@After
public void tearDown() throws Exception {
driver.quit();
String verificationErrorString = verificationErrors.toString();
if (!””.equals(verificationErrorString)) {
fail(verificationErrorString);
}
}

private boolean isElementPresent(By by) {
try {
driver.findElement(by);
return true;
} catch (NoSuchElementException e) {
return false;
}
}
}

Please help me in parametrisation.

Reply

sarmila March 9, 2013 at 8:36 am

Hi Seetaram,
When i run this code,i am getting error com.thoughtworks.selenium.SeleniumException: ERROR: Element num not found.Please tell me the solution.

Reply

seetaram August 26, 2013 at 6:09 am

Try to debug step by step and find the issue

Reply

suman August 7, 2013 at 3:01 pm

Can you please tell me on how to get the test data from an excel sheet using driver(instead of selenium)?

Reply

seetaram August 20, 2013 at 9:24 am

Use Apache POI or JXL to read test data from excel

Reply

Mukesh August 28, 2013 at 8:27 am

Sir i have some question please update all the ans of these

1) How to Parameterized your Junit
2)How do you fail Test case in TestNG
3)Can you Execute test cases parallely in TestNg
4)How to run the test script in multiple browser
5)How to work with radio buttons
6)If you have multiple alert, how do you handle it
7)How to handle google search text.
8)How to handle ajax object.
9)Explain the syntax for finding the row count in dynamic web table.
10)What is DOM concept.
11) How to handle window ID
12)How to handle dynamic web table
13)Why you need to go for a TestNG framework.
14)What is Parameterization and how to achieve it.

pls ans me sir

Reply

seetaram August 30, 2013 at 11:13 am

Lot of them are there in the blog, let me answer others with few more posts because it requires that much of information

Reply

Amit September 18, 2013 at 6:46 am

Hi SeetaRam,
It is a nice post and is very helpfull to all beginners. I have a doubt that I am creating a framework in selenium and I a, using the dataProvider technique for getting data from excel sheet. But I want to ask that do I need to keep a already created java file called “ExcelHandler.java” (which is created for reading excel sheet having all methods to read excel) in my framework or not. Or can I use this dataprovider approach in my framework to read data from excel sheet.
Kindly suggest on this.

Thanks

Reply

seetaram September 18, 2013 at 10:26 am

ExcelHandler way is easier but still you can use either of them

Reply

Amit September 18, 2013 at 2:11 pm

Hi SitaRam,
Thanks for the quick reply. I am using Excel_Handler and below is my code for DataProvider which I have kept in a separate package:-
@DataProvider
public static Object[][] ValidnameProvider(ITestContext context) throws Exception
{
String ExcelPath = context.getCurrentXmlTest().getParameter(“path”);
File file = new File(ExcelPath);
ExcelHandler Excel = new ExcelHandler(file);
//ExcelHandler Excel=new ExcelHandler(new File(Excelpath));
Excel.selectSheet(“Meritnation_Registration”);
String Name = Excel.getColumn(4, true).get(11).getContents();
return new Object[][]{{Name}};
}

Here I am fetching only one row data for one field from the excel. But now my question is how can I get the username/password simultaneously from the excel sheet.

kindly help me here to get data for two fields. Your help would be really appreciated.

Thanks

Reply

Amit September 19, 2013 at 9:26 am

Any help on the above comment……

Reply

seetaram September 20, 2013 at 4:32 am

If you have lot of test cases then DataProvider way is not good. Write your own data handling class. For example write a POJO for the test data, Read the excel file and then load the data into this object, then you can close the excel connection. Now, you have all the data in the object. It has been read and kept in the object so you can use all the data from your spread sheet simultaneously if you want.

Example:

package com.sample.programs;

public class TestData {

private String strUserName;
private String strPassword;

public static final String USERNAME=”USERNAME”;
public static final String PASSWORD=”PASSWORD”;

/**
* @return the strUserName
*/
public String getStrUserName() {
return strUserName;
}
/**
* @param strUserName the strUserName to set
*/
public void setStrUserName(String strUserName) {
this.strUserName = strUserName;
}
/**
* @return the strPassword
*/
public String getStrPassword() {
return strPassword;
}
/**
* @param strPassword the strPassword to set
*/
public void setStrPassword(String strPassword) {
this.strPassword = strPassword;
}

}

Read the excel and then load the data into the object of TestData and then use it inside your test case

Reply

Amit September 20, 2013 at 11:21 am

Hi Sitaram,
Thanks for the reply and clarifying the doubts.
I got the answer of my first question to read the data from excel using dataprovider.
can you tell me what is POJO.
Is it any model like POM or somthing other than that.

And if I have 100 test cases then can I use @DataProvider? If not so what kind of problem it can create.
Kindly clarify my doubts.

Thanks

Reply

seetaram September 22, 2013 at 7:20 am

POJO – Plain Old Java Object. Object which holds the data. Consider the example which I have explained earlier.

Reply

Amit September 24, 2013 at 12:35 pm

Hi SeetaRam.
I have one query.
Hi,
I have created a method to select a dropdown value automatically. Then i am calling this method in main function but its not selecting the value in the drop down. Can you help e here that why its not selecting the value from the drop down. Am I doing anything wrong here.
This is the method for selecting the dropdown value:-
public static void setComboBoxValue(WebElement selectElement, String listName){
List optionlist = selectElement.findElements(By.tagName(“option”));
for(WebElement optionelement : optionlist){
if(optionelement.getText().equalsIgnoreCase(listName)){
optionelement.click();
break;
}
}
}

———————–
Below is the main method where I am calling the above dropdown selection method:-
public static void main(String[] args) throws InterruptedException{
String val = null;
driver = new FirefoxDriver();
driver.get(“any site”);
driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
WebElement drpVal = driver.findElement(By.id(“Type_Loan”));
setComboBoxValue(drpVal, val);
}

But its not selecting the value from the dropdown.

Kindly help

Reply

seetaram September 25, 2013 at 6:09 am

Code to select combobox looks good to me but in main method I doubt whether you are on the proper page. If it throws No Such Element exception then you need to check whether you are on the correct page and then debug

Reply

Amit September 27, 2013 at 10:26 am

Hi SeetaRam,
Am able to select the value from the dropdown using the function.
Code is correct but while calling the method in the test, I was not passing the value to be selected from the dropdown. I did that and its working fine.
Thanks

Reply

seetaram October 1, 2013 at 12:12 pm

Good…

Cheers!!
Seetaram

Reply

deepthi October 10, 2013 at 11:12 am

Hi Seetaram,
i am new to selenium… there is requirement where i need to retrieve website url, username and password details from excel sheet.could you please provide me the code using data driven framework with jxl…

Reply

seetaram October 10, 2013 at 4:19 pm

Can you go through “Selenium” page on this blog, which describes your requirement

Reply

Amit November 22, 2013 at 7:25 am

Hi Seetaram,
I am creating framework using page object model and I am facing a problem in framework.
I decribe as below:-

My page objects are below:-
loginpageObjects.class, homepageObjects.class, editpostObjects.class and updateProfileObjects.class
Here loginpageObjects.class is extending homepageObjects.class and homepageObjects.class is extending updateProfileObjects.class and updateProfileObjects.class is extending Actiondriver.class (which is in separate package hving all the actions defined like clicking button typing in text boxes etc…)
Now in homepageObjects.class the below code is:-
package pageObjects;

//public class homepageObjects extends actionsDriver{
public class homepageObjects extends updateprofileObjects{
public homepageObjects(WebDriver driver) {
super(driver);
}
homepageElements homepageelements_Obj = new homepageElements();
public homepageObjects clickSignoutBtn(){
click(homepageelements_Obj.clickSignOut());
return new homepageObjects(driver);
}
public updateprofileObjects click_UpdateMyProfile_Link(){
//homepageelements_Obj.click_updateMyProfile();
click(homepageelements_Obj.click_updateMyProfile());
return new updateprofileObjects(driver);
}
}

Here “homepageObjects” is extending “updateprofileObjects.class” and hence on clicking “updateprofile_link” methiod, I have returned updateprofileObjects.
On the same home page, there is another link (EditPost link) to goto my posted adds (it’s a new page to which I will land where I can edit my created add) and for this I have created a new pageobject called “editinforObjects.class”.
Now my question is that how can I create a method for clicking above “EditPost” link of Homepage and return this “EditInfoObjects.class” object.
DO I need to extend the class “editinfoObjects.class” in “homepageObjects.class”. But “homepageObjects.class” is already extending “updateprofileObjects.class”. SO kindly let me know how can I achieve this.
It would be really very helpful.

Thanks
Amit

Reply

seetaram November 26, 2013 at 6:19 am

DO I need to extend the class “editinfoObjects.class” in “homepageObjects.class”. But “homepageObjects.class” is already extending “updateprofileObjects.class”.

EditInfoObjects class should extend the HomePageObjects class as the link which opens the page to edit created ads exists in Home Page. You said “HomePageObjects class is already extending UpdateProfileObjects class”. My question is have you defined the objects / methods in EditInfoObjects already in UpdateProfileObjects? if yes, then I dont think you need to create this class again.

Regards,
Seetaram

Reply

Amit November 26, 2013 at 7:10 am

Hi Seetaram,
Thanks for looking into the issue and providing your feedback. No its not.
However I have resolved the issue with the help of your comments.
Thanks a lot for your help…

Reply

Leave a Comment

{ 1 trackback }

Previous post:

Next post:

Do you want to get updates on the recent articles written? Please subscribe to RSS feed or Email