It can be useful to read Excel documents within test cases for two reasons : get some input for a test and check the output of a test when an Excel document is produced by the SUT. I propose to discuss quickly those 2 cases and see how easy it is to achieve it with Robot Framework.

The main reason I see for Excel document to be a good candidate for test cases input data is that Excel sheets are able to do computations (quite complicated ones in fact). In my previous job, the expected of our test cases were often some financial computation (derivate products, rates etc.) and the tool of choice for Product Management and QA team to compute the expected of different use cases was Excel. When we chose to automate those examples, we basically copy/pasted the results from Excel to our testing tool. Until one day we thought it would be even more convenient to read the Excel file directly from our testing tool. To be honest, the idea was stolen from a demo of a tester building a data-driven tests framework based on Quality Center and Excel (yes, I know…)

The other side of the coin is when the SUT is producing Excel documents that we want to check. In that case, the expected could be stored in our tests, or it could be another XLS file (and we could compare results found in both).

So, here is some Robot Framework code that you could use as a start:

*** Settings ***
library  openpyxl.reader.excel
 
*** variables ***
${expected_value}  expected result
 
*** test cases ***
Excel sheet
    ${wb} = load_workbook sample.xlsx
    ${ws} = set variable ${wb.get_active_sheet()}
    ${cell} = set variable ${ws.cell('A1')}
    ${actual_value} =  set variable ${cell.value}
    should be equal  ${expected_value}  ${actual_value}

Here I open the document sample.xlsx and check that the value in the first cell match a value that I stored in a variable. I am using the openpyxl library (warning : can read only XLSX documents). This is, IMHO, quite compact and simple code. From there, you can write your own keyword to parse the document according to your needs.

Some final thoughts :

1. Robot Framework text format for test suites/cases is great as it fits perfectly in SCM. And this is where we want to store our tests artifacts (quote from a great article from Elisabeth Hendrickson : “Technical artifacts including test automation and manual regression test scripts belong in the Source Control System versioned with the associated code”). Unfortunately, Excel document (even in the XLSX format, which is XML… but zipped!) are binary files and if we store them in SCM, we won’t be able to compare different versions. That’s a major limit to their use.
2. as I swim in the open source world all day long, I could have come up with a post about how to read some files that have a more open format than XLS…. The thing is that sometimes we don’t have the choice of the tools (example, customers are asking for export as XLSX in my product, I will have to test it one way or another) and that is the tool with which I had the experience I wanted to share.
3. It is interesting to see that this Excel topic is discussed for many testing tools. Some are reading Excel from Junit, some other from Cucumber and there was a proposition to include it by default in Robot Framework.

Oh and thanks to Jean-Charles and Matthieu for the testing dojo in which we came up with this little snippet of code !