Data Provider in TestNG using Excel


Data Provider in TestNG using Excel

Lets take a look at how make your selenium script data driven using Excel. we will see how to write the code step by step.


Step1 : Design the Excel format. we will create the Excel with below format.


Step2: Create a class which will read the excel file and store the data into map. Key will be Variable name and Value will be value mentioned in excel. I have used the JXL java api to read the excel sheet. you can use any other api for the same. logic will remain same.

package utilityComponents;

import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class DataReader {
 public static Map<String, String> data = new HashMap<String,String>();
 public static Object[][] dataset;

 public static Object[][] testcase(String TestID) throws IOException, BiffException
 Properties prop = ReadConfig.config("");
 //Map or = OrReader.objReader("OR");
 // create map to store web elements
 data = new HashMap<String, String>();
 Workbook workbook = Workbook.getWorkbook(new"Excel sheet path"));
 Sheet sheet = workbook.getSheet("Sheet name");
 int rowcount = sheet.getRows();
 System.out.println("Datasheet inputs");
 for(int i=1;i<rowcount;i++)

 Cell ObjectName = sheet.getCell(0, i);
 Cell ObjectValue = sheet.getCell(1, i);
 data.put(ObjectName.getContents(), ObjectValue.getContents());
 System.out.println("Input VariableName: "+ObjectName.getContents()+"Variable Value from Excel::"+ ObjectValue.getContents() );
 Object[] keys = data.keySet().toArray();
 Object[] values = data.values().toArray();
 dataset = new String[keys.length][values.length];
 for(int i=0;i<keys.length;i++) {
 dataset[0][i] = (String)keys[i];
 dataset[1][i] = (String)values[i];
 return dataset;


Step3: lets now create a test class where we will call this above method and get the data.

package SmokeTestSuite;

import org.testng.annotations.Test;

import utilityComponents.DataReader;

import org.testng.annotations.BeforeMethod;

import java.util.Map;

import org.testng.annotations.AfterMethod;
import org.testng.annotations.DataProvider;

public class DataDrivenTest {
 public static Map<String, String> data;
 @Test(dataProvider = "dp")
 public void f(Map<String, String> testData) throws BiffException, IOException {
 public void beforeMethod() {

 public void afterMethod() {

 public Object[][] dp() throws BiffException, IOException {

 Object[][] dpdataset = (Object[][]) DataReader.testcase("ID0001");
 return dpdataset;

That’s all.. i hope you are able to use these code to make your script data driven.

Leave A Comment