Using Excel for data driven testing with WebDriver and C#.

Using Excel for data driven testing with WebDriver and C#.

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.

ExcelReaderAsDictionary
ExcelReaderAsDictionary

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)

44 Comments

  1. 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 .

    1. 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));

    2. Aditya Kumar

      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.

    1. Aditya

      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.

      1. Nasim

        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.

        1. Thato

          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

    1. Aditya

      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.

  2. arun

    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

    1. Aditya

      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.

  3. arun

    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.

  4. 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?

  5. Siri

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

    }

    1. Aditya

      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?

        1. Aditya

          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.

  6. Michelle

    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.

    1. Aditya

      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.

  7. Michelle

    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 🙂

  8. Vish

    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?

    1. aditya

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

  9. Tina

    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.

    1. Aditya

      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.

Leave a Reply

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