Passing data to DataProvider from Excel Sheet in TestNG

Shivani Kukreti

16 May 2023

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.