
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
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?
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
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 ?
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
Hope this should help !!!
Thanks,
Karthik KK
Thanks for the quick responses Karthik…!!! i will try out the same. 🙂
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
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
Hi Karthik,
I am getting a out of range exception when i reused the excel library above.
pls advice.
Thanks,
Subha
I am too…. 🙁
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.
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
Found Solution
What was your solution, I’m having the same problem
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.
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.
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
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
Just remove “<" sign from second loop while reading the data from data table
Sorry remove “+ ” sign
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.
Thanks! It was helpful to me:)
I am debugging the program and found that after iteration for column 2 , there is no data thatswhy its giving exception.
Oh good.
Atleast you found the problems root cause.
Thanks,
Karthik KK
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
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
And now your code will start to work for .xls as well !!!
Thanks,
Karthik KK
Please suggest me some solution for this problem.
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
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
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
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!
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.
It seems this solved problem for me too. Thanks for sharing resolvation. Greetz!
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.
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?
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:)
Great videos, thanks Karthik!
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
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
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!
Nope, put the line of code in For loop
Hope it makes sense !!
Thanks,
Karthik KK
can you please let us know ..how to write the data in excel sheet
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
The code looks good to me, can you tell me in which line you are getting the exception.
Thanks,
Karthik KK
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
Try removing the less than or equal to just less than, something like this
Thanks,
Karthik KK
I’m trying to collect data from Website and copy to Excel sheet.
Could someone help me in this part of code please ??
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)
Can you tell exactly in which line ?
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
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!
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
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.
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?
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.
Great !!
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.
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[] { });
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.. 🙁
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,
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.
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,
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.
Yes, as mentioned in the video, the first row is always considered as column name if set to true.
Thanks,
Karthik KK
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
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
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
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
Awesome !
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
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?
Hi,
I am running into the same issue. Were you able to resolve this issue by any chance?
Thanks,
Mike.
Hi Luis,
I am able to resolve this issue by adding the below in MyTestCleanup ():
ExcelUtil.dataCol.Clear();
Where you made a change and add ExcelUtil.dataCol.Clear();
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
Did you got any solution for this issue
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”));
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.
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”
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
Thanks Rakesh, but I added an video as well just in case https://www.youtube.com/watch?v=_h_4-HxrMMc
Thanks,