Dev Explorer

Reading and Writing to Excel Spreadsheets in Python

Date Published: 28/05/2009 13:13

One of my primary uses of Python in the business environment is to produce reports from various data sources. The humble spreadsheet has become one of the most widely used file formats within business for its flexibility and scalability. Having the ability to produce spreadsheets using an automated system can be useful, as they can be easily integrated into existing business systems. This is possible to achieve using .NET technologies of course and when working on a Windows platform I would advise using the Jet engine. However, I primarily work in a Linux environment where the .NET framework is not available. Here is an introduction to the simple but useful, xlwt (excel write) and xlrd (excel read) modules of Python for writing to, and reading from spreadsheets. This is compatible with Python versions 2.1 to 2.6, is multi-platform and does not require an installation of excel.

Acquiring and Installing the xlwt and xlrd Modules

The xlwt and xlrd modules are not included as part of the standard Python install, therefore you will have to download them and install them yourself. The modules can be found at the following locations.

• XLWT - Excel Write (http://pypi.python.org/pypi/xlwt)
• XLRD - Excel Read (http://pypi.python.org/pypi/xlrd)

If you are using Windows the installation is as simple as downloading the executable and going through the installer. For linux users just download either the .tar.gz or .zip file, extract the contents and run the setup.py file and the setup script will do the rest.

python setup.py install

Creating Excel Spreadsheets with xlwt

Now we have the modules installed we can make a start by creating an excel spreadsheet and putting some data in it. First we must import the xlwt module we have just installed.

import xlwt

Now we can create a new workbook object which we will populate with information. In this example I have set the encoding parameter to utf-8 but this is optional.

book = xlwt.Workbook(encoding="utf-8")

With a workbook object made we can now add some sheets.

sheet1 = book.add_sheet("Python Sheet 1")
sheet2 = book.add_sheet("Python Sheet 2")
sheet3 = book.add_sheet("Python Sheet 3")

We have now formed a workbook object which we can add data to before outputting it to the file system. Adding information to a spreadsheet is simply a case of using the write() function of the sheet objects we created before.

sheet1.write(0, 0, "This is the First Cell of the First Sheet")
sheet2.write(0, 0, "This is the First Cell of the Second Sheet")
sheet3.write(0, 0, "This is the First Cell of the Third Sheet")
sheet2.write(1, 10, "This is written to the Second Sheet")
sheet3.write(0, 2, "This is part of a list of information in the Third Sheet")
sheet3.write(1, 2, "This is part of a list of information in the Third Sheet")
sheet3.write(2, 2, "This is part of a list of information in the Third Sheet")
sheet3.write(3, 2, "This is part of a list of information in the Third Sheet")

After the spreadsheet is formed, the sheets are added and the data is written it is time to commit our new spreadsheet to the file system. To do this we use the book's save() method, passing the path to the file as a parameter. If a file already exists where you intend to save your spreadsheet it will be overwritten.

book.save("python_spreadsheet.xls")

There we have a simple introduction to using Python to create excel spreadsheets. The library lacks support for many standard excel features such as formulas and macros but is very effective for simple tasks. XLWT currently supports up to Excel 2003 with support for Excel 2007 on the way, along with a wave of new functionality. Here is the script above in its entirety to make it easier for you to copy.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import xlwt

book = xlwt.Workbook(encoding="utf-8")

sheet1 = book.add_sheet("Python Sheet 1")
sheet2 = book.add_sheet("Python Sheet 2")
sheet3 = book.add_sheet("Python Sheet 3")

sheet1.write(0, 0, "This is the First Cell of the First Sheet")
sheet2.write(0, 0, "This is the First Cell of the Second Sheet")
sheet3.write(0, 0, "This is the First Cell of the Third Sheet")
sheet2.write(1, 10, "This is written to the Second Sheet")
sheet3.write(0, 2, "This is part of a list of information in the Third Sheet")
sheet3.write(1, 2, "This is part of a list of information in the Third Sheet")
sheet3.write(2, 2, "This is part of a list of information in the Third Sheet")
sheet3.write(3, 2, "This is part of a list of information in the Third Sheet")

book.save("python_spreadsheet.xls")

Reading Excel Spreadsheets with xlrd

Reading spreadsheets is just as easy as writing them but first we must first import the xlrd module we installed earlier.

import xlrd

We must now open a workbook using the open_workbook() method passing the file path as a parameter.

book = xlrd.open_workbook("python_spreadsheet.xls")

In order to access a sheet within the workbook object we have just created, we can use the books sheet_by_name() method passing the name of the sheet as a parameter. If we do not know the names of the sheets in the book we can access the workbook objects sheet_names() method which will return the names of the sheets inside the book as a list. Here is an example which iterates through the sheets of the book object printing out their names to the terminal.

for sheet_name in book.sheet_names():
   print sheet_name

Using the sheet_by_name() method will return a sheet object which we can use to browse the data within that sheet. To access a sheet's contents we can use the sheet object's row_values() method which when passed an index will return a list of the cell values in that row. This list will contain data up until the final cell and any empty cells in between will be empty elements. The below example iterates through each sheet in the workbook and prints out the value of the first cell on each sheet to the terminal (this will error if that cell has no value).

for sheet_name in book.sheet_names():
   sheet = book.sheet_by_name(sheet_name)
   print sheet.row_values(0)[0]

This is just a simple introduction to accessing spreadsheets from Python which covers the basics needed to make a start. Here is the full reading script.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import xlrd

book = xlrd.open_workbook("python_spreadsheet.xls")

for sheet_name in book.sheet_names():
   sheet = book.sheet_by_name(sheet_name)
   print sheet.row_values(0)[0]

Conclusion

Not only is this utility useful for regular tasks such as database reporting but can also be very useful for reducing time on otherwise tedious jobs. I regularly use these modules to read information from spreadsheets into XML files which would otherwise take hours of copying and pasting. I hope this has been useful to someone as there is very little support for this functionality out there on the web, and it can be quite difficult to find a quick simple tutorial to get you going. If you have any problems feel free to comment below and I'll reply as soon as I can.

8 Comments

If you have any suggestions about how this article could be improved or if you want any further information feel free to comment below. No HTML please, your email address will not be displayed and the human check (used to stop spam bots) is not case sensitive.

Name:

Email Address:

Comment:

New Image

Enter the Text Above:

5 Most Recent Articles

Sponsors