Reading and Writing data from/to excel sheets is very easy with the python modules xlrd, xlwt.
There is also an xlutils module available to help in copying data, filtering data, etc. But, I don’t use that module simply because such a need has not arisen yet. So, I’m going to focus on the xlrd and xlwt modules only.
Download the modules, extract contents and run setup in a way similar to that described in the installation steps for selenium
Now, that you are ready to use the modules, let me list out the methods I find most useful while using these modules.
Writing data to Excel using xlwt
First we import the module into our script
Then, we create a workbook object to write data into. You can pass an optional parameter to specify encoding here, but it is not necessary (I normally don’t specify it).
wb = xlwt.Workbook(encoding="utf-8")
Next, we will add some sheets to the workbook. Again, there’s an optional parameter cell_overwrite_ok. I generally use it because I need to overwrite already existing excel sheets with new test results.
First parameter is the name you want to give the sheet being created.
ws1 = wb.add_sheet('Sheet 1',cell_overwrite_ok=True) ws2 = wb.add_sheet('Sheet 2',cell_overwrite_ok=True) ws3 = wb.add_sheet('Sheet 3',cell_overwrite_ok=True)
We can add the data to the workbook in various ways, as detailed below:
ws1.row(0).write(0, 'Data written in first cell of first sheet') ws1.write(0, 0, 'Data overwritten in the first cell of first sheet') ws2.write(0, 0, 'Data written in first cell of second sheet') ws3.write(0, 0, 'Data written in first cell of third sheet') ws1.write(0, 1, 'Data written in first row,second column of first sheet') ws1.row(1).write(1, 'Data written in second row,second column of first sheet') var = "Data from variable written in third row,second column of first sheet" ws1.row(2).write(1,var)
Like you might have already deduced by now, we first specify the row no. (0, for 1st row, and onwards), then the column no. (0, for 1st column, and onwards), and then you enter whatever data we want written (as a string, or maybe as a variable – as shown in the last statement)
The final step is to save this workbook object as an actual excel file. We pass the path to the file as a parameter. If a file already exists at the given path, the sheet will be overwritten.
Reading data from Excel using xlrd
Now that we have saved the data in an Excel file successfully, it’s time to read the saved data from it.
We start by importing the xlrd module.
Then, we open the workbook to be read by passing the file path parameter to the open_workbook() method. In this case, we’ll be opening the file we just saved.
wb2 = xlrd.open_workbook('D:\\test_logs\\Spreadsheet_test.xls')
To access a sheet of the workbook object, we can use the sheet_by_name() or the sheet_by_index() method.These methods return a sheet object that can be used to access the cell contents.
There’s another method sheet_names() that returns the names of all the sheets in the file.
sheet1 = wb2.sheet_by_name('Sheet 1') sheet2 = wb2.sheet_by_index(1) sheet3 = wb2.sheet_by_name(wb2.sheet_names())
After the sheet objects have been created, we can read the values using the following methods
print "Number of rows in sheet 1:" + sheet1.nrows print "List of cell values in 1st row:" + sheet1.row_values(0) print "Number of columns in sheet 1:" + sheet1.ncols print "List of cell values in 2nd column:" + sheet1.col_values(1) print "Data in 1st cell of sheet 1:" + sheet1.cell(0,0).value print "Data in 3rd row,2nd col of sheet1:"+sheet1.cell(2,1).value print "Data in 1st cell of sheet 2:" + sheet2.cell(0,0).value
The row_values() and col_values() methods return a list of data upto the final cell that contains data. Any empty cell in between will be read as an empty element. The nrows and ncols values will also be calculated accordingly.
Another thing to note is that while reading integer values from an excel, xlrd methods will return them as floats. So, in case you have a value “100” in the first cell of the first sheet, you will need to convert it back to integer while reading it.