Python怎样构建自动化报表？pandas+openpyxl

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, Border, Side, Alignment
from openpyxl.chart import BarChart, Reference
from openpyxl.utils import get_column_letter

# 1. 模拟数据加载
# 实际中可能是 pd.read_csv('sales_data.csv') 或 pd.read_sql(...)
data = {
    '日期': pd.to_datetime(['2023-01-05', '2023-01-12', '2023-01-18', '2023-02-03', '2023-02-10', '2023-02-25']),
    '产品': ['A', 'B', 'A', 'C', 'B', 'A'],
    '销售额': [1200, 800, 1500, 2000, 900, 1800],
    '区域': ['华东', '华南', '华东', '华北', '华南', '华东']
}
df = pd.DataFrame(data)

# 2. 数据处理与聚合 (pandas的拿手好戏)
# 计算每个月、每个区域的总销售额
df['月份'] = df['日期'].dt.to_period('M')
monthly_regional_sales = df.groupby(['月份', '区域'])['销售额'].sum().unstack(fill_value=0)

# 3. 将数据写入Excel
output_file = '自动化销售报告.xlsx'

# 先用pandas把数据写进去，这是最直接的方式
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='原始数据', index=False)
    monthly_regional_sales.to_excel(writer, sheet_name='月度区域销售', index=True)

# 4. 使用openpyxl进行高级格式化和图表生成
wb = load_workbook(output_file)

# 格式化 '月度区域销售' 工作表
ws_sales = wb['月度区域销售']

# 设置标题行样式
header_font = Font(name='微软雅黑', size=12, bold=True, color='FFFFFF')
header_fill = '4F81BD' # 蓝色背景
for col_idx in range(1, ws_sales.max_column + 1):
    cell = ws_sales.cell(row=1, column=col_idx)
    cell.font = header_font
    cell.fill = openpyxl.styles.PatternFill(start_color=header_fill, end_color=header_fill, fill_type='solid')
    cell.alignment = Alignment(horizontal='center', vertical='center')

# 自动调整列宽
for col in ws_sales.columns:
    max_length = 0
    column = col[0].column # Get the column name
    for cell in col:
        try: # Necessary to avoid error on empty cells
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = (max_length + 2) * 1.2 # Add a little padding
    ws_sales.column_dimensions[get_column_letter(column)].width = adjusted_width

# 添加边框
thin_border = Border(left=Side(style='thin'),
                     right=Side(style='thin'),
                     top=Side(style='thin'),
                     bottom=Side(style='thin'))
for row in ws_sales.iter_rows():
    for cell in row:
        cell.border = thin_border

# 创建柱状图
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = "月度区域销售额对比"
chart.y_axis.title = "销售额"
chart.x_axis.title = "月份"

# 定义数据范围
data_range = Reference(ws_sales, min_col=2, min_row=1, max_col=ws_sales.max_column, max_row=ws_sales.max_row)
# 定义类别标签 (月份)
categories = Reference(ws_sales, min_col=1, min_row=2, max_row=ws_sales.max_row)

chart.add_data(data_range, titles_from_data=True)
chart.set_categories(categories)

# 将图表添加到工作表
ws_sales.add_chart(chart, "A" + str(ws_sales.max_row + 2)) # 放置在数据下方

# 保存最终的Excel文件
wb.save(output_file)
print(f"自动化报告已生成：{output_file}")

DataFrame

groupby()

merge()

join()

to_excel