Data driven test using Excel file in C# with Selenium RC

Data driven test using Excel file in C# with Selenium RC


Excel file details


File Name: UserList.xls
Sheet Name: Sheet1
Column1: EmailAddress
Column2: Password

I would like to thank Sony Paul and Vishal P Vishwas for their help who made this happen.


Please post comments if you have any doubts OR any kind of clarification required.

using System;

using System.Text;

using System.Text.RegularExpressions;

using System.Threading;

using NUnit.Framework;

using Selenium;

using System.Data.OleDb;

using System.Data;

namespace SeleniumTesting

{

    [TestFixture]

    public class Untitled

    {

        private ISelenium selenium;

        private StringBuilder verificationErrors;

        [SetUp]

        public void SetupTest()

        {

            selenium = new DefaultSelenium("localhost", 4444,"*iehta", "http://www.YourSiteURL.com/");

            selenium.Start();

            verificationErrors = new StringBuilder();

        }

        [TearDown]

        public void TeardownTest()

        {

            try

            {

                selenium.Stop();

            }

            catch (Exception)

            {

                // Ignore errors if unable to close the browser

            }

            Assert.AreEqual("", verificationErrors.ToString());

        }

        [Test]

        public void ExcelDataDrivenTest()

        {

            // Declare a object to use the read data

            DataSet dsLoginDet = new DataSet();

            dsLoginDet = getData();

            // Table name should be same which you have declared in getData() i.e., LoginDet

            // For Loop begins from here

            for (int i = 0; i < dsLoginDet.Tables["LoginDet"].Rows.Count; i++)

            {

                selenium.Open("http://www.YourSiteURL.com/");

                selenium.WaitForPageToLoad("30000");

                //This step will introduce 1 second delay for the following steps. Comment if not required

                selenium.SetSpeed("1000");

                //txtEmailAddress - Name of Email id field textbox

                selenium.Type("txtEmailAddress", Convert.ToString(dsLoginDet.Tables["LoginDet"].Rows[i]["EmailAddress"]));

                //txtPassword - Name of Password field textbox

                selenium.Type("txtPassword",Convert.ToString(dsLoginDet.Tables["LoginDet"].Rows[i]["Password"]));

                //btnLoginAccount - Name of Login button

                selenium.Click("btnLoginAccount");

                selenium.WaitForPageToLoad("30000");

                //btnLogout - Name of Logout button

                selenium.Click("btnLogout");

                selenium.WaitForPageToLoad("30000");

            }

        }

        // Reading a Excel file and getting the data to a Dataset.

        private DataSet getData()

        {

            // Connection String to read a excel file

            string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\UserList.xls';Extended Properties='Excel8.0;HDR=Yes;IMEX=1'";

            OleDbConnection conn = new OleDbConnection(connString);

            OleDbDataAdapter adpDetails = new OleDbDataAdapter("select * from [Sheet1$]", conn);

            DataSet dsDetails = new DataSet();

            //Declaring a Data Table for storing the Excel Data

            //LoginDet will be Table in which we will be storing data

            DataTable dtDetails = new DataTable("LoginDet");

            //EmailAddress is the name of the Column in the Excel which we are reading

            dtDetails.Columns.Add("EmailAddress");

            //Password is the name of the Column in the Excel which we are reading

            dtDetails.Columns.Add("Password");

            dsDetails.Tables.Add(dtDetails);

            //Storing the read data to LoginDet table

            adpDetails.Fill(dsDetails.Tables["LoginDet"]);

            return dsDetails;

        }

    }

}