utf-8编码无法处理�字符，邮件导出Excel遇阻求助

我编写了一个程序，将Outlook邮箱中的邮件转换为DataFrame后导出至Excel，但某收件箱中的非标准表情符号�导致Excel写入失败。尝试过论坛中的处理方法：

'\udf81'.encode('utf-16','surrogatepass').decode('utf-16')

但仍出现无法编码'\udf81'的错误。

完整代码 #

import win32com.client as client
import pandas as pd
import time

start = time.perf_counter()

pd.set_option('display.max_columns', None)

outlook = client.Dispatch('Outlook.Application')

namespace = outlook.GetNameSpace('MAPI')

account = namespace.Folders['gianfabian52@gmail.com']

inbox = account.Folders['Inbox']

all_inbox = inbox.Items

#test inbox
print(inbox.Name)
#test account is correct
print(inbox.Parent.Name)

#count inbox items to verify code collects all items
print(inbox.Items.Count)


#collect all emails in inbox
AllEM = [message for message in inbox.Items]

#create DF

dict = {'Subject': [],'Body':[],'Sender_Email':[],'Account':[]
        ,'Recieved_Time':[],'Classification':[]}
df = pd.DataFrame(dict)


#'Classification':[]

EM = [message for message in inbox.Items]
#message.SenderEmailType- determine email type and if in directory
#MailItem.Sender.GetExchangeUser().PrimarySmtpAddress - supposed to pull email from directory-issues
#message.SenderEmailAddress - pull SMTP email
def EmailDF():
    for message in EM:
            df.loc[len(df.index)] = [message.Subject, message.Body, message.SenderEmailAddress, account, message.ReceivedTime, 'NA']
        
EmailDF()
        
#remove timezone to avoid errors, and to keep clarity in pandas package 

df["Recieved_Time"]=df["Recieved_Time"].dt.tz_convert(None)

#sort by date and time
df = df.sort_values("Recieved_Time")

#print
print(df)

#writes out as csv without index
#csv
#df.to_csv('GianMail.csv', encoding='utf-8', index=False)

#excel
df.to_excel("GianMail.xlsx",engine='xlsxwriter', index=False)

finish = time.perf_counter()
print("Code executed in",round(finish-start,2),"seconds")

解决方案 #

1. 清理无效Unicode代理字符 #

单独的代理字符（如\udf81）是未配对的UTF-16代理项，无法正常编码。可以编写函数过滤这类字符：

import re

def clean_invalid_unicode(text):
    if not text:
        return ''
    # 移除未配对的代理字符
    return re.sub(r'[\ud800-\udbff](?![\udc00-\udfff])|(?<![\ud800-\udbff])[\udc00-\udfff]', '', text)

修改EmailDF函数，对邮件主题和正文应用清理：

def EmailDF():
    for message in EM:
            df.loc[len(df.index)] = [
                clean_invalid_unicode(message.Subject),
                clean_invalid_unicode(message.Body),
                message.SenderEmailAddress,
                account.Name,  # 替换原account对象为名称，避免序列化异常
                message.ReceivedTime,
                'NA'
            ]

2. 兼容处理代理字符（替代方案） #

如果想保留有效表情，仅修复无效代理对，可尝试以下函数，编码失败时再降级清理：

def fix_surrogates(text):
    if not text:
        return ''
    try:
        return text.encode('utf-16', 'surrogatepass').decode('utf-16')
    except UnicodeEncodeError:
        # 编码失败则移除无效字符
        return re.sub(r'[\ud800-\udbff](?![\udc00-\udfff])|(?<![\ud800-\udbff])[\udc00-\udfff]', '', text)

同样在EmailDF中替换对应的字段即可。

3. 额外优化 #

原代码中直接将account文件夹对象存入DataFrame，可能导致Excel序列化异常，建议替换为account.Name存储账户名称。

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