Read data from Excel to DataProvider in Selenium
Read data from excel to dataprovider in selenium will explain how we can get the data from excel sheet and will pass the same to testng dataprovider. While working with the data driven testing we need to pass so much of data to the test methods as parameters. But maintaining the data is very difficult within the method as sometimes we need to pass multiple data sets to same method as parameters to test the same functionality.
To simplify this we will use TestNG DataProvider concept. Will read multiple sets of data from excel and will place the same in dataprovider object array and will pass the same to the test method as parameters. Here, we need to remember one thing is; dataprovider array values and test method parameter values must be same.
We will take one sample program to read data from excel to dataprovider. Here to interact with the excel sheet will use Apache POI api. Will read some sample data from excel to dataprovider and will print the same.
Below is the sample Excel File:
Below is the sample program:
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
public class ExcelToDataProvider {
String xlFilePath = "/KRISHNA VOLUME/Jar Files/poi-3.16-beta1/TestData.xlsx";
String sheetName = "Credentials";
ExcelApiTest eat = null;
@Test(dataProvider = "userData")
public void fillUserForm(String userName, String passWord, String dateCreated, String noOfAttempts, String result)
{
System.out.println("UserName: "+ userName);
System.out.println("PassWord: "+ passWord);
System.out.println("DateCreated: "+ dateCreated);
System.out.println("NoOfAttempts: "+ noOfAttempts);
System.out.println("Result: "+ result);
System.out.println("*********************");
}
@DataProvider(name="userData")
public Object[][] userFormData() throws Exception
{
Object[][] data = testData(xlFilePath, sheetName);
return data;
}
public Object[][] testData(String xlFilePath, String sheetName) throws Exception
{
Object[][] excelData = null;
eat = new ExcelApiTest(xlFilePath);
int rows = eat.getRowCount(sheetName);
int columns = eat.getColumnCount(sheetName);
excelData = new Object[rows-1][columns];
for(int i=1; i<rows; i++)
{
for(int j=0; j<columns; j++)
{
excelData[i-1][j] = eat.getCellData(sheetName, j, i);
}
}
return excelData;
}
}
In the above program, fillUserForm() is a test method and will accept 5 parameters. So, we need to read data from excel to dataprovider to pass these 5 parameters multiple times. For this we will use testData() method to read the data from the excel sheet using Apache POI. It takes two parameters called excel file path and sheet name. By using these two parameters it will read the excel data and store in Object array.
userFormData() is actually a dataProvider to pass the data to test method. It will get the data from the testData() method and the return type of this method is Object array. We have separated this testData() method from the dataProvider because there is a chance of using the same logic more than one test method. In this scenario we can use the same testData() method to create a separate dataProvider for each and every test method to get the parameters.
Output of the above program is:
Can get the code for how we can get row count and column count in excel here.
Please watch the youtube video for better understanding and more examples.
