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.

excel selenium

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.

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:

Step-3: Set Data Excel File Name in BaseTest Class

I should also add testDataExcelFileName in BaseTest class because all tests use the same excel file but their sheets are different.

Step-4: Setup Test Data in Test Class

We need to set the excel file and sheet name before starting the tests. We have to do it in related test class because each test class has different test data and their sheets in the global test data excel are different too.

Step-5: Create a Test Excel File

Now, it is time to construct our test excel data file for data-driven testing. In this example, I will modify our login scenarios (tests). First one is “invalid username” and “invalid password” test. I will store the following variables in the LoginData sheet:

  • username (invalid)
  • password (invalid)
  • username error message 
  • password error message 
  • test status (automation code will update after test execution.)

For the second test, I will test the empty username and empty password case. Thus, my data will be like that:

  • username (empty)
  • password (empty)
  • username error message 
  • password error message 
  • test status (automation code will update after test execution.)

Here is what it looks like:

Step-6: Modify Test and Page Classes

In LoginTests class, we should start to modify our code for data-driven testing. First, let’s start with “invalidLoginTest_InvalidUserNameInvalidPassword” test. I will use “getRowData” method for logintoN11 operation. In order to get first test data values (first row), we should use the below code:

All lists values in excel file start from “0”. Thus, I have to use “1” for to get first test’s data.

Now, it is time to change “loginToN11” method in LoginPage class.

As you seen above, loginToN11 method gets XSSFRow variable as a parameter, and we can get the required values with

row.getCell(1).toString(): This equals to username value.

row.getCell(2).toString(): This equals to password value.

Then, in LoginTests class, I set row number (1) and test status/result column number (5). I will use these numbers to update test status after test finished in TestListener Class. You can see the implementations below. First, I set the row and column numbers:

Then, in TestListener class I update the test result/status for each test status (passed, failed, skipped):

In order to verify the login password message, I did below modification:

Here, getCellData method gets row number as 1, and column number as 4 because 4th column holds expected password error message.

When we go to the verifyLoginPassword method’s implementation, we will see that it gets expectedPasswordMessage as a parameter and verify it with the actual value.

I did the same implementations for the second test, you can check the whole code at GitHub address. I don’t want to explain the same thing again and again. 😉

Let’s run the data-driven testing code and check the result at both IntelliJ console and our excel file. If all test pass, we will see the status values as PASSED! 🙂

If any of the tests failed, we will see the status of the failed test as FAILED.

You can find the sample data driven testing with excel, apache poi, and selenium webdriver project code on our GitHub page.

https://github.com/swtestacademy/ExcelReadWrite

Selenium Webdriver Tutorial Series

All the best! Thanks for reading! I love you guys! Happy testing.
I believe that knowledge will make us modest, instead of arrogant! 
-Onur