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: 2010-02-15  |   Tags: linux, python, ubuntu   |   8 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:

8 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.

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 Slicehost