Write Excel Files Atomically in Python

A report job saves report.xlsx every hour into a shared folder that a dashboard, a colleague's macro and a sync client all read. One day the job runs out of memory halfway through wb.save(). What is left on disk is not last hour's report and not this hour's — it is a truncated zip file that Excel calls corrupt, and the dashboard fails until someone notices. Even without a crash, a reader that opens the file during the second or two the save takes can get half a workbook. The fix is an old Unix habit: write the new file under a temporary name next to the target, make sure it is complete and valid, then swap it into place with one atomic rename. Readers see either the old file or the new one, never something in between. This page builds that as a context manager and tests that it holds up. It belongs to Error Handling and Logging in Excel Automation.

Prerequisites

pip install openpyxl pandas

Everything here uses the standard library (os, tempfile, zipfile) plus the Excel library you already use. The behaviour of os.replace described below holds on Linux, macOS and Windows for files on the same local volume; network shares and sync folders are discussed at the end.

Step 1 — See the problem

Simulate a save that fails partway through. openpyxl writes the zip archive piece by piece, so an exception in the middle leaves a broken file behind:

import zipfile
from openpyxl import Workbook, load_workbook

def make_workbook(label, rows=2000):
    wb = Workbook()
    ws = wb.active
    ws.append(["Run", "Row", "Value"])
    for i in range(rows):
        ws.append([label, i, i * 1.5])
    return wb

make_workbook("good run").save("report.xlsx")              # yesterday's good report

class Boom(Exception):
    pass

wb = make_workbook("new run")
original = zipfile.ZipFile.writestr                        # simulate dying mid-save
calls = {"n": 0}
def flaky(self, *a, **k):
    calls["n"] += 1
    if calls["n"] == 4:
        raise Boom("out of memory")
    return original(self, *a, **k)
zipfile.ZipFile.writestr = flaky
try:
    wb.save("report.xlsx")
except Boom as e:
    print("save failed:", e)
finally:
    zipfile.ZipFile.writestr = original

try:
    load_workbook("report.xlsx")
    print("report.xlsx still opens")
except Exception as e:
    print("report.xlsx is now broken:", type(e).__name__)

Saving straight over the target truncated it the moment the save began, so when the save died, yesterday's good report was already gone and nothing valid replaced it. The same window exists on every successful save too — it is just short enough that it rarely bites, until a reader polls at the wrong moment.

Step 2 — The atomic write context manager

The pattern has four parts: create a temporary file in the same directory as the target, write the workbook to it, flush it to disk, and os.replace it over the target. If anything fails, delete the temporary file and leave the target alone:

import os
import tempfile
from contextlib import contextmanager
from pathlib import Path

@contextmanager
def atomic_path(target, validate=None):
    """Yield a temporary path next to `target`; replace `target` with it on success."""
    target = Path(target)
    fd, tmp = tempfile.mkstemp(prefix=f".{target.stem}-", suffix=target.suffix, dir=target.parent)
    os.close(fd)
    tmp = Path(tmp)
    try:
        yield tmp
        with open(tmp, "rb+") as fh:                     # make sure the bytes are on disk
            fh.flush()
            os.fsync(fh.fileno())
        if validate:
            validate(tmp)
        if target.exists():
            os.chmod(tmp, target.stat().st_mode & 0o777)  # keep the old file's permissions
        os.replace(tmp, target)
    except BaseException:
        tmp.unlink(missing_ok=True)
        raise

with atomic_path("report.xlsx") as tmp:
    make_workbook("good run").save(tmp)
print("report.xlsx opens:", load_workbook("report.xlsx").active["A2"].value)
print("leftover temp files:", list(Path(".").glob(".report-*")))

tempfile.mkstemp creates a uniquely named file, so two jobs writing the same report at once cannot clobber each other's temporary file. It must be in the same directory: os.replace is only atomic within one file system, and the system temp directory is often on a different one — renaming across file systems either fails or turns into a non-atomic copy. The leading dot and the .xlsx suffix matter too: the dot hides the file from casual directory listings and from many folder watchers, and the suffix lets Excel libraries that check extensions write to it. fsync asks the operating system to put the bytes on disk before the rename, so a power cut cannot leave a renamed but empty file. mkstemp creates the file readable only by its owner, so the old file's permissions are copied across — otherwise colleagues who could read yesterday's report suddenly cannot.

Step 3 — Validate before the swap

Since nothing is visible until the rename, there is a free moment to check the new file. A validator that opens the workbook and checks it has data catches a broken or empty report before it replaces a good one:

def has_rows(path, sheet=None, min_rows=2):
    wb = load_workbook(path, read_only=True)
    ws = wb[sheet] if sheet else wb.active
    n = ws.max_row
    wb.close()
    if n < min_rows:
        raise ValueError(f"{path.name} has only {n} row(s)")

try:
    with atomic_path("report.xlsx", validate=has_rows) as tmp:
        Workbook().save(tmp)                          # an empty report, e.g. the query returned nothing
except ValueError as e:
    print("refused:", e)
print("report.xlsx still has the good run:", load_workbook("report.xlsx").active["A2"].value)

The empty workbook is rejected, its temporary file is deleted, and the previous report stays in place. Put whatever checks your report needs here — expected sheets, a total that is not zero, a date in the title matching today; Validate an Excel Report Before Sending It has a fuller set. Keeping the last good report is usually better than replacing it with a wrong one, as long as the failure is logged and alerts someone.

Step 4 — Use it with pandas and XlsxWriter

The context manager yields a path, so it works with anything that writes to a path:

import pandas as pd

df = pd.DataFrame({"Region": ["North", "South"], "Revenue": [18400, 12100]})
with atomic_path("summary.xlsx", validate=has_rows) as tmp:
    with pd.ExcelWriter(tmp, engine="xlsxwriter") as writer:
        df.to_excel(writer, sheet_name="Summary", index=False)
print(pd.read_excel("summary.xlsx").to_dict("records"))

The inner with closes the ExcelWriter, which is when XlsxWriter actually writes the file; only after that does the outer block validate and rename. Keep that nesting order — validating inside the writer block would check a file that has not been written yet. The same works for CSV exports, and for any other output a downstream process reads.

Step 5 — Prove readers never see a partial file

A test can hammer the file with a reader while a writer replaces it repeatedly, and count how often the reader got something that is not a valid workbook:

import threading

stop = threading.Event()
seen = {"ok": 0, "broken": 0}

def reader():
    while not stop.is_set():
        try:
            with zipfile.ZipFile("report.xlsx") as z:
                z.testzip()
            seen["ok"] += 1
        except (zipfile.BadZipFile, FileNotFoundError, EOFError, OSError):
            seen["broken"] += 1

t = threading.Thread(target=reader)
t.start()
for i in range(15):
    with atomic_path("report.xlsx") as tmp:
        make_workbook(f"run {i}", rows=3000).save(tmp)
stop.set()
t.join()
print(f"reads: {seen['ok']} valid, {seen['broken']} broken")

Every read the reader made — and it makes many while the writer saves fifteen reports — found a complete, valid zip archive. Swap the context manager for a plain save("report.xlsx") in the loop and the picture reverses: in the same test, the reader got 9 valid reads and more than 3,000 broken ones, because for most of each save the file on disk is incomplete. A test like this is worth keeping for any job whose output is polled by something else.

Windows, network shares and sync folders

• Windows and open files. If someone has report.xlsx open in Excel, Excel holds a lock and os.replace fails with PermissionError — but the old file is untouched and the temporary file is cleaned up. Retry after a pause, or write to a dated name and update a pointer; see Handle "Permission Denied" When Writing Excel in Python.
• Network shares (SMB, NFS). A rename within one share is atomic on the server for most configurations, which is what readers on other machines see. Always create the temporary file on the share itself, never locally then copy.
• OneDrive, Dropbox and similar folders. The sync client may upload the temporary file before the rename. The hidden, dotted name keeps most clients from syncing it, but where that matters, write outside the synced folder on the same drive and replace into it.
• Many readers on a schedule. If consumers cache a file handle, an atomic swap still works: they keep reading the old file until they reopen, then see the new one.

Common pitfalls

• Temp file in /tmp or %TEMP%. Different file system, so the rename is not atomic or fails.
• Validating inside the writer block. The file is written when the writer closes; validate after.
• Losing permissions. mkstemp files are private; copy the target's mode.
• Leaving temp files after errors. Delete them in the exception path.
• Retrying forever on Windows locks. Retry a few times, then fail and alert.

Conclusion

Saving directly over a workbook exposes readers to a truncated file during every save and destroys the previous version when a save fails. Write to a uniquely named temporary file in the same folder, fsync it, validate it, copy the old file's permissions, and os.replace it over the target — wrapped in a context manager that deletes the temporary file on any error. Readers then always see either the complete old report or the complete new one, and a broken run leaves the last good report in place.

Frequently asked questions

How do I save an Excel file atomically in Python? Create a temporary file in the same folder with tempfile.mkstemp, save the workbook to it, fsync it, then call os.replace to rename it over the target. If anything fails, delete the temporary file.

Why must the temporary file be in the same folder? os.replace is only atomic within one file system. A temporary file elsewhere may be on another volume, where the rename fails or becomes a copy that readers can catch half-done.

Does os.replace work on Windows? Yes, it replaces an existing file atomically on the same volume. If the target is open in Excel the call fails with PermissionError and leaves the old file intact.

Can I check the workbook before it replaces the old one? Yes. Open the temporary file and check sheets, row counts or totals before os.replace. If a check fails, delete the temporary file and keep the previous report.

Related

• Up one level: Error Handling and Logging in Excel Automation — making report jobs robust.
• Prevent Overlapping Runs of an Excel Report Job — one writer at a time.
• Name, Version and Archive Generated Excel Reports — keeping earlier versions too.
• Write Excel Files to a Network Share from Python — the share case in detail.