如何阻止openpyxl在数组公式单元格中自动插入@符号？

你用openpyxl编写数组公式后，打开生成的Excel文件，发现公式被自动加上了@符号，移除后公式能正常计算。这是因为Excel的动态数组功能会自动给非数组格式的公式添加@（兼容性运算符），而直接通过cell.value设置数组公式时，openpyxl没有标记它为数组公式，导致Excel识别异常。

原代码示例 #

import openpyxl as xl

test    = xl.Workbook()
sheet   = test.active
sheet.title = 'Test'

year = 2014
pct  = .02
for col in range(1, 12):
    sheet.cell(row = 1, column = col).value = year
    sheet.cell(row = 2, column = col).value = pct
    sheet.cell(row = 2, column = col).number_format = '0.00%'
    if col % 2 == 1:
        pct += .01
    if col % 2 == 0:
        year += 1

for row in reversed(range(4, year - 2009)):
    sheet.cell(row = row, column = 1).value = year
    # 原写法会导致Excel添加@符号
    sheet.cell(row = row, column = 2).value = '=PRODUCT(1+TRANSPOSE($A$1:$K$1=$A' + str(row) + ')*TRANSPOSE($A$2:$K$2))-1'
    sheet.cell(row = row, column = 2).number_format = '0.00%'
    year -= 1

test.save('test.xlsx')

解决方法 #

在openpyxl中，要写入数组公式，不能直接使用cell.value赋值，而是要使用cell.array_formula属性。这个属性会告诉openpyxl将公式标记为数组公式，Excel打开时就不会自动添加@符号。

修改代码中设置公式的那一行：

# 替换原有的value赋值为array_formula
sheet.cell(row = row, column = 2).array_formula = '=PRODUCT(1+TRANSPOSE($A$1:$K$1=$A' + str(row) + ')*TRANSPOSE($A$2:$K$2))-1'

修改后的完整代码 #

import openpyxl as xl

test    = xl.Workbook()
sheet   = test.active
sheet.title = 'Test'

year = 2014
pct  = .02
for col in range(1, 12):
    sheet.cell(row = 1, column = col).value = year
    sheet.cell(row = 2, column = col).value = pct
    sheet.cell(row = 2, column = col).number_format = '0.00%'
    if col % 2 == 1:
        pct += .01
    if col % 2 == 0:
        year += 1

for row in reversed(range(4, year - 2009)):
    sheet.cell(row = row, column = 1).value = year
    sheet.cell(row = row, column = 2).array_formula = '=PRODUCT(1+TRANSPOSE($A$1:$K$1=$A' + str(row) + ')*TRANSPOSE($A$2:$K$2))-1'
    sheet.cell(row = row, column = 2).number_format = '0.00%'
    year -= 1

test.save('test.xlsx')

这样修改后，生成的Excel文件中公式会被正确识别为数组公式，不会自动添加@符号，打开即可正常计算。

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