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
[...] 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 [...]
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
[...] 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. [...]
Does it also write excel format files?
@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.
[...] Read the rest of this great post here [...]
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.
@Attila Bleier:
Nice catch, thanks. It’s fixed now.
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
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
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.
@Ryan
PyExcelerator is no longer supported/maintained. Try xlwt for COM-free creation of files.
Hi,
Thanks a lot. Could you please let me know on how to update a cell in a xls file using xlrd.
Regards,
Shakila.
@ 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
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
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.
[...] Ref: http://scienceoss.com/read-excel-files-from-python/ [...]
@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 :-)
[...] a previous post (which turned out to be pretty popular) I showed you how to read Excel files with Python. Now for [...]
Hi Guys,
I need a script, how to open the excel sheet using python script.
Monu
Would someone please share or write a python script that will open an Excel file written in .XHT format (or any non-.XLS format) and save it as a .XLS format?
Thank you,
John
Hey thanks
very helpful…