Data Driven Testing using Excel in TestNG
Quite simply put, data-driven testing is when you store test data (input, expected output, etc) in some external storage (database, spreadsheet, xml-files, etc) and then use that data iteratively in your tests when running them.We will use Apache Poi(Excel) to drive data from Excel spreadsheet.
Apache POI (Excel) and TestNG Data Providers to accomplish this in TestNG Framework. Let's get started.
Step 1: Create a test case of Login Application with TestNG Data Provider.
Step 2: Create a Test Data sheet.
Step 3: Create functions to Open & Read data from Excel
Step 4: Create a TestNg test case for accepting data from Excel using Data Provider.
Step 5: Run the test against the Test Case name in the Test Data file.
I am going to use same class from previous post, 'DataProvider' class code and the code from previous post is below --
package automationFramework;
import java.util.concurrent.TimeUnit;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.safari.SafariDriver;
import org.testng.Assert;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
public class DataProviderTest {
public static WebDriver sDriver = new SafariDriver();
@DataProvider(name= "LoginValidation")
public static Object[][] credentials() {
return new Object[][] { { "user1", "pass1" }, { "user2", "pass2" }};
}
@Test(dataProvider="LoginValidation")
public void testOne(String sUsername,String sPassword ) throws InterruptedException {
sDriver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
//Open website
sDriver.get("http://amazon.com");
//Verify Page title
WebElement myAccountLink = sDriver.findElement(By.id("nav-link-yourAccount"));
//Adding assertion check here. Test will only continue if below statement is True.
Assert.assertTrue(myAccountLink.isDisplayed());
//My Account Link will be clicked only if the above condition is true
myAccountLink.click();
Thread.sleep(1000);
sDriver.findElement(By.xpath(".//*[@id='ap_email']")).sendKeys(sUsername);
sDriver.findElement(By.xpath(".//*[@id='ap_password']")).sendKeys(sPassword);
sDriver.findElement(By.xpath(".//*[@id='signInSubmit']")).click();
}
}
Next, Create excel spreadsheet with test data.. I created below spreadsheet -
Next, Setup Apache POI library, which allows you to read, create and edit MS Office documents using Java. Follow below steps to install, setup and implement POI Libraries.
1. Download POI libraries here - https://poi.apache.org/download.html
Add all the Jar files to Eclipse Project.
2. Next , Create a New Package file in the Project and name it as ‘testData’, by right click on the Project and select New > Package.
Place all of your test data in this folder (package) whether it is a sql file, excel file or anything meaning, save your testData file into this Package location
Create a new Class in Utility package and ‘ExcelUtils‘ that include basic read/write methods. Copy and paste below read/write basic excel reader code.
The folder so far should look like this -
Now that XLS reader is ready. Let's modify the DataProviderTest class to accept the test data from excel file.
4. Create a TestNg test case and under Annotations, check ‘@BeforeMethod‘, ‘@AfterMethod‘ & ‘DataProvider‘ and click Finish. By default it will looks like this.. We need to modify it now.
Add a method Login_data() to your Test class. This method takes two strings as input parameters.
Next, Divide the test cases into 3 parts - @BeforeMethod : Launch Firefox and direct it to the Base URL
@Test : Enter Username & Password to Login, Print console message and Log out
@AfterMethod : Close Firefox browser
Test Case will look like this:
package utility;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtils {
private static XSSFSheet ExcelWSheet;
private static XSSFWorkbook ExcelWBook;
private static XSSFCell Cell;
private static XSSFRow Row;
//This method is to set the File path and to open the Excel file, Pass Excel Path and Sheetname as Arguments to this method
public static void setExcelFile(String Path,String SheetName) throws Exception {
try {
// Open the Excel file
FileInputStream ExcelFile = new FileInputStream(Path);
// Access the required test data sheet
ExcelWBook = new XSSFWorkbook(ExcelFile);
ExcelWSheet = ExcelWBook.getSheet(SheetName);
} catch (Exception e){
throw (e);
}
}
public static Object[][] getTableArray(String FilePath, String SheetName, int iTestCaseRow) throws Exception
{
String[][] tabArray = null;
try{
FileInputStream ExcelFile = new FileInputStream(FilePath);
// Access the required test data sheet
ExcelWBook = new XSSFWorkbook(ExcelFile);
ExcelWSheet = ExcelWBook.getSheet(SheetName);
int startCol = 1;
int ci=0,cj=0;
int totalRows = 1;
int totalCols = 2;
tabArray=new String[totalRows][totalCols];
for (int j=startCol;j<=totalCols;j++, cj++)
{
tabArray[ci][cj]=getCellData(iTestCaseRow,j);
System.out.println(tabArray[ci][cj]);
}
}
catch (FileNotFoundException e)
{
System.out.println("Could not read the Excel sheet");
e.printStackTrace();
}
catch (IOException e)
{
System.out.println("Could not read the Excel sheet");
e.printStackTrace();
}
return(tabArray);
}
//This method is to read the test data from the Excel cell, in this we are passing parameters as Row num and Col num
public static String getCellData(int RowNum, int ColNum) throws Exception{
try{
Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
String CellData = Cell.getStringCellValue();
return CellData;
}catch (Exception e){
return"";
}
}
public static String getTestCaseName(String sTestCase)throws Exception{
String value = sTestCase;
try{
int posi = value.indexOf("@");
value = value.substring(0, posi);
posi = value.lastIndexOf(".");
value = value.substring(posi + 1);
return value;
}catch (Exception e){
throw (e);
}
}
public static int getRowContains(String sTestCaseName, int colNum) throws Exception{
int i;
try {
int rowCount = ExcelUtils.getRowUsed();
for ( i=0 ; i<rowCount; i++){
if (ExcelUtils.getCellData(i,colNum).equalsIgnoreCase(sTestCaseName)){
break;
}
}
return i;
}catch (Exception e){
throw(e);
}
}
public static int getRowUsed() throws Exception {
try{
int RowCount = ExcelWSheet.getLastRowNum();
return RowCount;
}catch (Exception e){
System.out.println(e.getMessage());
throw (e);
}
}
}
Now Update DataProviderWithExcel class
package automationFramework;
import org.testng.annotations.Test;
import utility.ExcelUtils;
import org.testng.annotations.BeforeMethod;
import java.util.concurrent.TimeUnit;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.safari.SafariDriver;
import org.testng.Assert;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.DataProvider;
public class DataProviderWithExcel {
WebDriver sDriver;
private String sTestCaseName;
private int iTestCaseRow;
@BeforeMethod
public void beforeMethod() {
sDriver = new SafariDriver();
sDriver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
//Open website
sDriver.get("http://amazon.com");
}
@Test(dataProvider = "LoginValidation")
public void credentials(String sUsername, String sPassword) throws Exception{
//Verify Page title
WebElement myAccountLink = sDriver.findElement(By.id("nav-link-yourAccount"));
//Adding assertion check here. Test will only continue if below statement is True.
Assert.assertTrue(myAccountLink.isDisplayed());
//My Account Link will be clicked only if the above condition is true
myAccountLink.click();
System.out.println("My Account is clicked");
Thread.sleep(1000);
sDriver.findElement(By.xpath(".//*[@id='ap_email']")).sendKeys(sUsername);
System.out.println("UserName is: " + sUsername);
sDriver.findElement(By.xpath(".//*[@id='ap_password']")).sendKeys(sPassword);
System.out.println("Password is: " +sPassword);
sDriver.findElement(By.xpath(".//*[@id='signInSubmit']")).click();
}
@AfterMethod
public void afterMethod() {
sDriver.close();
}
@DataProvider
public Object[][] LoginValidation() throws Exception{
// Setting up the Test Data Excel file
ExcelUtils.setExcelFile("//Users//User//Documents//workspace//Java//localFramework//src//testData//TestData.xlsx","Sheet1");
sTestCaseName = this.toString();
// From above method we get long test case name including package and class name etc.
// The below method will refine your test case name, exactly the name use have used
sTestCaseName = ExcelUtils.getTestCaseName(this.toString());
// Fetching the Test Case row number from the Test Data Sheet
// Getting the Test Case name to get the TestCase row from the Test Data Excel sheet
iTestCaseRow = ExcelUtils.getRowContains(sTestCaseName,0);
Object[][] testObjArray = ExcelUtils.getTableArray("//Users//User//Documents//workspace//Java//localFramework//src//testData//TestData.xlsx","Sheet1",iTestCaseRow);
return (testObjArray);
}
}
