Data driven test - using many excel sheets

Best practices, code snippets for common functionality, examples, and guidelines.
Posts: 9
Joined: Fri Jul 31, 2015 8:24 am

Data driven test - using many excel sheets

Post by Sajczi » Mon Oct 12, 2015 11:29 am

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?

User avatar
Ranorex Guru
Ranorex Guru
Posts: 7469
Joined: Mon Aug 13, 2012 9:54 am
Location: Zilina, Slovakia

Re: Data driven test - using many excel sheets

Post by odklizec » Mon Oct 12, 2015 11:56 am


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... ... -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.
Pavel Kudrys
Ranorex explorer at Descartes Systems

Please add these details to your questions:
  • Ranorex Snapshot. Learn how to create one >here<
  • Ranorex xPath of problematic element(s)
  • Ranorex version
  • OS version
  • HW configuration

User avatar
Posts: 37
Joined: Mon Jun 08, 2015 7:37 am

Re: Data driven test - using many excel sheets

Post by jasoncleo » Tue Oct 13, 2015 3:27 am

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.