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
|
|
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;
}
}
|
