Python把收到的Excel附件自动合并汇总

用imaplib下载邮件里的Excel附件，pandas自动读取多个xlsx/csv并纵向合并成一张总表，加上来源文件列，附完整可运行代码与去重处理。

场景痛点

销售每天把各自的日报Excel发过来，十几份文件名还不一样，手动一个个打开复制粘贴到总表，半小时就没了，还容易漏行错列。让脚本自动把最近邮件里的xlsx/csv附件下载下来，pandas读出来纵向拼成一张总表，再加一列"来源文件"方便追溯，每天跑一下就行。

用到的库

pip install pandas openpyxl

imaplib、email是标准库；pandas负责合并，openpyxl负责读写xlsx。

完整代码

# -*- coding: utf-8 -*-
import os
import imaplib
import email
import pandas as pd
from email.header import decode_header, make_header

IMAP_SERVER = "imap.qq.com"
IMAP_PORT = 993
ACCOUNT = "your_account@qq.com"
PASSWORD = "your_imap_auth_code"

DOWNLOAD_DIR = "./excel_inbox"
OUTPUT_FILE = "./销售日报汇总.xlsx"

# 只处理这些扩展名的附件
EXCEL_EXT = {".xlsx", ".xls", ".csv"}


def decode_str(s):
if not s:
return ""
return str(make_header(decode_header(s)))


def safe_name(name):
for ch in '/\\:*?"<>|':
name = name.replace(ch, "_")
return name or "attachment"


def download_excel_attachments(limit=50):
os.makedirs(DOWNLOAD_DIR, exist_ok=True)
mail = imaplib.IMAP4_SSL(IMAP_SERVER, IMAP_PORT)
mail.login(ACCOUNT, PASSWORD)
mail.select("INBOX")
_, msgs = mail.search(None, "ALL")
ids = msgs[0].split()[-limit:]

saved = []
for mid in reversed(ids):
_, data = mail.fetch(mid, "(BODY.PEEK[])")
msg = email.message_from_bytes(data[0][1])
for part in msg.walk():
filename = part.get_filename()
if not filename:
continue
filename = safe_name(decode_str(filename))
ext = os.path.splitext(filename)[1].lower()
if ext not in EXCEL_EXT:
continue
payload = part.get_payload(decode=True)
if payload is None:
continue
path = os.path.join(DOWNLOAD_DIR, filename)
with open(path, "wb") as f:
f.write(payload)
saved.append(path)
print("下载附件：", filename)
mail.logout()
return saved


def merge_excel_files(file_paths):
frames = []
for path in file_paths:
ext = os.path.splitext(path)[1].lower()
try:
if ext == ".csv":
df = pd.read_csv(path, encoding="utf-8-sig")
else:
df = pd.read_excel(path)
# 加一列来源，方便追溯
df["来源文件"] = os.path.basename(path)
frames.append(df)
print(f"读取成功：{os.path.basename(path)}  {len(df)}行")
except Exception as e:
print(f"跳过 {path}：{e}")

if not frames:
print("没有可合并的Excel文件")
return
merged = pd.concat(frames, ignore_index=True)
# 按整行去重，避免重复发的附件叠加
merged = merged.drop_duplicates()
merged.to_excel(OUTPUT_FILE, index=False)
print(f"\n合并完成，共 {len(merged)} 行，保存到 {OUTPUT_FILE}")


if __name__ == "__main__":
files = download_excel_attachments(limit=50)
merge_excel_files(files)

代码讲解

两段式：第一段download_excel_attachments只负责把邮件里的Excel附件下到本地，第二段merge_excel_files负责读取合并。分开好处是下载一次可以反复调试合并逻辑，不用每次都重新连邮箱。
EXCEL_EXT白名单：只处理xlsx/xls/csv，PDF、图片等其他附件直接跳过。
pd.read_excel：pandas读xlsx自动用openpyxl引擎，不用额外指定。csv用utf-8-sig编码，兼容Excel导出的带BOM的csv。
df["来源文件"] = ...：在总表里加一列标记每行来自哪个附件，出问题能回溯是哪份日报数据对不上。
pd.concat(frames, ignore_index=True)：纵向堆叠，要求各Excel列名基本一致。列名差太多时会出现一堆NaN，实战里先统一模板。
drop_duplicates()：整行去重，防止同一封附件发了两遍导致数据翻倍。