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.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…