Openpyxl: How to remove the '@' symbol appearing in excel formulae when using Python

user_1068879 0
Viewed 38

I'm trying to use a formula in Excel using Python and the OpenPyXL library. The aim is to output the following into an excel cell.

=SMALL(IF(G4:BI4>BJ4,G4:BI4),1)

However, when I output the above in excel, the end result has an '@' symbol.

=SMALL(IF(@G4:BI4>BJ4,G4:BI4),1)

The code I'm using is as follows:

col_num += 1
cell = worksheet.cell(row=row_num, column=col_num)
cell.value = f"=SMALL(IF({var_first}:{var_last}>{lowest_offer_cell},{var_first}:{var_last}),1)"

How do I get rid of the '@' symbol and get the correct output?

openpyxl excel-formula python
1 Answers

Answering my own question based on suggestions from this answer.

I added one line to my code.

col_num += 1
cell = worksheet.cell(row=row_num, column=col_num)
cell.value = f"=SMALL(IF({var_first}:{var_last}>{lowest_offer_cell},{var_first}:{var_last}),1)"
worksheet.formula_attributes[cell.coordinate] = {'t': 'array', 'ref': f"{cell.coordinate}:{cell.coordinate}"}

I set the worksheet.formula_attributes to an array formula, then set the ref to the cell's coordinate. Now when I generate the Excel file, the '@' symbol is not included.

user_10688790
Related