Data Driven Testing is a commonly used method of software testing where the test data is externally stored in spreadsheets or in table format. Data Driven Testing enables the testing team to execute their functional tests against a huge set of test data being supplied from a precise table and outputs the test result in the same or different table. It is also known as parameterized testing or table driven testing.

Data Driven Testing usually comes into the picture when the below problems starts rising:

• The test data is scattered across multiple test scripts 
• For different test environment, different test data has to be maintained inside the test script
• Test data maintenance involving lot of human resources with lot of time and manual efforts

For the testing team that runs their tests against a huge set of data and on multiple environments, it is important to adopt data driven testing methodology. This would reduce the efforts and cost of adding and maintaining existing test data and further, changing the test data in intervals in case of change in business process logic. Additionally, data driven testing can help in analysing data that is more important for a particular test environment. This analysis defines the clear image of what feature to be tested against the particular data.

Data Driven Testing in Selenium

Before moving towards Data Driven Testing in selenium, let’s quickly understand what exactly a test automation framework is.

Test Automation Framework is a set of rules or tools that are being incorporated for automating the test cases. It is not just a single tool but a collection of tools and processes that runs parallely to empower test automation. Automation Framework is an integral combination of functions like test data, libraries, dependencies and resulable modules.

The set of rules in the automation framework defines the coding standards, test data handling techniques, ways to store and display test results and object repositories.

Types of automation frameworks in Selenium:

1. Data Driven Test Framework : It is used to segregate the test data from the test scripts. The same test script can further be executed sequentially or parallely against a huge set of test data instead of single hard coded test data.
2.  Keyword Driven Test Framework : It is a scripting approach that utilizes tables or spreadsheets to maintain keywords for the application under test. The keywords define the action set that are required to perform each step. 
3. Hybrid Test Framework : It is a combination of both Data Driven and Keyword Driven Testing Framework where the keywords as well as test data are maintained externally, probably in the form of tables or spreadsheets. This framework can involve the participation of non-technical professionals as well, as they can have a look around the keywords and test data to get a basic understanding of the flow of the test script. 
   

What is Data Driven Framework in Selenium?

Generally, when an application is tested manually, different scenarios are performed against different test data. The test data being used for manual testing is usually stored in some files like excel file, csv file, text file or may be in the database. 

Similarly, for running automated tests against a huge set of test data we can have different repositories/database/file to maintain the test data. The selenium data driven framework is used to drive test scripts with the externalized data. The externalized data is probably from data sheets like xls, xlsx, and csv files.

The Data Driven Framework in Selenium is a commonly adopted technique to segregate the ‘test data set’ from the actual ‘test case’. Since the test data is maintained externally, the test data can be modified anytime without making any changes in the code.

For example, if a login page has to be tested against multiple test data having a combination of characters, digits and alphanumeric, such test data can be stored in excel or csv so that the same data can be fed into the test scripts at the time of execution. 

Selenium is a widely preferred open-source automation testing framework for web applications, however, it doesn’t support any predefined feature that can be utilised to perform – create, read, update and delete (CRUD) operations on the externally maintained test data fed from excel, csv, database, etc. Thankfully, there are multiple tools/api’s available that can supply external test data to the test scripts and can perform other relevant operations on the test data as per the project requirement. 

In this article, we would understand about Apache POI API that can be integrated with Selenium to perform CRUD operations.

Pros and Cons of Data Driven Framework

Pros of data driven test framework :

• Allows testing an application against huge set of test data in regression testing
• Test data can be maintained externally in a single file and can be kept separate from test scripts
• Allows maintaining the record of test data input and result output with particular test data
• Helpful in improving test efficiency and increasing the test coverage by executing test scripts against multiple test data
• Any changes done in test script doesn’t impact test data, vice versa
• The same test method can be executed multiple times with different test data instead of separately creating/calling same test methods for multiple test data
• The external test data can be reviewed even by non-technical professionals as they don’t have to dig into the code

Cons of data driven test framework :

• Data reading and validation might consume a lot of time in case of large data set
• The amount of code increases while integrating Selenium with Data Driven Framework
• Over time the data driven test framework code might require maintenance
• High level of technical skills are required for developing data driven test framework
• For different test module, different test data files needs to be created

Implementation of Data Driven Framework With Selenium Using Apache POI Library

Apache POI (Poor Obfuscation Implementation) is an open-source library developed by Apache Software Foundation. It is the most preferable API for building a selenium data driven framework. Apache POI API enables us to perform operations on excel files using Java programs. In short, we can easily perform read and write operations on excel with Apache POI functions. With such read and write operations, the externally maintained test data can be smoothly supplied to test scripts. 

Prerequisites to implement Data Driven Framework with Apache POI:

1. Eclipse, Intellij or any IDE
2. JDK
3. Microsoft excel or LibreOffice excel
4. Maven dependencies of TestNG, Selenium, Apache POI

Test Scenario : To verify pCloudy login system against different work emails and passwords. 

1. Direct to pCloudy login page
2. Enter work email
3. Enter Password
4. Click on Login
5. Get the title of the landing page after login
6. Set the test case result in excel for corresponding test data

Before moving towards the implementation of a data driven framework in Selenium, it is important to maintain the test data externally. To automate the above mentioned scenario, we can create an excel sheet having work emails and corresponding passwords. The goal is to feed all the data present in rows and columns i.e. work email and passwords to the test scripts to achieve maximum test coverage.

Let’s now understand the implementation of Data Driven Framework with Selenium in detail:

1. 1. Pom.xml – Project Object Model file contains information of the maven project along with the configurations such as dependencies, plugins, goals, build directory etc. Below are a few dependencies required to proceed with TestNG, Selenium and Apache POI.

<dependencies>
		<dependency>
			<groupId>org.testng</groupId>
			<artifactId>testng</artifactId>
			<version>6.10</version>
		</dependency>

		<dependency>
			<groupId>io.github.bonigarcia</groupId>
			<artifactId>webdrivermanager</artifactId>
			<version>3.8.1</version>
			<scope>compile</scope>
		</dependency>
		
		<dependency>
			<groupId>org.seleniumhq.selenium</groupId>
			<artifactId>selenium-java</artifactId>
			<version>2.53.1</version>
		</dependency>
		
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>4.1.2</version>
		</dependency>
                <dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>4.1.2</version>
			<scope>test</scope>
		</dependency>
	        </dependencies>

• 2. ExcelOperations Class : This is a utility class that includes all the operations to be performed on excel :

• setExcelFile : Initializing a workbook and setting up an excel file using excel file path.
• getCellData : Reading the test data maintained in excel using row number and column number.
• getRowCountInSheet : Getting the number of rows occupied in the sheet.
• setCellValue : Writing the value in the excel sheet cells using row number and column number.

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelOperations {

	private static HSSFWorkbook workbook;
    private static HSSFSheet sheet;
    private static HSSFRow row;
    private static HSSFCell cell;

   public void setExcelFile(String excelFilePath,String sheetName)
 throws IOException {
       File file =    new File(excelFilePath);
       
       //Creating object of FileInputStream class to read excel
       FileInputStream inputStream = new FileInputStream(file);
       
       //creating a workbook instance (.xls file)
       workbook=new HSSFWorkbook(inputStream);
       
       sheet=workbook.getSheet(sheetName);
   }

    public String getCellData(int rowNumber,int columnNumber){
       //getting the cell value from rowNumber and column Number
        cell =sheet.getRow(rowNumber).getCell(columnNumber);
        
        return cell.getStringCellValue();
    }

    public int getRowCountInSheet(){
    	//getting the count of rows in sheet
       int rowcount = sheet.getLastRowNum()-sheet.getFirstRowNum();
       return rowcount;
    }

    public void setCellValue(int rowNum,int columnNum,String 
cellValue,String excelFilePath) throws IOException {
    	//creating a new cell in row and writing value to it      
    	sheet.getRow(rowNum).createCell(columnNum).setCellValue(cellValue);
        
    	FileOutputStream outputStream = new FileOutputStream(excelFilePath);
    	workbook.write(outputStream);
    }
    
}

• 3. Constants Class : This class is created to separate test script constants so that these can be reused in the entire test suite. The primary advantage of creating a constants class is, if any change needs to be done in value, the value can simply be modified only in the constants class instead of hampering different test scripts. We have specified test application URL, test data excel file path, test data excel file name in the constants class below :

public class Constants {
	
   public static final String URL = "https://device.pcloudy.com/login";
   public static final String TestData_FILEPATH = "/home/ramit/Documents/";
   public static final String TestData_FILENAME = "LoginTestData.xls";

}

• 4. pCloudyLogin Class :  This is a test script class, validating the pCloudy login system against multiple test data of work emails and passwords stored in excel sheet. The excelOperations class methods are being called in this class to feed data to this test script from the externalized excel file. Furthermore, after execution of the test case against each test data, the corresponding test result would be auto added in the same sheet.

import java.io.IOException;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;
import io.github.bonigarcia.wdm.WebDriverManager;

public class pCloudyLogin {
	
    static ExcelOperations excel = new ExcelOperations();    
    static String excelFilePath 
=Constants.TestData_FILEPATH+Constants.TestData_FILENAME;
	static WebDriver driver;
	
	@BeforeClass
	public void setup() 
	{
		  WebDriverManager.chromedriver().setup(); 
		  driver = new ChromeDriver();
	}


   @Test
   public void loginTest() throws InterruptedException, IOException
   {
	  
	   driver.get(Constants.URL);
	   driver.manage().window().maximize();
	   driver.manage().deleteAllCookies();
	   
	   excel.setExcelFile(excelFilePath, "LoginTestData");

	   
	   for(int i=1;i<=excel.getRowCountInSheet();i++)
       {
		   WebElement workEmail =
 driver.findElement(By.id("userId"));
		   workEmail.clear();
		   workEmail.sendKeys(excel.getCellData(i, 0));
		  
		   WebElement passWord = 
driver.findElement(By.name("password"));
		   passWord.clear();
		   passWord.sendKeys(excel.getCellData(i, 1));
		   
		   WebElement createAccountButton =
 driver.findElement(By.id("loginSubmitBtn"));
		   createAccountButton.click();
		   
		   String title = driver.getTitle();
		   if(title.equals("HubSpot Form"))
		   {
			   excel.setCellValue(i, 2, "Passed", excelFilePath);
		   }
		   else
		   {
			   excel.setCellValue(i, 2, "Failed", excelFilePath);
		   }	
       }
	   
   }
   
   @AfterClass
   public void tearDown()
   {
	  driver.quit();
   }
}

Output: