Search code examples
pythonexcelformulaopenpyxl

Inserting formula with Blank in excel using openpyxl python

I am trying to insert a formula in excel sheet using openpyxl package. The code is as shown below.

wb = openpyxl.load_workbook('Review.xlsx')
ws = wb.get_sheet_by_name('Final')
for i, cellObj in enumerate(ws['J'],1):
        if i != 1:
            cellObj.value = "=IFERROR(VLOOKUP(F{0},'mapping'!A:B,2,0),"")".format(i)

for i, cellObj in enumerate(ws['K'],1):
        if i != 1:
            cellObj.value = "=J{0}".format(i)

for i, cellObj in enumerate(ws['L'],1):
        if i != 1:
            cellObj.value = "=IFERROR(VLOOKUP(K{0},'mapping_countries'!B:C,2,0),"")".format(i)

wb.save('Review.xlsx')
wb.close()

In formulas, columns J & L , I am intending to place a blank cell when there is an error. But this is not working and instead i am getting 0 in the cell. Could anyone please help achieve this? Thanks a lot!

Solution

  • I think it has to do with ". Try to escape them \" in these lines:

    cellObj.value = "=IFERROR(VLOOKUP(F{0},'mapping'!A:B,2,0),\"\")".format(i)

    cellObj.value = "=IFERROR(VLOOKUP(K{0},'mapping_countries'!B:C,2,0),\"\")".format(i)