xlsx

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() 验证：

   # ... 创建/编辑 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 中计算后将结果硬编码写入。 这样表格才能在源数据变化时自动重算。

❌ 错误做法

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

✅ 正确做法

sheet['B10'] = '=SUM(B2:B9)'
sheet['C5'] = '=(C4-C2)/C2'
sheet['D20'] = '=AVERAGE(D2:D19)'

所有计算——合计、百分比、比率、差值——均适用此原则。

---

多 Sheet 处理复杂任务

当任务涉及多个维度、多个数据集或数据量较大时，将内容合理拆分到多个 Sheet，而非堆在一张表上。每个 Sheet 聚焦单一主题，并在顶部用简短说明交代该 Sheet 的用途与数据范围，帮助读者快速定位所需信息。

---

技术参考

读取与分析数据

使用 pandas（推荐用 jupyter_cell_exec工具）

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 文件

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 文件

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 引擎重算所有公式，并扫描全部单元格检查错误。无需安装任何外部软件。

使用方式

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)

函数签名

recalc(filename: str, timeout: int = 60) -> dict

• filename：xlsx 文件的绝对路径
• timeout：重算超时秒数，默认 60

返回值

{
  "status": "success",
  "total_errors": 0,
  "total_formulas": 42,
  "error_summary": {}
}

若 status 为 errors_found，error_summary 会列出每种错误的位置（最多 20 处）：

{
  "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 网格）

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, , )  

关键点：

• anchor._from.col/row 和 anchor.to.col/row 都是 0-indexed
• 第 1 行 = row = 0，A 列 = col = 0
• 占据行数 = to.row - _from.row
• 不要手动计算每个图表的坐标 — 用 place_chart 函数或等价的公式统一计算，避免算错

图表数据引用（Reference）

关键原则：Reference 的行列范围必须精确匹配数据区域，避免包含多余的表头或空行。

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

饼图特殊说明：

# 饼图通常只有一个数据系列，不需要系列名
# ✅ 正确：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 文件本身：为复杂公式或关键假设添加单元格注释，为硬编码值注明数据来源。