Putting formula into excel sheet using openpyxl in python

Question

A while back I got some help with putting formulas into a spreadsheet using xlsxwriter with this: Adding formulas to excel spreadsheet using python

I needed to move to use openpyxl and am having a problem doing the same and filling in a column while incrementing the row. If I try:

for i, cellObj in enumerate(ws3['AC2:AC'+str(ws3.max_row)],2):
    cellObj[0].value = "=VLOOKUP(B${0},'SheetName'!$B$2:$R$2199,17,FALSE)".format(i)

when I run the formulas are not showing in the Excel file.

I tried the example given earlier with the same result, no formula is entered into the sheet.

import openpyxl
wb = openpyxl.load_workbook('testy.xlsx')
Sheet = wb.get_sheet_by_name('Sheet1')

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

Any pointer/s would be much appreciated.

Mike67 Mike67 · Accepted Answer · 2020-08-12T17:12:35

This code worked for me, though excel gave a circular reference warning when I opened the document. Note that I had to set the sheet name to Sheet1 in the formula.

import openpyxl
wb = openpyxl.load_workbook('test.xlsx')

print("\nAll Sheets:", wb.sheetnames, end='\n\n')

Sheet = wb['Sheet1']

for i, cellObj in enumerate(list(Sheet.columns)[1], 1):
    f = "=VLOOKUP(B${0},'Sheet1'!$B$2:$R$2199,17,FALSE)".format(i)
    print(f)
    cellObj.value = f
wb.save('testy.xlsx')

Putting formula into excel sheet using openpyxl in python

1 Answers