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.

look into using a ForEach or a For Loop.. do a google search as well.. — MethodMan, Aug 01 '12 at 17:04
Look at this link and learn how to use Interop based on this example it will be a great learning experience.. http://stackoverflow.com/questions/11593628/reading-data-from-excel-spreadsheet-cannot-perform-runtime-binding-on-a-null-r — MethodMan, Aug 01 '12 at 17:07
Thanks DJ KRAZE, i looked at the thread but it's too complicated to understand. All i want is to loop over the excel rows which i am not able to do. — seleniumlover, Aug 01 '12 at 17:09
I just posted 2 examples below.. it's not as complicated as you may think.. this is the easiest example I could come up with based on your fears of trying.. try the example and change it to fit your code sample above let me know if this works for you.. thanks — MethodMan, Aug 01 '12 at 17:16
I am going to post an even easier example that you should be able to follow.. please try not to get frustrated... make sure to add the necessary references at the top of your form as well — MethodMan, Aug 01 '12 at 18:05

MethodMan · Answer 1 · 2012-08-01T18:03:56.460

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

Thanks DJ KRAZE for your answer. I need to declare variable for the row 1 inside while loop in example 1 so that the variable changes as per the iteration, how can i do that? — seleniumlover, Aug 01 '12 at 17:18
be more specific.. are you referring to my example or something else.. edit your initial post with the loop that you are using so that I can help you from there.. thanks — MethodMan, Aug 01 '12 at 17:22
I'm referring to your 1st example, how can you associate a variable to the row such that it reads all the row in the while loop. — seleniumlover, Aug 01 '12 at 17:30
Also i'm getting error for IExcelDataReader. What reference do i need to add so that it is considered as an object? — seleniumlover, Aug 01 '12 at 17:54
did you add a using statement at the top of the project something like this..this is called Aliasing using Excel = Microsoft.Office.Interop.Excel; — MethodMan, Aug 01 '12 at 18:02
Yes i added on the top saying using Excel=Microsoft.Office.Interop.Excel and also added the reference to the excel.dll downloading it from http://exceldatareader.codeplex.com/ — seleniumlover, Aug 01 '12 at 18:09
the newer example at the top is better and will show how to use variable when looping you need to know the range ? or not ..the example should provide that as well plus I added a LINK at the bottom there are so many way to do what you are wanting to do..personally I would use the DataSet example — MethodMan, Aug 01 '12 at 18:14

score 0 · Answer 2 · answered Sep 16 '16 at 19:48

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);

        }

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

2 Answers2