Creating a data-driven test framework using C#, Visual Studio and MS Excel

In this tutorial I will provide the fundamental principles with code for running test data with Selenium from an MS Excel spreadsheet.

In its most fundamental form, data-driven testing is a test automation framework where the data that ‘drives’ the testing is not hard-coded but taken from a table external to the source code and used by the test scripts during execution. Often (but not essentially) expected results will also be available in a table to verify the results.

In this example we’re going to keep things simple with the data source as an Excel spreadsheet. This makes manual input of source data practical and deskills the task of inputting the test data that comes with using an advanced database management system for this job.

Firstly, we need our Selenium code. For this example we’re going to login to gmail as a theorical user whose Google account id is Serious.Business.Example and whose Password is ‘Password123456’. Replace these with an actual Google account if you have one to see a more promising end result.

The Source Data

Lets keep things simple – one row, four columns. You need to create the following Excel sheet:

Email: Serious.Business.Example Password: Password123456

Setting Up Visual Studio

The first thing we need to do is set up a new test.

If you prefer to use Nunit (and already know what you’re doing) feel free to skip this step.

From the Test menu on the top corner, click New Test. You will be given the New Unit Text dialogue box.

basicTest

Here, enter a name for the test (remembering to keep the .cs file extension) and click Ok.

Following this, you will be asked to create a project/solution. Name this and click Ok to complete the project setup.

Now you should be looking at a fresh sheet of C# code. Add references to the project to use Selenium and Excel.

If you haven’t already download the Selenium C# libraries from NuGet, links below:

http://docs.seleniumhq.org/download/

I highly recommend the NuGet Package Manager extension for Visual Studio for handling this task. See separate tutorials for using this to add references:

http://visualstudiogallery.msdn.microsoft.com/27077b70-9dad-4c64-adcf-c7cf6bc9970c

Set up references in C#. You will need first the Selenium references.

references2

To add references, right click the References in the Solution Viewer, click Add Reference and click on the Browse tab to locate your Selenium .dll’s downloaded earlier.

To add the MS Office reference, click the .NET tab of the Add References dialogue and locate the MicrosoftOffice.Interop.Excel library. Do the same for Microsoft.CSharp.

references

Onto the code:

First, here’s the test case in its entirety before we break it down:

using System;
using System.Text;
using System.Collections.Generic;
using System.Threading;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Microsoft.CSharp;
using OpenQA.Selenium;
using OpenQA.Selenium.Firefox;
using OpenQA.Selenium.Support.UI;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExampleTest1
{
[TestClass]
public class ExampleTest
{
[TestMethod]
public void TestMethod1()
{
string workbookPath = “C:\\Users\\slewin\\Documents\\theExample.xlsx”;

Excel.Application excelApp;
Excel.Workbook excelWorkbook;
Excel.Sheets excelSheets;
Excel.Worksheet excelWorksheet;

string currentSheet = “Sheet1”;

excelApp = new Excel.Application();
excelWorkbook = excelApp.Workbooks.Add(workbookPath);
excelSheets = excelWorkbook.Sheets;
excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

Excel.Range myIDBinder = (Excel.Range)excelWorksheet.get_Range(“B1”, “B1”);
string myID = myIDBinder.Value.ToString();

Excel.Range myPasswordBinder = (Excel.Range)excelWorksheet.get_Range(“D1”, “D1”);
string myPassword = myPasswordBinder.Value.ToString();

string baseURL = “http://google.co.uk”;

FirefoxDriver driver = new FirefoxDriver();

driver.Navigate().GoToUrl(baseURL + “/”);
driver.FindElement(By.CssSelector(“#gb_23 > span.gbts”)).Click();
Thread.Sleep(3000);
driver.FindElement(By.Id(“Email”)).Clear();
driver.FindElement(By.Id(“Email”)).SendKeys(myID);
driver.FindElement(By.Id(“Passwd”)).Clear();
driver.FindElement(By.Id(“Passwd”)).SendKeys(myPassword);
driver.FindElement(By.Id(“signIn”)).Click();

}
}
}

Firstly we need references for the above mentioned libraries.

using System;
using System.Text;
using System.Collections.Generic;
using System.Threading;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Microsoft.CSharp;
using OpenQA.Selenium;
using OpenQA.Selenium.Firefox;
using OpenQA.Selenium.Support.UI;
using Excel = Microsoft.Office.Interop.Excel;

Now we want to set up the test Excel interaction.

We set up instances of each Excel object we need – the application, the workbook itself, the sheets within that workbook (in case we need to change between them) and finally the actual worksheet to use at this time.

<code>Excel.Application excelApp;
Excel.Workbook excelWorkbook;
Excel.Sheets excelSheets;
Excel.Worksheet excelWorksheet;</code>

Then populate these with useable details, using the string currentSheet to choose ‘Sheet1’ as our current sheet.

<code>string currentSheet = "Sheet1";

excelApp = new Excel.Application();
excelWorkbook = excelApp.Workbooks.Add(workbookPath);
excelSheets = excelWorkbook.Sheets;
excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);</code>

Now we need to take this data from the Excel objects and turn it into strings. We do this by using transitory ‘bind’ variables to grab the Excel.Range data from a given range (obviously, a range of one cell only returns the contents of that cell), then converting those to strings with the method .Value.ToString(). Remember you cannot implicitly use an object as a string.


Excel.Range myIDBinder = (Excel.Range)excelWorksheet.get_Range("B1", "B1");
string myID = myIDBinder.Value.ToString();

Excel.Range myPasswordBinder = (Excel.Range)excelWorksheet.get_Range(“D1”, “D1”);
string myPassword = myPasswordBinder.Value.ToString();

From this point I will assume some knowledge of Selenium webdriver, but in short: we create our driver as ‘driver’, an instance of FirefoxDriver, then use this to navigate to Gmail from the BaseURL ‘http://Google.co.uk&#8217;.

string baseURL = "http://google.co.uk";

FirefoxDriver driver = new FirefoxDriver();

driver.Navigate().GoToUrl(baseURL + “/”);
driver.FindElement(By.CssSelector(“#gb_23 > span.gbts”)).Click();
Thread.Sleep(3000);
driver.FindElement(By.Id(“Email”)).Clear();

And finally the good stuff: we use the data from the Excel sheet to drive this test, having turned the Excel object return values into string variables. (The Thread.Sleep is just there as a dirty work-around for the loading time).


Thread.Sleep(3000);
driver.FindElement(By.Id("Email")).Clear();
driver.FindElement(By.Id("Email")).SendKeys(myID);
driver.FindElement(By.Id("Passwd")).Clear();
driver.FindElement(By.Id("Passwd")).SendKeys(myPassword);
driver.FindElement(By.Id("signIn")).Click();

That concludes a basic overview of data-driven testing using Selenium with Excel. With some imaginative use of control loops, adding database connectivity and using Selenium’s ability to check and store results, data-driven testing can be a very powerful tool.

This entry was posted in Uncategorized. Bookmark the permalink.

3 Responses to Creating a data-driven test framework using C#, Visual Studio and MS Excel

  1. isha says:

    how we can take multiple entries from excel and run in test

  2. Hi, nice post….But i would like to get a detailed description of how to automatically get the test cases line by line

    • lewinstephan says:

      Hi Prince.

      Do you mean get the steps to perform, or the data? Getting the steps to perform the test cases line by line would come under keyword-driven testing (using prompts from keywords in the external data to trigger test steps). Is this what you mean? Otherwise please give more detail and I’ll see if I can help.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s