1

I am currently trying to write a python script to generate a excel workbook template file from a json file. To do this I have been using openpyxl and implementing excel functions in the template for ease of access. I'm not well-versed in excel, and I am using python as a "catalyst of learning." What I'm discovering when I open a generated file is that the functions I input have '@' symbols in certain places.

In python:

from openpyxl import Workbook
wb = Workbook()
sh = wb.active
sh["A2"] = "=IF(ISERR(VLOOKUP(F1,A1:B21,2,FALSE)),”False:Not Contains”,”True: Text Found”)"

(excel function from: https://excelx.com/formula/if-cell-contains-text/#bm4)

In excel:
=@IF(ISERR(VLOOKUP(F1,A1:B21,2,FALSE)),”False:(Not) Contains”,”True: Text Found”)

I believe that this is on the excel side, as when I print the cell in python, I will get the same string that I input.

print(sh["A2"].value)

I was wondering if anyone has had experience with openpyxl to shed some light on how to go about fixing this issue.

efloden
  • 21
  • 4
  • Would it help to put the formula in curly brackets, e.g.: `{=IF(ISERR(VLOOKUP(F1,A1:B21,2,FALSE)),”False:Not Contains”,”True: Text Found”)}`. Most likely the problem is due to you having ExcelO365 where, if not provided as being one, an array formula would suffer from an operation called implicit intersection. That's what the "@" is about. That being said, I don't know enough about openpyxl to tell you if this would fix it. – JvdV Nov 13 '20 at 20:18
  • Maybe [this](https://stackoverflow.com/q/57298554/9758194) link shows you how to add an array formula through openpyxl – JvdV Nov 13 '20 at 20:29
  • Thank you so much, the link is exactly what I needed. The problem is that I guess I don't know what to ask in excel terms. – efloden Nov 13 '20 at 22:38

1 Answers1

1

Following the answer in: How to insert array formula in an Excel sheet with openpyxl? (thank you JvdV)

from openpyxl import Workbook
wb = Workbook()
sh = wb.active
sh["A2"] = '=IF(ISERR(VLOOKUP(F1,A1:B21,2,FALSE)),”False:Not Contains”,”True: Text Found”)'
sh.formula_attributes["A2"] = {"t": "array", "ref": "A2:A2"}

Where 't' represents type, and 'ref' is the array of cells to write the formula onto. However, I was not able to find any resources related to:

openpyxl.worksheet.worksheet.Worksheet.formula_attributes
efloden
  • 21
  • 4