TestNG Data Provider with Excel

Simona Pitam
Data Driven Testing
A key benefit of automating functional testing is the ability to test large volumes of data on the system

quickly. But you must be able to manipulate the data sets, perform calculations, and quickly create

hundreds of test iterations and permutations with minimal effort. Test Automation Frameworks must have

capability to integrate with spreadsheets and provide powerful calculation features.

Simona Pitam
 APACHE POI
Most commercial automated software tools on the market support some sort of data driven testing, which

allows you to automatically run a test case multiple times with different input and validation values. As

Selenium Webdriver is more an automated testing framework than a ready-to-use tool, you will have to put in

some effort to support data driven testing in your automated tests. I usually prefer to use Microsoft Excel as

the format for storing my parameters. An additional advantage of using Excel is that you can easily outsource

the test data administration to someone other than yourself, someone who might have better knowledge ofSimona Pitam
the test cases that need to be run and the parameters required to execute them.
TestNG Data Providers
Data Driven framework is used to drive the test cases and suite from an external data feed. The data feed can be data
sheets like xls, xlsx, and csv files.

TestNG is a testing framework created in line with the Junit, but with more features that makes it suitable for use in
regression test automation projects. Data Provider is one such feature in TestNG. It allows a test method to be
executed with multiple sets of data.

In this article, we will look at the process involved in using the Data Provider feature for supplying the test data from
an excel sheet to a test method. In the article we will cover the following:
Simona Pitam
1. Architecture for Data Driven Framework.
2. What is a Data Provider?
3. Test Scenario for login page
4. How to provide Multiple sets of data to a Test method using DataProvider.
5. Fetching data from Excel sheet using Data provider.
Architecture for Data Driven testing

Simona Pitam
TestNG Data Providers
Data Provider is a method used for supplying the test data to a test method. It is used to test the App with multiple sets
of data. The Data-driven concept is achieved by @Data Provider annotation in TestNG.

Data Provider returns a two-dimensional object to a test method. To supply the data for the test method it can be
specified with an attribute “name” or if we do not specify the attribute “name” then the Data Provider name will be
same as the corresponding method name.

Simona Pitam
Class HSSFWorkbook
In Order to read Data from POI we will use
The following library:

Class HSSFWorkbook

● java.lang.Object
● org.apache.poi.POIDocument
● org.apache.poi.hssf.usermodel.HSSFWorkbook
● All Implemented Interfaces:
● java.io.Closeable, java.lang.AutoCloseable, java.lang.Iterable<Sheet>, Workbook
Simona Pitam
Class HSSFSheet

Class HSSFSheet
● java.lang.Object
● org.apache.poi.hssf.usermodel.HSSFSheet
● All Implemented Interfaces:
● java.lang.Iterable<Row>, Sheet

Simona Pitam
Class HSSF Row

● java.lang.Object
● org.apache.poi.hssf.usermodel.HSSFRow
● All Implemented Interfaces:
● java.lang.Comparable<HSSFRow>, java.lang.Iterable<Cell>, Row

Simona Pitam
Create a TestNg test case for accepting data from Excel using
Data Provider
1) Create a TestNG class ‘DataProviderWithExcel’ by Pressing Ctrl+N , select ‘Create TestNG Class‘ under
TestNG category and Under Annotations, check ‘@BeforeMethod‘, ‘@AfterMethod‘ & ‘DataProvider‘ and
click Finish.

3) Add a method Registration_data() to your Test class. This method takes two strings as input parameters.

4) Now divide the test case in to three parts :

@BeforeMethod : Launch Firefox and direct it to the Base URL

@Test : Enter Username & Password to Login, Print console message and Log out
Simona Pitam
@AfterMethod : Close Firefox browser

Test Case will look like this:

How to do it…

1) Download JAR files of Apache POI and Add Jars to your project library. You can download it from here.

That’s all about configuration of Apache POI with eclipse. Now you are ready to write your test.

2) Create a ‘New Package‘ file and name it as ‘testData’, by right click on the Project and select New >

Package. Place all of your test data in this folder (package) whether it is a sql file, excel file or anything.

Simona
3) Place a Excel file in the above created package location and save it asTestData.xlsx. Fill the data in the Pitam
excel like below image:
Create functions to Open & Read data from Excel

We need a way to open this Excel sheet and read data from it within our Selenium
test script. For this purpose, I use the Apache POI library, which allows you to
read, create and edit Microsoft Office-documents using Java. The classes and
methods we are going to use to read data from Excel sheet are located in the
org.apache.poi.hssf.usermodel package.
To see step by step process to set up Apache POI Excel, please visit Data Driven
Framework. Simona Pitam
Selenium & Maven

Download Maven and Install Maven-Eclipse Plugin

1. Download Maven.

2. Next is to create a Maven Project. If you haven’t used Maven before in Eclipse then you may

also need to install the Maven-Eclipse plugin.

3. You need to click on the Help->Install New Software. Enter Simona Pitam

http://download.eclipse.org/technology/m2e/releases in ‘Work with’ an click on Add.

4. Select Maven plugin. Click on Next and then Finish.

Selenium & Maven

Simona Pitam
Create Maven Project

Create Maven Project

1. Once Maven Plugin is installed. You need to create Maven Project. If you are new to Maven,
step by step details are here.
2. In the Group Id, enter com.javacodegeeks.testng.maven. In artifact Id, enter the project name, for
example, testNgMavenExample. Click on Finish to create the project. Simona Pitam
Create Maven Project

Simona Pitam
 POM File Dependencied
Now we will create a test class in src/test/java/com/javacodegeeks/testng/maven

Simona Pitam
What is Maven
Apache Maven is a build automation tool for Java projects. Think of Ant, or Make, but much more
powerful and easier to use. If you've ever had to deal with building a Java project with
dependencies or special build requirements then you've probably gone through the frustrations
that Maven aims to eliminate.

Throughout this article I'll explain a bit more about the tool, how it's used, and hopefully answer
your question - What is Maven? Simona Pitam
POM File
Using Maven is extremely easy, once you learn a few of the main concepts. Each project contains
a file called a POM (Project Object Model), which is just an XML file containing details of the
project. Some of these details might include project name, version, package type, dependencies,
Maven plugins, etc.

Simona Pitam
Maven Commands
The command could be a number of things, but to name a few:

● test: Runs all of the tests for the project

● clean: Deletes all of the old build files
● package: Packages the project in to a jar (or other format depending on what was specified)
● install: Places the project in the local Maven repository
● deploy: Places the project in the remote Maven repository Simona Pitam
Logging

Why logging is important in any application?

Logging is very important to any application. It helps us collect information about how the application is
running and also helps us debug if any failure occurs.

Simona Pitam
 Simona Pitam

Log4J
What is Log4j?
Log4j is a brilliant logging API available both on Java and .net framework. Advantages are:

– Log4j allows you to have a very good logging infrastructure without putting in any efforts.

– Log4j gives you the ability to categorize logs at different levels (Trace, Debug, Info, Warn, Error and Fatal).

– Log4j gives you the ability to direct logs to different outputs. For e.g. to a file, Console or a Database.

– Log4j gives you the ability to define the format of output logs.

– Log4j gives you the ability to write Asynchronous logs which helps to increase the performance of the
application.

– Loggers in Log4j follow a class hierarchy which may come handy to your applications.

If you are not able to understand any of these points than don’t worry. Things will get clearer as we approach
to the end of Log4j Tutorial series.
 Simona Pitam

Log4j

Log4j
Log4j consists of four main components

– LogManager

– Loggers

– Appenders

– Layouts

With these comes some additional components which will be covered in the individual headings in following
tutorials
 Log4j
package Log4jSample;

import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

public class SampleEntry {

//mainLogger is a logger object that we got from LogManager. All loggers are
//using this method only. We can consider LogManager as a factory to create
//Logger objects
static Logger mainLogger = LogManager.getLogger(SampleEntry.class.getName());
Simona Pitam
public static void main(String[] args) {
// TODO Auto-generated method stub

BasicConfigurator.configure();
mainLogger.info("This is just a logger");

}
}
Loggers

Logger
This is a class which helps you log information at different logging levels. In the above sample code you can
see that we have created a logger named mainLogger using the LogManager static class. Now we can use it to
write logs. As you can see we have mainLogger.info(“Comments that you want to log”) statement which logs
the string.

Simona Pitam
Download Log4J
https://logging.apache.org/log4j/1.2/download.html

Extract Add Log4J into JRE Library