Automated Excel report generation tool that supports generating professional Excel reports from multiple data sources.
Feature Overview
This Skill can help you:
- 📊 Generate Excel reports from CSV, databases, or Python data structures
- 📈 Create data analysis reports containing charts, formatting, and formulas
- 📋 Generate business reports by filling data based on templates
- 💾 Batch export system data to formatted Excel files
- 🎨 Apply professional styles, colors, and conditional formatting
Core Tech Stack
- pandas: Data processing and analysis
- openpyxl: Excel file reading, writing, and formatting
- xlsxwriter: Advanced charts and format support (optional)
Use Cases
1. Data Analysis Reports
Generate comprehensive reports containing statistical analysis, pivot tables, and visualization charts from raw data.
Example Requests:
- "Help me generate a sales analysis report from this CSV"
- "Create a data analysis Excel containing monthly trend charts"
- "Generate a financial report with statistical summaries"
2. Business Reports
Regularly generate standardized business reports, such as sales reports, KPI dashboards, etc.
Example Requests:
- "Generate this month's sales performance report"
- "Create a KPI tracking report"
- "Export a quarterly business summary Excel"
3. Data Export
Export database query results or system data to formatted Excel files.
Example Requests:
- "Export user data to Excel"
- "Save database query results as an Excel file"
- "Export datasets from multiple worksheets"
4. Template Filling
Fill dynamic data based on predefined Excel templates.
Example Requests:
- "Use this template to generate a report"
- "Fill in the data in the Excel template"
- "Batch generate invoices based on templates"
How to Use
Basic Workflow
- Prepare data source: CSV file, pandas DataFrame, database connection, or Python dictionary
- Define report requirements: Describe the required format, charts, and styles
- Generate report: Automatically create a formatted Excel file
- Validate output: Check whether the generated file meets the requirements
Command Examples
Generate a report from CSV:
请从 sales_data.csv 生成一个销售分析报表，包含：
- 按产品分类的销售汇总
- 月度销售趋势图
- Top 10 产品排名
Generate a report from DataFrame:
我有一个 pandas DataFrame，帮我生成 Excel 报表，包括：
- 数据透视表
- 条件格式高亮异常值
- 自动筛选和冻结首行
Use a template:
基于 templates/monthly_report.xlsx 模板，填充当月数据并生成报告
Implementation Guide
When a user requests Excel report generation, follow these steps:
Step 1: Data Preparation
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: Data Processing
# 数据清洗和转换
df_clean = df.dropna()
# 统计分析
summary = df.groupby('category').agg({
'sales': ['sum', 'mean', 'count'],
'profit': 'sum'
})
Step 3: Create Excel File
# 使用 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: Formatting and Styling
# 标题样式
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: Add Charts (Optional)
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")
Advanced Features
Conditional Formatting
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)
)
Data Validation
from openpyxl.worksheet.datavalidation import DataValidation
# 下拉列表
dv = DataValidation(type="list", formula1='"优秀,良好,一般,较差"', allow_blank=True)
worksheet.add_data_validation(dv)
dv.add('D2:D100')
Formula Application
# 添加求和公式
worksheet['B11'] = '=SUM(B2:B10)'
# 添加平均值公式
worksheet['C11'] = '=AVERAGE(C2:C10)'
Best Practices
1. Performance Optimization
- For large datasets (>100,000 rows), use
openpyxlthewrite_onlymode - Process data in batches to avoid memory overflow
- Use
xlsxwriterengine to handle complex charts and formatting
2. Error Handling
try:
df = pd.read_csv('data.csv')
except FileNotFoundError:
print("数据文件不存在")
except pd.errors.EmptyDataError:
print("数据文件为空")
3. File Naming Conventions
from datetime import datetime
# 使用时间戳避免文件覆盖
timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
filename = f'sales_report_{timestamp}.xlsx'
4. Data Validation
# 检查必需列
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. Template Management
- Store commonly used templates in the
~/.claude/skills/excel-report-generator/templates/directory - Use relative paths for references:
templates/monthly_report.xlsx - Keep templates simple, containing only structure and styles, not data
Quick Reference
Common Code Snippets
See examples/quick_reference.py for common code snippets, including:
- Basic Excel creation
- Multiple worksheet management
- Styles and formatting
- Chart creation
- Conditional formatting
- Formula application
Example Files
examples/basic_report.py- Basic report generation exampleexamples/advanced_report.py- Advanced features exampleexamples/template_fill.py- Template filling exampletemplates/business_report.xlsx- Business report templatetemplates/data_analysis.xlsx- Data analysis template
Dependency Installation
Make sure the required Python packages are installed:
pip install pandas openpyxl xlsxwriter
Optional dependencies:
pip install matplotlib seaborn # 用于数据可视化
pip install sqlalchemy pymysql # 用于数据库连接
Troubleshooting
Common Issues
Q: The generated Excel file cannot be opened
A: Make sure to use the .xlsx extension, check file permissions, and verify that the data contains no illegal characters
Q: Charts do not display A: Check whether the data reference range is correct, and make sure the data type is numeric
Q: Chinese characters are garbled
A: Use encoding='utf-8-sig' to read CSV, or use UTF-8 encoding in Excel
Q: Generating large files is very slow
A: Use write_only=True mode, reduce formatting operations, and write data in batches
Related Resources
- pandas official documentation
- openpyxl official documentation
- xlsxwriter official documentation
- See
REFERENCE.mdfor detailed API reference
Version History
- v1.0.0 (2025-01-12) - Initial version, supporting basic report generation and template filling