This post is about how to create styles in [Excel spreadsheets][excel] with [the most excellent xlwt][xlwt] for [Python][python]. The documentation for xlwt (version 0.7.2) is a little sketchy on how to use formatting. So here goes…
To apply formatting to a cell you pass an instance of the `xlwt.XFStyle` class as the fourth argument to the `xlwt.Worksheet.write` method. The best way to create an instance is to use the `xlwt.easyxf` helper, which takes a string that specifies the formatting for a cell.
The other thing about using styles is you should only make one instance of each, then pass that same style object every time you want to apply it to a cell.
An example which uses a few styles:
import xlwt
styles = dict(
bold = ‘font: bold 1’,
italic = ‘font: italic 1’,
# Wrap text in the cell
wrap_bold = ‘font: bold 1; align: wrap 1;’,
# White text on a blue background
reversed = ‘pattern: pattern solid, fore_color blue; font: color white;’,
# Light orange checkered background
light_orange_bg = ‘pattern: pattern fine_dots, fore_color white, back_color orange;’,
# Heavy borders
bordered = ‘border: top thick, right thick, bottom thick, left thick;’,
# 16 pt red text
big_red = ‘font: height 320, color red;’,
)
I have no idea what it is based on, but 20 = 1 pt. So 320 = 16 pt text.
book = xlwt.Workbook()
sheet = book.add_sheet(‘Style demo’)
for idx, k in enumerate(sorted(styles)):
style = xlwt.easyxf(styles[k])
sheet.write(idx, 0, k)
sheet.write(idx, 1, styles[k], style)
book.save(‘Example.xls’)
It isn’t included with [the current distribution on the cheese shop][pypi], but there is [a useful Excel spreadsheet demonstrating cell patterns][sheet] in the source repository.
You can find the complete list of possible cell formats by reading [the source for `xlwt.Styles`][styles].
[excel]: http://office.microsoft.com/en-us/excel/
[xlwt]: http://www.python-excel.org/
[python]: http://www.python.org/
[styles]: https://secure.simplistix.co.uk/svn/xlwt/tags/0.7.2/xlwt/Style.py
[pypi]: http://pypi.python.org/pypi/xlwt/0.7.2
[sheet]: https://secure.simplistix.co.uk/svn/xlwt/tags/0.7.2/xlwt/doc/pattern_examples.xls
