DDT with Excel in CUIT (Part 1)

We have already discussed Data Driven Testing in Coded UI with CSV in previous post. In previous post we decorated our test method with [DataSource] attribute and used TestContext.DataRow[] method to read data out from CSV file. But 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 complete video explaining the above article as video Here is the code snippet for the above video

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

Thanks for watching the video and reading the post !!! Please leave your comments and let me know if there is anything need to be improved in this post. Thanks, Karthik KK