Efficient way of reading data from Excel in Selenium C#

Question

I am creating some test cases in Selenium WebDriver using C# (I can't use Java as client has said so). These are sample test cases which will be used for presenting a demo to client and senior management, so my intentions are to make the test cases as robust as I can.

Currently, I am using an Excel file as input and this excel file contains information for all the elements which are there on the page under test. So, I will be creating one excel per page/module. Further, I am reading this excel file in a separate class (.cs file) and then using that in another class (.cs file). So, you can say this excel is a kind of object repository for me (earlier I used CSV file but then switched to Excel).

For making it more flexible I added following code in Excel class, where I will first find which column contains a matching element name and then using its Locator value (I have done this, because I want to get rid of the sequence in which elements are added in excel file, so that if some other member adds elements in this file, he/she don't need to care about the order of elements in file and same used in code).

Code:

public void GetTestPageElements()
        {

            List<string> rowValue = new List<string> { };

            var ExcelFilePath = "C:\\Dhiman\\Practice-Automation-Form.xls";
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(ExcelFilePath);
            Excel.Worksheet xlWorksheet = xlWorkbook.Worksheets.get_Item("PageElements");
            Excel.Range xlRange = xlWorksheet.UsedRange;
            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;
            //Console.WriteLine(rowCount);
            //Console.WriteLine(colCount);
            for (int j = 1; j <= rowCount; j++)
            {
                rowValue.Add(xlRange.Cells[j, 1].Value2.ToString());
                Console.WriteLine(rowValue[j-1] + "  ");

            }
            Console.WriteLine("");
            //Console.WriteLine(a);
            for (int i = 0; i < rowValue.Count; i++)
            {
                if (rowValue[i].Equals("Partial Link Test"))
                {
                    LocatorOfPartialLinkTest = xlRange.Cells[i + 1, 2].Value2.ToString();
                }
                if (rowValue[i].Equals("Link Test"))
                {
                    LocatorOfLinkTest = xlRange.Cells[i + 1, 2].Value2.ToString();
                }
                if (rowValue[i].Equals("First name"))
                {
                    LocatorOfFirstName = xlRange.Cells[i + 1, 2].Value2.ToString();
                }
                if (rowValue[i].Equals("Last name"))
                {
                    LocatorOfLastName = xlRange.Cells[i + 1, 2].Value2.ToString();
                }
                if (rowValue[i].Equals("Sex"))
                {
                    LocatorOfSex = xlRange.Cells[i + 1, 2].Value2.ToString();
                }
                if (rowValue[i].Equals("Years of Experience"))
                {
                    LocatorOfExperience = xlRange.Cells[i + 1, 2].Value2.ToString();
                }
                if (rowValue[i].Equals("Date"))
                {
                    LocatorOfDate = xlRange.Cells[i + 1, 2].Value2.ToString();
                }
            }
            rowValue.Clear();
            xlWorkbook.Close();
            xlApp.Quit();
        }

Excel File:

Issues:

1. I fear that if there are 50 or more such elements on a page then I will end up with lots of IF statements, is this a correct and efficient way of doing this?
2. Will using such a large number of conditions, make my test case slow for execution?
3. What's the alternative/better way of doing this like using CSV/XML instead of Excel, etc.?

Answer 1

I found one more way (definitely there can be other ways too) to do this, and using that I don't need to use IF statement and Loop. This has also reduced my lines of code by 50%.

LocatorOfPartialLinkTest = xlRange.Cells[rowValue.IndexOf("Partial Link Test") + 1, 2].Value2.ToString();
LocatorOfLinkTest = xlRange.Cells[rowValue.IndexOf("Link Test") + 1, 2].Value2.ToString();
LocatorOfFirstName = xlRange.Cells[rowValue.IndexOf("First name") + 1, 2].Value2.ToString();    
LocatorOfLastName = xlRange.Cells[rowValue.IndexOf("Last name") + 1, 2].Value2.ToString();

Posting it as an answer, because I found this as an alternate to my existing implementation.

Answer 2

Even many hundreds of if statement will not cause any noticeable slowdown, but you may consider switch/case statement

If your goal is only parsing and reformatting text files, you may consider learning IronPython. It has .NET bindings (so you can create Excel spreadsheets and write to them), and you will learn new skill :-) One of best uss cases for python is flexible text manipulation.

SQLite is flexible and simple SQL (single user) database, if you want simple yet flexible persistent data repository.

Warning: After you learn Python, C# will seem to you clunky. I know that Python did it to me, and I had to switch jobs :-/

Answer 3

I would recommend using LINQ. This will allow you to 'query' the spreadsheet and pull in the data based on these queries. An example using excel can be found here.

As for the execution of it, I would use an enum or array of the different parameters accepted, select those items and then process by the result.

If the test cases grow out of hand (IE millions/billion of rows) than this may have issues iterating over every row, but I wouldn't think this would be a legitimate problem.