Use the delivered workbook, not an empty example
The source expense workbook is a real native Excel deliverable from the public Office workflow. Its raw export has 483 rows; Power Query produces 480 unique documents. The workbook has 69 formulas, a query-loaded table, two chart parts and one PivotTable part. A separate monthly-close workbook has 1,814 formulas and one chart part, with no query-table or connection parts.
Those are deliberately different inputs. If we tested only the formula-based close, the result would say little about query preservation. If we tested only the expense workbook, it would be easy to overgeneralize the loss of query features to every XLSX. The saved reports include the exact SHA-256 hashes, so you can check which files were used.
For each input we ran load_workbook with its default options, made no cell edits and saved a new filename. We then compared package inventories and counted formulas with nonempty saved value elements. The script refuses to overwrite the source or an existing output.
What changed in these two files
The expense workbook lost eight package parts. Among them were xl/connections.xml, xl/queryTables/queryTable1.xml, the query table’s relationship file and three custom XML parts. Its two chart parts and PivotTable part remained. There were no warnings captured during this run.
The monthly-close workbook lost only xl/calcChain.xml and xl/sharedStrings.xml from its package inventory. Those same two parts also disappeared from the expense workbook. Removing a shared-string table can be a serialization change rather than text loss, because a writer may store strings inline. A removed calculation chain alone is not proof that formulas are wrong. Do not count all missing ZIP parts as equivalent defects.
The concrete preservation concern is the missing connection/query plumbing in a workbook that originally had it. We did not prove a native refresh outcome for the rewritten file, and the article does not claim every workbook or every openpyxl version behaves this way.
| Observed feature | Expense workbook before → after | Monthly close before → after |
|---|---|---|
| File bytes | 85,189 → 61,489 | 140,480 → 126,070 |
| Formula elements | 69 → 69 | 1,814 → 1,814 |
| Formulas with saved values | 69 → 0 | 1,814 → 0 |
| Connection XML parts | 1 → 0 | 0 → 0 |
| Query-table XML parts | 1 → 0 | 0 → 0 |
| Chart XML parts | 2 → 2 | 1 → 1 |
| PivotTable XML parts | 1 → 1 | 0 → 0 |
Formulas survived; their saved values did not
The formula counts were unchanged in both files. The nonempty saved formula-value counts fell to zero. That distinction matters when the next system reads cached values rather than running a calculation engine. A formula can still be present while a data-only read has nothing to return.
The openpyxl loading documentation distinguishes formula text from the value saved by Excel. Our package inspection measures the stored representation directly. It does not evaluate whether the formulas would calculate correctly after Excel opens the result.
Do not make a workbook “pass” by replacing formulas with constants. If the deliverable needs live formulas, retain them and recalculate/save in the required engine. If it needs Power Query, inspect and test that feature too. Read our workbook verification guide for the full acceptance contract.
The local operation was quick, but that was not the acceptance test
The expense load took 0.342980 seconds and save took 0.114022 seconds in this run. The monthly-close load took 0.159135 seconds and save took 0.189809 seconds. These are single local observations, not benchmark distributions, native Excel timings or complete job costs.
A fast write establishes that the library produced a file. It does not establish that the file still supports next month’s refresh. Decide what must survive first; then measure the time and cost of the workflow that actually passes those checks.
What happened when the rewritten files reached Excel
We followed the local package inspection with a prepared native reopen test on Panes. Excel 16.0 build 20527 opened both originals. The rewritten expense workbook failed the default Workbooks.Open call with “Unable to get the Open property of the Workbooks class.” The script did not request a repair mode or silently replace the failed file. This single failure does not diagnose every removed part or establish that all openpyxl outputs fail.
The rewritten monthly-close workbook opened, recalculated and saved. It retained one native chart and 1,814 formula cells, with zero detected formula errors and the expected $1,988,001.06 revenue value. The original monthly workbook returned the same checked value. That demonstrates a successful native recalculation for this fixture, despite the empty saved formula results in the intermediate library output.
The four-case native sequence reached its saved JSON in 4.005 seconds, excluding application cleanup; the API reported 7.035 seconds and controller wall time was 7.113 seconds. It was a prepared script, with no independent agent planning or attributable per-task cost. Read the full native result and reopen script. An initial COM SpecialCells range count overcounted expense formulas. We replaced it with a Formula-array cell count and reran; saved ZIP inspection independently confirms 69 expense formulas. The raw result records that correction.
| Input | Outcome | Checked business value |
|---|---|---|
| Expense original | Opened; 1 query, 1 pivot, 2 charts | $686,500 October expense |
| Expense library round trip | Default open failed | Not accepted |
| Monthly original | Opened; 1 chart; 0 formula errors | $1,988,001.06 revenue |
| Monthly library round trip | Opened; 1 chart; 0 formula errors | $1,988,001.06 revenue |
Run the experiment on a copy of your own workbook
Download the script and the original expense workbook or monthly-close workbook. Use a scratch directory and pin the package version. Your workbook may exercise different features, so inspect its before/after report rather than expecting our counts.
Next open a disposable output in desktop Excel, inspect any repair messages, verify connections and refresh behavior, recalculate, save and compare the result to its source. Our separate native follow-up above records both the successful recalculation and the failed default open. Never use the rewritten experimental file as a replacement for the verified download.
File libraries remain useful for many tasks, including reading cells and generating straightforward spreadsheets. Panes is useful when acceptance depends on the real Windows app. Keep the simple operations simple, and put the application-specific steps in the signed-in desktop.
python -m venv .venv
.venv/bin/pip install openpyxl==3.1.5
.venv/bin/python workbook_roundtrip.py \
"October close.xlsx" "scratch-roundtrip.xlsx" \
--report report.json