skills/productivity/xlsx/SKILL.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.
xlsx_restructure.py),
copy/rename sheets, tables, names, notes, protection.xlsx_recalc.py).soffice --headless --convert-to xlsx old.xls).openpyxl (pip install openpyxl). No other
third-party packages are needed; everything else is stdlib.soffice) for headless recalculation or
format conversion.Run the helper scripts with the terminal tool from this skill's
scripts/ directory (every script supports --help):
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
Author the JSON spec with write_file, inspect script JSON output with
read_file or directly from stdout.
| Task | Command |
|---|---|
| Create workbook from spec | xlsx_create.py spec.json out.xlsx |
| Sheet names + dimensions | xlsx_read.py f.xlsx --sheets |
| Dump sheet as JSON | xlsx_read.py f.xlsx --json --sheet S |
| Dump sheet as CSV | xlsx_read.py f.xlsx --csv --out d.csv |
| List formulas + cached values | xlsx_read.py f.xlsx --formulas |
| Set a cell / formula | xlsx_edit.py f.xlsx --set "A1==SUM(B:B)" |
| Append a row | xlsx_edit.py f.xlsx --append '[1,"x",true]' |
| Insert 2 rows, refs NOT shifted | xlsx_edit.py f.xlsx --insert-rows 3:2 |
| Insert 2 rows, refs shifted | xlsx_restructure.py f.xlsx --insert-rows 3:2 |
| Delete a column, refs shifted | xlsx_restructure.py f.xlsx --delete-cols B |
| Create a native table | xlsx_edit.py f.xlsx --add-table Sales:A1:C9 |
| Append inside a table | --table-append 'Sales=["West",5]' |
| List tables | xlsx_edit.py f.xlsx --list-tables |
| Defined names | --define-name "Rates='Data'!$B$2:$B$9" / --delete-name Rates / xlsx_read.py f.xlsx --names |
| Hyperlink | `--hyperlink "A1=https://example.com |
| Cell note | `--note "B2=Check this |
| Protect sheet (see Pitfalls) | --protect your-password --unlock B2:B9 |
| Recalculate via LibreOffice | xlsx_recalc.py f.xlsx |
| Copy / rename sheet | --copy-sheet Src:New --rename-sheet Old:New |
| Force recalc on open | xlsx_edit.py f.xlsx --recalc |
| CSV -> styled xlsx | csv_to_xlsx.py in.csv out.xlsx |
| xlsx -> CSV | xlsx_to_csv.py f.xlsx out.csv --encoding utf-8 |
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"."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.--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.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.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.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).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.
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.--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.; delimiters and
decimal commas — use --delimiter ';' and expect strings like
"12,5" to stay strings.datetime/date objects. Dumps here emit ISO
strings.[ ] : * ? / \.xlsx_read.py out.xlsx --sheets and confirm sheet
names, dimensions, merged ranges, and chart counts match intent.--json and compare against the source values.--formulas lists them and that --recalc was applied.xlsx_restructure.py: read its JSON report, then re-run
--formulas and --sheets to confirm references and ranges landed
where expected.soffice --headless --convert-to pdf out.xlsx and inspect the PDF.