事情是这样的，上个月我接手了一个活，每天要处理一批Excel报表，大概有二十多个文件。每个文件的格式差不多，但里面的数据列名偶尔会变，有的叫“金额”，有的叫“交易额”，还有的叫“成交金额”。最烦的是，这些文件需要统一汇总，然后生成一个带图表的总表，最后还要发邮件给几个不同的人，每个人看的字段还不一样。
最开始两天我还老老实实手动搞，复制粘贴、调整格式、截图、写邮件，一套下来差不多四十分钟。干到第三天我就受不了了，这不是人干的活。我就想，写个脚本吧，虽然写脚本也要花时间，但写一次能用很久，而且不用每天重复这种无脑操作。
我用的Python 3.10，主要依赖pandas 1.5.3处理数据，openpyxl 3.1.2读写Excel，smtplib发邮件。机器是Windows 10，IDE用的VS Code，虚拟环境用的venv。
先说读取文件这块。所有报表都放在一个叫“input”的文件夹里，文件名格式是“2024-05-xx_报表.xlsx”。我需要遍历这个文件夹，只处理xlsx后缀的文件。本来直接用glob就行，但我遇到了一个坑：有些文件里面有空行，pandas的read_excel会把空行读成NaN，后面统计的时候就会多出来一堆空数据行。我一开始没处理，结果汇总表里出现了几十行空值，排查了半天才发现。
解决办法是读完之后加一个dropna(how='all')，把全为空的行删掉。代码大概是：
```python
import pandas as pd
import glob
import os
files = glob.glob('input/*.xlsx')
data_frames = []
for f in files:
df = pd.read_excel(f, engine='openpyxl')
df = df.dropna(how='all')
data_frames.append(df)
```
这里engine指定openpyxl是因为pandas默认的xlrd在新版Excel文件上会报错，我踩过这个坑，直接指定openpyxl省心。
然后就是列名统一。每个文件里“金额”字段的名字不一样，我写了一个映射字典，把几种可能的列名都映射成统一的“amount”。如果某个文件里找不到任何一个匹配的列名，就抛个异常，打印出这个文件实际的列名，方便排查。
```python
column_mapping = {
'金额': 'amount',
'交易额': 'amount',
'成交金额': 'amount'
}
def unify_columns(df):
for old_name, new_name in column_mapping.items():
if old_name in df.columns:
df.rename(columns={old_name: new_name}, inplace=True)
break
if 'amount' not in df.columns:
raise ValueError(f'找不到金额列，实际列名: {list(df.columns)}')
return df
```
这里有个细节，我用了break，因为一个文件里不可能同时出现两个不同的金额列名，找到第一个就停了。
汇总之后需要生成图表。我用了openpyxl自带的图表功能，没有用matplotlib，因为要直接把图表嵌入Excel里。生成一个柱状图，横轴是日期，纵轴是金额总和。这里要注意，openpyxl的图表数据源必须是连续的单元格区域，不能有跳跃。我先把汇总后的DataFrame按日期排序，然后写入一个新的sheet，再在同一个sheet里创建图表。
```python
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
wb = Workbook()
ws = wb.active
ws.title = '汇总'
# 写入数据
for row in dataframe_to_rows(summary_df, index=False, header=True):
ws.append(row)
# 创建图表
chart = BarChart()
chart.title = '每日金额汇总'
chart.y_axis.title = '金额'
chart.x_axis.title = '日期'
data = Reference(ws, min_col=2, min_row=1, max_row=len(summary_df)+1)
cats = Reference(ws, min_col=1, min_row=2, max_row=len(summary_df)+1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, 'E5')
```
这里dataframe_to_rows是openpyxl.utils里的工具函数，能把pandas的DataFrame转成行迭代器。注意min_row和max_row的索引是从1开始的，而且第一行是表头，所以数据从第二行开始。
图表生成之后，保存文件。保存之前我习惯先检查输出目录存不存在，不存在就创建。
```python
output_dir = 'output'
os.makedirs(output_dir, exist_ok=True)
wb.save(f'{output_dir}/汇总报表.xlsx')
```
最后是发邮件。不同的人需要的字段不一样，比如老板要看全部字段，财务只看金额和日期，销售还要看客户名。我建了一个配置字典，每个人对应一个字段列表。然后用smtplib发邮件，附件就是刚才生成的汇总文件。
发邮件这块我踩了一个坑，公司用的是Exchange邮件服务器，需要开启TLS，端口是587。我之前一直以为端口是25，结果一直连不上，报错timeout。后来查了公司的邮件服务器配置，改成587才通。
```python
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encoders
def send_email(to_addr, cc_addr, subject, body, attachment_path):
msg = MIMEMultipart()
msg['From'] = 'your_email@company.com'
msg['To'] = to_addr
msg['CC'] = ','.join(cc_addr)
msg['Subject'] = subject
msg.attach(MIMEText(body, 'plain'))
with open(attachment_path, 'rb') as f:
part = MIMEBase('application', 'octet-stream')
part.set_payload(f.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition', f'attachment; filename={os.path.basename(attachment_path)}')
msg.attach(part)
with smtplib.SMTP('smtp.company.com', 587) as server:
server.starttls()
server.login('your_email@company.com', 'your_password')
server.send_message(msg)
```
密码我肯定没有硬编码在代码里，用的是环境变量，os.getenv('EMAIL_PASSWORD')。写死在代码里那是给自己挖坑，万一代码传到Git上就炸了。
这个脚本写完之后，我放到Windows的任务计划程序里，每天下午四点自动跑一次。跑完之后给我自己发一封通知邮件，如果出错了也会发错误日志。到现在跑了三周，一次都没出过问题。唯一一次是input文件夹里多了一个临时的tmp文件，不是xlsx格式，glob没匹配到，所以也没影响。
踩坑经验总结一下：
第一，pandas读Excel的时候，空行问题很隐蔽，一定要加dropna。第二，openpyxl的图表数据源索引是从1开始的，不是0，写代码的时候容易搞混。第三，邮件端口别想当然用25，先查一下公司的配置。第四，配置信息（密码、邮件服务器地址）用环境变量或者配置文件，别硬编码。第五，脚本写完之后加个简单的日志，记录每次运行的结果，方便排查问题。我用的就是Python自带的logging，设置成输出到文件，级别INFO就够了。
这个脚本大概花了我一个下午写的，调试用了两个小时。但之后每天省下四十分钟，一周就是三个多小时，一个月就是十多个小时。这笔账怎么算都划算。而且以后再遇到类似的报表处理任务，改改配置就能用，不用从头写。
本文由猫哥AI助手自动发布 🐱
10个月宝宝每天需要喝多少奶粉？
