前言
每个月底都要花几小时整理 Excel 报表？数据汇总、格式调整、图表制作……这些重复劳动完全可以交给 Python 自动化完成。
本文教你用 Python 自动处理 Excel，从数据读取到报表生成，一键搞定。
场景描述
假设你每月需要处理这样的销售数据：
sales_data.xlsx
├── 1月：订单表、客户表、产品表
├── 2月：订单表、客户表、产品表
└── ...
需要汇总成月度报表，包括：
- 总销售额
- 各产品销量排行
- 客户购买分析
- 可视化图表
环境准备
# 安装必要的库
pip install pandas openpyxl matplotlib
核心代码
1. 读取 Excel 数据
import pandas as pd
def read_sales_data(file_path):
"""读取销售数据"""
# 读取多个 sheet
excel_file = pd.ExcelFile(file_path)
data = {}
for sheet_name in excel_file.sheet_names:
data[sheet_name] = pd.read_excel(file_path, sheet_name=sheet_name)
return data
# 使用示例
data = read_sales_data('sales_data.xlsx')
orders = data['订单表']
print(orders.head())
2. 数据汇总统计
def generate_summary(orders_df):
"""生成销售汇总"""
summary = {
'总销售额': orders_df['金额'].sum(),
'订单数量': len(orders_df),
'平均订单金额': orders_df['金额'].mean(),
'最大订单': orders_df['金额'].max(),
'最小订单': orders_df['金额'].min()
}
return summary
# 产品销量排行
def product_ranking(orders_df):
"""产品销量排行"""
ranking = orders_df.groupby('产品名称').agg({
'数量': 'sum',
'金额': 'sum'
}).sort_values('金额', ascending=False)
return ranking
3. 生成报表 Excel
def create_report(data, output_path):
"""生成报表文件"""
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
# 写入汇总数据
summary_df = pd.DataFrame([generate_summary(data['订单表'])])
summary_df.to_excel(writer, sheet_name='汇总', index=False)
# 写入产品排行
ranking = product_ranking(data['订单表'])
ranking.to_excel(writer, sheet_name='产品排行')
# 写入原始数据
for sheet_name, df in data.items():
df.to_excel(writer, sheet_name=sheet_name, index=False)
print(f"报表已生成：{output_path}")
# 使用
create_report(data, '月度销售报表.xlsx')
4. 添加图表
import matplotlib.pyplot as plt
from openpyxl.drawing.image import Image as XLImage
def add_charts(excel_path):
"""为报表添加图表"""
from openpyxl import load_workbook
wb = load_workbook(excel_path)
# 创建图表数据
ranking = pd.read_excel(excel_path, sheet_name='产品排行')
# 生成柱状图
plt.figure(figsize=(10, 6))
ranking['金额'].head(10).plot(kind='bar')
plt.title('Top 10 产品销售额')
plt.tight_layout()
plt.savefig('chart.png')
plt.close()
# 插入到 Excel
ws = wb.create_sheet('图表')
img = XLImage('chart.png')
ws.add_image(img, 'A1')
wb.save(excel_path)
print("图表已添加")
完整自动化脚本
import pandas as pd
import matplotlib.pyplot as plt
from openpyxl import load_workbook
from openpyxl.drawing.image import Image as XLImage
import os
from datetime import datetime
def auto_generate_report(input_folder, output_folder):
"""自动生成月度报表"""
# 确保输出文件夹存在
os.makedirs(output_folder, exist_ok=True)
# 获取当前月份
current_month = datetime.now().strftime('%Y年%m月')
output_file = f"{output_folder}/{current_month}销售报表.xlsx"
# 读取所有数据文件
all_data = []
for file in os.listdir(input_folder):
if file.endswith('.xlsx'):
file_path = os.path.join(input_folder, file)
data = read_sales_data(file_path)
all_data.append(data['订单表'])
# 合并数据
combined_data = pd.concat(all_data, ignore_index=True)
# 生成报表
create_report({'订单表': combined_data}, output_file)
# 添加图表
add_charts(output_file)
print(f"✅ 报表生成完成：{output_file}")
return output_file
# 每月自动执行
if __name__ == '__main__':
auto_generate_report('./raw_data', './reports')
进阶技巧
数据清洗
# 处理缺失值
orders_df['金额'].fillna(0, inplace=True)
# 数据类型转换
orders_df['日期'] = pd.to_datetime(orders_df['日期'])
# 异常值处理
orders_df = orders_df[orders_df['金额'] > 0]
条件格式
from openpyxl.styles import PatternFill
def highlight_top10(excel_path):
"""高亮显示前10名"""
wb = load_workbook(excel_path)
ws = wb['产品排行']
# 红色填充
red_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
# 高亮前10行
for row in range(2, 12):
for col in range(1, 4):
ws.cell(row=row, column=col).fill = red_fill
wb.save(excel_path)
定时自动执行
# 添加到 crontab，每月1号凌晨执行
0 0 1 * * /usr/bin/python3 /path/to/auto_generate_report.py
总结
用 Python 处理 Excel：
- ✅ 读取多个 sheet
- ✅ 数据汇总统计
- ✅ 自动生成图表
- ✅ 格式化美化
- ✅ 定时自动执行
从此告别手工报表，每月节省数小时！
下一篇预告：《Python 自动化办公：邮件批量发送，一键触达千人》
