excel-report-generator

Original source / Raw SKILL.md

---
name: excel-report-generator
description: Automatically generate Excel reports from data sources including CSV, databases, or Python data structures. Supports data analysis reports, business reports, data export, and template-based report generation using pandas and openpyxl. Activate when users mention Excel, spreadsheet, report generation, data export, or business reporting.
---

# Excel Report Generator

自动化 Excel 报表生成工具，支持从多种数据源生成专业的 Excel 报告。

## 功能概述

这个 Skill 可以帮助你：
- 📊 从 CSV、数据库或 Python 数据结构生成 Excel 报表
- 📈 创建包含图表、格式化和公式的数据分析报告
- 📋 基于模板填充数据生成业务报告
- 💾 将系统数据批量导出为格式化的 Excel 文件
- 🎨 应用专业的样式、颜色和条件格式

## 核心技术栈

- **pandas**: 数据处理和分析
- **openpyxl**: Excel 文件读写和格式化
- **xlsxwriter**: 高级图表和格式支持（可选）

## 使用场景

### 1. 数据分析报表
从原始数据生成包含统计分析、透视表和可视化图表的综合报告。

**示例请求**:
- "帮我从这个 CSV 生成销售分析报表"
- "创建一个包含月度趋势图的数据分析 Excel"
- "生成带有统计汇总的财务报表"

### 2. 业务报告
定期生成标准化的业务报告，如销售报告、KPI 仪表板等。

**示例请求**:
- "生成本月的销售业绩报告"
- "创建 KPI 跟踪报表"
- "导出季度业务总结 Excel"

### 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: 数据准备
```python
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: 数据处理
```python
# 数据清洗和转换
df_clean = df.dropna()

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

### Step 3: 创建 Excel 文件
```python
# 使用 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: 格式化和样式
```python
# 标题样式
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: 添加图表（可选）
```python
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")
```

## 高级功能

### 条件格式
```python
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)
)
```

### 数据验证
```python
from openpyxl.worksheet.datavalidation import DataValidation

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

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

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

## 最佳实践

### 1. 性能优化
- 对于大数据集（>10万行），使用 `openpyxl` 的 `write_only` 模式
- 分批处理数据，避免内存溢出
- 使用 `xlsxwriter` 引擎处理复杂图表和格式

### 2. 错误处理
```python
try:
    df = pd.read_csv('data.csv')
except FileNotFoundError:
    print("数据文件不存在")
except pd.errors.EmptyDataError:
    print("数据文件为空")
```

### 3. 文件命名规范
```python
from datetime import datetime

# 使用时间戳避免文件覆盖
timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
filename = f'sales_report_{timestamp}.xlsx'
```

### 4. 数据验证
```python
# 检查必需列
required_columns = ['date', 'product', 'sales']
if not all(col in df.columns for col in required_columns):
    raise ValueError(f"缺少必需列: {required_columns}")

# 数据类型验证
df['date'] = pd.to_datetime(df['date'])
df['sales'] = pd.to_numeric(df['sales'], errors='coerce')
```

### 5. 模板管理
- 将常用模板存放在 `~/.claude/skills/excel-report-generator/templates/` 目录
- 使用相对路径引用: `templates/monthly_report.xlsx`
- 保持模板简洁，只包含结构和样式，不包含数据

## 快速参考

### 常用代码片段

查看 `examples/quick_reference.py` 获取常用代码片段，包括：
- 基本 Excel 创建
- 多工作表管理
- 样式和格式化
- 图表创建
- 条件格式
- 公式应用

### 示例文件

- `examples/basic_report.py` - 基础报表生成示例
- `examples/advanced_report.py` - 高级功能示例
- `examples/template_fill.py` - 模板填充示例
- `templates/business_report.xlsx` - 业务报告模板
- `templates/data_analysis.xlsx` - 数据分析模板

## 依赖安装

确保已安装必需的 Python 包：

```bash
pip install pandas openpyxl xlsxwriter
```

可选依赖：
```bash
pip install matplotlib seaborn  # 用于数据可视化
pip install sqlalchemy pymysql  # 用于数据库连接
```

## 故障排查

### 常见问题

**Q: 生成的 Excel 文件无法打开**
A: 确保使用 `.xlsx` 扩展名，检查文件权限，验证数据中没有非法字符

**Q: 图表不显示**
A: 检查数据引用范围是否正确，确保数据类型为数值型

**Q: 中文乱码**
A: 使用 `encoding='utf-8-sig'` 读取 CSV，或在 Excel 中使用 UTF-8 编码

**Q: 大文件生成很慢**
A: 使用 `write_only=True` 模式，减少格式化操作，分批写入数据

## 相关资源

- [pandas 官方文档](https://pandas.pydata.org/docs/)
- [openpyxl 官方文档](https://openpyxl.readthedocs.io/)
- [xlsxwriter 官方文档](https://xlsxwriter.readthedocs.io/)
- 查看 `REFERENCE.md` 获取详细 API 参考

## 版本历史

- v1.0.0 (2025-01-12) - 初始版本，支持基础报表生成和模板填充


---

## Referenced Files

> The following files are referenced in this skill and included for context.

### examples/quick_reference.py

```python
#!/usr/bin/env python3
"""
Excel 快速参考 - 常用代码片段

这个文件包含了 Excel 报表生成中最常用的代码片段，可以直接复制使用
"""

import pandas as pd
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, numbers
from openpyxl.chart import BarChart, LineChart, PieChart, Reference
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, DataBarRule
from openpyxl.utils.dataframe import dataframe_to_rows
from datetime import datetime


# ============================================================================
# 1. 基础操作
# ============================================================================

def snippet_create_workbook():
    """创建新工作簿"""
    wb = Workbook()
    ws = wb.active
    ws.title = "My Sheet"
    wb.save('workbook.xlsx')


def snippet_load_workbook():
    """加载现有工作簿"""
    wb = load_workbook('existing.xlsx')
    ws = wb.active  # 获取活动工作表
    ws = wb['SheetName']  # 按名称获取工作表


def snippet_read_csv_to_excel():
    """CSV 转 Excel"""
    df = pd.read_csv('data.csv')
    df.to_excel('output.xlsx', sheet_name='Data', index=False)


def snippet_read_excel_to_dataframe():
    """Excel 转 DataFrame"""
    df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
    # 或读取多个工作表
    excel_file = pd.ExcelFile('data.xlsx')
    df1 = excel_file.parse('Sheet1')
    df2 = excel_file.parse('Sheet2')


# ============================================================================
# 2. 单元格操作
# ============================================================================

def snippet_write_cell():
    """写入单元格"""
    wb = Workbook()
    ws = wb.active

    # 方式 1: 直接引用
    ws['A1'] = 'Hello'
    ws['B1'] = 123
    ws['C1'] = datetime.now()

    # 方式 2: 使用 cell()
    ws.cell(row=1, column=1, value='Hello')


def snippet_read_cell():
    """读取单元格"""
    wb = load_workbook('data.xlsx')
    ws = wb.active

    # 读取值
    value = ws['A1'].value
    value = ws.cell(row=1, column=1).value


def snippet_write_dataframe():
    """将 DataFrame 写入工作表"""
    df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
    wb = Workbook()
    ws = wb.active

    # 方式 1: 使用 pandas
    with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name='Data', index=False)

    # 方式 2: 使用 openpyxl
    for r in dataframe_to_rows(df, index=False, header=True):
        ws.append(r)


# ============================================================================
# 3. 样式和格式
# ============================================================================

def snippet_font_style():
    """字体样式"""
    ws['A1'].font = Font(
        name='Arial',
        size=12,
        bold=True,
        italic=False,
        color='FF0000'  # 红色
    )


def snippet_fill_color():
    """单元格填充颜色"""
    ws['A1'].fill = PatternFill(
        start_color='FFFF00',  # 黄色
        end_color='FFFF00',
        fill_type='solid'
    )


def snippet_alignment():
    """对齐方式"""
    ws['A1'].alignment = Alignment(
        horizontal='center',  # left, center, right
        vertical='center',    # top, center, bottom
        wrap_text=True        # 自动换行
    )


def snippet_border():
    """边框"""
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    ws['A1'].border = thin_border


def snippet_number_format():
    """数字格式"""
    ws['A1'].number_format = '#,##0.00'  # 千分位，两位小数
    ws['A2'].number_format = '0.00%'     # 百分比
    ws['A3'].number_format = 'yyyy-mm-dd'  # 日期格式
    ws['A4'].number_format = '$#,##0.00'   # 货币格式


def snippet_header_style():
    """标题行完整样式"""
    header_font = Font(bold=True, color='FFFFFF', size=11)
    header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
    header_alignment = Alignment(horizontal='center', vertical='center')

    for cell in ws[1]:  # 第一行
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_alignment


# ============================================================================
# 4. 行列操作
# ============================================================================

def snippet_adjust_column_width():
    """调整列宽"""
    # 固定宽度
    ws.column_dimensions['A'].width = 20

    # 自动调整（基于内容）
    for column in ws.columns:
        max_length = 0
        column_letter = column[0].column_letter
        for cell in column:
            if cell.value:
                max_length = max(max_length, len(str(cell.value)))
        ws.column_dimensions[column_letter].width = max_length + 2


def snippet_adjust_row_height():
    """调整行高"""
    ws.row_dimensions[1].height = 25


def snippet_insert_delete_rows():
    """插入/删除行列"""
    ws.insert_rows(1)  # 在第 1 行前插入一行
    ws.insert_cols(1)  # 在第 1 列前插入一列
    ws.delete_rows(5, 3)  # 从第 5 行开始删除 3 行
    ws.delete_cols(2, 1)  # 删除第 2 列


def snippet_merge_cells():
    """合并单元格"""
    ws.merge_cells('A1:D1')  # 合并 A1 到 D1
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=4)

    # 取消合并
    ws.unmerge_cells('A1:D1')


def snippet_freeze_panes():
    """冻结窗格"""
    ws.freeze_panes = 'A2'  # 冻结第一行
    ws.freeze_panes = 'B1'  # 冻结第一列
    ws.freeze_panes = 'B2'  # 冻结第一行和第一列


# ============================================================================
# 5. 图表
# ============================================================================

def snippet_bar_chart():
    """柱状图"""
    chart = BarChart()
    chart.title = "Sales Chart"
    chart.x_axis.title = "Products"
    chart.y_axis.title = "Sales"

    data = Reference(ws, min_col=2, min_row=1, max_row=10)
    categories = Reference(ws, min_col=1, min_row=2, max_row=10)

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

    ws.add_chart(chart, "E5")


def snippet_line_chart():
    """折线图"""
    chart = LineChart()
    chart.title = "Trend Analysis"
    chart.style = 12

    data = Reference(ws, min_col=2, min_row=1, max_row=10, max_col=3)
    chart.add_data(data, titles_from_data=True)

    ws.add_chart(chart, "E5")


def snippet_pie_chart():
    """饼图"""
    chart = PieChart()
    chart.title = "Market Share"

    data = Reference(ws, min_col=2, min_row=2, max_row=5)
    labels = Reference(ws, min_col=1, min_row=2, max_row=5)

    chart.add_data(data, titles_from_data=False)
    chart.set_categories(labels)

    ws.add_chart(chart, "E5")


# ============================================================================
# 6. 条件格式
# ============================================================================

def snippet_color_scale():
    """色阶条件格式"""
    ws.conditional_formatting.add(
        'B2:B100',
        ColorScaleRule(
            start_type='min', start_color='F8696B',  # 红色
            mid_type='percentile', mid_value=50, mid_color='FFEB84',  # 黄色
            end_type='max', end_color='63BE7B'  # 绿色
        )
    )


def snippet_data_bars():
    """数据条"""
    ws.conditional_formatting.add(
        'C2:C100',
        DataBarRule(
            start_type='min',
            end_type='max',
            color="4472C4"
        )
    )


def snippet_cell_highlight():
    """单元格高亮规则"""
    red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
    green_fill = PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid')

    # 小于 0 的值标红
    ws.conditional_formatting.add(
        'B2:B100',
        CellIsRule(operator='lessThan', formula=['0'], fill=red_fill)
    )

    # 大于 1000 的值标绿
    ws.conditional_formatting.add(
        'B2:B100',
        CellIsRule(operator='greaterThan', formula=['1000'], fill=green_fill)
    )


# ============================================================================
# 7. 公式
# ============================================================================

def snippet_formulas():
    """Excel 公式"""
    ws['D2'] = '=SUM(B2:C2)'  # 求和
    ws['D3'] = '=AVERAGE(B2:B10)'  # 平均值
    ws['D4'] = '=MAX(B2:B10)'  # 最大值
    ws['D5'] = '=MIN(B2:B10)'  # 最小值
    ws['D6'] = '=COUNT(B2:B10)'  # 计数
    ws['D7'] = '=IF(B2>100,"High","Low")'  # 条件判断
    ws['D8'] = '=VLOOKUP(A2,Sheet2!A:B,2,FALSE)'  # 查找


# ============================================================================
# 8. 高级功能
# ============================================================================

def snippet_auto_filter():
    """自动筛选"""
    ws.auto_filter.ref = 'A1:D10'  # 对范围启用筛选
    ws.auto_filter.ref = ws.dimensions  # 对整个数据区域启用筛选


def snippet_data_validation():
    """数据验证（下拉列表）"""
    from openpyxl.worksheet.datavalidation import DataValidation

    dv = DataValidation(type="list", formula1='"优秀,良好,一般,较差"')
    ws.add_data_validation(dv)
    dv.add('E2:E100')


def snippet_hyperlink():
    """超链接"""
    ws['A1'].hyperlink = "https://www.example.com"
    ws['A1'].value = "Click Here"
    ws['A1'].font = Font(color='0000FF', underline='single')


def snippet_named_range():
    """命名范围"""
    from openpyxl.workbook.defined_name import DefinedName

    # 创建命名范围
    wb.create_named_range('SalesData', ws, 'A1:D10')

    # 在公式中使用
    ws['F1'] = '=SUM(SalesData)'


def snippet_protect_sheet():
    """保护工作表"""
    ws.protection.sheet = True
    ws.protection.password = 'mypassword'

    # 允许特定单元格编辑
    ws['A1'].protection = Protection(locked=False)


# ============================================================================
# 9. 多工作表操作
# ============================================================================

def snippet_multiple_sheets():
    """多工作表操作"""
    wb = Workbook()

    # 创建工作表
    ws1 = wb.active
    ws1.title = "Sales"

    ws2 = wb.create_sheet("Summary")
    ws3 = wb.create_sheet("Charts", 0)  # 插入到第一个位置

    # 复制工作表
    ws4 = wb.copy_worksheet(ws1)
    ws4.title = "Sales Copy"

    # 删除工作表
    wb.remove(ws4)

    # 遍历所有工作表
    for sheet in wb:
        print(sheet.title)


# ============================================================================
# 10. 性能优化
# ============================================================================

def snippet_write_only_mode():
    """只写模式（大数据集）"""
    from openpyxl import Workbook

    wb = Workbook(write_only=True)
    ws = wb.create_sheet()

    # 写入数据（只能追加）
    for row in range(100000):
        ws.append([f'Row {row}', row * 2, row * 3])

    wb.save('large_file.xlsx')


def snippet_read_only_mode():
    """只读模式（快速读取）"""
    wb = load_workbook('large_file.xlsx', read_only=True)
    ws = wb.active

    for row in ws.iter_rows(min_row=1, max_row=10):
        for cell in row:
            print(cell.value)


# ============================================================================
# 11. 完整示例模板
# ============================================================================

def complete_example_template():
    """完整的报表生成模板"""
    # 1. 准备数据
    df = pd.DataFrame({
        'Product': ['A', 'B', 'C', 'D', 'E'],
        'Sales': [1000, 1500, 800, 2000, 1200],
        'Profit': [200, 300, 150, 400, 250]
    })

    # 2. 创建 Excel
    output_file = 'complete_report.xlsx'

    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        # 写入数据
        df.to_excel(writer, sheet_name='Data', index=False)

        # 获取对象
        workbook = writer.book
        worksheet = writer.sheets['Data']

        # 3. 格式化标题
        for cell in worksheet[1]:
            cell.font = Font(bold=True, color='FFFFFF')
            cell.fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
            cell.alignment = Alignment(horizontal='center')

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

        # 5. 添加图表
        chart = BarChart()
        chart.title = "Sales Analysis"
        data = Reference(worksheet, min_col=2, min_row=1, max_row=6)
        categories = Reference(worksheet, min_col=1, min_row=2, max_row=6)
        chart.add_data(data, titles_from_data=True)
        chart.set_categories(categories)
        worksheet.add_chart(chart, "E2")

        # 6. 条件格式
        worksheet.conditional_formatting.add(
            'B2:B6',
            ColorScaleRule(start_type='min', start_color='F8696B',
                          end_type='max', end_color='63BE7B')
        )

        # 7. 添加合计行
        last_row = len(df) + 2
        worksheet.cell(row=last_row, column=1, value='TOTAL')
        worksheet.cell(row=last_row, column=2, value=f'=SUM(B2:B{last_row-1})')
        worksheet.cell(row=last_row, column=3, value=f'=SUM(C2:C{last_row-1})')

        for col in range(1, 4):
            worksheet.cell(row=last_row, column=col).font = Font(bold=True)

    print(f"✓ 报表已生成: {output_file}")


# 运行完整示例
if __name__ == "__main__":
    print("Excel 快速参考 - 代码片段集合\n")
    print("每个函数都是独立的代码片段，可以直接复制使用\n")

    # 运行完整示例
    complete_example_template()

```

### examples/basic_report.py

```python
#!/usr/bin/env python3
"""
基础 Excel 报表生成示例

演示如何从 CSV 文件或 DataFrame 生成基本的 Excel 报表
"""

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from datetime import datetime


def create_basic_report(data_source, output_file=None):
    """
    创建基础 Excel 报表

    参数:
        data_source: str 或 DataFrame - CSV 文件路径或 pandas DataFrame
        output_file: str - 输出文件名（可选，默认使用时间戳）

    返回:
        str - 生成的文件路径
    """
    # Step 1: 加载数据
    if isinstance(data_source, str):
        df = pd.read_csv(data_source)
        print(f"✓ 已加载数据文件: {data_source}")
    elif isinstance(data_source, pd.DataFrame):
        df = data_source
        print("✓ 已加载 DataFrame 数据")
    else:
        raise ValueError("数据源必须是 CSV 文件路径或 pandas DataFrame")

    # Step 2: 数据预处理
    print(f"  数据行数: {len(df)}")
    print(f"  数据列数: {len(df.columns)}")

    # Step 3: 生成输出文件名
    if output_file is None:
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        output_file = f'report_{timestamp}.xlsx'

    # Step 4: 创建 Excel 文件
    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        # 写入数据到工作表
        df.to_excel(writer, sheet_name='Data', index=False)

        # 获取 workbook 和 worksheet 对象
        workbook = writer.book
        worksheet = writer.sheets['Data']

        # Step 5: 应用格式
        apply_basic_formatting(worksheet, df)

    print(f"✓ 报表已生成: {output_file}")
    return output_file


def apply_basic_formatting(worksheet, df):
    """应用基础格式到工作表"""

    # 标题行样式
    header_font = Font(bold=True, color='FFFFFF', size=11)
    header_fill = PatternFill(start_color='4472C4', end_color='4472C4', 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 worksheet[1]:
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_alignment
        cell.border = thin_border

    # 应用数据行边框
    for row in worksheet.iter_rows(min_row=2, max_row=worksheet.max_row,
                                   min_col=1, max_col=worksheet.max_column):
        for cell in row:
            cell.border = thin_border
            cell.alignment = Alignment(vertical='center')

    # 自动调整列宽
    for column in worksheet.columns:
        max_length = 0
        column_letter = column[0].column_letter

        for cell in column:
            try:
                if cell.value:
                    max_length = max(max_length, len(str(cell.value)))
            except:
                pass

        adjusted_width = min(max_length + 2, 50)  # 最大宽度限制为 50
        worksheet.column_dimensions[column_letter].width = adjusted_width

    # 冻结首行
    worksheet.freeze_panes = 'A2'

    # 添加自动筛选
    worksheet.auto_filter.ref = worksheet.dimensions


def create_summary_sheet(df, writer, sheet_name='Summary'):
    """
    创建数据汇总工作表

    参数:
        df: DataFrame - 原始数据
        writer: ExcelWriter - Excel 写入器
        sheet_name: str - 工作表名称
    """
    # 生成汇总统计
    summary = df.describe()

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

    # 获取工作表并格式化
    worksheet = writer.sheets[sheet_name]

    # 标题格式
    for cell in worksheet[1]:
        cell.font = Font(bold=True, size=11)
        cell.fill = PatternFill(start_color='E7E6E6', end_color='E7E6E6', fill_type='solid')
        cell.alignment = Alignment(horizontal='center')

    # 第一列格式（统计指标名称）
    for cell in worksheet['A']:
        if cell.row > 1:
            cell.font = Font(bold=True)

    print(f"✓ 已创建汇总工作表: {sheet_name}")


# 示例用法
if __name__ == "__main__":
    # 示例 1: 从 CSV 生成报表
    # create_basic_report('sales_data.csv')

    # 示例 2: 从 DataFrame 生成报表
    sample_data = {
        'Product': ['A', 'B', 'C', 'D', 'E'],
        'Sales': [1000, 1500, 800, 2000, 1200],
        'Profit': [200, 300, 150, 400, 250],
        'Region': ['East', 'West', 'East', 'North', 'South']
    }
    df = pd.DataFrame(sample_data)

    # 生成报表
    output_file = create_basic_report(df, 'sample_report.xlsx')

    # 添加汇总工作表
    with pd.ExcelWriter(output_file, engine='openpyxl', mode='a') as writer:
        create_summary_sheet(df, writer)

    print("\n示例报表生成完成！")
    print("查看文件: sample_report.xlsx")

```

### examples/advanced_report.py

```python
#!/usr/bin/env python3
"""
高级 Excel 报表生成示例

演示高级功能：图表、条件格式、数据透视表、公式等
"""

import pandas as pd
import numpy as np
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, numbers
from openpyxl.chart import BarChart, LineChart, PieChart, Reference
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, DataBarRule
from openpyxl.worksheet.table import Table, TableStyleInfo
from datetime import datetime, timedelta


def create_advanced_report(df, output_file='advanced_report.xlsx'):
    """
    创建包含高级功能的综合报表

    功能包括:
    - 多工作表
    - 图表可视化
    - 条件格式
    - Excel 表格
    - 公式计算
    """
    print(f"正在生成高级报表: {output_file}")

    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        # 1. 原始数据工作表
        df.to_excel(writer, sheet_name='Raw Data', index=False)

        # 2. 数据透视汇总
        create_pivot_summary(df, writer)

        # 3. 趋势分析
        create_trend_analysis(df, writer)

        # 获取 workbook
        workbook = writer.book

        # 4. 添加图表
        add_charts(workbook, df)

        # 5. 应用条件格式
        apply_conditional_formatting(workbook)

        # 6. 创建仪表板
        create_dashboard(workbook, df)

    # 7. 后处理：添加公式
    add_formulas(output_file)

    print(f"✓ 高级报表已生成: {output_file}")
    return output_file


def create_pivot_summary(df, writer):
    """创建数据透视汇总"""
    # 按类别和区域汇总
    if 'Category' in df.columns and 'Region' in df.columns:
        pivot = pd.pivot_table(
            df,
            values='Sales' if 'Sales' in df.columns else df.columns[0],
            index='Category',
            columns='Region',
            aggfunc='sum',
            margins=True,
            margins_name='Total'
        )

        pivot.to_excel(writer, sheet_name='Pivot Summary')

        # 格式化透视表
        ws = writer.sheets['Pivot Summary']
        format_pivot_table(ws)

        print("✓ 已创建数据透视汇总")


def format_pivot_table(worksheet):
    """格式化透视表"""
    # 标题行格式
    for cell in worksheet[1]:
        cell.font = Font(bold=True, size=11)
        cell.fill = PatternFill(start_color='366092', end_color='366092', fill_type='solid')
        cell.font = Font(bold=True, color='FFFFFF')
        cell.alignment = Alignment(horizontal='center')

    # 第一列格式
    for cell in worksheet['A']:
        if cell.row > 1:
            cell.font = Font(bold=True)

    # 合计行格式
    if worksheet.max_row > 1:
        for cell in worksheet[worksheet.max_row]:
            cell.font = Font(bold=True, size=11)
            cell.fill = PatternFill(start_color='D9E1F2', end_color='D9E1F2', fill_type='solid')


def create_trend_analysis(df, writer):
    """创建趋势分析工作表"""
    # 如果有日期列，按日期汇总
    date_columns = df.select_dtypes(include=['datetime64']).columns

    if len(date_columns) > 0:
        date_col = date_columns[0]
        df_trend = df.groupby(pd.Grouper(key=date_col, freq='M')).agg({
            'Sales': ['sum', 'mean', 'count']
        }).reset_index()

        df_trend.to_excel(writer, sheet_name='Trend Analysis', index=False)
        print("✓ 已创建趋势分析")
    else:
        print("⚠ 未找到日期列，跳过趋势分析")


def add_charts(workbook, df):
    """添加各种图表"""
    # 1. 柱状图 - 销售对比
    add_bar_chart(workbook)

    # 2. 折线图 - 趋势
    add_line_chart(workbook)

    # 3. 饼图 - 占比
    add_pie_chart(workbook)

    print("✓ 已添加图表")


def add_bar_chart(workbook):
    """添加柱状图"""
    if 'Pivot Summary' not in workbook.sheetnames:
        return

    ws = workbook['Pivot Summary']

    # 创建柱状图
    chart = BarChart()
    chart.title = "Sales by Category and Region"
    chart.style = 10
    chart.y_axis.title = 'Sales Amount'
    chart.x_axis.title = 'Category'

    # 数据引用（排除合计行）
    data = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row-1, max_col=ws.max_column)
    categories = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row-1)

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

    # 添加图表到工作表
    ws.add_chart(chart, "H2")


def add_line_chart(workbook):
    """添加折线图"""
    if 'Trend Analysis' not in workbook.sheetnames:
        return

    ws = workbook['Trend Analysis']

    chart = LineChart()
    chart.title = "Sales Trend Over Time"
    chart.style = 12
    chart.y_axis.title = 'Sales'
    chart.x_axis.title = 'Date'

    data = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
    dates = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)

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

    ws.add_chart(chart, "F2")


def add_pie_chart(workbook):
    """添加饼图"""
    if 'Pivot Summary' not in workbook.sheetnames:
        return

    ws = workbook['Pivot Summary']

    chart = PieChart()
    chart.title = "Sales Distribution"
    chart.style = 10

    # 使用第一列数据
    data = Reference(ws, min_col=2, min_row=2, max_row=ws.max_row-1)
    labels = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row-1)

    chart.add_data(data, titles_from_data=False)
    chart.set_categories(labels)

    ws.add_chart(chart, "H18")


def apply_conditional_formatting(workbook):
    """应用条件格式"""
    if 'Raw Data' in workbook.sheetnames:
        ws = workbook['Raw Data']

        # 1. 色阶格式 - 销售额
        ws.conditional_formatting.add(
            'B2:B1000',  # 假设销售额在 B 列
            ColorScaleRule(
                start_type='min', start_color='F8696B',
                mid_type='percentile', mid_value=50, mid_color='FFEB84',
                end_type='max', end_color='63BE7B'
            )
        )

        # 2. 数据条 - 可视化数值
        ws.conditional_formatting.add(
            'C2:C1000',  # 假设利润在 C 列
            DataBarRule(
                start_type='min', start_value=0,
                end_type='max', end_value=100,
                color="4472C4"
            )
        )

        # 3. 高亮低于平均值的单元格
        red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
        ws.conditional_formatting.add(
            'B2:B1000',
            CellIsRule(operator='lessThan', formula=['AVERAGE($B$2:$B$1000)'], fill=red_fill)
        )

        print("✓ 已应用条件格式")


def create_dashboard(workbook, df):
    """创建仪表板工作表"""
    # 创建新工作表
    dashboard = workbook.create_sheet('Dashboard', 0)

    # 设置标题
    dashboard['A1'] = 'Business Performance Dashboard'
    dashboard['A1'].font = Font(size=16, bold=True, color='1F4E78')
    dashboard.merge_cells('A1:F1')
    dashboard['A1'].alignment = Alignment(horizontal='center', vertical='center')
    dashboard.row_dimensions[1].height = 30

    # KPI 指标
    kpis = {
        'Total Sales': df['Sales'].sum() if 'Sales' in df.columns else 0,
        'Average Sales': df['Sales'].mean() if 'Sales' in df.columns else 0,
        'Total Orders': len(df),
        'Unique Products': df['Product'].nunique() if 'Product' in df.columns else 0
    }

    row = 3
    for kpi_name, kpi_value in kpis.items():
        # KPI 名称
        dashboard.cell(row=row, column=1, value=kpi_name)
        dashboard.cell(row=row, column=1).font = Font(bold=True, size=12)

        # KPI 值
        dashboard.cell(row=row, column=2, value=kpi_value)
        dashboard.cell(row=row, column=2).font = Font(size=14, color='0070C0')
        dashboard.cell(row=row, column=2).number_format = '#,##0.00'

        row += 1

    # 调整列宽
    dashboard.column_dimensions['A'].width = 20
    dashboard.column_dimensions['B'].width = 15

    print("✓ 已创建仪表板")


def add_formulas(filename):
    """添加 Excel 公式"""
    wb = load_workbook(filename)

    if 'Raw Data' in wb.sheetnames:
        ws = wb['Raw Data']

        # 在最后一行添加合计公式
        last_row = ws.max_row + 2
        ws.cell(row=last_row, column=1, value='TOTAL')
        ws.cell(row=last_row, column=1).font = Font(bold=True, size=12)

        # 销售额合计
        ws.cell(row=last_row, column=2, value=f'=SUM(B2:B{last_row-2})')
        ws.cell(row=last_row, column=2).font = Font(bold=True, size=12)
        ws.cell(row=last_row, column=2).number_format = '#,##0.00'

        # 利润合计
        if ws.max_column >= 3:
            ws.cell(row=last_row, column=3, value=f'=SUM(C2:C{last_row-2})')
            ws.cell(row=last_row, column=3).font = Font(bold=True, size=12)
            ws.cell(row=last_row, column=3).number_format = '#,##0.00'

    wb.save(filename)
    print("✓ 已添加公式")


# 示例用法
if __name__ == "__main__":
    # 生成示例数据
    np.random.seed(42)
    dates = pd.date_range('2024-01-01', periods=100, freq='D')

    sample_data = {
        'Date': dates,
        'Product': np.random.choice(['Product A', 'Product B', 'Product C', 'Product D'], 100),
        'Category': np.random.choice(['Electronics', 'Clothing', 'Food', 'Books'], 100),
        'Region': np.random.choice(['North', 'South', 'East', 'West'], 100),
        'Sales': np.random.randint(100, 2000, 100),
        'Profit': np.random.randint(20, 500, 100),
        'Quantity': np.random.randint(1, 50, 100)
    }

    df = pd.DataFrame(sample_data)

    # 生成高级报表
    create_advanced_report(df, 'advanced_sample_report.xlsx')

    print("\n高级报表生成完成！")
    print("查看文件: advanced_sample_report.xlsx")

```

### examples/template_fill.py

```python
#!/usr/bin/env python3
"""
Excel 模板填充示例

演示如何使用预定义的 Excel 模板并填充动态数据
"""

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from datetime import datetime
import os


def fill_template(template_path, data, output_file=None):
    """
    基于模板填充数据

    参数:
        template_path: str - 模板文件路径
        data: dict - 要填充的数据字典
        output_file: str - 输出文件名

    返回:
        str - 生成的文件路径
    """
    # 检查模板文件是否存在
    if not os.path.exists(template_path):
        raise FileNotFoundError(f"模板文件不存在: {template_path}")

    # 加载模板
    wb = load_workbook(template_path)
    print(f"✓ 已加载模板: {template_path}")

    # 填充数据
    fill_data_cells(wb, data)

    # 生成输出文件名
    if output_file is None:
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        base_name = os.path.splitext(os.path.basename(template_path))[0]
        output_file = f'{base_name}_filled_{timestamp}.xlsx'

    # 保存文件
    wb.save(output_file)
    print(f"✓ 报表已生成: {output_file}")

    return output_file


def fill_data_cells(workbook, data):
    """
    填充数据到工作簿中的命名单元格或指定位置

    数据格式示例:
    data = {
        'Sheet1': {
            'A1': 'Company Name',
            'B5': 12345,
            'named_range': 'Value'
        },
        'Sheet2': {
            'C10': datetime.now()
        }
    }
    """
    for sheet_name, sheet_data in data.items():
        if sheet_name not in workbook.sheetnames:
            print(f"⚠ 工作表 '{sheet_name}' 不存在，跳过")
            continue

        ws = workbook[sheet_name]

        for cell_ref, value in sheet_data.items():
            try:
                # 检查是否是命名范围
                if cell_ref in workbook.defined_names:
                    # 处理命名范围
                    destinations = workbook.defined_names[cell_ref].destinations
                    for title, coord in destinations:
                        ws = workbook[title]
                        ws[coord] = value
                else:
                    # 直接单元格引用
                    ws[cell_ref] = value

                print(f"  ✓ {sheet_name}!{cell_ref} = {value}")
            except Exception as e:
                print(f"  ✗ 填充失败 {sheet_name}!{cell_ref}: {e}")


def fill_template_with_dataframe(template_path, df, sheet_name='Data', start_cell='A1'):
    """
    将 DataFrame 数据填充到模板的指定位置

    参数:
        template_path: str - 模板文件路径
        df: DataFrame - 要填充的数据
        sheet_name: str - 目标工作表名称
        start_cell: str - 起始单元格位置
    """
    wb = load_workbook(template_path)

    if sheet_name not in wb.sheetnames:
        wb.create_sheet(sheet_name)

    ws = wb[sheet_name]

    # 解析起始单元格
    from openpyxl.utils import column_index_from_string, get_column_letter

    start_col = column_index_from_string(start_cell[0])
    start_row = int(start_cell[1:]) if len(start_cell) > 1 else 1

    # 写入列名
    for col_idx, col_name in enumerate(df.columns, start=start_col):
        cell = ws.cell(row=start_row, column=col_idx, value=col_name)
        cell.font = Font(bold=True)
        cell.fill = PatternFill(start_color='D9E1F2', end_color='D9E1F2', fill_type='solid')

    # 写入数据
    for row_idx, row_data in enumerate(df.values, start=start_row + 1):
        for col_idx, value in enumerate(row_data, start=start_col):
            ws.cell(row=row_idx, column=col_idx, value=value)

    print(f"✓ 已将 {len(df)} 行数据填充到 {sheet_name}!{start_cell}")

    timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
    output_file = f'filled_report_{timestamp}.xlsx'
    wb.save(output_file)

    return output_file


def create_invoice_from_template(template_path, invoice_data):
    """
    从模板生成发票

    参数:
        template_path: str - 发票模板路径
        invoice_data: dict - 发票数据
            {
                'invoice_number': 'INV-001',
                'date': datetime.now(),
                'customer_name': 'ABC Company',
                'items': [
                    {'description': 'Product A', 'quantity': 10, 'price': 100},
                    {'description': 'Product B', 'quantity': 5, 'price': 200}
                ]
            }
    """
    wb = load_workbook(template_path)
    ws = wb.active

    # 填充表头信息
    ws['B2'] = invoice_data.get('invoice_number', '')
    ws['B3'] = invoice_data.get('date', datetime.now())
    ws['B4'] = invoice_data.get('customer_name', '')

    # 填充明细项
    items = invoice_data.get('items', [])
    start_row = 7  # 假设明细从第 7 行开始

    for idx, item in enumerate(items):
        row = start_row + idx
        ws.cell(row=row, column=1, value=idx + 1)  # 序号
        ws.cell(row=row, column=2, value=item.get('description', ''))  # 描述
        ws.cell(row=row, column=3, value=item.get('quantity', 0))  # 数量
        ws.cell(row=row, column=4, value=item.get('price', 0))  # 单价
        # 小计公式
        ws.cell(row=row, column=5, value=f'=C{row}*D{row}')

    # 总计公式
    total_row = start_row + len(items) + 1
    ws.cell(row=total_row, column=4, value='Total:')
    ws.cell(row=total_row, column=4).font = Font(bold=True)
    ws.cell(row=total_row, column=5, value=f'=SUM(E{start_row}:E{start_row + len(items) - 1})')
    ws.cell(row=total_row, column=5).font = Font(bold=True)

    # 保存
    invoice_num = invoice_data.get('invoice_number', 'INV')
    output_file = f'Invoice_{invoice_num}.xlsx'
    wb.save(output_file)

    print(f"✓ 发票已生成: {output_file}")
    return output_file


def batch_fill_templates(template_path, data_list):
    """
    批量生成报告

    参数:
        template_path: str - 模板路径
        data_list: list - 数据字典列表

    返回:
        list - 生成的文件路径列表
    """
    output_files = []

    for idx, data in enumerate(data_list, 1):
        print(f"\n正在处理第 {idx}/{len(data_list)} 个报告...")

        # 生成唯一文件名
        identifier = data.get('identifier', f'report_{idx}')
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        output_file = f'{identifier}_{timestamp}.xlsx'

        # 填充模板
        result = fill_template(template_path, data, output_file)
        output_files.append(result)

    print(f"\n✓ 批量生成完成！共生成 {len(output_files)} 个文件")
    return output_files


# 示例用法
if __name__ == "__main__":
    print("Excel 模板填充示例\n")

    # 示例 1: 基本单元格填充
    print("=" * 50)
    print("示例 1: 基本单元格填充")
    print("=" * 50)

    # 注意：需要先创建模板文件
    # template_path = '../templates/business_report.xlsx'

    sample_data = {
        'Sheet1': {
            'B2': 'Annual Sales Report 2024',
            'B4': 'Total Revenue',
            'C4': 1250000,
            'B5': 'Total Orders',
            'C5': 3500,
            'B6': 'Average Order Value',
            'C6': '=C4/C5'  # 公式
        }
    }

    # fill_template(template_path, sample_data, 'filled_report.xlsx')

    # 示例 2: DataFrame 填充
    print("\n" + "=" * 50)
    print("示例 2: DataFrame 数据填充")
    print("=" * 50)

    sales_data = {
        'Product': ['A', 'B', 'C', 'D'],
        'Q1': [1000, 1500, 800, 2000],
        'Q2': [1200, 1400, 900, 2100],
        'Q3': [1100, 1600, 850, 1900],
        'Q4': [1300, 1700, 950, 2200]
    }
    df = pd.DataFrame(sales_data)

    # fill_template_with_dataframe(template_path, df, 'Sales Data', 'A10')

    # 示例 3: 发票生成
    print("\n" + "=" * 50)
    print("示例 3: 发票生成")
    print("=" * 50)

    invoice_data = {
        'invoice_number': 'INV-2024-001',
        'date': datetime.now(),
        'customer_name': 'ABC Corporation',
        'items': [
            {'description': 'Laptop Computer', 'quantity': 5, 'price': 1200},
            {'description': 'Wireless Mouse', 'quantity': 10, 'price': 25},
            {'description': 'USB Cable', 'quantity': 15, 'price': 8}
        ]
    }

    # invoice_template = '../templates/invoice_template.xlsx'
    # create_invoice_from_template(invoice_template, invoice_data)

    # 示例 4: 批量生成
    print("\n" + "=" * 50)
    print("示例 4: 批量生成报告")
    print("=" * 50)

    batch_data = [
        {
            'identifier': 'report_jan',
            'Sheet1': {'B2': 'January Report', 'C4': 100000}
        },
        {
            'identifier': 'report_feb',
            'Sheet1': {'B2': 'February Report', 'C4': 120000}
        },
        {
            'identifier': 'report_mar',
            'Sheet1': {'B2': 'March Report', 'C4': 110000}
        }
    ]

    # batch_fill_templates(template_path, batch_data)

    print("\n" + "=" * 50)
    print("提示：取消注释相关代码并提供模板文件后运行")
    print("=" * 50)

```