本文目录导读：
我来为您介绍Python报表生成的多个实用案例,从简单到复杂逐步深入。
使用Pandas生成Excel报表
基础案例：销售报表生成
import pandas as pd
import numpy as np
from datetime import datetime, timedelta
# 生成示例数据
def generate_sales_data():
"""生成示例销售数据"""
np.random.seed(42)
dates = [datetime.now() - timedelta(days=x) for x in range(30)]
data = {
'日期': dates,
'产品': np.random.choice(['产品A', '产品B', '产品C', '产品D'], 30),
'销量': np.random.randint(10, 100, 30),
'单价': np.random.randint(50, 500, 30),
'区域': np.random.choice(['华东', '华北', '华南', '西南'], 30)
}
df = pd.DataFrame(data)
df['销售额'] = df['销量'] * df['单价']
return df
# 生成报表
def create_sales_report():
"""创建销售报表"""
# 获取数据
df = generate_sales_data()
# 基础统计
summary = df.groupby('产品').agg({
'销量': 'sum',
'销售额': 'sum',
'区域': lambda x: ', '.join(x.unique())
}).reset_index()
# 创建Excel写入器
with pd.ExcelWriter('销售报表.xlsx', engine='openpyxl') as writer:
# 写入原始数据
df.to_excel(writer, sheet_name='原始数据', index=False)
# 写入汇总数据
summary.to_excel(writer, sheet_name='产品汇总', index=False)
# 写入透视表
pivot = pd.pivot_table(df, values='销售额',
index='产品',
columns='区域',
aggfunc='sum',
fill_value=0)
pivot.to_excel(writer, sheet_name='区域分析')
print("报表生成完成！")
return df, summary
# 高级报表：带格式
def create_formatted_report():
"""创建带格式的高级报表"""
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
df = generate_sales_data()
# 创建报表
with pd.ExcelWriter('格式化报表.xlsx', engine='openpyxl') as writer:
df.to_excel(writer, sheet_name='销售报表', index=False)
# 加载并格式化
wb = load_workbook('格式化报表.xlsx')
ws = wb['销售报表']
# 定义样式
header_font = Font(bold=True, color='FFFFFF', size=12)
header_fill = PatternFill(start_color='366092', end_color='366092', fill_type='solid')
header_alignment = Alignment(horizontal='center', vertical='center')
thin_border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
# 格式化表头
for cell in ws[1]:
cell.font = header_font
cell.fill = header_fill
cell.alignment = header_alignment
cell.border = thin_border
# 格式化数据行
for row in ws.iter_rows(min_row=2, max_row=ws.max_row, max_col=ws.max_column):
for cell in row:
cell.border = thin_border
cell.alignment = Alignment(horizontal='center')
# 高亮高销售额行
if row[4].value and row[4].value > 10000:
for cell in row:
cell.fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
# 设置列宽
for column in ws.columns:
max_length = 0
column_letter = column[0].column_letter
for cell in column:
try:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
except:
pass
ws.column_dimensions[column_letter].width = max_length + 2
wb.save('格式化报表.xlsx')
print("格式化报表生成完成！")
# 运行示例
df, summary = create_sales_report()
print("\n产品汇总：")
print(summary)
create_formatted_report()
使用ReportLab生成PDF报表
from reportlab.lib import colors
from reportlab.lib.pagesizes import letter, landscape
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.units import inch, mm
from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, PageBreak
from reportlab.lib.enums import TA_CENTER, TA_RIGHT
import pandas as pd
import numpy as np
from datetime import datetime
class PDFReportGenerator:
"""PDF报表生成器"""
def __init__(self, filename='report.pdf'):
self.filename = filename
self.styles = getSampleStyleSheet()
self.elements = []
def create_header(self):
"""创建报告头"""
# 标题
title_style = ParagraphStyle(
'CustomTitle',
parent=self.styles['Heading1'],
fontSize=24,
alignment=TA_CENTER,
spaceAfter=20
)
self.elements.append(Paragraph('月度销售报告', title_style))
# 日期
date_style = ParagraphStyle(
'CustomDate',
parent=self.styles['Normal'],
alignment=TA_CENTER,
spaceAfter=30
)
self.elements.append(Paragraph(f'生成日期：{datetime.now().strftime("%Y-%m-%d")}', date_style))
def create_summary_table(self, data):
"""创建汇总表格"""
# 准备数据
table_data = [['指标', '数值']]
summary_items = [
('总销售额', f'¥{data["销售额"].sum():,.2f}'),
('平均销售额', f'¥{data["销售额"].mean():,.2f}'),
('最高销售额', f'¥{data["销售额"].max():,.2f}'),
('销售总数量', f'{data["销量"].sum():,}'),
('产品种类数', f'{data["产品"].nunique()}')
]
table_data.extend(summary_items)
# 创建表格
table = Table(table_data, colWidths=[2*inch, 2*inch])
table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#366092')),
('TEXTCOLOR', (0, 0), (-1, 0), colors.white),
('ALIGN', (0, 0), (-1, -1), 'CENTER'),
('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
('FONTSIZE', (0, 0), (-1, 0), 14),
('BOTTOMPADDING', (0, 0), (-1, 0), 12),
('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#f8f9fa')),
('GRID', (0, 0), (-1, -1), 1, colors.black)
]))
self.elements.append(Paragraph('关键指标摘要', self.styles['Heading2']))
self.elements.append(Spacer(1, 10))
self.elements.append(table)
self.elements.append(Spacer(1, 20))
def create_detail_table(self, data):
"""创建详细数据表格"""
# 准备数据
table_data = [['日期', '产品', '销量', '单价', '区域', '销售额']]
for _, row in data.iterrows():
table_data.append([
row['日期'].strftime('%Y-%m-%d'),
row['产品'],
str(row['销量']),
f'¥{row["单价"]}',
row['区域'],
f'¥{row["销售额"]:,.2f}'
])
# 创建表格
table = Table(table_data, colWidths=[1.2*inch, 1*inch, 0.8*inch, 0.8*inch, 0.8*inch, 1.2*inch])
table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#366092')),
('TEXTCOLOR', (0, 0), (-1, 0), colors.white),
('ALIGN', (0, 0), (-1, -1), 'CENTER'),
('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
('FONTSIZE', (0, 0), (-1, 0), 10),
('FONTSIZE', (0, 1), (-1, -1), 8),
('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.HexColor('#f8f9fa')]),
('GRID', (0, 0), (-1, -1), 1, colors.HexColor('#dee2e6')),
('ALIGN', (2, 1), (-1, -1), 'RIGHT'),
('VALIGN', (0, 0), (-1, -1), 'MIDDLE')
]))
self.elements.append(Paragraph('销售明细', self.styles['Heading2']))
self.elements.append(Spacer(1, 10))
self.elements.append(table)
def add_chart_to_report(self):
"""添加图表到报表（简化示例）"""
try:
import matplotlib.pyplot as plt
import matplotlib
# 设置中文字体
matplotlib.rcParams['font.sans-serif'] = ['SimHei']
matplotlib.rcParams['axes.unicode_minus'] = False
# 创建图表
fig, axes = plt.subplots(1, 2, figsize=(10, 4))
# 生成示例数据
data = generate_sales_data()
# 销售趋势图
daily_sales = data.groupby('日期')['销售额'].sum()
axes[0].plot(daily_sales.index, daily_sales.values, marker='o', linewidth=2)
axes[0].set_title('每日销售趋势')
axes[0].set_xlabel('日期')
axes[0].set_ylabel('销售额')
axes[0].tick_params(axis='x', rotation=45)
# 产品占比图
product_sales = data.groupby('产品')['销售额'].sum()
colors = ['#ff9999', '#66b3ff', '#99ff99', '#ffcc99']
axes[1].pie(product_sales.values, labels=product_sales.index,
autopct='%1.1f%%', colors=colors)
axes[1].set_title('产品销售占比')
plt.tight_layout()
plt.savefig('chart.png', dpi=100, bbox_inches='tight')
plt.close()
# 添加到PDF
from reportlab.platypus import Image
self.elements.append(PageBreak())
self.elements.append(Paragraph('数据可视化', self.styles['Heading2']))
self.elements.append(Spacer(1, 10))
self.elements.append(Image('chart.png', width=7*inch, height=3*inch))
except ImportError:
print("Matplotlib未安装，跳过图表生成")
def generate(self):
"""生成PDF报告"""
# 准备数据
data = generate_sales_data()
# 创建PDF文档
doc = SimpleDocTemplate(
self.filename,
pagesize=letter,
rightMargin=72,
leftMargin=72,
topMargin=72,
bottomMargin=72
)
# 构建内容
self.create_header()
self.create_summary_table(data)
self.elements.append(Spacer(1, 20))
self.create_detail_table(data)
self.add_chart_to_report()
# 生成PDF
doc.build(self.elements)
print(f"PDF报表已生成：{self.filename}")
# 使用示例
if __name__ == "__main__":
# 生成PDF报表
generator = PDFReportGenerator('销售报告.pdf')
generator.generate()
使用Matplotlib生成可视化报表
import matplotlib.pyplot as plt
import matplotlib
import pandas as pd
import numpy as np
from datetime import datetime, timedelta
# 设置中文字体
matplotlib.rcParams['font.sans-serif'] = ['SimHei']
matplotlib.rcParams['axes.unicode_minus'] = False
def create_visualization_report():
"""创建可视化报表"""
# 生成数据
data = generate_sales_data()
# 创建多子图报表
fig = plt.figure(figsize=(15, 10))
# 1. 每日销售趋势
ax1 = plt.subplot(2, 3, 1)
daily_sales = data.groupby('日期')['销售额'].sum()
ax1.plot(daily_sales.index, daily_sales.values,
marker='o', linewidth=2, color='#2196F3')
ax1.set_title('每日销售趋势', fontsize=12, fontweight='bold')
ax1.set_xlabel('日期')
ax1.set_ylabel('销售额 (元)')
ax1.tick_params(axis='x', rotation=45)
ax1.grid(True, alpha=0.3)
# 2. 产品销量对比
ax2 = plt.subplot(2, 3, 2)
product_sales = data.groupby('产品')['销量'].sum()
colors = ['#FF6384', '#36A2EB', '#FFCE56', '#4BC0C0']
bars = ax2.bar(product_sales.index, product_sales.values, color=colors)
ax2.set_title('产品销售对比', fontsize=12, fontweight='bold')
ax2.set_xlabel('产品')
ax2.set_ylabel('总销量')
for bar, value in zip(bars, product_sales.values):
ax2.text(bar.get_x() + bar.get_width()/2, bar.get_height() + 5,
str(value), ha='center', va='bottom')
# 3. 区域销售额占比
ax3 = plt.subplot(2, 3, 3)
region_sales = data.groupby('区域')['销售额'].sum()
ax3.pie(region_sales.values, labels=region_sales.index, autopct='%1.1f%%',
colors=['#FF6384', '#36A2EB', '#FFCE56', '#4BC0C0'])
ax3.set_title('区域销售额占比', fontsize=12, fontweight='bold')
# 4. 箱线图：各产品价格分布
ax4 = plt.subplot(2, 3, 4)
product_prices = [data[data['产品']==p]['单价'] for p in data['产品'].unique()]
box = ax4.boxplot(product_prices, labels=data['产品'].unique())
ax4.set_title('产品价格分布', fontsize=12, fontweight='bold')
ax4.set_ylabel('单价 (元)')
ax4.grid(True, alpha=0.3)
# 5. 热力图：产品-区域销售额
ax5 = plt.subplot(2, 3, 5)
pivot_table = data.pivot_table(values='销售额', index='产品',
columns='区域', aggfunc='sum', fill_value=0)
im = ax5.imshow(pivot_table.values, cmap='YlOrRd', aspect='auto')
ax5.set_xticks(range(len(pivot_table.columns)))
ax5.set_yticks(range(len(pivot_table.index)))
ax5.set_xticklabels(pivot_table.columns)
ax5.set_yticklabels(pivot_table.index)
ax5.set_title('产品-区域销售额热力图', fontsize=12, fontweight='bold')
plt.colorbar(im, ax=ax5)
# 6. 统计摘要
ax6 = plt.subplot(2, 3, 6)
ax6.axis('off')
summary_text = f"""
销售报表摘要
{"="*20}
总销售额: ¥{data['销售额'].sum():,.2f}
总销量: {data['销量'].sum():,}
平均销售额: ¥{data['销售额'].mean():,.2f}
最好业绩: {data.loc[data['销售额'].idxmax(), '产品']}
最差业绩: {data.loc[data['销售额'].idxmin(), '产品']}
产品种类: {data['产品'].nunique()}
覆盖区域: {data['区域'].nunique()}
数据范围:
{data['日期'].min().strftime('%Y-%m-%d')}
至 {data['日期'].max().strftime('%Y-%m-%d')}
"""
ax6.text(0.1, 0.9, summary_text, transform=ax6.transAxes,
fontsize=10, verticalalignment='top',
family='monospace')
plt.tight_layout()
plt.savefig('可视化报表.png', dpi=150, bbox_inches='tight')
plt.show()
print("可视化报表已生成：可视化报表.png")
# 运行可视化报表
create_visualization_report()
综合报表生成系统
import json
import os
from datetime import datetime
class ReportManager:
"""报表管理器"""
def __init__(self):
self.report_types = {
'excel': self.generate_excel_report,
'pdf': self.generate_pdf_report,
'image': self.generate_image_report
}
def generate_excel_report(self, data=None):
"""生成Excel报表"""
create_formatted_report()
return '格式化报表.xlsx'
def generate_pdf_report(self, data=None):
"""生成PDF报表"""
generator = PDFReportGenerator('自动报表.pdf')
generator.generate()
return '自动报表.pdf'
def generate_image_report(self, data=None):
"""生成图片报表"""
create_visualization_report()
return '可视化报表.png'
def generate_all_reports(self):
"""生成所有类型报表"""
reports = {}
for report_type, func in self.report_types.items():
try:
filename = func()
reports[report_type] = {
'status': 'success',
'filename': filename,
'size': os.path.getsize(filename)
}
except Exception as e:
reports[report_type] = {
'status': 'error',
'message': str(e)
}
return reports
def save_report_metadata(self, reports, metadata_file='report_metadata.json'):
"""保存报表元数据"""
metadata = {
'generated_at': datetime.now().isoformat(),
'reports': reports
}
with open(metadata_file, 'w', encoding='utf-8') as f:
json.dump(metadata, f, indent=2, ensure_ascii=False)
print(f"报表元数据已保存到 {metadata_file}")
# 完整示例
if __name__ == "__main__":
print("=" * 50)
print("Python报表生成系统")
print("=" * 50)
# 创建报表管理器
manager = ReportManager()
# 生成所有报表
reports = manager.generate_all_reports()
# 保存元数据
manager.save_report_metadata(reports)
# 打印结果
print("\n报表生成结果：")
for report_type, info in reports.items():
if info['status'] == 'success':
print(f"✅ {report_type.upper()}: {info['filename']}")
print(f" 大小: {info['size']:,} 字节")
else:
print(f"❌ {report_type.upper()}: 生成失败 - {info['message']}")
使用建议
安装依赖
pip install pandas openpyxl reportlab matplotlib numpy
选择报表类型
| 报表类型 | 适用场景 | 优点 | 缺点 |
|---|---|---|---|
| Excel | 数据分析、数据导出 | 易于编辑、格式丰富 | 文件较大 |
| 正式报告、分享 | 格式固定、跨平台 | 不易修改 | |
| 图片 | 展示、嵌入网页 | 直观、易分享 | 数据有限 |
最佳实践
- 数据预处理：先清理和验证数据
- 模板设计：创建可复用的报表模板
- 错误处理：添加异常捕获和处理
- 性能优化：大数据量时考虑分批处理
- 版本控制：保存报表生成参数
高级功能扩展
# 添加定时生成
import schedule
import time
def scheduled_report():
print(f"生成定时报表: {datetime.now()}")
manager = ReportManager()
manager.generate_all_reports()
# 每天上午9点生成
schedule.every().day.at("09:00").do(scheduled_report)
while True:
schedule.run_pending()
time.sleep(60)
这个案例涵盖了Python报表生成的各个方面,您可以根据实际需求选择适合的方案。
标签： 数据可视化
