xlsx-win: certify an audit-first XLSX debloater/optimizer and external-link freeze workflow

[dachent]

Parent: #33
Related: #39, #41, #79, #84
Baseline reviewed: main at af1aab7

Decision

Build an audit-first Workbook Inspector and Conservative Compactor, then finish a controlled Windows/Excel bake-off before selecting an execution path.

GO now: read-only bloat/dependency audit, representative corpus, reproducible bake-off, hash-bound plans, and change-catalog design.
NO-GO now: a universal OOXML garbage collector, automatic cleanup, a production XML transformer, or a new XlsxWinOptimizer.exe.

The useful product is not “make every workbook as small as possible.” It is:

1. explain why a workbook is large or slow;
2. identify cleanup that is safe enough to review;
3. create a smaller staged copy only after explicit approval;
4. optionally freeze selected external sources;
5. prove what changed and flag everything unexplained.

No-op is a valid successful outcome. File size, Excel open/save time, calculation performance, portability, and confidential-data sanitation are separate objectives and must be reported separately.

Why this is needed

Investment-banking models and transaction templates are copied and reused for years. They accumulate:

• styled blank cells and inflated serialized/used ranges;
• excessive or duplicated styles;
• stale external formulas, names, relationships, caches, and metadata;
• old comments, drawings, print settings, validations, conditional formatting, and hidden structures;
• large media, pivot/query/Data Model caches, and embedded objects.

The effects are familiar: unexpectedly large files, slow open/save, unstable editing, email/data-room friction, fragile portability, and accidental disclosure of client paths or source workbooks. “Bloat” is nevertheless only a symptom. A large pivot cache or embedded object may be required; a small workbook can still calculate slowly because of volatile formulas, whole-column references, or connections.

Microsoft exposes two relevant native precedents:

• Inquire: Clean Excess Cell Formatting can reduce spreadsheet bloat, but Microsoft recommends a backup, warns that the operation cannot be undone, and notes that it can sometimes increase file size.
• Check Performance / Cleanup Cells detects cells with formatting, blanks, or non-printing content that consume space.

These are useful interactive tools, not a deterministic, reviewable, corpus-tested workflow with source protection, external-link policy, before/after evidence, and automation-safe verification.

Product contract

Mode 1 — audit (default)

Audit is strictly read-only: no Excel launch by default, no workbook sheet added, and input SHA-256 plus modification time must remain unchanged.

Produce a canonical versioned sidecar report containing:

• compressed and uncompressed bytes by package part and by worksheet;
• three sheet extents: meaningful content, intentional structure, and serialized/styled extent;
• stored-cell counts split into formulas, constants, explicit blanks, style-only cells, and other structured cells;
• style counts, exact duplicates, unused candidates, number formats, protection, and proximity to Excel style limits;
• shared strings, comments/notes, hyperlinks, validations, conditional formatting, tables, names, merges, print areas, drawings, media, embeddings, connections, pivots, slicers, external links, VBA, signatures, encryption/protection, and Data Model presence;
• estimated savings by candidate operation, confidence, blockers, and whether native Excel is required;
• Excel open/save timing and LinkSources reconciliation only in an explicit deep-audit pass.

The report must distinguish detected, cross-checked, unresolved, and out of scope. It must never infer that a blank-looking rectangle is disposable merely from the last non-empty cell.

Mode 2 — execute (explicit opt-in)

Execution consumes a reviewed plan bound to the exact input SHA-256. It must:

• require a new output path and never mutate the source;
• stage locally and publish only a fully verified output;
• reject a stale plan, existing destination, insufficient disk space, or unsupported workbook profile before mutation;
• execute only named operations against exact ranges/sources;
• preserve the last valid staged artifact on failure but publish nothing;
• reopen the output in native Excel with no repair, then rerun static verification;
• make a second optimizer pass a semantic no-op.

Initial executable operation, if earned by the bake-off: remove formatting/serialized style-only cells only from approved blank tail rectangles. Do not delete rows/columns or mutate cells inside the intentional-structure extent. Excel Range.ClearFormats is not cosmetic-only: it removes cell formatting and can affect number formats and protection-related formatting. Approved regions must therefore be proven disposable, not merely visually empty.

External dependency catalog and freeze

Cataloging external dependencies is independently valuable for portability, closing books, and disclosure review. The audit should enumerate:

• serialized external cell formulas and their saved values;
• external defined names;
• Excel external-link parts/relationships;
• chart, validation, conditional-formatting, query, OLE/DDE, add-in, XLM/VBA/UDF, and dynamic INDIRECT-like surfaces where detectable;
• source-level results from Excel Workbook.LinkSources, reconciled against the static inventory.

Default output is a redacted source-and-range-cluster sidecar. A raw occurrence-level JSONL/CSV is opt-in because paths and formulas can disclose usernames, servers, deal names, clients, and internal topology. An embedded workbook sheet is optional and sanitized; embedding the exhaustive catalog by default would mutate audit output, re-bloat the file, and preserve sensitive paths in the deliverable.

Policy:

• keep is the default;
• break is a separate semantic operation, not a size optimization;
• break only explicitly selected sources, never “all links” by default;
• do not refresh before breaking in v1;
• block missing/error/stale cached values unless explicitly overridden;
• use Excel's supported source-level Workbook.BreakLink, which converts linked formulas to values;
• rescan afterward and prove that targeted links disappeared while untargeted formulas, sources, and cached results did not change.

If Excel reports a source that static analysis cannot attribute, safe mode must block the break and report the coverage gap.

Change catalog

Every run emits one canonical sidecar manifest. “Comprehensive” means no unreported operation or delta, not millions of repetitive rows in the primary report.

Record:

• tool/build, plan/schema/policy versions, Office build, timestamps, and environment;
• input/output paths, sizes, SHA-256 hashes, and immutable-plan hash;
• every approved operation, affected source/range/part, reason, risk class, and before/after counts;
• every skipped candidate and blocker;
• hashes for every package part, with added/removed/changed classification and an explicit allowlist for expected changes;
• formula text and cached-result fingerprints, constants, names, meaningful styles and style definitions, calculation properties, sheet order/state, link inventory, and risk-feature inventory;
• Excel open/save/reopen timing, repair status, calculation/error-cell checks, and owned-process cleanup;
• verification outcome, unexplained deltas, warnings, and publication decision.

Raw cell-level evidence may be a compressed secondary artifact. The final workbook cannot contain its own final package hash without circularity; that hash belongs in the sidecar.

Architecture: reuse the existing runtime

Python control plane
  ├─ streaming ZIP/XML audit and dependency catalog
  ├─ hash-bound plan, staging/publication, reports, and bake-off orchestration
  └─ optional narrow direct-package experiment
             │
Existing C# supervisor/worker
  ├─ owned Excel lifecycle and Job Object containment
  ├─ native ClearFormats / BreakLink only after approved preflight
  ├─ SaveAs, close, and zero-orphan enforcement
  └─ fresh read-only reopen verification

Do not add XlsxWinOptimizer.exe now. The supervisor/worker already supplies the process boundary, lifecycle ownership, timeout handling, logging, and containment. Another executable would duplicate deployment, contracts, telemetry, and failure handling before a direct transformer has proved necessary.

If a C# OOXML implementation is later justified, start with an XlsxWinOptimizer.Core library. Promote it to a separate process only if untrusted-package isolation, independent deployment/versioning, or measured crash/memory isolation requires it.

Foundation gaps that precede production execution

The inspected baseline contains useful safety infrastructure, but optimizer execution must not inherit claims stronger than the real path:

• audit_manifest.py is not wired into cli.py run;
• dry-run simulates validation, reopen validation, and publication that the real worker does not yet perform;
• C# and Python manifest contracts differ around timeouts and unknown fields;
• a supervised job failure can be confused with supervision success by callers unless final result semantics drive the process exit/publication decision;
• the current corpus does not contain representative bloated banking workbooks;
• native Excel testing from a genuinely non-interactive agent/session remains open in xlsx-win v2: sandboxed-agent-to-desktop-user-session handoff is untested and undocumented #79.

Either fix these before execute mode or isolate the optimizer from the affected publication claims. Audit-only work does not need to wait.

Bake-off already completed

An uncommitted certification prototype was built against af1aab7. It compares four deliberately narrow arms against the same input and reviewed ranges:

Arm	Hypothesis isolated
Rezip unchanged parts	Compression alone explains the bloat.
Native Excel open + SaveAs	Excel's own rewrite/garbage collection is sufficient.
Native Excel ClearFormats + SaveAs	The supported Inquire-like native path is sufficient.
Streaming XML trim	Removing exact style-only cell records materially outperforms native/simple paths.

Prototype safety properties:

• .xlsx only;
• plan schema 0.1, bound to reviewed input SHA-256;
• exact top-left-to-bottom-right A1 ranges within Excel bounds;
• clear ranges cannot intersect the protected A1:keep_ref rectangle;
• XML and Excel arms share the same approved regions;
• a range containing any retained stored cell blocks the comparison;
• source hash and modification time are rechecked;
• external entities and ambiguous/duplicate ZIP members fail closed;
• formula text and cached results, constants, meaningful-cell styles plus styles.xml, calculation properties, workbook structure, risk inventory, and every package-part hash are compared;
• every completed arm is run twice for idempotence;
• native Excel part deltas are reported, not silently whitelisted;
• no automatic winner is selected.

Portable synthetic result

Fixture: 998,000 stored cells, of which 996,000 were approved style-only cells and zero were blocked.

Arm	Time	Output	Reduction	Semantic/package gate	Second pass
Rezip	0.567 s	1,897,256 B	0.0000%	pass	byte-identical
XML trim	7.625 s	16,028 B	99.1552%	pass	byte-identical
Excel SaveAs	not run	—	—	Windows required	—
Excel ClearFormats	not run	—	—	Windows required	—

The source remained byte- and mtime-identical. Ten portable adversarial tests passed: exact-part preservation for rezip, exact-range targeting, non-target style preservation, formula-cache detection, stale-plan rejection, protected-range rejection, source immutability, candidate removal, and semantic/second-pass idempotence.

This proves two narrow points:

1. Deflate recompression does not solve serialized formatted-cell bloat.
2. Surgical cell-record removal can remove that synthetic bloat.

It does not select the production path. The run was on Linux without .NET, Windows, or Excel; the C# path was not compiled; both native arms were skipped; only one synthetic bloat mechanism was tested; and worksheet dimensions were deliberately preserved because surviving cell extent alone is not a safe basis for rewriting them. The package dimension—and openpyxl's read-only view—therefore still reports 50,000 rows × 20 columns; native Excel behavior remains untested. Size reduction is not yet proof of corrected UsedRange behavior or faster Excel open/save.

Bake-off still required

1. Complete the Windows matrix

Run all four arms serially in an interactive, controlled Windows session with 64-bit Excel and no pre-existing Excel process. Record the exact Windows and Office build/channel, CPU, RAM, disk, available/system commit, and cold/warm run status.

For every arm:

• start from the same immutable source and hash-bound plan;
• disable macros, link updates, refresh-on-open, and outbound network access where practical;
• record Excel PID identity and enforce zero owned survivors;
• measure audit, open, mutation, save, close, and fresh-reopen time separately;
• detect any repair/recovery event rather than accepting a silently repaired save;
• rescan package and workbook semantics after native reopen;
• run a second pass and repeat enough times to characterize timing variance.

Add a validation sequence that opens the XML output in native Excel and SaveAs to determine whether Excel accepts, repairs, expands, or further compacts it. Treat XML+Excel as a validation/composition path, not a fifth winner unless it proves independently useful.

2. Build a decision-grade corpus

Synthetic fixtures must isolate:

• bottom and right-side style-only tails;
• entire-row/column formatting and retained row/column styles;
• duplicate and near-duplicate styles approaching Excel limits;
• merges, tables, filters, print areas, names, validation, conditional formatting, comments, drawings, charts, hyperlinks, hidden sheets, and page settings crossing proposed cutoffs;
• shared/array/spilled formulas and formula cached results;
• cell/name/chart/OLE/DDE/broken/dynamic external dependencies;
• pivots, slicers, Power Query, Data Model, embeddings, macros, signatures, protection, encryption, malformed ZIP/XML, duplicate entries, and compression bombs;
• timeout, crash, disk-full, stale-plan, existing-output, and publication failure.

Then run the frozen harness on 30–50 consented, representative transaction models. Store workbooks locally; collect only redacted aggregate metrics unless explicit retention is approved. Include clean controls, excess-formatting cases, link-heavy models, and files dominated by media/caches so the tool learns to say “no safe/material optimization.”

3. Release gates

Safety gates are absolute:

• zero source mutations in success, failure, timeout, and crash paths;
• zero Excel repair events;
• zero unexplained formula, cached-result, constant, name, relationship, feature, or meaningful-style deltas;
• zero new error cells;
• zero orphaned owned Excel processes;
• exact targeted external-link conversion and exact preservation of untargeted dependencies;
• every non-allowlisted package part byte-identical for direct-package operations;
• second execution is a semantic no-op;
• default-redacted artifacts leak none of the planted path/formula/value canaries.

Benefit gates prevent needless mutation:

• default no-op when forecast savings are both below 10% and below 5 MB, unless explicitly overridden;
• release “optimizer” positioning only if the targeted bloated cohort achieves at least ~20% median size or open/save-time improvement;
• custom OOXML compaction must materially outperform plain Excel SaveAs/native clearing, not merely tie it.

Speed is compared only after safety passes. Select the simplest passing path, not the smallest output at any cost.

Decision after the bake-off

Evidence	Action
Excel SaveAs captures nearly all benefit	Ship staged SaveAs + verification; do not build a transformer.
ClearFormats + SaveAs materially wins and passes	Add one narrow, hash-bound operation to the existing worker.
Streaming XML materially wins, all touched parts are provably allowlisted, and Excel validates it	Ship the smallest streaming transformer behind the audit/plan gate; keep native reopen mandatory.
No path clears safety/benefit gates	Ship the inspector and dependency catalog only; do not market an optimizer.

Only after this decision:

1. wire the winning thin execution slice into staging, publication, and the canonical change manifest;
2. add external-link freeze as a separate beta operation with keep default;
3. certify against the real corpus and pinned Office build;
4. document supported/blocked workbook profiles and recovery;
5. consider broader compaction only when remaining corpus evidence earns it.

Explicitly deferred

• global style-table deduplication/remapping;
• shared-string compaction and index rewrites;
• orphan part/relationship garbage collection;
• pivot, slicer, query, external-link, or Data Model cache removal;
• media recompression;
• confidential-data sanitation or hidden-content deletion as part of “optimization”;
• .xlsm mutation, .xlsb, .xls, signed, encrypted, or protected workbook mutation;
• automatic cleanup during ordinary refresh/recalc jobs;
• full pixel diffing of every sheet;
• policy DSL/plugin system, fleet service, telemetry backend, or new optimizer executable.

Audit may inventory all of these. Mutation requires a separate evidence-backed profile because each changes the risk model.

Deliverables and acceptance criteria

• Versioned audit/plan/change-manifest contract with default redaction.
• Deterministic streaming auditor with bounded memory and no Excel launch.
• Complete external-dependency catalog with coverage classification.
• Frozen portable + Windows bake-off harness and adversarial synthetic corpus.
• Results across 30–50 representative workbooks, including plain Excel controls.
• Documented technology decision using the table above.
• At most one initial compaction operation, source immutable and hash-bound.
• Optional per-source link freeze, keep default, no automatic refresh.
• Native Excel reopen/no-repair verification and second-pass no-op.
• Comprehensive, deterministic change catalog and privacy-canary tests.
• All safety gates pass; benefit threshold met or execution is deliberately not shipped.

The first releasable milestone is therefore Inspector + plan + completed bake-off, not “comprehensive optimizer.” Advanced cleanup must be earned by real banking-workbook evidence.