Write Excel files with Python using xlwt

 

In a previous post (which turned out to be pretty popular) I showed you how to read Excel files with Python. Now for the reverse: writing Excel files.

First, you’ll need to install the xlwt package by John Machin.

The basics

In order to write data to an Excel spreadsheet, first you have to initialize a Workbook object and then add a Worksheet object to that Workbook. It goes something like this:

import xlwt wbk = xlwt.Workbook() sheet = wbk.add_sheet('sheet 1')

Now that the sheet is created, it’s very easy to write data to it.

# indexing is zero based, row then column sheet.write(0,1,'test text')

When you’re done, save the workbook (you don’t have to close it like you do with a file object)

wbk.save('test.xls')

Digging deeper

Overwriting cells

Worksheet objects, by default, give you a warning when you try to overwrite:

sheet.write(0,0,'test') sheet.write(0,0,'oops')     # returns error: # Exception: Attempt to overwrite cell: sheetname=u'sheet 1' rowx=0 colx=0

To change this behavior, use the cell_overwrite_ok=True kwarg when creating the worksheet, like so:

sheet2 = wbk.add_sheet('sheet 2', cell_overwrite_ok=True) sheet2.write(0,0,'some text') sheet2.write(0,0,'this should overwrite')

Now you can overwrite sheet 2 (but not sheet 1).

More goodies

# Initialize a style style = xlwt.XFStyle()    # Create a font to use with the style font = xlwt.Font() font.name = 'Times New Roman' font.bold = True    # Set the style's font to this new one you set up style.font = font    # Use the style when writing sheet.write(0, 0, 'some bold Times text', style)

xlwt allows you to format your spreadsheets on a cell-by-cell basis or by entire rows; it also allows you to add hyperlinks or even formulas. Rather than recap it all here, I encourage you to grab a copy of the source code, in which you can find the examples directory. Some highlights from the examples directory in the source code:

• dates.py, which shows how to use the different date formats
• hyperlinks.py, which shows how to create hyperlinks (hint: you need to use a formula)
• merged.py, which shows how to merge cells
• row_styles.py, which shows how to apply styles to entire rows.

Non-trivial example

Here’s an example of some data where the dates not formatted well for easy import into Excel:

20 Sep, 263, 1148,   0,   1,   0,   0,   1,   12.1,   13.9, 1+1, 19.9  20 Sep, 263, 1118,   0,   1,   0, 360,   0,   14.1,   15.3, 1+1, 19.9  20 Sep, 263, 1048,   0,   1,   0,   0,   0,   14.2,   15.1, 1+1, 19.9  20 Sep, 263, 1018,   0,   1,   0, 360,   0,   14.2,   15.9, 1+1, 19.9  20 Sep, 263, 0948,   0,   1,   0,   0,   0,   14.4,   15.3, 1+1, 19.9

The first column has the day and month separated by a space. The second column is year-day, which we’ll ignore. The third column has the time. The data we’re interested in is in the 9th column (temperature). The goal is to have a simple Excel file where the first column is date, and the second column is temperature.

Here’s a [heavily commented] script to do just that. It assumes that you have the data saved as weather.data.example.

''' Script to convert awkwardly-formatted weather data into an Excel spreadsheet using Python and xlwt. '''    from datetime import datetime import xlwt    # Create workbook and worksheet wbk = xlwt.Workbook() sheet = wbk.add_sheet('temperatures')    # Set up a date format style to use in the # spreadsheet excel_date_fmt = 'M/D/YY h:mm' style = xlwt.XFStyle() style.num_format_str = excel_date_fmt    # Weather data has no year, so assume it's the current year. year = datetime.now().year    # Convert year to a string because we'll be # building a date string below year = str(year)    # The format of the date string we'll be building python_str_date_fmt = '%d %b-%H%M-%Y'    row = 0  # row counter f = open('weather.data.example') for line in f:     # separate fields by commas     L = line.rstrip().split(',')        # skip this line if all fields not present     if len(L) < 12:         continue        # Fields have leading spaces, so strip 'em     date = L[0].strip()     time = L[2].strip()        # Datatypes matter. If we kept this as a string     # in Python, it would be a string in the Excel sheet.     temperature = float(L[8])        # Construct a date string based on the string     # date format  we specified above     date_string = date + '-' + time + '-' + year        # Use the newly constructed string to create a     # datetime object     date_object = datetime.strptime(date_string,                                     python_str_date_fmt)        # Write the data, using the style defined above.     sheet.write(row,0,date_object, style)     sheet.write(row,1,temperature)        row += 1    wbk.save('reformatted.data.xls')       http://scienceoss.com/write-excel-files-with-python-using-xlwt/