Passing data to DataProvider from Excel Sheet in TestNG

In TestNG DataProvider helps  us to send multiple sets of data  from our excel sheet to a test method. But  here we need to make sure that the data returned by data provider should match with the parameter which we provide in Test method.

I explained in Below example how to pass the data to Data providers by reading the data from Excel sheet. We will write a simple program in which we will Validate the product addition by taking details of products from Excel sheet.

Step 1: First create a method to read excel data and return string array.
Step 2: Create before class and after class methods which helps in getting the browser and closing them when done.
Step 3: Create a data provider which actually gets the values by reading our excel sheet.
Step 4: Create a Test which takes different parameters i.e Name, Description, Weight, price, Result .
Step 5: Add dataprovider name for @Test method to receive data from dataprovider.

Below i mentioned code for my HSSFWorkbook, HSSFSheet worksheet , file location initialization and keep in mind that If you use the common interfaces like Workbook, you code is  workable with both HSSF and XSSF

package framework;

import java.awt.AWTException;
import java.awt.Robot;
import java.awt.event.KeyEvent;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.concurrent.TimeUnit;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.openqa.selenium.By;
import org.openqa.selenium.JavascriptExecutor;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.support.ui.ExpectedConditions;
import org.openqa.selenium.support.ui.Select;
import org.openqa.selenium.support.ui.WebDriverWait;
import org.testng.annotations.BeforeSuite;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class Addproduct {

	static WebDriver driver;
	String chrome_path;
	public static HSSFWorkbook workbook;
	public static HSSFSheet worksheet;
	public static DataFormatter formatter= new DataFormatter();
	public static String file_location = System.getProperty("user.dir")+"/Akeneo_product";
	static String SheetName= "Sheet1";
	public  String Res;
	Write obj1=new Write();
	public int DataSet=-1;

	public void OpenBrowser() throws InterruptedException

		System.setProperty("webdriver.chrome.driver", "/home/**");
		WebDriver driver = new ChromeDriver();

Below I mentioned program of Data provider which provide my data from excel sheet.

	public static Object[][] ReadVariant() throws IOException
	FileInputStream fileInputStream= new FileInputStream(file_location); //Excel sheet file location get mentioned here
		workbook = new HSSFWorkbook (fileInputStream); //get my workbook 
		worksheet=workbook.getSheet(SheetName);// get my sheet from workbook
   	    HSSFRow Row=worksheet.getRow(0);   	 //get my Row which start from 0   
    	int RowNum = worksheet.getPhysicalNumberOfRows();// count my number of Rows
    	int ColNum= Row.getLastCellNum(); // get last ColNum 
    	Object Data[][]= new Object[RowNum-1][ColNum]; // pass my  count data in array
    		for(int i=0; i<RowNum-1; i++) //Loop work for Rows
     			HSSFRow row= worksheet.getRow(i+1);
    			for (int j=0; j<ColNum; j++) //Loop work for colNum
    					Data[i][j]= "";
    					HSSFCell cell= row.getCell(j);
    						Data[i][j]= ""; //if it get Null value it pass no data 
    						String value=formatter.formatCellValue(cell);
    						Data[i][j]=value; //This formatter get my all values as string i.e integer, float all type data value

    	return Data;

Data Set / Test Data Examples

The below is the excel sheet for “Add product in akeneo with Valid Data”

Why and How to use Data Set / Test Data in Program:

There are many cases where you may need to check calculation / percentage by adding more number of records. In such cases we have to use data set and pass different values.

We need to make sure to identify the number of fields in the function / page and define the number of columns. On the basis of these columns, data will be provided to input fields in the application.

Below I mentioned  program for akeneo product addition by getting data from excel sheet.

@Test //Test method
(dataProvider="ReadVariant") //It get values from ReadVariant function method

//Here my all parameters from excel sheet are mentioned.
public void AddVariants(String NAME, String DESCRIPTION, String WEIGHT, String PRICE, String MODEL, String RS) throws Exception
Data will set in Excel sheet once one parameter will get from excel sheet to Respective locator position.
System.out.println("NAme of product available are:" +NAME);
System.out.println("Weight for products are:" +DESCRIPTION);
System.out.println("Volume of product are:" +WEIGHT);
System.out.println("Description quotation are:" +PRICE);
System.out.println("Description picklings are:" +MODEL);

WebDriverWait wait =  new WebDriverWait(driver, 90);

// User get Login


// Click on product label
WebElement element1 = wait.until(ExpectedConditions.visibilityOfElementLocated(By.xpath("//a[@href ='#/enrich/product/']")));
driver.findElement(By.xpath("//a[@href ='#/enrich/product/']")).click();
//click on create product
driver.findElement(By.cssSelector("div[data-drop-zone='buttons'] a")).click();
//click on product model
//click on SKU and passed data from dataprovider i.e MODEL 
// Select family of product
WebElement element2 = wait.until(ExpectedConditions.visibilityOfElementLocated(By.xpath("/html/body/div[17]/div[2]/div/div/div[2]/div[3]/div[2]/div[2]/div/a")));

//click on save

WebElement element3 = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("div[data-attribute='name'] input")));
//click on name input field and clear .
driver.findElement(By.cssSelector("div[data-attribute='name'] input")).clear();
//Send Name parameter from dataprovider
driver.findElement(By.cssSelector("div[data-attribute='name'] input")).sendKeys(NAME);
//clear description field
//send description parameter from data provider
//clear weight field
driver.findElement(By.cssSelector("div[data-attribute='weight'] input")).clear();
//send weight parameter from data provider
driver.findElement(By.cssSelector("div[data-attribute='weight'] input")).sendKeys(WEIGHT);
//send price parameter from data provider
driver.findElement(By.cssSelector("div[data-attribute='main_price'] input")).sendKeys(PRICE);

//Result is equal to pass mentioned here
obj1.WriteResult(Res, DataSet+1); //It call write Result program and DataSet get increase by +1 after execution of write Result method

/* code for logout */

//driver.findElement(By.cssSelector("div[data-attribute='Model_new'] input")).sendKeys(MODEL);


Below program for writeResult program in Excel sheet:

package framework;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.testng.annotations.Test;

import framework.*;

public class Write 
		public static HSSFWorkbook workbook;
		public static HSSFSheet worksheet;
		public String ColName="Result";
		public int col_num;
	public void WriteResult(String Ress, int DR) throws Exception
		FileInputStream file_input_stream= new FileInputStream("/home/users/shivani.kukreti/Documents/akeneo-product.xls");
		workbook=new HSSFWorkbook(file_input_stream);
		HSSFRow Row=worksheet.getRow(0);

		int sheetIndex=workbook.getSheetIndex(Addproduct.SheetName);
		DataFormatter formatter = new DataFormatter();
			System.out.println("No such sheet in file exists");
		} else 		{
				for(int i=0;i<Row.getLastCellNum();i++)
					HSSFCell cols=Row.getCell(i);
					String colsval=formatter.formatCellValue(cols);
				Row= worksheet.getRow(DR);
					//get my Row which is equal to Data  Result and that colNum
						HSSFCell cell=worksheet.getRow(DR).getCell(col_num);
						// if no cell found then it create cell
						if(cell==null) {
						//Set Result is pass in that cell number
				catch (Exception e)
			FileOutputStream file_output_stream=new FileOutputStream("/home/users/shivani.kukreti/Documents/akeneo-product.xls");
			if(col_num==-1)	{
				System.out.println("Column you are searching for does not exist");

Here Passed Results in my excel sheet are:

Category(s) Selenium TestNG
. . .