Btukfyl

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.

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

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



        }