如何用openpyxl设置Excel 365动态数组公式且无需预知返回数组大小

openpyxl原生解决方案 #

你不需要提前预知自定义UDF返回数组的尺寸，只需配置公式属性时按以下规则设置即可：

• ref字段仅填写公式所在的单个单元格地址
• 新增o字段，值设为"1"，该字段为Excel 365动态溢出特性的专属标记，告诉Excel无需限制数组返回范围，自动根据计算结果调整溢出区域
  示例代码如下：

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 自定义公式写入A1单元格
target_cell = ws["A1"]
target_cell.value = "=_xldudf_GETVALUES(<some input>)"
# 配置动态数组公式属性
target_cell.formula_attributes = {
    "t": "array",
    "o": "1",
    "ref": "A1" # 仅填写公式所在单元格即可，无需填写完整数组范围
}

wb.save("dynamic_array_test.xlsx")

按上述配置生成的文件在Excel 365中打开后，公式不会出现@前缀，会自动根据UDF返回的数组大小完成溢出展示。

备选方案：使用xlwings #

如果因openpyxl版本过低等原因上述方案不生效，可以使用xlwings实现需求，该库原生适配Excel 365的动态数组特性，无需手动配置公式属性：

import xlwings as xw

# 新建工作簿
wb = xw.Book()
ws = wb.sheets.active

# 直接写入公式即可，自动适配动态溢出逻辑
ws["A1"].formula = "=GETVALUES(<some input>)"

wb.save("dynamic_array_test.xlsx")
wb.close()

注意：xlwings需要运行环境中安装有Excel程序才可正常使用。

内容的提问来源于stack exchange，提问作者mlin