Hi all, in this article I will describe you how to use excel files in your test automation projects for data driven testing. We can do data driven testing in several ways. We can use TestNG data provider for small data sets such as 3-4 different login data or if we have more data we can choose to use excel files or we can store the test data in a database. In this article, I will explain how to use excel files to store all test-related data. In order to manipulate excel files, I mean read the excel file and write to an excel file, we can use Apache POI API. I will show you how to integrate POI libraries into our test project.
I will go on with our Allure reporting example, it comprises of Page Object Model (POM) pattern, ExtentReports Reporting, and Allure Reporting features and we will add excel manipulation capability in that project. In order to do that, I will add an ExcelUtil class and this class does all kinds of excel operations. Here is the final snapshot of our project.
I will go step by step 😉 Don’t worry! I hope, I will do by best, and you will get the topic without any problem. 😉
Step-1: Add Apache POI Dependencies
In order to use Apache POI libraries in your project, you should add required dependencies into your pom.xml as shown below.
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
Step-2: Create an ExcelUtil Class for Data Driven Testing
In order to manipulate excel files and do excel operations, we should create an excel file and called it “ExcelUtil” under excelutils package as shown below.
In this file, I wrote all excel operation methods.
setExcelFileSheet: 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.
getCellData: This method reads the test data from the Excel cell. We are passing row number and column number as parameters.
getRowData: This method takes row number as a parameter and returns the data of the given row number.
setCellData: This method gets excel file, row, and column number and set a value to that cell.
and I have setters and getters for rows and columns. I will use all of the methods in test classes.
Here is the implementation of ExcelUtil Class: