top of page
Search

Selenium C# Tutorial: Read and Use Data from Excel

Updated: Mar 5

We know that Multiple users usually use web applications, and each interacts with the application using their data. So, considering the complexity and overall usage, it becomes the primary responsibility of the automation team to test the web application with varying data sets.


The user's interactions with the app may be the same using the same flows. However, the data set will be different. Therefore, it makes more sense to execute the same test cases with varying sets of data to ensure that the web app functionalities result in the expected results (Instead of writing different test cases for each user flow with each data set).


This is where Microsoft Excel comes in very handy, one of the most frequently used tools for storing data. Excel in selenium is one of the most used combinations for storing test data and then running test flows against various data sets.


In this tutorial, I will use "Helpers" classes, and as the name suggests, they are libraries of code that act like reusable code for the whole framework. These helpers classes work as shred code libraries for all the code within our test framework and project, which refers to them.


Before we begin to write some code, you should know that the time for developing it takes some time since they will have many libraries (of course adding great value) and ease automation code development.


So what is an "Excel" helper mean?

Well, Excel helper acts as a library to read data from excel sheets, parse data, and store data in memory collections. We will do it by using one of the most popular Excel data readers available, called "Excel Data Reader."



Why do we use an in-memory collection to store data?

So why do we need to store data in C# collections? Well, the reason is that Excel sheet is one of the external data sources, meaning that every time we need to read its data, we will have to open an external data source (Meaning that we will use an I/O operation) and rapidly perform I/O operations base on our testing needs (Which consume time and may lead to I/o failures which reduce the reliability of our tests). To overcome it, we can (and should) use C# Collections to store all the data parsed and retrieved via ExcelDataReader. Hence, I will do the following:

  1. Create a custom class.

  2. Create a List for storing data List<CustomClass>.

  3. Populate the ExcelDataReader data into Loth to meet our required format.

Custome Class Defenition

Our custom class will contain the following properties, which we will use as part of the use of Excel data files:

  1. ColumnName - Holds the column name

  2. ColumnValue - Holds the column value

  3. ColumnNumber - Holds all the row numbers


Scenario:

  1. Log-In to my blog at https://www.agilequalitymadeeasy.com/blog

  2. Navigate to the site “Log-In” form.

  3. Select “Sign up with email.”

  4. Insert user details “Email” and “Password” (Negative and Positive Tests).

  5. Validate Success.


So let's start writing some code!


Phase 1: Adding Nuggets to our project

The nuggets we will use in our projects are:

  • ExcelDataReader

  • ExcelDataReader.DataSet



Phase 2: Creating the Excel File and adding it to our project

  1. Create the Excel file in the System.

  2. Add a new folder in the project "Data."

  3. Right-click on folder -> "Open File in Solution Explorer"

  4. Copy your file to this folder.

  5. Right, Click on the Excel File -> "Include in the project."

  6. Right Click on the Excel File -> Properties- > Change "Copy to Output" to "Copy if newer".

Note: If you cannot see the file in the solution explorer, please allow the option of "Show all files."



Phase 3: Create the ExcelData Class

This class will perform all the action items related to the Excel file, such as importing its data and populating it to our tests. 

using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using OpenQA.Selenium;

namespace SeleniumExcel
{
public class ExcelData
{
//Creating the collection we will use to store data 
private static List<DataCollection> dataCollection = new List<DataCollection>();        

//Populating stream data into the "dataCollection" collection 
public static void PopulateInCollection(string Excelfilename)
{
DataTable table = ExcelToDataTable(Excelfilename);

//Iterate through the columns and rows 
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
DataCollection dtTable = new DataCollection()
{
RowNumber = row,
ColumnName = table.Columns[col].ColumnName,
ColumnValue = table.Rows[row - 1][col].ToString()
};

//Add detaile per row 
dataCollection.Add(dtTable);
}
}
}

public static DataTable ExcelToDataTable(String Excelfilename)
{
//Open system file amd returns it as a stream 
using (FileStream stream = File.Open(Excelfilename, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (data) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
//Get all the Tables 
DataTableCollection table = result.Tables;

//Store it in DataTable 
DataTable resultTable = table["Sheet1"];

return resultTable;
}
}
}

public static string ReadData(int rowNumber, string ColumnName)
{
try
{
//Retriving Data using LINQ 
string data = (from colData in dataCollection
where colData.ColumnName == ColumnName && colData.RowNumber == rowNumber
select colData.ColumnValue).SingleOrDefault();

return data.ToString();
}
catch (Exception e)
{
return null;
}
}

//Class properties that will store the Excel data by Row and Column Name 
public class DataCollection
{
public int RowNumber { get; set; }
public string ColumnName { get; set; }
public string ColumnValue { get; set; }
}
}
}


Phase 4: Supporting class for our tests

Prior we can start running our tests, I will create a supporting class containing the Methods we will use: 

using OpenQA.Selenium;
using System;
using System.Collections.Generic;
using System.Text;

namespace SeleniumExcel
{
class Sign_Up_Page
{
public static void ClearLoginInformation(IWebDriver Driver)
{
Driver.FindElement(By.Id("input_input_emailInput_SM_ROOT_COMP11")).Clear();
Driver.FindElement(By.Id("input_input_passwordInput_SM_ROOT_COMP11")).Clear();
}

public static void LoginInformation(string UserEmail, string password, IWebDriver Driver)
{   Driver.FindElement(By.Id("input_input_emailInput_SM_ROOT_COMP11")).SendKeys(UserEmail);
            Driver.FindElement(By.Id("input_input_passwordInput_SM_ROOT_COMP11")).SendKeys(password);
}
}
}


Phase 5: Creating some tests

Now that we have all the framework that supports our tests, let's create a simple Positive and Negative test example:


using Microsoft.VisualStudio.TestTools.UnitTesting;

using OpenQA.Selenium;

using OpenQA.Selenium.Chrome;

using OpenQA.Selenium.Support.UI;


using System;

using System.Text;


namespace SeleniumExcel

{

[TestClass]

public class UnitTest1

{

static IWebDriver Driver;

//Create an instance of the WebDriverWait Class

static WebDriverWait Driver_Wait;


//Create an instance of the ChromeOptions class

static ChromeOptions chromeOptions = new ChromeOptions();