xlsx
Compare original and translation side by side
🇺🇸
Original
English🇨🇳
Translation
ChineseXlsx Skill
Xlsx Skill
Work with Excel .xlsx workbooks using Python and openpyxl: build styled
multi-sheet workbooks with formulas and charts, inspect or dump existing
files, edit cells and structure, and convert to/from CSV. All helper
scripts are argparse CLIs that print JSON and use explicit UTF-8 I/O.
使用Python和openpyxl处理Excel .xlsx工作簿:构建包含公式和图表的带样式多工作表工作簿,检查或导出现有文件,编辑单元格和结构,以及在CSV格式之间进行转换。所有辅助脚本都是argparse命令行工具,可输出JSON并使用显式UTF-8输入输出。
When to Use
适用场景
- Creating .xlsx reports: multiple sheets, number formats, styling, merged cells, freeze panes, autofilter, conditional formatting, charts, data-validation dropdowns, native Excel tables, defined names, hyperlinks, cell notes, sheet protection.
- Reading a workbook: sheet inventory, dumping data as JSON or CSV, listing formulas vs cached values, notes, defined names, tables.
- Editing existing files: set cells, append rows, insert/delete
rows/columns (reference-aware via ), copy/rename sheets, tables, names, notes, protection.
xlsx_restructure.py - Recalculating formulas headlessly via LibreOffice
().
xlsx_recalc.py - CSV interop with type inference and non-UTF-8 encodings.
- Not for the legacy .xls binary format (use LibreOffice to convert
first: ).
soffice --headless --convert-to xlsx old.xls
- 创建.xlsx报表:包含多工作表、数字格式、样式、合并单元格、冻结窗格、自动筛选、条件格式、图表、数据验证下拉菜单、原生Excel表格、定义名称、超链接、单元格批注、工作表保护。
- 读取工作簿:工作表清单、以JSON或CSV格式导出数据、列出公式与缓存值、批注、定义名称、表格。
- 编辑现有文件:设置单元格、追加行、插入/删除行/列(通过实现引用感知)、复制/重命名工作表、表格、名称、批注、保护。
xlsx_restructure.py - 通过LibreOffice无头重新计算公式()。
xlsx_recalc.py - CSV互操作,支持类型推断和非UTF-8编码。
- 不支持旧版.xls二进制格式(需先使用LibreOffice转换:)。
soffice --headless --convert-to xlsx old.xls
Prerequisites
前置条件
- Python 3.10+ with (
openpyxl). No other third-party packages are needed; everything else is stdlib.pip install openpyxl - Optional: LibreOffice () for headless recalculation or format conversion.
soffice
- Python 3.10+ 及库(
openpyxl)。无需其他第三方包;其余均为标准库。pip install openpyxl - 可选:LibreOffice()用于无头重新计算或格式转换。
soffice
How to Run
运行方式
Run the helper scripts with the tool from this skill's
directory (every script supports ):
terminalscripts/--helpbash
python scripts/xlsx_create.py spec.json report.xlsx # build from JSON spec
python scripts/xlsx_read.py report.xlsx --sheets # inventory
python scripts/xlsx_read.py report.xlsx --json --sheet Data
python scripts/xlsx_read.py report.xlsx --formulas
python scripts/xlsx_edit.py report.xlsx --sheet Data --set B2=42 --recalc
python scripts/xlsx_restructure.py report.xlsx --sheet Data --insert-rows 3:2
python scripts/xlsx_recalc.py report.xlsx
python scripts/csv_to_xlsx.py data.csv out.xlsx --encoding utf-8
python scripts/xlsx_to_csv.py report.xlsx out.csv --sheet DataAuthor the JSON spec with , inspect script JSON output with
or directly from stdout.
write_fileread_file从本技能的目录使用工具运行辅助脚本(每个脚本均支持参数):
scripts/terminal--helpbash
python scripts/xlsx_create.py spec.json report.xlsx # 从JSON配置文件构建工作簿
python scripts/xlsx_read.py report.xlsx --sheets # 查看工作表清单
python scripts/xlsx_read.py report.xlsx --json --sheet Data
python scripts/xlsx_read.py report.xlsx --formulas
python scripts/xlsx_edit.py report.xlsx --sheet Data --set B2=42 --recalc
python scripts/xlsx_restructure.py report.xlsx --sheet Data --insert-rows 3:2
python scripts/xlsx_recalc.py report.xlsx
python scripts/csv_to_xlsx.py data.csv out.xlsx --encoding utf-8
python scripts/xlsx_to_csv.py report.xlsx out.csv --sheet Data使用编写JSON配置文件,使用或直接从标准输出查看脚本的JSON输出。
write_fileread_fileQuick Reference
快速参考
| Task | Command |
|---|---|
| Create workbook from spec | |
| Sheet names + dimensions | |
| Dump sheet as JSON | |
| Dump sheet as CSV | |
| List formulas + cached values | |
| Set a cell / formula | |
| Append a row | |
| Insert 2 rows, refs NOT shifted | |
| Insert 2 rows, refs shifted | |
| Delete a column, refs shifted | |
| Create a native table | |
| Append inside a table | |
| List tables | |
| Defined names | |
| Hyperlink | `--hyperlink "A1=https://example.com |
| Cell note | `--note "B2=Check this |
| Protect sheet (see Pitfalls) | |
| Recalculate via LibreOffice | |
| Copy / rename sheet | |
| Force recalc on open | |
| CSV -> styled xlsx | |
| xlsx -> CSV | |
| 任务 | 命令 |
|---|---|
| 从配置文件创建工作簿 | |
| 查看工作表名称+尺寸 | |
| 将工作表导出为JSON | |
| 将工作表导出为CSV | |
| 列出公式+缓存值 | |
| 设置单元格/公式 | |
| 追加一行 | |
| 插入2行,不移动引用 | |
| 插入2行,移动引用 | |
| 删除一列,移动引用 | |
| 创建原生表格 | |
| 向表格内追加数据 | |
| 列出表格 | |
| 定义名称 | |
| 超链接 | `--hyperlink "A1=https://example.com |
| 单元格批注 | `--note "B2=Check this |
| 保护工作表(参见注意事项) | |
| 通过LibreOffice重新计算 | |
| 复制/重命名工作表 | |
| 打开时强制重新计算 | |
| CSV转换为带样式的xlsx | |
| xlsx转换为CSV | |
Procedure
操作步骤
- Create: write a JSON spec (schema documented in
and its docstring). Each sheet supports
xlsx_create.py --help(scalars or styled cell objects), sparserowsoverrides,cells,column_widths,row_heights,merges,freeze_panes,autofilter(cell_is rules and color scales),conditional_formats(bar/line/pie from cell ranges),charts(list dropdowns),validations(native Excel tables with a style name), andtables. Workbook-levelprotectionmaps names to refs. Cell objects also takedefined_namesandhyperlink. Typed values: JSON numbers/bools pass through; dates usenote. Number formats are Excel format strings: currency{"value": "2026-01-31", "type": "date"}, percent"$#,##0.00", date"0.0%"."yyyy-mm-dd" - Formulas: set with in the spec or
"formula": "SUM(B2:B9)"in the editor. When writing formulas, add--set "C1==SUM(A:A)"(spec) or"full_calc_on_load": true(editor); this sets the workbook's--recalcflag so Excel/LibreOffice recompute everything on open. openpyxl itself NEVER evaluates formulas.fullCalcOnLoad - Read: for inventory (names, dimensions, merged ranges, chart count, tables, protection, defined names),
--sheets/--jsonfor data,--csvto pair each formula string with its cached result,--formulasfor cell comments,--notesfor defined names. Cached results exist only if the file was last saved by a real spreadsheet app; files fresh from openpyxl return--namesthere. To materialize results headlessly runnull(uses LibreOffice; printsxlsx_recalc.py file.xlsxand exits 0 when{"recalculated": false, ...}is absent), then reload withsoffice.--data-only - Edit: applies renames/copies first, then structural row/column changes, then
xlsx_edit.py/--set. It edits in place unless--appendis given — copy the file first if you need the original.--out - Restructure: for insert/delete on sheets that have formulas,
merges, tables, or filters, use instead of
xlsx_restructure.py. It rewrites formula references on ALL sheets (absolutexlsx_edit.pyrefs, ranges, cross-sheet refs), shifts merges, autofilter, freeze panes, validation and conditional-format ranges, table refs, defined names, and row/column dimensions, then prints a JSON report including a$list. Rules and limits:not_shifted.references/restructuring.md - CSV interop: infers int/float/bool/ISO-date per cell and styles the header row;
csv_to_xlsx.pywrites ISO dates and blank strings for empty cells. Both default to UTF-8 and acceptxlsx_to_csv.py(e.g.--encodingfor Excel-friendly BOM,utf-8-sigfor legacy Windows exports).cp1252
- 创建:编写JSON配置文件(架构文档见及其文档字符串)。每个工作表支持
xlsx_create.py --help(标量或带样式的单元格对象)、稀疏rows覆盖、cells、column_widths、row_heights、merges、freeze_panes、autofilter(单元格规则和颜色刻度)、conditional_formats(基于单元格范围的柱状图/折线图/饼图)、charts(列表下拉菜单)、validations(带样式名称的原生Excel表格)和tables。工作簿级别的protection将名称映射到引用。单元格对象还支持defined_names和hyperlink。类型化值:JSON数字/布尔值直接传递;日期使用note。数字格式为Excel格式字符串:货币{"value": "2026-01-31", "type": "date"}、百分比"$#,##0.00"、日期"0.0%"。"yyyy-mm-dd" - 公式:在配置文件中通过设置,或在编辑器中通过
"formula": "SUM(B2:B9)"设置。编写公式时,添加--set "C1==SUM(A:A)"(配置文件)或"full_calc_on_load": true(编辑器);这会设置工作簿的--recalc标志,以便Excel/LibreOffice在打开时重新计算所有内容。openpyxl本身从不计算公式。fullCalcOnLoad - 读取:用于查看清单(名称、尺寸、合并范围、图表数量、表格、保护、定义名称),
--sheets/--json用于导出数据,--csv用于将每个公式字符串与其缓存结果配对,--formulas用于查看单元格批注,--notes用于查看定义名称。缓存结果仅在文件最后由真实电子表格应用保存时才存在;刚由openpyxl生成的文件此处返回--names。要无头生成结果,请运行null(使用LibreOffice;当xlsx_recalc.py file.xlsx不存在时,输出soffice并以0状态退出),然后使用{"recalculated": false, ...}重新加载。--data-only - 编辑:先应用重命名/复制操作,然后进行结构性行/列更改,最后执行
xlsx_edit.py/--set操作。除非指定--append参数,否则它会就地编辑文件——如果需要保留原始文件,请先复制。--out - 重构:对于包含公式、合并单元格、表格或筛选器的工作表进行插入/删除操作,请使用而非
xlsx_restructure.py。它会重写所有工作表中的公式引用(绝对xlsx_edit.py引用、范围、跨工作表引用),移动合并单元格、自动筛选、冻结窗格、验证和条件格式范围、表格引用、定义名称以及行/列尺寸,然后输出包含$列表的JSON报告。规则和限制见not_shifted。references/restructuring.md - CSV互操作:会按单元格推断整数/浮点数/布尔值/ISO日期,并为表头行设置样式;
csv_to_xlsx.py会写入ISO日期,为空单元格写入空字符串。两者默认使用UTF-8编码,并接受xlsx_to_csv.py参数(例如--encoding用于Excel友好的BOM,utf-8-sig用于旧版Windows导出)。cp1252
Converting to PDF
转换为PDF
LibreOffice converts headlessly (also works for CSV export of a single
sheet):
bash
soffice --headless --convert-to pdf report.xlsx --outdir out/
soffice --headless --convert-to csv report.xlsx --outdir out/ # 1st sheet onlyOnly the first sheet lands in a CSV; for other sheets use
. If is missing, install
LibreOffice or hand the file to the user unconverted.
xlsx_to_csv.py --sheet NAMEsofficeLibreOffice可进行无头转换(也适用于单工作表的CSV导出):
bash
soffice --headless --convert-to pdf report.xlsx --outdir out/
soffice --headless --convert-to csv report.xlsx --outdir out/ # 仅转换第一个工作表CSV导出仅包含第一个工作表;如需导出其他工作表,请使用。如果缺少,请安装LibreOffice或将文件交给用户手动转换。
xlsx_to_csv.py --sheet NAMEsofficePitfalls
注意事项
- openpyxl does not calculate. Formula results are available only
via and only when the file was previously saved by Excel/LibreOffice. Otherwise you get
load_workbook(path, data_only=True).None - insert/delete does not shift references (raw openpyxl behavior). Use
xlsx_edit.py, which does — but even it cannot move chart anchors, images, or conditional-format RULE formulas; read its JSON report'sxlsx_restructure.pylist andnot_shifted.references/restructuring.md - Sheet protection is NOT security. sets the standard xlsx sheet-protection hash: it signals "don't edit this" to well-behaved apps and nothing more. Anyone can strip it by editing the zip's XML or unchecking it in LibreOffice. Never rely on it for confidentiality or integrity; it does not encrypt anything.
--protect - then save silently discards all formulas (cached values replace them). Never save a workbook loaded that way unless that is the goal.
data_only=True - Loading strips charts/images: openpyxl does not round-trip charts, so editing a charted workbook and saving drops the charts. Re-add charts after editing, or avoid re-saving charted files.
- CSV locale traps: always pass explicit encodings (the scripts
already do) and remember European CSVs often use delimiters and decimal commas — use
;and expect strings like--delimiter ';'to stay strings."12,5" - Dates are datetimes: Excel stores dates as serial numbers;
openpyxl returns /
datetimeobjects. Dumps here emit ISO strings.date - Sheet names are capped at 31 chars and reject .
[ ] : * ? / \
- openpyxl不计算公式。仅当文件之前由Excel/LibreOffice保存时,才能通过获取公式结果。否则将返回
load_workbook(path, data_only=True)。None - 的插入/删除操作不会移动引用(原生openpyxl行为)。请使用
xlsx_edit.py,它会移动引用——但即使如此,它也无法移动图表锚点、图片或条件格式规则公式;请查看其JSON报告的xlsx_restructure.py列表和not_shifted。references/restructuring.md - 工作表保护不具备安全性。设置标准的xlsx工作表保护哈希:它仅向合规应用发出"请勿编辑此内容"的信号,除此之外没有其他作用。任何人都可以通过编辑压缩包中的XML或在LibreOffice中取消勾选来移除保护。切勿依赖它来保证机密性或完整性;它不会加密任何内容。
--protect - 使用加载后保存会静默丢弃所有公式(缓存值会替换公式)。除非有意为之,否则切勿保存以此方式加载的工作簿。
data_only=True - 加载时会剥离图表/图片:openpyxl不支持图表的往返编辑,因此编辑包含图表的工作簿并保存会丢失图表。编辑后重新添加图表,或避免重新保存包含图表的文件。
- CSV区域设置陷阱:始终传递显式编码(脚本已默认处理),并记住欧洲CSV通常使用作为分隔符、逗号作为小数点——使用
;,并预期类似--delimiter ';'的字符串保持为字符串类型。"12,5" - 日期是日期时间类型:Excel将日期存储为序列号;openpyxl返回/
datetime对象。此处导出时会生成ISO字符串。date - 工作表名称最多31个字符,且不能包含字符。
[ ] : * ? / \
Verification
验证方法
- After creating: and confirm sheet names, dimensions, merged ranges, and chart counts match intent.
xlsx_read.py out.xlsx --sheets - Dump data with and compare against the source values.
--json - After edits: re-dump the touched range; if formulas were written,
confirm lists them and that
--formulaswas applied.--recalc - After : read its JSON report, then re-run
xlsx_restructure.pyand--formulasto confirm references and ranges landed where expected.--sheets - For a full visual check, open in LibreOffice:
and inspect the PDF.
soffice --headless --convert-to pdf out.xlsx
- 创建后:运行,确认工作表名称、尺寸、合并范围和图表数量符合预期。
xlsx_read.py out.xlsx --sheets - 使用导出数据,与源值进行比较。
--json - 编辑后:重新导出修改的范围;如果写入了公式,确认列出了这些公式,且已应用
--formulas。--recalc - 使用后:读取其JSON报告,然后重新运行
xlsx_restructure.py和--formulas,确认引用和范围处于预期位置。--sheets - 如需完整的视觉检查,使用LibreOffice打开:并检查PDF。
soffice --headless --convert-to pdf out.xlsx