Excel Studio
by @tobewin
Professional Excel spreadsheet generator. Use when user needs to create data tables, reports, charts, financial models, or any structured data in Excel forma...
clawhub install excel-studio📖 About This Skill
name: excel-studio description: Professional Excel spreadsheet generator. Use when user needs to create data tables, reports, charts, financial models, or any structured data in Excel format. Supports .xlsx, .xls, .csv formats with multi-language, charts, formulas, and formatting. Excel电子表格生成、数据分析、财务报表。 version: 1.0.0 license: MIT-0 metadata: {"openclaw": {"emoji": "📊", "requires": {"bins": ["python3"]}}} dependencies: "pip install openpyxl xlwt"
Excel Studio
Professional Excel spreadsheet generator with full formatting, charts, formulas, and multi-sheet support.
Features
Trigger Conditions
Document Types
Business (商务)
Data Analysis (数据分析)
Financial (财务)
Personal (个人)
Step 1: Understand Requirements
请提供以下信息：文档类型：（报表/数据分析/财务/其他）
格式要求：（xlsx/xls/csv）
Sheet数量：
数据内容：
图表需求：（柱状图/折线图/饼图/无）
语言：（中文/英文）
特殊要求：（公式/条件格式/数据验证等）
Step 2: Generate Excel
Python Script Template
python3 << 'PYEOF'
import os
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, numbers
from openpyxl.chart import BarChart, LineChart, PieChart, Reference
from openpyxl.utils import get_column_letter
from datetime import datetimeclass ExcelGenerator:
def __init__(self):
self.wb = Workbook()
self.sheets = {}
def add_sheet(self, name, active=False):
"""Add a new worksheet"""
if active:
ws = self.wb.active
ws.title = name
else:
ws = self.wb.create_sheet(name)
self.sheets[name] = ws
return ws
def get_sheet(self, name):
"""Get worksheet by name"""
return self.sheets.get(name, self.wb.active)
def set_cell(self, sheet_name, row, col, value, format_type=None):
"""Set cell value with optional formatting"""
ws = self.get_sheet(sheet_name)
cell = ws.cell(row=row, column=col, value=value)
if format_type:
self.apply_format(cell, format_type)
return cell
def apply_format(self, cell, format_type):
"""Apply formatting to cell"""
formats = {
'header': {
'font': Font(bold=True, size=12, color='FFFFFF'),
'fill': PatternFill(start_color='3182CE', end_color='3182CE', fill_type='solid'),
'alignment': Alignment(horizontal='center', vertical='center')
},
'title': {
'font': Font(bold=True, size=16),
'alignment': Alignment(horizontal='center')
},
'number': {
'number_format': '#,##0.00'
},
'percent': {
'number_format': '0.00%'
},
'date': {
'number_format': 'YYYY-MM-DD'
},
'currency': {
'number_format': '¥#,##0.00'
},
'border': {
'border': Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
}
}
if format_type in formats:
for key, value in formats[format_type].items():
setattr(cell, key, value)
def write_table(self, sheet_name, start_row, start_col, headers, data,
header_format=True, border=True):
"""Write a formatted table"""
ws = self.get_sheet(sheet_name)
# Write headers
if headers:
for i, header in enumerate(headers):
cell = ws.cell(row=start_row, column=start_col + i, value=header)
if header_format:
self.apply_format(cell, 'header')
if border:
self.apply_format(cell, 'border')
# Write data
for row_idx, row_data in enumerate(data, start=start_row + 1):
for col_idx, value in enumerate(row_data, start=start_col):
cell = ws.cell(row=row_idx, column=col_idx, value=value)
if border:
self.apply_format(cell, 'border')
# Auto-format numbers
if isinstance(value, (int, float)):
cell.number_format = '#,##0.00'
return start_row, start_col, start_row + len(data), start_col + len(headers) - 1
def add_chart(self, sheet_name, chart_type, data_range, title,
categories_range=None, position='E2'):
"""Add a chart to the sheet"""
ws = self.get_sheet(sheet_name)
charts = {
'bar': BarChart(),
'line': LineChart(),
'pie': PieChart()
}
chart = charts.get(chart_type, BarChart())
chart.title = title
chart.style = 10
# Add data
data = Reference(ws, min_col=data_range[0], min_row=data_range[1],
max_col=data_range[2], max_row=data_range[3])
chart.add_data(data, titles_from_data=True)
# Add categories if provided
if categories_range:
cats = Reference(ws, min_col=categories_range[0],
min_row=categories_range[1],
max_col=categories_range[2],
max_row=categories_range[3])
chart.set_categories(cats)
# Add chart to sheet
ws.add_chart(chart, position)
return chart
def set_column_width(self, sheet_name, col, width):
"""Set column width"""
ws = self.get_sheet(sheet_name)
ws.column_dimensions[get_column_letter(col)].width = width
def auto_column_width(self, sheet_name):
"""Auto-adjust column widths"""
ws = self.get_sheet(sheet_name)
for col in ws.columns:
max_length = 0
column = col[0].column_letter
for cell in col:
try:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
except:
pass
adjusted_width = min(max_length + 2, 50)
ws.column_dimensions[column].width = adjusted_width
def add_formula(self, sheet_name, row, col, formula):
"""Add a formula to a cell"""
ws = self.get_sheet(sheet_name)
ws.cell(row=row, column=col, value=formula)
def merge_cells(self, sheet_name, start_row, start_col, end_row, end_col):
"""Merge cells"""
ws = self.get_sheet(sheet_name)
ws.merge_cells(start_row=start_row, start_column=start_col,
end_row=end_row, end_column=end_col)
def save(self, output_path):
"""Save workbook"""
self.wb.save(output_path)
return output_path
Example: Create a sales report
def create_sales_report():
gen = ExcelGenerator()
# Sheet 1: Sales Data
ws1 = gen.add_sheet('销售数据', active=True)
# Title
gen.merge_cells('销售数据', 1, 1, 1, 6)
title_cell = gen.set_cell('销售数据', 1, 1, '2026年第一季度销售报告')
gen.apply_format(title_cell, 'title')
# Headers
headers = ['月份', '产品A', '产品B', '产品C', '总计', '增长率']
gen.write_table('销售数据', 3, 1, headers, [])
# Data
data = [
['1月', 150000, 230000, 180000, None, None],
['2月', 180000, 250000, 200000, None, None],
['3月', 220000, 280000, 230000, None, None],
]
for i, row in enumerate(data, start=4):
for j, value in enumerate(row, start=1):
gen.set_cell('销售数据', i, j, value)
# Add formulas
for row in range(4, 7):
gen.add_formula('销售数据', row, 5, f'=SUM(B{row}:D{row})')
if row > 4:
gen.add_formula('销售数据', row, 6, f'=(E{row}-E{row-1})/E{row-1}')
# Total row
gen.set_cell('销售数据', 7, 1, '总计')
gen.apply_format(gen.get_sheet('销售数据').cell(row=7, column=1), 'header')
for col in range(2, 6):
gen.add_formula('销售数据', 7, col, f'=SUM({get_column_letter(col)}4:{get_column_letter(col)}6)')
# Auto-width
gen.auto_column_width('销售数据')
# Add chart
gen.add_chart('销售数据', 'bar', (1, 3, 4, 6), '月度销售对比',
categories_range=(1, 4, 1, 6), position='A9')
# Sheet 2: Summary
ws2 = gen.add_sheet('汇总分析')
gen.set_cell('汇总分析', 1, 1, '产品销售占比')
gen.apply_format(gen.get_sheet('汇总分析').cell(row=1, column=1), 'title')
summary_headers = ['产品', '销售额', '占比']
gen.write_table('汇总分析', 3, 1, summary_headers, [])
summary_data = [
['产品A', 550000, None],
['产品B', 760000, None],
['产品C', 610000, None],
]
for i, row in enumerate(summary_data, start=4):
gen.set_cell('汇总分析', i, 1, row[0])
gen.set_cell('汇总分析', i, 2, row[1])
gen.add_formula('汇总分析', i, 3, f'=B{i}/SUM(B4:B6)')
gen.apply_format(gen.get_sheet('汇总分析').cell(row=i, column=3), 'percent')
# Add pie chart
gen.add_chart('汇总分析', 'pie', (1, 3, 2, 6), '产品销售占比',
position='A8')
return genGenerate
output_dir = os.environ.get('OPENCLAW_WORKSPACE', os.getcwd())
output_path = os.path.join(output_dir, 'sales_report.xlsx')gen = create_sales_report()
gen.save(output_path)
print(f"✅ Excel已生成：{output_path}")
print(f"")
print(f"📊 包含内容：")
print(f" - 2个工作表（销售数据/汇总分析）")
print(f" - 专业格式（表头/边框/数字格式）")
print(f" - 自动公式（SUM/增长率）")
print(f" - 柱状图+饼图")
print(f" - 自动列宽调整")
PYEOF
Format Types (格式类型)
Cell Formats
FORMATS = {
# 对齐
'center': Alignment(horizontal='center', vertical='center'),
'left': Alignment(horizontal='left'),
'right': Alignment(horizontal='right'),
# 数字格式
'number': '#,##0.00',
'integer': '#,##0',
'percent': '0.00%',
'currency': '¥#,##0.00',
'date': 'YYYY-MM-DD',
'datetime': 'YYYY-MM-DD HH:MM:SS',
# 字体
'bold': Font(bold=True),
'title': Font(bold=True, size=16),
'header': Font(bold=True, size=12, color='FFFFFF'),
# 填充
'blue_fill': PatternFill(start_color='3182CE', fill_type='solid'),
'green_fill': PatternFill(start_color='48BB78', fill_type='solid'),
'red_fill': PatternFill(start_color='F56565', fill_type='solid'),
'yellow_fill': PatternFill(start_color='ECC94B', fill_type='solid'),
}
Chart Types (图表类型)
| 类型 | 代码 | 适用场景 |
|------|------|----------|
| 柱状图 | bar | 对比数据 |
| 折线图 | line | 趋势分析 |
| 饼图 | pie | 占比分析 |
| 散点图 | scatter | 相关性 |
Formula Support (公式支持)
Common Formulas
# 求和
'=SUM(A1:A10)'平均值
'=AVERAGE(A1:A10)'最大/最小
'=MAX(A1:A10)'
'=MIN(A1:A10)'计数
'=COUNT(A1:A10)'
'=COUNTA(A1:A10)'条件
'=IF(A1>100, "高", "低")'查找
'=VLOOKUP(A1, B:C, 2, FALSE)'百分比变化
'=(B1-A1)/A1'
Multi-Sheet Operations (多Sheet操作)
Cross-Sheet Reference
# 引用其他Sheet的数据
'=Sheet1!A1'跨Sheet求和
'=SUM(Sheet1:Sheet3!A1)'条件汇总
'=SUMIF(Sheet1!A:A, "条件", Sheet1!B:B)'