Another XLWT Example

After completing the last example, I wanted to try something a little more interesting with XLWT this time around. This article describes a short script that uses Python and XLWT to download some raw data from the web, parse it, and write a spreadsheet with a new column derived from the data. The data for this example comes from research done by David Harrison and Daniel L. Rubinfeld in “Hedonic Housing Prices and the Demand for Clean Air”, published in the Journal of Environmental Economics and Management, Volume 5, (1978), and contains information on location, pricing, tax and other information from the Boston housing market. I’ll be illustrating three things in this script:

  • Downloading data directly from the web
  • Parsing the data, removing extraneous information at the top of the file and writing the real data fields to a spreadsheet
  • Adding a hyperlink for each record that links to a Google Map, based on the latitude and longitude data given

Note that it’s possible to do these steps using a web browser and an interactive Excel session. You can easily download the file, import it into Excel, remove the leading text, and make a formula to produce a hyperlink. But the beauty of this script is that everything is done automatically, which can be very handy if the source data is constantly updated.

Here’s the script that performs these operations:

#
# xlwt_bostonhousing.py
#
import sys
from urllib2 import urlopen
from xlwt import Workbook, easyxf, Formula

def doxl():
    '''Read the boston_corrected.txt file based on
       Harrison, David, and Daniel L. Rubinfeld, "Hedonic Housing Prices
       and the Demand for Clean Air," Journal of Environmental Economics
       and Management, Volume 5, (1978), write to an excel spreadsheet .  
       '''
    #URL = 'http://stat.cmu.edu/datasets/boston_corrected.txt'
    URL = 'https://raw.github.com/pythonexcels/xlwt/master/boston_corrected.txt'
    try:
        fp = urlopen(URL)
    except:
        print 'Failed to download %s' % URL
        sys.exit(1)
    lines = fp.readlines()

    wb = Workbook()
    ws = wb.add_sheet('Housing Data')
    ulstyle = easyxf('font: underline single')
    r = 0
    for line in lines:
        tokens = line.strip().split('\t')
        if len(tokens) != 21:
            continue
        for c,t in enumerate(tokens):
            for dtype in (int,float):
                try:
                    t = dtype(t)
                except:
                    pass
                else:
                    break 
            ws.write(r,c+1,t)
        if r == 0:
            hdr = tokens
            ws.write(r,0,'MAPLINK')
        else:
            d = dict(zip(hdr,tokens))
            link = 'HYPERLINK("http://maps.google.com/maps?q=%s'+\
                   ',+%s+(Observation+%s)&hl=en&ie=UTF8&z=14&'+\
                   'iwloc=A";"MAP")'
            link = link % (d['LAT'],d['LON'],d['OBS.'])
            ws.write(r,0,Formula(link),ulstyle)

        r += 1
    wb.save('bostonhousing.xls')
    print 'Wrote bostonhousing.xls'

if __name__ == "__main__":
    doxl()

As in the previous post, you must have xlwt installed (refer to the site http://www.python-excel.org for information on downloading and installing). Looking at the important bits in the script above, the following lines

#URL = 'http://stat.cmu.edu/datasets/boston_corrected.txt'
URL = 'https://raw.github.com/pythonexcels/xlwt/master/boston_corrected.txt'
try:
    fp = urlopen(URL)
except:
    print 'Failed to download %s' % URL
    sys.exit(1)
lines = fp.readlines()

open the URL for the boston_corrected.txt file, then reads the URL and returns a list of strings (update 9/15/12: the original link is now broken, I’ve updated the script to pull the data from a copy on github). The next section:

wb = Workbook()
ws = wb.add_sheet('Housing Data')
ulstyle = easyxf('font: underline single')
r = 0

creates a new Workbook object, then adds a sheet named “Housing Data” to the workbook. The easyfx function provides a convenient way to add formatting to the spreadsheet; in this example, the single underline format is used to denote a hyperlink. In the next line, the variable r acts as a row counter.

The for loop below examines each row of data:

for line in lines:
    tokens = line.strip().split('\\t')
    if len(tokens) != 21:
        continue
    for c,t in enumerate(tokens):
        for dtype in (int,float):
            try:
                t = dtype(t)
            except:
                pass
            else:
                break
        ws.write(r,c+1,t)

Each line is “stripped” (leading and trailing white space characters are removed), then split by tab characters. A data line contains 21 fields of information, otherwise it is rejected. To properly format the data for the spreadsheet, the datatype is set using try-except-else within the for* loop. The loop only considers string, integer and float data, which is sufficient for this input data. More complex input files may contain date information which would require additional handling. The cell data with the correct type setting is written to the spreadsheet using the ws.write statement.

The next section builds the hyperlink to a Google Map using the latitude and longitude information within the input data.

    if r == 0:
        hdr = tokens
        ws.write(r,0,'MAPLINK')
    else:
        d = dict(zip(hdr,tokens))
        link = 'HYPERLINK("http://maps.google.com/maps?q=%s'+\
               ',+%s+(Observation+%s)&hl=en&ie=UTF8&z=14&'+\
               'iwloc=A";"MAP")'
        link = link % (d['LAT'],d['LON'],d['OBS.'])
        ws.write(r,0,Formula(link),ulstyle)

    r += 1
wb.save('bostonhousing.xls')
print 'Wrote bostonhousing.xls'

If this is the first row of data (if r == 0), it is assumed to be header data and is saved in the hdr variable. Otherwise, the statement d = dict(zip(hdr,tokens)) builds a dictionary, using the header information as keys. This allows each field to be referenced by its column heading. The hyperlink is built by specifying a URL containing http://maps.google.com/maps?, with the corresponding latitude (d['LAT']) and longitude (d['LON']) information from the current line of data. (Forgive the link formatting, my original one line of code is split across four lines to reduce the line width.) Finally, the hyperlink data is written to the spreadsheet with ws.write. The last two lines write the spreadsheet and print a message.

The original raw data looks like this:

rawdata

The bostonhousing.xls spreadsheet containing the original data and the new map hyperlink looks like this:

bostonhousing

Clicking on the MAP link brings up a Google Map showing the location according to the latitude and longitude.

googlemap

For some reason, the location of latitude 42.255000 and longitude -70.955000 isn’t in the town of Nahant, but in the middle of Rock Island Cove near Quincy. It’s left as an exercise for the reader to determine why this is so.

Prerequisites

Python (refer to http://www.python.org)

xlwt (refer to http://www.python-excel.org)

Source Files and Scripts

Source for the program and data text file are available athttp://github.com/pythonexcels/xlwt/tree/master

References

http://www.python-excel.org

This site contains pointers to the best information available about working with Excel files in the Python programming language.

http://groups.google.com/group/python-excel

Google group for questions on xlrd, xlwt, xlutils and general questions on interfacing to Excel with Python

Thanks everyone — Dan

Using XLWT to Write Spreadsheets Without Excel

Long before I even started thinking about writing a blog covering the integration possibilities between Excel and Python, the boys at Simplistix (http://www.simplistix.co.uk) were crafting some of the handiest Python modules around: xlrd, xlwt and xlutils. These fantastic modules enable Python to directly read and write an Excel .xls file, without the need for Microsoft Excel. Not only can you insert numbers and text, but xlwt supports a multitude of formatting options. There’s even a great set of documentation, and a Google group monitored by the maintainers. You can find out more about these modules athttp://www.python-excel.org

I’ve written a fair number of Python scripts that interface with Excel through the COM interface, but for many tasks you don’t need to invoke Excel to operate on spreadsheet data. Although some advanced operations like charting and pivot tables are not currently supported, these utilities provide an excellent way of interfacing with Excel spreadsheets for many common jobs.

In my example below, I format a raw text file containing information on reference account categories for a hospital (based on data from data.gov):

09700RESEARCH
09800PHYSICIANS  PRIVATE OFFICES
09900NONPAID WORKERS
MANAGEMENT FEES
REFERENCE LABS

The raw data is smashed together, with the account number and name running into each other without separation. Some of the data doesn’t even have an account number at all. The script accomplishes the following:

  • Separate the account number and name, and write them into two separate columns in the spreadsheet
  • Assign an account number of 99999 for unnumbered accounts, and set the cell color to red
  • Convert the upper cased account name to a properly capitalized name, remove any extra spaces
  • Format the column width of each spreadsheet column based on the width of the data

Based on the input above, the goal is to produce Excel output like this:

Excel output

Since there are no charts, pivot tables or other fancy formatting needed, xlwt is perfect for the job. Here is the script that performs the conversion:

import sys
import re
from xlwt import Workbook, easyxf

def doxl():
    '''Read raw account number and name strings, separate the data and
       write to an excel spreadsheet.  Properly capitalize the account
       names and mark cells with no account number as 99999 with red fill
       '''
    try:
        fp = open("hospdata.txt")
    except:
        print 'Failed to open hospdata.txt'
        sys.exit(1)
    lines = fp.readlines()

    nameandnum = re.compile(r'(\d+)\s*(.*)\s*')
    wb = Workbook()
    wsraw = wb.add_sheet('Raw Data')
    ws = wb.add_sheet('Account List')
    ws.write(0,0,'Account Number')
    ws.write(0,1,'Account Name')
    ws.col(0).width = len('Account Number') * 256
    ws.col(1).width = max([len(l) for l in lines]) * 256
    r = 1

    for line in lines:
        wsraw.write(r,0,line.strip())
        m = nameandnum.match(line)
        if m:
            ws.write(r,0,int(m.group(1)))
            ws.write(r,1,' '.join([w.capitalize() for w in m.group(2).split()]))
        else:
            ws.write(r,0,99999,easyxf('pattern: pattern solid, fore_colour red;'))
            ws.write(r,1,' '.join([w.capitalize() for w in line.split()]))
        r += 1
    wb.save('accounts.xls')
    print 'Wrote accounts.xls'

if __name__ == "__main__":
    doxl()

Before running this example, you must have xlwt installed (please refer to the sitehttp://www.python-excel.org for information on downloading and installing). Let’s look at the important bits in the script above.

The following lines:

Lines 12-17:

try:
   fp = open("hospdata.txt")
except:
   print 'Failed to open hospdata.txt'
   sys.exit(1)
lines = fp.readlines()

attempt to open the file hospdata.txt. If successful, the file contents are read into “lines” as a list of strings. Next, line 19

nameandnum = re.compile(r'(\d+)\s*(.*)\s*')

establishes a compiled regular expression used to parse each line and separate the account number and account name. It’s assumed that the account numbers can only contain digits, and the account names cannot begin with a digit. The compiled regular expression is used later to attempt a match on each line in the input file. Lines 20-26:

wb = Workbook()
wsraw = wb.add_sheet('Raw Data')
ws = wb.add_sheet('Account List')
ws.write(0,0,'Account Number')
ws.write(0,1,'Account Name')
ws.col(0).width = len('Account Number') * 256
ws.col(1).width = max([len(l) for l in lines]) * 256

create a new workbook with two worksheets, “Raw Data” and “Account List”, which are used to store the unprocessed and processed data. The headings “Account Number” and “Account Name” are written to the first row of the Account List worksheet. To set the column width, the length of the longest string in each column is multiplied by 256 to calculate the setting. Referring to the documentation for xlwt, you’ll see that the column width setting is based on the width of the 0 character for the first font in the list, not the default font, so you may want to fine tune the calculation to make the column width setting slightly wider than the actual data.

The next section iterates over each stored line from the text file:

for line in lines:
    wsraw.write(r,0,line.strip())
    m = nameandnum.match(line)
    if m:
        ws.write(r,0,int(m.group(1)))
        ws.write(r,1,' '.join([w.capitalize() for w in m.group(2).split()]))
    else:
        ws.write(r,0,99999,easyxf('pattern: pattern solid, fore_colour red;'))
        ws.write(r,1,' '.join([w.capitalize() for w in line.split()]))

The raw data is written to the Raw Data sheet, then the line is matched against the regular expression. If the match is successful, the number part is written to the first column, and the text is written to the second column. The following statement in line 34:

' '.join([w.capitalize() for w in m.group(2).split()])

splits account name information into individual words, capitalizes each word, then reassembles the string. If you wanted to insert the account name string as-is, you would simply replace the statement above with the following:

m.group(2)

If no match occurs, then the string may not contain an account number and it is assumed that the whole string contains the account name only. The dummy account number 99999 is inserted, and the cell background is changed to a red color. This is done to flag the text for quick visual scanning.

After all input lines are processed, the accounts.xls file is written and the following message is printed by the following lines:

wb.save('accounts.xls')
print 'Wrote accounts.xls'

The completed accounts.xls spreadsheet looks like this, with account number and name split into two columns and with a reformatted account name string:

excelfile

Prerequisites

Python (refer to http://www.python.org)

xlwt (refer to http://www.python-excel.org)

Source Files and Scripts

Source for the program and data text file are available at http://github.com/pythonexcels/xlwt/tree/master

References

http://www.python-excel.org contains pointers to the best information available aboutworking with Excel files in the Python programming language.

http://groups.google.com/group/python-excel is the Google group for questions on xlrd, xlwt, xlutils and general questions on interfacing to Excel with Python

Thanks everyone — Dan

Installing Python

Getting started with Python is easy, you just need to download the python installation package and install onto your computer.

Versions

As of this writing, Python 3.1 is the latest version, though I won’t be using the latest bleeding-edge build for my exercises. There’s nothing wrong with 3.1, but third party package support for the packages I use can trail the latest release by a version or two. If you plan on installing third party libraries not provided in the standard Python release, you will want to use a slightly older version. My exercises will be based on the latest Python 2.6.

Which Python foundation should I use?

There are different ways to obtain Python for the Windows platform. Two of the most popular distributions are from the Python foundation and Activestate Software.

Python.org

The site http://www.python.org, maintained by the Python Software Foundation, is the main portal for information on Python. Python.org contains news, documentation, information on the latest releases, download source for current and previous versions of Python, and binary install files for Windows. The latest version, as well as older versions of Python, can be found at http://www.python.org/download. This is the Python foundation that I typically use, the examples that follow will be based on Python 2.6.

ActiveState Python

ActiveState Software provides software solutions for individuals and businesses, including a complete download package containing Python executables and documentation called ActivePython. The package is non open source and available with an OEM license, which can be important in some corporate environments. Please refer tohttp://www.activestate.com/activepython for more information. Python luminary Alex Martelli offers a concise description of Why ActiveState in this StackOverflow post.

Build from Source

Source code for Python is available at http://www.python.org/download, and while it is possible to create a working Python installation for Windows by compiling from source, it’s beyond the scope of these exercises.

Python Modules

One of the wonderful things about Python is the excellent library support for the language. Python is “batteries included”: a large number of libraries are provided out-of-the-box, right in the standard distribution. Other modules are available from a variety of sources, chances are you can find a module that helps you solve a problem with a simple web search. I’ll use a variety of third party modules for the exercises that follow, but you’ll at least need to start with the pywin module. You can find the appropriate pywin for your Python 2.6 installation at http://sourceforge.net/projects/pywin32/files

Resources

I won’t be covering the basics of Python, I suggest you check one of the many other resources. The links listed on http://www.python.org/doc are an excellent starting point, many people also like the Dive into Python book at http://diveintopython.org. You can also find a number of other resources by searching for learn python.

Thanks — Dan

What is Python Excels?

It’s time to give back. And get back. I’ve been Googling your blogs, your wikis, your stackoverflows for years. Grabbing a program here, a snippet there, and using it to build my scripts that do anything from prepping SAP data for an Excel worksheet pivot table, to dumping tables from Oracle DB XE into a spreadsheet, to formatting time card data from a restaurant POS system, to summing times from an Optical Proximity Correction log file, to characterizing random behavior in a Critical Area simulator, to constructing a QA test flow for an automatic scan test pattern generation and insertion tool suite, to dozens of other minor tasks I’ve long forgotten. Thanks for your postings, your hints, your ideas and your links to other helpful pages. I can only hope that a few people find my postings useful. If you like what you see, let me know.

I got into Python a few years back, an unrequited Perl lover who lost patience and faith waiting for Perl 6 to make its appearance. I’ve written hundreds of Perl scripts, and it was my tool of choice for many years. Perl is an amazing, vexing language, still used very very heavily in the Electronic Design Automation industry where I work. For me, I wondered what else was out there, and after taking a sojourn into the world of Ruby, I was able to conquer my ridiculous aversion to white space and arrived in the world of Python.

I’ll be sharing some ideas from the Python programs I’ve written, and talk about what problems were solved. I like to share work that is as complete as possible, so in most cases you should be able to download the script and try it right away for yourself. We won’t be using the latest, most bleeding edge version of Python, because some of the scripts require other modules that may not be available yet in the latest release. Maintaining good Pythonic style is important, and I try my best, but if you spot something in my code that could be done better, leave a comment and share your knowledge.

So sit back and let’s get ready to write some Python!

(This post originally published July 3, 2009)