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
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
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.
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.
@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 :-)
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…
Hi , Could you please help me with opening and excel .xls in append format so that i need not every time write on a new excel sheet. Please help me out with this.
Thank you
Prasanna
Is there a way to read the hyperlinks from the excel columns file using python or xlrd?
I love the library, very useful.
Although, i have a comment to make on what you said John. Excel has a way to format cells, if you change that, you will get the same result on every computer, regardless of the local settings. This is what someone else above was talking about.
I know it can be hard to take the raw data (ex: 100.0) and apply the format that is specified somewhere in the Excel file (so it gives something like: $ 100), but in many cases we want to know what people are seeing. Maybe you could make a separate function to get the shown value of the cell, or just get the format settings for the cell and let us do the treatment to get it to the shown value by ourselves.
nice post.
but just a question, how do we close an opened workbook?
would that be xlrd.close_workbook()??
Thanks!
hi! what if i just want to access the first row of the excel file? how is that? thanks in advance
will u please tell me how we get rounded value?
and if cell has any formatting its give nagitive value. like
a cell text has red color and its closed in parentheses ().
please help
thanx
Does this work for different languages?
Thank you.
Hi,
Can you please tell me how can i use python for excel ODBC?
I also recommend the PyWorkbooks project.
PyWorkbooks is a module to treat open excel files as native python objects, and interface with them using standard calls. (i.e. B[[1, :10] will get you the first 10 points of data on row 1, the same as B['A2:J2'], both are valid), and change it using standard calls as well.
check out the source, distutils install file, and documentation here:
http://sourceforge.net/projects/pyworkbooks/
amazing, i wonder how to read row~cols symbolic names themselves. very useful using nrows and ncols for measuring workspaces!
Hi,
Highly informative site!
Wanna know if there’s any way to ignore the grayed out or the fields that have been struck-off of an excel sheet while writing its contents into another file using xlrd..
Thanks in advance:)
Thanks for this post. I am heading straight for the next tutorial on writing into a .xls file.
Regards.
Why do people see a post thats “How to read an excel file from python” then respond “I need a script, how to open the excel sheet using python script.”. Seriously guys, read the article. Its really really easy.
Great post and sweet lib!
/K