The Kitchin Research Group

There may be a time where you have an Excel sheet that already has a model built into it, and you normally change cells in the sheet, and it solves the model. It can be tedious to do that a lot, and we can use python to do that. Python has a COM interface that can communicate with Excel (and many other windows programs. see http://my.safaribooksonline.com/1565926218 for Python Programming on Win32). In this example, we will use a very simple Excel sheet that calculates the volume of a CSTR that runs a zeroth order reaction (\(-r_A = k\)) for a particular conversion. You set the conversion in the cell B1, and the volume is automatically computed in cell B6. We simply need to set the value of B1, and get the value of B6 for a range of different conversion values. In this example, the volume is returned in Liters.

import win32com.client as win32
excel = win32.Dispatch('Excel.Application')

wb = excel.Workbooks.Open('c:/Users/jkitchin/Dropbox/pycse/data/cstr-zeroth-order.xlsx')
ws = wb.Worksheets('Sheet1')

X = [0.1, 0.5, 0.9]
for x in X:
    ws.Range("B1").Value = x
    V = ws.Range("B6").Value
    print 'at X = {0} V = {1:1.2f} L'.format(x, V)
 
# we tell Excel the workbook is saved, even though it is not, so it
# will quit without asking us to save.
excel.ActiveWorkbook.Saved = True 
excel.Application.Quit()

at X = 0.1 V = 22.73 L
at X = 0.5 V = 113.64 L
at X = 0.9 V = 204.55 L

This was a simple example (one that did not actually need Excel at all) that illustrates the feasibility of communicating with Excel via a COM interface.

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

org-mode source

Matlab post

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]

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

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')

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

org-mode source