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
April 10th, 2008 at 11:35 am
[...] 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 [...]
April 10th, 2008 at 1:30 pm
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
April 10th, 2008 at 1:33 pm
[...] 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. [...]
April 10th, 2008 at 2:56 pm
Does it also write excel format files?
April 10th, 2008 at 5:03 pm
@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.
April 12th, 2008 at 9:47 pm
[...] Read the rest of this great post here [...]
April 25th, 2008 at 11:56 am
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.
April 25th, 2008 at 1:19 pm
@Attila Bleier:
Nice catch, thanks. It’s fixed now.
July 20th, 2008 at 2:08 am
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
July 20th, 2008 at 9:44 am
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
August 6th, 2008 at 11:44 am
Is there a way to read cells in their display format as opposed to their native formats? For example, get the cell content as “$66,777.31″ if the value 66777.31 is formatted as currency.
February 25th, 2009 at 6:06 am
@Ryan
PyExcelerator is no longer supported/maintained. Try xlwt for COM-free creation of files.
March 25th, 2009 at 2:49 pm
Hi,
Thanks a lot. Could you please let me know on how to update a cell in a xls file using xlrd.
Regards,
Shakila.
April 3rd, 2009 at 12:34 am
@ Ryan, Shakila: xlrd ReaDs files, xlwt WriTes files. There is also a package called xlsutils, which has filter and (soon) copy facilities. You can find them all at http://pypi.python.org/pypi/PACKAGENAME … discussion at http://groups.google.com/group/python-excel
April 27th, 2009 at 9:19 pm
Thanks for the tutorial on xlrd. I always love finding out how easy important python modules like this are to use! I’m a grad student in experimental psychology; XLS format is a path all our data must pass through before getting analyzed!!
Cheers,
Matt
May 1st, 2009 at 9:54 am
Hi,
i have a value like 100 in a cell and i try to read using python script. While retrieving the value , it reads as 100.0. Is there a way to read as it is given in the excel sheet? (The type of the cell will be in General mode only)
Any help is appreciated. Thanks.
Regards,
Mega.
June 27th, 2009 at 9:41 pm
@Mega: One of the motivating factors behind developing xlrd was the what-you-see-is-often-NOT-what-you’ve-got problem with approaches like ODBC, COM (yes, it lies to you about floats with a $ in the format) and save-as-CSV. So xlrd tells you what data is in the file and what type it is, not what somebody with some unknown locale setting with some custom format might see on the screen. Your file contains a float, 100.0. The rest is up to you.
Forum for questions about xlrd, xlwt, xlutils: http://groups.google.com/group/python-excel … response time typically < 1 day, not two months :-)