I'm currently working on a Python script that utilizes OpenPyXL to write formulas to a .xlsx file. The specific formula I'm using involves the TEXTJOIN and INDEX functions. However, upon opening the saved .xlsx file in Excel, I noticed that an '@' symbol is automatically inserted before the INDEX function in the formula. This unexpected '@' symbol breaks the formula, and the formula only works as expected when I manually remove the '@' symbol.
Here's the code snippet I'm using to write the formula to the .xlsx file:
formula = '=_xlfn.TEXTJOIN(" + ", TRUE, IF(INDEX(Products!$B:$G, MATCH($A2, Products!$A:$A, 0), 0)=1, Products!$B$1:$G$1, ""))'
ws.cell(row=1, column=2, value=formula)
When I open the saved .xlsx file in Excel, the formula appears like this:
=TEXTJOIN(" + ", TRUE, IF(@INDEX(Products!$B:$G,
MATCH($A2, Products!$A:$A, 0), 0)=1, Products!$B$1:$G$1, ""))
As you can see, the '@' symbol is causing the issue with the INDEX function in the formula.
I have already added the '_xlfn' prefix to the TEXTJOIN function to ensure it works properly, but I can't figure out why the '@' symbol is being inserted before the INDEX function. Could someone please explain why this is happening and suggest a solution to prevent the '@' symbol from being added to the formula?
Any insights or help would be greatly appreciated!
I've found a solution to address this problem.
When dealing with array formulas in Excel using the openpyxl library, it's necessary to utilize the ArrayFormula class to properly handle them. Regular formulas are represented as strings, but array formulas require a more specific structure.
Here's an example:
formula = '=_xlfn.TEXTJOIN(" + ", TRUE, IF(INDEX(Products!$B:$G, MATCH($A2, Products!$A:$A, 0), 0)=1, Products!$B$1:$G$1, ""))'
ws.cell(row=1, column=1).value = ArrayFormula(ref="A1", text=formula)
By employing this approach, the formula in Excel becomes an array formula, and the unexpected insertion of '@' is avoided.