In this tutorial, we will change the font style in Excel using Apache POI.
What is Apache POI?
Apache POI, where POI stands for (Poor Obfuscation Implementation) is the Java API for Microsoft Documents. It offers a collection of Java libraries. These libraries help us to read, write, and manipulate different Microsoft files such as Excel sheets, PowerPoint, and Word files.
Add the below mentioned dependencies to the project. The latest dependencies can be downloaded from Maven.
<!-- POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.3.0</version>
</dependency>
<!-- POI XML -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.3.0</version>
</dependency>
Every system comes bundled with a huge collection of fonts such as Arial, Impact, Times New Roman, etc. The collection can also be updated with new fonts, if required. Similarly there are various styles in which a font can be displayed, for example, bold, italic, underline, strike through, etc.
Apache POI provides methods to handle font in excel sheet. We can create font, set color, set size etc. The Font is an interface which provides methods to handle the font.
Font font = workbook.createFont();
Implementation
Step 1 – Create a blank workbook.
XSSFWorkbook workbook = newXSSFWorkbook();
Step 2 – Create a blank sheet and assign name to the sheet – Example_Sheet.
Step 7– Write the workbook to a File and close the workbook. Catches any IOException errors that might occur during the file creation or writing process.
Below is the sample code for changing the font style in the excel in Java.
package com.example.Excel;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class ApplyFont_Excel {
public static void main(String[] args) throws IOException {
// create blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Example_Sheet");
//Create a row
Row row = sheet.createRow(0);
//Create a cell
Cell cell = row.createCell(0);
//Set the value of the cell
cell.setCellValue("Happy Days");
//Create a new font
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 24);
font.setFontName("Arial");
font.setBold(true);
font.setItalic(true);
font.setColor(IndexedColors.DARK_RED.getIndex());
font.setUnderline(Font.U_SINGLE);
//Create a cell style and set the font
CellStyle style = workbook.createCellStyle();
style.setFont(font);
//Apply the style to the cell
cell.setCellStyle(style);
//Write the output to the file
try {
FileOutputStream outputStream = new FileOutputStream("src/test/StyledCell.xlsx");
workbook.write(outputStream);
outputStream.close();
System.out.println("StyledCell.xlsx Workbook is successfully created");
} catch (IOException e) {
e.printStackTrace();
} finally {
workbook.close();
}
}
}
The output of the above program is
StyledCell.xlsx excel is created in src/test folder.
Open the excel and it looks like the below shown image:
To apply different font styles to different rows in an Excel sheet using Apache POI, you can define separate CellStyle objects, each associated with a unique Font, and apply these styles to the desired cells.
package com.example.Excel;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class ApplyFontMultipleRows_Excel {
public static void main(String[] args) throws IOException {
// create blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Example_Sheet");
//Create a new font1
Font font1 = workbook.createFont();
font1.setFontHeightInPoints((short) 12);
font1.setFontName("Arial");
font1.setBold(true);
font1.setItalic(true);
font1.setColor(IndexedColors.DARK_RED.getIndex());
font1.setUnderline(Font.U_SINGLE);
//Create a new font2
Font font2 = workbook.createFont();
font2.setFontHeightInPoints((short) 14);
font2.setFontName("Times New Roman");
font2.setBold(true);
font2.setItalic(true);
font2.setColor(IndexedColors.BLUE.getIndex());
//Create a new font3
Font font3 = workbook.createFont();
font3.setFontHeightInPoints((short) 16);
font3.setFontName("Courier New");
font3.setBold(true);
font3.setColor(IndexedColors.GREEN.getIndex());
//Create a cell style and set the font
CellStyle style1 = workbook.createCellStyle();
style1.setFont(font1);
CellStyle style2 = workbook.createCellStyle();
style2.setFont(font2);
CellStyle style3 = workbook.createCellStyle();
style3.setFont(font3);
// Apply styles to rows
Row row1 = sheet.createRow(0);
Cell cell1 = row1.createCell(0);
cell1.setCellValue("Underlined Italic Bold Arial");
cell1.setCellStyle(style1);
Row row2 = sheet.createRow(1);
Cell cell2 = row2.createCell(0);
cell2.setCellValue("Bold Italic Times New Roman");
cell2.setCellStyle(style2);
Row row3 = sheet.createRow(2);
Cell cell3 = row3.createCell(0);
cell3.setCellValue("Courier New");
cell3.setCellStyle(style3);
//Write the output to the file
try {
FileOutputStream outputStream = new FileOutputStream("src/test/FontStyle.xlsx");
workbook.write(outputStream);
outputStream.close();
System.out.println("Workbook FontStyle.xlsx is created");
} catch (IOException e) {
e.printStackTrace();
} finally {
workbook.close();
}
}
}
The output of the above program is
Open the excel and it looks like the below shown image:
In this example: 1. We created three different “Font“ objects with different styles (Underlined Italic Bold Arial, Times New Roman Bold Italic, and Courier New Italic). 2. We created three corresponding “CellStyle” objects and set the respective fonts. 3. We applied these styles to specific cells in different rows.
Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!! Cheers!!
A keyword-driven framework is a software testing framework that separates the test script logic from the test data through a set of keywords or actions.
1. Keywords: Keywords or actions represent the basic building blocks of the test script. Examples of keywords can include “click,” “sendKeys,” “verifyText,” etc. These keywords are defined in a central repository or spreadsheet, along with the associated test data and expected results.
2. Test Data: Test data is the input or parameters required to perform a specific keyword action. It can be stored in a separate data source such as an Excel spreadsheet or XML file. Test data includes information like usernames, passwords, URLs, input values, and expected outcomes.
3. Test Scripts: Test scripts are developed to execute the keywords. Each test script consists of a series of actions driven by the keywords. The test script fetches the keyword from the central repository and performs the associated action using the test data. It also captures and verifies the results against the expected outcome.
4. Central Repository: The central repository contains all the keywords, associated test data, and expected results. It acts as a bridge between test scripts and test data, allowing for easy maintenance and modification.
Project Structure
Here is the final snapshot of our project.
Dependency List
Selenium – 4.21.0
TestNG – 7.10.2
Apache POI – 5.2.5
Commons – 2.16.1
Maven Surefire – 3.2.5
Maven Compiler – 3.13.0
Java 17
Maven – 3.9.6
Implementation Steps
Step 1- Download and Install Java
Selenium needs Java to be installed on the system to run the tests. Click here to learn How to install Java.
Step 2 – Download and setup Eclipse IDE on the system
The Eclipse IDE (integrated development environment) provides strong support for Java developers, which is needed to write Java code. Click here to learn How to install Eclipse.
Step 3 – Setup Maven
To build a test framework, we need to add many dependencies to the project. It is a very tedious and cumbersome process to add each dependency manually. So, to overcome this problem, we use a build management tool. Maven is a build management tool that is used to define project structure, dependencies, build, and test management. Click here to learn How to install Maven.
Step 6 – Create a Java Keyword Class for each page
In this example, we will access 2 web pages, “Login” and “Home” pages.
Hence, we will create 2 Java classes for keywords – LoginPageKeywords.java and HomePageKeywords.java and a BasePage class to initialize the driver using PageFactory.
BasePage
package com.example.pages;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.support.PageFactory;
public class BasePage {
public WebDriver driver;
public BasePage(WebDriver driver) {
this.driver = driver;
PageFactory.initElements(driver,this);
}
}
LoginPageKeywords
package com.example.keywords;
import com.example.utils.ExcelUtils;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.support.FindBy;
public class LoginPageKeywords extends BasePage{
public LoginPageKeywords(WebDriver driver) {
super(driver);
}
@FindBy(name = "username")
public WebElement userName;
@FindBy(name = "password")
public WebElement password;
@FindBy(xpath = "//*[@class='oxd-form']/div[1]/div/span")
public WebElement missingUsernameErrorMessage;
@FindBy(xpath = "//*[@class='oxd-form']/div[2]/div/span")
public WebElement missingPasswordErrorMessage;
@FindBy(xpath = "//*[@class='oxd-form']/div[3]/button")
public WebElement loginBtn;
@FindBy(xpath = "//*[@id='app']/div[1]/div/div[1]/div/div[2]/div[2]/div/div[1]/div[1]/p")
public WebElement errorMessage;
public void enterUsername(String strUserName) {
userName.sendKeys(strUserName);
}
public void enterPassword(String strPassword) {
password.sendKeys(strPassword);
}
public void login() {
loginBtn.click();
}
public void login(String strUserName, String strPassword) {
userName.sendKeys(strUserName);
password.sendKeys(strPassword);
}
public String getMissingUsernameText() {
return missingUsernameErrorMessage.getText();
}
public String getMissingPasswordText() {
return missingPasswordErrorMessage.getText();
}
public String getErrorMessage() {
return errorMessage.getText();
}
public LoginPageKeywords saveTestResults(int row, int column) {
ExcelUtils.rowNumber = row ;
ExcelUtils.columnNumber = column;
return this;
}
}
HomePageKeywords
package com.example.keywords;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.support.FindBy;
public class HomePageKeywords extends BasePage {
public HomePageKeywords(WebDriver driver) {
super(driver);
}
@FindBy(xpath = "//*[@id='app']/div[1]/div[1]/header/div[1]/div[1]/span/h6")
public WebElement homePageUserName;
public String verifyHomePage() {
return homePageUserName.getText();
}
}
Step 7 – Create an ExcelUtils Class
To manipulate Excel files and do Excel operations, we should create an Excel file and call it “ExcelUtils” under the utilspackage as shown below.
package com.example.utils;
import org.apache.poi.ss.usermodel.DataFormatter;
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 java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelUtils {
public static String testDataExcelPath = null; //Location of Test data excel file
private static XSSFWorkbook excelWorkBook; //Excel WorkBook
private static XSSFSheet excelWorkSheet; //Excel Sheet
private static XSSFCell cell; //Excel cell
private static XSSFRow row; //Excel row
public static int rowNumber; //Row Number
public static int columnNumber; //Column Number
public static FileInputStream ExcelFile;
public static DataFormatter formatter;
public static FileOutputStream fileOut;
// This method has two parameters: "Test data excel file name" and "Excel sheet name"
// It creates FileInputStream and set excel file and excel sheet to excelWBook and excelWSheet variables.
public static void setExcelFileSheet(String sheetName) throws IOException {
testDataExcelPath = Constants.currentDir + Constants.resourcePath;
// Open the Excel file
ExcelFile = new FileInputStream(testDataExcelPath + Constants.testDataExcelFileName);
excelWorkBook = new XSSFWorkbook(ExcelFile);
excelWorkSheet = excelWorkBook.getSheet(sheetName);
}
//This method reads the test data from the Excel cell.
public static String getCellData(int rowNum, int colNum) {
cell = excelWorkSheet.getRow(rowNum).getCell(colNum);
formatter = new DataFormatter();
return formatter.formatCellValue(cell);
}
//This method takes row number as a parameter and returns the data of given row number.
public static XSSFRow getRowData(int rowNum) {
row = excelWorkSheet.getRow(rowNum);
return row;
}
//This method gets excel file, row and column number and set a value to the that cell.
public static void setCellData(String value, int rowNum, int colNum) throws IOException {
row = excelWorkSheet.getRow(rowNum);
cell = row.getCell(colNum);
if (cell == null) {
cell = row.createCell(colNum);
cell.setCellValue(value);
} else {
cell.setCellValue(value);
}
// Write to the workbook
fileOut = new FileOutputStream(testDataExcelPath + Constants.testDataExcelFileName);
excelWorkBook.write(fileOut);
fileOut.flush();
fileOut.close();
}
}
In this file, I wrote all Excel operation methods.
setExcelFileSheet: This method has two parameters: “testdata.xlsx” and “LoginData”. It creates FileInputStream and sets Excel file and Excel sheet to excelWorkBook and excelWorkSheet variables.
getCellData: This method reads the test data from the Excel cell. We are passing row numbers and column numbers as parameters.
getRowData: This method takes the row number as a parameter and returns the data of the given row number.
setCellData: This method gets an Excel file, row, and column number andsets a value to that cell.
Step 8 – Create a Listener Class
We need to create a TestNG Listener class to check the status of each of the tests.
import com.example.tests.BaseTests;
import org.openqa.selenium.WebDriver;
import org.testng.ITestContext;
import org.testng.ITestListener;
import org.testng.ITestResult;
import java.io.IOException;
public class TestListener implements ITestListener {
private static String getTestMethodName(ITestResult iTestResult) {
return iTestResult.getMethod().getConstructorOrMethod().getName();
}
@Override
public void onStart(ITestContext iTestContext) {
System.out.println("I am in onStart method :" + iTestContext.getName());
}
@Override
public void onFinish(ITestContext iTestContext) {
System.out.println("I am in onFinish method :" + iTestContext.getName());
}
@Override
public void onTestStart(ITestResult iTestResult) {
System.out.println("I am in onTestStart method :" + getTestMethodName(iTestResult) + ": start");
}
@Override
public void onTestSuccess(ITestResult iTestResult) {
System.out.println("I am in onTestSuccess method :" + getTestMethodName(iTestResult) + ": succeed");
try {
ExcelUtils.setCellData("PASSED", ExcelUtils.rowNumber, ExcelUtils.columnNumber);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
@Override
public void onTestFailure(ITestResult iTestResult) {
System.out.println("I am in onTestFailure method :" + getTestMethodName(iTestResult) + " failed");
try {
ExcelUtils.setCellData("FAILED", ExcelUtils.rowNumber, ExcelUtils.columnNumber);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
@Override
public void onTestSkipped(ITestResult iTestResult) {
System.out.println("I am in onTestSkipped method :" + getTestMethodName(iTestResult) + ": skipped");
try {
ExcelUtils.setCellData("SKIPPED", ExcelUtils.rowNumber, ExcelUtils.columnNumber);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
@Override
public void onTestFailedButWithinSuccessPercentage(ITestResult iTestResult) {
System.out.println("Test failed but it is in defined success ratio " + getTestMethodName(iTestResult));
}
}
Step 9 – Create a Constant Class
Create a new class file named “Constants” in the utils package in which the tester will define constants like URL, filePath, and excelData. The source code looks as below:
package com.example.utils;
public class Constants {
public static final String testDataExcelFileName = "Test_Cases.xlsx"; //Global test data excel file
public static final String currentDir = System.getProperty("user.dir"); //Main Directory of the project
public static final String resourcePath = "\\src\\test\\resources\\"; //Main Directory of the project
public static final String excelTestDataName = "LoginData";
}
Step 10 – Create a BaseTests Class
This BaseTests class contains the setup and tearDown methods to initialize the driver at the start of the test and exit the driver at the end of the test.
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.chrome.ChromeOptions;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.BeforeMethod;
import java.time.Duration;
public class BaseTests {
public static WebDriver driver;
public final static int TIMEOUT = 10;
@BeforeMethod
public void setup() {
ChromeOptions options = new ChromeOptions();
options.addArguments("--remote-allow-origins=*");
options.addArguments("--no-sandbox");
options.addArguments("--disable-dev-shm-usage");
options.addArguments("--headless");
driver = new ChromeDriver(options);
driver.manage().window().maximize();
driver.get("https://opensource-demo.orangehrmlive.com/");
driver.manage().timeouts().implicitlyWait(Duration.ofSeconds(TIMEOUT));
}
@AfterMethod
public void tearDown() {
driver.quit();
}
}
Step 11 – Create a Test Excel File
Create a test file – Test_Cases.xlsx and place it in src/test/resources. I have stored the following data in the file.
Sno – Numbering of the test cases
Name – Name of the test case
Step_ID – Number of each step of a test case
Description – Detail of each keyword
Keyword – Keyword defined for each step
InputData – This is an optional field. We need this field to provide the input data like username, password, error message, and other validations.
ExpectedResponse – This is the response we expect to get from the execution for the particular test.
ActualResponse – This is the response we get after the execution of the particular test
Status – This could be pass, fail, skip
Step 12 – Create the Tests in src/test/java
In the below LoginPageTests class, we have 2 different tests and the test result will be saved in Test_Cases.xlsx file for both the tests.
A Data Driven Framework is a software testing framework that separates the test script logic from the test data. The test data is stored separately in external files like csv, xlsx, or databases that allows easy maintenance of the framework. In data driven framework, the test scripts retrieves the input values and expected results from the external source and write the actual result to the same source like spreadsheets, XML files, CSV files or database.
Project Structure
Here is the final snapshot of our project.
Dependency List
Selenium – 4.21.0
TestNG – 7.10.2
Apache POI – 5.2.5
Commons – 2.16.1
Maven Surefire – 3.2.5
Maven Compiler – 3.13.0
Java 17
Maven – 3.9.6
Implementation Steps
Step 1- Download and Install Java
Selenium needs Java to be installed on the system to run the tests. Click here to learn How to install Java.
Step 2 – Download and setup Eclipse IDE on the system
The Eclipse IDE (integrated development environment) provides strong support for Java developers, which is needed to write Java code. Click here to learn How to install Eclipse.
Step 3 – Setup Maven
To build a test framework, we need to add a number of dependencies to the project. It is a very tedious and cumbersome process to add each dependency manually. So, to overcome this problem, we use a build management tool. Maven is a build management tool that is used to define project structure, dependencies, build, and test management. Click here to learn How to install Maven.
In this example, we will access 2 web pages, “Login” and “Home” pages.
Hence, we will create 2 Java classes in Page Layer – LoginPage.java and HomePage.java and a BasePage class to initialize the driver using PageFactory.
BasePage
package com.example.pages;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.support.PageFactory;
public class BasePage {
public WebDriver driver;
public BasePage(WebDriver driver) {
this.driver = driver;
PageFactory.initElements(driver,this);
}
}
LoginPage
package com.example.pages;
import com.example.utils.ExcelUtils;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.support.FindBy;
import org.openqa.selenium.support.PageFactory;
public class LoginPage extends BasePage{
public LoginPage(WebDriver driver) {
super(driver);
}
@FindBy(name = "username")
public WebElement userName;
@FindBy(name = "password")
public WebElement password;
@FindBy(xpath = "//*[@class='oxd-form']/div[1]/div/span")
public WebElement missingUsernameErrorMessage;
@FindBy(xpath = "//*[@class='oxd-form']/div[2]/div/span")
public WebElement missingPasswordErrorMessage;
@FindBy(xpath = "//*[@class='oxd-form']/div[3]/button")
public WebElement login;
@FindBy(xpath = "//*[@id='app']/div[1]/div/div[1]/div/div[2]/div[2]/div/div[1]/div[1]/p")
public WebElement errorMessage;
public void login(String strUserName, String strPassword) {
userName.sendKeys(strUserName);
password.sendKeys(strPassword);
login.click();
}
public String getMissingUsernameText() {
return missingUsernameErrorMessage.getText();
}
public String getMissingPasswordText() {
return missingPasswordErrorMessage.getText();
}
public String getErrorMessage() {
return errorMessage.getText();
}
public LoginPage saveTestResults(int row, int column) {
ExcelUtils.rowNumber = row ;
ExcelUtils.columnNumber = column;
return this;
}
}
HomePage
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.support.FindBy;
import org.openqa.selenium.support.PageFactory;
public class HomePage extends BasePage {
public HomePage(WebDriver driver) {
super(driver);
}
@FindBy(xpath = "//*[@id='app']/div[1]/div[1]/header/div[1]/div[1]/span/h6")
public WebElement homePageUserName;
public String getHomePageText() {
return homePageUserName.getText();
}
}
Step 7 – Create an ExcelUtils Class
In order to manipulate excel files and do excel operations, we should create an excel file and called it “ExcelUtils” under utils package as shown below.
package com.example.utils;
import org.apache.poi.ss.usermodel.DataFormatter;
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 java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelUtils {
public static final String testDataExcelFileName = "testdata.xlsx"; //Global test data excel file
public static final String currentDir = System.getProperty("user.dir"); //Main Directory of the project
public static final String resourcePath = "\\src\\test\\resources\\"; //Main Directory of the project
public static String testDataExcelPath = null; //Location of Test data excel file
private static XSSFWorkbook excelWorkBook; //Excel WorkBook
private static XSSFSheet excelWorkSheet; //Excel Sheet
private static XSSFCell cell; //Excel cell
private static XSSFRow row; //Excel row
public static int rowNumber; //Row Number
public static int columnNumber; //Column Number
public static FileInputStream ExcelFile;
public static DataFormatter formatter;
public static FileOutputStream fileOut;
// This method has two parameters: "Test data excel file name" and "Excel sheet name"
// It creates FileInputStream and set excel file and excel sheet to excelWBook and excelWSheet variables.
public static void setExcelFileSheet(String sheetName) throws IOException {
testDataExcelPath = currentDir + resourcePath;
// Open the Excel file
ExcelFile = new FileInputStream(testDataExcelPath + testDataExcelFileName);
excelWorkBook = new XSSFWorkbook(ExcelFile);
excelWorkSheet = excelWorkBook.getSheet(sheetName);
}
//This method reads the test data from the Excel cell.
public static String getCellData(int rowNum, int colNum) {
cell = excelWorkSheet.getRow(rowNum).getCell(colNum);
formatter = new DataFormatter();
return formatter.formatCellValue(cell);
}
//This method takes row number as a parameter and returns the data of given row number.
public static XSSFRow getRowData(int rowNum) {
row = excelWorkSheet.getRow(rowNum);
return row;
}
//This method gets excel file, row and column number and set a value to the that cell.
public static void setCellData(String value, int rowNum, int colNum) throws IOException {
row = excelWorkSheet.getRow(rowNum);
cell = row.getCell(colNum);
if (cell == null) {
cell = row.createCell(colNum);
cell.setCellValue(value);
} else {
cell.setCellValue(value);
}
// Write to the workbook
fileOut = new FileOutputStream(testDataExcelPath + testDataExcelFileName);
excelWorkBook.write(fileOut);
fileOut.flush();
fileOut.close();
}
}
In this file, I wrote all excel operation methods.
setExcelFileSheet: This method has two parameters: “testdata.xlsx” and “LoginData”. It creates FileInputStream and set excel file and excel sheet to excelWorkBook and excelWorkSheet variables.
getCellData: This method reads the test data from the Excel cell. We are passing row numbers and column numbers as parameters.
getRowData: This method takes row number as a parameter and returns the data of the given row number.
setCellData: This method gets an excel file, row, and column number and sets a value to that cell.
Step 8 – Create a Listener Class
We need to create a TestNG Listener class to check the status of each of the test.
import com.example.tests.BaseTests;
import org.openqa.selenium.WebDriver;
import org.testng.ITestContext;
import org.testng.ITestListener;
import org.testng.ITestResult;
import java.io.IOException;
public class TestListener implements ITestListener {
private static String getTestMethodName(ITestResult iTestResult) {
return iTestResult.getMethod().getConstructorOrMethod().getName();
}
@Override
public void onStart(ITestContext iTestContext) {
System.out.println("I am in onStart method :" + iTestContext.getName());
}
@Override
public void onFinish(ITestContext iTestContext) {
System.out.println("I am in onFinish method :" + iTestContext.getName());
}
@Override
public void onTestStart(ITestResult iTestResult) {
System.out.println("I am in onTestStart method :" + getTestMethodName(iTestResult) + ": start");
}
@Override
public void onTestSuccess(ITestResult iTestResult) {
System.out.println("I am in onTestSuccess method :" + getTestMethodName(iTestResult) + ": succeed");
try {
ExcelUtils.setCellData("PASSED", ExcelUtils.rowNumber, ExcelUtils.columnNumber);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
@Override
public void onTestFailure(ITestResult iTestResult) {
System.out.println("I am in onTestFailure method :" + getTestMethodName(iTestResult) + " failed");
try {
ExcelUtils.setCellData("FAILED", ExcelUtils.rowNumber, ExcelUtils.columnNumber);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
@Override
public void onTestSkipped(ITestResult iTestResult) {
System.out.println("I am in onTestSkipped method :" + getTestMethodName(iTestResult) + ": skipped");
try {
ExcelUtils.setCellData("SKIPPED", ExcelUtils.rowNumber, ExcelUtils.columnNumber);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
@Override
public void onTestFailedButWithinSuccessPercentage(ITestResult iTestResult) {
System.out.println("Test failed but it is in defined success ratio " + getTestMethodName(iTestResult));
}
}
Step 9 – Create a BaseTests Class
This BaseTests class contains the setup and tearDown methods to initialize the driver at the start of the test and exit the driver at the end of the test.
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.chrome.ChromeOptions;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.BeforeMethod;
import java.time.Duration;
public class BaseTests {
public static WebDriver driver;
public final static int TIMEOUT = 10;
@BeforeMethod
public void setup() {
ChromeOptions options = new ChromeOptions();
options.addArguments("--remote-allow-origins=*");
options.addArguments("--no-sandbox");
options.addArguments("--disable-dev-shm-usage");
options.addArguments("--headless");
driver = new ChromeDriver(options);
driver.manage().window().maximize();
driver.get("https://opensource-demo.orangehrmlive.com/");
driver.manage().timeouts().implicitlyWait(Duration.ofSeconds(TIMEOUT));
}
@AfterMethod
public void tearDown() {
driver.quit();
}
}
Step 10 – Create a Test Excel File
Create a test file – testdata.xlsx and place it in src/test/resources. I have stored the following data in the file.
Test Case Name – Name of the test case
Username
Password
ExpectedResponse – This is the response we expect to get from the execution for the particular test.
ActualResponse – This is the response we get after the execution of the particular test
Status – This could be pass, fail, skip
Step 11 – Create the Tests in src/test/java
In the below LoginPageTests class, we have 4 different tests and we are failing test – validCredentials() to show that the test result will be saved in testdata.xlsx file for both passed and failed tests.
package com.example.tests;
import com.example.pages.HomePage;
import com.example.pages.LoginPage;
import com.example.utils.ExcelUtils;
import com.example.utils.TestListener;
import org.openqa.selenium.WebDriver;
import org.testng.Assert;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Listeners;
import org.testng.annotations.Test;
import java.io.IOException;
@Listeners({TestListener.class })
public class LoginPageTests extends BaseTests{
String actualResponse;
@BeforeTest
public void setupTestData() throws IOException {
System.out.println("Setup Test Data");
ExcelUtils.setExcelFileSheet("LoginData");
}
@Test
public void invalidCredentials() throws IOException {
LoginPage objLoginPage = new LoginPage(driver);
objLoginPage.login(ExcelUtils.getCellData(1,1), ExcelUtils.getCellData(1,2));
actualResponse = objLoginPage.getErrorMessage();
ExcelUtils.setCellData(actualResponse,1,4);
objLoginPage.saveTestResults(1,5);
Assert.assertEquals(actualResponse,ExcelUtils.getCellData(1,3));
}
@Test
public void missingUsername() throws IOException {
LoginPage objLoginPage = new LoginPage(driver);
objLoginPage.login(ExcelUtils.getCellData(2,1), ExcelUtils.getCellData(2,2));
actualResponse = objLoginPage.getMissingUsernameText();
ExcelUtils.setCellData(actualResponse,2,4);
objLoginPage.saveTestResults(2,5);
Assert.assertEquals(actualResponse,ExcelUtils.getCellData(2,3));
}
@Test
public void missingPassword() throws IOException {
LoginPage objLoginPage = new LoginPage(driver);
objLoginPage.login(ExcelUtils.getCellData(3,1), ExcelUtils.getCellData(3,2));
actualResponse = objLoginPage.getMissingPasswordText();
ExcelUtils.setCellData(actualResponse,3,4);
objLoginPage.saveTestResults(3,5);
Assert.assertEquals(actualResponse,ExcelUtils.getCellData(3,3));
}
//Fail this test
@Test
public void validCredentials() throws IOException {
LoginPage objLoginPage = new LoginPage(driver);
objLoginPage.login(ExcelUtils.getCellData(4,1), ExcelUtils.getCellData(4,2));
HomePage objHomePage = new HomePage(driver);
actualResponse = objHomePage.getHomePageText();
ExcelUtils.setCellData(actualResponse,4,4);
objLoginPage.saveTestResults(4,5);
Assert.assertEquals(actualResponse,ExcelUtils.getCellData(4,3));
}
}
Step 12 – Create testng.xml at the root of the project
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE suite SYSTEM "https://testng.org/testng-1.0.dtd">
<suite name="Data Driven Framework">
<test name="Login Test">
<classes>
<class name="com.example.tests.LoginPageTests"/>
</classes>
</test> <!-- Test -->
</suite> <!-- Suite -->
Step 13 – Run the tests through testng.xml
Right-click on the testng.xml and select “Run ..\testng.xml”.
The output of the above execution is
As expected, 3 test are passed and 1 test is failed.
The testdata.xlsx file will be updated with the actual response and the test execution status as shown in the below image.
Note – Make sure to close the excel workbook before starting the test execution, otherwise the test execution will be in the hung state.
Step 14 – View TestNG Reports
If you are using IntelliJ, the HTML Reports do not generate automatically. Go to the Configuration -> Listeners -> select Use default Reporters.
The TestNG Reports will be generated in test-output folder.
We are concerned about 2 reports – index.html and emailable-report.html.
index.html
emailable-report.html
Summary:
The test scripts in Data Driven Framework can be reused with different sets of test data.
The Test data can be easily modified without modifying the actual code implementation.
This framework has used excel as an external source to store the test data and test results.
QA Automation Expert 