How to use Excel DataSource in the UserCode

Ranorex Spy, Recorder, and Studio.
eric.franc
Posts: 20
Joined: Tue May 05, 2015 11:25 am

How to use Excel DataSource in the UserCode

Post by eric.franc » Wed Aug 05, 2015 12:09 pm

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
Ranorex Guru
Posts: 2683
Joined: Tue Feb 07, 2012 4:14 pm
Location: Austin, Texas, USA

Re: How to use Excel DataSource in the UserCode

Post by krstcs » Wed Aug 05, 2015 2:27 pm

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.
Shortcuts usually aren't...

eric.franc
Posts: 20
Joined: Tue May 05, 2015 11:25 am

Re: How to use Excel DataSource in the UserCode

Post by eric.franc » Thu Aug 06, 2015 11:34 am

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
Posts: 250
Joined: Tue Mar 24, 2015 5:05 pm
Location: Des Moines, Iowa, USA

Re: How to use Excel DataSource in the UserCode

Post by Vaughan.Douglas » Mon Aug 31, 2015 4:02 pm

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.
Doug Vaughan