Make Excel Output Reproducible in Python

Run a report twice on the same inputs and you would expect the same file. Usually you do not get it: the two workbooks open identically in Excel but differ byte for byte, so a hash comparison says they changed, a backup tool stores both, and a "has the report changed since yesterday?" check always says yes. Some of the differences are metadata written by the library; others come from the script itself — files read in whatever order the operating system lists them, a set iterated in arbitrary order, the clock. This page, part of Testing and Packaging Excel Automation Scripts, finds each source and removes it.

Prerequisites

pip install pandas openpyxl xlsxwriter

Three small input files in inputs/, one per region, are combined into a report. The examples hash the output with hashlib to compare runs.

Step 1 — See the difference

Write the same DataFrame twice, a couple of seconds apart, and compare the files' hashes and their metadata:

import hashlib
import time
import zipfile
import pandas as pd

def digest(path):
    return hashlib.sha256(open(path, "rb").read()).hexdigest()[:12]

df = pd.DataFrame({"Region": ["North", "South"], "Revenue": [48200, 39100]})
df.to_excel("run1.xlsx", index=False)
time.sleep(1.1)
df.to_excel("run2.xlsx", index=False)
print(pd.get_option("io.excel.xlsx.writer"), "->", digest("run1.xlsx"), digest("run2.xlsx"))

core = zipfile.ZipFile("run1.xlsx").read("docProps/core.xml").decode()
print(core[core.index("<dcterms:created"):][:120])

Same data, different hashes. to_excel uses xlsxwriter when it is installed — the auto setting prefers it over openpyxl — and xlsxwriter records the current time as the workbook's creation date in docProps/core.xml. That one timestamp is the only difference.

Step 2 — Fix it for xlsxwriter

xlsxwriter already writes every zip entry with a fixed 1980 date, so the creation timestamp is all that varies. Set it explicitly — to the report's as-of date, which is meaningful and stable:

from datetime import datetime

as_of = datetime(2026, 9, 30)
for name in ["fixed1.xlsx", "fixed2.xlsx"]:
    with pd.ExcelWriter(name, engine="xlsxwriter") as writer:
        writer.book.set_properties({"created": as_of, "title": "Daily sales", "author": "Reporting"})
        df.to_excel(writer, index=False)
    time.sleep(1.1)
print("xlsxwriter:", digest("fixed1.xlsx"), digest("fixed2.xlsx"))

Identical bytes. Using the as-of date rather than an arbitrary constant keeps the metadata honest: the file says it describes 30 September, which is true however many times it is regenerated. Set Workbook Properties with Python covers the other properties.

Step 3 — Fix it for openpyxl

openpyxl is harder: it overwrites the modified time with the current time on every save, even if you set it, and stamps each zip entry with the current time too. Setting properties is not enough, so normalise the file after saving — rewrite the archive with a fixed date on every entry and a fixed modified value:

import re
from openpyxl import Workbook

def normalise_xlsx(path, when=datetime(2026, 9, 30)):
    stamp = when.strftime("%Y-%m-%dT%H:%M:%SZ")
    with zipfile.ZipFile(path) as src:
        entries = [(info, src.read(info.filename)) for info in src.infolist()]
    with zipfile.ZipFile(path, "w", zipfile.ZIP_DEFLATED) as dst:
        for info, data in entries:
            if info.filename == "docProps/core.xml":
                data = re.sub(rb"(<dcterms:(created|modified)[^>]*>)[^<]*", lambda m: m.group(1) + stamp.encode(), data)
            fixed = zipfile.ZipInfo(info.filename, date_time=when.timetuple()[:6])
            fixed.compress_type = zipfile.ZIP_DEFLATED
            fixed.external_attr = info.external_attr
            dst.writestr(fixed, data)

hashes = []
for name in ["op1.xlsx", "op2.xlsx"]:
    wb = Workbook()
    wb.active.append(["Region", "Revenue"])
    wb.active.append(["North", 48200])
    wb.save(name)
    before = digest(name)
    normalise_xlsx(name)
    hashes.append((before, digest(name)))
    time.sleep(1.1)
print("openpyxl before:", [b for b, _ in hashes], "after:", [a for _, a in hashes])

Before normalising, the two saves differ; afterwards they match. The rewrite preserves entry order and content, and the file opens normally. Call it as the last step after wb.save() — including when pandas writes through the openpyxl engine.

Step 4 — Remove the differences your own code makes

Fixed metadata only helps if the content is the same too. The usual culprits in a report script are the order files are read in, iteration over sets, random numbers and reading the clock. Compare an order-dependent version with a deterministic one:

from pathlib import Path

def combine(paths):
    return pd.concat([pd.read_excel(p) for p in paths], ignore_index=True)

listed = list(Path("inputs").glob("*.xlsx"))
print("as listed:", [p.name for p in listed])
print("sorted:   ", [p.name for p in sorted(listed)])

report = combine(sorted(Path("inputs").glob("*.xlsx"))).sort_values(["Region", "Customer"], ignore_index=True)
regions = sorted(set(report["Region"]))
print(report.to_string(index=False))
print("regions:", regions)

glob returns files in the order the file system stores them, which differs between machines and can change when files are rewritten; sort the list. Sort the combined data by a key that is unique per row, so the output order depends only on the data. Sort sets before iterating — string hashing is randomised per process, so iteration order over a set of strings changes from run to run. Setting PYTHONHASHSEED would also fix the order, but sorting states the intent in the code and works however the script is started. Seed any randomness (np.random.default_rng(42)), and pass the report date in rather than calling datetime.now(), as in Backfill Excel Reports for Past Dates.

Step 5 — Prove it in a test

The check that keeps it reproducible is a test that builds the report twice and compares hashes:

def build_report(out, as_of=datetime(2026, 9, 30)):
    data = combine(sorted(Path("inputs").glob("*.xlsx"))).sort_values(["Region", "Customer"], ignore_index=True)
    with pd.ExcelWriter(out, engine="xlsxwriter") as writer:
        writer.book.set_properties({"created": as_of})
        data.to_excel(writer, sheet_name="Sales", index=False)
    return out

first = digest(build_report("report_a.xlsx"))
time.sleep(1.1)
second = digest(build_report("report_b.xlsx"))
assert first == second, "report output is not reproducible"
print("reproducible:", first == second, first)

In pytest, put the same assertion in a test function that writes to tmp_path. When it fails, unzip both files and diff the XML parts — the part that differs names the cause. Compare Excel Output to a Golden File in Tests takes the next step: comparing against a stored known-good file.

Across machines, compare content rather than bytes

Byte-identical output is reliable on one machine with fixed library versions. Across machines it can still differ for reasons outside your code: a different zlib build may compress the same XML to different bytes, and a different openpyxl or xlsxwriter version may write attributes in another order. When a check has to work between a laptop and a server, hash the uncompressed parts instead — read each entry of the zip in name order and hash its contents — which ignores compression differences while still catching every real change. Pinning library versions in the project's requirements removes the second cause.

What reproducibility buys

Once identical inputs give identical files, a hash becomes a reliable "did anything change?" signal. A scheduled job can skip re-sending a report whose hash matches yesterday's — Skip a Report When the Input File Has Not Changed applies the same idea to inputs. A rerun of a closed month can be proved to match what was sent. And upgrading pandas or openpyxl becomes testable: rebuild last month's reports and see which, if any, change.

Common pitfalls

• Setting openpyxl's modified property and expecting it to stick. It is overwritten on save; normalise afterwards.
• Forgetting zip entry dates. Fixing core.xml alone leaves the archive different.
• Unsorted glob results. Sort file lists before reading.
• Iterating over sets of strings. Their order changes between processes; sort first.
• datetime.now() in content. Titles and "generated at" cells change every run; use the as-of date, or accept and exclude that one cell from comparisons.
• Library upgrades. A new version can change the XML it writes; pin versions for reports that must be identical.

Performance notes

Normalising rewrites the zip once, which costs about as much as writing it; negligible for report-sized files. Hashing is fast even for large workbooks.

Conclusion

Byte-identical output needs fixed metadata and deterministic content. With xlsxwriter, set the created date with set_properties; with openpyxl, rewrite the saved archive with fixed entry dates and a fixed modified value. In your own code, sort file lists, rows and sets, seed randomness and pass the report date in instead of reading the clock. Guard it with a test that builds the report twice and compares hashes.

Frequently asked questions

Why do two runs of my script produce different xlsx files? An xlsx file records when it was created and last modified, and some libraries also stamp each entry in the zip archive with the current time. Even with identical data, those timestamps make the files differ byte for byte.

How do I make xlsxwriter output identical every run? Set the created date explicitly by calling set_properties on the workbook with a fixed created datetime. xlsxwriter already writes fixed zip entry dates, so with a fixed created date the same content produces the same bytes.

How do I make openpyxl output reproducible? openpyxl sets the modified time to the current time on every save and stamps zip entries with it. After saving, rewrite the archive with a fixed date for every entry and a fixed modified value in docProps/core.xml.

Why does it matter if the bytes are identical? Identical inputs giving identical files lets you check a rerun changed nothing with a hash, store only reports that actually changed, avoid re-sending unchanged reports and trust that a regression test compares content rather than timestamps.

Related

• Up one level: Testing and Packaging Excel Automation Scripts — reliable report code.
• Compare Excel Output to a Golden File in Tests — regression tests on content.
• Set Workbook Properties with Python — the metadata involved.
• Backfill Excel Reports for Past Dates — rerunning with confidence.