Data driven testing in Selenium WebDriver with TestNG using Excel

When we want to implement data driven testing using Excel, you need to understand how to read from an Excel sheet. You can read an Excel sheet by using Apache POI. 

You need to use the below jars for reading from an excel file. 

1. poi-3.7-20101029.jar
2. poi-ooxml-3.7-20101029.jar
3. poi-ooxml-schemas-3.7-20101029.jar
4. xmlbeans-2.3.0.jar
5. dom4j-1.6.1.jar

I am using Apache POI 3.7 but you can download the latest jars from downloads page of Apache POI(http://poi.apache.org/download.html). Make sure you have added all the above jars to your project build path.

An example excel file(either .xslx or .xls formats) is given below:

The below self explanatory class is used for reading any cell of an excel sheet.
//Keywords class DataDrivenExcel.java

package example.aks.data.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * This class allows us the use of an Excel spreadsheet to provide input data to
 * a test or set of tests.
 * 
 * @author A. K. Sahu
 * 
 */

public class DataDrivenExcel {

 private Workbook wb;
 private Sheet ws;

 /**
  * Opens a excel sheet
  * 
  * @param fileName
  *            name of the file where you want data
  * @param sheetName
  *            name of the sheet in the excel file
  */
 public DataDrivenExcel(String fileName, String sheetName) {
  try {
   if (fileName.indexOf("xlsx") < 0) { //for .xls format
    wb = new HSSFWorkbook(new FileInputStream(new File(fileName)));
    ws = wb.getSheet(sheetName);
   } else { //for .xlsx format
    wb = new XSSFWorkbook(fileName);
    ws = (XSSFSheet) wb.getSheet(sheetName);
   }
  } catch (IOException io) {
   System.err.println("Invalid file '" + fileName
     + "' or incorrect sheet '" + sheetName
     + "', enter a valid one");
  }
 }

 /**
  * Gets a cell value from the opened sheet
  * 
  * @param rowIndex
  *            starting with 0 index
  * @param columnIndex
  *            starting with 0 index
  * @return
  */
 public String getCell(int rowIndex, int columnIndex) {
  Cell cell = null;
  try {
    cell = ws.getRow(rowIndex).getCell(columnIndex);
  } catch (Exception e) {
   System.err.println("The cell with row '" + rowIndex + "' and column '"
     + columnIndex + "' doesn't exist in the sheet");
  }
  return new DataFormatter().formatCellValue(cell);
 }
}

The test class that uses the above keywords of the DataDrivenExcel class looks like below which is also self explanatory.
//Usage in the TestNG test class DataDrivenTest.java

package example.aks.data.test;

import java.util.ArrayList;

import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

import example.aks.data.util.DataDrivenExcel;

/**
 * This class explains the usage of data driven excel
 * 
 * @author A. K. Sahu
 * 
 */
public class DataDrivenTest {

 @DataProvider(name = "loginToAppWithAllRoles")
 public Object[][] getLoginDataForAllRoles() throws Exception {

  DataDrivenExcel userData = new DataDrivenExcel(
    "D:/workspace/DataDriven/data/SampleExcel.xlsx", "Sheet1");

  ArrayList<Object> dataList = new ArrayList<Object>();

  int i = 1;// excluding header row
  int totalRows = 6;
  while (i < totalRows) {
   System.out.println("loginToAppWithAllRoles : line : " + i);

   Object[] dataLine = new Object[4];
   dataLine[0] = userData.getCell(i, 0);
   dataLine[1] = userData.getCell(i, 1);
   dataLine[2] = userData.getCell(i, 2);
   dataLine[3] = userData.getCell(i, 3);

   dataList.add(dataLine);

   i++;
  }

  Object[][] data = new Object[dataList.size()][];
  for (i = 0; i < dataList.size(); i++)
   data[i] = dataList.get(i);

  return data;
 }

 @Test(dataProvider = "loginToAppWithAllRoles", 
       description = "Login with different roles")
 public void testLogin(String userID, String username, String password,
   String role) {

  WebDriver driver = new FirefoxDriver();

  driver.get("http://yoursiteurl.com");

  // Do required actions using the input data, i am just printing here
  System.out.println("userID:" + userID);
  System.out.println("username:" + username);
  System.out.println("password:" + password);
  System.out.println("role:" + role);

  driver.quit();
 }
}

Similarly, you can use an excel file with .xls format.
Important Note: 
In the above test class we used

Object[] dataLine = new Object[4];

Number here should match with the number of parameters for the test case that using this dataprovider.

The  output after executing the tests looks like below:
TestNG Console

Console output

Note: While you are running the tests if you encounter any problem recheck about the below:

1. Make sure all the above mentioned jars are added to your build path.
2. The object size defined in the data provider is equal to the number of parameters defined in the test method.
3. You are reading an existing cell value with a valid row and column index.