Write formula to Excel with Python error

Question

I try to follow this question to add some formula in my excel using python and openpyxl package.

That link is what i need for my task.

but in this code :

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

i take an error at Sheet.columns[2] any idea why ? i follow the complete code.

i have python 2.7.13 version if that helps for this error.

****UPDATE****

COMPLETE CODE :

import openpyxl
wb = openpyxl.load_workbook('test1.xlsx')
print wb.get_sheet_names()
Sheet = wb.worksheets[0]
for i, cellObj in enumerate(Sheet.columns[2], 1):
    cellObj.value = '=IF($A${0}=$B${0}, "Match", "Mismatch")'.format(i)

error message :

for i, cellObj in enumerate(Sheet.columns[2], 1):

TypeError: 'generator' object has no attribute 'getitem'

Answer 1

For other Stack adventurers looking to copy/paste a formula:

# Writing from pandas back to an existing EXCEL workbook

wb = load_workbook(filename=myfilename, read_only=False, keep_vba=True)
ws = wb['Mysheetname']

# Paste a formula Vlookup!  Look at column A, put result in column AC.

for i, cellObj in enumerate(ws['AC'], 1):
    cellObj.value = "=VLOOKUP($A${0}, 'LibrarySheet'!C:D,2,FALSE)".format(i)

One issue, I have a header and the formula overwrites it. Anyone know how to start from row 2?

Answer 2

ws.columns and ws.rows are properties that return generators. But openpyxl also supports slicing and indexing for rows and columns

So, ws['C'] will give a list of the cells in the third column.