使用 Python 将公式写入 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39195957/
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
Write formula to Excel with Python
提问by spiderlily
I am in the process of brain storming how to best tackle the below problem. Any input is greatly appreciated.
我正在集思广益如何最好地解决以下问题。非常感谢任何输入。
Sample Excel sheet columns:
示例 Excel 工作表列：
Column A | Column B | Column C
Apple | Apple |
Orange | Orange |
Pear | Banana |
I want Excel to tell me whether items in column A and B match or mismatch and display results in column C. The formula I enter in column C would be =IF(A1=B1, "Match", "Mismatch")
我想让 Excel 告诉我 A 列和 B 列中的项目是匹配还是不匹配，并在 C 列中显示结果。我在 C 列中输入的公式是 =IF(A1=B1, "Match", "Mismatch")
On excel, I would just drag the formula to the rest of the cells in column C to apply the formula to them and the result would be:
在 excel 上，我只需将公式拖到 C 列中的其余单元格即可将公式应用于它们，结果将是：
Column A | Column B | Column C
Apple | Apple | Match
Orange | Orange | Match
Pear | Banana | Mismatch
To automate this using a python script, I tried:
为了使用 python 脚本自动执行此操作，我尝试过：
import openpyxl
wb = openpyxl.load_workbook('test.xlsx')
Sheet = wb.get_sheet_by_name('Sheet1')
for cellObj in Sheet.columns[2]:
cellObj.value = '=IF($A=$B, "Match", "Mismatch")
wb.save('test.xlsx')
This wrote the formula to all cells in column C, however the formula only referenced cell A1 and B1, so result in all cells in column C = Match.
这将公式写入 C 列中的所有单元格，但是该公式仅引用了单元格 A1 和 B1，因此导致 C 列中的所有单元格 = 匹配。
Column A | Column B | Column C
Apple | Apple | Match
Orange | Orange | Match
Pear | Banana | Match
How would you handle this?
你会如何处理这件事？
回答by Moses Koledoye
You probably want to make the creation of the formula dynamic so each row of Ctakes from the corresponding rows of Aand B:
你可能想以制动态的创建因此每排C由相应行需要A和B：
for i, cellObj in enumerate(Sheet.columns[2], 1):
cellObj.value = '=IF($A##代码##=$B##代码##, "Match", "Mismatch")'.format(i)
