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

Table of contents
Reading Time: 3 minutes

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.
excel screenshot1

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.

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.

Written by 

Gaurav is a Module Lead QA Consultant having experience of more than 4.5 years. He is well familiar with core QA concepts and well versed in designing automation frameworks in a microservice environment. He has good hands-on experience in automation tools like ReadyAPI, SoapUI,,,,, Selenium (Java & Scala) and others. He actively promotes shifting left in the development cycle and understands the project domain to work closely with the development team. He has a good understanding of different domains like Airline, health Care and Shipping and cruising domain.