Advanced Selenium automation with C# (Data driven testing using Excel.)

May 6, 2017, autoqa, 0

Data driven testing using Excel:

Data driven testing is a testing in which test data/ expected data is got from an external source such as Excel files,  CSV files, ODBC sources.  Data driven testing should be used when the testing relays on a huge number of input data, for example, Online Application Form . This application has a lot of different data combination and permutation which is hard to handle and needs pulling from the external data source.

How to execute Data driven testing:

• Read data from external data source
• Populate data on UI by data entry operation.

Here is the code Read data from Excel  example:

We follow bellow steps:

 

/// 1. Define collection class to store Data from excel sheet
/// 2. Read data from file excel and store data into Datatable

/// 3. Read data from Datatable and populate in collection

/// 4. Get required test data from Collection to use ( GetData())

///5. Call GetData() when we want to get test data.

//// 1. Define collection class to store Data from excel sheet
 public class Datacollection
 {
 public int rowNumber { get; set; }
 public string colName { get; set; }
 public string colValue { get; set; }
 }
// Declare collection to use

 static List<Datacollection> dataCollection = new List<Datacollection>();

// 2.Read file excel and store data into Datatable
 private static DataTable ExcelToDataTable(string fileName, string SheetNumber)
 {
 DataTable resultTable = new DataTable();

 // Open file and return as Stream
 FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);

 if (stream != null)
 {
 //Createopenxmlreader via ExcelReaderFactory
 IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
 if (excelReader != null)
 {
 //Set the First Row as Column Name
 excelReader.IsFirstRowAsColumnNames = true;

 //Return as dataset
 DataSet result = excelReader.AsDataSet();

 //Get all the tables from Dataset
 DataTableCollection table = result.Tables;

 // Get datatable contains data from excel sheet and Store it in data table
 resultTable = table[SheetNumber];
 }
 }

 return resultTable;
 }

// 3.Read data and populate in collection
 public static void PutDataInCollection(string fileName, string SheetName)
 {
 // clear collection fisrt
 dataCollection.Clear();

 // Read file excel and store data into Datatable
 DataTable table = ExcelToDataTable(fileName, SheetName);

 //Iterate through the rows and columns of the Table
 for (int row = 1; row <= table.Rows.Count; row++)
 {
 for (int col = 0; col < table.Columns.Count; col++)
 {
 Datacollection dtTableCollection = new Datacollection()
 {
 rowNumber = row,
 colName = table.Columns[col].ColumnName,
 colValue = table.Rows[row - 1][col].ToString()
 };

 //Add all the details for each row
 dataCollection.Add(dtTableCollection);

 }
 }

 }

 //4. Get data from Collection
 public static string GetData(int rowNumber, string columnName)
 {
 // Row number in Excel file always starts by 2
 rowNumber--;
 string data = string.Empty;
 try
 {
 //Retriving Data using LINQ to reduce much of iterations 
 data = (from item in dataCollection
 where item.colName == columnName && item.rowNumber == rowNumber
 select item.colValue).SingleOrDefault();
 // data = dataCollection.Where(x => x.colName == columnName && x.rowNumber == rowNumber).SingleOrDefault().colValue;
 return data;
 }
 catch (Exception e)
 {
 Console.WriteLine("Exception occurred in Read Data Excel:" + e.Message.ToString());
 return data;
 }

 }

//// 5. Call Getdata ()
  public void LoginTest()

 {
 //Launch the url
 Base.driver.Navigate().GoToUrl(Base.URL);
 Base.driver.Navigate().

 // Maximize browser
 Base.driver.Manage().Window.Maximize();

 // Input Username (using Extension Method) 
 //CommonMethods.EnterText(Username, ExcelLib.GetData(2, "UserName"));
 Username.EnterText(ExcelLib.GetData(2, "UserName")); 
 

 // Input Password 
 Password.EnterText(ExcelLib.GetData(2, "PassWord"));
}

Advanced Selenium automation with C# (Taking screenshot) Generate automation report using ExtentReports in C#