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.

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)