Reading/Writing Excel sheets in Python


excel_sheet

 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.

Installing the modules
xlrd – https://pypi.python.org/pypi/xlrd
xlwt – https://pypi.python.org/pypi/xlwt

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

import xlwt

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.

wb.save('D:\\test_logs\\Spreadsheet_test.xls')

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.

import xlrd

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()[2])

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.

print int(sheet1.cell(0,0).value)
Advertisements

2 thoughts on “Reading/Writing Excel sheets in Python

    • wb2.sheet_names() returns an array of sheet names in the workbook wb2.
      To get the name of sheet 3 we read the 3rd element of the array by adding [2] ahead. Remember, array is read as 0,1,2,…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s