xlsx

# xlsx A pragmatic, recipe-first guide for reading, editing, creating, and recalculating `.xlsx` / `.xlsm` / `.csv` / `.tsv` files. The default pairing is **pandas for tabular data + openpyxl for formulas, styles, named ranges, and charts**. Recalculation through [`scripts/recalc.py`](scripts/recalc.py) (LibreOffice headless) is mandatory before delivery — openpyxl writes formulas as strings and never evaluates them. > **Formula-first.** A spreadsheet without live formulas is just a > CSV with a fancier extension. **Every computed value** — totals, > averages, growth rates, ratios, cross-sheet references, percent > changes, anything derivable from other cells — **must be written as > a live `=…` formula**, not as the pre-computed number. Hard-coded > numbers belong only in the **Assumptions** block (inputs the user > can flip to re-run the model). When in doubt, write the formula. > Full convention in §5 and > [`docs/conventions-guide.md`](docs/conventions-guide.md) §4. > > **❌ The most common anti-pattern.** Loading a workbook with pandas, > computing derived columns in Python (`df["total"] = df["a"] + df["b"]`, > `df.groupby(...).sum()`, etc.), then writing the result back with > `df.to_excel(...)` ships **static numbers** — the workbook becomes a > dead snapshot the moment any input changes. Use pandas/polars only > to load and clean **raw inputs**; emit every derived value as a live > `=` formula via openpyxl (§3.2). This rule applies regardless of how > easy it would be to compute the value in Python first. > **Spreadsheet output only.** For Word documents, PowerPoint slides, > HTML reports, standalone Python scripts, database pipelines, or the > Google Sheets API, switch to the matching skill. ## Operational rules — read before doing anything > **1. Match user query against [`docs/pitfalls-index.md`](docs/pitfalls-index.md) > FIRST.** It contains 8 production-ready **canonical query templates** > (X1–X8), each with a `Match signatures` block (sample queries) and a > complete executable prompt that already encodes Formula-first, recalc > verification, formula-count gate, sample-data-integrity, slicer > handling, and every other rule below. Workflow: > > 1. Scan the Quick lookup table — match user's query keywords to a row. > 2. **Copy the matching canonical query verbatim**, substitute the > `Slots` (e.g. `{INPUT}`, `{COMPANY}`, `{OUTPUT_XLSX}`) with the > user's actual values, and execute step-by-step. > 3. Multiple partial matches → fuse: take the strictest verification > from each, never relax a constraint. > 4. No match → fall back to the Decision Tree / per-section guides below. > > Do NOT skip verification steps in the canonical queries — every > "ship a wrong workbook" failure traces back to skipping recalc, > total_formulas check, or row-count canary. > **2. Formula-first is non-negotiable.** A delivery that ships static > numbers where formulas were possible is a **failed delivery** — even > if every number is numerically correct the moment you saved it. The > user's first edit will expose the lie. Hardcode only the Assumptions > block; everything derivable from other cells goes in as `=…`. Full > regime in §5. > **3. `total_formulas == 0` is a red flag, not a green light.** A > "success" recalc with zero formulas means the workbook is a static > dump — the user can't audit, can't re-run scenarios, can't > introspect derivations. Treat it as a delivery failure and rewrite > via §3.2 / openpyxl `=` formulas. The only legitimate exception is > a workbook the user explicitly asked to be a static snapshot > (in which case `pandas.to_excel` is the right tool, not this skill). > **4. Source-data-integrity rule — never `df.sample(N)` / `df.head(N)` > on the raw sheet.** Down-sampling 400k rows to 100k "because openpyxl > writes are slow" silently destroys every aggregation built on top — > the pivot or `=SUMIF` summary will be off by ~75% and look entirely > plausible. **Write the full row count, even if it takes minutes.** > If write throughput is the real bottleneck, switch the writer > (xlsxwriter §3.3, or `Workbook(write_only=True)` streaming pattern > in [`docs/advanced-reference.md`](docs/advanced-reference.md) §6), > never the sample size. > **5. Summaries must be Excel-native — pivot tables or `=SUMIFS` / > `=COUNTIFS` over the Raw sheet, never Python `groupby` written back > as values.** This is the same rule as §3 phrased for the most > common offender. The user expects: edit a Raw row, hit recalc, see > the totals move. Static summaries break that contract silently. > **6. Slicers cannot be authored from scratch by openpyxl.** The > `xl/slicers/*.xml` part requires GUID-bound pivot cache references > that openpyxl has no API for. Two production paths: > (a) template-inheritance — author a `template.xlsx` once in Excel > or LibreOffice with the slicer wired to a named range, then > `load_workbook(template) → write into named range → save as new`; > (b) raw-XML transplant from a known-good template via > `scripts/office/{unpack,pack}.py`. **If the user asks for a slicer > and you have no template, say so before silently downgrading to a > static dropdown.** > **7. Don't suppress stderr.** `2>/dev/null` is **never** the right > choice in this skill (`recalc.py`, `soffice`, `pdfinfo`, `unpack.py`, > `pack.py`). On failure you lose the only signal that explains why > and have to rerun blind. If output is too noisy, redirect to a log > file and grep on demand: > ```bash > python scripts/recalc.py file.xlsx 60 2>/tmp/recalc.log > # If JSON shows status != "success" or exit != 0, then: > # grep -in "error\|trace\|fail" /tmp/recalc.log | head -20 > ``` > **8. When the user states a numeric range for a derived value, > enforce it in the formula.** "Composite score 0–100" → wrap the > formula with `=ROUND(MIN(MAX(raw, 0), 100), 1)`. Don't ship a > 0–1804 column and blame "data anomaly". Spot-check `MIN()` / > `MAX()` of the output column after recalc. > **9. 500k+ rows: pandas/polars first, not openpyxl row walking.** > For large tabular inputs (roughly **500,000+ rows**, or hundreds of MB), > default to `pandas`/`polars` for reading, filtering, type normalization, > joins, and QA spot-checks. Do **not** iterate cell-by-cell with openpyxl to > inspect or transform the source workbook — it is too slow and encourages > accidental sampling. Use openpyxl only at the output boundary for formulas, > styles, charts, templates, and final `.xlsx` assembly. If the output is a > static raw-data dump, `DataFrame.to_excel`/`xlsxwriter` is acceptable; if it > contains derived values, write full raw rows and Excel-native `=` formulas. > **10. Non-standard XLSX packages: trust recalc raw-XML fallback.** > Workbooks with heavy merged cells, charts/drawings, or vendor-generated XML > can make openpyxl crash even after LibreOffice recalculated successfully. > `scripts/recalc.py` now catches openpyxl parse failures and falls back to a > raw worksheet XML scanner. If JSON returns `scanner: "raw_xml_fallback"` with > `status: success` / `errors_found`, treat it as authoritative and do not > retry blindly. Use the `compatibility_hint` field to decide whether to avoid > downstream openpyxl rewrites. Details in [`docs/recalc-guide.md`](docs/recalc-guide.md) §10. For deeper material — creation and edit recipes, recalc internals, financial-model conventions, large-file alternatives — see [`docs/`](docs/). --- ## 1. Scope and When to Use This skill covers the everyday spreadsheet chores an LLM agent is most often asked to perform end-to-end: - inspecting an existing workbook (sheet names, merged regions, headers) - reading tabular data into pandas / polars for analysis or QA - creating a new workbook with formulas, styles, named ranges, and charts - editing an existing workbook in place (insert / delete / restyle / replace) - recalculating every formula via [`scripts/recalc.py`](scripts/recalc.py) and verifying `total_errors == 0` in the JSON - enforcing the financial-model conventions in §5 on every delivery - converting between tabular formats (`csv` ↔ `xlsx` ↔ `ods` ↔ `tsv`) Use a different approach when: - the deliverable is a Word document, slide deck, standalone script, or database pipeline — switch to the matching skill (`docx`, `pptx`, etc.) - the workflow is read-only and the user just wants the cell values printed — `extract-text` (`docs/advanced-reference.md` §5) is enough - the workbook lives behind the Google Sheets API rather than on disk --- ## 2. Decision Tree ``` What does the user want from / for this workbook? | +-- Read tabular data only ───────────────> pandas / polars -> §3.1, §3.4 +-- Inspect quickly without code ─────────> extract-text CLI -> docs/advanced-reference.md §5 +-- Create a new workbook ────────────────> openpyxl -> §3.2 +-- Edit an existing workbook in place ───> openpyxl -> §3.2 +-- Output has any derived/computed cells > openpyxl + `=` formulas -> §3.2, §5 +-- Very large workbook (500k+ rows) ─────> pandas/polars read + openpyxl/xlsxwriter output -> §3.1, §3.4, docs/advanced-reference.md §6 +-- Recalculate formulas (mandatory) ─────> scripts/recalc.py -> §4.1 +-- Convert format (csv ↔ xlsx ↔ ods) ────> pyexcel -> §3.5 +-- Sandboxed env (no AF_UNIX) ───────────> office.soffice shim -> §4.1 ``` **Default rule.** Read with pandas, write and edit with openpyxl, recalculate with `scripts/recalc.py`. Drop to xlsxwriter when the workload is write-only and throughput-bound; drop to polars when the input file is too big for pandas to hold in memory. **Never use `pandas.to_excel` / `polars.write_excel` for derived/computed values — those write static numbers, not live formulas. See §3.1's ⚠️ box.** --- ## 3. Library Cookbook Five subsections, one per library. Each entry is a minimum viable example plus a short note. Deeper recipes — full styling, conditional formatting, charts, edit gotchas — live in [`docs/create-edit-guide.md`](docs/create-edit-guide.md). ### 3.1 pandas — tabular read / write (default) ```python import pandas as pd frame = pd.read_excel("mau_forecast.xlsx", engine="openpyxl") # first sheet sheets = pd.read_excel("mau_forecast.xlsx", sheet_name=None) # {sheet: DataFrame} frame.to_excel("clean_mau.xlsx", sheet_name="clean", index=False) ``` > ⚠️ **`to_excel()` writes static numbers only — no formulas survive.** > Use it strictly for **raw data dumps**: cleaned input data, exported > query results, anything where every cell is itself a primary value. > The moment the deliverable contains a derived value — totals, > averages, ratios, growth rates, anything computable from other cells > — switch to §3.2 and write it as a live `=` formula. **Never** do > `df["total"] = df["a"] + df["b"]; df.to_excel(...)`: the workbook > will look fine until the user edits an input and discovers the > "totals" don't move. For aggregations across rows/sheets, write the > raw rows here and emit `=SUM(...)` / `=SUMIF(...)` on the openpyxl > side (`docs/advanced-reference.md` §6 has the worked large-file > example). Default for any "read tabular data" job. For 500k+ rows, prefer pandas/polars for source inspection, filtering, joins, and QA; avoid openpyxl cell-by-cell source traversal. Pair with openpyxl only on the write side when you need formulas or formatting. ### 3.2 openpyxl — create and edit (default for write + edit) ```python from openpyxl import Workbook, load_workbook from openpyxl.styles import Font book = Workbook() # create sheet = book.active sheet["A1"], sheet["B1"], sheet["C1"], sheet["D1"], sheet["E1"] = ( "Quarter", "MAU (mm)", "Tokens/MAU", "Take-rate", "ARR (¥mm)" ) # Inputs (Assumptions) — hardcoded primary values; §5.2 requires blue. input_font = Font(color="0000FF") sheet["A2"], sheet["B2"], sheet["C2"], sheet["D2"] = "2025Q3", 148, 27, 0.85 for ref in ("B2", "C2", "D2"): sheet[ref].font = input_font # Derived ARR — references inputs, not literal numbers; §5.2 requires black. sheet["E2"] = "=B2*C2*D2" sheet["E2"].font = Font(color="000000") book.save("mau_forecast.xlsx") book = load_workbook("mau_forecast.xlsx") # edit book["Sheet"]["D2"] = 0.90 # flip take-rate # E2 still says "=B2*C2*D2" — recalc.py will refresh the cached value book.save("mau_forecast.xlsx") ``` The edit example flips the **input** (`D2`), not the result — that is the whole point of formula-first. If `E2` had been written as the literal `=148*27*0.85`, this edit would have left ARR stale. `data_only=True` is **read-only safe only** — saving such a workbook permanently replaces every formula with its cached value. See [`docs/create-edit-guide.md`](docs/create-edit-guide.md) §7. ### 3.3 xlsxwriter — write-only throughput ```python import xlsxwriter book = xlsxwriter.Workbook("big_table.xlsx") sheet = book.add_worksheet("Tokens") for r, row in enumerate(rows): sheet.write_row(r, 0, row) sheet.write_formula(0, 5, "=SUM(F2:F100001)") book.close() ``` Faster than openpyxl on six-figure-row writes; cannot reopen what it wrote. Reach for it when the workbook is the terminal output. ### 3.4 polars — fast read for huge files ```python import polars as pl frame = pl.read_excel("big.xlsx") as_pandas = frame.to_pandas() # interop ``` > ⚠️ **`polars.write_excel()` writes static numbers — same trap as > `pandas.to_excel`.** No formulas, no charts, no styles. Polars is a > read-side accelerator for files above ~500k rows; for the write side, > always pair with openpyxl and emit derived values as `=` formulas > (`docs/advanced-reference.md` §6 has the worked large-file pattern). ### 3.5 pyexcel — format-agnostic conversion ```python import pyexcel as pe records = pe.get_records(file_name="upload.xlsx") # also csv / ods / tsv pe.save_book_as(file_name="raw.csv", dest_file_name="clean.xlsx") ``` Use it when the input format is unknown ahead of time, or when the pipeline accepts several formats. --- ## 4. Recalculation Routes openpyxl writes formulas as strings and never evaluates them. Recalculation is mandatory before delivery; it refreshes the cached values and surfaces the seven Excel error markers. ### 4.1 `scripts/recalc.py` — LibreOffice headless (default) ```bash python scripts/recalc.py mau_forecast.xlsx 30 ``` Sample success output on stdout: ```json { "status": "success", "total_errors": 0, "total_formulas": 42