Read Excel files from Python

Use the excellent xlrd package, which works on any platform. That means you can read Excel files from Python in Linux! Example usage:

Open the workbook

import xlrd
wb = xlrd.open_workbook('myworkbook.xls')

Check the sheet names

wb.sheet_names()

Get the first sheet either by index or by name

sh = wb.sheet_by_index(0)
sh = wb.sheet_by_name(u'Sheet1')

Iterate through rows, returning each as a list that you can index:

for rownum in range(sh.nrows):
    print sh.row_values(rownum)

If you just want the first column:

first_column = sh.col_values(0)

Index individual cells:

cell_A1 = sh.cell(0,0).value
cell_C4 = sh.cell(rowx=3,colx=2).value

(Note Python indices start at zero but Excel starts at one)

Turns out the put_cell() method isn’t supported, so ignore the following section (Thanks for the heads up, John!)

Put something in the cell:

row = 0
col = 0
ctype = 1  # see below
value = 'asdf'
xf = 0  # extended formatting (use 0 to use default)
sh.put_cell(row, col, ctype, value, xf)
sh.cell(0,0)  # text:u'asdf'
sh.cell(0,0).value  # 'asdf'

Possible ctypes: 0 = empty, 1 = string, 2 = number, 3 = date, 4 = boolean, 5 = error

Tags: , ,

10 Responses to “Read Excel files from Python”

  1. Reading Excel Files From Python | foojam.com Says:

    [...] has a nice little tutorial today showing how to read in an excel file in Python using the xlrd package. This approach allows you to manipulate excel files on any platform without [...]

  2. Corey Goldberg Says:

    Thanks for this post. I normally end up saving Excel files as CSV and parsing raw data like that. This is a cool way to work with Excel files directly.

    Great blog… I am enjoying going through your Python posts.

    -Corey

  3. Read Excel files with Python (no win32com) : [ mkhairul.com ] Says:

    [...] Read excel files from python using a package called xlrd that can read excel files from anywhere, even LINUX! No more importing win32com as displayed in recipes. Maybe with this I can solve the problem involving the excel sheet and vba thing that I’m having here at work. Not a priority, just someone needs help with it and seems like a challenge. I’m not very familiar with converting unicode back and forth. [...]

  4. Josef Assad Says:

    Does it also write excel format files?

  5. ryan Says:

    @Josef Assad:
    I don’t think it can write Excel format files, you have to have an existing Excel file.

    You could try PyExcelerator though (http://sourceforge.net/projects/pyexcelerator), which does write to Excel format. I’ll give it a shot myself and write a post on using it.

  6. Techy News » scienceoss.com " Blog Archive " Read Excel files from Python Says:

    [...] Read the rest of this great post here [...]

  7. Attila Bleier Says:

    Please note that the example above has a typo. The 2nd line should be: wb=xlrd.open_workbook(’myworkbook.xls’), because of the namespace.

    excellent post, thanks.

  8. ryan Says:

    @Attila Bleier:
    Nice catch, thanks. It’s fixed now.

  9. John Machin Says:

    Hi, Ryan, I’m the author/maintainer of xlrd. Thanks for the advertisement!

    One point though, relating to this line in your posting:

    sh.put_cell(row, col, ctype, value, xf)

    Sheet.put_cell is only for internal use building the sheet contents, it is intentionally not documented and is definitely *not* supported.

    Bonus extra point:
    cell_C4 = sh.cell(2,3).value
    should be
    cell_C4 = sh.cell(3, 2).value
    or (better)
    cell_C4 = sh.cell(rowx=3, colx=2).value

    Cheers,
    John

  10. ryan Says:

    John, thanks for xlrd, it’s fantastic.

    I made the edits you suggested.

    Just checking: is it correct that there is no [supported] way to add content to an existing Excel sheet using xlrd?

    -ryan

Leave a Reply