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 <?xml version="1.0" …?>. 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 (<f> without <v>): you wrote the formula node <f> but no value node <v> → 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 <v> 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 <definedName> entries may point at ranges that no longer exist → they carry #REF! → Excel complains. Avoidance: strip dead <definedName> entries at the string level (do not parse the workbook root element); if all of them go, remove the <definedNames> 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 (<c r=.. s=template's style index>value</c>). 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 <c r=.. s=S/>; 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 <c>'s string; the <worksheet>/<workbook> 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 → <worksheet> 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 <?xml … standalone="yes"?> as the zip is written.
  • Rule 2-③: the formula-cache backfill function exists purely to fill in cached values for cells that have <f> but no <v>.
  • Rule 2-④: the dead-name filter strips invalid <definedName> entries at the string level, and removes the entire <definedNames> 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.