使用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替换为“[#此行]”，这就解释了为什么在同一行中输入公式时会起作用。 当公式is not recognized.时，openpyxl还会将“@”符号添加到公式中 但是，这两个链接都不能解释为什么在我的示例中添加“@”

这是在IF函数中使用范围的副作用吗？这是将范围解析为IF函数的错误（/特性）吗

下面是我用来复制错误的代码。我的Openpyxl版本是3.0.5，我使用微软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让我走上了正确的道路

在{a2}答案中，我找到了解决我问题的方法。在示例代码中保存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文档只是一个zip文件。我在我的文件中找到了这些文件：

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/worksheets/sheet1.xml”文件，查看原始xml，查找在保存之前正确显示的公式。我在相关单元格中找到了：

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

在openpyxl docs中提到了“xlfn.”的用法

因此，对于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及其邻国那里得到