Site Loader
Auckland, New Zealand
karthik@techgeek.co.in

DDT with Excel in CUIT (Part 2)

In previous post, 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

Here is the complete video of the above discussion

Here is the source code from the above video

Custom Class

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

Populating Data into Collections

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

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

 

Breaking Changes

Please check it out now !

There happened some breaking changes in the latest version of Excel data reader and the complete discussion is available here in this video

Thanks for reading the post and watching video !!

Please share your comments and let me know if there is anything need to be improved in this post !!!

Thanks,
Karthik KK

Post Author: Karthik kk

80 Replies to “DDT with Excel in CUIT (Part 2)”

  1. Hello Karthik,

    I can create data library as per your code but if i want to run multiple test runs with data varying from data file then its not working for me.

    For example :
    I want to run below scenario for which i have different data combinations stored in data file (like 100 rows).

    – User login
    – Create new member in system (member name, Dob, Place etc.)
    – User logout

    I want above scenario to be repeated as many times as the number of rows available in data file.

    i have tried with order test but in that i have to again put hierarchy of test methods which i wanted to be called which seems inappropriate approach to me. Can you please help me if there is any other better way for the same?

    Reply

    1. Since all the test (3 as you mentioned) are going to be interdependent on datas and they need to run in order, then surely, only you have to write code as you mentioned like hierarchy of code to perform the operation, since CUIT will noway handle these situations.

      Thanks,
      Karthik KK

      Reply

      1. Thanks Karthik.

        I did the same and its working fine.
        However i am not able to initiate browser every time. If i re-initialize browser for each row data its not working properly, so i have to start with user logout and new scenario with user login.

        Is there any solution where i can initiate browser every time with new set of raw data ?

        Reply

        1. Then my answer for this problem is surely going to be like previous one 🙂

          You have to handle this situation in your code.

          You can design your code this way

          for(int dataRow = 1;dataRow <= ExcelData.length; dataRow++)
{
   
   //Perform Login Ops
   Login();
   //Create new member in system
   CreateUser();
}

//Then Call logout functionality
Logout()

          Hope this should help !!!

          Thanks,
          Karthik KK

          Reply

            1. Hi Rushi,

              I’m also facing the same issue, i want to run one test case with multiple data rows. Could you share your code for running multiple data rows

              Reply

  2. Hi Karthik,
    Thanks for your videos. It helped me a lot.
    I need help on database testing with Coded UI Test.
    Please help me.
    Thanks.
    Jagan Nimma

    Reply

  3. Hi Karthik,

    I am getting a out of range exception when i reused the excel library above.
    pls advice.

    Thanks,
    Subha

    Reply

  4. Hi Karthik

    I have followed both videos and they are easy to follow.
    I get an error when running the code – excelreaderfactory.cs file not found.
    I have download the Excel reader from nugget and it is referenced in the class.
    Please can you help.

    Reply

    1. Can you please try to reinstall the package, seems like something wrong with Nuget installation.

      Since I have never faced this kind of issue.

      Thanks,
      Karthik KK

      Reply

  5. Hi Karthik,
    I have followed your videos and they are great but i am facing a issue, while trying to read data from collection the it is not reading the data from Excel. It is passing the Value as System.Data.DataRow into the fields .Not sure where the problem is.

    Reply

  6. Hi Karthik Sir,

    I followed your video everything was fine except one error.
    I am able to open the browser and navigate to the URL but while reading from excel i m getting error/exception “{“Cannot find column 2.”}”

    I hope I could attach snapshot.

    Reply

    1. Hi Pankaj,

      Seems like the issue is because of following reasons

      1. The Excel does not has the column name you specified in the code.
      2. The code is looking at the wrong row number

      Hope this will help !!!

      Thanks,
      Karthik KK

      Reply

      1. I am following code without any change.
        And my application is very much similar to yours.
        I m facing exception in Datacollection dtTable section of populateIncollection class.
        And exception is IndexOut of Range and comment is “Cannot find column 2”.

        I would really appreciate your help.
        Thanks

        Reply

    2. Hi Pankaj,

      Change this > for (int col = 0; col < table.Columns.Count; col++)
      it should be < and <= .. Col starts with 0 and 0 is name and 1 is initials from Karthik excel example.

      Hope it helps.

      Reply

  7. I am debugging the program and found that after iteration for column 2 , there is no data thatswhy its giving exception.

    Reply

      1. Hi kartik,

        I am trying to read data from .xls and not able to achieve with the above methods. I can you in the videos that you used .xlsx file. Is there any work around to use .xls for the above approach and achieve reading data from it.

        Thanks in Advance for any help.

        Venu

        Reply

        1. HI Venu,

          In Part 1 of this post, we discussed how to create ExcelToDataTable method, in that you need to change the line number 6 to 

          IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);

          And now your code will start to work for .xls as well !!!

          Thanks,
          Karthik KK

          Reply

  9. Hi Karthik Sir,

    In addition to this I wanted to ask that do u have any tutorial on Cross browser testing in selenium.
    Your help is highly appreciated.

    Thanks

    Reply

    1. Hi Pankaj,

      I hope, the cross browser testing Selenium is very simple, since all you have to do is just to change different type of WebDrivers like

      1. FireFoxDriver,
      2. IEDriver,
      3. ChromeDriver

      Hope this will help !!!

      But the question is in wrong post though 🙂

      Thanks,
      Karthik KK

      Reply

  10. HI Karthik,

    I am getting System.OutOfMemoryException at this line dataCol.Add(dtTable). Pleas let me know if you have any thoughts on how to solve this.

    Thanks in advance
    Deepak

    Reply

  11. Regarding of “Cannot find column 2” issue, I ran in to similar situation.
    I did found the possible reason and a working solution (for me at least).

    for the method: PopulateInCollection

    I would suggest to change nested inner loop to
    for (int col = 0; col < table.Columns.Count; col++)
    I suspect the column counts start from 0 and should end with 1 – for a 2 column tables such as this one.

    Thank you for posting all the information in such clear way.

    PS. I got here fro your post on "Selenium automation with C#" 12 episodes series.
    Excellent post! And Thank you for posting it, it was great help!

    Reply

    1. Hi Charles,

      Thanks for your post. You have resolved the issue that i was facing i.e. “Cannot find column 2” issue. And big thanks to “Karthik” for writing such an informational and detailed blog on ExcelReading. I was able to read the data from excel with the help of code provided in the blog. Keep up the good work.

      Reply

  12. Hey Karthik,

    If i have multiple sheets with data in my excel (e.g. Sheet1, Sheet2, Sheet3 etc.) then how can i use different sheet names in my code. currently the code suggests that i can use only one sheet at a time. (DataTable resultTable = table[“Sheet1”];), is there a way so that i can make this sheet name dynamic and provide the name in my test.

    Reply

    1. Karthik, i would also like to know how can i perform ‘swipe’ action in C# like swiping from left to right or right to left?

      Reply

  13. Hi Karthik,

    Thanks A Lot for all these Wonderful Videos:)
    Really appreciate all your efforts…. Please continue uploading more videos…

    I need a help in Integrating Coded UI with Specflow(BDD). I’ve watched your videos for Coded UI & Specflow. I tried various ways but it didn’t work.
    If possible, Could you please upload a video for this.

    E.g. For a Simple Calculator application, how can we integrate Coded UI & Specflow..

    Thanks for your help in Advance:)

    Reply

  15. Hi Kartik,

    I tried using your code but i am not able to fetch data in my string resultant ,just like you did.Also will this work for recoded methods aswell.Please revert asap

    Reply

  16. Hello Kartik,

    I need to help for how to check the url link which is listed in excel and did testing for url hit successfully pass or fail

    Reply

  17. Hi Karthik,

    I have seen you DDT video in youtube. I have one question, in excel if we have text data for more than 1 row. How to handle?
    For Example if i want execute same method for 5 times with 5 different test data. Do i always need to write (1, initial), (1,UserName), …….. (5, inital), (5, UserName)… Hope you reply!

    Reply

    1. Nope, put the line of code in For loop

      for(int i=0;i<5;i++)
{
  (i,initial), (i,USerName)
}

      Hope it makes sense !!

      Thanks,
      Karthik KK

      Reply

  19. 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);
    }
    }
    }

    I get errors when I am writing this code it says operator cannot be applied to operands of type int and method group.Please help

    Reply

    1. The code looks good to me, can you tell me in which line you are getting the exception.

      Thanks,
      Karthik KK

      Reply

  20. for (int row = 1; row <= table.Rows.Count() ; row++)
    error under count
    {

    colName = table.Columns[col].ColumnName,
    colValue = table.Rows[row – 1][col].ToString()
    error under table.columns and table.Rows

    Reply

    1. Try removing the less than or equal to just less than, something like this

       for (int row = 1; row < table.Rows.Count() ; row++)

      Thanks,
      Karthik KK

      Reply

  21. I’m trying to collect data from Website and copy to Excel sheet.

    Could someone help me in this part of code please ??

    Reply

  22. Hello!
    I’m using ecactly your code and i get this message:

    Exception Message: Object reference not set to an instance of an object. (type NullReferenceException)

    Reply

      1. hi Karthik I am also getting same error null exception error in read data method the query which you are using to read from dafacol
        can you please respond immediately if possible … I need it ASAP

        Reply

  23. Hi Karthik,

    First, thank for the help, everything works fine when you execute one TC at a time, but when I do a “Run All”, the second one fails, displaying the error: System.ArgumentNullException: Argument ‘username’ cannot be null. Seems like a problem with the ReadData method, could you please give me a hand?

    Thanks!

    Reply

    1. Hi Sergio,

      See whats the count for for (int row = 1; row <= table.Rows.Count; row++) snippet, since, it seems like somehow the rows are less than the count and hence you are getting ArgumentNullException.

      Thanks,
      Karthik KK

      Reply

      1. Thanks Karthik for the reply! This is what I love about internet, the opportunity to share knowledge with no interests. I’ve review my problem and source of it, was the ReadData method, the problem there was that its using SingleOrDefault, I changed to FirstOrDefault so it success no matter how many elements we have in the collection, and now it works when I execute multiple TCs referring to the same table.

        Reply

  24. Hi Karthik,

    I have a problem with executing test.
    The Result Message is: System.NullReferenceException
    Stacktrace lead me to these 2 lines:
    ExcelLib.PopulateInCollection(@”D:\data.xlsx”)
    and
    for (int row = 1; row <= table.Rows.Count; row++)

    The browser opens and page is loaded but fields aren't filled with data. Is that problem with read data from .xlsx or something else?

    Reply

    1. Ok, I found what was wrong. I use Polish language in Excel so there was no “Sheet1” im my .xlsx file – this name was different.

      Reply

  25. Hi Karthik,

    Thank you for your great HELP.
    Can you tell me how to write to Excel in Nunit frame work.
    EX: In “PO_NUMBER” column i want to write 12345 and this value should be visible Excel file after my test execution.

    Reply

  26. Hi Karthik,

    I have used the above concept for developing keyword driven frame work, it is working fine for single keyword, please let me know how to handle multiple keywords.

    String resultant1 = util.ReadTestData(1, “KeyWordName_1”);

    CodedUITest2 myClassObj = new CodedUITest2();

    Type myTypeObj = myClassObj.GetType();

    MethodInfo myMethodInfo = myTypeObj.GetMethod(resultant1);

    object myClassObj1 = myMethodInfo.Invoke(myClassObj, new object[] { });

    Reply

  27. Hi Karthik,

    First of all thank you sharing this, truly very helpful !!!

    i am getting Null exception error on below line i have just tried above code in Coded UI by creating the Class file.
    var 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();

    kindly help me on this.. 🙁

    Reply

    1. Could you quick watch and see whats there in datacol

      You can see if the datacol has got data in it, I guess something wrong will filling data from excel sheet.

      Thanks,

      Reply

      1. Hi Karthik,

        thank a ton for quick response, in fact i dont know how to check whether data comes in datacol, but i verified dtTable data collection shows the values on below line.

        datacol.Add(dtTable);

        quick response is much appreciated !!!, i have demo to present and i am using this approach since it very useful.

        Reply

        1. So, I guess the problem can be in either the columnname or row number.

          Check to see if the columnname exist for that row number, if not, there will be problem.

          Thanks,

          Reply

          1. thank you again Karthik, i figured out the root for the issue, in face coumn name its reading out as column1 and column2 in datacol, though below line of code is there in ExceltoData() method.


            //Create Column names for the first Row.
            excelReader.IsFirstRowAsColumnNames = true;


            now if i pass column name as column1 instead of the actual column name it reading out.
            any suggestions.

            Reply

            1. Yes, as mentioned in the video, the first row is always considered as column name if set to true.

              Thanks,
              Karthik KK

              Reply

              1. Thank you very much for timely help.. Much appreciated.
                Now how to handle same wpfcontrol types with same property..?

                any input ? thank you very much !!!

                Thanks
                Jaya Gudi

                Reply

  28. Hi Karthik,

    Wonderful video. Neatly presented.
    I have below questions, kindly answer
    1. How can merged columns work? With this code I do not get merged col displayed in data table.
    2. Should “Column names” be unique? I have an data excel like below. How can I use column names to check if the value for Run is Y or N?

    Context Feature1 Run? Feature2 Run? Feature3 Run?
    Link 1 Tabs Y Search N Properties Y
    Link 2 Create N Search Y Send Mail Y

    Reply

    1. HI Rashmi,

      Thank you !
      1. How can merged columns work? With this code I do not get merged col displayed in data table.
      Not sure, never tried before (but should work I guess)
      2. Should “Column names” be unique? I have an data excel like below. How can I use column names to check if the value for Run is Y or N?
      Yes, Unique column names are less prone to confusion and best for robust coding.

      Thanks,
      Karthik KK

      Reply

      1. Thanks for the reply Karthik.
        I have made column names unique by adding numbers 🙂
        Merged columns in data table shows only the column name for first column and remaining columns are named as column 3 ,column 4 so on. As of now I am not using merged cells, so happily working with your code. Thanks again.

        Regards,
        Rashmi

        Reply

  29. Hi Karthik,

    my program working fine with 1st Row but it show System.ArgumentNullException for rest of the rows. how can i handle it.

    Thanks&Regards,
    Harish MS

    Reply

  30. Hi Karthik,

    My program.cs has until now 10 independent tests initialized by [Test] method and for each I need to login with same credentials and finish with driver.Quit();

    When I run each test separately (selecting just one in Test Explorer) they work fine just getting the same data from excel file for username and password but, when I run all of them trough a playlist file, only the first test is passing with the data from the excel.
    I get this error from the 2nd test to the last:
    —————————–
    Message: System.Reflection.TargetInvocationException : Exception has been thrown by the target of an invocation.
    —-> System.ArgumentNullException : text cannot be null
    Parameter name: text
    ———————————

    I’m initializing the excel in the [setup] method :
    ExcelDataLibrary.PopulateInCollection(@”C:\ExcelDataLibrary.xlsx”);

    and in each [Test] is:
    LoginPageObjects loginPage = new LoginPageObjects();
    HomePageObjects HomePage = loginPage.Login(ExcelDataLibrary.ReadData(1, “username”), ExcelDataLibrary.ReadData(1, “password”));

    Why is this happening? Can You help me?

    Reply

  31. Hi Karthik,
    Thank you for the video and tutorial . Nicely presented which helped me a lot

    I have used the same code as you provided but I am getting below error.Kindly help me
    Please find below code and error message. Thank you in advance.

    namespace Automation
    {
    public class ExcelUtil
    {
    private static DataTable ExcelToDatable(string fileName)
    {

    // open file and returns as stream
    FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);

    // create openXmlReader via ExcelReaderFactory
    IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

    var result1 = excelReader.AsDataSet(new ExcelDataSetConfiguration()
    {
    ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
    {
    UseHeaderRow = false
    }
    });

    // Get all tables
    DataTableCollection table = result1.Tables;

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

    // return
    return resultTable;

    }

    static List dataCol = new List();
    public static void PopulateInCollection(string fileName)
    {

    DataTable table = ExcelToDatable(fileName);
    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()
    };
    dataCol.Add(dtTable);

    }
    }
    }

    public static 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();

    return data.ToString();
    }
    catch (Exception e)
    {
    return null;
    }
    }

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

    }
    }

    Test Class

    // Test Class : for same test for 5 clients (client_id)
    // Trying to read client_code from ExcelUtil.ReadData and repeat for 5 clients
    [Test]
    [Category("FunctionalTest")]
    public void DataDriven_FromExcelUtil()
    {

    CcLoginPageObject ccPageLogin = new CcLoginPageObject();
    ClientInstanceLoginPageObject clientLoginPage = new ClientInstanceLoginPageObject();
    ccPageLogin.Login("email@email.com", "password");
    Driver.FindElement(By.XPath("//*[@id='btnLogin']")).Click();

    // Initializing Excel Sheet
    ExcelUtil.PopulateInCollection(@"C:\Safety_Madan\Test\TestData\test1.xlsx");
    Driver.FindElement(By.Id("gridClientsSearch")).SendKeys(ExcelUtil.ReadData(1,"client_id"));

    Driver.FindElement(By.XPath("//*[@id='tabContent0']/table/tbody/tr[2]/td[1]")).Click();
    Driver.FindElement(By.Id("contentPlaceholder_txtcloseButton")).Click();

    Driver.Quit

    }

    // Error:

    Test Name: DataDriven_FromExcelUtil
    Test FullName: Automation.FunctionalTest.DataDriven_FromExcelUtil
    Test Source: C:\Safety_Madan\-localRepoVisualStudioC#\Automation\FunctionalTests.cs : line 660
    Test Outcome: Failed
    Test Duration: 0:00:44.952

    Result StackTrace:
    at OpenQA.Selenium.Remote.RemoteWebElement.SendKeys(String text)
    at Automation.FunctionalTest.DataDriven_FromExcelUtil() in C:\Safety_Madan\-localRepoVisualStudioC#\Automation\FunctionalTests.cs:line 674
    Result Message:
    System.ArgumentNullException : text cannot be null
    Parameter name: text
    Result StandardOutput:
    Opened
    Closed browser

    Reply

    2. Got an solution for below error

      “System.ArgumentNullException : text cannot be null”

      In Program.cs you change this 2 line (also add latest ExcelDataReader and ExcelDataReader.Dataset)

      EAPageObject pageEA = pageLogin.Login(ExcelLib.ReadData(2, “Column0”), ExcelLib.ReadData(2, “Column1”));

      pageEA.FillUserForm(ExcelLib.ReadData(2, “Column2”), ExcelLib.ReadData(2, “Column3”), ExcelLib.ReadData(2, “Column4”));

      Reply

  32. This was a Great Help . I released the excelreader from ExcelToDatable() method by using below and now it fits my code well
    excelReader.Close();
    excelReader.Dispose();
    Thanks Karthik. Very Informative Video.

    Reply

  33. Hi Karthik,
    The video is informative and very helpful. Thanks for the great help.
    In the above example –
    EAPageObject pageEA = pageLogin.Login(ExcelLib.ReadData(2, “Column0”), ExcelLib.ReadData(2, “Column1”));
    Can we not use the actual column name like “TestParm1″,”TestParm1” instead of “Column1″ or #Column1”

    Reply

  34. Hi Karthik,

    excelReader.IsFirstRowAsColumnNames = true;
    look like this is not supported now
    so googled and got this
    var conf = new ExcelDataSetConfiguration
    {
    ConfigureDataTable = _ => new ExcelDataTableConfiguration
    {
    UseHeaderRow = true
    }
    };

    DataSet result = excelReader.AsDataSet(conf);

    After following same steps from video DDT in Selenium with C3 – Part 12 i got below error
    Kindly let me know the what might be the issue

    ExecuteTest
    Source: Program.cs line 30
    Duration: 4 sec

    Message:
    System.IndexOutOfRangeException : Cannot find column 6.
    Stack Trace:
    DataColumnCollection.get_Item(Int32 index)
    ExcelLib.PopulateInCollection(String fileName) line 52
    Program.ExecuteTest() line 32

    Open additional output for this result

    Reply

Leave a Reply

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