Data Provider in TestNG using Excel

Data Provider in TestNG

555

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.

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.