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.io.IOException;
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;
import jxl.read.biff.BiffException;

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("datasheet.properties");
 //Map or = OrReader.objReader("OR");
 // create map to store web elements
 data = new HashMap<String, String>();
 Workbook workbook = Workbook.getWorkbook(new java.io.File("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);
 if(ObjectName.getContents()!="")
 {
 data.put(ObjectName.getContents(), ObjectValue.getContents());
 
 System.out.println("Input VariableName: "+ObjectName.getContents()+"Variable Value from Excel::"+ ObjectValue.getContents() );
 
 }
 else
 
 break;
 
 }
 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 jxl.read.biff.BiffException;
import utilityComponents.DataReader;

import org.testng.annotations.BeforeMethod;

import java.io.IOException;
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 {
 System.out.println(testData);
 
 }
 @BeforeMethod
 public void beforeMethod() {
 }

 @AfterMethod
 public void afterMethod() {
 }


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

You might also like More from author

Loading...