Data Driven Test Automation with Apache POI (Part-1)

What is Data Driven Test Automation?

To test the functionality of an application it is requires to test with multiple input values. Data driven test automation allows to create test scripts where test data or input/output values are read from an external data files instead of using hard code each time the test run. Data driven test framework allows user to separate their data to the code for re-usability purpose.

Types of external data files

The External data files can be any of them mention below:

• Excel files
• CSV files
• ODBC sources
• ADO objects

Here we are using excel sheet as an external data source. The test scripts are written in Scala.

How to access the data From excel Files ?

To access the data from excel files we need to use Apache POI, a project by Apache foundation which enables to use pure Java libraries to read and write the data in Microsoft Office format. For accessing the Apache POI you have to keep the dependency in your project build file.

Apache POI has various components but here we are using only XSSF. You can also use HSSF depends on your format of Microsoft document.

HSSF : Stands for Horribel SpreadSheet Format used for .xls format of excel files.

XSSF : Stands for Xml SpreadSheet Format used for .xlsx format of excel files.

Apache POI dependency

For getting started, first you have to add its dependency in build.sbt .

libraryDependencies += “org.apache.poi” % “poi-ooxml” % “3.13”
libraryDependencies += “org.apache.poi” % “poi-ooxml-schemas” % “3.13”
libraryDependencies += “org.apache.poi” % “poi-scratchpad” % “3.13”
libraryDependencies += “org.apache.poi” % “poi” % “3.13”
libraryDependencies += “org.apache.poi” % “poi-excelant” % “3.13”

How Data Driven Framework works ?

data driven test automation framework works in two steps :

• First step is to create an external data file for which store the test data. Here we are storing data for user login .
• Populate the data from excel sheet into automation test scripts. It has multiple sub steps:
  • First of all we have to import all the required packages.
  • declare a trait which has multiple methods to access the data from excel file
  • Create a method which will read the data from excel file and write the data in excel file.

package seleniumHelper

import java.io.File
import java.io.FileInputStream
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.apache.poi.xssf.usermodel.XSSFSheet

trait RdXl {

// Location of the excel file
val excelPath = "/home/knoldus/readexcel.xlsx"

// Create a object for accessing excel file from given location.
val file = new File(excelPath)

// Create an object for FileInputStream to perform input and output operations
val fis = new FileInputStream(file)

// To load the full excel workbook
val xfb = new XSSFWorkbook(fis)

//Create a method for reading data of single row
def getDataFromExcel(sheetNumber: Int, row: Int) = {

// Create an object for accessing the data from given sheet of loaded workbook
val sheet = xfb.getSheetAt(sheetNumber)

// To access the data of the given row
val roww = sheet.getRow(row)

// To get the value of columns and change it into string
(roww.getCell(0).toString(), roww.getCell(1).toString())

}

// Create a method to access the full data from given sheet of loaded workbook
def getAllData(sheetNumber: Int) = {

// Access the first sheet with index 0 of workbook fis stated in this location
val sheet = xfb.getSheetAt(sheetNumber) //access the first sheet with index 0 of workbook fis stated in this location

// To get the all the records of excel sheet.
val r = sheet.getFirstRowNum+1 to sheet.getLastRowNum

// Store the data in a list
val data = for (x <- r.toList) yield {

// To access the data of the given row
val roww = sheet.getRow(x)

// To get the value of columns and change it into string
(roww.getCell(0).toString() -> roww.getCell(1).toString())

}

// Return the data in form of list
data.toList

}

This code enables you to read the data from excel sheet and store it in the form of list.
roww.getcell() method return the value of type cell so its necessary to change it into string.

How to populate the data in test scripts ?

To populate the data in Test cases we have to extends that trait in our test class and import the required packages. Testsetup is a trait in which you can define the various user define methods required for running your test scripts.

class LogIn extends Testsetup with ReadExcel {

val sheet = xfb.getSheetAt(0)
println("no of records: " + sheet.getLastRowNum)

"Application" should
"must login with register email-id" in {

running(TestServer(port, FakeApplication(additionalConfiguration = inMemoryDatabase())), HTMLUNIT) { browser =>

getAllData(0).foreach { data =>
println("signin as => " + data)
signIn(data._1, data._2)
logout()
Thread.sleep(5000)
}
}
}

Here I am using XSSFWorkbook for accessing the .xlsx format excel file if you want to access the xls format excel file you can use HSSF instead of XSSF.

Thank You.