使用Python在Excel中计算公式 [英] Calculating formulae in Excel with Python

问题描述

我想使用Python在Excel中插入一个计算. 通常，可以通过将公式字符串插入相关单元格来完成此操作. 但是，如果我需要为整个列多次计算公式 必须为每个单独的单元格更新公式.例如，如果我需要 计算两个像元的总和，则对于像元C(k)，计算将为A(k)+ B(k). 在excel中，可以计算C1 = A1 + B1，然后自动展开 从C1向下拖动鼠标进行计算. 我的问题是:使用Python是否有可能实现同一目标，即仅在一个单元格中定义一个公式，然后使用Excel功能扩展整个列/行的计算?

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?

预先感谢您， 萨沙

推荐答案

为 xlwt 和可信赖的for循环:

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

这篇关于使用Python在Excel中计算公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！