Saltycrane logo

SaltyCrane Blog

Notes on Python, Django, and web development on Ubuntu Linux

    

Using Python to write to an Excel / OpenOffice Calc spreadsheet on Ubuntu Linux

Date: Feb. 15, 2010  |   Tags: linux, persistence, python, ubuntu   |   11 Comments

Via Matt Harrison's blog post, here is how to write Excel or OpenOffice.org Calc spreadsheet files using Python and the xlwt library. Xlwt is a fork of pyExcelerator which handles only writing spreadsheet files. For reading spreadsheets, see xlrd. Note, these libraries don't use COM, so they will work on non-Windows OSes, such as Linux. For more information, see Matt's blog post. He even has a PDF cheat sheet.

  • Install pip
  • Install xlwt 
    sudo pip install xlwt
  • Create an example script: 
    import xlwt

DATA = (("The Essential Calvin and Hobbes", 1988,),
        ("The Authoritative Calvin and Hobbes", 1990,),
        ("The Indispensable Calvin and Hobbes", 1992,),
        ("Attack of the Deranged Mutant Killer Monster Snow Goons", 1992,),
        ("The Days Are Just Packed", 1993,),
        ("Homicidal Psycho Jungle Cat", 1994,),
        ("There's Treasure Everywhere", 1996,),
        ("It's a Magical World", 1996,),)

wb = xlwt.Workbook()
ws = wb.add_sheet("My Sheet")
for i, row in enumerate(DATA):
    for j, col in enumerate(row):
        ws.write(i, j, col)
ws.col(0).width = 256 * max([len(row[0]) for row in DATA])
wb.save("myworkbook.xls")
  • Results:

11 Comments — feed icon Comments feed for this post


#1 Derek commented on 2010-02-25:

Just for clarity: I don't think its actually necessary to install pip in order to install xlwt (tho' it may be easier...).


#2 Eliot commented on 2010-02-25:

Derek: You're right, it's not necessary-- I'm just evangelizing.


#3 Darryl Hebbes commented on 2010-04-07:

and something I struggled to get right, adding frozen headings with styling to the worksheet:

import xlwt

DATA = (("The Essential Calvin and Hobbes", 1988,),
        ("The Authoritative Calvin and Hobbes", 1990,),
        ("The Indispensable Calvin and Hobbes", 1992,),
        ("Attack of the Deranged Mutant Killer Monster Snow Goons", 1992,),
        ("The Days Are Just Packed", 1993,),
        ("Homicidal Psycho Jungle Cat", 1994,),
        ("There's Treasure Everywhere", 1996,),
        ("It's a Magical World", 1996,),)

wb = xlwt.Workbook()
ws = wb.add_sheet("My Sheet")

# Add headings with styling and froszen first row
heading_xf = xlwt.easyxf('font: bold on; align: wrap on, vert centre, horiz center')
headings = ['Bill Watterson Books', 'Year Published']
rowx = 0
ws.set_panes_frozen(True) # frozen headings instead of split panes
ws.set_horz_split_pos(rowx+1) # in general, freeze after last heading row
ws.set_remove_splits(True) # if user does unfreeze, don't leave a split there
for colx, value in enumerate(headings):
    ws.write(rowx, colx, value, heading_xf)

for i, row in enumerate(DATA):
    for j, col in enumerate(row):
        ws.write(i, j, col)
ws.col(0).width = 256 * max([len(row[0]) for row in DATA])
wb.save("myworkbook.xls")

#4 Eliot commented on 2010-04-07:

Darryl: Thank you for adding your notes!


#5 Eloi commented on 2010-05-04:

In comment #3 where is: ws.write(i, j, col)

Should be: ws.write(i+1, j, col)

Otherwise it will rise: Exception: Attempt to overwrite cell

Thank's Eliot for the post it helped me a lot and thank you Darryl for the cool tip.


#6 Eliot commented on 2010-05-05:

Eloi: Thanks for the fix.


#7 PATX commented on 2010-06-21:

AWESOME article. Saved my life, working on a project that I will need to use this on. Thanks Eliot, this blog rules.

Also, maybe edit the tags and add "excel"?


#8 Eliot commented on 2010-06-21:

PATX: Thank you, glad it was helpful.


#9 Garrett commented on 2011-03-21:

I also recommend the PyWorkbooks project, although it does not yet work with Open Office, it should soon.

PyWorkbooks is a module to treat open Excel or Gnumeric files (and eventually Open Office) 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/


#10 Bruno commented on 2011-08-08:

Thanks, that saved a lot of time!


#11 Holger commented on 2011-08-11:

I also recommend the PyWorkbooks project, although it does not yet work with Open Office, it should soon.

Just for those who stumble upon this whilst looking for a tool to create spreadsheets standalone, like me:

Note that PyWorkbooks with Excel currently only works on Win (needs COM) and seems to require an active Excel session. I suppose I missed the word "open" in "[...] treat open Excel or Gnumeric files [...]" in comment #9.

Post a comment
Required
Required, but not displayed
Optional

Format using Markdown. (No HTML.)
  • Code blocks: prefix each line by at least 4 spaces or 1 tab (and a blank line before and after)
  • Code span: surround with backticks
  • Blockquotes: prefix lines to be quoted with >
  • Links: <URL>
  • Links w/ description: [description](URL)
Created with Django | Hosted by Linode