xlsx

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

XLSX creation, editing, and analysis

XLSX文件的创建、编辑与分析

Task	Approach
Create or edit with formulas/formatting	`openpyxl` — see gotchas below
Bulk data in or out	`pandas` ( `read_excel` , `to_excel` )
Quick look at a sheet	`markitdown file.xlsx` — `## SheetName` per sheet; reads `.xlsm` too. No cell coordinates, so don't plan edits from it
Read a model (formulas and values)	two `load_workbook` passes — see gotchas

openpyxl
,
pandas
, and
markitdown
are preinstalled — do not run
pip install
first; write the script and import directly. Only if an import fails (or the
markitdown
command is missing):
pip install
the missing package.

Script paths below are relative to this skill's directory.

任务	处理方式
使用公式/格式创建或编辑	`openpyxl` — 参见下方常见陷阱
批量数据导入/导出	`pandas` （ `read_excel` 、 `to_excel` 方法）
快速查看工作表内容	`markitdown file.xlsx` — 每个工作表对应 `## SheetName` ；支持读取 `.xlsm` 文件。该方法不显示单元格坐标，因此无法基于此规划编辑操作
读取模型（包含公式和数值）	两次调用 `load_workbook` — 参见下方常见陷阱

openpyxl
、
pandas
和
markitdown
已预安装 — 无需先执行
pip install
；直接编写脚本并导入即可。仅当导入失败（或
markitdown
命令缺失）时，再执行
pip install
安装缺失的包。

下方脚本路径均相对于本Skill的目录。

Requirements for every output

所有输出的要求

Professional font (Arial, Times New Roman) throughout, unless the user says otherwise.
Zero formula errors. Never ship while
```
recalc.py
```
reports
```
errors_found
```
. If you think an error predates you, prove it: load the original with
```
data_only=True
```
and look at that cell. An error you introduced looks exactly like one you inherited.
Use formulas, never hardcoded results. Write
```
sheet['B10'] = '=SUM(B2:B9)'
```
, not the Python-computed total. The sheet must recalculate when its inputs change.
Follow the user's spec literally. Exact tab names, exact column headers, and the formula they spelled out. A redesign that computes something else fails, however elegant.
Document every assumption and hardcoded number where the reader will see it — a cell comment, or an adjacent cell at a table's end. Cite a real source when one exists (
```
Source: Company 10-K, FY2024, Page 45, Revenue Note, [SEC EDGAR URL]
```
); when the number came from the user, say so plainly.
A workbook you create for someone to fill in needs a short legend naming which cells to edit, and one example row of realistic values showing the expected format. Never add such a row to a file you were asked to edit.
Editing an existing file: match its conventions exactly. They override every guideline here. Find its designated input cells first — a distinct font color, fill, or shading marks them — write only there, and leave every existing formula untouched.

全程使用专业字体（Arial、Times New Roman），除非用户另有说明。
零公式错误。当
```
recalc.py
```
报告
```
errors_found
```
时，绝不能交付文件。若你认为错误是原有文件自带的，请验证：使用
```
data_only=True
```
加载原始文件并查看对应单元格。你引入的错误与继承的错误表现完全一致。
使用公式，而非硬编码结果。应编写
```
sheet['B10'] = '=SUM(B2:B9)'
```
，而非Python计算得出的总计值。表格必须能在输入内容改变时重新计算。
严格遵循用户的规格要求。使用精确的工作表名称、列标题以及用户指定的公式。无论设计多么优雅，若计算结果与要求不符，即为失败。
记录所有假设和硬编码数值，且需让读者可见——可添加单元格批注，或在表格末尾的相邻单元格中注明。若数值来自真实来源，请引用（例如
```
来源：公司2024财年10-K报告，第45页，收入说明，[SEC EDGAR URL]
```
）；若数值来自用户，则直接说明。
为他人创建的待填写工作簿需包含简短图例，标注可编辑的单元格，并添加一行符合预期格式的真实示例数据。请勿在用户要求编辑的文件中添加此类示例行。
编辑现有文件：完全匹配其既有规范。这些规范优先于本指南中的所有准则。首先找到文件中指定的输入单元格——通常通过独特的字体颜色、填充色或阴影标记——仅在这些单元格中写入内容，保留所有现有公式不变。

Recalculate (mandatory whenever the file contains formulas)

重新计算（只要文件包含公式则必须执行）

openpyxl writes formulas as strings with no cached values. Until you recalculate, every formula cell reads back as

None

to anything reading cached values —

pandas

load_workbook(data_only=True)

, and most previewers.

bash

python scripts/recalc.py output.xlsx [timeout_seconds]   # default 30

LibreOffice computes every formula, the file is rewritten in place, and you get JSON:

status

(

success

errors_found

total_formulas

total_errors

, and an

error_summary

naming up to 100 cells per error type (

locations_truncated

says how many it withheld — trust

total_errors

, not the length of the list). Fix what it names and run it again. JSON with an
error
key instead of a
status
means nothing was recalculated, and only that case exits non-zero —

errors_found

exits 0, so never treat a clean exit as a clean workbook.

A green recalc proves your formulas evaluate, not that they are right. An off-by-one range or a reference to the wrong row yields a clean, error-free file with wrong numbers. Write 2–3 formulas first and check they pull the values you expect, before building out a grid.

A workbook that links to another file loses those links if you re-save it with openpyxl and then recalculate. Such a formula reads

='[1]Returns Analysis'!$B$2

— the

[1]

is an index into the workbook's external-reference list, naming a separate file on disk, not a sheet. That file is rarely present here, so the cell's cached value is the only thing holding its data. openpyxl strips that value on save; LibreOffice then has to resolve the reference for real, fails, writes

#NAME?

, and deletes every link.

recalc.py

refuses to run in that state — copy those cells' values out of the original before you save over them (

--force

overrides, and accepts the loss).

openpyxl将公式以字符串形式写入，不包含缓存值。在重新计算之前，所有公式单元格在读取缓存值的工具（如

pandas

、

load_workbook(data_only=True)

以及大多数预览工具）中都会被读取为

None

。

bash

python scripts/recalc.py output.xlsx [timeout_seconds]   # 默认超时时间30秒

LibreOffice会计算所有公式，文件将原地重写，并返回JSON结果：包含

status

（

success

或

errors_found

）、

total_formulas

、

total_errors

以及

error_summary

（每种错误类型最多列出100个单元格位置，

locations_truncated

表示被省略的数量——请以

total_errors

为准，而非列表长度）。修复列出的错误后再次运行该脚本。若返回的JSON包含
error
键而非
status
，则表示未执行任何重新计算，只有这种情况会返回非零退出码——

errors_found

会返回0，因此绝不能将干净的退出状态视为工作簿无问题。

重新计算通过仅证明公式可被求值，不代表公式正确。范围差一位或引用错误行都会生成无错误但数值错误的文件。在构建完整表格之前，先编写2-3个公式并验证其是否能获取预期值。

包含外部文件链接的工作簿，若使用openpyxl重新保存后再执行重新计算，会丢失这些链接。此类公式格式为

='[1]Returns Analysis'!$B$2

——其中

[1]

是工作簿外部引用列表中的索引，指向磁盘上的另一个文件，而非工作表。该文件通常不在当前环境中，因此单元格的缓存值是唯一保留数据的方式。openpyxl在保存时会清除该值；LibreOffice尝试解析引用失败后，会写入

#NAME?

并删除所有链接。此时

recalc.py

会拒绝运行——在覆盖保存之前，请先将原始文件中这些单元格的值复制出来（

--force

参数可强制运行，但会接受数据丢失）。

Choosing formulas that survive verification

选择可通过验证的公式

LibreOffice implements fewer functions than Excel, and one it cannot evaluate becomes a literal

#NAME?

baked into the file you deliver.

Prefer Excel-2007-era functions —
```
SUMIFS
```
,
```
INDEX
```
,
```
MATCH
```
,
```
IFERROR
```
,
```
SUMPRODUCT
```
— which need no prefix.
Six post-2007 functions work, but only with an
_xlfn.
prefix, because openpyxl writes your formula into the XML verbatim and Excel stores post-2007 names prefixed (its UI hides the prefix):
```
_xlfn.TEXTJOIN
```
,
```
_xlfn.CONCAT
```
,
```
_xlfn.IFS
```
,
```
_xlfn.SWITCH
```
,
```
_xlfn.MAXIFS
```
,
```
_xlfn.MINIFS
```
. Written bare, each yields
```
#NAME?
```
.
Never use
XLOOKUP
,
XMATCH
,
SORT
,
FILTER
,
UNIQUE
, or
SEQUENCE
. The runtime's LibreOffice cannot evaluate them under any prefix. Newer builds do evaluate them, but they are spilling array functions and an openpyxl-written file has no spill metadata, so only the top-left cell of the range gets a value — and
```
recalc.py
```
reports
```
total_errors: 0
```
on the truncated result. Use
```
INDEX
```
/
```
MATCH
```
for lookups, and sort, filter, and de-duplicate in Python before writing the cells.
A formula LibreOffice could not parse is written back lowercased — a quick tell beside a
```
#NAME?
```
.

LibreOffice支持的函数少于Excel，无法求值的函数会在交付的文件中显示为

#NAME?

。

优先使用Excel 2007时代的函数——
```
SUMIFS
```
、
```
INDEX
```
、
```
MATCH
```
、
```
IFERROR
```
、
```
SUMPRODUCT
```
——这些函数无需前缀。
6个2007年后的函数可使用，但必须添加
_xlfn.
前缀，因为openpyxl会将公式原样写入XML，而Excel会为2007年后的函数名称添加前缀（UI会隐藏该前缀）：
```
_xlfn.TEXTJOIN
```
、
```
_xlfn.CONCAT
```
、
```
_xlfn.IFS
```
、
```
_xlfn.SWITCH
```
、
```
_xlfn.MAXIFS
```
、
```
_xlfn.MINIFS
```
。若不添加前缀，这些函数都会显示为
```
#NAME?
```
。
切勿使用
XLOOKUP
、
XMATCH
、
SORT
、
FILTER
、
UNIQUE
或
SEQUENCE
。当前环境的LibreOffice无法通过任何前缀求值这些函数。新版本虽然能求值，但它们是溢出数组函数，而openpyxl生成的文件没有溢出元数据，因此只有范围左上角的单元格会获得值——且
```
recalc.py
```
会报告
```
total_errors: 0
```
，导致结果被截断。请使用
```
INDEX
```
/
```
MATCH
```
实现查找功能，并在写入单元格前用Python完成排序、过滤和去重操作。
LibreOffice无法解析的公式会被小写返回——这是除
```
#NAME?
```
之外的快速判断依据。

openpyxl gotchas

openpyxl常见陷阱

Reading a model takes two loads.
```
data_only=True
```
yields cached values with the formulas gone; the default yields formula strings with no values. One pass cannot give you both.
data_only=True
is destructive if you save. That workbook has no formulas left, so saving replaces every one with a literal — permanently.
data_only=True
on a file openpyxl just wrote returns
None
everywhere — run
```
recalc.py
```
first. (A formula whose result is
```
""
```
also reads back as
```
None
```
.)
Merged cells: write the top-left anchor only. Every other cell in the range is a
```
MergedCell
```
whose
```
.value
```
is read-only.
.xlsm
loses its macros unless you pass
keep_vba=True
to
```
load_workbook
```
.
A sheet name containing a space must be quoted in a cross-sheet reference:
```
='Assumptions Inputs'!$B$5
```
. Unquoted, it evaluates to
```
#VALUE!
```
.

读取模型需要两次加载。
```
data_only=True
```
会返回缓存值，但公式会丢失；默认方式会返回公式字符串，但无数值。单次加载无法同时获取两者。
若保存时使用
data_only=True
会造成破坏性影响。该工作簿将不再包含公式，保存后所有公式都会被替换为字面量——且无法恢复。
对openpyxl刚写入的文件使用
data_only=True
加载，所有单元格都会返回
None
——请先运行
```
recalc.py
```
。（结果为
```
""
```
的公式也会被读取为
```
None
```
。）
合并单元格：仅写入左上角锚点单元格。合并范围中的其他单元格均为
```
MergedCell
```
，其
```
.value
```
属性为只读。
.xlsm
文件会丢失宏，除非在
load_workbook
中传入
keep_vba=True
参数。
包含空格的工作表名称在跨表引用中必须加引号：例如
```
='Assumptions Inputs'!$B$5
```
。若不加引号，会求值为
```
#VALUE!
```
。

Financial models

财务模型规范

Unless the user says otherwise, or the existing file already does something else.

Color: blue text (

0,0,255

) for hardcoded inputs and scenario levers · black for formulas · green (

0,128,0

) for links to another sheet · red (

255,0,0

) for links to another file · yellow fill (

255,255,0

) for key assumptions and cells the user should fill in.

Numbers: currency

$#,##0

, with the unit named in the header (

Revenue ($mm)

) · zeros render as

, including in percentages (

$#,##0;($#,##0);-

) · negatives in parentheses · percentages

0.0%

, stored as fractions (

0.15

renders

15.0%

; storing

renders

1500.0%

) · valuation multiples

0.0x

· years as text (

"2024"

, never

2,024

Structure: every assumption in its own labeled cell, referenced by the formulas that use it (

=B5*(1+$B$6)

, never

=B5*1.05

) · formulas consistent across every projection period, since a lone edited cell mid-row is the commonest silent error · guard denominators that can be zero.

除非用户另有说明，或现有文件已采用其他规范。

颜色规则：硬编码输入和场景控制项使用蓝色文本（

0,0,255

）· 公式使用黑色文本 · 跨工作表链接使用绿色文本（

0,128,0

）· 跨文件链接使用红色文本（

255,0,0

）· 关键假设和用户需填写的单元格使用黄色填充（

255,255,0

）。

数字格式：货币使用

$#,##0

格式，单位在表头中标注（例如

Revenue ($mm)

）· 零值显示为

，百分比格式也遵循此规则（

$#,##0;($#,##0);-

）· 负值用括号包裹 · 百分比使用

0.0%

格式，以分数形式存储（

0.15

显示为

15.0%

；若存储为

则显示为

1500.0%

）· 估值倍数使用

0.0x

格式 · 年份以文本形式存储（

"2024"

，绝不能是

2,024

）。

结构规则：每个假设都放在单独的带标签单元格中，公式通过引用该单元格使用假设值（例如

=B5*(1+$B$6)

，绝不能是

=B5*1.05

）· 所有预测周期的公式保持一致，因为行中单独编辑的单元格是最常见的隐性错误 · 对可能为零的分母添加防护逻辑。

Dependencies

依赖项

openpyxl

pandas

markitdown

(pip, preinstalled — install only if an import fails or the command is missing) · LibreOffice (

soffice

, auto-configured for sandboxed environments via

scripts/office/soffice.py

)

openpyxl

、

pandas

、

markitdown

（通过pip预安装——仅当导入失败或命令缺失时才需安装）· LibreOffice（

soffice

，通过

scripts/office/soffice.py

为沙箱环境自动配置）",