How to use Excel DataSource in the UserCode

[eric.franc]

Hello,

I am a bit struggling with the excel data sources.
If I have two sheets in my excel file, for that I have created two separate Excel Data Sources in ranorex.

How can I access them separatly in the UserCode, currently I can only access one.

TestCase.Current.DataContext.Source.Rows[0].Values[0].ToString();

I tried to bind a variable to the data source but I can't get any value from it.
What is the best way to work with multiple Data sources in the same test case ?

Regards,
/Eric

[krstcs]

Only one data source can be bound to any given Test Case. Each recording or user-code module inside the Test Case can then have it's module variables bound to any column in the data source.

Think of the Test Case as a for-each loop that loops over each line of data in the data source.

You can nest Test Cases and then the modules inside the inner test case can see both data sets. However, be careful with this because each row of data in the inner data source will be iterated over for every row in the outer data source. So, if you have 2 rows in the outer source and two rows in the inner source, the inner test case will be run 4 times total.

I would suggest that if this is not how you want the test to work that you re-think your data layout. If you need access to all that data in one test case then you should probably just put it all in one work sheet.

I would also suggest that you use CSV files instead of Excel worksheets because you don't need to have Excel installed on the system to work with CSV, but you do to work with XLS files. And you can still edit/save CSV from Excel when you are working with the data.

[eric.franc]

I was afraid of this answer , I really need to use more than one excel sheet during a single test case.
I guess I will write a quick connector using EPPLUS http://epplus.codeplex.com/ to do the job then I will not need office installed on the test machine.

I think it is a big limitation, but I can easily outcome it.
Thanks for your time .

[Vaughan.Douglas]

Sorry that I'm a little late to the party, but I frequently use Microsoft ACE to query Excel sheets especially when I'm using relational data.

Here's a quick example of using a dataadapter to fill a datatable with the query results from an Excel file.

Code: Select all

Dim objOleDbConn As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & System.IO.Path.Combine(Environment.CurrentDirectory, "BigFatTestDataCreation.xlsx") & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;""")
            Dim objOleDbCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand("Select [LeaderID], [Leader_Last_Frist], [Leader_Display_Name], [EmployeeWFID], [Employee_Last_First], [Employee_Display_Name], [Status] From [LoopQuerySource$] Where  [LeaderID] = '" & Leader_WFID & "' And [Status] <> 'Saved' And [Status] <>  'Failed' And [Status] <>  'Filter'", objOleDbConn)
            Dim objOleDataAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(objOleDbCommand)
            Dim myDataTable As New dsLoopOverEmployees.LoopOverEmployeesDataTable

            objOleDataAdapter.Fill(myDataTable)
            objOleDataAdapter = Nothing
            objOleDbCommand = Nothing
            objOleDbConn = Nothing

In this case my Excel file is BigFatTestDataCreation.xlsx and is packaged with the test suite. This is achieved simply by placing my datasource worksheet inside of the same workbook. This allows me to use a relative path.