用 Python 在 Excel 中计算公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1116725/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculating formulae in Excel with Python
提问by Sasha
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?
我想使用 Python 在 Excel 中插入一个计算。通常可以通过在相关单元格中插入公式字符串来完成。但是，如果我需要为整列多次计算公式，则必须为每个单独的单元格更新公式。例如，如果我需要计算两个单元格的总和，那么对于单元格 C(k)，计算将是 A(k)+B(k)。在excel中可以计算C1=A1+B1，然后通过从C1向下拖动鼠标来自动扩展计算。我的问题是：是否可以用 Python 做同样的事情，即只在一个单元格中定义一个公式，然后使用 Excel 功能扩展整个列/行的计算？
Thank you in advance, Sasha
提前谢谢你，萨沙
回答by mechanical_meat
回答by rob
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..
如果您使用的是 COM 绑定，那么您可以简单地在 Excel 中记录一个宏，然后将其转换为 Python 代码。
如果您使用的是 xlwt，则必须求助于 Python 中的普通循环。
回答by Greg
Sasha,
萨沙，
Python code translated from your macro would look like this:
从您的宏翻译的 Python 代码如下所示：
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.
没有测试过，但我经常在工作中写这个。如果它不起作用，请告诉我。
回答by engineerchuan
If you want to iterate in the horizontal direction, here is a function I use. 0 -> a, 26 -> aa, 723 -> aav
如果你想在水平方向上迭代，这里是我使用的一个函数。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)
回答by Petr
If you want to iterate in xlwt for columns (in formulas) you can use Utils module from xlwt like this:
如果您想在 xlwt 中迭代列（在公式中），您可以使用 xlwt 中的 Utils 模块，如下所示：
from xlwt import Utils
print Utils.rowcol_pair_to_cellrange(2,2,12,2)
print Utils.rowcol_to_cell(13,2)
>>>
C3:C13
C14
回答by bradbase
Using one of the COM wrapping libraries will allow you to use a User Defined Functions written in Python. This is the domain of xlwingsand PyXll.
使用 COM 包装库之一将允许您使用用 Python 编写的用户定义函数。这是xlwings和PyXll的域。
FlyingKoalaalso 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.
FlyingKoala也这样做，因为它是 xlwings 的扩展。FlyingKoala 增加了（即时）读取 Excel 函数并将其转换为 Python 代码并在 Python 中计算方程的能力。
