For data driven testing we need to create some kind of repository like excel or xml or any database to save our test data. XML is most popular one becuae accessing XML is quite easy and there are various dlls are easily available which we can download and start using if our data is not that complex. In some case where we need to customize our test data in excels file. I tried to write a simple code using C# and read data in excel. follwing table is a sample data sheet in Excel.
Excel DataFile: Name: TestDataFile.xlsx
There are three different methods to access the data.
1.
[sourcecode language=”csharp”]string[] ReadAllDataInColumn(string columnName, string sheetName, string DataFileName)[/sourcecode]
2.
[sourcecode language=”csharp”]string[] ReadAllRowDataBetweenColumns(string startColumnName, string endColumnName, string sheetName, string DataFileName)[/sourcecode]
3.
[sourcecode language=”csharp”]string[] ReadAllDataBetweenRows(int startRowNumber, int endRowNumber, string sheetName, string DataFileName)[/sourcecode]
4.
[sourcecode language=”csharp”]Dictionary<string, string>[] ReadAllDataWhereFirstRowIsHeader(string sheetName, string DataFileName)[/sourcecode]
You can pass corresponding input and get the data back.
a: In the output, header data will not be included.
b: Header row is considered as row 0.
c: For NULL cell value output returned as null which you can use.
d: Multiple column data appear in row with delimiter(~) which you can modify while using that.

Download this dll and use this as per your test plan.
[wpdm_package id=’16376′]
Rename as ExcelReader.dll
[sourcecode language=”csharp”]using ExcelReader;[/sourcecode]
Let me if it’s not working at your end.
Here is the code:
[sourcecode language=”csharp”]
using System ;
using System.Collections.Generic;
using System.Diagnostics;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
namespace ExcelReader
{
public sealed class xlReader
{
[DllImport("user32.dll")]
private static extern IntPtr GetWindowThreadProcessId(IntPtr hWnd, out IntPtr ProcessId);
public static string[] ReadAllDataInColumn(string columnName, string sheetName, string DataFileName)
{
List<string> iList = new List<string>();
Application appClass = new Application();
IntPtr hwnd = new IntPtr(appClass.Hwnd);
IntPtr processId;
try
{
Workbook workBook = appClass.Workbooks.Open(DataFileName, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Worksheet sheet = (Worksheet) workBook.Sheets[sheetName];
Range excelRange = sheet.UsedRange;
object[,] valueArray = (object[,]) excelRange.Value[XlRangeValueDataType.xlRangeValueDefault];
int usedCol = excelRange.Columns.Count;
int totalcell = excelRange.Count;
int usedRow = totalcell/usedCol;
for (int i = 1; i <= usedCol; i++)
{
if (valueArray[1, i].ToString() == columnName)
{
for (int j = 2; j <= usedRow; j++)
{
if (valueArray[j, i] == null)
{
iList.Add(null);
}
else
{
iList.Add(valueArray[j, i].ToString());
}
}
}
}
workBook.Close();
appClass.Quit();
if (sheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}
if (excelRange != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelRange);
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
}
IntPtr foo = GetWindowThreadProcessId(hwnd, out processId);
Process proc = Process.GetProcessById(processId.ToInt32());
proc.Kill(); // set breakpoint here and watch the Windows Task Manager kill this exact EXCEL.EXE
GC.Collect();
}
catch (Exception)
{
IntPtr foo = GetWindowThreadProcessId(hwnd, out processId);
Process proc = Process.GetProcessById(processId.ToInt32());
proc.Kill();
GC.Collect();
iList.Add("Invalid Datafile or sheetName. Please Check");
}
return iList.ToArray();
}
public static string[] ReadAllRowDataBetweenColumns(string startColumnName, string endColumnName, string sheetName, string DataFileName)
{
int startC = -1;
int endC = -1;
List<string> iList = new List<string>();
Application appClass = new Application();
IntPtr hwnd = new IntPtr(appClass.Hwnd);
IntPtr processId;
try
{
Workbook workBook = appClass.Workbooks.Open(DataFileName, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing
);
int numSheets = workBook.Sheets.Count;
Worksheet sheet = (Worksheet) workBook.Sheets[sheetName];
Range excelRange = sheet.UsedRange;
object[,] valueArray = (object[,]) excelRange.Value[XlRangeValueDataType.xlRangeValueDefault];
int usedCol = excelRange.Columns.Count;
int totalcell = excelRange.Count;
int usedRow = totalcell/usedCol;
for (int i = 1; i <= usedCol; i++) { if (valueArray[1, i].ToString() == startColumnName) { startC = i; } if (valueArray[1, i].ToString() == endColumnName) { endC = i; } } if (startC == -1) { string errorInC1 = "Invalid Start ColumnName: " + startColumnName + ". Plesae check the case of header."; iList.Add(errorInC1); } if (endC == -1) { string errorInC2 = "Invalid End ColumnName: " + endColumnName + ". Plesae check the case of header."; iList.Add(errorInC2); } if (startC > -1 && endC > -1)
{
for (int i = 2; i <= usedRow; i++)
{
string rowElements = string.Empty;
string cellElement;
if (endC < startC) { for (int j = startC; j >= endC; j–)
{
if (valueArray[i, j] == null)
{
cellElement = string.Empty;
}
else
{
cellElement = valueArray[i, j].ToString();
}
rowElements += cellElement + "~";
}
}
else //for reverse rows.
{
for (int j = startC; j <= endC; j++) { if (valueArray[i, j] == null) { cellElement = "NULL"; } else { cellElement = valueArray[i, j].ToString(); } rowElements += cellElement + "~"; } } iList.Add(rowElements.TrimEnd(new char[] {‘~’})); } } workBook.Close(); appClass.Quit(); if (sheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); } if (excelRange != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelRange); } if (workBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook); } IntPtr foo = GetWindowThreadProcessId(hwnd, out processId); Process proc = Process.GetProcessById(processId.ToInt32()); proc.Kill(); GC.Collect(); } catch (Exception) { IntPtr foo = GetWindowThreadProcessId(hwnd, out processId); Process proc = Process.GetProcessById(processId.ToInt32()); proc.Kill(); GC.Collect(); iList.Add("Invalid Datafile or sheetName. Please Check"); } return iList.ToArray(); } public static string[] ReadAllDataBetweenRows(int startRowNumber, int endRowNumber, string sheetName, string DataFileName) { if (endRowNumber > startRowNumber)
{
List<string> iList = new List<string>();
Application appClass = new Application();
IntPtr hwnd = new IntPtr(appClass.Hwnd);
IntPtr processId;
try
{
Workbook workBook = appClass.Workbooks.Open(DataFileName, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing
);
Worksheet sheet = (Worksheet) workBook.Sheets[sheetName];
Range excelRange = sheet.UsedRange;
object[,] valueArray = (object[,]) excelRange.Value[XlRangeValueDataType.xlRangeValueDefault];
int usedCol = excelRange.Columns.Count;
int totalcell = excelRange.Count;
int usedRow = totalcell/usedCol;
if (startRowNumber > usedRow)
{
startRowNumber = usedRow;
}
for (int i = startRowNumber + 1; i <= endRowNumber + 1; i++)
{
string rowElements = string.Empty;
for (int j = 1; j <= usedCol; j++)
{
string cellElement;
if (valueArray[i, j] == null)
{
cellElement = null;
}
else
{
cellElement = valueArray[i, j].ToString();
}
rowElements += cellElement + "~";
}
iList.Add(rowElements.TrimEnd(new char[] {‘~’}));
}
workBook.Close(null, null, null);
appClass.Quit();
if (sheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}
if (excelRange != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelRange);
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
}
IntPtr foo = GetWindowThreadProcessId(hwnd, out processId);
Process proc = Process.GetProcessById(processId.ToInt32());
proc.Kill(); // set breakpoint here and watch the Windows Task Manager kill this exact EXCEL.EXE
GC.Collect();
}
catch (Exception)
{
IntPtr foo = GetWindowThreadProcessId(hwnd, out processId);
Process proc = Process.GetProcessById(processId.ToInt32());
proc.Kill();
GC.Collect();
iList.Add("Invalid Datafile or sheetName. Please Check");
}
return iList.ToArray();
}
else
{
string[] errorOutput ={"End Row Is Greater Than Start Row"};
return errorOutput;
}
}
public static Dictionary<string, string>[] ReadAllDataWhereFirstRowIsHeader(string sheetName, string DataFileName)
{
List<string> iList = new List<string>();
Application appClass = new Application();
IntPtr hwnd = new IntPtr(appClass.Hwnd);
IntPtr processId;
Dictionary<string, string>[] rowsDictionary = null;
try
{
Workbook workBook = appClass.Workbooks.Open(DataFileName, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Worksheet sheet = (Worksheet)workBook.Sheets[sheetName];
Range excelRange = sheet.UsedRange;
object[,] valueArray = (object[,])excelRange.Value[XlRangeValueDataType.xlRangeValueDefault];
int usedCol = excelRange.Columns.Count;
int totalcell = excelRange.Count;
int usedRow = totalcell / usedCol;
rowsDictionary = new Dictionary<string, string>[usedRow – 1];
for (int i = 2; i <= usedRow; i++)
{
rowsDictionary[i – 2] = new Dictionary<string, string>();
for (int k = 1; k <= usedCol; k++)
{
if (valueArray[1, k] != null)
rowsDictionary[i – 2].Add(valueArray[1, k].ToString(), valueArray[i, k].ToString());
}
}
workBook.Close();
appClass.Quit();
if (sheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}
if (excelRange != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelRange);
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
}
IntPtr foo = GetWindowThreadProcessId(hwnd, out processId);
Process proc = Process.GetProcessById(processId.ToInt32());
proc.Kill(); // set breakpoint here and watch the Windows Task Manager kill this exact EXCEL.EXE
GC.Collect();
}
catch (Exception)
{
IntPtr foo = GetWindowThreadProcessId(hwnd, out processId);
Process proc = Process.GetProcessById(processId.ToInt32());
proc.Kill();
GC.Collect();
iList.Add("Invalid Datafile or sheetName. Please Check");
}
return rowsDictionary;
}
}
}
[/sourcecode]
I tried to cover all possible basic scenarios. You can tweak the code as per your need.
Note: Based on CG’s request I added another method in above code where you can see result array as an array of dictionary and each dictionary will have column as key and its corresponding value as value.(2/23/2016)
Nice and clean code. Downloaded dll and used in my script and It really worked. Thanks!
Your welcome Scott.
Hi,
Thanks.
Do you have something similar to write data into excel file?
Thanks again,
Yaniv
Yaniv ,
I do not have any for now but I can write definitely . If you can share your requirement so I can write the code based on that.
Thanks for the code. I have a newbie question though. How do you pass the first value in the sheet and so on?
I am thinking SendKeys(String.Format(“//option[contains(text(), {0})]”, x)); but I am missing something important .
Also Nunit keeps throwing “invalid datafile or sheet name”…any advice is mucho appreciated
const string excelfile = @”C:\Users\SH\ExcelTestData.xlsx”;
string targetCol = “A”;
const string sheetname = “page”;
string[] xy = xlReader.ReadAllDataInColumn(targetCol, sheetname, excelfile);
Console.WriteLine(String.Format(“//option[contains(text(), {0})]”, xy));
in invalid dataSfile or sheet name issue. if you please email me your file with some data similar to that you are using I can look at that…
Look like you are trying to pass xy as single value and output of any method given above. If that so then please pass the value not an array in this SendKey() method as the retrun type for methods are an array and this will accept one value not array.
How to use this dll file in my project?. Am using selenium webdriver using C#…. Please advice
download the dll,
add into your reference as usual.
add “using ExcelReader;”
if needed then check if “using System ;
using System.Collections.Generic;
using System.Diagnostics;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;” are into code page.
start using methods. i.e. string[] ReadAllDataInColumn(string columnName, string sheetName, string DataFileName) etc.
Let me know if doesn’t work.
Hi,
my query is, there are 10 rows of employee records and I want to read all 10 rows value and set it to employee. from first row it will add 1 employee record then from next row and so on.
So how do you do that?
Please send me examples.
Hi Nasim,
Check out your email and see if that solves your issue.
Hello All,
I know I am a bit behind, but this code seems to have helped a lot of ppl, Thank you very much for the awesome work done here Aditya, how ever I am getting erros and failing to read the xml file with the code, I am new in Automation, kindly assist with the novice step by step of what to do with the dll file once downloaded.
Warm regards
Thato
Sorry for being this late but did you find the answer?
Hi,
Can we use the same dll to test the xml file
thanks
Hi Christus,
Sorry but this dll is only for Excel. But I like your idea to have dll ready for XML as well.
Can you please share the XML template you are using so I can write another dll to read xml.
Thanks for you great idea. Keep posting.
Hi team..
Hats off to u, many thanks
Finally i got correct project, can u pls explain this in video and pls add UI also, it wll help alot for me…
Pls reply soon..
Once again huge thanks
Hi Arun,
Thanks for you kind words,
What kind of info you want in video. as per blog you can download the dll file attached in the blog. Add into your reference, instantiate the class and use the method given in the screenshots.
Hi Aditya,
Thanks for reply..
I want this project with user form – is this possible..?, and pls explain this in video, this is valuable project for me..
Iam beginner for C sharp, so asking..Thanks Aditya.
Hi Arun,
Check your inbox please.
Hi Aditya,
S checked Aditya, i have not rcvd any mails from this post (expect December 2, 2014 at 10:51 PM your mail).
I sent you an email to your gmail id from [email protected]. Make sure you check your spam folder.
Hi Aditya, first i want to send my thanks to you for this sharing. It’s working well for me.
But i want to ask you that;
I have an excel doc. and i read it with your dll. but i need to write too on the same excel doc.
How can i write data in that same excel? Can you help me for it?
Ozan, Thanks for your kind words. if you can email me or share your requirement what you want to write, I can try to write code and post on this site.
Hi Aditya,
Thanks for sharing the code and detailed steps.
It really helped me.
Hai aditya,
My code is showing errors from this line
iList.Add(rowElements.TrimEnd(new char[] {‘~’}));
}
workBook.Close(null, null, null);
appClass.Quit();
if (sheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}
if (excelRange != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelRange);
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
}
IntPtr foo = GetWindowThreadProcessId(hwnd, out processId);
Process proc = Process.GetProcessById(processId.ToInt32());
proc.Kill(); // set breakpoint here and watch the Windows Task Manager kill this exact EXCEL.EXE
GC.Collect();
}
catch (Exception)
{
IntPtr foo = GetWindowThreadProcessId(hwnd, out processId);
Process proc = Process.GetProcessById(processId.ToInt32());
proc.Kill();
GC.Collect();
iList.Add(“Invalid Datafile or sheetName. Please Check”);
}
return iList.ToArray();
}
else
{
string[] errorOutput ={“End Row Is Greater Than Start Row”};
return errorOutput;
}
}
}
}
Sorry that I missed this question. Did you get this resolved?
Hi Aditya,
Can you pls post the same in java code?
Hi Syed,
Do you want duplicated code in java or just basic code to read excel in java that you can extend?
Hi,
When doing a “ReadAllRowDataBetweenColumns”, is there a way to access each value by the column name? Or do i have to do a split by ~ then access by index num?
Thanks!
CG,
In this code, you have to split by ~ but you raise good point and one of the readers also asked over email for similar thing in past. I think I should write another methods to read your values like row[0][colName].
Is this what you were looking for?
Yes, exactly what I’m looking for!
Thanks.
CG,
I have updated the blog, code and dll as well. Please check this out. Now the code will read first row as header and the output will be something like
valueUnderHeaderColInFirstRow = result[0][“Header”]
valueUnerLastNameColumnInSecondRow = result[1][“LastName”].
Try with .dll and let me know if that works.
Hi Aditya,
It is very nice the code, thank you 🙂
One question I’m trying to use the class ExcelReader for further modifications and I have some errors like “One or more types required to compile a dynamic expression cannot be found. Are you missing a reference?” at lines
1. Worksheet sheet = (Worksheet) workBook.Sheets[sheetName];
2. object[,] valueArray = (object[,]) ExcelRange.Value[XlRangeValueDataType.xlRangeValueDefault];
I did add the reference “using Microsoft.Office.Interop.Excel;” do I miss something?
Thank you for your help, dll file is working perfect.
Michelle,
Glad that dll is working fine. May I know which version of VS are you using? 2010? or 2013?
Hi,
I’m using VS 2012 + update SP4 and the version of the Microsoft.Office.Interop.Excel is 14.0.0.
Thanks,
Mihaela
Hi Mihaela,
It should work fine but before doing more investigation, just curious if you have MSOffice installed on the machine you are working on. If Yes then what is the version. I will try to replicate same setup on my local and see if I can reproduce.
I think is something about the versions of the VS and reference. Now I use the dll file and it is working to perfect, no need to invest time in reproducing it.
Thank you again 🙂
I have used this excel based framework for accessing the data in the application.
It is working fine with some minor changes.
Thank you 🙂
Hi Aditya,
Thanks for this Utility. Making my job easier here 🙂
Needed one small help. I am iterating through all the rows/columns in my excel. There is a step where i need to read the 10th (cell) value in Row 1 & choose a value that is the available in 5th(cell) in the row.
Can you help me out?
Thank Vish for using utility and I am glad that it’s working for you.
To read specific cell you can have another method extending the same code but reading the file.
Just hardcode the cell value in following:
cellElement = valueArray[i, j].ToString();
Hi Aditya
I have an application with 5 pages . I need to fetch data from excel and Click continue to next page. Again fetch data from excel and Click Continue to next page like that. Is that possible to do that using Selenium c#. I am totally new to automation. Please excuse my ignorance.
Yes, You can do that but it’s not recommended to read external files specially heavyweight Excel. I would suggest to read all data for all 5 pages in dictionary or other DataStructure so your code can remember and then keep changing page and get data from from next object.
Thanks. i will try that way 🙂