Write formula to Excel with Python

Question

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

Sample Excel sheet columns:

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

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

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

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

How would you handle this?

Answer 1

fortunately now you can easy do formulas in certain records. Also there are simpler functions to use, such as:

1. wb.sheetnames instead of wb.read_sheet_names()
2. sheet = wb['SHEET_NAME'] instead of sheet = wb.get_sheet_by_name('SHEET_NAME')

And formulas can be easily inserted with:

sheet['A1'] = '=SUM(1+1)'