Automation testing for any website or product is critical in terms of time taken, quality, and cost of the project delivery. Though there are many ways to perform automation testing – Unit, Regression, Smoke, and others – the Data-driven method is repeatedly proved to be the simplest-yet-flexible way of all, since it bridges the gap between the traditional recordable automated tests and the new-age ones. The only problem quoted against the former method was its inflexible and non-extendable nature; however, data-driven testing has overcome those complaints and proved to be highly flexible and extendable.

How Data-Driven Automation Testing Works?

The need for automation arises when testers are persuaded to repeat the same task again and again. Such redundant activities create boredom among the testers and thereby will impact the productivity. On the other hand, automation testing has one big con – the compulsion to maintain a ton of test data. Hard-coding each and every data in the scripts is proved to be cumbersome and, moreover, maintaining the hard-coded script becomes tedious when switching over from one test environment to another.

In order to avoid such situations, it’s advisable, and actually convenient, to keep data for automation tests in a separate class file or external file formats like Excel, Word, text file or even from the database tables. These data are then fed to the automation scripts as parameters at the time of execution.

In this article, you’ll be learning how to extract data from external files onto automated test scripts. Expect a plethora of codes and syntaxes below (for you nerdy testers!). Sorry you average joe! This perhaps isn’t your cup of tea.

Implementation of Data-Driven Automation

Data-driven automation can be implemented using selenium webdriver and TestNG, because it’s an open-source framework and Java supports the external-file libraries and inbuilt functions for implementation.

Following libraries in Java are used to read data from external files:

  • JExcel
  • Apache POI
  • Aspose.cells

Apache POI is the most common library used for reading the values from the various formats of external files such as .xls, .xlsx, .ppt, .doc, .txt and so on. On the other hand, JExcel can only read from .xl

Though there are loads of file formats, testers prefer to use Excel because it’s easy to add extensive data, extract cell-wise or row-wise data, and columnize based on categories. We have two formats to read the values from the excel files:

  • HSSF(Horrible Spreadsheet Format) – Reads and Writes (.xls) files.
  • XSSF(XML Spreadsheet Format) – Reads and writes (.xlsx) files.

There are also some other formats which will be helpful in reading and writing the values from other Microsoft Office formats.

Before starting the execution, it’s important to add the POI Jar files. Follow the steps below to add Jar files:

  1. Go to build path and select Configure build path.
  2. Click on Add external JAR files.
  3. Search for POI jar file from the system and add it.

Note: You can download the Apache JAR files here.

There’s a prerequisite to know the following parameters as well:

  • Excel Filename.
  • Filepath.
  • Sheetname.
  • Permission.
  • Structure of the Excel sheet.

Data in Excel

Steps to Read the data from Excel Sheet

  • Open the Excel sheet using XSSF or HSSF.
  • Go to the specific sheet using XSSFSheet or HSSFSheet→getSheet.
  • Go to the specific row using XSSFRow or HSSFRow→getRow.
  • Go to the specific cell using XSSFCell or HSSFCell→getCell.
  • Read the data from a particular cell by using getStringCellValue().

PseudoCode

File src = new File(“path to the excel file”);
XSSFWorkbook wb = new XSSFWorkbook(src);
XSSFSheet sheet = wb.getSheet(“Sheetname”);
XSSFRow row = sheet.getRow(“Rowcount”);
XSSFCell cell= row.getCell();
System.Out.Println(cell.getStringCellValue());

In the case of a cell containing numerical values, it’s mandatory to add a string “`” as a prefix.

Sample code

package passdatasfromexcel;
import java.io.File;
import java.io.IOException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.CellType;
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;
import org.testng.annotations.Test;

public class ReadExcel {

@DataProvider(name = “fetchData”)
public Object[][] readExcel(String sheetName) throws InvalidFormatException, IOException {

//Load the Excel File
File src = new File(“./data/”+sheetName+”.xlsx”);

//Open the Book
XSSFWorkbook wb = new XSSFWorkbook(src);

//Go to Sheet
XSSFSheet sheet = wb.getSheet(“Sheet1”);

//Row Count
int rowCount = sheet.getLastRowNum();
System.out.println(rowCount);

//Column Count
int column = sheet.getRow(0).getLastCellNum();
System.out.println(column);
Object[][] data = new Object[rowCount][column];


// Row – for
for (int i = 1; i <=rowCount; i++) {
XSSFRow row = sheet.getRow(i);

//Column – for
for (int j = 0; j < column; j++) {
XSSFCell cell = row.getCell(j);
System.out.println(cell.getStringCellValue());
data[i-1][j] = cell.getStringCellValue();
}
}
return data;
}
}

Automation Script

Integrate the Sample code with the automation script

package cclogin;

import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.remote.RemoteWebDriver;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;

//importing the Excel code sheet
import utils.Excelutility;

public class CClogin extends Excelutility{
public String testCaseName, testDescription, category, dataSheetName, browserName;

@BeforeClass
public void setData() {
testCaseName = “CClogin”;
testDescription = “login in account”;
dataSheetName = “blogdatas”; //Excel sheet name
browserName = “Chrome”;
}

@Test(dataProvider = “fetchData”)

public void loginpage(String url, String uname, String pwd) {
RemoteWebDriver driver;
System.setProperty(“webdriver.chrome.driver”, “./drivers/chromedriver.exe”);
driver = new ChromeDriver();
driver.get(url);
driver.findElementById(“email”).sendKeys(uname);
driver.findElementById(“pass”).sendKeys(pwd);
driver.findElementById(“send2”).click();
}
}

Explanation

In the event of executing the above code, it extends the utils.Excel utility and takes the readExcel method as a reference. Furthermore, with the help of DataProvider it takes the value from the excel and passes through the automation scripts via the parameters declared under the public void class.

References

TestNG Data Provider with Excel
Data Driven Framework (Apache POI – Excel)

Hope this turned out to be useful.

 




Ready to Write an Interesting blog in QA Touch?


If you have an interesting blog post you would want to share with the Testing Community, please forward it to info@qatouch.com along with your picture and a short bio. Our editorial team will review and publish it.

Subscribe to our blog

Prasanna Venkatesh

Prasanna Venkatesh

Prasanna Venkatesh – a Senior Quality Analyst in DCKAP, has experience in successfully delivering Quality Products and Services. He is passionate about everything in QA and takes ownership of every task. Loves to play cricket and chess.

3
Leave a Reply

avatar
3 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
Aske Recent comment authors
trackback
Peter Hosey

Youre so cool! I dont suppose Ive read something like this before. So nice to seek out any person with some authentic ideas on this subject. realy thanks for beginning this up. this web site is one thing that’s needed on the web, somebody with somewha…

trackback
John wills

Wonderful write ups, Cheers!

Aske
Guest
Aske

Dynamic data-driven automation can utilize a range of data sources; spreadsheets, databases, web services, and more. If you’re interested in a codeless way to drive test automation with data, I recommend this article: https://www.leapwork.com/blog/excel-data-dynamic-automation