使用Python将公式写入Excel [英] Write formula to Excel with Python

问题描述

我正在集思广益，如何最好地解决以下问题.任何输入，我们将不胜感激.

I am in the process of brain storming how to best tackle the below problem. Any input is greatly appreciated.

示例Excel工作表列:

Sample Excel sheet columns:

Column A  |  Column B  | Column C
Apple     |  Apple     |
Orange    |  Orange    |
Pear      |  Banana    |

我希望Excel告诉我A列和B列中的项目是否匹配，并在C列中显示结果.我在C列中输入的公式为=IF(A1=B1, "Match", "Mismatch")

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上，我将公式拖动到C列的其余单元格中，以将公式应用于它们，结果将是:

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:

Column A  |  Column B  | Column C
Apple     |  Apple     | Match
Orange    |  Orange    | Match
Pear      |  Banana    | Mismatch

要使用python脚本自动执行此操作，我尝试过:

To automate this using a python script, I tried:

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$1=$B$1, "Match", "Mismatch")
wb.save('test.xlsx')

这会将公式写入C列中的所有单元格，但是该公式仅引用了A1和B1单元格，因此导致C列中的所有单元格=匹配.

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.

Column A  |  Column B  | Column C
Apple     |  Apple     | Match
Orange    |  Orange    | Match
Pear      |  Banana    | Match

您将如何处理?

推荐答案

您可能希望动态创建公式，以便C的每一行都取自A和B的相应行:

You probably want to make the creation of the formula dynamic so each row of C takes from the corresponding rows of A and B:

for i, cellObj in enumerate(Sheet.columns[2], 1):
    cellObj.value = '=IF($A${0}=$B${0}, "Match", "Mismatch")'.format(i)

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