使用Openpyxl将IF公式插入excel后出现"@“符号

Question

我的目标是使用Openpyxl将这个公式输入到Excel中。

=CORREL(IF(A2:A7;A2:A7);B2:B7)

IF公式的原因是忽略内部包含0的单元格，默认情况下CORREL函数不会忽略这些单元格。

将此公式插入到excel中时，输出实际上是：

=CORREL(IF(@A2:A7;A2:A7);B2:B7)

如果公式输入的单元格与给定范围的同一行(此处为第2-7行)，则此公式仍然有效。但是给出了#值！当放在另一排的时候。手动删除"@“可以解决这个问题，但这是不可行的，因为我想将其用于自动化分析。

我发现，在某些情况下，excel为数组添加了"@“，并将其替换为pre-2010 versions of Excel中的"#This Row”，这解释了为什么在同一行输入该公式时有效。当公式is not recognized.时，openpyxl也会将"@“符号添加到公式中。然而，这两个链接都不能解释为什么在我的示例中添加"@”。

这是在IF函数中使用范围的副作用吗？这是一个将范围解析为IF函数的bug(/feature)吗？

下面是我用来复制错误的代码。我的Openpyxl版本是3.0.5，我使用的是Microsoft 365的Excel。

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
rows = [
    ['Size', 'Batch 1', 'Batch 2'],
    [2, 40, 30],
    [0, 40, 25],
    [0, 50, 30],
    [5, 30, 25],
    [6, 25, 35],
    [7, 20, 40],
]

for row in rows:
    ws.append(row)

formula = '=CORREL(IF(A2:A7,A2:A7),B2:B7)'
ws.cell(row=5, column=5, value=formula) # Formula in row 5 works
ws.cell(row=9, column=5, value=formula) # Formula in row 9 returns #VALUE!
formula = '=CORREL(A2:A7,B2:B7)'
ws.cell(row=6, column=5, value=formula) # CORREL function with ranges works fine
formula = '=IF(A2:A3,A2:A3)'
ws.cell(row=7, column=5, value=formula) # enters "=@IF(@A2:A3;A2:A3)"
formula = '=IF(A2,B2)'
ws.cell(row=8, column=5, value=formula) # enters "=IF(A2;B2)"

wb.save("test.xlsx")

在'test.xlsx‘中，这是我的输出(单元格E5-E9)

-0.9528
-0.9528
#VALUE!
40
#VALUE!

公式如下：

=CORREL(IF(@A2:A7;A2:A7);B2:B7)
=CORREL(A2:A7;B2:B7)
=@IF(@A2:A3;A2:A3)
=IF(A2;B2)
=CORREL(IF(@A2:A7;A2:A7);B2:B7)

Answer 1

在这里回答我自己的问题。感谢JvdV让我走上了正确的道路。

在this answer中，我找到了解决问题的方法。在示例代码中保存excel文件之前，我添加了下面这一行：

ws.formula_attributes['E9'] = {'t': 'array', 'ref': "E9:E9"}

这实际上将单元格'E9‘中的公式设置为读取为数组公式，在Excel中通过将公式放在大括号({})之间来显示。在旧版本的Excel中，这是通过在输入公式时按ctrl-shift-enter组合键来完成的。请注意，在公式字符串中写入这些大括号不起作用。

如果有人知道'ref‘部分是如何工作的，请随时在评论中添加此内容。

Answer 2

通过大量的尝试和错误，我找到了一种使用openpyxl将简单公式输入到单元格的方法。我是通过检查从Excel for Mac保存的电子表格的XML来做到这一点的。.xlsx文档只是一个压缩文件。我在我的文件里找到了这些文件：

docProps/app.xml
docProps/core.xml
xl/theme/theme1.xml
xl/worksheets/sheet1.xml
xl/styles.xml
_rels/.rels
xl/workbook.xml
xl/_rels/workbook.xml.rels
[Content_Types].xml

我打开了“xl/worksheet/sheet1.xml”文件，在保存之前查看了原始XML中正确显示的公式。我在相关的单元格里发现了这个：

<f ca="1">_xlfn.DAYS(C2,TODAY())</f>

“_xlfn”的用法。在openpyxl docs中提到了。

因此，对于openpyxl，我使用此代码在新创建的工作簿中正确输入了所需的公式：

#!/usr/bin/env python3

import openpyxl
import csv
import os

fileCSV  = os.path.expanduser('~/doc/SSL_Cert_Expiry.csv')
fileXLSX = os.path.expanduser('~/doc/SSL_Cert_Expiry.xlsx')

print(f'Using\n\tCSV {fileCSV}\n\tXLSX {fileXLSX}')

wb = openpyxl.Workbook()
ws = wb.active

with open(fileCSV, newline='') as fIn:
  sslReader = csv.reader(fIn, delimiter=',')
  for idx, row in enumerate(sslReader):
    row.append(f'=_xlfn.DAYS(C{idx + 1},TODAY())')
    ws.append(row)

wb.save(fileXLSX)

我在Excel for Mac中打开了它，我的公式显示正确，而不是可怕的！#名称。

脚注:我希望必须为我的公式单元格设置'ca‘属性，但最终这并不是必需的。这可以用eg来完成。

ws.formula_attributes['D2'] = {'ca': '1'}

不显示数组公式的单元格不需要“ref”属性。这可以从this page和它的邻居那里收集到。