# XLSX Surgery > The physics of surgically editing an existing Excel/xlsx (OOXML) file. Use when you must change a few cells or structures in a workbook that already exists while leaving everything else byte-identical — formulas, styles, charts, pivot tables, external links. Covers why library round-trips silently corrupt parts you never touched, how to make minimal string/ZIP-level incisions, the five file-level landmines that real Excel refuses to open but permissive tools show as fine, and the streaming rules for reading hundred-megabyte worksheets without exhausting memory (the 50x memory law, on-disk point lookups, never materialize a whole sheet). - Skill: `poloplay0114/xlsx-surgery` (Agent Skill) - Install (CLI): `npx skillmds@latest add poloplay0114/xlsx-surgery` - Raw SKILL.md: https://api.skillmd.com/api/skills/poloplay0114/xlsx-surgery/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - Author: poloplay0114 (https://skillmd.com/u/poloplay0114) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/poloplay0114/xlsx-surgery --- # xlsx Surgery ## When to use this Any time you need to **modify an existing .xlsx** under the requirement "change only the cells or regions I named — **leave everything else byte-for-byte identical**." Especially when the original contains **formulas, fine-grained styles, charts, pivot tables, or external links**: the things a library round-trip quietly damages. An xlsx is a zip wrapping a pile of XML parts; a naive "load → edit → save" will **silently destroy parts you never touched**. This skill is the map of those landmines and how to walk around them. ### ★Trigger discipline (when you MUST check your work against this skill) **The trigger is not "when you sit down to write xlsx-manipulation code" — it is "any moment any code path touches an xlsx deliverable."** That includes **wiring up an existing module**, not just new code. A real incident: Rules 1/3/7 were written down in black and white, yet simply connecting "produce the deliverable" to an **existing** function (which internally did `load_workbook` + `wb.save`, a full-workbook rewrite) destroyed the charts anyway — because nobody walked that existing path through the rules **at the moment of wiring it up**. **Two hard rules — write them into the plan, run them before delivery:** 1. **Check before you wire**: any code path that **writes / converts / moves / saves-as** an xlsx deliverable (**including calls into existing modules**) gets walked through **Rules 1–7** before you connect it, and "this path uses ZIP surgery vs. a full rewrite" goes into the plan. No matter how harmless the existing function's name looks (`append_source` / `build` / `save_as`…), open it and read **how it actually writes the file**. 2. **The exit self-check is mandatory**: before delivery, run **Rule 3's exit self-check** — open the file in real Excel (or diff it against the original at the byte / chart-parts level) and confirm "it opens + the charts are there + the structure is equivalent." A green from a permissive tool (openpyxl/LO) does **not** count as passing. --- ## Core physics ### Rule 1: Minimal incision — change the target only, never rewrite the whole file or sheet Mainstream libraries (openpyxl, for instance) "load the whole workbook and save it back," which **re-serializes every part**. Anything the library does not understand (certain charts, pivot tables, external links, uncommon namespaces) can be dropped, downgraded, or rewritten. **The surgical way**: - Do **precise string-level replacement** on the **target part only** (one worksheet XML); move every other part across **untouched** (ZIP-level copy). - Swapping one sheet means swapping that one part's bytes — do not repack the entire workbook. - **Never parse a whole sheet's root element** (see the namespace landmine in Rule 2). ### Rule 2: Five file-level landmines that real Excel rejects and permissive tools cannot see Every one of these is **tolerated by an openpyxl / LibreOffice round-trip (it renders fine — a false green)**, and only surfaces when **real Microsoft Excel opens the file**. Typical symptom: "the file is corrupt and must be repaired," part XML error `0x808c0002` at "row 1, column 0." 1. **Whole-tree parsing destroys namespaces**: parse a whole worksheet with an XML tree library (ElementTree, say) and write it back out → it **renames** namespace prefixes (`mc:` → `ns1:`) and **deletes** namespace declarations it "believes are unused" as if they were garbage — except that declaration was referenced **inside an attribute's *value*** (`mc:Ignorable="x14ac"`, where `x14ac` is a prefix living in the attribute value; a tree library cannot read attribute values, so it deletes the declaration → the reference now points at an undefined prefix → the root element is broken). **Avoidance: never parse the root element; do string-level edits on the target element only.** 2. **Missing XML declaration**: the first line of every XML part must be ``. Some serialization paths (`ElementTree.tostring(encoding="unicode")`, for one) **emit no declaration** → Excel judges the part corrupt. **Avoidance: before writing back any `.xml`/`.rels` part, run a final check and add the declaration if it is missing.** 3. **A formula cell with no cached value (`` without ``)**: you wrote the formula node `` but no value node `` → before it recalculates, real Excel shows that cell as **blank or 0** (it trusts the cache); permissive tools evaluate on the fly, so they hide the problem. **Avoidance: when you write a formula, backfill the `` cache alongside it** (with a value from an independent source — see verification-discipline), or explicitly flag the file `fullCalcOnLoad`. 4. **Broken defined names (`definedName` → `#REF!`)**: after deleting rows, columns, or sources, the workbook's `` entries may point at ranges that no longer exist → they carry `#REF!` → Excel complains. **Avoidance: strip dead `` entries at the string level (do not parse the workbook root element); if all of them go, remove the `` container too.** 5. **Formulas flattened into dead values**: load in a values-only mode (`data_only=True`, for instance) and save → **live formulas become the dead values they last evaluated to**, and they will never update when the source changes. The value looks right today: this is the most insidious false green of all. **Avoidance: when touching a file with formulas, always preserve the formula expressions; never read values out with data_only and write them back as content.** ### Rule 3: A permissive tool's "green" is a false green — real Excel is the acceptance gate What the five landmines have in common: **a clean openpyxl round-trip, and LibreOffice opening the file, prove nothing about whether real Excel can open it**. Those tools are highly tolerant and will render a corrupt file as normal. **Final acceptance for an xlsx artifact must be a real Microsoft Excel actually opening it** (or a byte-level comparison against a known-good original); never take a permissive tool's success as a pass. (This echoes verification-discipline: verify in the user's real environment, not through a lenient proxy.) ### Rule 4: Inherit the whole cell — write the style with the value at the source, leave no "patch the styles later" step When you want to "copy a template cell's complete appearance (borders / fill / number format / font) and only swap the value inside," **write the style index and the value into the new cell in one step** (`value`). **Do not** "write values first and patch styles afterwards" — that second pass will eventually miss cells (especially blank ones that still carry borders or fill). **Blank cells inherit too** (a bordered empty cell still gets written as ``; do not skip it). Nail it in one pass — there is no second pass to forget. ### Rule 5: Inherit number formats per column, never blanket one format across the sheet Different columns in one table usually carry different number formats (thousands separators here, percentages there, dates in a third). When filling values, **each column inherits the number format of its corresponding template column**; do not apply one format to the whole sheet. A broken format does not change "the value is right" but it does change "this is correct" (see verification's multi-dimensional comparison). ### Rule 6: Structural insertion means "insert + shift," never "overwrite" When adding a column, a row, or a period (a monthly report gaining one more month column, a chart gaining one more data point), you **insert and push everything after it along** — you do **not overwrite some existing position**. Overwriting quietly eats whatever was there. Inserting a column → every column at or after the target shifts by `+1`; a chart's sliding window → the new data point is **added and the window shifts**, rather than clobbering the oldest entry. ### Rule 7: Move live objects (pivots/charts) verbatim + "refresh on load"; never rebuild them with a library Live objects like pivot tables and charts are almost guaranteed to come out distorted — or vanish — when a library rebuilds them. Instead: - **Verbatim ZIP-copy**: move the pivot/chart parts across untouched (caches included) and change only the **source data** they point at. - Set the **refresh-on-open** flag (`refreshOnLoad` for pivots) so Excel recomputes them from the new data when the file opens — you never compute their contents by hand. - As a bonus this is faster than the library route (no cost of rebuilding large objects). ### Rule 8: The physics of reading big files — stream, never materialize a whole sheet Rules 1–7 cover "how to write without breaking things"; this one covers "how to read without blowing up." An xlsx's XML body is often far larger than the file itself (compression is very effective), and **a mainstream library's normal load consumes roughly 50 times the file size in memory** (the official documentation says so outright: a 50MB file → 2.5GB of memory). For a worksheet in the hundreds of megabytes, loading the whole sheet or building a whole-sheet dictionary of cell objects **is guaranteed to exhaust memory. That is a documented property of the library, not your bug.** - **Always stream a large sheet**: read-only lazy loading + row-by-row iteration (iter_rows), which holds memory nearly constant; **never materialize the whole sheet** (no whole-sheet cell dict, no "read it all into a list first, then process"). - **Need repeated lookups → go to disk, not to memory**: stream it into an embedded database (SQLite, say — a single file, zero dependencies) and then point-query by cell (microseconds, near-zero memory). **Never pickle the whole dictionary as your persistence layer** — loading it back rebuilds the whole thing and the memory peak reappears exactly as before, which makes the work pointless. - **Peak memory must be pinned by an assertion**: peak RSS on the big-file path goes into the regression baseline (see engineering-economy Rule 12). "Constant memory" is a guarantee held down by a breaking test, not a claim. --- ## Case files from this project (supporting evidence, not required for the general rules) - **The core module, a string-level XML surgery module**: one financial-reporting automation project turned all of the above into a "minimal string-level" editing module. Its docstring says so from the first line: "**change only the target ``'s string; the ``/`` root elements, every namespace declaration, and every other cell are left untouched — never parse a whole sheet's root element**." - **All five landmines were struck for real, by the owner, during acceptance in real Excel**: - Rule 2-①: ET parsing a whole sheet turned `mc` into `ns1` and deleted the `xmlns:x14ac` that was referenced from the `mc:Ignorable="x14ac"` attribute value → `` corrupt at row 2 column 0, `0x808c0002` (the namespace-corruption detector is the dedicated check for exactly this landmine: does every prefix referenced by Ignorable have a declaration, and are there leftover `ns0/ns1` rewrites?). - Rule 2-②: `ET.tostring(encoding="unicode")` emits no declaration → Excel reports a part XML error; the declaration-completion function at the write boundary adds `` as the zip is written. - Rule 2-③: the formula-cache backfill function exists purely to fill in cached values for cells that have `` but no ``. - Rule 2-④: the dead-name filter strips invalid `` entries at the string level, and removes the entire `` container when they all go. - Rule 3: for that same corrupt file, "LO/openpyxl tolerate it = false green" is written verbatim in the comments of the declaration-completion function — only real Excel catches it. - **Rules 4/5**: the whole-cell inheritance writer writes a cell in one step (`s=` takes the entire style index from the corresponding cell of the April template, overwriting only value/formula; blanks inherit as whole cells too), under the iron rule "there is no separate 'patch the styles' step to miss"; the cell-by-cell comparator's column-anchor pairing aligns columns and compares them one by one (number formats included). - **Rule 6**: the column-shift function (after inserting, every col ≥ at_col shifts by `+count`), the in-place region replacement function (swap a rectangular region while preserving column order), and "add + shift" for a new month column; the chart's sliding window works the same way. - **Rule 7**: the three pivot-table deliverables use verbatim ZIP-copy + `refreshOnLoad` (faster than an openpyxl rebuild and lossless); the audit explicitly records "pivot recomputes live via refreshOnLoad." - **Rule 8**: one 296MB (uncompressed) dependency sheet — building a whole-sheet dictionary of a million cell values was OOM-killed twice in a row; after switching to "read_only + iter_rows streaming → batch writes into SQLite → cross-references via point lookups," the same sheet seeded in 158.5 seconds with zero OOM and constant memory. Peak RSS was then locked in as a baseline assertion plus a breaking test ("deliberately materialize the whole sheet → it must bark"). The 50x memory law only came to light afterwards, by reading the official documentation: everyone hits this. Checking external experience first (engineering-economy Rule 1) would have saved both OOMs.