Fix "ValueError: I/O operation on closed file" with ExcelWriter

ValueError: I/O operation on closed file is Python's way of saying that something tried to read from or write to a file object that had already been closed. With pandas' ExcelWriter it nearly always comes from the order of with blocks: the workbook is only written to its destination when the writer closes, so if the destination — a file handle or an in-memory buffer — is closed first, the final write fails. The traceback points at close() or the end of a with block, not at the line that caused the problem, which makes it confusing. This page reproduces the three common causes, explains what actually happened to the file, shows the fix for each, and covers a fourth case that is worse because it raises no error at all. It belongs to Troubleshooting Common Python Excel Errors.

Prerequisites

pip install pandas openpyxl

The examples use the default openpyxl engine; XlsxWriter behaves the same way for these cases.

Why the timing matters

ExcelWriter collects sheets in memory while to_excel is called, and only builds the .xlsx package — a zip archive — and writes it out when it is closed, either explicitly with close() or at the end of a with pd.ExcelWriter(...) block. Until then, the destination has received nothing. Anything that closes the destination before that moment leaves the writer with nowhere to write.

Reading the traceback

The traceback for this error is long and mostly inside pandas, openpyxl and the zipfile module, and its last lines usually mention write or close in one of those libraries. The useful frame is the first one in your code: it will be a close() call or the last line of a with pd.ExcelWriter(...) block, or a getvalue(), read() or read_excel() on a buffer. That line is where the closed object was used, but the cause is earlier — wherever that object was closed. Look upwards for the end of a with open(...) or with io.BytesIO() block, an explicit .close(), or a function that returned from inside a with. The three causes below cover nearly every case seen in practice.

Cause 1 — The file handle closes before the writer

import pandas as pd

df = pd.DataFrame({"Region": ["North", "South"], "Revenue": [1200, 860]})

try:
    with open("report.xlsx", "wb") as f:
        xw = pd.ExcelWriter(f)
        df.to_excel(xw, index=False)
    xw.close()                                         # f is already closed here
except ValueError as e:
    print("ValueError:", e)

import os
print("report.xlsx is", os.path.getsize("report.xlsx"), "bytes")

The writer is given an open file handle, the sheet is added, and then the with open(...) block ends and closes the handle. When xw.close() runs, it builds the workbook and tries to write it to f, which is closed: ValueError. The file exists, because open(..., "wb") created it, but it is empty — zero bytes — and Excel will say it is corrupt. The fix is to nest the blocks so the writer closes first, inside the handle's block, or simpler still, to pass the path and let pandas open and close the file itself. Passing a handle is only worth it when something else owns the file — a temporary file, a network stream, an archive member — and in that case the writer's block must sit entirely inside the owner's:

with open("report.xlsx", "wb") as f:
    with pd.ExcelWriter(f) as xw:                      # writer closes first, then the handle
        df.to_excel(xw, index=False)
print("nested:", os.path.getsize("report.xlsx"), "bytes")

with pd.ExcelWriter("report.xlsx") as xw:              # or just pass the path
    df.to_excel(xw, index=False)
print("path:", os.path.getsize("report.xlsx"), "bytes")

Cause 2 — A buffer returned from inside its own with block

import io

def build_report_broken(df):
    with io.BytesIO() as buf:
        with pd.ExcelWriter(buf) as xw:
            df.to_excel(xw, index=False)
        return buf                                     # the with block closes buf on the way out

try:
    build_report_broken(df).getvalue()
except ValueError as e:
    print("ValueError:", e)

def build_report(df):
    buf = io.BytesIO()
    with pd.ExcelWriter(buf) as xw:
        df.to_excel(xw, index=False)
    return buf.getvalue()                              # return the bytes, not the buffer

data = build_report(df)
print(len(data), "bytes, starts with", data[:2])

Building a workbook in memory is common in web apps and email jobs, and with io.BytesIO() as buf: looks tidy. But a with block closes its object when it exits — including when it exits through return — so the caller receives a closed buffer, and the first getvalue() or read_excel fails. The fix is not to manage the buffer with with at all: a BytesIO holds only memory and needs no closing, and returning buf.getvalue() hands the caller plain bytes, which cannot be closed. The bytes start with PK, the signature of a zip archive, which is a quick sanity check that a real workbook was produced. A test can assert exactly that — data[:2] == b"PK" and a non-trivial length — without opening the workbook at all. Sending those bytes from a web endpoint is covered in Build an Excel Workbook in Memory with BytesIO.

Cause 3 — Reading a buffer after it was closed

The same error appears on the reading side when a buffer is passed to pd.read_excel after something closed it — for example an upload handler that reads the request's file object once, closes it, and then hands it to a second function. A closed buffer cannot be reopened. Read the bytes once with .read() or .getvalue(), keep the bytes, and wrap them in a fresh io.BytesIO(data) for each reader that needs a file-like object. This also avoids the subtler cousin of the error, where the buffer is open but its position is at the end after a first read, so the second reader sees an empty file; buf.seek(0) fixes that one.

The silent case — writing after the with block

with pd.ExcelWriter("two_sheets.xlsx") as xw:
    df.to_excel(xw, sheet_name="Summary", index=False)
df.to_excel(xw, sheet_name="Detail", index=False)     # after the block: no error at all

with pd.ExcelFile("two_sheets.xlsx") as book:
    print(book.sheet_names)

This one raises nothing, which makes it the most dangerous. The second to_excel call is outside the with block, after the writer has already written and closed the file. pandas accepts the call — the sheet is added to the writer's in-memory book — but nothing will ever write that book again, so the file on disk contains only Summary. There is no error and no warning; the Detail sheet is simply missing. It typically happens after a refactor moves a line out of an indented block, or when code that used to call writer.close() explicitly at the end is converted to a with block and one call is left behind below it. Running a linter does not catch it, because the code is valid; only checking the output does. The protection is to keep every to_excel for a writer inside its with block, and to check the sheet names of important outputs after writing, as in Validate an Excel Report Before Sending It.

A note on writer.save()

Older code calls writer.save() instead of writer.close(). save() was deprecated in pandas 1.5 and removed in 2.0; on current versions it raises AttributeError: ... object has no attribute 'save'. Replacing it with close() — or, better, a with block — is the fix, and moving to a with block also removes the chance of the ordering mistakes above.

Common pitfalls

• Closing the destination first. The writer writes on close; close it before the file handle.
• with BytesIO() plus return buf. Return buf.getvalue() instead.
• Reusing a consumed buffer. Keep the bytes and make a new BytesIO for each reader.
• Writing after the with block. No error, but the sheet is lost.
• writer.save(). Removed in pandas 2; use close() or a with block.

Conclusion

"I/O operation on closed file" from ExcelWriter means the destination was closed before the writer wrote to it, or a buffer was used after closing. Let the writer close first — nest it inside any file handle, or pass a path — return buf.getvalue() rather than a buffer from inside a with block, and keep bytes rather than buffers when several readers need the data. Keep every to_excel call inside the writer's with block, because writing afterwards fails silently.

Frequently asked questions

Why does ExcelWriter raise "I/O operation on closed file"? The writer writes the workbook when it closes; if the file handle or buffer it writes to was closed first, that final write fails.

Why is my Excel file 0 bytes after this error? The file was created when the handle was opened, but the workbook was never written to it because the handle closed before the writer did.

How do I return an Excel file from a function in memory? Write to a BytesIO without a with block and return buf.getvalue(), which gives plain bytes.

What replaced ExcelWriter.save()?close(), or simply using with pd.ExcelWriter(...) as writer: so the writer closes automatically.

Related

• Up one level: Troubleshooting Common Python Excel Errors — common errors and fixes.
• Write Multiple DataFrames to One Excel File — using one writer for many sheets.
• Fix "Excel Found Unreadable Content" After Writing with Python — files that open broken.
• Write Excel Files Atomically in Python — never leave an empty file behind.