In the previous tutorial, I explained the DataProvider in TestNG without using Excel. In this tutorial, I will explain the use of Excel in DataProvider for TestNG.
I have created an Excel – SearchInBing.xlsx and placed it on the Desktop. You can create a Test Data folder in your project and place the Excel file within it. So, my datasheet looks like the below:
Next, we will create a DataProvider method that will use another method to read the Excel file & create a 2D object from the row & column values of the Excel and return the same value, so that our test script can use it. The code for it would look like the below:
import org.apache.poi.ss.usermodel.Cell;
import org.testng.annotations.DataProvider;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelDataProvider {
@DataProvider(name = "excelData")
public Object[][] excelDataProvider() throws IOException {
// We are creating an object from the excel sheet data by calling a method that
// reads data from the excel stored locally in our system
Object[][] arrObj = getExcelData(
C:\\Users\\Vibha\\Desktop\\SearchInBing.xlsx","Details");
return arrObj;
}
// This method handles the excel - opens it and reads the data from the
// respective cells using a for-loop & returns it in the form of a string array
public String[][] getExcelData(String fileName, String sheetName) throws IOException {
String[][] data = null;
try {
FileInputStream fis = new FileInputStream(fileName);
XSSFWorkbook workbook = new XSSFWorkbook(fis);
XSSFSheet sheet = workbook.getSheet(sheetName);
XSSFRow row = sheet.getRow(0);
int noOfRows = sheet.getPhysicalNumberOfRows();
int noOfCols = row.getLastCellNum();
Cell cell;
data = new String[noOfRows - 1][noOfCols];
for (int i = 1; i < noOfRows; i++) {
for (int j = 0; j < noOfCols; j++) {
row = sheet.getRow(i);
cell = row.getCell(j);
data[i - 1][j] = cell.getStringCellValue();
}
}
} catch (Exception e) {
System.out.println("The exception is: " + e.getMessage());
}
return data;
}
}
Now, create a class that contains the test code. By default, the data provider will be looked for in the current test class or one of its base classes. If you want to put your data provider in a different class, it needs to be a static method or a class with a no-arg constructor, and you specify the class where it can be found in the data provider class attribute.
import io.github.bonigarcia.wdm.WebDriverManager;
import org.openqa.selenium.By;
import org.openqa.selenium.Keys;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import org.testng.Assert;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.BeforeMethod;
import org.testng.annotations.Test;
public class DataProviderWithExcelDemo {
WebDriver driver;
@BeforeMethod
public void setUp() {
System.out.println("Start test");
WebDriverManager.chromedriver().setup();
driver = new ChromeDriver();
driver.get("https://www.bing.com");
driver.manage().window().maximize();
}
@Test(dataProvider = "excelData", dataProviderClass = ExcelDataProvider.class)
public void search(String keyWord1, String keyWord2) {
WebElement txtBox = driver.findElement(By.id("sb_form_q"));
txtBox.sendKeys(keyWord1, " ", keyWord2);
System.out.println("Keyword entered is : " + keyWord1 + " " + keyWord2);
txtBox.sendKeys(Keys.ENTER);
System.out.println("Search results are displayed.");
System.out.println("RESULT: "+ driver.getTitle());
Assert.assertTrue(driver.getPageSource().contains(keyWord1));
}
@AfterMethod
public void burnDown() {
driver.quit();
}
}
To run the code, right-click and Select – TestNG Test.
The Execution status will look like this, as shown below:
This test execution generates reports under the test-output folder.
We are concerned about two reports – index.html and emailable-report.html.
Below is the image of emailable-report.html.
Below is the image of index.html.
See how easy it is to read data from Excel and use it in the Test Code using DataProvider.
I hope you have enjoyed this tutorial. Happy Learning!!
QA Automation Expert 