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