Excel大师

Excel大师是面向批量表格处理的能力包。它不只罗列脚本，更解决四个高频痛点： 大xlsx一加载就内存爆炸、用pandas读写后格式公式全丢失、长数字变成科学计数法、 data_only=True拿到公式却是None。 核心能力： - 文件规模分层：小文件(<10MB)用pandas、中文件(10-100MB)用openpyxl流式、大文件(>100MB)分块 - 格式保留矩阵：明确哪些操作会丢格式、哪些能保留，给出"两条路径"选择 - 陷阱速查：data_only陷阱、科学计数法陷阱、CSV编码陷阱、合并单元格陷阱 - 16个可执行脚本：合并/拆分/筛选/聚合/校验/VLOOKUP/转置/模板填充/条件格式等 - 性能优化：read_only/write_only流式、批量写入、列裁剪、分页读取 适用场景： - 月底汇总多个部门Excel报表 - 处理超10万行的大表导致pandas卡死 - 需要保留原Excel的公式、颜色、图表 - 身份证号/订单号变成科学计数法 - 多表VLOOKUP对齐主表 差异化： - 原始版本只给脚本清单，本版补齐"什么场景用什么脚本+为什么"的决策路径 - 新增文件规模分层策略与格式保留矩阵 - 新增四大陷阱章节与对应规避方案 - 增加错误代码表与故障排查 - 压缩重复API说明，按需加载reference 触发关键词：Excel、xlsx、合并、拆分、VLOOKUP、透视、条件格式、科学计数法、openpyxl、pandas

处理Excel文件、表格数据、批量转换或报表生成时应用本skill。核心信条：先看文件多大，再选工具；先问要不要保格式，再动笔。

四大痛点与对策

痛点	典型表现	本skill对策
大文件内存爆炸	50万行xlsx一加载OOM	文件规模分层 + read_only/write_only流式
格式丢失	pandas读写后颜色/公式/图表全没了	格式保留矩阵 + 两条路径
科学计数法	身份证号变成1.23E+19	列格式强制文本 + 写入前预处理
公式不计算	data_only=True拿到None	区分"读公式"vs"读缓存值" + 重算方案

---

第一步：按文件规模选工具

文件规模	行数估计	推荐工具	关键参数
小文件	<1万行	pandas	pd.read_excel(engine="openpyxl")
中文件	1万-50万行	openpyxl	load_workbook(read_only=True)
大文件	>50万行	openpyxl流式 + 分块	read_only=True + write_only=True + 分页
超大文件	>100万行	列裁剪 + 分块 + 落地CSV	配合pandas chunksize

中大文件流式读取（避免OOM）

python

from openpyxl import load_workbook

# read_only=True 不把整个文件载入内存
wb = load_workbook("big.xlsx", read_only=True, data_only=True)
ws = wb["Sheet1"]
for row in ws.iter_rows(values_only=True):
    # 逐行处理，内存恒定
    process(row)
wb.close()  # read_only 必须显式关闭

大文件流式写入

python

from openpyxl import Workbook

wb = Workbook(write_only=True)  # 流式写，无法修改已写行
ws = wb.create_sheet("结果")
ws.append(["列A", "列B", "列C"])
for row in data_stream:
    ws.append(row)
wb.save("output.xlsx")  # 不会OOM

---

第二步：格式保留矩阵

90%的"格式丢失"问题源于选错了工具。

操作需求	用pandas	用openpyxl	说明
纯数据读写	✅	✅	pandas更简洁
保留单元格颜色/字体	❌	✅	pandas会丢
保留公式	❌	✅	pandas只存值或公式字符串
保留图表/透视表	❌	⚠️部分	openpyxl能保留已有图表，不能新建复杂图表
保留合并单元格	❌	✅	pandas会展开
保留条件格式	❌	✅	pandas会丢
多表合并/分析	✅	⚠️慢	pandas DataFrame更适合
大文件流式	⚠️chunksize	✅read_only	openpyxl更稳

两条路径选择

路径A：纯数据处理（不关心格式）

python

import pandas as pd
df = pd.read_excel("input.xlsx", engine="openpyxl")
# ... 处理 ...
df.to_excel("output.xlsx", index=False, engine="openpyxl")

路径B：保留原格式改数据（只动目标单元格）

python

import openpyxl
wb = openpyxl.load_workbook("input.xlsx")  # 不加read_only
ws = wb["Sheet1"]
ws["C2"] = new_value  # 只改这一格，其余格式全保留
wb.save("output.xlsx")

---

第三步：四大陷阱与规避

陷阱1：data_only=True拿到None

python

# 错误：文件从未被Excel打开过，公式没有缓存值
wb = openpyxl.load_workbook("file.xlsx", data_only=True)
ws = wb.active
print(ws["A1"].value)  # None，因为公式结果从未被Excel计算并保存

# 正确方案1：用Excel打开保存一次（让Excel写入缓存值）
# 正确方案2：用formulas库重算
# pip install formulas
import formulas
xl = formulas.ExcelModel().loads("file.xlsx").finish()
sol = xl.calculate()
# 正确方案3：只读公式字符串（不读值）
wb = openpyxl.load_workbook("file.xlsx", data_only=False)
print(ws["A1"].value)  # "=SUM(B1:B10)" 公式字符串

陷阱2：长数字变科学计数法

python

# 错误：身份证号110101199001011234变成1.10E+17
df = pd.read_excel("input.xlsx")
df["身份证号"]  # 已是float，精度丢失

# 正确方案1：读取时指定dtype
df = pd.read_excel("input.xlsx", dtype={"身份证号": str})

# 正确方案2：openpyxl读
wb = openpyxl.load_workbook("input.xlsx")
ws = wb.active
for row in ws.iter_rows(values_only=True):
    id_card = str(row[0])  # 字符串保留

# 正确方案3：写入前强制文本格式
from openpyxl.styles import numbers
ws["A1"].number_format = numbers.FORMAT_TEXT
ws["A1"] = "110101199001011234"

陷阱3：CSV编码错乱

python

# 错误：中文乱码或报错
df = pd.read_csv("input.csv")  # 默认UTF-8，但Excel导出的CSV可能是GBK

# 正确方案1：尝试多种编码
for enc in ["utf-8", "gbk", "gb18030", "utf-8-sig"]:
    try:
        df = pd.read_csv("input.csv", encoding=enc)
        break
    except UnicodeDecodeError:
        continue

# 正确方案2：写入CSV时加BOM（让Excel正确识别UTF-8）
df.to_csv("output.csv", index=False, encoding="utf-8-sig")

陷阱4：合并单元格读写

python

# 读取：合并单元格只有左上角有值，其余为None
ws = wb["Sheet1"]
for row in ws.iter_rows():
    for cell in row:
        if cell.value is None and cell.coordinate in ws.merged_cells:
            # 处于合并区域内，取左上角值
            pass

# 写入：先解除合并再写
from openpyxl.utils import range_boundaries
for merged_range in list(ws.merged_cells.ranges):
    ws.unmerge_cells(str(merged_range))
ws["A1"] = value

---

第四步：脚本速查表

你想做的事	调用脚本	典型参数
多Excel/多sheet合成一张表	merge_sheets.py	--inputs 文件或目录 --output out.xlsx
sheet导出CSV	excel_to_csv.py	--input file.xlsx --output file.csv
CSV转Excel	csv_to_excel.py	--input a.csv --output a.xlsx
按条件筛选行	filter_excel.py	--where "列名=值" 或 "列名>100"、"列名~北京"
按行数/按列拆分	split_excel.py	--by-rows 5000 或 --by-column 地区
按列去重	deduplicate_excel.py	--keys 编号 --keep first
分组聚合	aggregate_excel.py	--group-by 地区 --agg "销售额:sum"
校验必填列/重复键/空行	validate_excel.py	--require-cols 列名 --key-cols 列名
选择/重命名列	select_columns.py	--columns 列1,列2 --rename "旧:新"
两表按键合并（VLOOKUP）	merge_tables.py	--left a.xlsx --right b.xlsx --on 键列
主表对多表VLOOKUP	vlookup_multi.py	--main 主.xlsx --lookups "表1.xlsx:键列"
行列转置	transpose_excel.py	--input in.xlsx --output out.xlsx
模板填充{{列名}}	template_fill.py	--template t.xlsx --data d.csv --output out.xlsx
重命名工作表	rename_sheets.py	--rename "Sheet1:新名" 或 --prefix "2024_"
条件格式	format_conditional.py	--column C --rule gt --value 100 --fill red
列设为文本格式	format_columns_as_text.py	--columns 身份证号,订单号

本地运行

bash

# 进入skill目录或把scripts/加入PATH
pip install -r scripts/requirements.txt
python scripts/merge_sheets.py --help

---

第五步：通用处理流程

1. 确认输入：文件路径、sheet名或索引、是否有表头、编码（CSV时）
2. 选工具：按文件规模分层表选pandas或openpyxl
3. 读取：按需读整表/区域/流式
4. 处理：转换、过滤、合并、计算
5. 写出：指定输出路径与格式；需保格式则用openpyxl单格改写
6. 校验：检查行数、关键列、重复值、业务规则

读取Excel

python

# 整表为list of dict（保表头）
import openpyxl
wb = openpyxl.load_workbook("input.xlsx", read_only=True, data_only=True)
ws = wb.active
rows = list(ws.iter_rows(values_only=True))
header = rows[0]
data = [dict(zip(header, row)) for row in rows[1:]]
wb.close()

# pandas（适合分析、过滤、合并）
import pandas as pd
df = pd.read_excel("input.xlsx", sheet_name=0, engine="openpyxl")

# 指定区域
df = pd.read_excel("input.xlsx", usecols="A:D", header=0, nrows=100)

写入Excel

python

# 新建并写入（openpyxl）
from openpyxl import Workbook
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
ws.title = "结果"
ws.append(["列A", "列B", "列C"])
for row in data_rows:
    ws.append(row)
ws["A1"].font = Font(bold=True)
wb.save("output.xlsx")

# pandas多sheet写出
with pd.ExcelWriter("output.xlsx", engine="openpyxl") as writer:
    df1.to_excel(writer, sheet_name="汇总", index=False)
    df2.to_excel(writer, sheet_name="明细", index=False)

# 追加到已有文件
wb = openpyxl.load_workbook("existing.xlsx")
ws = wb["Sheet1"]
for row in new_rows:
    ws.append(row)
wb.save("existing.xlsx")

---

第六步：批量处理目录

python

from pathlib import Path
import openpyxl, json

results = []
errors = []
for file in Path("目录").glob("*.xlsx"):
    try:
        wb = openpyxl.load_workbook(file, read_only=True, data_only=True)
        ws = wb.active
        # 处理...
        results.append({"file": file.name, "rows": ws.max_row})
        wb.close()
    except Exception as e:
        errors.append({"file": file.name, "error": str(e)})
        continue  # 出错继续处理其余文件

# 汇总报错列表
if errors:
    with open("errors.json", "w", encoding="utf-8") as f:
        json.dump(errors, f, ensure_ascii=False, indent=2)

批量输出命名规则建议：原名_out.xlsx 或统一汇总到一个文件。

---

第七步：性能优化技巧

场景	慢的原因	优化方案
逐单元格写入	每次write触发渲染	用ws.append(row)批量行写入
大文件读取OOM	整文件载入	read_only=True流式
大文件写出OOM	整工作簿在内存	write_only=True流式
只需部分列	读全部列	usecols="A:C"列裁剪
百万行分析	全量载入	pd.read_excel(chunksize=10000)分块
多文件合并	串行读	并行读（concurrent.futures）
公式重算慢	全表重算	用formulas库按需算

---

校验与错误处理

• 读取前用Path(file).exists()检查文件存在
• 表为空或缺少预期列时给出明确提示（列名/行数）
• 写入前若目标文件已存在，按用户要求覆盖或换名；大文件用write_only=True或分块
• 捕获openpyxl.utils.exceptions.InvalidFileException、KeyError（工作表名）并返回可读错误

常见错误代码

错误	原因	解决
InvalidFileException	文件不是有效xlsx/xls	检查文件是否损坏、是否实为.csv改后缀
KeyError: 'Sheet1'	工作表名不存在	用wb.sheetnames查看实际表名
PermissionError	文件被Excel占用	关闭Excel再处理
MemoryError	文件太大	切换read_only流式
UnicodeDecodeError	CSV编码不对	尝试gbk/gb18030/utf-8-sig
ValueError: No column	列名写错或有多余空格	df.columns = df.columns.str.strip()

---

技术栈

• 读写.xlsx：openpyxl（保留格式、公式、多工作表）
• 数据分析/透视：pandas + openpyxl引擎
• 旧格式.xls：xlrd（只读）
• 公式重算：formulas库（按需）

bash

pip install openpyxl pandas xlrd formulas

---

FAQ

Q：50万行Excel一读就OOM怎么办？ A：用load_workbook(read_only=True, data_only=True)流式读取，配合iter_rows(values_only=True)逐行处理，内存恒定。

Q：用pandas读写后Excel颜色和公式都没了？ A：pandas只处理数据不处理格式。需要保格式就用openpyxl加载原文件，只改目标单元格再save（见"路径B"）。

Q：身份证号变成科学计数法怎么救？ A：读取时dtype={"身份证号": str}，或openpyxl读后str(cell.value)。写入前设number_format = FORMAT_TEXT。

Q：data_only=True读公式单元格是None？ A：文件从未被Excel打开保存过，没有缓存值。要么用Excel打开存一次，要么用formulas库重算，要么data_only=False读公式字符串。

Q：多个Excel文件需要合并到一个sheet？ A：用merge_sheets.py --inputs 目录 --output out.xlsx，或pandas的pd.concat([pd.read_excel(f) for f in files])。

Q：CSV用Excel打开中文乱码？ A：写入时encoding="utf-8-sig"加BOM，Excel就能正确识别UTF-8。

---

故障排查

症状	可能原因	解决
打开文件报InvalidFile	文件损坏或后缀不符	用Excel打开验证，或另存为xlsx
sheet名找不到	有隐藏空格	wb.sheetnames查看，strip空格
写入后打不开	write_only模式未append任何行	确保至少append一行再save
数值精度丢失	用了float存大数	改用str或Decimal
条件格式不生效	规则写错或范围不对	先用Excel手动验证规则
公式显示为文本	单元格格式是文本	设number_format = 'General'再写公式

---

依赖说明

运行环境

• Agent平台：支持SKILL.md的任意AI Agent（Claude Code / Cursor / Codex / Gemini CLI等）
• 操作系统：Windows / macOS / Linux
• Python：3.8+（推荐3.10+）

第三方依赖

依赖项	类型	是否必需	获取方式
openpyxl	Python库	必需	pip install openpyxl
pandas	Python库	必需	pip install pandas
xlrd	Python库	可选（读.xls）	pip install xlrd
formulas	Python库	可选（公式重算）	pip install formulas
LLM API	API	必需	由Agent内置LLM提供

API Key 配置

• 本skill基于本地脚本，无需额外API Key
• 涉及读取在线Excel（如OneDrive）时需对应平台OAuth Token

可用性分类

• 分类：MD+EXEC（Markdown指令 + Python脚本执行）
• 说明：通过自然语言指令驱动Agent调用scripts/下的Python脚本完成Excel处理