## Interacting with Excel in python

Posted March 08, 2013 at 02:39 PM | categories: programming | tags:

There will be times it is convenient to either read data from Excel, or write data to Excel. This is possible in python (http://www.python-excel.org/). You may also look at (https://bitbucket.org/ericgazoni/openpyxl/wiki/Home).

import xlrd wb = xlrd.open_workbook('data/example.xlsx') sh1 = wb.sheet_by_name(u'Sheet1') print sh1.col_values(0) # column 0 print sh1.col_values(1) # column 1 sh2 = wb.sheet_by_name(u'Sheet2') x = sh2.col_values(0) # column 0 y = sh2.col_values(1) # column 1 import matplotlib.pyplot as plt plt.plot(x, y) plt.savefig('images/excel-1.png')

[u'value', u'function'] [2.0, 3.0]

## 1 Writing Excel workbooks

Writing data to Excel sheets is pretty easy. Note, however, that this overwrites the worksheet if it already exists.

import xlwt import numpy as np x = np.linspace(0, 2) y = np.sqrt(x) # save the data book = xlwt.Workbook() sheet1 = book.add_sheet('Sheet 1') for i in range(len(x)): sheet1.write(i, 0, x[i]) sheet1.write(i, 1, y[i]) book.save('data/example2.xls') # maybe can only write .xls format

## 2 Updating an existing Excel workbook

It turns out you have to make a copy of an existing workbook, modify the copy and then write out the results using the `xlwt`

module.

from xlrd import open_workbook from xlutils.copy import copy rb = open_workbook('data/example2.xls',formatting_info=True) rs = rb.sheet_by_index(0) wb = copy(rb) ws = wb.add_sheet('Sheet 2') ws.write(0, 0, "Appended") wb.save('data/example2.xls')

## 3 Summary

Matlab has better support for interacting with Excel than python does right now. You could get better Excel interaction via COM, but that is Windows specific, and requires you to have Excel installed on your computer. If you only need to read or write data, then xlrd/xlwt or the openpyxl modules will server you well.

Copyright (C) 2013 by John Kitchin. See the License for information about copying.