back
loading skill details...
创建、编辑或分析 .xlsx / .xlsm / .csv / .tsv 文件。当用户要求生成、处理、下载 Excel 表格，或提及表格文件名/路径并希望对其操作时，使用本技能。包括：打开、读取、编辑、修复已有文件（增列、计算、格式化、图表、数据清洗），从零或其他数据源创建新表格，以及表格格式间的转换。对于结构混...
---
name: xlsx
description: 创建、编辑或分析 .xlsx / .xlsm / .csv / .tsv 文件。当用户要求生成、处理、下载 Excel 表格，或提及表格文件名/路径并希望对其操作时，使用本技能。包括：打开、读取、编辑、修复已有文件（增列、计算、格式化、图表、数据清洗），从零或其他数据源创建新表格，以及表格格式间的转换。对于结构混乱的表格数据（错行、表头错位、垃圾数据）清洗为规范表格也适用。交付物须为表格文件；当用户未指定格式时默认生成 .xlsx 而非 .csv 等其他格式。若主要产出为 Word 文档、HTML 报告、独立 Python 脚本或数据库管道，则不应触发本技能。
---
# 核心原则
每份交付的 Excel 文件须同时满足以下四点：
1. **零公式错误**：不得出现 `#REF!` `#DIV/0!` `#VALUE!` `#N/A` `#NAME?`
2. **视觉美观**：使用统一的专业字体（如 Arial、微软雅黑），表头与数据区层次分明，数字格式统一，列宽适配内容，整体风格协调
3. **图表配套**：数据中存在对比、趋势或占比维度时，配套生成相应图表（折线图 / 柱状图 / 饼图等），提升可阅读性
4. **保留已有模板**：修改现有文件时，必须完全匹配原有格式、样式和约定，绝不覆盖已有样式，现有约定优先于本指南
5. **区分任务类型，保护原表数据**：先判断用户意图——若是多个文件汇总为一个新表，可创建新文件；若是基于现有文件进行处理（如分析、汇总、增加图表），则必须在原文件基础上操作，保留所有原始 Sheet 及其数据，将结果写入新增的 Sheet，不得丢弃或覆盖原始数据
---
# 工作流
## 工具使用规则
所有 Python 代码统一通过 **`jupyter_cell_exec`工具** 执行（用户每次提问时自动启动新内核）。输出文件保存到工作目录（`OUTPUT_ROOT`）下。注意：用户每发起新提问时 Jupyter 环境会重置，变量与状态仅在当轮多次调用之间保留。
## 执行步骤
1. **规划结构**：确定 Sheet 划分、数据布局、需要生成的图表类型
2. **选择库**：数据分析用 pandas，公式 / 格式 / 图表处理用 openpyxl
3. **编写并执行代码**：通过 `jupyter_cell_exec`工具 在**同一个代码块**中完成文件创建与公式重算。使用了公式时，必须在保存文件后紧接着调用 `recalc()` 验证：
```python
# ... 创建/编辑 Excel 文件的代码 ...
wb.save(output_path)
# 重算公式并验证（使用了公式时必须执行）
import sys, os
sys.path.insert(0, os.path.join(os.getenv('skill_path'), 'xlsx', 'scripts'))
from recalc import recalc
result = recalc(output_path)
print(result)
```
- 函数签名：`recalc(filename: str, timeout: int = 60) -> dict`
- 纯 Python 重算所有公式，并扫描全部单元格检查错误（无需安装外部软件）
4. **验证与修复**：
- 若返回 `status: "errors_found"`，根据 `error_summary` 中的位置修复后再次调用 `recalc()`
- 常见错误：`#REF!`（无效引用）、`#DIV/0!`（除以零）、`#VALUE!`（数据类型错误）、`#NAME?`（未识别的公式名）
---
# 关键原则：使用公式，不要硬编码值
**始终使用 Excel 公式，而非在 Python 中计算后将结果硬编码写入。** 这样表格才能在源数据变化时自动重算。
### ❌ 错误做法
```python
total = df['Sales'].sum()
sheet['B10'] = total # 硬编码为 5000
growth = (df.iloc[-1]['Revenue'] - df.iloc[0]['Revenue']) / df.iloc[0]['Revenue']
sheet['C5'] = growth # 硬编码为 0.15
```
### ✅ 正确做法
```python
sheet['B10'] = '=SUM(B2:B9)'
sheet['C5'] = '=(C4-C2)/C2'
sheet['D20'] = '=AVERAGE(D2:D19)'
```
所有计算——合计、百分比、比率、差值——均适用此原则。
---
# 多 Sheet 处理复杂任务
当任务涉及多个维度、多个数据集或数据量较大时，将内容合理拆分到多个 Sheet，而非堆在一张表上。每个 Sheet 聚焦单一主题，并在顶部用简短说明交代该 Sheet 的用途与数据范围，帮助读者快速定位所需信息。
---
# 技术参考
## 读取与分析数据
### 使用 pandas（推荐用 jupyter_cell_exec工具）
```python
import pandas as pd
df = pd.read_excel('file.xlsx')
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)
df.head()
df.info()
df.describe()
df.to_excel('output.xlsx', index=False)
```
## 创建新 Excel 文件
```python
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
wb = Workbook()
sheet = wb.active
sheet['A1'] = 'Hello'
sheet['B1'] = 'World'
sheet.append(['Row', 'of', 'data'])
sheet['B2'] = '=SUM(A1:A10)'
sheet['A1'].font = Font(bold=True, color='FF0000')
sheet['A1'].fill = PatternFill('solid', start_color='FFFF00')
sheet['A1'].alignment = Alignment(horizontal='center')
sheet.column_dimensions['A'].width = 20
wb.save('output.xlsx')
```
## 编辑现有 Excel 文件
```python
from openpyxl import load_workbook
wb = load_workbook('existing.xlsx')
sheet = wb.active
sheet['A1'] = 'New Value'
sheet.insert_rows(2)
sheet.delete_cols(3)
new_sheet = wb.create_sheet('NewSheet')
new_sheet['A1'] = 'Data'
wb.save('modified.xlsx')
```
## 重算公式
openpyxl 写入的公式只是字符串，Excel 打开前不会有计算值。通过 `recalc` 模块使用纯 Python 引擎重算所有公式，并扫描全部单元格检查错误。无需安装任何外部软件。
### 使用方式
```python
import sys, os
sys.path.insert(0, os.path.join(os.getenv('skill_path'), 'xlsx', 'scripts'))
from recalc import recalc
result = recalc("/path/to/output.xlsx")
print(result)
```
### 函数签名
```python
recalc(filename: str, timeout: int = 60) -> dict
```
- `filename`：xlsx 文件的绝对路径
- `timeout`：重算超时秒数，默认 60
### 返回值
```json
{
"status": "success",
"total_errors": 0,
"total_formulas": 42,
"error_summary": {}
}
```
若 `status` 为 `errors_found`，`error_summary` 会列出每种错误的位置（最多 20 处）：
```json
{
"status": "errors_found",
"total_errors": 2,
"total_formulas": 42,
"error_summary": {
"#REF!": {
"count": 2,
"locations": ["Sheet1!B5", "Sheet1!C10"]
}
}
}
```
### 支持的函数范围
recalc 使用纯 Python 公式引擎，覆盖大多数常用 Excel 函数：
- 数学：SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, ROUND, ABS, MOD
- 逻辑：IF, AND, OR, NOT, IFERROR
- 查找：VLOOKUP, HLOOKUP, INDEX, MATCH, OFFSET
- 条件：SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF
- 文本：LEFT, RIGHT, MID, LEN, CONCATENATE, TRIM, UPPER, LOWER
- 日期：DATE, YEAR, MONTH, DAY, TODAY, NOW
- 财务：NPV, IRR, PMT, FV, PV
若遇到不支持的函数，recalc 会降级为静态分析（检查引用和结构），并在返回值中包含 `warning` 字段说明情况。
## 公式验证清单
### 必要验证
- [ ] 先在 2-3 个单元格上测试公式，确认值正确后再批量应用
- [ ] 列映射：确认 Excel 列对应关系（第 64 列 = BL）
- [ ] 行偏移：Excel 行从 1 开始（DataFrame 第 5 行 = Excel 第 6 行）
- [ ] 验证公式引用的所有单元格确实存在
### 常见陷阱
- [ ] NaN 处理：用 `pd.notna()` 检查空值
- [ ] 远右侧列：财年数据常在第 50+ 列
- [ ] 多重匹配：搜索所有匹配项而非只取第一个
- [ ] 除以零：在公式中使用 `/` 前检查分母（`#DIV/0!`）
- [ ] 跨表引用：使用正确格式（`Sheet1!A1`）
- [ ] 边界测试：包含零值、负数和超大数值的场景
---
# 财务模型规范
### 颜色编码（除非用户或现有模板另有说明）
- **蓝色文字（RGB: 0,0,255）**：硬编码输入值
- **黑色文字（RGB: 0,0,0）**：所有公式和计算结果
- **绿色文字（RGB: 0,128,0）**：同一工作簿内跨工作表的引用链接
- **红色文字（RGB: 255,0,0）**：链接到其他文件的外部引用
- **黄色背景（RGB: 255,255,0）**：关键假设或需要更新的单元格
### 数字格式
- **年份**：文本字符串（如 "2024" 而非 "2,024"）
- **货币**：`$#,##0`，表头注明单位（如 "Revenue ($mm)"）
- **零值**：显示为 "-"（格式：`$#,##0;($#,##0);-`）
- **百分比**：`0.0%`
- **倍数**：`0.0x`
- **负数**：括号形式 `(123)`
### 公式构建
- 将所有假设放在独立单元格，公式引用单元格而非硬编码：`=B5*(1+$B$6)` 而非 `=B5*1.05`
- 修改现有模板时，必须完全匹配原有格式、样式和约定，现有约定优先于本指南
### 硬编码值来源文档
- 所有硬编码数值须标注来源，格式：`Source: [系统/文档], [日期], [具体引用], [URL（如有）]`
- 示例：
- `Source: 公司年报, FY2024, 第45页, 营收附注`
- `Source: Bloomberg Terminal, 2025/8/15, AAPL US Equity`
- `Source: Wind, 2025/8/20, 一致预期数据`
### 图表布局（TwoCellAnchor 网格系统）
使用 `TwoCellAnchor` 精确定位，**必须遵循网格布局规则防止重叠**。
#### 网格布局规则（必须遵守）
将图表区域看作网格，先确定布局参数，再算每个图表的坐标：
```
布局参数（先确定再写代码）:
DATA_END_ROW = 数据区最后一行（0-indexed）
CHART_START = DATA_END_ROW + 2（图表起始行，留 1 行缓冲）
COLS_PER_CHART = 7（每个图表占的列数，推荐 6-8）
ROWS_PER_CHART = 15（每个图表占的行数，推荐 14-16）
GAP_COLS = 1（列间距，必须 ≥ 1）
GAP_ROWS = 2（行间距，必须 ≥ 2）
网格坐标计算公式:
第 i 行第 j 列图表（i, j 从 0 开始）:
from_col = j * (COLS_PER_CHART + GAP_COLS)
from_row = CHART_START + i * (ROWS_PER_CHART + GAP_ROWS)
to_col = from_col + COLS_PER_CHART
to_row = from_row + ROWS_PER_CHART
```
**核心规则**：
1. 相邻图表的坐标范围**绝不允许重叠** — 即一个图表的 `to_col` 必须 ≤ 下一列图表的 `from_col`，`to_row` 必须 ≤ 下一行图表的 `from_row`
2. **图表区域内禁止存放任何数据** — 图表网格占据的整个矩形区域（从 `CHART_START` 行、第 0 列开始，到最后一个图表的 `to_row`、`to_col`）内不得写入辅助数据
3. 图表需要的辅助数据（如分布统计、饼图源数据等）必须放在**图表区域之外**：写在主数据表的右侧空列（确保不与图表列重叠），或写在单独的 sheet 中
#### 代码模板（6 个图表 2×3 网格）
```python
from openpyxl.chart import BarChart, PieChart, LineChart
from openpyxl.drawing.spreadsheet_drawing import TwoCellAnchor
# 1. 确定布局参数
CHART_START = 19 # 数据区结束后 +2
COLS = 7 # 每图表占 7 列
ROWS = 15 # 每图表占 15 行
GAP_C = 1 # 列间距（图表之间留 1 列空白）
GAP_R = 2 # 行间距（图表之间留 2 行空白）
def place_chart(ws, chart, grid_row, grid_col):
"""将图表放入网格的 (grid_row, grid_col) 位置，0-indexed"""
a = TwoCellAnchor()
a._from.col = grid_col * (COLS + GAP_C)
a._from.row = CHART_START + grid_row * (ROWS + GAP_R)
a.to.col = a._from.col + COLS
a.to.row = a._from.row + ROWS
chart.anchor = a
ws._charts.append(chart)
# 2. 创建图表并放入网格（图表之间自动留白）
# 第 0 行: chart1(0,0) [1列空白] chart2(0,1)
# [2行空白]
# 第 1 行: chart3(1,0) [1列空白] chart4(1,1)
# [2行空白]
# 第 2 行: chart5(2,0) [1列空白] chart6(2,1)
place_chart(ws, chart1, 0, 0) # A20:G34
place_chart(ws, chart2, 0, 1) # I20:O34 (H列空白)
place_chart(ws, chart3, 1, 0) # A37:G51 (35-36行空白)
place_chart(ws, chart4, 1, 1) # I37:O51
place_chart(ws, chart5, 2, 0) # A54:G68
place_chart(ws, chart6, 2, 1) # I54:O68
```
**关键点**：
- `anchor._from.col/row` 和 `anchor.to.col/row` 都是 **0-indexed**
- 第 1 行 = `row = 0`，A 列 = `col = 0`
- 占据行数 = `to.row - _from.row`
- **不要手动计算每个图表的坐标** — 用 `place_chart` 函数或等价的公式统一计算，避免算错
#### 图表数据引用（Reference）
**关键原则**：`Reference` 的行列范围必须精确匹配数据区域，避免包含多余的表头或空行。
```python
from openpyxl.chart import Reference
# 假设数据结构：
# A1: 姓名 B1: 语文 C1: 数学 D1: 英语 E1: 总分
# A2: 张三 B2: 85 C2: 90 D2: 88 E2: 263
# A3: 李四 B3: 78 C3: 82 D3: 85 E3: 245
# ... (共 8 行数据，A2:E9)
# ✅ 正确：柱状图显示总分
data = Reference(ws, min_col=5, min_row=1, max_row=9) # E1:E9（包含表头"总分"）
categories = Reference(ws, min_col=1, min_row=2, max_row=9) # A2:A9（学生姓名，不含表头）
chart.add_data(data, titles_from_data=True) # titles_from_data=True 会把 E1 作为系列名
chart.set_categories(categories)
# ✅ 正确：折线图显示三科成绩
data = Reference(ws, min_col=2, max_col=4, min_row=1, max_row=9) # B1:D9（包含表头）
categories = Reference(ws, min_col=1, min_row=2, max_row=9) # A2:A9（学生姓名）
chart.add_data(data, titles_from_data=True) # B1:D1 作为系列名（语文、数学、英语）
chart.set_categories(categories)
# ❌ 错误：data 包含了姓名列
data = Reference(ws, min_col=1, max_col=4, min_row=1, max_row=9) # 错误地包含了 A 列
chart.add_data(data, titles_from_data=True) # 会把"姓名"也当成数据系列
# ❌ 错误：categories 包含了表头
categories = Reference(ws, min_col=1, min_row=1, max_row=9) # 错误地包含了 A1
chart.set_categories(categories) # 横轴会显示"姓名 张三 李四..."
```
**关键点**：
- `min_row=1` 且 `titles_from_data=True` → 第 1 行作为系列名（图例）
- `min_row=2` → 从第 2 行开始取数据
- `categories` 通常不包含表头（`min_row=2`）
- `data` 包含表头时设置 `titles_from_data=True`
**饼图特殊说明**：
```python
# 饼图通常只有一个数据系列，不需要系列名
# ✅ 正确：data 和 categories 都不包含表头
data = Reference(ws, min_col=2, min_row=2, max_row=5) # B2:B5（数值）
categories = Reference(ws, min_col=1, min_row=2, max_row=5) # A2:A5（分类名）
pie_chart.add_data(data, titles_from_data=False) # 饼图不需要系列名
pie_chart.set_categories(categories)
# ❌ 错误：data 包含表头会导致标签显示 "表头名, 分类名, 值, 百分比"
data = Reference(ws, min_col=2, min_row=1, max_row=5) # B1:B5（包含表头）
pie_chart.add_data(data, titles_from_data=True) # 错误！饼图标签会变成 "人数, 90分以上, 10, 100%"
```
#### ⚠️ 禁止使用 `add_chart` + `width/height`
不要使用 `ws.add_chart(chart, 'A3')` + `chart.width/height`，该方案的单位是 cm，极易算错导致重叠。**始终使用上述 TwoCellAnchor 网格系统。**
---
# 最佳实践
### 库的选择
- **pandas**：数据分析、批量操作、简单数据导出
- **openpyxl**：复杂格式、公式、Excel 特有功能
### openpyxl 注意事项
- 单元格索引从 1 开始（row=1, column=1 对应 A1）
- `data_only=True` 读取已计算的值；以此模式保存会永久丢失公式
- 大文件：读取用 `read_only=True`，写入用 `write_only=True`
### pandas 注意事项
- 指定数据类型：`pd.read_excel('file.xlsx', dtype={'id': str})`
- 只读取需要的列：`pd.read_excel('file.xlsx', usecols=['A', 'C', 'E'])`
- 日期处理：`pd.read_excel('file.xlsx', parse_dates=['date_column'])`
## 代码风格规范
**Python 脚本**：编写简洁代码，避免多余注释、冗长变量名和不必要的 print。
**Excel 文件本身**：为复杂公式或关键假设添加单元格注释，为硬编码值注明数据来源。
don't have the plugin yet? install it then click "run inline in claude" again.