Guide

Automating Reporting WorkflowsDeep dive

Write Excel Files to a Network Share from Python

Q: How do I use a UNC path from Python?

Pass it directly as a raw string, for example r"\\\\server\\reports\\monthly". pathlib handles UNC paths on Windows. On Linux, mount the share with cifs and use the mount point as an ordinary path.

Publish reports to an SMB or mounted network share safely — atomic write-then-rename, handling a file open in Excel, UNC paths, permissions, and retention of old reports.

Not every organisation publishes to the cloud. A network share is still where a great many recurring reports land — it is already there, everyone has a drive letter for it, and no security review is required. The pitfalls are different from an API upload but no less real: a reader can open the file in Excel and lock it, a slow copy leaves a truncated workbook visible for the whole transfer, and a script that works from your terminal fails under the scheduler because the drive letter does not exist there. This guide covers writing to a share safely. It is the on-premise path from Publishing Excel Reports to Cloud Storage.

Prerequisites

Bash

pip install pandas xlsxwriter

Access to the share, addressed the right way for the platform:

Python

from pathlib import Path

# Windows: a UNC path, not a mapped drive letter.
share = Path(r"\\fileserver\reports\monthly")

# Linux/macOS: mount the share first, then treat it as an ordinary path.
# sudo mount -t cifs //fileserver/reports /mnt/reports \
#     -o credentials=/etc/samba/report-creds,uid=1000
share = Path("/mnt/reports/monthly")

Use the UNC path, not a drive letter. Mapped drives are per-session: R:\ exists in your interactive session and does not exist for the account a scheduled task runs under. This single detail accounts for most "works for me, fails under the scheduler" reports — the fuller treatment of scheduler environments is in running a Python Excel script on Windows Task Scheduler.

Step 1 — Write atomically

Write to a temporary name in the destination folder, then rename. os.replace is a single filesystem operation on both Windows and POSIX, so a reader sees the old file or the new one and nothing in between:

Python

import os
import tempfile
from pathlib import Path

def publish_atomic(local_path, share_dir, name):
    """Copy a report onto a share without ever exposing a partial file."""
    share_dir = Path(share_dir)
    share_dir.mkdir(parents=True, exist_ok=True)
    target = share_dir / name

    # The temporary file MUST live in the destination folder, or the rename
    # crosses filesystems and degrades into a non-atomic copy.
    fd, tmp_name = tempfile.mkstemp(dir=share_dir, prefix=".", suffix=".tmp")
    tmp = Path(tmp_name)
    try:
        with os.fdopen(fd, "wb") as out, open(local_path, "rb") as src:
            while chunk := src.read(1 << 20):
                out.write(chunk)
            out.flush()
            os.fsync(out.fileno())      # force the bytes out before renaming

        os.replace(tmp, target)         # atomic within one filesystem
    except BaseException:
        tmp.unlink(missing_ok=True)     # never leave litter behind
        raise
    return target

Three details earn their place. The dir=share_dir argument keeps the temporary file on the same volume — put it in the system temp directory instead and os.replace falls back to a copy, losing the guarantee entirely. The os.fsync forces buffered data to the server before the rename, so a crash between write and rename cannot promote a partial file. And the except clause removes the temporary file on any failure, including a keyboard interrupt, so a folder does not silently fill with .tmpXXXX fragments.

Writing the workbook straight to the share is the same shape:

Python

import io
import pandas as pd

def publish_dataframe(df, share_dir, name, sheet_name="Summary"):
    buffer = io.BytesIO()
    with pd.ExcelWriter(buffer, engine="xlsxwriter") as writer:
        df.to_excel(writer, sheet_name=sheet_name, index=False)

    share_dir = Path(share_dir)
    fd, tmp_name = tempfile.mkstemp(dir=share_dir, prefix=".", suffix=".tmp")
    tmp = Path(tmp_name)
    try:
        with os.fdopen(fd, "wb") as out:
            out.write(buffer.getvalue())
            out.flush()
            os.fsync(out.fileno())
        os.replace(tmp, share_dir / name)
    except BaseException:
        tmp.unlink(missing_ok=True)
        raise
    return share_dir / name

Step 2 — Handle a file somebody has open

On Windows, Excel holds an exclusive lock while a workbook is open, and even the rename fails. Detect it and report clearly rather than letting the job die with a bare PermissionError:

Python

from pathlib import Path

def is_locked(target):
    """True if the file exists and cannot be opened exclusively."""
    target = Path(target)
    if not target.exists():
        return False
    try:
        with target.open("ab"):
            return False
    except (PermissionError, OSError):
        return True

def lock_holder(target):
    """Excel leaves a hidden owner file beside an open workbook."""
    target = Path(target)
    owner = target.with_name("~$" + target.name)
    if not owner.exists():
        return None
    try:
        # The user name sits near the start, UTF-16 encoded.
        raw = owner.read_bytes()[:120]
        return raw.decode("utf-16", errors="ignore").strip("\x00 ").strip()
    except OSError:
        return None

def publish_checked(local_path, share_dir, name):
    target = Path(share_dir) / name
    if is_locked(target):
        who = lock_holder(target)
        raise RuntimeError(
            f"{target} is open in Excel"
            + (f" by {who}" if who else "")
            + " — the report was not published."
        )
    return publish_atomic(local_path, share_dir, name)

LibreOffice uses a different convention, a .~lock.report.xlsx# file, so ignore both patterns when listing a folder or you will treat lock files as reports.

A brief retry is worth adding, because the lock is often momentary — somebody glancing at last month's figures. Give it a few minutes, then give up loudly:

Python

import time

def publish_waiting(local_path, share_dir, name, attempts=6, gap=30):
    target = Path(share_dir) / name
    for attempt in range(1, attempts + 1):
        if not is_locked(target):
            return publish_atomic(local_path, share_dir, name)
        if attempt < attempts:
            print(f"{target.name} is locked; retrying in {gap}s "
                  f"({attempt}/{attempts})")
            time.sleep(gap)
    raise RuntimeError(
        f"{target} stayed locked for {attempts * gap}s"
        + (f"; held by {lock_holder(target)}" if lock_holder(target) else "")
    )

Publishing to a dated name sidesteps the problem entirely, since nobody can have a file open that does not exist yet. Reserve the stable -latest name for the copy that might be locked, and let the dated write always succeed.

A share that is not mounted looks like an empty directory on Linux, so a naive script writes the report into the local mount point and reports success. Check before building:

Python

from pathlib import Path

def check_share(share_dir, marker=".reports-share"):
    """Verify the share is mounted and writable before doing any work."""
    share = Path(share_dir)

    if not share.is_dir():
        raise RuntimeError(f"{share} is not reachable — is the share mounted?")

    # A marker file placed on the share itself proves it is the real target
    # rather than an empty local mount point.
    if not (share / marker).exists():
        raise RuntimeError(
            f"{share} is missing {marker} — this looks like an unmounted "
            "mount point, not the share."
        )

    probe = share / f".write-probe-{os.getpid()}"
    try:
        probe.write_bytes(b"ok")
    except OSError as exc:
        raise RuntimeError(f"{share} is not writable: {exc}") from None
    finally:
        probe.unlink(missing_ok=True)

    return True

The marker file is the part worth copying. Checking is_dir() alone passes on an empty local directory where the share should be, which is precisely the silent-success case.

Dated reports accumulate. Prune on a schedule, keeping recent files and one per month for the archive:

Python

import re
from datetime import date, timedelta
from pathlib import Path

DATED = re.compile(r"-(\d{4})-(\d{2})(?:-(\d{2}))?\.xlsx$")

def prune(share_dir, keep_days=90, dry_run=True):
    """Delete dated reports older than keep_days, keeping the first of each month."""
    cutoff = date.today() - timedelta(days=keep_days)
    removed = []

    for path in sorted(Path(share_dir).glob("*.xlsx")):
        if path.name.startswith(("~$", ".~lock")):
            continue                      # lock files, not reports
        match = DATED.search(path.name)
        if not match:
            continue                      # not a dated report; leave it alone

        year, month, day = match.groups()
        stamp = date(int(year), int(month), int(day or 1))
        if stamp >= cutoff or day in (None, "01"):
            continue                      # recent, or the monthly keeper

        removed.append(path)
        if not dry_run:
            path.unlink()

    return removed

for path in prune("/mnt/reports/monthly", dry_run=True):
    print("would remove", path.name)

Default the function to dry_run=True. A deletion pass over a share is exactly the code you want to read the output of before trusting, and the day somebody widens the glob pattern is the day that default saves the archive.

Common pitfalls and fixes

Symptom	Cause	Fix
`PermissionError` on an existing file	Open in Excel	Detect the lock, retry briefly, report who holds it.
Works interactively, fails under the scheduler	Mapped drive letters are per-session	Use the full UNC path.
Readers see a truncated workbook	Written directly to the target	Write to a temporary name and `os.replace`.
Rename is slow and not atomic	Temporary file on another filesystem	Create it in the destination folder.
Report written but nobody can find it	Share not mounted; wrote to the mount point	Check for a marker file before writing.
`~$report.xlsx` treated as a report	Excel lock file matched the glob	Skip `~$` and `.~lock` prefixes.
Folder full of `.tmp` files	Failures left staging files behind	Clean up in an `except`/`finally`.
`OSError: [Errno 28]`	Share is full	Prune old reports; alert on free space.

Performance and scale notes

Network filesystems make metadata operations expensive. A local stat is microseconds; over SMB it is a round trip, so code that looks harmless locally becomes slow on a share.

The main rule is minimise round trips. Listing a directory once and working from the result beats calling exists() per file:

Python

from pathlib import Path

# Slow on a share: one round trip per name.
present = [n for n in expected_names if (share / n).exists()]

# Fast: one listing, then in-memory lookups.
on_share = {p.name for p in share.iterdir()}
present = [n for n in expected_names if n in on_share]

Build locally, copy once. Writing a workbook directly to a share means every buffered flush crosses the network. Generate to a local temporary file, then transfer the finished bytes in one streamed copy — which is what publish_atomic above does:

Python

import tempfile
from pathlib import Path

with tempfile.TemporaryDirectory() as work:
    local = Path(work) / "report.xlsx"
    df.to_excel(local, index=False, engine="xlsxwriter")   # local disk speed
    publish_atomic(local, "/mnt/reports/monthly", "regional-2026-08.xlsx")

Parallelise across files, modestly. A fan-out of one report per region benefits from a small thread pool, since the copies are I/O-bound — but a share is a shared resource, and eight concurrent writers usually make everyone slower rather than the job faster. Four is a reasonable ceiling.

Finally, keep an eye on free space as a first-class concern. A full share fails every publish at once, and the failure arrives as a confusing OSError rather than a clear message. Checking before writing turns it into an actionable alert, which is the same fail-fast reasoning applied throughout error handling and logging in Excel automation:

Python

import shutil

usage = shutil.disk_usage("/mnt/reports")
free_pct = usage.free / usage.total * 100
if free_pct < 5:
    raise RuntimeError(f"share is {free_pct:.1f}% free — prune before publishing")

Conclusion

A network share is a perfectly good report destination as long as you treat it like the shared, remote, occasionally locked resource it is. Write to a temporary name inside the destination folder and os.replace it into place, so nobody ever opens a half-copied workbook. Detect the Excel lock and say who holds it instead of dying on a bare PermissionError. Address the share by UNC path so the scheduler sees the same thing you do, prove it is really mounted with a marker file before doing any work, and prune dated reports on a schedule with a dry run by default.

Frequently asked questions

Why does my write fail with permission denied when the file exists? Somebody has it open in Excel, which holds a lock on Windows. Write to a temporary name in the same folder and rename it into place — and even that fails while the file is open, so detect the lock and report it rather than silently skipping the publish.

Does os.replace really overwrite atomically? Within one filesystem, yes — on both Windows and POSIX it is a single operation, so readers see either the old file or the new one. Across filesystems it degrades to a copy, so keep the temporary file in the destination folder.

How do I use a UNC path from Python? Pass it directly as a raw string, for example r"\\server\reports\monthly". pathlib handles UNC paths on Windows. On Linux, mount the share with cifs and use the mount point as an ordinary path.

Why does the script work interactively but fail under Task Scheduler? The scheduled task runs as a different account, and mapped drive letters are per-session. Use the full UNC path rather than a drive letter, and run the task as an account with rights to the share.

What is the .~lock or ~$ file next to my report? A lock file created by Excel or LibreOffice while the document is open. Ignore it when listing reports, and treat its presence as a signal that the file is in use.

Up to the parent: Publishing Excel Reports to Cloud Storage — the naming and atomicity patterns applied here.
Handle Permission Denied When Writing Excel in Python — the locked-file error in depth.
Run a Python Excel Script on Windows Task Scheduler — why drive letters vanish under the scheduler.
Upload an Excel Report to Amazon S3 with boto3 — the same job against object storage.
Log Python Excel Script Output to a File — recording what the publish step did.