Data Driven Testing with Excel in Selenium

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 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 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 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 this 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 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 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 wish that Knowledge will make us modest, instead of arrogant! 
-Onur

By |2018-11-27T14:00:44+00:00October 30th, 2017|Selenium Tutorials|6 Comments

About the Author:

Onur Baskirt is a senior IT professional with 10+ years of experience. He worked at Bahçesehir University, ST Microelectronics, Huawei and Ericsson as a research assistant, design verification engineer, and software test leader. Also, he worked as software test leader and software operations manager at Turkey's biggest technology retailer, Teknosa. After Teknosa, he worked as Head of Software Testing and Manager of two Development Teams at Kariyer.net. Now, he is working as a Senior Technical Consultant at Emirates Airlines in Dubai. His current research areas are technical software testing, programming, and computer science. Formerly, he has several research works and papers on digital chip design & verification. His hobbies are sport, dancing, traveling, and nutrition. You can find detailed information about him on his linked-in page.

6 Comments

  1. Muktheswer September 25, 2018 at 1:06 pm - Reply

    Hi,
    It is very good article and thanks for this 🙂

  2. uu December 7, 2018 at 12:00 pm - Reply

    Hocam merhaba,
    Ekran görüntüsünü nasıl aldınız acaba ben alamıyorum..

  3. tugce December 15, 2018 at 12:59 pm - Reply

    Hocam merhaba,
    Öncelikle proje cok güzel olmuş elinize sağlık.

    Bu projede sadece test annotation yazan methodların hepsi çalışıyor senaryoya ayıramadım. Ben excelden inputları alıp adım adım test koşmak istiyorum. Örneğin 3 tane senaryom olacak ilk senaryom sadece n11 sitesini acacak,2.senaryom login olcak,3. senaryom sepete birşey ekleyecek.. Ben sadece n11 sitesini açan senaryoyu koşmak istediğimde diğer 2 senaryoyu çalıştırmayacak..Bu çalıştırılan senaryonun sonucunu extendReport ta gosterecek..Bunu yapmak için nasıl bir yol izlemeliyim.

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.