Automation Testing: Read Data from Excel

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.

Project for Test

• Microsoft.Office.Interop.Excel

Microsoft.Office.Interop.Excel

system data search result

Starting to Code

Start your Code by importing the libraries for which references are recently added to the project;

using System.Data;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;

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;

  1. SQL Query (string)
  2. Connection string (string)

        private static DataTable GetDataTable(string sql, string connectionString)
        {
            DataTable dt = new DataTable();
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                {
                    using (OleDbDataReader rdr = cmd.ExecuteReader())
                    {
                        dt.Load(rdr);
                        return dt;
                    }
                }
            }
        }
}
}

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:

  1. Sheet Name (string)
  2. File Path (string)

public static DataTable getDataFromSheet(string sheetName, string path)
        {
string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=yes'", path);
OleDbConnection connection = new OleDbConnection(connString);
DataTable dataTable = GetDataTable("SELECT * FROM [" + sheetName + "$]", connString);
              return dataTable;
        }

As of now, methods are implemented and data from the Excel sheets is read and loaded in Data Table. Now, data can be easily extracted from a particular Data Table and used in our solution to fill-in different forms or fields.

Example Below:

private static string SearchdepfilePath = @"FilePath from the System";
DataTable searchDepSeq = ExcelDataReader.getDataFromSheet("SheetName", SearchdepfilePath);

Example of Reading Data from Data Table

Once data is loaded in the Data Table, it can easily be read as

dataTableName.Rows[rowIndex]["columnName"];

where:

  1. “columnName” is the column Heading in MS Excel.
  2. “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.

Leave a Reply

Your email address will not be published. Required fields are marked *