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