Python xlwt: writing excel files

To write excel files with Python, first download / install xlwt.

pip install xlwt

A demo with common operations should help more than a write up:

Create workbook and sheet

import xlwt
workbook = xlwt.Workbook() 
sheet = workbook.add_sheet("Sheet Name") 

Write a cell

sheet.write(0, 0, 'foobar') # row, column, value

Apply styles to a cell (bold)

style = xlwt.easyxf('font: bold 1')
sheet.write(0, 0, 'foobar', style)

Set width of column

To set the widths, you must set the width attribute to 256*NUM_CHARS where 256 is equal to the width of the zero character.

sheet.col(0).width = 256 * (len(key) + 1) 
# set width.. 256 = 1 width of 0 character

Apply multiple styles to a cell

Note that you are limited to 4k styles per document, meaning you should not initialize a style for every cell but re-use them (read below for simple cache solution).

style = xlwt.easyxf('font: bold 1, color red;'))
sheet.write(0, 0, 'foobar', style)

Apply currency style

To set currency, add the keyword argument num_format_str to the easyxf function or set the attribute on the returned style object.

style = easyxf(num_format_str='$#,##0.00')
# or set it directly on the style object
style = easyxf('font: bold 1')
style.num_format_str = '$#,##0.00'
sheet.write(0, 0, '100.00', style)

Write excel formulas

Writing formulas is trivial with xlwt.Formula

sheet.write(0, 0, xlwt.Formula('HYPERLINK("http://yujitomita.com"; "click me")'))

Save

workbook.save("foobar.xls") 
# done!

Gotchas

Here are some things that “got” me good (took some troubleshooting).

Can’t override cells

I actually like this feature – it prevents cells from being overridden, so I’ve figured out on more than one occasion my script was failing. Why would you be overwriting a cell anyways?

# to overwrite cells, create the sheet with kwarg cell_overwrite_ok
workbook.add_sheet('foobar', cell_overwrite_ok=True) 

Valid sheet name

• Sheets are only valid if under 31 characters
• They can’t contain characters such as ‘:’, ‘/’. More to come I’m sure…

Style 4k limit per document

Applying styles has been made trivial, if you know the rules.

There’s a 4k limit on styles defined in a document, so I created a cached easyxf function which at least tries to pull a definition from cache before creating a new style.

class MyClass(object):
	kwd_mark = object()
	def cached_easyxf(self, string='', **kwargs):
		if not hasattr(self, '_cached_easyxf'):
			self._cached_easyxf = {}
		key = (string,) + (self.kwd_mark,) + tuple(sorted(kwargs.items()))
		return self._cached_easyxf.setdefault(key, xlwt.easyxf(string, **kwargs))

Easyxf string format

I just did a bit of experimentation to figure out the common formats.

For example, the string format appears to accept an array of key value pairs separated by a space.

‘KEY: KEY-VALUE VALUE, KEY-VALUE VALUE; KEY2: KEY-VALUE2 VALUE2’

sheet.write(0, 0, xlwt.easyxf('font: bold 1')) # bold
sheet.write(0, 0, xlwt.easyxf('font: bold 1, color: blue, underline single'))