scienceoss.com

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')

Still curious? Other questions? Check out the python-excel google group ! Also check out xlutils for more functionality, which I plan to play around with next.

This entry was posted on Sunday, September 20th, 2009 at 1:29 pm and is filed under Python, excel, other software. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.