Reading Excel Spread Sheet Using Python

In this tutorial we will see the simple task of Reading an Excel Spread sheet using Python. It is very easy to do these by following the steps below.

Installing required library.

xlrd is used for reading excel sheet. It can be easily installed in both Linux and Windows machines by following the steps below.

In linux Machines

Open terminal and install using the below command

sudo pip install xlrd

In windows Machines

Download the tar file from https://pypi.python.org/pypi/xlrd/0.9.2. Extract it and then use
python setup.py install

to install the module. If you are not familiar with this you can use the windows installer available at https://pypi.python.org/pypi/xlrd/0.7.9 which  is for an old version of xlrd.


Opening an Excel Spread Sheet
import xlrd
excel_sheet = xlrd.open_workbook('workbook.xls')

Here we have used the method open_workbook to open the excel spread sheet workbook.xls

Opening work sheet of an Excel Spread Sheet
sheet1= excel_sheet.sheet_by_name('Sheet1')

Here we have used the method sheet_by_name to open a specific sheet with the name Sheet1 . The names of all sheets with in a spread sheet can be done using,

print excel_sheet.sheet_names()

Reading row by row in a work sheet

We can find the number of rows in the sheet using,
sheet1= excel_sheet.sheet_by_name('Sheet1')
max_rows = sheet1.nrows

Here we have used sheet1.nrows to get the number of rows in the sheet. To read each row we can use the corresponding row index. For example,

row = sheet1.row(0)
print row

will give us a list of xlrd.sheet.Cell objects and the result is similar to the output below.

[empty:”,text:u’OWP :: 28 Oct – 1 Nov 2013′, Number:5441, Date:39682, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, , empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”, empty:”]

first_cell = row(0)
print type(first_cell)

From this we can understand that first_cell is a xlrd.sheet.Cell object with cell type as  text and value u’OWP :: 28 Oct – 1 Nov 2013′.

We can get the cell type using

print first_cell.ctype

Or

print sheet1.cell_type(row_index,col_index)

The different cell types for a Cell object are,

0 denoting the cell value is Empty
1 denoting the cell value is of type Text
2 denoting the cell value is of type Number
3 denoting the cell value is of type Date
4 denoting the cell value is of type Boolean
5 denoting the cell value is of type Error
and 6 denoting the cell value is Blank

We can read each row in the sheet by using the sample code below.

curr_row_index = 0

while curr_row_index < max_rows:

row = sheet1.row(curr_row_index)

curr_row_index+=1

Reading cell by cell in a worksheet

Like reading row we can read cell by cell of a sheet. For that we should know the column index as well as the row index. As we saw above for getting cell value type at index (0,0) we should use,

type = sheet1.cell_type(0,0)

print cell_type

will print any of the values from 0-6 denoting the type of value stored in the cell. Similarly to get the actual value being stored in the cell we can use,

value = sheet1.cell_value(0,0)

print value

To get all the cell value in a sheet we should know the maximum number of rows in the sheet as well as the maximum number of columns in the sheet. The maximum number of columns in a sheet can be find using

max_cols = sheet1.ncols

So  we can read each cell value using the below code,

curr_row_index = 0

while curr_row_index < max_rows:
curr_col_index = 0
while curr_col_index < max_cols:
value = sheet1.row(curr_row_index,curr_col_index)
curr_row_index+=1

So next time use Python when you want to deal with Excel Spread sheet