如何使用openpyxl在Excel工作表中插入数组公式？

Question

我正在使用OpenPyxl创建和修改Excel表格。 我在Excel中有以下公式：

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

这个公式是一个“数组公式”，它可以正常工作，但如果要手动编写，必须以CTRL+SHIFT+ENTER结尾（因为它是一个数组公式）。这将把公式转换为以下形式：

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

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

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

然而，它不起作用。OpenPyxl 无法处理它。它给我写好的公式，但不能工作。

我可以使用 XLSX Writer https://xlsxwriter.readthedocs.io/example_array_formula.html 来实现。

然而，XLSX Writer 无法处理已经创建的文件。

我不知道该走哪条路。

- Alex Dana

---

我已经看过这篇文章，但不知道如何使其工作。 https://stackoverflow.com/questions/52488989/keep-the-nature-of-array-formulas-when-using-openpyxl - Alex Dana

Answer 1

使用 worksheet.formula_attributes 来设置数组公式。将公式放置在所需单元格中，本示例中为 A1 单元格。然后将 formula_attributes 设置为要应用公式的单元格范围。

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

- ConSod

---

请问一下，ref属性是如何工作的呢？ 我举了一个例子，比如公式 ws['D1'] ="=SUM(A1:A5*B1:B5)"，然后 ws.formula_attributes['D1'] = {'t': 'array', 'ref': "D1:D1"}。这样可以运行，但是我是否总是可以将ref设置为单元格的坐标？ - Vertex

我在这里找到了ref属性的描述链接：“公式适用的单元格范围[...]”，但这并没有对我有太大帮助 :) - Vertex

Answer 2

如果上述解决方案不起作用，请检查您在公式中是否使用了英文函数名称。

在我的情况下，我一直在使用捷克语函数名称，尽管如果手动插入公式，则公式可以工作，但是如果通过openpyxl插入，则无法工作。

切换到英文函数名称解决了这个问题！

- Sukondeez Nats

Answer 3

更新：版本3.1增加了对传统样式数组的支持（尚未支持动态数组）。请参阅Openpyxl文档。

这样就不再需要使用。

ws.formula_attributes

你仍然需要知道结果集的大小。如果你正在使用过滤器，你需要添加一个前缀，就像这样。

formula=formula.replace('FILTER','_xlfn._xlws.FILTER')

---

原始评论：

在我的情况下，公式在汇总之前使用数组进行中间结果计算，然后使用MAX函数。当手动输入公式时，公式可以正常工作，但是通过openpyxl插入时却无法正常工作。Excel的Office 365版本错误地插入了新的隐式交集运算符“@”。

formula: ="Y" & MAX(tbl_mcare_opt[Year]*(tbl_mcare_opt[Who]=[@Who])*(tbl_mcare_opt[Year]<=intyear(this_col_name())))

原来需要设置的属性如上所述。这样Excel才能正确解释公式。在我的情况下，ref实际上只是单个单元格地址。

我能确定该公式使用了动态数组和正则表达式。如果是这样，我就添加了公式属性。

        # provision for dynamic arrays to be included in formulas - notify excel
        if is_formula(values[cn]):
          regex_column=r'[A-Za-z_]+(\[\[?[ A-Za-z0-9]+\]?\])'
          pattern=re.compile(regex_column)
          matches=pattern.findall(values[cn]) 
          if len(matches): # looks like a dynamic formula
            address=get_column_letter(cix)+str(rix)
            ws.formula_attributes[address]={'t':'array','ref': address}

- Cronical