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.exampl e. ''' 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/