使用pandas保存DataFrame至Excel时的公式与编码问题

问题①：用xlsxwriter保存DataFrame时去除公式前的@，让IMAGE()正常工作 #

xlsxwriter自动给公式加@是因为默认启用了Excel 365的动态数组公式特性，要生成可正常执行的普通公式，需绕过pandas的默认写入逻辑，直接用xlsxwriter的write_formula方法手动写入：

1. 先将DataFrame中的公式列替换为占位值（比如空字符串），避免pandas自动处理公式
2. 导出DataFrame后，获取对应的worksheet对象
3. 遍历目标行，用write_formula写入公式，指定no_array=True禁用动态数组；针对本地语言公式，额外设置formula_local=True来支持分号分隔参数

示例代码：

import pandas as pd

# 先把公式列设为空占位符
for col in ["thumbnailHyperlink_en", "thumbnailHyperlink_locale"]:
    channel_videos_data_dict[col] = [""] * len(channel_videos_data_dict["video_id"])

df = pd.DataFrame(channel_videos_data_dict)

writer = pd.ExcelWriter("data.xlsx", engine="xlsxwriter")
df.to_excel(writer, index=False)
workbook = writer.book
worksheet = writer.sheets["Sheet1"]

# 遍历行写入公式
for row_num in range(1, len(df)+1):  # 跳过表头行
    thumbnail_url = channel_videos_data_dict["thumbnail_url"][row_num-1]
    # 写入英文IMAGE公式，禁用动态数组
    worksheet.write_formula(
        row_num, df.columns.get_loc("thumbnailHyperlink_en"),
        fr'IMAGE("{thumbnail_url}",,1)',
        options={"no_array": True}
    )
    
    # 写入本地语言OBRAZ公式，支持分号分隔参数
    worksheet.write_formula(
        row_num, df.columns.get_loc("thumbnailHyperlink_locale"),
        fr'OBRAZ("{thumbnail_url}"; ;1)',
        options={"no_array": True, "formula_local": True}
    )

writer.save()
writer.close()

这样写入的公式不会带@，本地语言公式用分号分隔也不会损坏工作簿。

问题②：CSV保存时解决编码问题，将开头带-的视频ID设为纯文本 #

编码问题解决 #

windows-1250编码不支持emoji等特殊Unicode字符，改用utf-8-sig编码保存CSV，Excel能正确识别UTF-8编码并兼容特殊字符。

开头带-的视频ID设为纯文本 #

在视频ID前添加单引号'，Excel打开时会自动识别为纯文本，不会误判为公式。

超链接替代方案 #

CSV本身不支持格式，但可以用HYPERLINK公式实现超链接，替换为本地语言对应的公式名即可。

示例代码：

import pandas as pd

df = pd.DataFrame(channel_videos_data_dict)

# 处理开头带'-'的视频ID，添加单引号前缀
df["video_id"] = df["video_id"].apply(lambda x: f"'{x}" if x.startswith("-") else x)

# 生成本地语言超链接公式（示例用分号分隔参数）
df["video_link"] = df.apply(
    lambda row: f'=HYPERLINK("{row["video_url"]}"; "{row["video_title"]}")',
    axis=1
)

# 用utf-8-sig编码保存
df.to_csv("data.csv", index=False, encoding="utf-8-sig")

这样保存的CSV：

• 不会出现emoji编码错误
• 开头带-的视频ID会被识别为纯文本
• 超链接公式可正常工作

内容的提问来源于stack exchange，提问作者Soren V. Raben