python - How to insert array formula in an Excel sheet with openpyxl?

Question

I'm using OpenPyxl to create and modify an Excel sheet. I have the following formula in Excel :

=(SUM(IF(LEFT(Balances!$B$2:$B$100,LEN($B4))=$B4,Balances!$D$2:$D$100)))

This formula which is an "array formula" is working but in order to write it by hand, I have to finish with CTRL+SHIFT+ENTER (because it's an array formula). This transform then the formula as follow :

{=(SUM(IF(LEFT(Balances!$B$2:$B$100,LEN($B4))=$B4,Balances!$D$2:$D$100)))}

I want to be able to write this formula via OpenPyxl with the following code :

    sheet.cell(row=j, column=i).value = '{=(SUM(IF(LEFT(Balances!$B$2:$B$100,LEN($B4))=$B4,Balances!$D$2:$D$100)))}'

However, it doesn't work. OpenPyxl can't manage it. It give me the formula writed but not working.

I could do it with XLSX Writer https://xlsxwriter.readthedocs.io/example_array_formula.html However XLSX writer doesn't work with already created files.

I don't see wich path to follow.

Answer 1

Use the worksheet.formula_attributes to set the array formula. Place the formula in the desired cell, A1 for this example. Then set the formula_attributes to the cell range you want to apply the formula to.

ws["A1"] = "=B4:B8"
ws.formula_attributes['A1'] = {'t': 'array', 'ref': "A1:A5"}