DDT with Excel in C# and Selenium

there are some limitation while using DataSource attribute like

• We don’t have control over the iteration (via code, but we can do via properties)
• We cannot be very precise about which column and row data we need

Custom data driven library for excel

Hence to overcome to shortcoming of DataSource attribute, we are going to design our own custom data driven library for excel, As always, before starting to create any custom library, it’s always a good practice to first create a design before writing code. Our design looks something like this

Reading and parsing data from excel

For reading and parsing data from excel, instead of we do lot of code to read data out from excel (since excel libraries are exposed as COM components and hence reading them need COM interops library) we will use one of the most popular excel data reader available in codeplex called Excel Data Reader

You can also download via Nuget package manager to reference in your project.

Here is the code snippet for the above video

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

public static DataTable ExcelToDataTable(string fileName) {     //open file and returns as Stream     FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);     //Createopenxmlreader via ExcelReaderFactory     IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); //.xlsx     //Set the First Row as Column Name     excelReader.IsFirstRowAsColumnNames = true;     //Return as DataSet     DataSet result = excelReader.AsDataSet();     //Get all the Tables     DataTableCollection table = result.Tables;     //Store it in DataTable     DataTable resultTable = table["Sheet1"];     //return     return resultTable; }

we discussed how to

• Design our custom library,
• Reading and parsing data using ExcelDataReader and
• Converting the data into Data Table.

In this part, we will discuss storing the data into a collection and then read data for our test method from the collection.

Why use Collection?

Well, reading data just once from external data source like Excel sheet and storing the data in-memory like collections, will improve the performance tremendously as opposed to reading data each and every time from external data source like excel, since this will affect the performance of our test script.

Storing data in C# collections

We will store all the data from Data Table retrieved via ExcelDataReader into C# collection, for this we will do the following steps

• Create a Custom class
• Create a List<CustomClass>
• Populate the ExcelDataReader data into List<CustomClass> to our required format

Custom Class

Our custom class will have following auto-implemented properties

• RowNumber – hold all the row numbers
• ColumnName – holds column name
• ColumnValue – holds column value
• Custom Class
  1 2 3 4 5 6

  public class Datacollection { public int rowNumber { get; set; } public string colName { get; set; } public string colValue { get; set; } }

  Populating Data into Collections
  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

  List dataCol = new List(); public void PopulateInCollection(string fileName) { DataTable table = ExcelToDataTable(fileName); //Iterate through the rows and columns of the Table for (int row = 1; row <= table.Rows.Count; row++) { for (int col = 0; col <= table.Columns.Count; col++) { Datacollection dtTable = new Datacollection() { rowNumber = row, colName = table.Columns[col].ColumnName, colValue = table.Rows[row - 1][col].ToString() }; //Add all the details for each row dataCol.Add(dtTable); } } }

  Reading data from Collection
  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

  public string ReadData(int rowNumber, string columnName) { try { //Retriving Data using LINQ to reduce much of iterations string data = (from colData in dataCol        where colData.colName == columnName && colData.rowNumber == rowNumber        select colData.colValue).SingleOrDefault(); //var datas = dataCol.Where(x => x.colName == columnName && x.rowNumber == rowNumber).SingleOrDefault().colValue; return data.ToString(); } catch (Exception e) { return null; } }