Calculating formulae in Excel with Python

Question

Hi,

I would like to insert a calculation in Excel using Python. Generally it can be done by inserting a formula string into the relevant cell. However, if i need to calculate a formula multiple times for the whole column the formula must be updated for each individual cell. For example, if i need to calculate the sum of two cells, then for cell C(k) the computation would be A(k)+B(k). In excel it is possible to calculate C1=A1+B1 and then automatically expand the calculation by dragging the mouse from C1 downwards. My question is: Is it possible to the same thing with Python, i.e. to define a formula in only one cell and then to use Excel capabilities to extend the calculation for the whole column/row?

Thank you in advance, Sasha

Answer 1

If you are using COM bindings, then you can simply record a macro in Excel, then translate it into Python code.
If you are using xlwt, you have to resort to normal loops in python..

Answer 2

Is it mandatory to be Excel? Because I know gnumeric has a Python scripting built-in that is a no brainer to use...

Answer 3

As Roberto mentions, you can use xlwt and a trusty for-loop:

import xlwt

w = xlwt.Workbook()
ws = w.add_sheet('mysheet')

for i in range(10):
    ws.write(i, 0, i)
    ws.write(i, 1, i+1)
    ws.write(i, 2, Formula("$A$%d+$B$%d" % (i+1, i+1)))

w.save('myworkbook.xls')

Answer 4

Sasha,

Python code translated from your macro would look like this:

startCell = mySheet.Range("M6")
wholeRange = mySheet.Range("M6:M592")
startCell.FormulaR1C1 = "=R[-1]C[-7]/RC[-10]*R[-1]C"
startCell.AutoFill(Destination=wholeRange)

Haven't tested it, but I write this often at work. Let me know if it doesn't work.