xlsx-official

# XLSX Skill An Apache-2.0 toolkit for producing, editing, and reading Microsoft Excel (`.xlsx`) files. Written from scratch against the public [ECMA-376 / ISO/IEC 29500 (SpreadsheetML)](https://www.ecma-international.org/publications-and-standards/standards/ecma-376/) specification and built on permissively-licensed tooling (`openpyxl` MIT, `pandas` BSD-3-Clause, `lxml` BSD-3-Clause, optional `xlsxwriter` BSD-2-Clause, optional external binary `soffice` MPL 2.0) so it can be reused in commercial projects without restriction. ## Decision matrix | Situation | Path | Read first | |-----------|------|------------| | Build a workbook from a prompt / dataframe / raw values | Author with `openpyxl` (formulas + formatting) or `pandas` (bulk data) | [`create.md`](create.md) | | Edit an existing `.xlsx` — add rows, patch cells, refresh formulas | Load with `openpyxl`, preserve formulas & styles | [`edit.md`](edit.md) | | Only need to read the data out (analysis, ETL, quick QA) | `pandas.read_excel` + `openpyxl` for structural inspection | [`read.md`](read.md) | | Clean, aggregate, or transform tabular data before writing back | pandas pipeline, then hand back to openpyxl for final polish | [`analyze.md`](analyze.md) | | Deep structural edits (custom XML parts, defined names, VBA-free surgery) | Unpack → edit XML → repack | [`edit.md`](edit.md) → *Raw XML workflow* | | Recompute formula values before shipping | `scripts/bake.py` via LibreOffice | see *QA* below | If the task mixes several of these, do them in this order: **read → plan → edit/create → recalc → validate.** ## One-time environment setup > **Bundled runtime:** when the `MIMO_PYTHON` environment variable is set, skip the pip installs below — run every command with `python3`/`uv run` replaced by `"$MIMO_PYTHON"` (e.g. `"$MIMO_PYTHON" scripts/overview.py book.xlsx`). openpyxl/pandas/lxml/xlsxwriter are preinstalled in that interpreter; pip console scripts are unavailable, so always go through `"$MIMO_PYTHON" -m <module>`. A bundled LibreOffice (for `bake.py`/`pdf_out.py`) is exposed as `MIMO_SOFFICE` and picked up automatically. ```bash python3 -m pip install --upgrade openpyxl pandas lxml # Optional but recommended: python3 -m pip install --upgrade xlsxwriter # For formula recalc and PDF export: # macOS brew install --cask libreoffice # Debian/Ubuntu apt-get install -y libreoffice ``` Every script under `scripts/` uses only the standard library plus `openpyxl` and `pandas`. No proprietary dependencies. ## Common commands ```bash # 1. Describe a workbook (sheets, dimensions, formula count, sample rows) python scripts/overview.py input.xlsx # 2. Recalculate every formula, then flag any residual #REF! / #DIV/0! / etc. python scripts/bake.py output.xlsx # default 30s LibreOffice timeout python scripts/bake.py output.xlsx --timeout 60 # custom timeout # 3. Validate ZIP integrity, XML well-formedness, and openpyxl load python scripts/audit.py output.xlsx # 4. Convert to CSV (one file per sheet, or a single sheet by name/index) python scripts/csv_out.py input.xlsx out_dir/ # all sheets python scripts/csv_out.py input.xlsx out.csv --sheet 0 # first sheet # 5. Convert to PDF for visual QA (needs LibreOffice) python scripts/pdf_out.py output.xlsx # writes output.pdf next to it # 6. Unpack an .xlsx into readable XML parts (for surgical edits) python scripts/explode.py input.xlsx unpacked/ # 7. Repack an unpacked directory into a fresh .xlsx python scripts/assemble.py unpacked/ output.xlsx ``` Every script is a small, self-contained Python file. Read the top of the file for full CLI options. ## Authoring principles Excel is a **live calculation surface**, not a static table renderer. Users open workbooks and expect to change numbers, watch the rest update, and trust what they see. Keep that in mind: 1. **Use formulas, not hardcoded values.** Compute totals with `=SUM(...)`, not with a Python `sum()` written into the cell. When source data changes, the workbook must recompute itself. 2. **Put assumptions in dedicated input cells.** Reference them from formulas (`=B5*(1+$B$6)`), never inline (`=B5*1.05`). This is the single biggest determinant of whether a model is usable. 3. **One sheet per idea.** Inputs on one sheet, calculations on another, output on a third. Cross-sheet references (`Inputs!B5`) make dependencies explicit. 4. **Named styles beat ad-hoc formatting.** For anything reused (headers, totals, inputs, error markers), register a `NamedStyle` once and reapply. 5. **Freeze headers.** `sheet.freeze_panes = "A2"` (or `"B2"` if the first column is a row label) — every scrolling table needs this. 6. **Format numbers per column, in one pass.** Apply `cell.number_format` in a loop over the data range of each column (`for row in ws.iter_rows(min_col=3, max_col=3): ...`). Note: `column_dimensions['C'].number_format` does **not** reliably format cells you write afterwards — openpyxl-written cells carry their own style. 7. **Never rely on openpyxl to evaluate formulas.** It stores the string `"=SUM(...)"` and a cached previous value (if the file was opened before). Freshly written formulas have no cached value until LibreOffice or Excel recomputes. ## Number-format cheatsheet | Kind | Format string | Renders | |------|---------------|---------| | Plain integer with thousands | `#,##0` | `1,234` | | Currency (USD, hide zeros) | `$#,##0;($#,##0);"-"` | `$1,234` / `($1,234)` / `-` | | Currency (2 dp) | `$#,##0.00` | `$1,234.56` | | Percentage (1 dp) | `0.0%` | `12.3%` | | Multiplier | `0.00"x"` | `1.35x` | | Year as text | `0` | `2026` (no comma) | | Short date | `yyyy-mm-dd` | `2026-07-04` | | Long date | `dddd, mmmm d, yyyy` | `Saturday, July 4, 2026` | | Scientific | `0.00E+00` | `1.23E+04` | Use parentheses for negatives in financial contexts; use a leading minus for scientific or engineering contexts. ## Color and style conventions There is no universal standard, but if the user does not specify one, this palette is safe for internal financial or operational models: | Purpose | Value | Rationale | |------------------------|-------------------|-----------| | Header text | `#1F1F1F` on `#F2F2F2` fill | High contrast, print-safe | | Input (user changes) | Blue `#0033CC` | Visually distinct from formulas | | Formula (calculated) | Black `#1F1F1F` | Default reading color | | Same-workbook link | Green `#116611` | "Comes from elsewhere in this file" | | Cross-file link | Red `#B22222` | "Fragile — points outside this file" | | Assumption to review | `#FFF2CC` fill | Yellow highlight, still readable in b/w | | Error / warning | `#FFC7CE` fill, `#9C0006` text | Excel's built-in "bad" style | Override these whenever the file has an existing template — match it exactly. ## QA checklist — always run before declaring done **Assume something is wrong.** Excel opens broken files quietly: a stray `#REF!`, an off-by-one range, a formula that quietly evaluates to `0`. Verify explicitly. 1. **Recalculate formulas.** openpyxl does not evaluate them — LibreOffice does. ```bash python scripts/bake.py output.xlsx ``` Read the JSON output. `status: "clean"` with `error_count: 0` is the only acceptable result. 2. **Structural validation.** ```bash python scripts/audit.py output.xlsx ``` Confirms the ZIP is well-formed, all XML parts parse, and openpyxl can round-trip the file. 3. **Spot-check the values.** Load with `data_only=True` after recalculation and read the cells you expect to be non-zero: ```python from openpyxl import load_workbook wb = load_workbook('output.xlsx', data_only=True) assert wb['Summary']['B10'].value == expected_total ``` 4. **Visual sanity.** Render a PDF and scan the first and last sheets for: - Columns clipped because widths were left at default. - Numbers displayed as `########` (column too narrow for the format). - Formulas showing as text (missing leading `=`, or a leading apostrophe). - Headers repeated per page, print area set for large sheets. ```bash python scripts/pdf_out.py output.xlsx ``` If any of these fail, fix and re-run. Do not paper over. ## Common formula pitfalls - **`#DIV/0!`** — wrap divisions defensively: `=IF(B2=0,0,A2/B2)` or `=IFERROR(A2/B2, 0)`. Prefer `IF` so real zeros stay visible; use `IFERROR` only for values that must always be numeric. - **`#REF!`** — a cell reference points to a deleted row/column. Rebuild the formula against current coordinates; do not just delete the offending cell. - **`#VALUE!`** — text where a number is expected, usually from a stray label in a data column. Check the column dtype in pandas before writing. - **`#NAME?`** — the formula uses a function name Excel does not recognize. Common causes: typos (`=SUMM(...)`), locale-specific separators (`;` vs `,`), or dynamic-array functions like `FILTER` in older Excel versions. - **`#N/A`** — usually from `VLOOKUP` / `XLOOKUP` / `MATCH` failing to find a key. Wrap in `IFNA(..., default)` when a miss is expected. - **Cross-sheet reference typos.** `Sheet1!A1` works; `Sheet 1!A1` needs quoting: `'Sheet 1'!A1`. openpyxl accepts either — Excel demands the quoting. ## What is out of scope - **`.xls` (Excel 97-2003 binary).** Convert to `.xlsx` first: `soffice --headless --convert-to xlsx old.xls`. - **VBA / macros / `.xlsm`.** This skill does not emit or execute macros. - **Password-protected or encrypted workbooks.** openpyxl cannot read encrypted files; strip protection through Excel/LibreOffice first. - **Live Excel automation.** For COM (Windows) or AppleScript (macOS) integration, use a dedicated automation library — this toolkit is file-in / file-out. ## Where each detail lives - **Creating from scratch**: [`create.md`](create.md) — workbooks, sheets, formulas, formatting, named styles, charts, images, freeze panes, print setup. - **Editing / templating**: [`edit.md`](edit.md) — patching cells, appending rows, inserting columns, preserving formulas & styles, unpack/repack for deep XML surgery, defined names, data validation. - **Reading / extracting**: [`read.md`](read.md) — pandas reads, structural inspection, formula extraction, conversion to CSV / TSV / PDF. - **Data analysis**: [`analyze.md`](analyze.md) — pandas pipelines, reshaping, groupby, joins, then handing back to openpyxl for the final writeable artifact. - **Scripts**: [`scripts/`](scripts/) — CLI utilities used throughout.