Excel Report Generator

3. 数据导出

将数据库查询结果或系统数据导出为格式化的 Excel 文件。

示例请求:

• "把用户数据导出到 Excel"
• "将数据库查询结果保存为 Excel 文件"
• "导出多个工作表的数据集"

4. 模板填充

基于预定义的 Excel 模板填充动态数据。

示例请求:

• "使用这个模板生成报告"
• "填充 Excel 模板中的数据"
• "批量生成基于模板的发票"

使用方法

基本工作流程

1. 准备数据源: CSV 文件、pandas DataFrame、数据库连接或 Python 字典
2. 定义报表需求: 描述所需的格式、图表、样式
3. 生成报表: 自动创建格式化的 Excel 文件
4. 验证输出: 检查生成的文件是否符合要求

命令示例

从 CSV 生成报表:

请从 sales_data.csv 生成一个销售分析报表，包含：
- 按产品分类的销售汇总
- 月度销售趋势图
- Top 10 产品排名

从 DataFrame 生成报表:

我有一个 pandas DataFrame，帮我生成 Excel 报表，包括：
- 数据透视表
- 条件格式高亮异常值
- 自动筛选和冻结首行

使用模板:

基于 templates/monthly_report.xlsx 模板，填充当月数据并生成报告

实现指南

当用户请求生成 Excel 报表时，遵循以下步骤：

Step 1: 数据准备

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils.dataframe import dataframe_to_rows

# 读取数据
df = pd.read_csv('data.csv')
# 或从数据库
# df = pd.read_sql(query, connection)

Step 2: 数据处理

# 数据清洗和转换
df_clean = df.dropna()

# 统计分析
summary = df.groupby('category').agg({
    'sales': ['sum', 'mean', 'count'],
    'profit': 'sum'
})

Step 3: 创建 Excel 文件

# 使用 pandas ExcelWriter
with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
    # 写入原始数据
    df_clean.to_excel(writer, sheet_name='Raw Data', index=False)

    # 写入汇总数据
    summary.to_excel(writer, sheet_name='Summary')

    # 获取 workbook 进行格式化
    workbook = writer.book
    worksheet = writer.sheets['Summary']

Step 4: 格式化和样式

# 标题样式
header_font = Font(bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')

# 应用样式到标题行
for cell in worksheet[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal='center')

# 列宽自动调整
for column in worksheet.columns:
    max_length = 0
    column_letter = column[0].column_letter
    for cell in column:
        if len(str(cell.value)) > max_length:
            max_length = len(str(cell.value))
    worksheet.column_dimensions[column_letter].width = max_length + 2

Step 5: 添加图表（可选）

from openpyxl.chart import BarChart, Reference

# 创建图表
chart = BarChart()
chart.title = "Sales by Category"
chart.x_axis.title = "Category"
chart.y_axis.title = "Sales"

# 数据引用
data = Reference(worksheet, min_col=2, min_row=1, max_row=10)
categories = Reference(worksheet, min_col=1, min_row=2, max_row=10)

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

# 添加到工作表
worksheet.add_chart(chart, "E5")

高级功能

条件格式

from openpyxl.formatting.rule import ColorScaleRule, CellIsRule

# 色阶格式
worksheet.conditional_formatting.add(
    'B2:B100',
    ColorScaleRule(start_type='min', start_color='FF6347',
                   mid_type='percentile', mid_value=50, mid_color='FFFF00',
                   end_type='max', end_color='90EE90')
)

# 基于规则的格式
red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
worksheet.conditional_formatting.add(
    'C2:C100',
    CellIsRule(operator='lessThan', formula=['0'], fill=red_fill)
)

数据验证

from openpyxl.worksheet.datavalidation import DataValidation

# 下拉列表
dv = DataValidation(type="list", formula1='"优秀,良好,一般,较差"', allow_blank=True)
worksheet.add_data_validation(dv)
dv.add('D2:D100')

公式应用

# 添加求和公式
worksheet['B11'] = '=SUM(B2:B10)'

# 添加平均值公式
worksheet['C11'] = '=AVERAGE(C2:C10)'

最佳实践

1. 性能优化

• 对于大数据集（>10万行），使用 openpyxl 的 write_only 模式
• 分批处理数据，避免内存溢出
• 使用 xlsxwriter 引擎处理复杂图表和格式

2. 错误处理