Calculating formulae in Excel with Python

Question

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

Some sample code would be useful. What module are you using? what os are you on? — Paul
– Paul, Commented Jul 12, 2009 at 19:46
I work with windows, excel 2003, some package called easyExcel - it is just a number of useful functions implemented with COM — Sasha
– Sasha, Commented Jul 12, 2009 at 20:05

Community · Accepted Answer · 2017-05-23 12:30:27Z

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, xlwt.Formula("$A$%d+$B$%d" % (i+1, i+1)))

w.save('myworkbook.xls')

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Jul 12, 2009 at 20:19

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sasha Over a year ago

Of course it is possible to that, but my question was how to do it using AutoFill capability of Excel...

engineerchuan Over a year ago

def _num_to_let(num): if num > 25: return _num_to_let(num/26-1) + chr(97+ num % 26) return chr(97+num)

rob · Accepted Answer · 2009-07-12 20:02:31Z

0

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

answered Jul 12, 2009 at 20:02

rob

37.9k2 gold badges60 silver badges67 bronze badges

1 Comment

Sasha Over a year ago

I am using COM, recorded macro, but I don't know how to translate the following: ActiveCell.FormulaR1C1 = "=R[-1]C[-7]/RC[-10]*R[-1]C" Range("M6").Select Selection.AutoFill Destination:=Range("M6:M592") Range("M6:M592").Select

Greg · Accepted Answer · 2009-07-27 03:32:12Z

0

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.

edited Jul 27, 2009 at 3:32

answered Jul 26, 2009 at 2:48

Greg

2,6254 gold badges23 silver badges28 bronze badges

2 Comments

John Machin Over a year ago

@Greg: please edit your response so that the code shows up as 4 lines not 1 line ... hint: start each line with 4 spaces.

Greg Over a year ago

Hmm, I thought I did... OK, fixed it.

engineerchuan · Accepted Answer · 2011-03-29 22:57:54Z

0

If you want to iterate in the horizontal direction, here is a function I use. 0 -> a, 26 -> aa, 723 -> aav

def _num_to_let(num):
        if num > 25:
            return _num_to_let(num/26-1) + chr(97+ num % 26)
        return chr(97+num)

answered Mar 29, 2011 at 22:57

engineerchuan

2,7085 gold badges24 silver badges22 bronze badges

Comments

Petr · Accepted Answer · 2016-04-02 22:24:16Z

0

If you want to iterate in xlwt for columns (in formulas) you can use Utils module from xlwt like this:

from xlwt import Utils
print Utils.rowcol_pair_to_cellrange(2,2,12,2)
print Utils.rowcol_to_cell(13,2)
>>>
C3:C13
C14

answered Apr 2, 2016 at 22:24

Petr

11 bronze badge

Comments

bradbase · Accepted Answer · 2020-05-04 07:28:17Z

0

Using one of the COM wrapping libraries will allow you to use a User Defined Functions written in Python. This is the domain of xlwings and PyXll.

FlyingKoala also does this as it's an extension of xlwings. FlyingKoala has added ability to (on-the-fly) read Excel functions and convert them into Python code and evaluate the equation in Python.

answered May 4, 2020 at 7:28

bradbase

5196 silver badges11 bronze badges

Collectives™ on Stack Overflow

Calculating formulae in Excel with Python

6 Answers 6

2 Comments

1 Comment

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

1 Comment

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related