Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
764 views
in Technique[技术] by (71.8m points)

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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"}

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...