如何使用openpyxl在Excel工作表中插入数组公式? [英] How to insert array formula in an Excel sheet with openpyxl?

问题描述

我正在使用OpenPyxl创建和修改Excel工作表.我在Excel中具有以下公式:

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)))

这个公式是一个数组公式"，但是要手工编写，我必须先按CTRL + SHIFT + ENTER(因为它是一个数组公式).然后进行如下转换:

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

我希望能够使用以下代码通过OpenPyxl编写此公式:

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)))}'

但是，它不起作用.OpenPyxl无法管理它.它给了我编写但无法使用的公式.

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

我可以使用XLSX Writer做到这一点 https://xlsxwriter.readthedocs.io/example_array_formula.html 但是XLSX编写器不适用于已创建的文件.

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.

我看不到要走的路.

推荐答案

使用 worksheet.formula_attributes 设置数组公式.将公式放在所需的单元格中，在此示例中为A1.然后将 formula_attributes 设置为您要将公式应用到的单元格区域.

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

这篇关于如何使用openpyxl在Excel工作表中插入数组公式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！