Styling your Excel data with xlwt

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)‘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].


One thought on “Styling your Excel data with xlwt

Leave a Reply

Your email address will not be published. Required fields are marked *