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(""; "click me")'))

# done!


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.


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

10 thoughts on “Python xlwt: writing excel files

  1. Is there any way to do auto number format, means if the value is number the excel treat it as number and not as a string and shows ‘Number stored as Text’

  2. Would you happen to know how to force the style for a cell such that it mimics what excel does when you create a hyperlink formula? That is, your data is link=xlwt.Formula(HYPERLINK(‘’, “friendly name’)

    and the appears: underlined, with font color blue, and (here’s the catch) once you click on it, its font color turns to violet.

    would this even be possible?
    Thanks in advance

  3. Do you know you to write the document properties with xlwt? I refer here to: Title, Subject, Author, Manager, Company, Category, Keywords and Comments.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s