Data Driven Framework in Selenium WebDriver Using Apache POI

How to work on Data Driven Framework in Selenium Using Apache POI?

Data Driven Framework is one of the most popular Automation Testing Frameworks in the current market. Data Driven automated testing is a method in which the test data set is created in the excel sheet, and is then imported into automation testing tools to feed to the software under test.

Selenium Webdriver is a great tool to automate web-based applications. However, it does not support read and write operations in excel files.

Therefore, we use third party APIs like Apache POI.

What you will learn in this tutorial:

• What is the data driven framework in Selenium WebDriver using excel example
• How to read and write data from an excel sheet in Selenium WebDriver using Apache POI

What is Apache POI?

Apache POI (Poor Obfuscation Implementation) is an API written in Java to support read and write operations – modifying office files. This is the most common API used for Selenium data driven tests.

There are several ways to implement a Data Driven Framework, and each differs in the effort required to develop the framework and maintenance.

Developing a Data Driven framework in Selenium using POI helps reduce maintenance and improve test coverage thus providing a good return on investment.

Recommended reads:

• Most Popular Test Automation Frameworks
• Data Driven Testing in Soap UI Pro

Why do data drive tests?

Often there might be may be a number of data sets that have to be used to test a feature of an application. Now running the same test with different data manually is time-consuming, error prone and a boring task.

Let us understand this scenario with an example.

Suppose we need to test the login/Register/ Any form with multiple input fields with 100 different data sets.

To test this, you have three different approaches:

1) Create 100 scripts one for each dataset and execute each test one by one.
2) Change the data in the script and execute it multiple times.
3) Import the data from the excel sheet and execute the script multiple times with different data.

The first two scenarios are laborious, time-consuming – implying low ROI. Hence, we must follow the third approach.

In the third approach, are implementing the Data Driven framework where all our data resides in an excel sheet where it is imported from and used to test the features of the application.

=> Want to learn more about Data Driven Framework? We have a detailed article that you can check here.

What do we need to do to implement the Data Driven Framework?

In order to follow this approach we must have Eclipse and TestNG properly configured.

Once done, we will look at:

• Various interfaces of Apache POI.
• Integration of Apache POI in the Eclipse.
• Read the Data from the Excel Sheet.
• Send the data to the Excel Sheet.
• Advantages of using Apache POI with Selenium.

Interface in POI

One of the most remarkable features of Apache POI is that it supports read and write operations on both .xls and .xslx files.

Below mentioned are some of the interfaces of the POI.

• XSSFWorkbook: Represents workbook in xlsx file.
• HSSFWorkbook: Represents workbook in xls file.
• XSSFSheet: Represents a sheet in XLSX file.
• HSSFSheet: Represents a sheet in XLS file.
• XSSFRow: Represents a row in a sheet of XLSX file.
• HSSFRow: Represents a row in a sheet of XLS file.
• XSSFCell: Represents a cell in a row of XLSX file.
• HSSFCell: Represents a cell in a row of XLS file.

Fields available in a cell:

• CELL_TYPE_BLANK: Represents a blank cell.
• CELL_TYPE_BOOLEAN: Represents a Boolean cell (true or false).
• CELL_TYPE_ERROR: Represents an error value in a cell.
• CELL_TYPE_FORMULA: Represents a formula result on a cell.
• CELL_TYPE_NUMERIC: Represents numeric data in a cell.
• CELL_TYPE_STRING: Represents string in a cell.

Steps to use Selenium with Apache POI

Let us create an automation script to test the login process of web-based applications.

Here, I have taken LinkedIn as an example.

We import data from an excel sheet and then use it to log into the application and after execution, we write the result in the excel sheet.

We need the following software installed on our system to carry on with the steps to execute the framework:

• Java JDK 1.7+
• Eclipse IDE
• TestNG
• Selenium jars
• Microsoft Office / Open Office

Step #1)

First, we need to configure the Eclipse with Apache POI.

Download jar files for Apache POI.

Step #2)

Unzip the jar file and add the following jars to your project and configure them.

• dom4j-1.6.1.jar
• poi-3.10-FINAL-20140208.jar
• poi-ooxml-3.10-FINAL-20140208.jar
• poi-ooxml-schemas-3.10-FINAL-20140208.jar
• xmlbeans-2.3.0.jar

Step #3)

After configuring the respective jars, create an excel sheet, enter some data in it and save it as TestData.xlsx at your preferred location.

Step #4)

Now let us follow the sample code to read the data from the excel sheet and use it to login to linkedin.com.

package automationFramework;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.concurrent.TimeUnit;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.support.ui.WebDriverWait;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;

/**
 * @author Admin
 *
 */
public class ReadWriteExcel
{
	WebDriver driver;
	WebDriverWait wait;
	HSSFWorkbook workbook;
	HSSFSheet sheet;
	HSSFCell cell;

 @BeforeTest
 public void TestSetup()
{
	// Set the path of the Firefox driver.
	System.setProperty("webdriver.gecko.driver", "C:\\Users\\geckodriver.exe");
	driver = new FirefoxDriver();

	// Enter url.
	driver.get("http://www.linkedin.com/");
	driver.manage().window().maximize();

	wait = new WebDriverWait(driver,30);
	driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
}

 @Test
 public void ReadData() throws IOException
 {
	 // Import excel sheet.
	 File src=new File("C:\\Users\\Admin\\Desktop\\TestData.xls");

	 // Load the file.
	 FileInputStream finput = new FileInputStream(src);

	 // Load he workbook.
	workbook = new HSSFWorkbook(finput);

     // Load the sheet in which data is stored.
	 sheet= workbook.getSheetAt(0);

	 for(int i=1; i<=sheet.getLastRowNum(); i++)
	 {
		 // Import data for Email.
		 cell = sheet.getRow(i).getCell(1);
		 cell.setCellType(Cell.CELL_TYPE_STRING);
		 driver.findElement(By.id("login-email")).sendKeys(cell.getStringCellValue());

		 // Import data for password.
		 cell = sheet.getRow(i).getCell(2);
		 cell.setCellType(Cell.CELL_TYPE_STRING);
		 driver.findElement(By.id("login-password")).sendKeys(cell.getStringCellValue());

        }
  } 

}

Step #5)
Right click on the test case class and click on Run as –> TestNG Test.
Apache POI imports data from the excel sheet and uses it to log into our application. Now that we have seen how to read data from the excel sheet, let’s look at how to write to the sheet.
package automationFramework;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.concurrent.TimeUnit;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.support.ui.WebDriverWait;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;

/**
 * @author Admin
 *
 */
public class ReadWriteExcel
{
	WebDriver driver;
	WebDriverWait wait;
	HSSFWorkbook workbook;
	HSSFSheet sheet;
	HSSFCell cell;

 @BeforeTest
 public void TestSetup()
{
	// Set the path of the Firefox driver.
	System.setProperty("webdriver.gecko.driver", "C:\\Users\\geckodriver.exe");
	driver = new FirefoxDriver();

	// Enter url.
	driver.get("http://www.linkedin.com/");
	driver.manage().window().maximize();

	wait = new WebDriverWait(driver,30);
	driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
}

 @Test
 public void ReadData() throws IOException
 {
	 // Import excel sheet.
	 File src=new File("C:\\Users\\Admin\\Desktop\\TestData.xls");

	 // Load the file.
	 FileInputStream finput = new FileInputStream(src);

	 // Load he workbook.
	workbook = new HSSFWorkbook(finput);

     // Load the sheet in which data is stored.
	 sheet= workbook.getSheetAt(0);

	 for(int i=1; i<=sheet.getLastRowNum(); i++)
	 {
		 // Import data for Email.
		 cell = sheet.getRow(i).getCell(1);
		 cell.setCellType(Cell.CELL_TYPE_STRING);
		 driver.findElement(By.id("login-email")).sendKeys(cell.getStringCellValue());

		 // Import data for password.
		 cell = sheet.getRow(i).getCell(2);
		 cell.setCellType(Cell.CELL_TYPE_STRING);
		 driver.findElement(By.id("login-password")).sendKeys(cell.getStringCellValue());

		 // Write data in the excel.
	   FileOutputStream foutput=new FileOutputStream(src);

		// Specify the message needs to be written.
		String message = "Data Imported Successfully.";

		// Create cell where data needs to be written.
		sheet.getRow(i).createCell(3).setCellValue(message);

		// Specify the file in which data needs to be written.
	    FileOutputStream fileOutput = new FileOutputStream(src);

	    // finally write content
	    workbook.write(fileOutput);

	     // close the file
	    fileOutput.close();

	 }
 }
}

Note: If you encounter any problems during this process, please check the following points.

Make sure all the mentioned jars are added to the project and are properly configured.
Required software is correctly installed.
Proper use of an interface with respect to excel files, like HSSF for .xls and XSSF for .xlsx.
Valid rows and column indexes are used.
The excel file must be closed before execution.
Proper classes used for the excel file like XSSF used for .xlsx files and HSSF used for .xls files.

Advantages of using Data Driven Framework

Improves test coverage.
Re-usability of the code.
Less maintenance.
Faster Execution.
Permits better error handling.

Conclusion
Input/Output from and to a file is a very critical part of the software testing process. Apache POI plays a vital role in making this possible for Selenium Test Automation.
Selenium integration with Apache POI facilitates you to run your script multiple times with different data sets, with all data maintained at a single location. It saves time and maintenance effort on the test script.
About the author: This is a guest post by Vivek, a QA Automation Engineer.
Do you have any queries implementing the data-driven testing framework in Selenium WebDriver using Apache POI? Let us know in the comments section below.

			


				

					

						
Was this helpful?
					
					

						

							
			

				
                			

										
			

				
                			

									
					
				

				

					

						
													

								

								

							
											
					
					

						Submit
						Cancel
					
				

				
Thanks for your feedback!

			

			
 
 
 
 
 
 
 


	
Recommended Reading
	

		

	
Keyword Driven Framework In Selenium With Examples

This Comprehensive Tutorial on Keyword Driven Framework Explains Various Components of the Framework & How to Create One in Selenium: In general, Framework is a set of guidelines, which when followed will give beneficial results. The Keyword-Driven framework is a technique to externalize keywords/actions that are used in the script…

	
Data-driven or Parameterized Testing With Spock Framework

Explore the Ways of Writing Data-driven or Parameterized Tests with the Spock Framework: In this Free Spock Training Tutorial Series, we explored all about Unit Testing in Spock and Test fixtures, Assertions and Reporting in our previous tutorial. In this tutorial, we will try to understand what parameterized tests are…

	
How Data Driven Testing Works (Examples of QTP and Selenium)

In this tutorial, we will discuss Data Driven Testing in an extensive manner.  We include what it is, how it works, pros & cons, etc. Let's get started.  Often, there are a number of data sets that we have to run the same tests on. Also, creating a different test…

	
Selenium Framework Creation and Accessing Test Data from Excel - Selenium Tutorial #21

In the last tutorial, we familiarized you with the basics of test automation Frameworks, their components, and their types. The frameworks illustrated in the previous tutorial were a few of the most popular frameworks used by the testing fraternity. We briefly discussed Module Frameworks, Library architecture-based frameworks, Keyword-driven frameworks, Data-driven…

	
What Is Hybrid Framework In Selenium?

This Informative Tutorial Explains What a Hybrid Framework, the Uses and Components of Selenium Hybrid Driven Framework, and How to Implement it is: What is a Hybrid Framework? Hybrid Driven Framework is a combination of both the Data-Driven and Keyword-Driven framework. Here, the keywords, as well as the test data, are externalized.…

	
Introduction to JUnit Framework and Its Usage in Selenium Script – Selenium Tutorial #11

This tutorial will give an insight into JUnit and its usage in selenium script. This is tutorial #11 in our comprehensive Selenium tutorials series. JUnit is an open-source unit testing tool used to test small/large units of code. To run the JUnit test, you don’t have to create a class…

	
How to Read Or Write Data From Excel Sheet In Selenium Web Driver

In This Tutorial, You will Learn to Read and Write Data from an Excel File in Selenium WebDriver Using FILLO API and SQL statements. Reading or writing data is one of the most commonly used operations, either fetching values from database tables or fetching values from an excel sheet and…

	
Big Data Tutorial For Beginners | What Is Big Data?

This Tutorial Explains all about Big Data Basics. Tutorial Includes Benefits, Challenges, Technologies, and Tools along with Applications of Big Data: In this digital world with technological advancements, we exchange large amounts of data daily like in Terabytes or Petabyte. If we are exchanging that amount of data daily then…