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 {

	public 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;


	@BeforeSuite
	public void OpenBrowser() throws InterruptedException

	{
		System.setProperty("webdriver.chrome.driver", "/home/**");
		driver = new ChromeDriver();
		driver.manage().window().maximize();
		driver.get("http://192.168.1.91/user/login");
		Thread.sleep(3000);
	}

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 {

public 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;

@BeforeSuite

public void OpenBrowser() throws InterruptedException

{

System.setProperty("webdriver.chrome.driver", "/home/**");

driver = new ChromeDriver();

driver.manage().window().maximize();

driver.get("http://192.168.1.91/user/login");

Thread.sleep(3000);

}

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

@DataProvider
	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&lt;RowNum-1; i++) //Loop work for Rows
    		{  
     			HSSFRow row= worksheet.getRow(i+1);
    			
    			for (int j=0; j&lt;ColNum; j++) //Loop work for colNum
    			{
    				if(row==null)
    					Data[i][j]= "";
    				else 
    				{
    					HSSFCell cell= row.getCell(j);
    					if(cell==null)
    						Data[i][j]= ""; //if it get Null value it pass no data 
    					else
    					{
    						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;
    }
}

@DataProvider

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

{

if(row==null)

Data[i][j]= "";

else

{

HSSFCell cell= row.getCell(j);

if(cell==null)

Data[i][j]= ""; //if it get Null value it pass no data

else

{

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.
DataSet++;
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
driver.get("http://192.168.1.91/user/login");
driver.findElement(By.id("prependedInput")).sendKeys("admin"); 
driver.findElement(By.id("prependedInput2")).sendKeys("admin");
driver.findElement(By.id("_submit")).click();

Thread.sleep(4000);

// 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();
Thread.sleep(2000);
//click on create product
driver.findElement(By.cssSelector("div[data-drop-zone='buttons'] a")).click();
//click on product model
driver.findElement(By.cssSelector("[data-form='pim-product-create-modal']")).click();
Thread.sleep(6000);
//click on SKU and passed data from dataprovider i.e MODEL 
driver.findElement(By.name("identifier")).sendKeys(MODEL);
//Thread.sleep(6000);
// 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")));
driver.findElement(By.xpath("/html/body/div[17]/div[2]/div/div/div[2]/div[3]/div[2]/div[2]/div/a")).click();
Thread.sleep(2000);

//driver.findElement(By.xpath("/html/body/div[19]/ul/li[1]")).click();
//click on save
driver.findElement(By.xpath("/html/body/div[17]/div[3]/a[2]")).click();
Thread.sleep(8000);

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
driver.findElement(By.className("note-editable")).clear();
//send description parameter from data provider
driver.findElement(By.className("note-editable")).sendKeys(DESCRIPTION);
//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);

Thread.sleep(4000);
driver.findElement(By.xpath("/html/body/div[1]/div/div[3]/div/div[1]/div[2]/div[1]/header/div[1]/div[2]/div[1]/div[1]/div[2]/div[2]/div[3]/button")).click();
//Result is equal to pass mentioned here
Res="Pass";
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 */

Thread.sleep(4000);
driver.findElement(By.xpath("/html/body/div[1]/div/div[3]/div/div[1]/div[2]/div[1]/header/div[1]/div[2]/div[1]/div[1]/div[2]/div[1]/div/div[1]/div[1]")).click();
Thread.sleep(4000);
driver.findElement(By.xpath("/html/body/div[1]/div/div[3]/div/div[1]/div[2]/div[1]/header/div[1]/div[2]/div[1]/div[1]/div[2]/div[1]/div/div[1]/div[2]/div[3]")).click();
//driver.findElement(By.cssSelector("div[data-attribute='Model_new'] input")).sendKeys(MODEL);



}

@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.

DataSet++;

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

driver.get("http://192.168.1.91/user/login");

driver.findElement(By.id("prependedInput")).sendKeys("admin");

driver.findElement(By.id("prependedInput2")).sendKeys("admin");

driver.findElement(By.id("_submit")).click();

Thread.sleep(4000);

// 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();

Thread.sleep(2000);

//click on create product

driver.findElement(By.cssSelector("div[data-drop-zone='buttons'] a")).click();

//click on product model

driver.findElement(By.cssSelector("[data-form='pim-product-create-modal']")).click();

Thread.sleep(6000);

//click on SKU and passed data from dataprovider i.e MODEL

driver.findElement(By.name("identifier")).sendKeys(MODEL);

//Thread.sleep(6000);

// 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")));

driver.findElement(By.xpath("/html/body/div[17]/div[2]/div/div/div[2]/div[3]/div[2]/div[2]/div/a")).click();

Thread.sleep(2000);

//driver.findElement(By.xpath("/html/body/div[19]/ul/li[1]")).click();

//click on save

driver.findElement(By.xpath("/html/body/div[17]/div[3]/a[2]")).click();

Thread.sleep(8000);

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

driver.findElement(By.className("note-editable")).clear();

//send description parameter from data provider

driver.findElement(By.className("note-editable")).sendKeys(DESCRIPTION);

//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);

Thread.sleep(4000);

driver.findElement(By.xpath("/html/body/div[1]/div/div[3]/div/div[1]/div[2]/div[1]/header/div[1]/div[2]/div[1]/div[1]/div[2]/div[2]/div[3]/button")).click();

//Result is equal to pass mentioned here

Res="Pass";

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 */

Thread.sleep(4000);

driver.findElement(By.xpath("/html/body/div[1]/div/div[3]/div/div[1]/div[2]/div[1]/header/div[1]/div[2]/div[1]/div[1]/div[2]/div[1]/div/div[1]/div[1]")).click();

Thread.sleep(4000);

driver.findElement(By.xpath("/html/body/div[1]/div/div[3]/div/div[1]/div[2]/div[1]/header/div[1]/div[2]/div[1]/div[1]/div[2]/div[1]/div/div[1]/div[2]/div[3]")).click();

//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;
@Test
	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);
		worksheet=workbook.getSheet(Addproduct.SheetName);
		HSSFRow Row=worksheet.getRow(0);

		int sheetIndex=workbook.getSheetIndex(Addproduct.SheetName);
		DataFormatter formatter = new DataFormatter();
		if(sheetIndex==-1)
		{
			System.out.println("No such sheet in file exists");
		} else 		{
			col_num=-1;
				for(int i=0;i&lt;Row.getLastCellNum();i++)
				{
					HSSFCell cols=Row.getCell(i);
					String colsval=formatter.formatCellValue(cols);
					if(colsval.trim().equalsIgnoreCase(ColName.trim()))
					{
						col_num=i;
						break;
					}
				}
//				
				Row= worksheet.getRow(DR);
				try
					{
					//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) {
							cell=Row.createCell(col_num);							
						}
						//Set Result is pass in that cell number
						cell.setCellValue(Ress);
										
						
					}
				catch (Exception e)
				{
				    System.out.println(e.getMessage()); 
				} 
   
		}
			FileOutputStream file_output_stream=new FileOutputStream("/home/users/shivani.kukreti/Documents/akeneo-product.xls");
			workbook.write(file_output_stream);
			file_output_stream.close();
			if(col_num==-1)	{
				System.out.println("Column you are searching for does not exist");
			}
	}
}

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;

@Test

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);

worksheet=workbook.getSheet(Addproduct.SheetName);

HSSFRow Row=worksheet.getRow(0);

int sheetIndex=workbook.getSheetIndex(Addproduct.SheetName);

DataFormatter formatter = new DataFormatter();

if(sheetIndex==-1)

{

System.out.println("No such sheet in file exists");

} else {

col_num=-1;

for(int i=0;i<Row.getLastCellNum();i++)

{

HSSFCell cols=Row.getCell(i);

String colsval=formatter.formatCellValue(cols);

if(colsval.trim().equalsIgnoreCase(ColName.trim()))

{

col_num=i;

break;

}

Row= worksheet.getRow(DR);

try

{

//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) {

cell=Row.createCell(col_num);

}

//Set Result is pass in that cell number

cell.setCellValue(Ress);

}

catch (Exception e)

{

System.out.println(e.getMessage());

}

FileOutputStream file_output_stream=new FileOutputStream("/home/users/shivani.kukreti/Documents/akeneo-product.xls");

workbook.write(file_output_stream);

file_output_stream.close();

if(col_num==-1) {

System.out.println("Column you are searching for does not exist");

}

Here Passed Results in my excel sheet are:

You may view Magento 2 Helpdesk, an all-in-one support ticketing solution to help your customers resolve their queries.

Tag(s) Dataprovider HSSFSheet TestNG WorkSheet

Category(s) Selenium TestNG

16 comments

ziv sivron Nov 27, 2022 at 01:28 PM

hi can you clarify this line please : Write obj1=new Write(); ?
im trying to implement it in my code and an unable to. i cant find which dependency allowes me to use this method. have you written it by yourself? thanks.

Komal Sharma (Moderator) May 22, 2023 at 11:47 AM
Hello Ziv Sivron,

This blog is not a maintainer blog. It is only on techincal purpose blog.

Thanks and Regards,
Uvdesk Team!!!

vassa Jul 7, 2022 at 10:07 PM

Thank you very much. Your code resolve my problem. Please continue in great work. I’m really appreciate it.

Himani Gupta (Moderator) Aug 8, 2022 at 11:34 AM
Thanks for your appreciation Vassa 🙂

sree Nov 2, 2020 at 11:16 PM

its so helpful…very clearly it was written each and every point..nic blog..expecting more information lik dis..tq

Komal Sharma (Moderator) Jun 26, 2023 at 08:01 AM
Thanks for your appreciation sree.

Vishnu Godse Aug 12, 2020 at 12:17 PM

Hello,

This is super!!

in one shot i can finish my requirement of test development.

Very much appreciated..

Thanks

Himani Gupta (Moderator) Aug 12, 2020 at 02:31 PM
Hello Vishnu Godse,
Glad to know that this guide helped you.
Thanks for appreciation and reading the blog.
Keep staying with us.
Thank You!!

Dileep Jun 11, 2020 at 10:28 AM

Good One!

Himani Gupta (Moderator) Jun 11, 2020 at 02:00 PM
Hello there,
Thanks for your appreciation.
Keep staying with us!!
Thanks & Regards
UVdesk Team

www.qacreators.com May 6, 2020 at 06:53 PM

Nice Article. I have been searching for this article in many blogs for a long time. Very good detailed explanation, found it in this website.
Keep up the good work. Expecting more this kind of content from you.

Himani Gupta (Moderator) May 14, 2020 at 11:37 AM
Hello there,
Many thanks for reading and appreciation.
Keep staying with us!!
Thanks & Regards
UVdesk Team

varaprasad Jun 27, 2019 at 07:45 AM

String value=formatter.formatCellValue(cell); I am getting error at formatter saying that it is an unresolved type though i have used below import statement
import org.apache.poi.ss.usermodel.DataFormatter;

Komal Sharma (Moderator) Jun 26, 2023 at 07:49 AM
Hello Varaprasad,

This blog is not a maintainer blog. It is only on techincal purpose blog.

Thanks and Regards,
Uvdesk Team!!!

daniel Nov 21, 2018 at 09:04 PM

http://192.168.1.91/user/login ?? it would be best if we could get this code in github, or I can upload it to a website

Komal Sharma (Moderator) Jun 20, 2023 at 12:06 PM
Hello Daniel,

This blog is not a maintainer blog. It is only on techincal purpose blog.

Kind Respects,
Uvdesk Team

Passing data to DataProvider from Excel Sheet in TestNG

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

Leave a Comment Cancel Reply

16 comments

COOKIE USAGE POLICY