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'

@PRMoureu is 'Sheet' simple like question link Sheet = wb.get_sheet_by_name('Sheet1') — Mar
– Mar, Commented Jul 16, 2017 at 21:21
can you please add the exact error you got, and the code could help too if you can update your post — PRMoureu
– PRMoureu, Commented Jul 16, 2017 at 21:26

Charlie Clark · Accepted Answer · 2017-07-17 08:46:59Z

7

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.

answered Jul 17, 2017 at 8:46

Charlie Clark

19.7k4 gold badges56 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arthur D. Howland · Accepted Answer · 2019-04-25 20:37:55Z

3

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?

answered Apr 25, 2019 at 20:37

Arthur D. Howland

4,6473 gold badges23 silver badges33 bronze badges

Comments

Spainey · Accepted Answer · 2020-05-13 08:44:02Z

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.

Noone · Accepted Answer · 2021-08-11 17:45:50Z

1

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

wb.sheetnames instead of wb.read_sheet_names()
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)'

answered Aug 11, 2021 at 17:45

Noone

2193 silver badges7 bronze badges

Collectives™ on Stack Overflow

Write formula to Excel with Python error

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related