Data Provider in TestNG using Excel

Data Provider in TestNG


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.

Subscribe to Newsletter
Get Latest Posts, Articles and Learning Lessons on Software Testing and Test Automation
You can unsubscribe at any time

Comments are closed.