Warning: fopen(/tmp/SghXTngBZPli-rj5HOj.tmp): failed to open stream: Disk quota exceeded in /home/executea/public_html/blog/wp-admin/includes/class-wp-filesystem-ftpext.php on line 139
Warning: unlink(/tmp/SghXTngBZPli-rj5HOj.tmp): No such file or directory in /home/executea/public_html/blog/wp-admin/includes/class-wp-filesystem-ftpext.php on line 142
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
27 Replies to “DDT with Excel in CUIT (Part 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.
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.
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”];
How would the //Store it in DataTable looks like if I do have “Sheet1” and “Sheet2″ ? Ｉ 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 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.
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.
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?)
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
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?)
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?)
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.
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
“How would the //Store it in DataTable looks like if I do have “Sheet1” and “Sheet2″ ? Ｉ 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”
Hi , I am learning automation with your tutorials. with this above tutorial for DDT in selenium, in that you have used code ” excelReader.IsFirstRowAsColumnNames = true;” when I write this, currently it is discontinued, and When I run this code I am always getting error for excel that column 5 is not found.
Can you please help me with this?
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.
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.
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.
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;
}
Hi when executing the code it says that “Cannot find central directory”
Could you help me with that
Thank you !!!
How would the //Store it in DataTable looks like if I do have “Sheet1” and “Sheet2″ ? Ｉ 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
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]
I also get the excelreaderfactory.cs file not found error. Did anyone find the solution?
Hi Karthik, thank you soooo much for your wonderful videos. Coded UI is a bear for an old WinRunner / QTP person like myself.
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
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
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!
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?)
Hi Vivek, I am seeing the same issues? did you find a fix for this yet?
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
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
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?)
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
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.
Hi Karthik,
I want to write data to existing Excel column. Can you please guide me?
Hi Karthik,
Thanks for the blog.
It looks like below code is not valid in ExcelDatReadder 3.3 version:
excelReader.IsFirstRowAsColumnNames = true;
Could you please suggest an alternate piece of code. I got one solution here:
https://github.com/ExcelDataReader/ExcelDataReader/issues/261
but it would be great if you could suggest any simpler way to set the first row as header.
Best Regards,
Sunil
Change the version from 3.3 to 2.1.2.3 and try..It should work.
Hi Karthik
Can you please share the code for Hybrid framework or keyword driven framework
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
“How would the //Store it in DataTable looks like if I do have “Sheet1” and “Sheet2″ ? Ｉ 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
Hi , I am learning automation with your tutorials. with this above tutorial for DDT in selenium, in that you have used code ” excelReader.IsFirstRowAsColumnNames = true;” when I write this, currently it is discontinued, and When I run this code I am always getting error for excel that column 5 is not found.
Can you please help me with this?