The first impression after hearing the term ‘data-driven testing’ leads to testing an application with different combinations of test data, along with some external sources where users can store, process, and validate the data. The test data is often presented in tabular format, which can be easily editable by test engineers without the necessity of any programming skills. Additionally, because the data is tabular, it is natural to present it in spreadsheets.
Let’s explore the following scenario: I needed to validate some business rules on more than 20 thousand records of test data. The test data is being processed by different kinds of Headless Automated Jobs, which are scheduled to run periodically on a daily basis, such as a run every two hours, or a run every six hours. Once these jobs run, they process the test data and subsequently perform certain activities based on the type of test data, such as sending an email, making an auto-purchase, or pulling vouchers. To create the test data, we have an Application UI which we have already automated using Selenium with C#.
In automation, whenever we look for framework where a system needs to test under a large volume of data, it should be either data-driven or key-word driven. The challenge, then, was to figure out an automation approach where I can do the following:
Considering those points, I used Visual Studio Excel project template. The additional benefits of this were:
Since I was looking for a data-driven approach, I identified the following framework components where I can read test data and process accordingly.
We only have a few test cases for the requirements we have to validate, but each test case itself represent the different permutation and combination of test conditions. Let’s explore this with one scenario.
A member scheduled and appointment (30 minutes/60 minutes) with a trainer every 24, 48, and 72 hours where either the member had no inventory and card on file false, or the member had only one inventory and card on file false.
While this is only one test case, we can derive multiple test conditions, such as:
And so on.
In this way, the test conditions are increasing exponentially, so I decided to divide them into different parameters on a spreadsheet like so:
|Appointment Type||Time Option||Inventory||Card on File|
In this way, each row is representing a test condition, as shown in the following image
The next step is to call this input sheet, process each condition (each row), and then create and write the output data in an excel sheet. I decided the entry point of the execution using the Test Execution Controller. This is the core of the framework. Because I was using an Excel Template, I provided choices to select scenarios and buttons to select test case sheets, process input conditions, and create data. Once the data is generated, I add a new worksheet and dump the output data into that sheet.
At the coding side, each control binds with some events like click, check, and select with the help of C#:
Below are the steps to configure an Excel Template using Visual Studio. Prerequisites: You should have Visual Studio 2010 or later, along with .Net Framework 4.0.
Now you are ready with your Excel project. In a folder structure, the project files would look like this:
Using Visual Studio Tools:
Using Excel Controls:
Coding with the Excel Workbook
It’s quite similar to working with other Visual Studio Projects, in that it allows you to add references, existing projects, etc. In this scenario, we had an existing framework which we were required to use in this project, so I added the existing project into this Excel project and referenced the DLL of the existing project in the Excel project.
Up until this point, we haven’t had any issues because everything is setup on my own machine and we are running excel only on my machine. However, what if I need to share my excel files with an end user so that they can also use the files on their machine?
We need to create deployment files using the Publishing option of Visual Studio. The steps to do this can be found here.
Once the publish is successful, the following folders will be created in your publish location:
Now the deployment files are ready, so all you have to do is copy this publish folder to another user’s machine, which can be done by following these steps:
Now you can use these Excel files on another user’s machine as well.
Note: Whenever you share a publish folder, don’t compress it because it will not work if another user extracts and installs it.