Why Excel Data Read is Required in Automation Testing?
Reading data from Excel is now a day to day task. The Automation Tester must have a grip on this area to make life & testing activity easy by interacting with Excel Files directly rather than with code base. From simple page login to difficult business flows of application, data manipulation is required in order to fill in forms and then submit those to validate results. Similarly, we can perform the validation checks for different field types and their allowed values. Instead of changing those values for complex forms through the code, we only maintain different Excel Files/ Sheets within the same Excel file to achieve the results more efficiently with less user code interaction.
Tool Versions & Libraries Required to Import
- Microsoft Visual Studio Enterprise 2017
- Version 15.8.0
- Microsoft Dot Net Framework Version 4.7.03056
- Microsoft Office Access Database Engine
References Need to be Added
When you create a new Project for Test (.NET Framework), you would be required to import the following references
- System.Data
- Microsoft.Office.Interop.Excel
How to add those references?
Right Click on the References from the Solution, and click on “Add References…”. A new window will pop up. From there, search for the upper described references, check the box beside their name, and click “OK”. Those references will be added in your existing solution.
Starting to Code
Start your Code by importing the libraries for which references are recently added to the project; As of the standard practice, Create one class which will be reading the data from the excel file. In the code below, Class with name “ExcelDataReader” is created containing two(2) methods. Both methods are static, so that without creating object of the class, these can be directly called with class name. Method 1 is private, because the scope for this is always within the class and there isn’t a need to call that out of the boundary of class. Method 2, however, is public and will be used to read the data from excel into our solution.
Details of Methods
One method will follow the standard SQL methodology to load the data table.
Parameters for Method 1
For ease of implementation, 2 parameters are passed to the method named “GetDataTable” which are;
- SQL Query (string)
- Connection string (string)
Parameters for Method 2
Again 2 parameters are passed to this method as well- one Excel File might contain multiple sheets hence we will pass:
- Sheet Name (string)
- File Path (string)
Example of Reading Data from Data Table
Once data is loaded in the Data Table, it can easily be read as where:
- “columnName” is the column Heading in MS Excel.
- “rowIndex” is Integer Value representing the Record Number (first record except columnName/ columnHeading will always have a value of 0).
If there are multiple records in the excel sheet against a single column, we can use loop to traverse through multiple records. If you have any question or queries, do not hesitate to reach out to us!
