Excel 电子表格生成

技能文档

---
name: excel-spreadsheet
title: Excel 电子表格生成
category: 办公效率
description: 使用 openpyxl 生成带样式、公式、图表和数据验证的 Excel .xlsx 文件。当用户需要创建 Excel 文件、生成电子表格、处理报表或添加公式计算时触发。触发词：Excel 生成、电子表格、报表生成、公式计算
---

# Excel 电子表格生成

使用 Python 的 `openpyxl` 库生成支持完整公式的 `.xlsx` 文件。

## 技能工作流

### 步骤1：环境准备

**注意：execute_code 沙箱未安装 openpyxl。** 必须使用专用 Python 虚拟环境 `/home/ubuntu/excel-venv`。

```bash
# 检查虚拟环境是否存在，不存在则创建
python3 -m venv /home/ubuntu/excel-venv 2>/dev/null || true
/home/ubuntu/excel-venv/bin/pip install openpyxl -q

# 使用 heredoc 生成 Excel
/home/ubuntu/excel-venv/bin/python << 'PYEOF'
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
# ... 你的代码 ...
wb.save("/path/to/output.xlsx")
print("Done")
PYEOF
```

### 步骤2：使用常用公式模式

| 用途 | 公式 |
|------|------|
| 总值计算 | `=G2*H2` |
| 单位利润 | `=G2-F2` |
| 利润率 % | `=(G2-F2)/F2` 然后格式化为 `0.0"%"` |
| 状态 LOW/OK | `=IF(H2<I2,"LOW","OK")` |
| 按类别 SUMIF | `=SUMIF('Daftar Barang'!D:D,"Makanan",'Daftar Barang'!J:J)` |
| 状态 COUNTIF | `=COUNTIF(M2:M21,"LOW")` |
| VLOOKUP 查找 | `=VLOOKUP(C2,'Daftar Barang'!B:H,7,FALSE)` |
| 余额累计 | `=IF(E2="Masuk",G2+F2,G2-F2)` |
| 平均值 | `=AVERAGE(G2:G21)` |
| SUMPRODUCT | `=SUMPRODUCT('Daftar Barang'!F2:F21,'Daftar Barang'!H2:H21)` |

### 步骤3：应用样式辅助

```python
header_fill = PatternFill(start_color="1F4E79", end_color="1F4E79", fill_type="solid")
header_font = Font(bold=True, color="FFFFFF")
warning_fill = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid")
success_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")
danger_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),
                     top=Side(style='thin'), bottom=Side(style='thin'))
center = Alignment(horizontal='center', vertical='center')

def style_header(ws, row, cols):
    for c in range(1, cols + 1):
        cell = ws.cell(row=row, column=c)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = center
        cell.border = thin_border

# 自动调整列宽
def auto_width(ws):
    for col in ws.columns:
        max_len = 0
        col_letter = get_column_letter(col[0].column)
        for cell in col:
            try:
                if len(str(cell.value)) > max_len:
                    max_len = len(str(cell.value))
            except: pass
        ws.column_dimensions[col_letter].width = min(max_len + 3, 45)
```

### 步骤4：添加数据验证（下拉列表）

```python
from openpyxl.worksheet.datavalidation import DataValidation
dv = DataValidation(type="list", formula1='"Makanan,Sembako,Protein,Minuman"', allow_blank=True)
ws.add_data_validation(dv)
dv.add(f"D2:D{last_row}")
```

### 步骤5：组织工作表结构

| 工作表 | 用途 |
|--------|------|
| `Daftar Barang` | 主项目列表，含公式（总值、利润、利润率、状态） |
| `Stok Movement` | 出入库交易，含余额累计公式 |
| `History Harga` | 价格变动追踪，含差异/百分比公式 |
| `Dashboard` | 摘要面板，使用 SUMIF/COUNTIF/AVERAGE 引用其他工作表 |
| `Tambah Barang` | 新项目输入表单 |
| `Panduan` | 帮助/文档 |

### 步骤6：设置数字格式

```python
# 货币格式
ws.cell(row=r, column=c).number_format = '#,##0'

# 百分比格式
ws.cell(row=r, column=c).number_format = '0.0"%"'

# 冻结窗格（保持表头可见）
ws.freeze_panes = "A2"
```

## 注意事项

- execute_code 沙箱缺少 openpyxl，使用 `/home/ubuntu/excel-venv`
- 公式字符串必须作为字符串传递：`value=f"=G{row}*H{row}"`
- VLOOKUP 跨工作表引用时，含空格的工作表名必须加引号：`'Daftar Barang'!B:H`
- 颜色填充使用不带 `#` 前缀的十六进制值

使用说明

# Excel 电子表格生成

使用 openpyxl 生成带完整公式支持的 .xlsx 文件。

## 核心能力

- 公式支持：SUMIF、COUNTIF、VLOOKUP、IF 等
- 样式：表头、边框、条件填充、自动列宽
- 数据验证：下拉列表
- 数字格式：货币、百分比、冻结窗格

## 环境要求

execute_code 沙箱未安装 openpyxl，必须使用专用虚拟环境：

```bash
python3 -m venv /home/ubuntu/excel-venv 2>/dev/null || true
/home/ubuntu/excel-venv/bin/pip install openpyxl -q
/home/ubuntu/excel-venv/bin/python your_script.py
```

## 推荐工作表结构

| 工作表 | 用途 |
|--------|------|
| Daftar Barang | 主项目列表，含公式 |
| Stok Movement | 出入库交易，余额累计 |
| Dashboard | 摘要面板，跨表引用 |

## 注意事项

- 公式以字符串传递：`value=f"=G{row}*H{row}"`
- 跨表 VLOOKUP 需引号包裹工作表名
- 颜色填充使用无 `#` 前缀的十六进制值

如何安装此技能？

访问技能市场，点击「安装」按钮，按提示将技能包放入 AI 编程助手的 skills 目录即可。