DataProvider in TestNG using Excel


In the previous tutorial, I explained the DataProvider in TestNG without using Excel. In this tutorial, I will explain the use of Excel in DataProvider for TestNG.

I have created an Excel – SearchInBing.xlsx and placed it on the Desktop. You can create a Test Data folder in your project and place the Excel file within it. So, my datasheet looks like the below:

Next, we will create a DataProvider method that will use another method to read the Excel file & create a 2D object from the row & column values of the Excel and return the same value, so that our test script can use it. The code for it would look like the below:

import org.testng.annotations.DataProvider;


import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelDataProvider {

    @DataProvider(name = "excelData")
    public Object[][] excelDataProvider() throws IOException {

        // We are creating an object from the excel sheet data by calling a method that
        // reads data from the excel stored locally in our system
        Object[][] arrObj = getExcelData(
        return arrObj;

    // This method handles the excel - opens it and reads the data from the
    // respective cells using a for-loop & returns it in the form of a string array
    public String[][] getExcelData(String fileName, String sheetName) throws IOException {
        String[][] data = null;
        try {

            FileInputStream fis = new FileInputStream(fileName);
            XSSFWorkbook workbook = new XSSFWorkbook(fis);
            XSSFSheet sheet = workbook.getSheet(sheetName);
            XSSFRow row = sheet.getRow(0);
            int noOfRows = sheet.getPhysicalNumberOfRows();
            int noOfCols = row.getLastCellNum();
            Cell cell;
            data = new String[noOfRows - 1][noOfCols];

            for (int i = 1; i < noOfRows; i++) {
                for (int j = 0; j < noOfCols; j++) {
                    row = sheet.getRow(i);
                    cell = row.getCell(j);
                    data[i - 1][j] = cell.getStringCellValue();
        } catch (Exception e) {
            System.out.println("The exception is: " + e.getMessage());
        return data;

Now, create a class that contains the test code. By default, the data provider will be looked for in the current test class or one of its base classes. If you want to put your data provider in a different class, it needs to be a static method or a class with a no-arg constructor, and you specify the class where it can be found in the data provider class attribute.

import io.github.bonigarcia.wdm.WebDriverManager;
import org.openqa.selenium.By;
import org.openqa.selenium.Keys;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.testng.Assert;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.BeforeMethod;
import org.testng.annotations.Test;

public class DataProviderWithExcelDemo {
  WebDriver driver;

    public void setUp() {
        System.out.println("Start test");
        driver = new ChromeDriver();


    @Test(dataProvider = "excelData", dataProviderClass = ExcelDataProvider.class)
    public void search(String keyWord1, String keyWord2) {

        WebElement txtBox = driver.findElement("sb_form_q"));
        txtBox.sendKeys(keyWord1, " ", keyWord2);
        System.out.println("Keyword entered is : " + keyWord1 + " " + keyWord2);
        System.out.println("Search results are displayed.");
        System.out.println("RESULT: "+ driver.getTitle());

    public void burnDown() {


To run the code, right-click and Select – TestNG Test.

The Execution status will look like this, as shown below:

This test execution generates reports under the test-output folder.

We are concerned about two reports – index.html and emailable-report.html.

Below is the image of emailable-report.html.

Below is the image of index.html.

See how easy it is to read data from Excel and use it in the Test Code using DataProvider.

I hope you have enjoyed this tutorial. Happy Learning!!


How to read data from Excel in Java


This tutorial describes to read data from Excel file in Java.

I’m using Apache POI to read the excel file. To download and install Apache POI, refer here.

The two mainly used  classes for managing Excel Workbooks are:

  • HSSFWorkbook- These class methods are used to read/write data to Microsoft Excel file in .xls format. It is compatible with MS-Office versions 97–2003.
  • XSSFWorkbook- These class methods are used to read-write data to Microsoft Excel in .xls or .xlsx format. It is compatible with MS-Office versions 2007 or later.

How to read Excel (.xlsx) from specific cell value?

Step 1 – To locate the location of file.

File file = new File("C:\\Selenium_ReadTestData.xlsx");

Import File from package


Step 2 – Instantiate FileInputStream to read from the file specified.

 FileInputStream fis = new FileInputStream(file);

Step 3 – Create object of XSSFWorkbook class

XSSFWorkbook wb = new XSSFWorkbook(fis);

Step 4 –  Import XSSFWorkbook from package

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

Step 5 – To read excel sheet by sheet name

XSSFSheet sheet1 = wb.getSheet("Read_TestData");

Step 6 – Import XSSFSheetfrom package

import org.apache.poi.xssf.usermodel.XSSFSheet;

Step 7 –  To access data from the XLSX file, use of  the following methods:

getRow(int rownum)
getCell(int cellnum)

Step 8 –  To get the cell value from column A and Row 1


Here getRow(0) will look into the first row, and getCell(0) will look into the first column, i.e. A1.

To retrieve the String value we are making use of getStringCellValue in the end.

Below is the Excel which I’m using to read the data as mentioned below:-

We want to get the value of Row 1, Cell B

We want to get the value of Row 3, Cell A


import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadSpecificData {

	public static void main(String[] args) {
     String path = "C:\\Selenium_ReadTestData.xlsx";	

		try {
			//Create an object of FileInputStream class to read excel file
			FileInputStream fis = new FileInputStream(path);
			//Create object of XSSFWorkbook class
			XSSFWorkbook wb = new XSSFWorkbook(fis);
			//Read excel sheet by sheet name 
			XSSFSheet sheet1 = wb.getSheet("Read_TestData");
			//Get data from specified cell
		} catch (IOException e) {




How to read the entire Excel sheet?

To read the complete data from Excel, you can iterate over each cell of the row, present in the sheet. 

To get the last and first-row number, there are two methods in the sheet  class:

  • getLastRowNum() 
  • getFirstRowNum()
   int rowCount=sheet.getLastRowNum()-sheet.getFirstRowNum();	  

Once you get the row, you can iterate over the cells present in the row by using the total number of cells, that we can calculate using getLastCellNum() method:

int cellcount=sheet.getRow(1).getLastCellNum();

Below is the entire program to read complete excel.


import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelFile {
	public static  void main(String args[]) throws IOException {
		String path = "C:\\Selenium_ReadTestData.xlsx";
		//Create an object of FileInputStream class to read excel file
		FileInputStream fis = new FileInputStream(path);
		//Create object of XSSFWorkbook class
		Workbook workbook = new XSSFWorkbook(fis);
		//Read excel sheet by sheet name 
		Sheet sheet = workbook.getSheet("Read_TestData");
		//Find number of rows in excel file
	    int rowCount=sheet.getLastRowNum()-sheet.getFirstRowNum();	    
	    System.out.println("row count:"+rowCount);
		 //iterate over all the row to print the data present in each cell.
	    for(int i=0;i<=rowCount;i++){
	        //get cell count in a row
	        int cellcount=sheet.getRow(i).getLastCellNum();	        
	        //iterate over each cell to print its value       
	        for(int j=0;j<cellcount;j++){
	            System.out.print(sheet.getRow(i).getCell(j).getStringCellValue().toString() +"||");

That’s it! We are done.

Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!! Cheers!!

How to download and install Apache POI


This tutorial describes how to download and install Apache POI.

Selenium does not have an inbuilt method to read data from an Excel File. But, there are various libraries in JAVA that helps in reading/writing data from Excel files. Apache POI is one of the most used libraries, which provides various classes and methods to read/write data from various formats of Excel files(xls, xlsx etc).

What is Apache POI?

Apache POI, where POI stands for (Poor Obfuscation Implementation)  is the Java API for Microsoft Documents that offers a collection of Java libraries that helps us to read, write, and manipulate different Microsoft files such as Excel sheets, power-point, and word files.

Download Apache POI

Step 1To download Apache POI, go to its official site, here. Click on the Download as shown in the image. This link will navigate to the page showing the latest release of Apache POI.  The latest Apache POI version is 5.2.3. You can follow the same steps for any version of POI.

Step 2 This page shows the latest Apache POI Release Artifacts. Here, you can see POI 5.0.0 is the latest one. Download any one of the Binary Distribution options. One option is .ztar.gz and another option is .zip. I have selected .zip option.

Step 3 After clicking on the link, it navigates to another page as shown below. I have used the highlighted link to download the POI library files.

Step 4 Once is downloaded and extracted, this is how the folder looks like

How to add POI libraries in Eclipse?

Step 1 Below is the Java project present in Eclipse.

Step 2 To add POI libraries to this project, Right-click on the project, hover over the Build path, select Configure Build Path.

Step 3  It will open the “Properties” of the project. After that, select the Libraries tab. Finally, click on the Add External JARs as highlighted below.

Step 4 Select the JARs in the parent folder of the unzipped POI files. Subsequently, click on the Open button to include them in the Eclipse project.

Step 5 – Next, select the JARs under the ooxml-lib folder in the unzipped POI folder as highlighted below:

Step 6 – Select the JARs under the lib folder in the unzipped POI folder as highlighted below.

Step 7 – After that, once all the POI JARs add, click on the Apply and Close button as highlighted below.

Step 8 – Once all the POI libraries successfully install in the Eclipse project, they will reflect under the Referenced Libraries folder in the left pane of the Eclipse project structure, as shown below:

How to add POI libraries to Maven Java Project

You can add the poi and poi-ooxml jar files to the Maven project by mentioning the dependencies in pom.xml.

 <!-- -->

<!-- -->

You need to make sure that these two dependencies should be of the same version.

That’s it! We have downloaded and installed Apache POI.

Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!! Cheers!!