Python Behave : How to read examples from external excel file for Scenarios Outline

Question

Instead of passing data from Examples: I want to pass the data from excel. as in that step I just want to compare expected and actual results for batch automation. and want to capture the total pass or failure of the test cases.

Scenario Outline: Browser Test

    When I visit the URL <base>/<page>/<ordNumber>/<custName>
    Then the browser contains test <custNumber>

    Examples: 
     | base                         | page   | ordNumber | custName |
     | http://www.stackoverflow.com | orders | 123       | John     |
     | http://www.stackoverflow.com | orders | 456       | Mike     |
     | http://www.stackoverflow.com | orders | 789       | Tom      |


examples.xls

 | base                         | page   | ordNumber | custName |
 | http://www.stackoverflow.com | orders | 123       | John     |
 | http://www.stackoverflow.com | orders | 456       | Mike     |
 | http://www.stackoverflow.com | orders | 789       | Tom      |

Answer 1

I would recommend putting all the data for a scenario inside of Gherkin documents, but you might have a valid use cases for pulling data from excel. However, in my experience, these type of requirements are rare. The reason why it is not recommended is, your BDD feature files are your requirements and should contain the right level of information to document the expected behavior of the system. If your data comes from an excel, then it just makes the requirement reading bit more difficult and makes it difficult to maintain.

Saying that if there is a strong reason for you to have these data stored in excel, you could easily achieve this using NoCodeBDD. All you have to do is map the column names and upload the excel and the tool take care of the rest. Please check this .gif to see how it is done. https://nocodebdd.live/examples-using-excel

Disclaimer: I am the founder of NoCodeBDD.

If you are using Junit5 here is an example on how it is done https://newbedev.com/data-driven-testing-in-cucumber-using-excel-files-code-example

Answer 2

I've had the same problem, and the solution isn't as straight forward as one might hope. You can't simply point Examples to your Excel file, instead, build your Examples table using the data from the Excel. You do this in environment.py under the before_feature hook.

Here's a proof of concept based on the scenario you posted:

1. The feature file should contain just the header in the Examples table. Note also that I have tagged the feature, will use the tag further down:

    @test-data-from-excel
    Feature: Examples table with test data from Excel
   
        Scenario Outline: Browser Test
   
            When I visit the URL <base>/<page>/<ordNumber>/<custName>
            Then the browser contains test <custNumber>
   
        Examples: 
         | base                         | page   | ordNumber | custName |
   

2. In environment.py:

   import pandas as pd
   
   def before_feature(context, feature):
   ...
   if 'test-data-from-excel' in feature.tags:   # >>> you can have this check on feature.name instead of tag
       path_to_file = '*<path/to/file/here.xlsx>*'
       df=pd.read_excel(path_to_file)
       example = next(sc.examples[0] for sc in feature.scenarios if sc.name == 'Browser Test') # >>> find the first examples object for scenario with given name
       test_table = example.table
       for row in df.itertuples(index=False):
           test_table.add_row(row)
   

I am using pandas to read the Excel file - I find it most convenient, also because I need it elsewhere in the framework. There are other libraries allowing the same operation, use whatever you prefer. If you don't have pandas installed, then:

pip install pandas