Page 1 of 1

Data driven test - using many excel sheets

Posted: Mon Oct 12, 2015 11:29 am
by Sajczi
Hello,
I would like to use data from a few excel sheets in my test. All the sheets are in the same excel workbook. How can I do this?

Re: Data driven test - using many excel sheets

Posted: Mon Oct 12, 2015 11:56 am
by odklizec
Hi,

I'm not quite sure what exactly you want to achieve? Could you be more specific about you problem?

Generally speaking, TestCase with attached excel data connector can use data from just one worksheet. You can specify the worksheet (and even cell range) to be used via "Manage Data Sources" dialog...
http://www.ranorex.com/support/user-gui ... -file.html

There may be a way to programmatically load data from multiple worksheets, but I think it's not recommended approach. It would just create a mess in test logic and makes your test harder to understand by someone else. I would suggest to reorganize you test data in the way that one TestCase would use the data from just one worksheet.

Re: Data driven test - using many excel sheets

Posted: Tue Oct 13, 2015 3:27 am
by jasoncleo
We were in the same situation, where having a test case only able to load a single dataset (or single Excel worksheet) was not always optimal. So we ended up implementing a supporting class that allowed us to load datasets (whether from CSV, Excel, etc) within the code module, so the code could iterate through a data driven test, drawing on multiple sources of data.

For Excel portability, we used the 'ExcelDataReader' library that you can find on GitHub. This library means that you don't have to have Office installed in your test environments.

Obviously loading from multiple datasources has risks if the code isn't written defensively. You need to ensure that your datasets are loaded at the beginning of your code module to ensure that they can be clearly found and people know where all the data is coming from.

Also, if you are using a single loop to iterate through your data (but the data is drawn from different sources), then do your logical checks at the beginning to make sure that all your datasets have the same number of rows so that you don't have some of them going out of scope.

The benefits gained from supporting multiple datasources are huge though, at least I think so:
- We are able to more easily standardise "shapes" of datasources for different purposes
- We can now do code controlled scopes, so the code can check the config at runtime, so that it only does limited rows for a Sanity run, but for a regression run it does all rows.
- We're able to avoid the tedious databinding, and we've applied typed datasets, so the values are even easier to pass around.