Python Pandas ExcelWriter 切换动态公式为数组公式

Question

背景

我从数据库查询了一些数据，使用 pd.dataframe 处理这些数据，然后通过 pd.ExcelWriter 将它们保存到一个格式化的 xlsx 模板中。保存的数据在另一个工作表中通过动态公式和其他公式进行引用。

问题

在这个 Excel 文件中，有两个工作表：“原始数据”和“格式化数据”：
“原始数据”存储了提取的数据，而“格式化数据”包含了来自“原始数据”的公式和引用单元格。

在 ExcelWriter 处理完这个 Excel 文件后，“格式化数据”中的动态公式变成了数组公式，这导致公式无法扩展。例如，有一个公式：
=FILTER(UNIQUE('Raw Data'!$I$4:$I$2000),UNIQUE('Raw Data'!$I$4:$I$2000)<>0)
这个公式应该返回 I4:I2000 范围内的所有唯一数据。

然而，在 ExcelWriter 处理后，动态公式变成了数组公式，范围变成了 I4:I400，因为原始模板中只有 396 个唯一值。即使更新的数据源有超过 396 个唯一值，它也只能显示 396 项。

如果有人能告诉我这个问题的原因，并提供一个解决方案来防止动态公式的变化，那就太好了。

备注：我尽量不使用 Python 进行数据处理，所以请不要对此发表评论。

代码片段供参考

writer = pd.ExcelWriter(excel_path, engine='openpyxl', mode='a', if_sheet_exists="overlay",)
    for config in excelUpdateConfigs:
        result = fetchSQL(db_conn, config["sql"])
        result = result.astype(config["dtype"])
        result.to_excel(writer, sheet_name="Raw Data", float_format="%.5f", startrow=2, startcol=config["startcol"], header=True, index=False, engine="io.excel.xlsx.writer")
writer.close()

我尝试过的

我在网上做了一些研究，但目前找不到关于动态公式的相关文章。
我怀疑 ExcelWriter 的底层逻辑是基于给定的路径创建一个新的 Excel 文件，而不是在原文件上进行编辑，这样就破坏了一些公式和插入的项目。如果我错了，请指正。

Answer 1

我试着用下面的代码来复现你的问题：

import pandas as pd
import numpy as np

excel_path = 'test.xlsx'

data = np.random.uniform(low=-10, high=10, size=400)

result = pd.DataFrame(data, columns=['close'])

writer = pd.ExcelWriter(excel_path, engine='openpyxl', mode='a', if_sheet_exists="overlay",)
result.to_excel(writer, sheet_name="Raw Data", float_format="%.5f", startrow=2, startcol=8, header=True, index=False, engine="io.excel.xlsx.writer")
writer.close()

test.xlsx这个文件里有两个标签页：“原始数据”和“格式数据”。在“格式数据”的A1单元格里，我输入了下面的内容：

=FILTER(UNIQUE('Raw Data'!$I$4:$I$2000),UNIQUE('Raw Data'!$I$4:$I$2000)<>0)

运行代码后，我发现过滤函数变成了这样：

所以我决定先改变在“格式数据”表中应用FILTER的方式，也就是在“格式数据”中选择A1:A2000，在公式栏输入=FILTER(UNIQUE('Raw Data'!$I$4:$I$2000),UNIQUE('Raw Data'!$I$4:$I$2000)<>0)，然后按下Ctrl + Shift + Enter。

这样做的话，openpyxl就不会改变它，并且它会始终适应2000个数据点。