Site Loader
Auckland, New Zealand
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

Post Author: Karthik kk

26 Replies to “DDT with Excel in CUIT (Part 1)”

  1. Hi..your videos are very helpful for beginners like me…I am watching up your videos and implementing in my project as well. Thankful to you as it is helping me out.

    Have one question,

    when I try to execute the same code above , am getting excelreaderfactory.cs file not found. I installed exceldatareader as you explained above. Not sure what is the reason. Please help me.

  2. Hi Karthik,
    First of all your vedio is helpful for beginner like me. Great job!!
    I got the same issue as bharathi above, ExcelReaderFactory.cs not found, do you know the solution to it? Thank you.

    1. Do not put breakpoint. Just continue. When you debug with step into , it is trying step into DLL.

      If you want to check the data you can put break point after this line check the value of dt.

      DataTable dt = ExcelUtil.ExcelToDataTable(…);

      Hope it helps.

  3. Please improve the code snippet by putting the Data set in “Using” so that it will be closed automatically when not in used .Below is the updated code:-

    using (DataSet result = excelReader.AsDataSet())
    {

    //Get all the Tables
    DataTableCollection table = result.Tables;

    //Store it in DataTable
    DataTable resultTable = table[“Sheet1”];

    //return
    return resultTable;
    }

    1. How would the //Store it in DataTable looks like if I do have “Sheet1” and “Sheet2″ ? I cannot return 2 statements within a same flow, the second statement will have a warning:”Unreachable code detected”, Could you please suggest the best approach on this? thanks a lot

  4. Hi Karthik i want add multiple sheets in the Excel with data driven how can achieve??

    for example in sheet1 i have username column sheet2 password column i want use these two in same testcase how can i use

    DataSource(“System.Data.Odbc”, “Dsn=ExcelFiles;Driver={Microsoft Excel Driver (*.xls)};dbq=|DataDirectory|\\Data.xls;defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5;readonly=true”, “Sheet1$”, DataAccessMethod.Sequential), DeploymentItem(“Sheet1.xls”), TestMethod]

    here we are using one sheet “Sheet1$ can i use multiple sheets here like this??

    DataSource(“System.Data.Odbc”, “Dsn=ExcelFiles;Driver={Microsoft Excel Driver (*.xls)};dbq=|DataDirectory|\\Data.xls;defaultdir=.;driverid=790;maxbuffersize=2048;pagetimeout=5;readonly=true”, “Sheet1$”, “Sheet2$”, DataAccessMethod.Sequential), DeploymentItem(“Sheet1.xls”), TestMethod]

  5. Hi Karthik, thank you soooo much for your wonderful videos. Coded UI is a bear for an old WinRunner / QTP person like myself.

  6. Hi Karthik,

    I am pretty new to this Selenium with C# where i am trying to implement your DataDriven way to read an output xlsx file.

    Using this method “ExcelToDataTable”, i am trying to read an xlsx output file which will be downloaded from the application. But unfortunately i am getting “ArgumentNullException” while reading an XLSX file at statement
    DataSet result = excelReader.AsDataSet();

    I checked the output xlsx file which is having some blank cells and while converting the data into DataSet it might be treating as Null values.

    Any help will be greatly appreciated.

    Thanks,
    Naveen

  7. Hello Karthik,
    First and above all thanks for easy to use video tutorials.

    I’m using the excel reader in my login methods and able to login by it. But when using the same login method in multiple tests and running all the tests at once it is giving errors.
    For 1st test it reads data from excel.
    For 2nd test onwards it fails to read data, when debugging i’m getting ReadTimeout errors on streams.

    May be can you please look into it.

    Thanks,
    Saadat

  8. Hello Karthik,

    Why do you need to create custom Data Driven Librariy for Excel instead of using the existing library Microsoft.Office.Interop.Excel?

    Thank you!

  9. Hi,
    I am using Visual studio 2017 & getting error in this line.
    excelReader.IsFirstRowAsColumnNames = true;
    It shows ‘IExcelDataReader’ does not contain a definition for ‘IsFirstRowAsColumnNames’ and no extension method ‘IsFirstRowAsColumnNames’ accepting a first argument of type ‘IExcelDataReader’ could be found (are you missing a using directive or an assembly reference?)

      1. I have 2015 and got the same problem. I installed Exceldatareader v2.1.2.3 and then changed the reference to using Excel; and worked

    1. Hi,
      For this issue, uninstall latest version of ExcelDataReader reference and install old version 2.1.2.3. from NuGet Pakages. It will solve your issue

    2. I am also facing same issue

      It shows ‘IExcelDataReader’ does not contain a definition for ‘IsFirstRowAsColumnNames’ and no extension method ‘IsFirstRowAsColumnNames’ accepting a first argument of type ‘IExcelDataReader’ could be found (are you missing a using directive or an assembly reference?)

  10. Hi karthik,
    I m using Visual Studio 2013 professional edition
    Following code is not working
    excelReader.IsFirstRowAsColumnNames = true;
    I m getting error like (are you missing a using directive or an assembly reference?)

    Can any one help me

  11. Hi Vivek, I found the fix – uninstall the `ExcelDataReader` Nugget package version 3.1 and install the 2.1.2.3 version and then change using statement from ExcelDataReader to Excel and it is working. Newer package doesn’t have those methods.

  12. Hello, thank you very good for your effort, I follow your updated video on Reding excel data,but try to read the excel data on datagridviewer by selecting the excel sheet name from comboBox , but it fails to load the data. It gives me an error that Object reference not set to an instance of an object. Please I need held.
    Thank you

  13. “How would the //Store it in DataTable looks like if I do have “Sheet1” and “Sheet2″ ? I cannot return 2 statements within a same flow, the second statement will have a warning:”Unreachable code detected”, Could you please suggest the best approach on this? thanks a lot”

    I’m facing a problem on multiple excel sheets

Leave a Reply

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