Read & Write Excel files with Python

Read Excel files from Python

Published on April 9, 2008 in Python. 31Comments Tags: excel, linux, xlrd.

Use the excellent xlrd package, which works on any platform. That means you can read Excel files from Python in Linux! Example usage:

Open the workbook

import xlrd wb = xlrd.open_workbook( 'myworkbook.xls' )

Check the sheet names

wb.sheet_names()

Get the first sheet either by index or by name

sh = wb.sheet_by_index( 0 ) sh = wb.sheet_by_name(u 'Sheet1' )

Iterate through rows, returning each as a list that you can index:

for rownum in range (sh.nrows):      print sh.row_values(rownum)

If you just want the first column:

first_column = sh.col_values( 0 )

Index individual cells:

cell_A1 = sh.cell( 0 , 0 ).value cell_C4 = sh.cell(rowx = 3 ,colx = 2 ).value

(Note Python indices start at zero but Excel starts at one)

Turns out the put_cell() method isn’t supported, so ignore the following section (Thanks for the heads up, John!)

Write Excel files with Python using xlwt

Published on September 20, 2009 in excel,other software and Python. 8Comments

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 asweather.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.