Read data from Excel sheet for WebDriver

Read data from excel sheet to use it in webDriver

During testing we often need to execute the same Test cases with different Test Data.
The Test Data can be obtained from various sources such as text file,excel file and so on.
In this tutorial we will learn to extract(read) data from an excel sheet and use it in our webdriver.

Example:Let us login to facebook account by reading Email and Password present in the excel sheet.


Step 1:Go to "c:\temp" folder and create a new Microsoft excel worksheet with name as "demo.xlsx"

Step 2 :Open "demo.xlsx" spreadsheet and enter Email and password as shown below

Step 3:Open Visual Studio and click on Tools>NuGet Package Manger>Manage Nuget Packages for solution...

Step 4:In the search bar search "Excel" and download and install Microsoft.Office.Interop.Excel into the current solution.

Step 5: In the namespace section add

               using excel = Microsoft.Office.Interop.Excel;

Step 6: Add the below code snippet
             
                //Creates a new instance of Excel
Excel.Application xlApp = new Excel.Application();
         //Opens demo.xlsx
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Temp\demo.xlsx");
         //Selects the first Sheet
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
        //Finds the range of cells used in the sheet
Excel.Range xlRange = xlWorksheet.UsedRange;



Step 7: To get the data in a specified cell  use xlRange.Cells[i, j].value2;
            where i and j being row and column index.

Step 8: Store the value obtained  from cell as a string             

                            String email, pass;
                 //get userid in first cell
                email = xlRange.Cells[1, 1].value2;
                 //get password in present cell
                pass= xlRange.Cells[1, 2].value2;

Step 9: Use the obtained data in Selenium WebDriver.



Complete program:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Drawing; using OpenQA.Selenium; // This is Selenium namespace using OpenQA.Selenium.Chrome;//Use this namespace when you are automating in google chrome using System.Threading; 
using OpenQA.Selenium.Support.UI; 
using Excel=Microsoft.Office.Interop.Excel;

 namespace First_Program
  { 
    class Program
    { 
      static void Main(string[] args)
       { // Create a new excel application instance 
         Excel.Application xlApp = new Excel.Application();
        //Open demo sheet 
         
       Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\temp\demo.xlsx");   
        //select the first sheet in demo.xlsx 
        Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];

        //Find the range of cells used 
         Excel.Range xlRange = xlWorksheet.UsedRange; 

          string email, pass; 
         //get userid in first cell email = xlRange.Cells[1, 1].value2; 
          //get password in present cell pass= xlRange.Cells[1, 2].value2; 

           IWebDriver driver = new ChromeDriver(); 
           driver.Navigate().GoToUrl("https://www.facebook.com");
                    driver.Manage().Timeouts().ImplicitlyWait(TimeSpan.FromMilliseconds(10000)); 

                    driver.FindElement(By.Id("email")).SendKeys(email);            
                   driver.FindElement(By.Id("pass")).SendKeys(pass);            
                   driver.FindElement(By.Name("u_0_l")).Click();
                   Thread.Sleep(1000);
              }
    }

 }