How to access multiple Excel files from UserCode Module

Ranorex Spy, Recorder, and Studio.
Samuel
Posts: 14
Joined: Tue Nov 12, 2013 10:50 am
Location: Germany (BW)

How to access multiple Excel files from UserCode Module

Post by Samuel » Tue Dec 17, 2013 1:42 pm

I know a lot has already been written about accessing Excel files, but I´ve been trying and reading the whole day until now and still can´t seem to get it right.

What I´m trying to do is access data that I´ve specified inside Excel (xlsx) files from inside a user code module (I´m running Ranorex 4.1.1). All files have only one column, but vary in their number of rows. I want to read all the rows inside this first column.

I thought the easiest way would be to add all excel files using the "Manage Data Sources" menu, and access them later by their names defined there (e.g. NewConnector).

So after adding them, the first thing I tried was

Code: Select all

Ranorex.Core.Data.DataCache dataSource =  DataSources.Get("NewConnector");
which returned null. I guess the excel files have to be set as data source for the current test case and not just on a global scale for this to work (since only one Data Source can be set per TestCase, it´s pobably no option in my case).

Then I read about the IList<DataCache> DataConnectorCache and thought I could access the files directly from inside the DataCache:

Code: Select all

 var dataCache = TestSuite.Current.DataConnectorCaches;
This line of code worked exactly one time (I´ve no idea why), but usually thows a System.NullReferenceException. I guess if it would be possible to access the DataConnectorCache somehow, then it would also be possible to access a specific data connector inside it?

Anyways, since that didn´t work either, I´ve then tried to access the files directly as described in this example.

Code: Select all

Ranorex.Core.Data.ExcelDataConnector excelConnector = new Ranorex.Core.Data.ExcelDataConnector("excelConnector",@"..\..\TestData\ScriptServiceControl\DeviceTypesToAssociatedScripts\" + pureDeviceName + ".xlsx","Tabelle1","",0);

					Ranorex.Core.Data.ColumnCollection columnCollection;
					Ranorex.Core.Data.RowCollection rowCollection;

					excelConnector.LoadData(out columnCollection, out rowCollection);
Initialising the excelConnector doesn´t throw a error, but the RowCollection and CollumnCollection that is returned using the LoadData method never holds any data (always 0 rows). Maybe I´ve also misunderstood the definition for the ExcelDataConnector. I thought maybe I have to specify the worksheetRange and tried various things like "Tabelle1-Tabelle2", "Tabelle2", "A-A", "1". Always LoadData throws a COM Exception "Failed to load Excel data." (I´m running a Ger. Excel version which is why the worksheets are called Tabelle by default).

Maybe someone else has already experience in doing this and can give me a few hints?
Last edited by Samuel on Thu Dec 19, 2013 8:14 am, edited 6 times in total.

Swisside
Posts: 92
Joined: Thu Oct 10, 2013 10:40 am

Re: How to access multiple Excel files from UserCode Module

Post by Swisside » Tue Dec 17, 2013 4:28 pm

Hello


Can you try the following ?
public void retrieveExcelData()
		{
			Ranorex.Core.Data.DataCache MyDataCache = DataSources.Get(dataConnectorName);
			MyDataCache.Load();
			//Here I only retrieve one value, you can also retrieve the list
			//Since your row has one column .Values[0] is used to return it
			string mydata = MyDataCache.Rows[RowIndex].Values[0];
		}

Regards

NB: I supposed you added them as Data Sources first.
A simple thank you always does wonders !

Samuel
Posts: 14
Joined: Tue Nov 12, 2013 10:50 am
Location: Germany (BW)

Re: How to access multiple Excel files from UserCode Module

Post by Samuel » Tue Dec 17, 2013 5:48 pm

Thank you for your answer. When I try to do this, then

Code: Select all

MyDataCache.Load();  
throws an exception because the object reference is not set on a object instance (MyDataCache is null after using the get method).

It seems the data source that could be accessed like this has to be set for the TestCase in which this method is used. The problem is, that inside the test case overview page (rxtst), when I specify the data source for a test case, only one data source can be specified per test case. It would probably work for this single source, but I want to be able to access different excel files depening on certain conditions. Therefore I´ve defined the data sources in the "Manage Data Sources" menu, but haven´t bound one source specifically to the test case.

krstcs
Ranorex Guru
Posts: 2683
Joined: Tue Feb 07, 2012 4:14 pm
Location: Austin, Texas, USA

Re: How to access multiple Excel files from UserCode Module

Post by krstcs » Tue Dec 17, 2013 6:09 pm

I would recommend that you look into using SQL Server Express instead of Excel.

From the sounds of your issue, you really need a system that handles complex data structures correctly, instead of the flat data sets that you have in Excel.

Excel is a great spreadsheet, but it is a very poor substitute for a real relational database management system.
Shortcuts usually aren't...

Swisside
Posts: 92
Joined: Thu Oct 10, 2013 10:40 am

Re: How to access multiple Excel files from UserCode Module

Post by Swisside » Wed Dec 18, 2013 8:45 am

Hi
It seems the data source that could be accessed like this has to be set for the TestCase in which this method is used.
I tried the code I provided in a User Code action (in a Recording module) that's why it is working for me and not for you. The same code generates the same error than you when used in a Code Module.

I think it's really strange it doesn't work in the second case.
A simple thank you always does wonders !

Samuel
Posts: 14
Joined: Tue Nov 12, 2013 10:50 am
Location: Germany (BW)

Re: How to access multiple Excel files from UserCode Module

Post by Samuel » Thu Dec 19, 2013 2:13 pm

I´ve now also tried to use the code inside the user code of a recorded module, but unfortunately got the same problem.

However I finally managed to get the ExcelDataConnector working. So if anyone else is searching for another example of how to access any kind of excel file from within user code here´s my solution:

Note: There´s no need to add the files to the project as data-source using the "Manage Data Sources" dialog first. In case someone gets confused by the filepath.. I´ve placed the files inside a folder called TestData which I added to the project.
P.S. If you want to read the following I suggest copying and pasteting it to a cs file in Ranorex or VS - the linebreaks will work ;)

Code: Select all

    // The connector won´t throw an exception if it´s created using a filepath that is incorrect but loading the data will fail, therefore I check, if the excel file exists first
    if(System.IO.File.Exists(@"..\..\TestData\" + fileName + ".xlsx")){

        // Now we create the connector itself
        // Note that "Tabelle1" is the name of the worksheet. On a english os, this probably has to be renamed to Worksheet1
        // A:A defines the column range. It is also possible to get those automatically by setting this to "Auto-Range"
        Ranorex.Core.Data.ExcelDataConnector excelConnector = new Ranorex.Core.Data.ExcelDataConnector("excelConnector",@"..\..\TestData\" + fileName + ".xlsx","Tabelle1","A:A",System.Windows.Forms.CheckState.Unchecked);
                   
        // Load all Data from the Excel file
        Ranorex.Core.Data.ColumnCollection columnCollection;
        Ranorex.Core.Data.RowCollection rowCollection;
        excelConnector.LoadData(out columnCollection, out rowCollection);
                      
        // We can now use the data - e.g. add all Texts of the first Excel column to a List of Strings
        List<String> allStringsFromFirstRow = new List<String>();
        foreach(var item in rowCollection)
        {
            allStringsFromFirstRow.Add(item.Values[0]);
        }
    }