Data Driven Framework Using Apache Poi

What is Data Driven Automation Framework?

As the name suggests, Data Driven Testing Framework is a framework which is driven by test data, which means that test data is the important factor here. The basic expectation with this kind of test automation framework is that your test scripts should be built in such a way that it should work for different sets of data without any changes to the test script. Consider a scenario where you have a large number of user credentials for an application. You need to login to the application with each of these credentials to find out which of the user credentials are working properly and which are not. In order to complete this task, you can create a script in Selenium which would read each of the user credentials data and try to login to the application and at the end would report the results. This is also useful when you have large set of fields to verify it with different set of data that you get from your client.

What is the basic approach used to create a Data Driven Framework?

In simple terms, all you have to do is to just separate your code and data. And your Data Driven Framework is ready. You would have to store your data in some external files (such as text files, excel, database etc) and figure out a way to read the data from this external source and pass it to your application through some variables. So let's start with the Actual Data Driven framework using apache poi. Download Apache Poi from apache.org website. It is recommend to download the latest version of apache poi.

1 Create New Project in Eclipse & add all these jars.

2.Create New  Project in Eclipse & add all these jars.

Test Case.	User Name	Password	Result
Login User1	gopal.mule@gmail.com	P@ssw0rd01	PASS
Login User2	gopal.mule@yahoo.co.in	P@ssw0rd02	FAIL

Right click on package testData and click on Refresh button. Once you have add Test data in excel sheet.

3. Add two constant variables (testData package path & Excel file name) in the Constant class.

package constant;

public class Constant { public static final String URL = "https://www.amazon.com/ap/signin"; public static final String Path_TestData = "E://Automation//DataDrivenFramework//bin//testData//"; public static final String File_TestData = "Testdata.xlsx"; }

4. Create New Package runDataDrivenFramework and create class ManageExcelData in which we will manage all read write operation of Excel data.

package runDataDrivenFramework; import constant.Constant; import java.io.FileInputStream; import java.io.FileOutputStream; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ManageExcelData { private static XSSFSheet ExcelWSheet; private static XSSFWorkbook ExcelWBook; private static XSSFCell Cell; private static XSSFRow Row; //This method is to set the File path and to open the Excel file, Pass Excel Path and Sheetname as Arguments to this method public static void setExcelFile(String Path,String SheetName) throws Exception { try { // Open the Excel file FileInputStream ExcelFile = new FileInputStream(Path); // Access the required test data sheet ExcelWBook = new XSSFWorkbook(ExcelFile); ExcelWSheet = ExcelWBook.getSheet(SheetName); } catch (Exception e){ throw (e); } } //This method is to read the test data from the Excel cell, in this we are passing parameters as Row num and Col num public static String getCellData(int RowNum, int ColNum) throws Exception{ try{ Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum); String CellData = Cell.getStringCellValue(); return CellData; }catch (Exception e){ return""; } } //This method is to write in the Excel cell, Row num and Col num are the parameters public static void setCellData(String Result, int RowNum, int ColNum) throws Exception { try{ Row = ExcelWSheet.getRow(RowNum); Cell = Row.getCell(ColNum, Row.RETURN_BLANK_AS_NULL); if (Cell == null) { Cell = Row.createCell(ColNum); Cell.setCellValue(Result); } else { Cell.setCellValue(Result); } // Constant variables Test Data path and Test Data file name FileOutputStream fileOut = new FileOutputStream(Constant.Path_TestData + Constant.File_TestData); ExcelWBook.write(fileOut); fileOut.flush(); fileOut.close(); }catch(Exception e){ throw (e); } } }

5. Once we are done with writing Excel functions we can go ahead and modify the Login module to accept the test data from excel sheet.

package runDataDrivenFramework; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.ss.util.SheetUtil; import org.openqa.selenium.By; import org.openqa.selenium.WebDriver; // Now this method does not need any arguments public class LoginWithAPP { public static void Execute(WebDriver driver) throws Exception{ //This Will Read Username & Password from Excel sheet. String Username = ManageExcelData.getCellData(1, 1); String Password = ManageExcelData.getCellData(1, 2); //driver.get("https://www.amazon.com/ap/signin"); driver.findElement(By.id("ap_email")).sendKeys(Username); driver.findElement(By.id("ap_password")).sendKeys(Password); driver.findElement(By.id("signInSubmit")).click(); } }

6. Now create Main class RunApplication, In this method We will Read Data From Excel sheet & use it for further Login process.
package runDataDrivenFramework;

import java.util.concurrent.TimeUnit;

import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;

import constant.Constant;

public class RunApplication {

/**
* @param args
*/

  private static WebDriver driver = null;
 
public static void main(String[] args) throws Exception {

    //This is to open the Excel file. Excel path, file name and the sheet name are parameters to this method

    ManageExcelData.setExcelFile(Constant.Path_TestData + Constant.File_TestData,"Sheet1");

    driver = new FirefoxDriver();

    driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);

    driver.get(Constant.URL);
    
    LoginWithAPP.Execute(driver);

    System.out.println("You are Login Successfully.");

    

    driver.quit();

    //This is to send the PASS value to the Excel sheet in the result column.

    ManageExcelData.setCellData("Pass", 1, 3);

}



}