Skip to main content.
June 20th, 2006

Generate an Excel Formatted File Right in Python


It turns out that this nifty pyExcelerator program will let you write an Excel formatted document directly from Python.

While you could always use COM automation with the Python Win32 extensions to make Excel documents, I think this library is the way to go. It lets you write an Excel file without even having to have Excel installed, and you don’t even need to be running on Windows. That’s pretty neat. It sounds like an easy way to offer an Excel output format option from your application or website, without requiring any messing with Windows or Excel.

Sadly there wasn’t any friendly documentation for this project online, so here I offer my first code snippet using this package. I mostly figured it out from the example files included with the package. It writes a few rows of data and styles the data depending on content.

import pyExcelerator as xl

def save_in_excel(headers,values):
    #Open new workbook
    mydoc=xl.Workbook()
    #Add a worksheet
    mysheet=mydoc.add_sheet("test")
    #write headers
    header_font=xl.Font() #make a font object
    header_font.bold=True
    header_font.underline=True
    #font needs to be style actually
    header_style = xl.XFStyle(); header_style.font = header_font
    for col,value in enumerate(headers):
        mysheet.write(0,col,value,header_style)
    #write values and highlight those that match my criteria
    highlighted_row_font=xl.Font() #no real highlighting available?
    highlighted_row_font.bold=True
    highlighted_row_font.colour_index=2 #2 is red,
    highlighted_row_style = xl.XFStyle(); highlighted_row_style.font = highlighted_row_font
    for row_num,row_values in enumerate(values):
        row_num+=1 #start at row 1
        if row_values[1]=='Manatee':
            for col,value in enumerate(row_values):
                #make Manatee's (sp) red
                mysheet.write(row_num,col,value,highlighted_row_style)
        else:
            for col,value in enumerate(row_values):
                #normal row
                mysheet.write(row_num,col,value)
    #save file
    mydoc.save(r'C:testpyexel.xlt')

headers=['Date','Name','Localatity']
data=[
['June 11, 2006','Greg','San Jose'],
['June 11, 2006','Greg','San Jose'],
['June 11, 2006','Greg','San Jose'],
['June 11, 2006','Greg','San Jose'],
['June 11, 2006','Manatee','San Jose'],
['June 11, 2006','Greg','San Jose'],
['June 11, 2006','Manatee','San Jose'],
]

save_in_excel(headers,data)

Here’s what my file ended up looking like:
Clipboard01.png

Tags: , , , ,

Posted by Greg Pinero (Primary Searcher) in Python, Excel

This entry was posted on Tuesday, June 20th, 2006 at 3:47 pm and is filed under Python, Excel. You can follow any responses to this entry through the comments RSS 2.0 feed. Both comments and pings are currently closed.

17 Responses to “Generate an Excel Formatted File Right in Python”

  1. Andrew says:

    For weeks I’ve been looking for a good Python library to (natively) create XLS files, and thanks to this, I managed to find one.

    (And your nice simple example made it straightforward for me to work out what I wanted.)

    Cheers!

    August 3rd, 2006 at 7:21 am
  2. Greg Pinero (Primary Searcher) says:

    Thanks Andrew, glad to hear I could help!

    August 3rd, 2006 at 8:54 am
  3. Derek says:

    A great reference - downloaded it yesterday and I already have a happy “customer”. I am working on a more handy ‘refence’ sheet of the properties if you want to collaborate?

    What I cannot find is to how to align various types of data: string, dates and numeric?

    August 18th, 2006 at 2:20 am
  4. Greg Pinero (Primary Searcher) says:

    Derek,

    My post above is the limit of my knowledge on this module for now :-( But I will gladly be a user of your reference sheet and post it here. Please email me once you’ve made it. It sounds extremely useful!

    I found the best way to learn this module is to browse through the examples it includes. Have you tried that?

    August 18th, 2006 at 1:09 pm
  5. Ivar Refsdal says:

    I made something *very* cheesy for this.

    Basically, you can do
    myvalue = mysheet.get_xy(’a', 1)
    (mysheet needs to be a BasicSheet class)

    Improvements are welcome. :)


    class BasicSheet(dict):
    def get_xy(self, x, y):
    y = y - 1
    if type(x)==str or type(x)==unicode:
    x = self.__x_to_int(x)
    if (y,x) in self.keys():
    return self[(y,x)]
    else:
    return None

    def __x_to_int(self, x):
    jalla = ("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", "aa", "ab", "ac", "ad", "ae", "af", "ag", "ah", "ai", "aj", "ak", "al", "am", "an", "ao", "ap", "aq", "ar", "as", "at", "au", "av", "aw", "ax", "ay", "az")
    find_list = enumerate(jalla)
    xres = None
    for index, item in find_list:
    if item.lower()==x.lower(): xres = int(index)
    return xres

    September 6th, 2006 at 6:38 am
  6. Greg Pinero (Primary Searcher) says:

    Thanks Ivar … what does it do?

    September 6th, 2006 at 10:08 am
  7. Derek says:

    Anyone else having problems loading files into Excel 2002 - I get a “too many fonts” error message. The same file seems to load OK in Excel 2000.

    October 6th, 2006 at 1:14 am
  8. Roger says:

    One flaw of pyExcelerator is can not write comments on one cell, at least I have not find the method.

    January 26th, 2007 at 1:03 am
  9. Greg Pinero (Primary Searcher) says:

    Roger, I believe you are correct. That would be a nice feature to have.

    January 26th, 2007 at 2:12 am
  10. Jimmy McMasters says:

    Does anyone know how to write the formulas in R1C1 reference? I changed the worksheet property _RC_ref_mode to True but I have not been able to get the formula to work. The exception states “cannot parse formula”.

    February 14th, 2007 at 5:32 pm
  11. Greg Pinero (Primary Searcher) says:

    Jimmy, I’m not sure about that one. Perhaps it’s a case issue? Otherwise dig into the source code starting that the file/line that threw that parsing exception.

    February 14th, 2007 at 6:40 pm
  12. 上海探戈 says:

    thanks for your help~
    and I need some help about SQL and excel
    can you help me ?
    thanks again.

    April 1st, 2007 at 1:46 am
  13. tuco says:

    How do you open an existing Excel file? I see Workbook() has a “save” method. It would be convenient to have an “open” method too.

    So far I have spent more time trying to figure how to use the module than writing code. I’m rummaging through the source and their isn’t even any COMMENTS or DOC STRINGS! A simple doc string for each method would would save hours if not days. Who has the time?

    October 4th, 2007 at 2:34 pm
  14. Greg Pinero (Primary Searcher) says:

    tuco, I don’t remember much from when I used this module. The code includes examples. Do any of those do what you need?

    October 4th, 2007 at 3:12 pm
  15. Todd says:

    I haven’t been able to open an Excel file with pyExcelerator either. There is an ImportXLS.py which can read a specified Excel file and spit out all the data with the parse_xls function. If I can get the Excel file open I can use Workbook() to add sheets, write data…

    I found xlrd which can open Excel files, but can’t write. Let me know if anybody has found a way to open an existing Excel file and let pyExcelerator modify the file and save it.

    October 25th, 2007 at 1:27 pm
  16. Janos says:

    Hi!

    I want to change background colour of cells in excel. Similarly like in HTML. (I have HTML tables and I want to export them to excel). So I have the colour of the cells in this format: #51FF7C (~green). How can I give this colour to excel???
    Thx for help.

    Janos

    February 11th, 2008 at 12:00 pm
  17. David Davies says:

    A far simple method is to seperate you text with \t (python for tab) and simply write the text to a file…..

    text=”Hello\tGoodbye\n1\t2″
    f=open(’c:\test.xls’,'w’)
    f.write(text)
    f.close()

    C’est Voila 1 beautiful Excel looking something like this….

    Hello Goodbye
    1 2

    Any not a trace of pyExcelerator in sight :)

    May 20th, 2008 at 11:37 am