Clash Royale CLAN TAG#URR8PPP

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

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 1 '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.. stackoverflow.com/questions/11593628/…
– MethodMan
Aug 1 '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 1 '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 1 '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 1 '12 at 18:05

2 Answers
2

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 1 '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 1 '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 1 '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 1 '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 1 '12 at 18:02

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




By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.