Data driven testing In Selenium using JXL (Part 1)

In my first post in Guvi blog, I would like to write one of the common scenario which we will encounter while working with Selenium and during framework development, which is getting data from external source (data driven testing).

We can get data from either excel sheet, database tables, XML or even CSV files, but in this post we are going to discuss about getting data from excel sheet. Note that all these operations we are going to do with Selenium Core (JAR) but not with Selenium IDE, since Selenium IDE itself has lot of limitations as shown below.

Selenium IDE Limitations

  • Using Conditional Statements
  • Using External Data
  • Logging
  • Exception handling
  • Reporting 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.

excelflow

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

Karthik KK

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s