Browse Tag: 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.

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)

Image Verification

Webdriver and Image Verification

Can you do image verification if it loaded on webpage?

 Yes! You can verify but you cannot say that images are half loaded. To verify if image is correct then there would be some other method but code will tell you just that images are loaded on page or not.

You can send http request to that source using GET method and check the status code as 200. if that the case then your image downloaded properly and image is there. 

Please drop your comments if you think if I am missing anything. If you have other idea to do image verification, please post in comment.