How to read all the rows of the excel sheet and run in Selenium test script using C#?

Question

I need help reading all the rows from excel sheet as a test data in my selenium test case.

I can read only one row of an excel sheet with the following code.

//set up test for selenium

 public void SetupTest()
    {
        selenium = new DefaultSelenium("localhost", 4444, "*googlechrome", "http://www.google.com/");
        selenium.Start();
        verificationErrors = new StringBuilder();
    }

//Connects me to my excel sheet which is SampleTestData.xls

public void ConnectExcel()
{
 excel.Application excelApp = new excel.Application();
 excelApp.Visible = true;
 string ExcelDataPath = @"C:\SampleTestData.xls";
 excel.Workbook excelWorkBook = excelApp.Workbooks.Open(ExcelDataPath, 0, false, 5, "", "", false, excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
 excel.Sheets excelSheets = excelWorkBook.Worksheets;
 excel.Worksheet DataworkSheet = (excel.Worksheet)excelSheets.get_Item("Sheet1");
 System.String Search1 = ((excel.Range)DataworkSheet.get_Range("A1", Type.Missing)).Value2 as string;
 }

//selenium code to run the test

public void StartTest()
{
selenium.open("");
selenium.WaitForPageToLoad("100000");
selenium.Type("gbqfif", search1);
selenium.Click("btnG");
selenium.WaitForPageToLoad("100000");

}

This code lets me select only one row that contains value of search1. However, I need to iterate it such a way that it will keep running StartTest() method until all the rows(50) in the excel sheet are entered.

Any help would be appreciated.

Answer 1

This answer may or may not help you but I am going to take a chance and post 2 samples of code that you can use..

Example #1

using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel; 

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp ;
            Excel.Workbook xlWorkBook ;
            Excel.Worksheet xlWorkSheet ;
            Excel.Range range ;

            string str;
            int rCnt = 0;
            int cCnt = 0;

            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Open("csharp.net-informations.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            range = xlWorkSheet.UsedRange;

            for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
            {
                for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                {
                    str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2 ;
                    MessageBox.Show(str);
                }
            }

            xlWorkBook.Close(true, null, null);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        } 

    }
}

Example #2

FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();

//5. Data Reader methods
while (excelReader.Read())
{
    //excelReader.GetInt32(0);
}

//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();

Example #3

using (FileStream fileStream = File.Open(inputFilenames[0], FileMode.Open, FileAccess.Read))
{
    IExcelDataReader excelReader;
    if (Path.GetExtension(inputFilenames[0]) == ".xls")
        excelReader = Factory.CreateReader(fileStream, ExcelFileType.Binary);
    else
        excelReader = Factory.CreateReader(fileStream, ExcelFileType.OpenXml);

    excelReader.NextResult();
    while (excelReader.Name != this.Worksheet)
        excelReader.NextResult();                

    while (excelReader.Read())
    {
        if (FirstRowHasColumnNames)
        {
            FirstRowHasColumnNames = false;
        }
        else
        {
            //do stuff
            var test = GetColumnData(excelReader, 1);
        }
    }

    this.Save(outputFilename);
}

you can look at this link as well Reading from Excel File

Answer 2

Try this:-

        //Reading from a OpenXml Excel file (2007 format; *.xlsx)
        IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

        // DataSet - Create column names from first row
        excelReader.IsFirstRowAsColumnNames = true;
        DataSet result = excelReader.AsDataSet();

        while (excelReader.Read())
        {    // Reading from row and get columns by index           
            var test = excelReader.GetString(1);

        }