#!/bin/env python
"""Executable cheatsheet illustrating use of pyExcelerator and its
fork, xlwt
I recommend using xlwt which is a somewhat unknown fork of
pyExcelerator. There are examples shipped with both projects, use
them if necessary, but the source is usually your best friend. The
libraries are quite capable but don't support charting. xlwt also has
a mailing list that is active here:
http://groups.google.com.au/group/python-excel
Another good link is here:
http://ntalikeris.blogspot.com/2007/10/create-excel-file-with-python-my-sort.html
This illustrates common usage for .xls generation, but also uses
factories to limit object creation of styles (which can crash Excel).
It's meant to show example, but for more details, I recommend the
sources I mention above.
Please send comments/suggestions my way
author: matthewharrison@gmail.com
"""
#import pyExcelerator as pycel
import xlwt as pycel
# Excel has issues when creating too many styles/fonts, hence use
# a factory to reuse instances (see FAQ#13 http://poi.apache.org/faq.html )
STYLE_FACTORY = {}
FONT_FACTORY = {}
def create_spreadsheet ():
# Create a workbook
wb = pycel.Workbook()
# Add a sheet
ws = wb.add_sheet( "Example Sheet ")
# Tweak printer settings
# following makes a landscape layout on Letter paper
# the width of the columns
ws.fit_num_pages = 1
ws.fit_height_to_pages = 0
ws.fit_width_to_pages = 1
# Set to Letter paper
# See BiffRecords.SetupPageRecord for paper types/orientation
ws.paper_size_code = 1
# Set to landscape
ws.portrait = 0
# Write some stuff using our helper function
# Formatting - hint, look at Format code in OOo
# format cells... Numbers tab
# Write a percent
write(ws, 0, 0, .495, {"format":"0%"})
# Write a percent with negatives red
write(ws, 1, 0, -.495, {"format":"0%;[RED]-0 %"})
# Dollar amounts
write(ws, 2, 0, 10.99, {"format":'$#,##0'})
# Font
# Size
write(ws, 0, 1, "Size 160(8pt) ", {"font": (("height", 160),)})
write(ws, 1, 1, "Size 200(10pt) ", {"font": (("height", 200),)})
# Bold
write(ws, 2, 1, "Bold text ", {"font": (("bold", True),)})
# Background color
# See http://groups.google.com.au/group/python-excel/attach/93621400bdddf464/palette_trial.xls?part=2
# for colour indices
YELLOW = 5
write(ws, 3, 1, "Yellow (5) Background ",
{ "background ": (("pattern ", pycel .Pattern .SOLID_PATTERN),
( "pattern_fore_colour ", YELLOW) )})
# Border
write(ws, 0, 2, "Border",
{ "border": (("bottom",pycel.Formatting .Borders .THIN),
( "bottom_colour ", YELLOW))})
# Wrapping
write(ws, 0, 3, "A bunch of long text to wrap ",
{ "alignment ":(("wrap", pycel .Alignment .WRAP_AT_RIGHT),)})
# Set column width
# (see pycel.BIFFRecords.ColInfoRecord for details, width in
# 1/256th of zero character)
write(ws, 0, 4, "A bunch of longer text not wrapped ")
ws.col(4).width = len("A bunch of longer text not wrapped ")*256
# Freeze/split headers when scrolling
write(ws, 0, 5, "Header")
ws.panes_frozen = True
ws.horz_split_pos = 1
for row in range(1, 200):
write(ws, row, 5, row)
# Save the workbook
wb.save("out.xls ")
def write(ws, row, col, data, style =None):
"""
Write data to row, col of worksheet (ws) using the style
information.
Again, I'm wrapping this because you'll have to do it if you
create large amounts of formatted entries in your spreadsheet
(else Excel, but probably not OOo will crash).
"""
if style:
s = get_style(style)
ws .write(row, col, data, s)
else:
ws .write(row, col, data)
def get_style (style):
"""
Style is a dict maping key to values.
Valid keys are: background, format, alignment, border
The values for keys are lists of tuples containing (attribute,
value) pairs to set on model instances...
"""
print "KEY", style
style_key = tuple(style.items())
s = STYLE_FACTORY .get(style_key, None)
if s is None:
s = pycel.XFStyle()
for key, values in style.items():
if key == "background ":
p = pycel.Pattern()
for attr, value in values:
p .__setattr__(attr, value)
s .pattern = p
elif key == "format":
s .num_format_str = values
elif key == "alignment ":
a = pycel.Alignment()
for attr, value in values:
a .__setattr__(attr, value)
s .alignment = a
elif key == "border":
b = pycel.Formatting .Borders()
for attr, value in values:
b .__setattr__(attr, value)
s .borders = b
elif key == "font":
f = get_font(values)
s .font = f
STYLE_FACTORY[style_key] = s
return s
def get_font (values):
"""
'height' 10pt = 200, 8pt = 160
"""
font_key = values
f = FONT_FACTORY .get(font_key, None)
if f is None:
f = pycel.Font()
for attr, value in values:
f .__setattr__(attr, value)
FONT_FACTORY[font_key] = f
return f
if __name__ == "__main__ ":
create_spreadsheet()
#!/bin/env python
"""Executable cheatsheet illustrating use of pyExcelerator and its
fork, xlwt
I recommend using xlwt which is a somewhat unknown fork of
pyExcelerator. There are examples shipped with both projects, use
them if necessary, but the source is usually your best friend. The
libraries are quite capable but don't support charting. xlwt also has
a mailing list that is active here:
http://groups.google.com.au/group/python-excel
Another good link is here:
http://ntalikeris.blogspot.com/2007/10/create-excel-file-with-python-my-sort.html
This illustrates common usage for .xls generation, but also uses
factories to limit object creation of styles (which can crash Excel).
It's meant to show example, but for more details, I recommend the
sources I mention above.
Please send comments/suggestions my way
author: matthewharrison@gmail.com
"""
#import pyExcelerator as pycel
import xlwt as pycel
# Excel has issues when creating too many styles/fonts, hence use
# a factory to reuse instances (see FAQ#13 http://poi.apache.org/faq.html )
STYLE_FACTORY = {}
FONT_FACTORY = {}
def create_spreadsheet():
# Create a workbook
wb = pycel.Workbook()
# Add a sheet
ws = wb.add_sheet("Example Sheet")
# Tweak printer settings
# following makes a landscape layout on Letter paper
# the width of the columns
ws.fit_num_pages = 1
ws.fit_height_to_pages = 0
ws.fit_width_to_pages = 1
# Set to Letter paper
# See BiffRecords.SetupPageRecord for paper types/orientation
ws.paper_size_code = 1
# Set to landscape
ws.portrait = 0
# Write some stuff using our helper function
# Formatting - hint, look at Format code in OOo
# format cells... Numbers tab
# Write a percent
write(ws, 0, 0, .495, {"format":"0%"})
# Write a percent with negatives red
write(ws, 1, 0, -.495, {"format":"0%;[RED]-0%"})
# Dollar amounts
write(ws, 2, 0, 10.99, {"format":'$#,##0'})
# Font
# Size
write(ws, 0, 1, "Size 160(8pt)", {"font": (("height", 160),)})
write(ws, 1, 1, "Size 200(10pt)", {"font": (("height", 200),)})
# Bold
write(ws, 2, 1, "Bold text", {"font": (("bold", True),)})
# Background color
# See http://groups.google.com.au/group/python-excel/attach/93621400bdddf464/palette_trial.xls?part=2
# for colour indices
YELLOW = 5
write(ws, 3, 1, "Yellow (5) Background",
{"background": (("pattern", pycel.Pattern.SOLID_PATTERN),
("pattern_fore_colour", YELLOW) )})
# Border
write(ws, 0, 2, "Border",
{"border": (("bottom",pycel.Formatting.Borders.THIN),
("bottom_colour", YELLOW))})
# Wrapping
write(ws, 0, 3, "A bunch of long text to wrap",
{"alignment":(("wrap", pycel.Alignment.WRAP_AT_RIGHT),)})
# Set column width
# (see pycel.BIFFRecords.ColInfoRecord for details, width in
# 1/256th of zero character)
write(ws, 0, 4, "A bunch of longer text not wrapped")
ws.col(4).width = len("A bunch of longer text not wrapped")*256
# Freeze/split headers when scrolling
write(ws, 0, 5, "Header")
ws.panes_frozen = True
ws.horz_split_pos = 1
for row in range(1, 200):
write(ws, row, 5, row)
# Save the workbook
wb.save("out.xls")
def write(ws, row, col, data, style=None):
"""
Write data to row, col of worksheet (ws) using the style
information.
Again, I'm wrapping this because you'll have to do it if you
create large amounts of formatted entries in your spreadsheet
(else Excel, but probably not OOo will crash).
"""
if style:
s = get_style(style)
ws.write(row, col, data, s)
else:
ws.write(row, col, data)
def get_style(style):
"""
Style is a dict maping key to values.
Valid keys are: background, format, alignment, border
The values for keys are lists of tuples containing (attribute,
value) pairs to set on model instances...
"""
print "KEY", style
style_key = tuple(style.items())
s = STYLE_FACTORY.get(style_key, None)
if s is None:
s = pycel.XFStyle()
for key, values in style.items():
if key == "background":
p = pycel.Pattern()
for attr, value in values:
p.__setattr__(attr, value)
s.pattern = p
elif key == "format":
s.num_format_str = values
elif key == "alignment":
a = pycel.Alignment()
for attr, value in values:
a.__setattr__(attr, value)
s.alignment = a
elif key == "border":
b = pycel.Formatting.Borders()
for attr, value in values:
b.__setattr__(attr, value)
s.borders = b
elif key == "font":
f = get_font(values)
s.font = f
STYLE_FACTORY[style_key] = s
return s
def get_font(values):
"""
'height' 10pt = 200, 8pt = 160
"""
font_key = values
f = FONT_FACTORY.get(font_key, None)
if f is None:
f = pycel.Font()
for attr, value in values:
f.__setattr__(attr, value)
FONT_FACTORY[font_key] = f
return f
if __name__ == "__main__":
create_spreadsheet()
pyExcelerator/xlwt Cheatsheet
matthewharrison@gmail.com
Creative Commons Attribution 3.0 License.
