Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Insert an Image from Memory into Excel with Python

Put images into a workbook straight from bytes — a logo downloaded over HTTP, a base64 string from an API, a photo stored in a database, a picture resized with Pillow — without writing temporary files, using openpyxl or XlsxWriter, and avoid the closed-buffer and format errors that only appear at save time.
Charts & imagesopenpyxl

Report images rarely start as files on disk. A company logo comes from a URL, a product photo from a database column, a signature or chart from an API as a base64 string, a thumbnail from Pillow after resizing. Writing each one to a temporary file just to read it back into Excel is slow, leaves files behind and breaks in read-only containers. Both openpyxl and XlsxWriter accept image bytes directly. This guide, part of Inserting Images and Logos into Excel, covers the common sources, the resizing and format conversions worth doing in memory, and the error that only shows up when the workbook is saved. For charts rendered with matplotlib, see Embed a Matplotlib Chart in an Excel Report.

Image bytes from anywhere, straight into the workbook Bytes downloaded over HTTP, base64 text from an API response, a BLOB column from a database and an image resized with Pillow are each wrapped in a BytesIO and inserted into the workbook without any temporary file. Workbook Image(BytesIO(data)) HTTP download response.content API field base64 text Database BLOB bytes column Pillow resized in memory

Prerequisites

Bash
pip install openpyxl pillow xlsxwriter

openpyxl needs Pillow to read image dimensions and formats. The examples create a small PNG in memory to stand in for a real logo:

Python
import base64
import io
import sqlite3

from openpyxl import Workbook, load_workbook
from openpyxl.drawing.image import Image
from PIL import Image as PILImage, ImageDraw

def make_logo(text="ACME", size=(480, 160), colour=(67, 56, 202)):
    picture = PILImage.new("RGB", size, colour)
    ImageDraw.Draw(picture).text((24, 64), text, fill="white")
    buffer = io.BytesIO()
    picture.save(buffer, format="PNG")
    return buffer.getvalue()

logo_bytes = make_logo()
print(len(logo_bytes) > 0)          # True

Step 1 — Bytes into openpyxl

Wrap the bytes in io.BytesIO and pass the buffer where a filename would go:

Python
wb = Workbook()
ws = wb.active
logo = Image(io.BytesIO(logo_bytes))
print(logo.format, logo.width, logo.height)       # png 480 160
logo.width, logo.height = 240, 80                  # display at half size
ws.add_image(logo, "A1")
wb.save("with_logo.xlsx")

openpyxl reads the format and pixel size through Pillow. Setting width and height changes how large the picture is displayed, in pixels, without touching the stored bytes. Positioning and anchoring are covered in Resize and Position Images in Excel with openpyxl.

Step 2 — Keep the buffer open until the save

The one surprise with in-memory images: openpyxl reads the image data when the workbook is saved, not when the Image is created. A buffer closed in between makes the save fail, far from the line that caused it:

Python
wb = Workbook()
ws = wb.active
with io.BytesIO(logo_bytes) as buffer:
    ws.add_image(Image(buffer), "A1")
try:
    wb.save("broken.xlsx")
except ValueError as exc:
    print(exc)                                     # I/O operation on closed file.

Create the BytesIO without a with block, or keep the block open around the save. A buffer made from bytes holds no file handle, so leaving it open costs nothing; it is freed with the workbook.

When openpyxl actually reads the image bytes The script creates a BytesIO, builds an openpyxl Image from it and adds it to the sheet; openpyxl only reads the image size at this point. The pixel data is read later, during wb.save. If the with block has already closed the buffer, the save raises ValueError I/O operation on closed file. Script BytesIO openpyxl 1. create from bytes 2. Image(buffer), add_image 3. read size and format 4. with block closes it 5. wb.save() 6. read data → ValueError

Step 3 — Common sources of image bytes

The pattern is the same whatever the source; only the way you obtain the bytes changes. A download returns them in response.content:

Python
import requests

response = requests.get("https://example.com/logo.png", timeout=10)
response.raise_for_status()
if not response.headers.get("Content-Type", "").startswith("image/"):
    raise ValueError("not an image")
web_logo = Image(io.BytesIO(response.content))

An API field or a data URL carries base64 text, and a database column carries raw bytes:

Python
api_field = "data:image/png;base64," + base64.b64encode(logo_bytes).decode()
from_api = base64.b64decode(api_field.split(",", 1)[1])

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE products (sku TEXT, photo BLOB)")
db.execute("INSERT INTO products VALUES (?, ?)", ("A-1", logo_bytes))
(from_db,) = db.execute("SELECT photo FROM products WHERE sku = 'A-1'").fetchone()
print(from_api == logo_bytes, from_db == logo_bytes)      # True True

Check the content type or try opening the bytes with Pillow before inserting: an HTML error page saved as logo.png produces a confusing error at save time instead of a clear one at download time.

Step 4 — Resize and convert with Pillow in memory

Display size does not shrink the file: a 4000-pixel photo shown at 200 pixels is still stored at full size in every workbook. Resize the pixels, and convert formats Excel may not display, before inserting:

Python
def prepare(data, max_px=400, fmt="PNG"):
    picture = PILImage.open(io.BytesIO(data))
    picture.thumbnail((max_px, max_px))
    if fmt == "JPEG" and picture.mode in ("RGBA", "P"):
        picture = picture.convert("RGB")
    out = io.BytesIO()
    picture.save(out, format=fmt, optimize=True)
    return out.getvalue()

big_photo = make_logo("PHOTO", size=(3000, 2000))
small = prepare(big_photo, max_px=400)
print(len(big_photo) > len(small), PILImage.open(io.BytesIO(small)).size)   # True (400, 267)

thumbnail keeps the aspect ratio. JPEG has no transparency, so RGBA and palette images are converted to RGB first. Formats such as WebP are accepted by openpyxl but are not displayed by older versions of Excel and by many viewers; converting everything to PNG or JPEG avoids the question. SVG cannot be read by Pillow at all — openpyxl raises UnidentifiedImageError — so render SVGs to PNG before inserting.

Changing display size versus resizing the pixels Setting width and height on the openpyxl Image changes only how large the picture appears; the full-size bytes are still stored in the workbook. Resizing with Pillow before inserting stores a smaller image, so the workbook shrinks, at the cost of detail if someone enlarges the picture later. img.width / img.height one line, no extra library full-size bytes stored file grows with every copy fine for small logos Pillow thumbnail first smaller workbook converts formats on the way detail lost if enlarged right for photos

Step 5 — The same with XlsxWriter

XlsxWriter's insert_image takes a filename for its bookkeeping plus an image_data buffer with the actual bytes:

Python
import xlsxwriter

book = xlsxwriter.Workbook("with_logo_xw.xlsx")
sheet = book.add_worksheet()
sheet.insert_image("A1", "logo.png", {"image_data": io.BytesIO(logo_bytes),
                                       "x_scale": 0.5, "y_scale": 0.5})
book.close()
print("written")

The filename is not opened; it names the image inside the file and tells XlsxWriter the format, so give it the right extension even though no such file exists. x_scale and y_scale shrink the display, like width and height in openpyxl. XlsxWriter also reads the buffer when the workbook closes, so the same keep-it-open rule applies. Placing images inside cells instead of floating above them is covered in Embed Images Inside Cells with xlsxwriter.

Step 6 — One image, many sheets

A logo on every sheet of a 20-sheet report does not need 20 downloads. Keep the bytes once and create a new Image object per placement — an Image can only be anchored once, but the bytes can be reused:

Python
wb = Workbook()
for name in ["North", "South", "East"]:
    sheet = wb.create_sheet(name)
    sheet.add_image(Image(io.BytesIO(logo_bytes)), "A1")
del wb["Sheet"]
wb.save("every_sheet.xlsx")
media = [n for n in __import__("zipfile").ZipFile("every_sheet.xlsx").namelist() if "media" in n]
print(len(media))                                  # 3

openpyxl writes a media file per placement, so the workbook grows with each copy — another reason to resize before inserting. For a report with dozens of sheets, a small, pre-sized logo keeps the file size reasonable; a full-resolution logo copied onto every sheet can add megabytes to a workbook whose data is only a few hundred kilobytes.

Step 7 — Check the images that were written

A report that should carry a logo on every sheet is easy to check after saving: openpyxl loads the images back as Image objects on each worksheet's _images list, and the file itself holds one media entry per placement:

Python
check = load_workbook("every_sheet.xlsx")
for sheet in check.worksheets:
    print(sheet.title, len(sheet._images))
# North 1
# South 1
# East 1

Add this to the report's tests, together with a check on the file size, and a missing logo or an accidentally full-resolution photo is caught before the workbook is sent. Getting images out of existing workbooks — including ones you did not create — is covered in Extract Images from an Excel File with Python.

Common pitfalls

  • A closed buffer. The with io.BytesIO(...) pattern fails at save time. Keep buffers open until the workbook is written.
  • Pillow missing. openpyxl imports Pillow only when an image is used; install it in the environment that runs the report, not just on your laptop.
  • Transparency in JPEG. JPEG has no alpha channel; saving an RGBA image as JPEG without converting fails in Pillow. Keep logos with transparency as PNG.
  • Huge images. Resize before inserting; display size does not reduce file size.

Conclusion

Both openpyxl and XlsxWriter accept image bytes wrapped in io.BytesIO, so logos, photos and API images go straight from memory into the workbook. Get the bytes from response.content, base64-decoded fields or database BLOBs, validate them, resize and convert with Pillow, and keep every buffer open until the save — openpyxl and XlsxWriter read the data only when the file is written. Create one Image per placement and reuse the bytes.

Frequently asked questions

Can openpyxl insert an image without saving it to a file first? Yes. Wrap the bytes in io.BytesIO and pass that to openpyxl.drawing.image.Image instead of a path. Pillow must be installed, because openpyxl uses it to read the image's size and format.

Why do I get "I/O operation on closed file" when saving? openpyxl reads the image data when the workbook is saved, not when the Image is created. If the BytesIO was closed — for example by a with block — the save fails. Keep the buffer open until after wb.save().

Can I insert an SVG image? Not with openpyxl or XlsxWriter's standard image insertion; Pillow cannot read SVG, so openpyxl raises UnidentifiedImageError. Render the SVG to PNG first, for example with a library such as cairosvg, and insert the PNG.

How do I resize an image before inserting it? Either set img.width and img.height in pixels on the openpyxl Image, which scales the display only, or resize the pixels with Pillow in memory first, which also makes the workbook smaller.

More on charts & images

More on charts & images →