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:
[tags]Excel, Python, Python Excel, Excel Format, pyExcelerator[/tags]
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!
Thanks Andrew, glad to hear I could help!
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?
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?
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
Thanks Ivar … what does it do?
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.
One flaw of pyExcelerator is can not write comments on one cell, at least I have not find the method.
Roger, I believe you are correct. That would be a nice feature to have.
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”.
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.
thanks for your help~
and I need some help about SQL and excel
can you help me ?
thanks again.
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?
tuco, I don’t remember much from when I used this module. The code includes examples. Do any of those do what you need?
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.
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
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