Having looked into the many uses people have found for Python, I can see why its such a popular language.

One of these uses is to control an Excel XLS, which could then be used for data-driven testing, with the aim of reading data from each row of the XLS and then using this as input data and expected results.

A quick search on Google and I found several examples of using Python to control an Excel XLS and also CSV files. One of the most useful pages was from scienceoss.com.

The basic steps are:

  • Install the xlrd package
  • In your Python script, import xlrd
  • You can then make use of xlrd for Excel XLS interactions
  • A quick and easy example is shown below. This opens a workbook named ‘Test1.xls’ then gets the first sheet, then prints out each row of the contents, using a for loop through the rows.
# Import the xlrd package
import xlrd
# Open an .xls file
book = xlrd.open_workbook("Test1.xls")
# Get the first sheet
sheet = book.sheet_by_index(0)
for rownum in range(sheet.nrows):
    print sheet.row_values(rownum)
  • Another example is shown below, with Python comments.
  • This example  does things in smaller steps than the first example and is useful for seeing how you can access columns and individual cells.
  • You can then place the values from the cells into variables in your Python script.
# Import the xlrd package
import xlrd
# Open an .xls file
book = xlrd.open_workbook("Test1.xls")
# Get the first sheet
sheet = book.sheet_by_index(0)
# get columns 1,2,3,4
first_column = sheet.col_values(0)
print first_column
second_column = sheet.col_values(1)
print second_column
third_column = sheet.col_values(2)
print third_column
# get some cells
cell_A1 = sheet.cell(0,0).value
print cell_A1
cell_A5 = sheet.cell(4,0).value
print cell_A5