Fix XlsxWriter Overwriting an Existing Workbook

A workbook has a carefully maintained Inputs sheet. A script is meant to add a Report sheet next to it. It runs without an error — and the file now contains only Report. The same thing happens with df.to_excel("book.xlsx", sheet_name="Report"), because pandas uses XlsxWriter by default when it is installed. Nothing went wrong in the library: XlsxWriter is a writer. It builds a new file from scratch and, when you call close(), replaces whatever was at that path. It cannot read an existing workbook, so it cannot add to one. This page shows how to recognise the problem, the right tool for each kind of edit, and a guard that stops it happening by accident. It belongs to Troubleshooting Common Python Excel Errors.

Prerequisites

pip install pandas openpyxl xlsxwriter

Each sample file starts with two sheets, Inputs and Notes, standing in for a workbook someone maintains by hand.

Step 1 — See the overwrite happen

import xlsxwriter
import pandas as pd
from openpyxl import load_workbook

book = xlsxwriter.Workbook("model.xlsx")
book.add_worksheet("Report").write("A1", "Revenue forecast")
book.close()
print("XlsxWriter:", load_workbook("model.xlsx").sheetnames)

pd.DataFrame({"Month": ["Oct", "Nov"], "Revenue": [120, 125]}).to_excel(
    "model_pandas.xlsx", sheet_name="Report", index=False)
print("to_excel:  ", load_workbook("model_pandas.xlsx").sheetnames)

Both files now contain only Report. XlsxWriter never opened model.xlsx; it created a new workbook in memory and wrote it to that name. to_excel with a path does the same, whichever engine it uses — writing to a path means "create this file". There was no warning, because overwriting a file is a normal thing for a writer to do.

Step 2 — Why append mode refuses XlsxWriter

pandas has an append mode for exactly this job, and asking for it with XlsxWriter gives the clearest explanation of the problem:

try:
    pd.ExcelWriter("model_append.xlsx", engine="xlsxwriter", mode="a")
except ValueError as exc:
    print("ValueError:", exc)

Append mode is not supported with xlsxwriter! — appending means reading the existing workbook first, and XlsxWriter has no reader. openpyxl does, which is why append mode works only with engine="openpyxl".

Step 3 — Add or replace a sheet with openpyxl

report = pd.DataFrame({"Month": ["Oct", "Nov", "Dec"], "Revenue": [120, 125, 131]})

with pd.ExcelWriter("model_append.xlsx", engine="openpyxl", mode="a") as xw:
    report.to_excel(xw, sheet_name="Report", index=False)
print(load_workbook("model_append.xlsx").sheetnames)

try:
    with pd.ExcelWriter("model_append.xlsx", engine="openpyxl", mode="a") as xw:
        report.to_excel(xw, sheet_name="Report", index=False)
except ValueError as exc:
    print("second run:", exc)

with pd.ExcelWriter("model_append.xlsx", engine="openpyxl", mode="a", if_sheet_exists="replace") as xw:
    report.to_excel(xw, sheet_name="Report", index=False)
print(load_workbook("model_append.xlsx").sheetnames)

The first run adds Report beside Inputs and Notes. Running the job again fails with Sheet 'Report' already exists and if_sheet_exists is set to 'error' — the safe default. For a job that refreshes its own sheet every day, if_sheet_exists="replace" deletes and rewrites Report while leaving the other sheets alone. The other options are "new" (write Report1, Report2, …) and "overlay" (write the values into the existing sheet, keeping its formatting) — the subject of Append a DataFrame to an Existing Excel File with pandas.

Note the cost: openpyxl loads and re-saves the whole workbook, and anything it does not support — images, charts it cannot read, some form controls — is lost on save, as listed in What openpyxl Loses When Saving an Existing Workbook. Test on a copy of a real workbook before trusting the round trip.

Step 4 — When you need XlsxWriter's features anyway

People reach for XlsxWriter for its charts, sparklines, conditional formats and speed. To get those and keep the inputs, invert the flow: read what you need from the existing workbook, then write a complete new workbook — inputs included — with XlsxWriter, to a new name:

inputs = pd.read_excel("model_append.xlsx", sheet_name="Inputs")
with pd.ExcelWriter("model_report.xlsx", engine="xlsxwriter") as xw:
    inputs.to_excel(xw, sheet_name="Inputs", index=False)
    report.to_excel(xw, sheet_name="Report", index=False)
    chart = xw.book.add_chart({"type": "column"})
    chart.add_series({"categories": "=Report!$A$2:$A$4", "values": "=Report!$B$2:$B$4", "name": "Revenue"})
    xw.sheets["Report"].insert_chart("D2", chart)
print(load_workbook("model_report.xlsx").sheetnames)

The source workbook stays untouched, and the output is a fresh file built entirely by XlsxWriter. This is the cleanest arrangement for reports: inputs are maintained in one file by people, outputs are generated into another by the script, and nothing generated ever writes back over something maintained.

Step 5 — Guard against overwriting by accident

The overwrite in Step 1 was silent. A tiny wrapper makes it loud, and writes atomically so a failed run never leaves a half-written file in place:

import os
import tempfile
from pathlib import Path

def safe_output_path(path, overwrite=False):
    path = Path(path)
    if path.exists() and not overwrite:
        raise FileExistsError(f"{path} already exists; pass overwrite=True or choose a new name")
    return path

def write_new_workbook(path, frames, overwrite=False):
    target = safe_output_path(path, overwrite)
    fd, tmp = tempfile.mkstemp(suffix=".xlsx", dir=target.parent)
    os.close(fd)
    with pd.ExcelWriter(tmp, engine="xlsxwriter") as xw:
        for name, df in frames.items():
            df.to_excel(xw, sheet_name=name, index=False)
    os.replace(tmp, target)
    return target

try:
    write_new_workbook("model_append.xlsx", {"Report": report})
except FileExistsError as exc:
    print("refused:", exc)
print("written:", write_new_workbook("report_2026-10.xlsx", {"Report": report}))

The guard refuses to replace an existing file unless the caller says so explicitly, and the temporary-file-then-os.replace step means the target is either the old file or the complete new one, never something in between. Atomic writes are covered in depth in Write Excel Files Atomically in Python.

Step 6 — Keep a backup before any job touches a maintained file

If a workbook has already been overwritten, recovery depends on where it lived: OneDrive and SharePoint keep a version history (right-click the file › Version history), Windows File History and macOS Time Machine keep snapshots if they are enabled, and a file under version control can be checked out again. A local folder with none of these has no undo. For jobs that do modify maintained workbooks in place — the append mode from Step 3 — take a timestamped copy first and prune old copies:

import shutil
from datetime import datetime

def backup(path, folder="backups", keep=10):
    src = Path(path)
    dest_dir = src.parent / folder
    dest_dir.mkdir(exist_ok=True)
    stamp = datetime.now().strftime("%Y%m%d-%H%M%S-%f")
    copy = dest_dir / f"{src.stem}.{stamp}{src.suffix}"
    shutil.copy2(src, copy)
    for old in sorted(dest_dir.glob(f"{src.stem}.*{src.suffix}"))[:-keep]:
        old.unlink()
    return copy

for _ in range(3):
    saved = backup("model_append.xlsx", keep=2)
with pd.ExcelWriter("model_append.xlsx", engine="openpyxl", mode="a", if_sheet_exists="replace") as xw:
    report.to_excel(xw, sheet_name="Report", index=False)
print("latest backup:", saved.name, "| kept:", len(list(Path("backups").glob("model_append.*.xlsx"))))

shutil.copy2 keeps the original timestamps, the time stamp down to microseconds keeps copies from colliding when a job runs twice in a second, and sorting the names (which start with the same stem and a sortable time stamp) puts the oldest first, so slicing off all but the last keep copies prunes the rest. Ten copies of a workbook are usually a few megabytes — cheap insurance for a file someone spent months building.

Performance notes

Append mode is slower than writing a fresh file, because openpyxl must load and re-serialise every sheet, not just the one you add. For a workbook with a few small input sheets the difference is negligible; for one with a 200,000-row data sheet, each append run pays the cost of reading and rewriting those rows. If that becomes noticeable, it is a sign the data belongs in its own file, with the maintained workbook referring to the generated one rather than containing it.

Common pitfalls

• to_excel(path) on an existing workbook. It always creates a new file; use an ExcelWriter in append mode.
• Expecting a warning. Neither XlsxWriter nor pandas warns before replacing a file.
• Append mode with the wrong engine. mode="a" needs engine="openpyxl".
• Re-running an append job. The default if_sheet_exists="error" fails on the second run; choose "replace" deliberately.
• Assuming openpyxl's round trip is lossless. It drops images and unsupported objects; check a real file.
• The file is open in Excel. On Windows, writing to a workbook someone has open fails with a permission error rather than overwriting it — the one case where the operating system protects you; see Handle Permission Denied When Writing Excel in Python.

Conclusion

XlsxWriter only writes new files, so pointing it — or to_excel with a path — at an existing workbook replaces the workbook. To add or refresh a sheet while keeping the others, use pd.ExcelWriter(path, engine="openpyxl", mode="a") with an explicit if_sheet_exists. When you need XlsxWriter's features, read the inputs and generate a separate output file. And wrap output paths in a guard that refuses to overwrite and writes atomically, so a mistake is an error message rather than a lost workbook.

Frequently asked questions

Can XlsxWriter modify an existing Excel file? No. XlsxWriter can only create new files. Creating a Workbook with the name of an existing file replaces that file when the workbook is closed.

Why did pandas to_excel delete my other sheets? Writing to a path creates a new file. To add a sheet to an existing workbook, use pd.ExcelWriter(path, engine="openpyxl", mode="a") and call to_excel on the writer.

What does "Append mode is not supported with xlsxwriter!" mean? Append mode has to read the existing workbook first, and XlsxWriter has no reader. Use engine="openpyxl" for mode="a".

How do I replace one sheet but keep the rest of the workbook? Use pd.ExcelWriter(path, engine="openpyxl", mode="a", if_sheet_exists="replace"). The named sheet is deleted and rewritten, and the other sheets are kept.

Related

• Up one level: Troubleshooting Common Python Excel Errors — the errors behind common surprises.
• openpyxl vs xlsxwriter vs pandas ExcelWriter — which writer does what.
• Write Multiple DataFrames to One Excel File — several sheets in one pass.
• Fill an Excel Template with Python and openpyxl — editing a formatted workbook safely.