我要评论引言：告别手动报表，拥抱自动化
在日常工作中，报表生成是一项频繁且繁琐的任务。作为数据分析师、运营或财务人员，你可能每周甚至每天都需要从数据库或csv中提取原始数据，用excel进行清洗、计算，然后制作成格式统一的报表，最后通过邮件发送给相关人员。这个过程重复、耗时，且容易出错。
在“python办公自动化”学习路径的第四阶段（第36-50天），我们的目标是让机器人学会“读写算”——能够处理excel、pdf、csv等常见办公文档。今天，我们将迎来本阶段的压轴实战项目：构建一个自动化报表生成器。这个项目将整合数据读取、清洗分析、excel报表生成、pdf转换以及邮件发送的全流程，让你亲身体验从原始数据到最终邮件的“一键式”自动化。
通过本文，你将掌握：
- 如何用
pandas从csv/数据库读取并清洗数据 - 如何用
openpyxl生成带有格式和图表的高颜值excel报表 - 如何利用
win32com（结合pypdf2）将excel总结页转换为pdf - 如何用
smtplib自动发送带附件的邮件
本文所有代码基于python 3.8+，请确保已安装以下库：
pip install pandas openpyxl pypdf2 pywin32 # pywin32用于windows com操作 # 如需连接数据库，还需安装 sqlalchemy pymysql 等
注意：pywin32仅支持windows系统，如果你使用的是macos或linux，可以考虑使用libreoffice的命令行模式代替excel的com组件，或使用pdfkit将html转换为pdf。本文将以windows环境为例进行演示。
一、项目需求概述
假设我们是一家连锁零售公司的数据分析师，需要每周一向管理层发送上一周的销售报表。原始数据存储在sales_data.csv文件中（也可从数据库读取），报表需包含以下内容：
- 原始数据总览：显示上周所有销售记录
- 销售汇总表：按区域、门店汇总销售额、订单量
- 趋势图表：每日销售额趋势图（嵌入式图表）
- 总结页：包含核心指标（总销售额、总订单量、客单价）及关键结论
最终，我们需要将总结页单独转换为pdf格式，与完整excel报表一起作为邮件附件发送给指定收件人。
整个流程分为五个核心步骤：
- 步骤1：数据读取 – 从csv或数据库加载原始数据
- 步骤2：数据清洗与分析 – 处理缺失值、计算统计指标
- 步骤3：excel报表生成 – 使用
openpyxl创建多sheet工作簿，设置格式，插入图表 - 步骤4：总结页转pdf – 利用
win32com调用excel应用程序，将指定sheet导出为pdf，并用pypdf2进行额外处理（如加密） - 步骤5：邮件发送 – 通过smtp发送带附件的邮件
二、数据读取：从源头获取原始数据
原始数据可能以多种形式存在，最常见的是csv文件和关系型数据库。我们编写一个通用函数，根据配置选择读取方式。
2.1 从csv读取
假设sales_data.csv包含以下字段：date（日期）、region（区域）、store（门店）、product（产品）、quantity（销量）、unit_price（单价）、total_amount（总金额）。
import pandas as pd
def read_csv_data(file_path):
df = pd.read_csv(file_path, parse_dates=['date'])
print(f"从csv读取数据，共 {len(df)} 行")
return df
2.2 从数据库读取（以sqlite为例）
如果数据存储在数据库中，我们可以使用sqlalchemy建立连接。以下示例连接sqlite数据库sales.db，读取sales表上周的数据。
from sqlalchemy import create_engine
def read_db_data(db_url, table_name, start_date, end_date):
engine = create_engine(db_url)
query = f"""
select * from {table_name}
where date between '{start_date}' and '{end_date}'
"""
df = pd.read_sql(query, engine, parse_dates=['date'])
print(f"从数据库读取数据，共 {len(df)} 行")
return df
为简化示例，后续我们将以csv文件为例。在实际项目中，你可以根据需求灵活选择数据源。
三、数据清洗与分析
原始数据往往包含缺失值、异常值或格式不一致的问题，需要进行清洗。同时，我们需要计算报表所需的汇总指标。
3.1 数据清洗
常见的清洗操作包括：
- 删除重复记录
- 处理缺失值（如填充0或删除）
- 确保数据类型正确（如将
total_amount转为浮点数） - 过滤异常数据（如销量为负的记录）
def clean_data(df):
# 删除重复行
df = df.drop_duplicates()
# 处理缺失值：这里假设total_amount不能为空，若为空则填充为0
df['total_amount'] = df['total_amount'].fillna(0)
# 确保数值列类型正确
df['quantity'] = pd.to_numeric(df['quantity'], errors='coerce').fillna(0)
df['unit_price'] = pd.to_numeric(df['unit_price'], errors='coerce').fillna(0)
# 过滤：销量和单价不能为负数
df = df[(df['quantity'] >= 0) & (df['unit_price'] >= 0)]
# 重新计算total_amount（防止原始数据有误）
df['total_amount'] = df['quantity'] * df['unit_price']
return df
3.2 分析汇总
我们需要生成按区域和门店的销售汇总表，以及每日销售趋势。
def analyze_data(df):
# 按区域和门店汇总
summary_by_store = df.groupby(['region', 'store']).agg(
total_sales=('total_amount', 'sum'),
order_count=('quantity', 'count'), # 假设每条记录为一笔订单
avg_order_value=('total_amount', 'mean')
).reset_index()
# 按日期汇总每日销售额
daily_sales = df.groupby(df['date'].dt.date)['total_amount'].sum().reset_index()
daily_sales.columns = ['date', 'daily_total']
# 计算全局指标
total_sales = df['total_amount'].sum()
total_orders = len(df)
avg_customer_value = total_sales / total_orders if total_orders > 0 else 0
metrics = {
'total_sales': total_sales,
'total_orders': total_orders,
'avg_customer_value': avg_customer_value
}
return summary_by_store, daily_sales, metrics
四、生成精美excel报表（使用openpyxl）
pandas自带的to_excel功能只能输出简单数据，无法满足格式要求。我们将使用openpyxl引擎，手动创建工作簿、设置样式、插入图表。
4.1 创建工作簿并写入数据
我们计划生成一个包含三个sheet的excel文件：
- 原始数据：
raw data - 汇总表：
summary by store - 趋势图：
daily trend
from openpyxl import workbook
from openpyxl.styles import font, patternfill, border, side, alignment, namedstyle
from openpyxl.chart import linechart, reference
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd
def create_excel_report(df_raw, summary_df, daily_df, metrics, output_path):
wb = workbook()
# 1. 原始数据 sheet
ws_raw = wb.active
ws_raw.title = "raw data"
# 将dataframe逐行写入
for r in dataframe_to_rows(df_raw, index=false, header=true):
ws_raw.append(r)
# 2. 汇总表 sheet
ws_summary = wb.create_sheet("summary by store")
for r in dataframe_to_rows(summary_df, index=false, header=true):
ws_summary.append(r)
# 3. 每日趋势 sheet
ws_trend = wb.create_sheet("daily trend")
for r in dataframe_to_rows(daily_df, index=false, header=true):
ws_trend.append(r)
# 保存临时文件，后续还要进行格式化和图表插入
wb.save(output_path)
return wb # 返回工作簿对象以便继续操作
4.2 设置单元格格式
为了使报表专业易读，我们需要对标题行加粗、填充背景色，设置数字格式，调整列宽等。
def format_excel(wb):
# 定义常用样式
header_font = font(bold=true, color="ffffff")
header_fill = patternfill(start_color="4f81bd", end_color="4f81bd", fill_type="solid")
thin_border = border(
left=side(style='thin'),
right=side(style='thin'),
top=side(style='thin'),
bottom=side(style='thin')
)
currency_style = namedstyle(name="currency", number_format='"¥"#,##0.00')
for sheet_name in wb.sheetnames:
ws = wb[sheet_name]
# 设置标题行样式（第一行）
for cell in ws[1]:
cell.font = header_font
cell.fill = header_fill
cell.border = thin_border
cell.alignment = alignment(horizontal='center')
# 调整列宽
for col in ws.columns:
max_length = 0
col_letter = col[0].column_letter
for cell in col:
try:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
except:
pass
adjusted_width = min(max_length + 2, 50)
ws.column_dimensions[col_letter].width = adjusted_width
# 为包含金额的列应用货币格式（假设列名包含'sales'或'amount'）
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
for cell in row:
cell.border = thin_border
if cell.column_letter in ['c','d']: # 根据实际列调整
cell.number_format = currency_style.number_format
return wb
4.3 插入图表
在daily trend sheet中插入折线图，展示每日销售额变化。
from openpyxl.chart import linechart, reference
def add_chart(wb):
ws = wb["daily trend"]
# 数据范围：假设日期在a列，销售额在b列，从第2行开始到最后
data = reference(ws, min_col=2, min_row=1, max_row=ws.max_row, max_col=2)
dates = reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
chart = linechart()
chart.add_data(data, titles_from_data=true)
chart.set_categories(dates)
chart.title = "每日销售额趋势"
chart.x_axis.title = "日期"
chart.y_axis.title = "销售额"
chart.style = 12 # 内置样式
# 将图表放在e2单元格附近
ws.add_chart(chart, "e2")
return wb
4.4 创建总结页
总结页通常放在第一个sheet，包含关键指标和文字结论。我们可以单独创建一个sheet，命名为“summary”。
def create_summary_sheet(wb, metrics):
ws = wb.create_sheet("summary", 0) # 插入到第一个位置
# 写入标题
ws['a1'] = "销售周报总结"
ws['a1'].font = font(size=16, bold=true)
ws.merge_cells('a1:c1')
# 写入核心指标
row = 3
ws[f'a{row}'] = "总销售额："
ws[f'b{row}'] = metrics['total_sales']
ws[f'b{row}'].number_format = '"¥"#,##0.00'
row += 1
ws[f'a{row}'] = "总订单数："
ws[f'b{row}'] = metrics['total_orders']
row += 1
ws[f'a{row}'] = "客单价："
ws[f'b{row}'] = metrics['avg_customer_value']
ws[f'b{row}'].number_format = '"¥"#,##0.00'
# 添加结论性文字
row += 2
ws[f'a{row}'] = "结论："
row += 1
conclusion = f"上周总销售额为¥{metrics['total_sales']:,.0f}，共完成{metrics['total_orders']}笔订单，客单价为¥{metrics['avg_customer_value']:.2f}。"
ws[f'a{row}'] = conclusion
ws.merge_cells(f'a{row}:c{row}')
# 调整列宽
ws.column_dimensions['a'].width = 15
ws.column_dimensions['b'].width = 20
ws.column_dimensions['c'].width = 15
return wb
五、将总结页转换为pdf
这是本项目的关键难点。openpyxl无法直接导出pdf，而pypdf2只能操作已有的pdf文件。在windows企业环境中，最可靠的方式是借助excel应用程序本身（通过win32com）将指定sheet另存为pdf。然后，我们可以使用pypdf2对生成的pdf进行额外处理，例如添加密码保护或水印。
5.1 使用win32com导出pdf
import win32com.client
import os
def export_sheet_to_pdf(excel_path, sheet_name, pdf_path):
"""
使用excel com对象将指定sheet导出为pdf
"""
excel = win32com.client.dispatch("excel.application")
excel.visible = false # 后台运行
try:
wb = excel.workbooks.open(os.path.abspath(excel_path))
ws = wb.sheets(sheet_name)
# 导出为pdf
ws.exportasfixedformat(0, pdf_path) # 0代表xltypepdf
print(f"已导出 {sheet_name} 到 {pdf_path}")
except exception as e:
print(f"导出pdf失败: {e}")
raise
finally:
wb.close(savechanges=false)
excel.quit()
5.2 使用pypdf2添加加密（可选）
假设我们希望pdf文件需要密码才能打开，可以使用pypdf2对生成的pdf进行加密。
import pypdf2
def encrypt_pdf(input_pdf, output_pdf, password):
with open(input_pdf, 'rb') as file:
pdf_reader = pypdf2.pdfreader(file)
pdf_writer = pypdf2.pdfwriter()
for page_num in range(len(pdf_reader.pages)):
pdf_writer.add_page(pdf_reader.pages[page_num])
pdf_writer.encrypt(password)
with open(output_pdf, 'wb') as out_file:
pdf_writer.write(out_file)
print(f"pdf已加密，保存至 {output_pdf}")
六、发送邮件附件
最后一步，将生成的excel文件和pdf文件作为附件，通过邮件发送给指定收件人。我们使用python内置的smtplib和email库。
import smtplib
from email.mime.multipart import mimemultipart
from email.mime.text import mimetext
from email.mime.base import mimebase
from email import encoders
import os
def send_email(sender, password, receiver, subject, body, attachments):
# 创建邮件对象
msg = mimemultipart()
msg['from'] = sender
msg['to'] = receiver
msg['subject'] = subject
# 添加正文
msg.attach(mimetext(body, 'plain', 'utf-8'))
# 添加附件
for file_path in attachments:
with open(file_path, 'rb') as attachment:
part = mimebase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header(
'content-disposition',
f'attachment; filename= {os.path.basename(file_path)}'
)
msg.attach(part)
# 发送邮件（以qq邮箱为例）
try:
server = smtplib.smtp_ssl('smtp.qq.com', 465)
server.login(sender, password)
server.send_message(msg)
server.quit()
print("邮件发送成功")
except exception as e:
print(f"邮件发送失败: {e}")
七、整合全流程：一键生成报表
现在，我们将所有步骤封装成一个函数generate_report，实现从数据到邮件的一键自动化。
def generate_report(csv_path, excel_output, pdf_output, email_config):
# 1. 读取数据
df_raw = read_csv_data(csv_path)
# 2. 清洗与分析
df_clean = clean_data(df_raw)
summary_df, daily_df, metrics = analyze_data(df_clean)
# 3. 生成excel（先保存基本数据）
wb = create_excel_report(df_clean, summary_df, daily_df, metrics, excel_output)
wb = format_excel(wb)
wb = add_chart(wb)
wb = create_summary_sheet(wb, metrics)
wb.save(excel_output)
print(f"excel报表已生成: {excel_output}")
# 4. 将summary sheet导出为pdf
export_sheet_to_pdf(excel_output, "summary", pdf_output)
# 可选：用pypdf2加密pdf
# pdf_encrypted = pdf_output.replace('.pdf', '_encrypted.pdf')
# encrypt_pdf(pdf_output, pdf_encrypted, 'weekly123')
# 5. 发送邮件
send_email(
sender=email_config['sender'],
password=email_config['password'],
receiver=email_config['receiver'],
subject=email_config['subject'],
body=email_config['body'],
attachments=[excel_output, pdf_output] # 同时发送excel和pdf
)
print("报表生成及发送流程全部完成！")
使用示例：
if __name__ == "__main__":
csv_file = "data/sales_data.csv"
excel_file = "output/sales_report.xlsx"
pdf_file = "output/summary.pdf"
email_cfg = {
'sender': 'your_email@qq.com',
'password': 'your_authorization_code', # qq邮箱授权码
'receiver': 'boss@company.com',
'subject': '上周销售报表',
'body': '您好，附件是上周销售报表，请查收。\n\n数据分析团队'
}
generate_report(csv_file, excel_file, pdf_file, email_cfg)
八、总结与扩展
通过本实战项目，我们成功构建了一个自动化报表生成器，涵盖了从数据读取、清洗分析、excel格式化、pdf导出到邮件发送的完整流程。这个工具可以每周定时运行（配合windows任务计划程序或linux cron），彻底解放人力。
以上就是python从零构建一个自动化报表生成器的详细内容，更多关于python自动化报表生成器的资料请关注代码网其它相关文章！
发表评论