如何优化含垂直制表符的Pandas DataFrame保存至Excel的程序？

我开发的数据库转Excel程序此前运行稳定，但近期发现当文本包含垂直制表符（ASCII十六进制0B，对应转义字符\v）时会出现异常，程序无法正常生成Excel文件。已知可以通过遍历每个字符串列替换垂直制表符解决，但希望找到更简便的方案——是否能通过为pd.ExcelWriter或df.to_excel添加参数的方式快速修复？

环境信息：Python 3.11，pandas 1.5.3，openpyxl 3.1.2

示例复现代码：

import pandas as pd

df1 = pd.DataFrame(data=dict(a=["x", "y\vz"], b=[1, 2]))
df2 = pd.DataFrame(data=dict(a=["x\vxx", "yz"], b=[11, 22]))

writer = pd.ExcelWriter("output.xlsx", engine='openpyxl')
for sheet_name, df in (("first", df1), ("second", df2)):
    df.to_excel(writer, index=False, sheet_name=sheet_name)
writer.close()

解决方案说明 #

目前pandas和openpyxl均未提供直接过滤垂直制表符的参数化选项，无法通过修改pd.ExcelWriter或df.to_excel的参数直接解决问题。下面是几种低改动、高健壮性的替代方案：

1. 批量清理DataFrame中的垂直制表符（最简便）

通过遍历字符串列批量替换\v，代码量小且对原流程改动极少：

import pandas as pd

df1 = pd.DataFrame(data=dict(a=["x", "y\vz"], b=[1, 2]))
df2 = pd.DataFrame(data=dict(a=["x\vxx", "yz"], b=[11, 22]))

# 筛选所有字符串类型列，批量替换垂直制表符
def clean_control_chars(df):
    str_cols = df.select_dtypes(include=['object']).columns
    df[str_cols] = df[str_cols].replace('\v', '', regex=True)
    # 可选：扩展处理其他可能导致Excel异常的控制字符
    # df[str_cols] = df[str_cols].replace(r'[\x00-\x1F\x7F]', '', regex=True)
    return df

df1_cleaned = clean_control_chars(df1)
df2_cleaned = clean_control_chars(df2)

writer = pd.ExcelWriter("output.xlsx", engine='openpyxl')
df1_cleaned.to_excel(writer, index=False, sheet_name="first")
df2_cleaned.to_excel(writer, index=False, sheet_name="second")
writer.close()

2. 从数据库源头过滤

如果数据直接来自数据库，可以在SQL查询阶段就替换垂直制表符，彻底避免后续处理：

• MySQL示例：SELECT REPLACE(your_column, CHAR(11), '') AS cleaned_column FROM your_table;
• PostgreSQL示例：SELECT REPLACE(your_column, CHR(11), '') AS cleaned_column FROM your_table;

3. 扩展openpyxl写入逻辑（进阶）

若不想修改原始DataFrame，可以通过自定义ExcelWriter子类过滤字符，但实现复杂度高于直接处理数据，适合特殊场景：

from pandas.io.excel._openpyxl import OpenpyxlWriter

class CleanOpenpyxlWriter(OpenpyxlWriter):
    def _write_cell(self, worksheet, cell, val):
        # 写入前过滤垂直制表符
        if isinstance(val, str):
            val = val.replace('\v', '')
        super()._write_cell(worksheet, cell, val)

# 使用自定义Writer
with CleanOpenpyxlWriter("output.xlsx") as writer:
    df1.to_excel(writer, index=False, sheet_name="first")
    df2.to_excel(writer, index=False, sheet_name="second")

内容的提问来源于stack exchange，提问作者Arpad Horvath -- Слава</think_never_used_51bce0c785ca2f68081bfa7d91973934>