Excel Studio

Professional Excel spreadsheet generator with full formatting, charts, formulas, and multi-sheet support.

## Features

- 📊 **Multi-Format**: Output as .xlsx, .xls, or .csv
- 📈 **Charts & Graphs**: Bar, line, pie, scatter charts
- 🔢 **Formulas**: SUM, AVERAGE, VLOOKUP, and more
- 🎨 **Professional Formatting**: Colors, fonts, borders, alignment
- 📑 **Multi-Sheet**: Multiple worksheets with cross-references
- 🌍 **Multi-Language**: Chinese, English, Japanese, etc.
- ✅ **Cross-Platform**: Windows, macOS, Linux
- 📱 **Excel Compatible**: Works with Excel, WPS, LibreOffice

## Trigger Conditions

- "帮我做Excel" / "Create an Excel file"
- "生成表格" / "Generate spreadsheet"
- "做数据分析" / "Create data analysis"
- "制作财务报表" / "Create financial report"
- "excel-studio"

## Document Types

### Business (商务)
- 销售报表
- 财务报表
- 预算表
- 库存管理
- 项目跟踪

### Data Analysis (数据分析)
- 数据汇总
- 数据透视
- 趋势分析
- 对比分析

### Financial (财务)
- 资产负债表
- 利润表
- 现金流量表
- 预算对比

### Personal (个人)
- 记账本
- 日程表
- 成绩单
- 清单

---

## Step 1: Understand Requirements

```
请提供以下信息：

文档类型：（报表/数据分析/财务/其他）
格式要求：（xlsx/xls/csv）
Sheet数量：
数据内容：
图表需求：（柱状图/折线图/饼图/无）
语言：（中文/英文）
特殊要求：（公式/条件格式/数据验证等）
```

---

## Step 2: Generate Excel

### Python Script Template

```python
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 datetime

class 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 gen

# Generate
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

```python
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

```python
# 求和
'=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

```python
# 引用其他Sheet的数据
'=Sheet1!A1'

# 跨Sheet求和
'=SUM(Sheet1:Sheet3!A1)'

# 条件汇总
'=SUMIF(Sheet1!A:A, "条件", Sheet1!B:B)'
```

---

## Security Notes

- ✅ No network calls or external endpoints
- ✅ No credentials or API keys required
- ✅ Local file processing only
- ✅ Open source dependencies (openpyxl, xlwt)
- ✅ No data uploaded to external servers

---

## Notes

- 使用openpyxl支持.xlsx格式
- 使用xlwt支持.xls格式
- CSV使用内置csv模块
- 支持中文和其他Unicode字符
- 跨平台兼容Excel/WPS/LibreOffice