python Excel处理特殊字符写入多个sheet

type

status

date

slug

summary

category

icon

password

💡

pandas对正常内容处理还不错，但是颜文字这类特殊字符总是报错，即使用escape消除特殊字符，仍报value错误，导致写入excel失败。故引入xlsxwriter

需求：现有多组数据，每组数据写入一个Excel的sheet，部分数据存在特殊字符。

核心配置： engine='xlsxwriter' 。有了它，自动处理所有特殊字符并写入表格。


import pandas as pd
import xlsxwriter

def write_to_excel(data, file_name):
    writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
    workbook = writer.book  # 获取工作簿
    header_format = workbook.add_format({'bold': True, 'bg_color': '#2F75B5', 'border': 1})
    cell_format = workbook.add_format({'border': 1})

    for sheet_name, df in data.items():
        df.to_excel(writer, index=False, sheet_name=sheet_name)
        worksheet = writer.sheets[sheet_name]

        for col_num, value in enumerate(df.columns.values):
            # 最小宽度为10，加上额外空间，即固定宽度，避免内容过长导致宽度异常
            column_len = max(len(value), 10) + 2  
            worksheet.set_column(col_num, col_num, column_len)
            worksheet.write(0, col_num, value, header_format)  # 写入蓝色表头

        for idx, row in df.iterrows():
            for col_idx, value in enumerate(row):
                worksheet.write(idx + 1, col_idx, value, cell_format)  # 写入数据
    #可能是writer._save()或者writer.save()，根据xlsxwriter版本来
    writer._save()

# 示例数据
data_1 = {
    'Column_A': ['Data_A1', 'Data_A2', 'Data_A3'],
    'Column_B': ['Data_B1', 'Data_B2', 'Data_B3'],
    'Column_C': ['Data_C1', 'Data_C2', 'Data_C3']
}

data_2 = {
    'Column_X': ['Data_X1', 'Data_X2', 'Data_X3'],
    'Column_Y': ['Data_Y1', 'Data_Y2', 'Data_Y3'],
    'Column_Z': ['Data_Z1', 'Data_Z2', 'Data_Z3 _(:з」∠)_']
}

# 将数据放入字典中
data_dict = {'Sheet_1': pd.DataFrame(data_1), 'Sheet_2': pd.DataFrame(data_2)}

# 调用函数写入Excel
write_to_excel(data_dict, 'multi_sheet_adjusted_column_width.xlsx')