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.

string[] ReadAllDataInColumn(string columnName, string sheetName, string DataFileName)

2.

string[] ReadAllRowDataBetweenColumns(string startColumnName, string endColumnName, string sheetName, string DataFileName)

3.

string[] ReadAllDataBetweenRows(int startRowNumber, int endRowNumber, string sheetName, string DataFileName)

4.

Dictionary<string, string>[] ReadAllDataWhereFirstRowIsHeader(string sheetName, string DataFileName)

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.



Rename as ExcelReader.dll

using ExcelReader;

Let me if it’s not working at your end.

Here is the code:

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

 

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)

42 Comments

  • Scott

    October 12, 2012

    Nice and clean code. Downloaded dll and used in my script and It really worked. Thanks!

    Reply
    • admin

      October 12, 2012

      Your welcome Scott.

      Reply
      • Yaniv

        December 21, 2015

        Hi,
        Thanks.
        Do you have something similar to write data into excel file?
        Thanks again,
        Yaniv

        Reply
        • Aditya

          December 21, 2015

          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.

          Reply
  • Shannon

    March 22, 2013

    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 .

    Reply
    • Shannon

      March 22, 2013

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

      Reply
      • Aditya Kumar

        March 28, 2013

        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…

        Reply
    • Aditya Kumar

      March 28, 2013

      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.

      Reply
  • sathish

    September 4, 2014

    How to use this dll file in my project?. Am using selenium webdriver using C#…. Please advice

    Reply
    • Aditya

      September 4, 2014

      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.

      Reply
      • Nasim

        March 31, 2016

        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.

        Reply
        • Aditya

          April 7, 2016

          Hi Nasim,
          Check out your email and see if that solves your issue.

          Reply
  • Christus

    September 5, 2014

    Hi,

    Can we use the same dll to test the xml file

    thanks

    Reply
    • Aditya

      September 5, 2014

      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.

      Reply
  • arun

    November 28, 2014

    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

    Reply
    • Aditya

      December 2, 2014

      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.

      Reply
  • arun

    December 3, 2014

    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.

    Reply
    • Aditya

      December 3, 2014

      Hi Arun,
      Check your inbox please.

      Reply
      • Arun

        December 4, 2014

        Hi Aditya,
        S checked Aditya, i have not rcvd any mails from this post (expect December 2, 2014 at 10:51 PM your mail).

        Reply
  • Ozan

    December 8, 2014

    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?

    Reply
    • Aditya

      December 21, 2015

      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.

      Reply
  • Jagadish

    February 4, 2015

    Hi Aditya,

    Thanks for sharing the code and detailed steps.

    It really helped me.

    Reply
  • Siri

    June 5, 2015

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

    }

    Reply
    • Aditya

      July 4, 2015

      Sorry that I missed this question. Did you get this resolved?

      Reply
  • Syed

    November 16, 2015

    Hi Aditya,

    Can you pls post the same in java code?

    Reply
    • Aditya

      November 16, 2015

      Hi Syed,
      Do you want duplicated code in java or just basic code to read excel in java that you can extend?

      Reply
  • CG

    February 22, 2016

    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!

    Reply
    • Aditya

      February 22, 2016

      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?

      Reply
      • CG

        February 22, 2016

        Yes, exactly what I’m looking for!

        Thanks.

        Reply
        • Aditya

          February 24, 2016

          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.

          Reply
  • Michelle

    February 29, 2016

    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.

    Reply
    • Aditya

      March 1, 2016

      Michelle,
      Glad that dll is working fine. May I know which version of VS are you using? 2010? or 2013?

      Reply
  • Michelle

    March 2, 2016

    Hi,

    I’m using VS 2012 + update SP4 and the version of the Microsoft.Office.Interop.Excel is 14.0.0.

    Thanks,
    Mihaela

    Reply
    • Aditya

      March 3, 2016

      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.

      Reply
  • Michelle

    March 3, 2016

    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 :)

    Reply
  • Nasim

    April 16, 2016

    I have used this excel based framework for accessing the data in the application.
    It is working fine with some minor changes.

    Thank you :)

    Reply
  • Vish

    June 11, 2016

    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?

    Reply
    • aditya

      June 13, 2016

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

      Reply
  • Tina

    January 31, 2017

    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.

    Reply
    • Aditya

      February 2, 2017

      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.

      Reply
      • Tina

        February 8, 2017

        Thanks. i will try that way :)

        Reply

Leave a Reply