errno

openpyxl: the second save() of a workbook with images raises I/O operation on closed file

· tested on openpyxl 3.1.5, Pillow 12.1.0, Python 3.12

Symptom

A municipal grant application had to be filled into the authority’s own .xlsx form — the kind with a letterhead image in the first rows. Filling it took several passes: first the identification block, then the budget table. Same Workbook object, one save() after each pass. The second one died:

Traceback (most recent call last):
  File "fill.py", line 23, in <module>
    wb.save("out.xlsx")
  File ".../openpyxl/workbook/workbook.py", line 386, in save
    save_workbook(self, filename)
  File ".../openpyxl/writer/excel.py", line 294, in save_workbook
    writer.save()
  File ".../openpyxl/writer/excel.py", line 275, in save
    self.write_data()
  File ".../openpyxl/writer/excel.py", line 79, in write_data
    self._write_images()
  File ".../openpyxl/writer/excel.py", line 116, in _write_images
    self._archive.writestr(img.path[1:], img._data())
                                         ^^^^^^^^^^^
  File ".../openpyxl/drawing/image.py", line 48, in _data
    img = _import_image(self.ref)
  File ".../openpyxl/drawing/image.py", line 16, in _import_image
    img = PILImage.open(img)
  File ".../PIL/Image.py", line 3518, in open
    fp.seek(0)
ValueError: I/O operation on closed file.

The second, nastier symptom appears when you try to read the result back:

KeyError: "There is no item named '[Content_Types].xml' in the archive"

That is not a second bug. The write died in the middle of assembling the ZIP, so the file on disk is a truncated archive without a manifest. If you were saving over the original — as most fill-in-a-form scripts do — the original is now gone.

Why images are the trigger

When load_workbook() reads a file, it does not extract the embedded images to disk. Each openpyxl.drawing.image.Image keeps a ref pointing at a BytesIO backed by the source archive, and the pixels are pulled lazily, at write time, by img._data().

The first save() runs to completion and closes that archive. The buffer is now dead, but the Image object in your still-live Workbook happily holds the reference. The next save() calls _data(), Pillow does fp.seek(0) on a closed file, and you get the ValueError.

The distinction is easy to confirm — the bug depends on where the image came from:

# A: image referenced by path, workbook built in this process
wb = Workbook()
wb.active.add_image(XLImage("hdr.png"), "C1")
wb.save("a1.xlsx")
wb.save("a2.xlsx")          # OK — ref is a path, Pillow reopens it

# B: image loaded from an existing file
wb = load_workbook("src.xlsx")
type(wb.active._images[0].ref).__name__   # -> 'BytesIO'
wb.save("b1.xlsx")
wb.save("b2.xlsx")          # ValueError: I/O operation on closed file.

So the rule is narrow but unforgiving: a Workbook that came from load_workbook() and contains images can be saved exactly once. No image, no problem — which is why the same script works fine for months on plain data sheets and explodes the day someone adds a logo to the template.

The fix: one load, one save, atomic replace

Do not treat Workbook as a long-lived document you can keep saving. Treat each edit as a transaction: load, mutate, save to a temporary path, replace.

import os
from openpyxl import load_workbook

def edit(path, mutate):
    wb = load_workbook(path)
    mutate(wb)
    tmp = path + ".tmp"
    wb.save(tmp)
    os.replace(tmp, path)     # same filesystem -> atomic

edit("form.xlsx", lambda wb: wb.active.__setitem__("B7", "applicant"))
edit("form.xlsx", lambda wb: wb.active.__setitem__("B9", "2027"))

Two edits, two loads, both values present and the letterhead intact. The temporary file plus os.replace() is the part that matters beyond the crash: a failed save() leaves the garbage in .tmp and your input file untouched, so the destructive half of this bug disappears even if a future openpyxl version finds a new way to fail mid-write.

If reloading is genuinely too expensive, the alternative is to re-point every image at real bytes before the second write — but that means owning openpyxl internals (ws._images[i].ref) in your own code. Reloading a spreadsheet costs milliseconds. Take the reload.

Two more things that bit me in the same form-filling job, both quiet rather than loud:

The pattern behind all three: openpyxl models what the file format stores, not what a user thinks a spreadsheet is. Anything that lives beside the cell grid — embedded binaries, print setup, merges — needs explicit handling, and its absence is never reported as an error.

python openpyxl excel data-corruption