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.
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
//Usage in the TestNG test class DataDrivenTest.java
Important Note:
In the above test class we used
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:
You need to use the below jars for reading from an excel file.
- poi-3.7-20101029.jar
- poi-ooxml-3.7-20101029.jar
- poi-ooxml-schemas-3.7-20101029.jar
- xmlbeans-2.3.0.jar
- dom4j-1.6.1.jar
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:
- Make sure all the above mentioned jars are added to your build path.
- The object size defined in the data provider is equal to the number of parameters defined in the test method.
- You are reading an existing cell value with a valid row and column index.
