Data Driven Framework with Apache POI – Excel

Most commercial automated software tools on the market support some sort of data driven testing, which allows you to automatically run a test case multiple times with different input and validation values. As Selenium Webdriver is more an automated testing framework than a ready-to-use tool, you will have to put in some effort to support data driven testing in your automated tests. I usually prefer to use Microsoft Excel as the format for storing my parameters. An additional advantage of using Excel is that you can easily outsource the test data administration to someone other than yourself, someone who might have better knowledge of the test cases that need to be run and the parameters required to execute them.

Reading data from the Excel

We need a way to open this Excel sheet and read data from it within our Selenium test script. For this purpose, I use the Apache POI library, which allows you to read, create and edit Microsoft Office-documents using Java. The classes and methods we are going to use to read data from Excel sheet are located in the org.apache.poi.hssf.usermodel package.

How to do it…

1) Download JAR files of Apache POI  and Add Jars to your project library. You can download it from here. That’s all about configuration of Apache POI with eclipse. Now you are ready to write your test.

2) Create a ‘New Package‘ file and name it as ‘testData’, by right click on the Project and select New > Package. Place all of your test data in this folder (package) whether it is a sql file, excel file or anything.

3) Place a Excel file in the above created package location and save it as TestData.xlsx. Fill the data in the excel like below image:

Apache-POI-15

4) Add two constant variables (testData package path & Excel file name) in the Constant class.

5) Create a ‘New Class‘ file, by right click on the ‘utility‘ Package and select New > Class and name it as ‘ExcelUtils. First we will write basic read/write methods.

6) Once we are done with writing Excel functions we can go ahead and modify the SignIn_Action module to accept the test data from excel file.

Note: In the later chapters we will see how to parameterise the row column as well, as we also have to avoid hard coded values in the scripts. This is just to give you an idea to use Excel and we will move forward step by step towards proper framework.

7) Create a ‘New Class‘ and name it as Apache_POI_TC by right click on the ‘automationFramework‘ Package and select New > Class. In this we will read the values from the Excel sheet to use them as the test data and write the test result in the Excel.

Give it a run, see how beautify your script will execute the code.

8) Once it finished open the Excel file and check for the result.

Apache-POI-16

Your Project explorer window will look like this now.

Apache-POI

Do not worry about the red cross signs on your test scripts, it is because we have removed arguments from SignIn_Action class.

Data Driven framework can be build up by using TestNg Data Provider.

 

navigate-left-icon

navigate-right-icon