使用openpyxl写入SORT(UNIQUE)溢出公式后Excel需恢复的问题

问题原因 #

openpyxl对Excel动态数组公式（如SORT、UNIQUE这类溢出公式）的支持需要明确标记公式类型，直接通过ws.append写入公式字符串时，openpyxl不会自动识别为动态数组公式，导致Excel打开时无法正确解析，触发文件恢复提示。

解决步骤 #

• 升级openpyxl版本：确保使用3.0.10及以上版本，旧版本对动态数组公式的支持存在缺陷。
• 显式标记动态数组公式：不要直接通过ws.append写入公式字符串，而是通过单元格对象设置公式，并指定data_type="dyn"，告诉openpyxl这是动态数组公式。

优化后的代码示例 #

def add_statistics(ws, data_start_row, data_end_row):
    first = ws.max_row + 1
    # 写入SORT(UNIQUE)动态数组公式，指定data_type为dyn
    ws.cell(row=first, column=1, value=f"=SORT(UNIQUE(G{data_start_row}:G{data_end_row}))", data_type="dyn")
    # 用溢出范围引用（#号）让COUNTIF自动生成所有唯一值的计数，无需手动写多行
    ws.cell(row=first, column=2, value=f"=COUNTIF(G{data_start_row}:G{data_end_row}, A{first}#)", data_type="dyn")

额外说明 #

使用A{first}#可以引用A列溢出的所有结果，COUNTIF会自动匹配每个唯一值生成计数，无需手动添加多行公式；手动添加公式时Excel会自动标记为动态数组，所以不会出现解析问题，而openpyxl需要显式指定类型才能让Excel正确识别。

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