How to access multiple Excel files from UserCode Module
Posted: 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
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:
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.
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?
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");
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;
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);
Maybe someone else has already experience in doing this and can give me a few hints?