DataProvider in TestNG using Excel


In the previous tutorial, I have 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 on Desktop. You can create a TestData folder in your project and place the excel file within it. So, my datasheet looks like 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 below-

@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(
		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 which 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 non-arg constructor, and you specify the class where it can be found in the dataProviderClass attribute.

public class DataProviderWithExcelDemo {

	WebDriver driver;

	public void setUp() {
		System.out.println("Start test");
		driver = new ChromeDriver();


	@Test(dataProvider = "excelData", dataProviderClass = ExcelDataProvider.class)
	public void search(String keyWord1, String keyWord2) {

		WebElement txtBox = driver.findElement("sb_form_q"));
		txtBox.sendKeys(keyWord1, " ", keyWord2);
		System.out.println("Keyword entered is : " + keyWord1 + " " + keyWord2);
		System.out.println("Search results are displayed.");

	public void burnDown() {


To run the code, right click and Select – TestNG Test.

The Execution status will look like as shown below

This test execution generate reports under 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 in the Test Code using DataProvider.

I hope you have enjoyed this tutorial. Happy Learning!!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s