Excel 表格处理（改编自 Nous Research xlsx）

适合“把 CSV 整理成可筛选的表”“检查这份 Excel 的公式和异常值”“按明细生成多工作表报表”。先确认源文件、目标表、口径、输出位置；默认另存新文件，修改原件前先征求同意。对统计或财务数字先校验行列和公式范围，不凭空补值。

在 WorkBuddy 中先定位当前 SKILL.md 所在文件夹，按绝对路径调用 scripts/ 中的脚本；Windows 路径和含空格路径需加引号，命令用 python -X utf8，确保中文 JSON 可按 UTF-8 读取。需要 Python 3.10+ 与 openpyxl，缺失时提示 python -m pip install openpyxl，安装排查读 @references/setup.md。openpyxl 写入公式不等于计算出结果；只有用 Excel/LibreOffice 真正重算并读到缓存值后，才能宣称结果已核对。若没有 LibreOffice，交付文件并明确标注“公式待 Excel 打开后重算”。检查脚本 JSON 输出，尤其 recalculated 字段。插行/列前先读 @references/restructuring.md，并核对图表与条件格式等无法自动平移的部分。

以下保留原版操作细节。原版 terminal、write_file、read_file 是执行命令和读写文本的泛称，不要求 WorkBuddy 存在同名工具。来源与改动见 SOURCE_AND_CHANGES.md。

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.

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 xlsx_restructure.py),
  copy/rename sheets, tables, names, notes, protection.
• 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).

Prerequisites

• Python 3.10+ with openpyxl (pip install openpyxl). No other
  third-party packages are needed; everything else is stdlib.
• Optional: LibreOffice (soffice) for headless recalculation or
  format conversion.

How to Run

Resolve this skill directory first and run the helper scripts by absolute
path (every script supports --help). Quote Windows paths and spaces:

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 Data

Create the UTF-8 JSON spec with the available file-writing capability;
inspect script JSON output directly or with the available file reader.

Quick Reference

Procedure

1. Create: write a JSON spec (schema documented in
   xlsx_create.py --help and its docstring). Each sheet supports
   rows (scalars or styled cell objects), sparse cells overrides,
   column_widths, row_heights, merges, freeze_panes,
   autofilter, conditional_formats (cell_is rules and color
   scales), charts (bar/line/pie from cell ranges),
   validations (list dropdowns), tables (native Excel tables with
   a style name), and protection. Workbook-level defined_names
   maps names to refs. Cell objects also take hyperlink and note.
   Typed values: JSON numbers/bools
   pass through; dates use {"value": "2026-01-31", "type": "date"}.
   Number formats are Excel format strings: currency "$#,##0.00",
   percent "0.0%", date "yyyy-mm-dd".
2. Formulas: set with "formula": "SUM(B2:B9)" in the spec or
   --set "C1==SUM(A:A)" in the editor. When writing formulas, add
   "full_calc_on_load": true (spec) or --recalc (editor); this sets
   the workbook's fullCalcOnLoad flag so Excel/LibreOffice recompute
   everything on open. openpyxl itself NEVER evaluates formulas.
3. Read: --sheets for inventory (names, dimensions, merged
   ranges, chart count, tables, protection, defined names),
   --json/--csv for data, --formulas to
   pair each formula string with its cached result, --notes for
   cell comments, --names for defined names. Cached results
   exist only if the file was last saved by a real spreadsheet app;
   files fresh from openpyxl return null there. To materialize
   results headlessly run xlsx_recalc.py file.xlsx (uses
   LibreOffice; prints {"recalculated": false, ...} and exits 0
   when soffice is absent), then reload with --data-only.
4. Edit: xlsx_edit.py applies renames/copies first, then
   structural row/column changes, then --set/--append. It edits in
   place unless --out is given — copy the file first if you need the
   original.
5. Restructure: for insert/delete on sheets that have formulas,
   merges, tables, or filters, use xlsx_restructure.py instead of
   xlsx_edit.py. It rewrites formula references on ALL sheets
   (absolute $ refs, 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 not_shifted list. Rules and
   limits: references/restructuring.md.
6. CSV interop: csv_to_xlsx.py infers int/float/bool/ISO-date
   per cell and styles the header row; xlsx_to_csv.py writes ISO
   dates and blank strings for empty cells. Both default to UTF-8 and
   accept --encoding (e.g. utf-8-sig for Excel-friendly BOM,
   cp1252 for legacy Windows exports).

Converting to PDF

LibreOffice converts headlessly (also works for CSV export of a single
sheet):

soffice --headless --convert-to pdf report.xlsx --outdir out/
soffice --headless --convert-to csv report.xlsx --outdir out/  # 1st sheet only

Only the first sheet lands in a CSV; for other sheets use
xlsx_to_csv.py --sheet NAME. If soffice is missing, install
LibreOffice or hand the file to the user unconverted.

Pitfalls

• openpyxl does not calculate. Formula results are available only
  via load_workbook(path, data_only=True) and only when the file was
  previously saved by Excel/LibreOffice. Otherwise you get None.
• xlsx_edit.py insert/delete does not shift references (raw
  openpyxl behavior). Use xlsx_restructure.py, which does — but even
  it cannot move chart anchors, images, or conditional-format RULE
  formulas; read its JSON report's not_shifted list and
  references/restructuring.md.
• Sheet protection is NOT security. --protect 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.
• data_only=True then save silently discards all formulas
  (cached values replace them). Never save a workbook loaded that way
  unless that is the goal.
• 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 --delimiter ';' and expect strings like
  "12,5" to stay strings.
• Dates are datetimes: Excel stores dates as serial numbers;
  openpyxl returns datetime/date objects. Dumps here emit ISO
  strings.
• Sheet names are capped at 31 chars and reject [ ] : * ? / \.

Verification

• After creating: xlsx_read.py out.xlsx --sheets and confirm sheet
  names, dimensions, merged ranges, and chart counts match intent.
• Dump data with --json and compare against the source values.
• After edits: re-dump the touched range; if formulas were written,
  confirm --formulas lists them and that --recalc was applied.
• After xlsx_restructure.py: read its JSON report, then re-run
  --formulas and --sheets to confirm references and ranges landed
  where expected.
• For a full visual check, open in LibreOffice:
  soffice --headless --convert-to pdf out.xlsx and inspect the PDF.