2 Query Based Excel Testing - essenius/FitNesseFitSharpExcel GitHub Wiki

Sometimes a script or scenario is not what you are looking for. Rather, you just want to check if a certain range contains an expected set of values. For example, we want to validate that the range C4:C15 in below sheet indeed contains the first 11 Fibonacci numbers.

It could be done via a script, but that would be cumbersome. We just want to specify the range of expected values. Let’s first do some refactoring of our test. Opening and closing the Excel sheet is something that can be shared between our tests, so we define a suite with a SuiteSetup and SuiteTearDown page that take care of opening and closing Excel as well as the workbook.

SuiteSetup:

!|script      |Excel                         |
|Load Workbook|ExcelFixtureTest.xlsm|readonly|

SuiteTeardown:

|script     |
|close excel|

Now we create a new page ExcelQueryTest:

!|script                                |
|Select Work sheet|sheet1               |
|$excel=          |get fixture          |

|query:Excel Query|$excel|C4:D8|useheaders|
|Input            |Fibonacci              |
|0                |0                      |
|1                |1                      |
|2                |1                      |
|3                |2                      |

So, we first open up the workbook via the SuiteSetup page and then bring up the right page. Then we put a reference to the fixture in the $excel symbol.

The Excel Query fixture needs this reference as its first parameter – so it needs an already open Excel workbook which is at the right page. The second parameter is the range that it needs to retrieve. The third parameter is either useheaders or something else. If it is useheaders, then the first row of the range will be expected to contain the header names. In our case, those are expected to be Input and Fibonacci. The rest of the range contains the values to be compared against.

If the third parameter is something else than useheaders (or missing) then the column name is Column followed by the column number, i.e. Column 1 for column A, Column 27 for column AA, etc. So then the query would be:

|query:Excel Query|$excel|C4:D8|
|Column 3         |Column 4    |
|Input            |Fibonacci   |
|0                |0           |
|1                |1           |
|2                |1           |
|3                |2           |