Write formula to Excel with Python error

user_7080457 0
Viewed 4015

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'

openpyxl excel python
4 Answers

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?

user_44877820

If you want to start from another row you can either use an if statement to skip the first row, or specify the range in the enumeration. A coded example is below:

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

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

# specifying range, up to max row on worksheet - or you can specify an exact range
for i, cellObj in enumerate(ws['AC2:AC'+str(ws.max_row)],2):
    cellObj[0].value = "=VLOOKUP($A${0}, 'LibrarySheet'!C:D,2,FALSE)".format(i)

The second method requires you to begin the index at 2 and returns a tuple rather than a cell object, so you need to specify cellObj[0].value to return the value of the cell object.

user_109966760

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

user_80000940
Related