In my earlier blog, I have discussed about Data Driven Automation Framework. This is a method in which test data and test script logic are kept separately. Thus a user can easily prepare a test data, modify it and also share it with other person.
Today, in this blog I will discuss about Implementation of Data Driven Automation Framework in Selenium WebDriver. I assume readers of this blog are well aware about Selenium WebDriver automation tool and also what is Data Driven automation framework.
My main focus is to discuss on how to create Test Data in excel sheet, how to import the same data in automation testing tool (Selenium) and further how to feed it to the software under test.
Selenium WebDriver is a popular automation tool used for web-based applications. It uses third party api named Apache POI for performing read and write operations on excel files.
Why Data Driven Testing is required?
Although this I have already discussed in my earlier blog about Data Driven automation framework. In brief, I would say, if you want to execute the same test with different test data than this method is best in itself. It saves both time and effort and improves the efficiency. Also both the positive and negative test cases can be included into a single test.
Tools/Softwares required to Implement Data Driven Testing?
For implementing this testing, following are the prerequisites:
- Java JDK
- TestNG
- Eclipse
- Selenium jars
- Microsoft Office
- Apache POI jars
Apache POI -
Apache POI is the most common JAVA api used for reading and writing test data in excel files. It has many pre-defined methods, classes and interfaces.
Interface in Apache POI -
Apache POI supports both reading and writing data from xls and xlsx files. Following are some of the interfaces of apache POI:
-
XSSFWorkbook: This represents workbook in xlsx file.
-
XSSFSheet: This represents a sheet in XLSX file.
-
XSSFRow: This represents a row in a sheet of XLSX file.
-
XSSFCell: This represents a cell in a row of XLSX file.
-
HSSFWorkbook: This represents workbook in xls file.
-
HSSFSheet: This represents a sheet in XLS file.
-
HSSFRow: This represents a row in a sheet of XLS file.
-
HSSFCell: This represents a cell in a row of XLS file.
Using Selenium with Apache POI -
We’ll now understand how selenium and Apache POI can be used together for reading and writing data into excel files.
Step 1 : Configuring Apache POI in eclipse.
Step 2 : Creating Test Data in excel sheets.

Step 3: Reading test data from excel sheets.
package <packagename>;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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;
public class ReadExcel
{
WebDriver driver;
WebDriverWait wait;
XSSFWorkbook workbook;
XSSFSheet sheet;
XSSFCell cell;
@BeforeTest
public void TestSetup()
{
// Set the path of the Firefox driver.
System.setProperty("webdriver.gecko.driver", "<GeckoDriver Path>");
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("<TestData.xlsx file path>");
// Load the file.
FileInputStream finput = new FileInputStream(src);
// Load the workbook.
workbook = new XSSFWorkbook(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());
}
}
}
Through this program line by line test data will be picked after the test execution starts. Check the results in testNG reports.
Step 4 : Executing the test cases
To execute the test cases, right click on the test case class then select Run as - TestNG Test option.
Before the actual execution, please keep in mind the following points:
1. All the required jars are added and properly configured to eclipse project
2. Installation of all the required softwares
3. Interfaces and classes are properly used i.e.HSSF for .xls and XSSF for .xlsx.
4. Usage of valid row and column index.
5. Excel file is not opened before execution.
By using Data Driven framework in Selenium using POI, helps in reduction of maintenance cost and effort, also test coverage is improved which results in good return on investment.
I hope this blog will save you from the tedious task of executing the same test cases again & again.