xlsx

1---2name: xlsx3description: Create, read, edit Excel .xlsx workbooks and CSVs.4version: 1.1.05author: Nous Research6license: MIT7platforms: [linux, macos, windows]8metadata:9  hermes:10    tags: [excel, spreadsheet, xlsx, csv, openpyxl, productivity]11    category: productivity12    related_skills: [docx, pdf, powerpoint]13---14 15# Xlsx Skill16 17Work with Excel .xlsx workbooks using Python and openpyxl: build styled18multi-sheet workbooks with formulas and charts, inspect or dump existing19files, edit cells and structure, and convert to/from CSV. All helper20scripts are argparse CLIs that print JSON and use explicit UTF-8 I/O.21 22## When to Use23 24- Creating .xlsx reports: multiple sheets, number formats, styling,25  merged cells, freeze panes, autofilter, conditional formatting,26  charts, data-validation dropdowns, native Excel tables, defined27  names, hyperlinks, cell notes, sheet protection.28- Reading a workbook: sheet inventory, dumping data as JSON or CSV,29  listing formulas vs cached values, notes, defined names, tables.30- Editing existing files: set cells, append rows, insert/delete31  rows/columns (reference-aware via `xlsx_restructure.py`),32  copy/rename sheets, tables, names, notes, protection.33- Recalculating formulas headlessly via LibreOffice34  (`xlsx_recalc.py`).35- CSV interop with type inference and non-UTF-8 encodings.36- Not for the legacy .xls binary format (use LibreOffice to convert37  first: `soffice --headless --convert-to xlsx old.xls`).38 39## Prerequisites40 41- Python 3.10+ with `openpyxl` (`pip install openpyxl`). No other42  third-party packages are needed; everything else is stdlib.43- Optional: LibreOffice (`soffice`) for headless recalculation or44  format conversion.45 46## How to Run47 48Run the helper scripts with the `terminal` tool from this skill's49`scripts/` directory (every script supports `--help`):50 51```bash52python scripts/xlsx_create.py spec.json report.xlsx   # build from JSON spec53python scripts/xlsx_read.py report.xlsx --sheets      # inventory54python scripts/xlsx_read.py report.xlsx --json --sheet Data55python scripts/xlsx_read.py report.xlsx --formulas56python scripts/xlsx_edit.py report.xlsx --sheet Data --set B2=42 --recalc57python scripts/xlsx_restructure.py report.xlsx --sheet Data --insert-rows 3:258python scripts/xlsx_recalc.py report.xlsx59python scripts/csv_to_xlsx.py data.csv out.xlsx --encoding utf-860python scripts/xlsx_to_csv.py report.xlsx out.csv --sheet Data61```62 63Author the JSON spec with `write_file`, inspect script JSON output with64`read_file` or directly from stdout.65 66## Quick Reference67 68| Task | Command |69|---|---|70| Create workbook from spec | `xlsx_create.py spec.json out.xlsx` |71| Sheet names + dimensions | `xlsx_read.py f.xlsx --sheets` |72| Dump sheet as JSON | `xlsx_read.py f.xlsx --json --sheet S` |73| Dump sheet as CSV | `xlsx_read.py f.xlsx --csv --out d.csv` |74| List formulas + cached values | `xlsx_read.py f.xlsx --formulas` |75| Set a cell / formula | `xlsx_edit.py f.xlsx --set "A1==SUM(B:B)"` |76| Append a row | `xlsx_edit.py f.xlsx --append '[1,"x",true]'` |77| Insert 2 rows, refs NOT shifted | `xlsx_edit.py f.xlsx --insert-rows 3:2` |78| Insert 2 rows, refs shifted | `xlsx_restructure.py f.xlsx --insert-rows 3:2` |79| Delete a column, refs shifted | `xlsx_restructure.py f.xlsx --delete-cols B` |80| Create a native table | `xlsx_edit.py f.xlsx --add-table Sales:A1:C9` |81| Append inside a table | `--table-append 'Sales=["West",5]'` |82| List tables | `xlsx_edit.py f.xlsx --list-tables` |83| Defined names | `--define-name "Rates='Data'!$B$2:$B$9"` / `--delete-name Rates` / `xlsx_read.py f.xlsx --names` |84| Hyperlink | `--hyperlink "A1=https://example.com|Docs"` |85| Cell note | `--note "B2=Check this|Reviewer"`; read via `xlsx_read.py f.xlsx --notes` |86| Protect sheet (see Pitfalls) | `--protect your-password --unlock B2:B9` |87| Recalculate via LibreOffice | `xlsx_recalc.py f.xlsx` |88| Copy / rename sheet | `--copy-sheet Src:New --rename-sheet Old:New` |89| Force recalc on open | `xlsx_edit.py f.xlsx --recalc` |90| CSV -> styled xlsx | `csv_to_xlsx.py in.csv out.xlsx` |91| xlsx -> CSV | `xlsx_to_csv.py f.xlsx out.csv --encoding utf-8` |92 93## Procedure94 951. **Create**: write a JSON spec (schema documented in96   `xlsx_create.py --help` and its docstring). Each sheet supports97   `rows` (scalars or styled cell objects), sparse `cells` overrides,98   `column_widths`, `row_heights`, `merges`, `freeze_panes`,99   `autofilter`, `conditional_formats` (cell_is rules and color100   scales), `charts` (bar/line/pie from cell ranges),101   `validations` (list dropdowns), `tables` (native Excel tables with102   a style name), and `protection`. Workbook-level `defined_names`103   maps names to refs. Cell objects also take `hyperlink` and `note`.104   Typed values: JSON numbers/bools105   pass through; dates use `{"value": "2026-01-31", "type": "date"}`.106   Number formats are Excel format strings: currency `"$#,##0.00"`,107   percent `"0.0%"`, date `"yyyy-mm-dd"`.1082. **Formulas**: set with `"formula": "SUM(B2:B9)"` in the spec or109   `--set "C1==SUM(A:A)"` in the editor. When writing formulas, add110   `"full_calc_on_load": true` (spec) or `--recalc` (editor); this sets111   the workbook's `fullCalcOnLoad` flag so Excel/LibreOffice recompute112   everything on open. openpyxl itself NEVER evaluates formulas.1133. **Read**: `--sheets` for inventory (names, dimensions, merged114   ranges, chart count, tables, protection, defined names),115   `--json`/`--csv` for data, `--formulas` to116   pair each formula string with its cached result, `--notes` for117   cell comments, `--names` for defined names. Cached results118   exist only if the file was last saved by a real spreadsheet app;119   files fresh from openpyxl return `null` there. To materialize120   results headlessly run `xlsx_recalc.py file.xlsx` (uses121   LibreOffice; prints `{"recalculated": false, ...}` and exits 0122   when `soffice` is absent), then reload with `--data-only`.1234. **Edit**: `xlsx_edit.py` applies renames/copies first, then124   structural row/column changes, then `--set`/`--append`. It edits in125   place unless `--out` is given — copy the file first if you need the126   original.1275. **Restructure**: for insert/delete on sheets that have formulas,128   merges, tables, or filters, use `xlsx_restructure.py` instead of129   `xlsx_edit.py`. It rewrites formula references on ALL sheets130   (absolute `$` refs, ranges, cross-sheet refs), shifts merges,131   autofilter, freeze panes, validation and conditional-format132   ranges, table refs, defined names, and row/column dimensions, then133   prints a JSON report including a `not_shifted` list. Rules and134   limits: `references/restructuring.md`.1356. **CSV interop**: `csv_to_xlsx.py` infers int/float/bool/ISO-date136   per cell and styles the header row; `xlsx_to_csv.py` writes ISO137   dates and blank strings for empty cells. Both default to UTF-8 and138   accept `--encoding` (e.g. `utf-8-sig` for Excel-friendly BOM,139   `cp1252` for legacy Windows exports).140 141## Converting to PDF142 143LibreOffice converts headlessly (also works for CSV export of a single144sheet):145 146```bash147soffice --headless --convert-to pdf report.xlsx --outdir out/148soffice --headless --convert-to csv report.xlsx --outdir out/  # 1st sheet only149```150 151Only the first sheet lands in a CSV; for other sheets use152`xlsx_to_csv.py --sheet NAME`. If `soffice` is missing, install153LibreOffice or hand the file to the user unconverted.154 155## Pitfalls156 157- **openpyxl does not calculate.** Formula results are available only158  via `load_workbook(path, data_only=True)` and only when the file was159  previously saved by Excel/LibreOffice. Otherwise you get `None`.160- **`xlsx_edit.py` insert/delete does not shift references** (raw161  openpyxl behavior). Use `xlsx_restructure.py`, which does — but even162  it cannot move chart anchors, images, or conditional-format RULE163  formulas; read its JSON report's `not_shifted` list and164  `references/restructuring.md`.165- **Sheet protection is NOT security.** `--protect` sets the standard166  xlsx sheet-protection hash: it signals "don't edit this" to167  well-behaved apps and nothing more. Anyone can strip it by editing168  the zip's XML or unchecking it in LibreOffice. Never rely on it for169  confidentiality or integrity; it does not encrypt anything.170- **`data_only=True` then save** silently discards all formulas171  (cached values replace them). Never save a workbook loaded that way172  unless that is the goal.173- **Loading strips charts/images**: openpyxl does not round-trip174  charts, so editing a charted workbook and saving drops the charts.175  Re-add charts after editing, or avoid re-saving charted files.176- **CSV locale traps**: always pass explicit encodings (the scripts177  already do) and remember European CSVs often use `;` delimiters and178  decimal commas — use `--delimiter ';'` and expect strings like179  `"12,5"` to stay strings.180- **Dates are datetimes**: Excel stores dates as serial numbers;181  openpyxl returns `datetime`/`date` objects. Dumps here emit ISO182  strings.183- Sheet names are capped at 31 chars and reject `[ ] : * ? / \`.184 185## Verification186 187- After creating: `xlsx_read.py out.xlsx --sheets` and confirm sheet188  names, dimensions, merged ranges, and chart counts match intent.189- Dump data with `--json` and compare against the source values.190- After edits: re-dump the touched range; if formulas were written,191  confirm `--formulas` lists them and that `--recalc` was applied.192- After `xlsx_restructure.py`: read its JSON report, then re-run193  `--formulas` and `--sheets` to confirm references and ranges landed194  where expected.195- For a full visual check, open in LibreOffice:196  `soffice --headless --convert-to pdf out.xlsx` and inspect the PDF.197