_edited_edited.png)
What is Data Driven Testing?
Data-driven testing (DDT) is a testing method that uses different types of data to run the same test case or script. It validates the application behavior under a wide range of input conditions and executes the same test logic again for each data set. So, the functionality of code changes based on the test data. It reduces the complexity of code and scenario or test cases.The test data uses external sources like Excel files, CSV files, Text files, Json files or databases.
How it works…
Selenium runs the same script to read the data from the source file and push it to the required application. In this case, the actual Output is recorded during the test execution phase, As per the usual process the expected output is usually derived, when the test case is created. The actual output represents the code response, And the expected result is the one, which will be compared against the test case by the tester. Eventually we can compare the expected output with the output from the application. A simple example is testing the Login page, we need dozens of credential pairs to validate the login page. To cover all the scenarios, the data set has positive and negative test scenarios such as invalid credential scenarios etc. In such cases, we can use data-driven testing methodology. And this is very effective in testing the various functionality with efficient coding.
How to implement Data Driven Testing?
The prerequisites are…
IDE - intellij/Eclipse
Framework -Testng
Excel File (.xls/.xlsx)
To implement CRUD operation on External File we need Apache POI (Poor Obfuscation Implementation) library. Apache POI is an API, That was provided by Apache foundation. Which is a collection of different java libraries. These libraries facilitate reading, writing and to manipulating different MS files such as excel, power-point, and word files.
Maven Dependencies:
In Maven add below dependencies of Apache poi and poi-ooxml in pom.xml then click-on Save to install the java library jars in the required Maven project.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.4</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.4</version>
</dependency>
The classes & methods in the org.apache.poi.hssf.usermodel package helps to perform CRUD operation on Excel with extension of “.xls” files are as follows,
HSSFWorkbook – Represents a Workbook in the .xls format
HSSFSheet – Represents a Sheet in the .xls workbook
HSSFRow – Represents a Row in the .xls file
HSSFCell – Represents a Cell in the Row of the .xls file
For the extension of “.xlxs” the file are,
XSSFWorkbook – Represents a Workbook in the .xlsx format
XSSFSheet – Represents a Sheet in the .xlsx workbook
XSSFRow – Represents a Row in the .xlsx file
XSSFCell – Represents a Cell in the Row of the .xlsx file
Lets create a Data Driven framework for the login page of SauceDemo.com...
Excel File: Created to handle below mentioned Twenty one Scenarios :
Excel Reader Java classes and Methods:
Step 1:
Create New folder as Testdata and add a required Excel file(.xlsx)in the folder.
Step 2:
Create file input Stream class and constructor
FileInputStream class is useful to read data from a file in the form of sequence of bytes.
FileInputStream(File file) Creates an input file stream to read from the specified File object.
Step 3:
Create an XSSFWorkbook class to read the file.
Step 4:
Create XSSFSheet class to open a sheet with sheet name or sheet number using getSheetName()/getSheetAt() .
Step 5:
Count the Row count and Cells count using getLastRowNum() and getLastCellNum() to iterate the loop.
Step 6.
Get the Cell value and apply the condition to identify the cell type and get the value based on cell type using a function like below example.
Sample code to read all the values from the excel based on the cell type:
String path=".//src/test/resources/Testdata/login_testdata.xlsx";
FileInputStream file = new FileInputStream(path);
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
int rows=sheet.getLastRowNum();
int cols=sheet.getRow(0).getLastCellNum();
for(int i=1; i<=rows;i++) {
XSSFRow row = sheet.getRow(i);
for(int j=0;j<cols;j++) {
XSSFCell c1 =row.getCell(j);
if(c1!=null) {
switch(c1.getCellType()) {
case STRING:
System.out.println(c1.getStringCellValue());
break;
case NUMERIC:
System.out.println(c1.getNumericCellValue());
break;
case BOOLEAN:
System.out.println(c1.getBooleanCellValue());
break;
}}}}
Code Using Data Formatter….
DataFormatter contains methods for formatting the value which is stored in a Cell. This can be useful for reports and GUI presentations, DataFormatter is to display data as it is in Excel. And DataFormatter Supports the various formats such as currency, SSN, percentages, decimals, dates, phone numbers, zip codes, etc.
XSSFWorkbook workbook=new XSSFWorkbook(path);
XSSFSheet sheet = workbook.getSheetAt(0);
DataFormatter data = new DataFormatter();
int rows=sheet.getLastRowNum();
int cols=sheet.getRow(0).getLastCellNum();
for(int i=1; i<=rows;i++) {
for(int j=0;j<cols;j++) {
System.out.println(data.formatCellValue(sheet.getRow(i).getCell(j))); }}}
Excel Reader for the above Example Application Login page :
This code reads the “login” sheet from the workbook and it collects the value of the cell with the header's username and password. Script reads the credential’s data and sends it to the login page of the application. It validates the data and prints the result whether it's a positive or negative scenario.
XSSFWorkbook workbook=new XSSFWorkbook(path);
int count=workbook.getNumberOfSheets();
for(int k=0;k<count;k++) {
if(workbook.getSheetName(k).equalsIgnoreCase("login")) {//irrespective of case(upper/lower)
XSSFSheet sheet=workbook.getSheetAt(k);
DataFormatter dataf = new DataFormatter();
int rows=sheet.getLastRowNum();// count of rows
//int cols=sheet.getRow(0).getLastCellNum();// count of cells
XSSFRow r = sheet.getRow(0);
for(int i=1; i<=rows;i++) {
driver.navigate().to(Url);
WebElement username = driver.findElement(By.id("user-name"));
WebElement password = driver.findElement(By.id("password"));
WebElement login = driver.findElement(By.id("login-button"));
XSSFRow row = sheet.getRow(i);// reads cell under username header
if(dataf.formatCellValue(r.getCell(0)).equalsIgnoreCase("username")) {
username.clear();
username.sendKeys(dataf.formatCellValue(row.getCell(0)));}
if(dataf.formatCellValue(r.getCell(1)).equalsIgnoreCase("password"))
{ // reads cell under password header
password.clear();
password.sendKeys(dataf.formatCellValue(row.getCell(1)));}
String page=driver.getCurrentUrl();
if(page.equals(Url))
{
System.out.println("Negative");
l.add("Test Data "+i+" Negative");
}
else if(page.equals(login_url)) {
System.out.println("Positive");
l.add("Test Data "+i+" Positive");
}
How to write in an Excel file?
Below mentioned code will print the output in the same excel, which we will use for testing. It will create a new column called Actual output, that will be next to the expected output column. And the code will print the actual result/output data in the newly created column(Actual output) for each of the test data. Then we can compare the results to filter the defects.
( In the code The setCellValue() method is to set the value in a particular cell.)
XSSFWorkbook workbook=new XSSFWorkbook(path);
int count=workbook.getNumberOfSheets();
for(int k=0;k<count;k++) {
if(workbook.getSheetName(k).equalsIgnoreCase("login")) {//irrespective of case(upper/lower)
XSSFSheet sheet=workbook.getSheetAt(k);
DataFormatter dataf = new DataFormatter();
int rows=sheet.getLastRowNum();// count of rows
int cols=sheet.getRow(0).getLastCellNum();// count of cells
XSSFRow row = sheet.getRow(0); // Row 0 is the first row
Cell cell = row.createCell(cols); // Column cols is next to the existing value in the row
cell.setCellValue("Actual Output");
int j=0;
for(int i=1; i<=rows;i++) {
XSSFRow r = sheet.getRow(i);
Cell c = r.createCell(cols);
c.setCellValue(l.get(j));
j=i;
}
}}
FileOutputStream outputStream = new FileOutputStream(path);
workbook.write(outputStream);
workbook.close();
outputStream.close();
Output of Above code in Excel file -
We can compare actual output value against expected output and identify the defects. If there are any mismatches. Then those are known as defects. The actual result can be used to update the status for the scenario, it can be pass or fail. And in case if the expected result doesn't match with the actual result, a defect will be logged. In the below result, there is a mismatch/defect in the highlighted Test Data.
Conclusion:
Data Driven Framework is good practice in automation. And Excel files are easy to handle. Which helps to reduce test cases and scripts.We have discussed the most explicit way to read the excel file, but it covered the common classes and methods of Apache POI. It has multiple ways, iterators and conditions to control the dynamic Excel workbook.
Quote: “Curiosity is the wick in the candle of learning.”―William Arthur Ward.
Kommentare