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

Build a Product Catalog with Images in Excel

Turn a product list and a folder of photos into a catalogue workbook: shrink each photo to a thumbnail before inserting it, keep its aspect ratio, centre it in a fixed-size cell, handle products without a photo, group by category, and anchor images so they move and hide with their rows when the sheet is sorted or filtered.
Charts & imagesReport layouts & templates

Sales teams, buyers and shop staff often want the product list with pictures: a price list for a trade fair, a range review, an order form for a franchise shop. The data is usually in a spreadsheet and the photos in a folder, named after the SKU. Putting them together by hand is slow, and the obvious script — insert every photo at full size — produces a 200 MB workbook with pictures that spill across rows and stay put when someone sorts by price. Doing it well takes four decisions: shrink the photos first, fit each into a fixed cell box without distorting it, handle products with no photo, and anchor the pictures so they travel with their rows. This page is part of Inserting Images and Logos into Excel.

Prerequisites

Bash
pip install pandas pillow xlsxwriter openpyxl

products.xlsx lists seven products with SKU, name, category and price. The photos/ folder holds a JPEG per SKU at camera-like sizes — 2400 × 1600 or 1600 × 2000 pixels, some landscape and some portrait — and one product, the desk lamp, has no photo yet.

Python
from pathlib import Path
import pandas as pd

products = pd.read_excel("products.xlsx")
photos = {p.stem: p for p in Path("photos").glob("*.jpg")}
print(len(products), "products,", len(photos), "photos")
print("without photo:", sorted(set(products["SKU"]) - set(photos)))
print(f"photo folder: {sum(p.stat().st_size for p in photos.values()) / 1e6:.1f} MB")
From a list and a photo folder to a catalogue The product list is matched to photos by SKU, each photo is shrunk to a thumbnail, fitted into a fixed cell box without distortion, and inserted so it moves and sizes with its cell; products without a photo get a placeholder text. Match by SKU list ↔ photos/ Shrink Pillow thumbnail Fit the box keep aspect ratio Insert move and size with cells Missing photo placeholder text

Step 1 — Shrink every photo before inserting it

Excel stores each picture at its original resolution, however small it is displayed. A 2400-pixel photo shown as a 120-pixel thumbnail still weighs its full megabytes in the file. Resize with Pillow first, to about twice the display size so thumbnails stay sharp on high-density screens:

Python
import io
from PIL import Image

BOX_W, BOX_H = 120, 90            # thumbnail box in pixels, as displayed

def thumbnail(path, scale=2):
    with Image.open(path) as img:
        img.thumbnail((BOX_W * scale, BOX_H * scale))
        buf = io.BytesIO()
        img.convert("RGB").save(buf, format="JPEG", quality=85, optimize=True)
        return buf, img.size

buf, size = thumbnail(photos["TB-200"])
with Image.open(photos["TB-200"]) as original:
    print("original", original.size, "-> thumbnail", size, f"{len(buf.getvalue()) / 1024:.1f} KB")

Image.thumbnail resizes in place to fit inside the given box and keeps the aspect ratio, so a landscape photo comes out wider than tall and a portrait photo taller than wide. Saving as JPEG at quality 85 with optimize=True gives small files with no visible loss at thumbnail size; use PNG only for logos and line drawings with sharp edges.

Step 2 — Fit each thumbnail into a fixed cell box

Every product row gets the same box: a fixed row height and column width. Scale the thumbnail to fit the box and offset it so it sits centred:

Python
def placement(size):
    w, h = size
    scale = min(BOX_W / w, BOX_H / h)
    shown_w, shown_h = w * scale, h * scale
    return {"x_scale": scale, "y_scale": scale,
            "x_offset": int((BOX_W - shown_w) / 2) + 4, "y_offset": int((BOX_H - shown_h) / 2) + 4}

for sku in ("TB-200", "CH-110"):
    print(sku, placement(thumbnail(photos[sku])[1]))

Using the same scale for x_scale and y_scale is what keeps products from being stretched — a chair squashed to fit a landscape box looks like a different chair. The offsets centre the picture horizontally and vertically, with a four-pixel margin, so landscape and portrait photos line up down the column.

Stretching to fill versus fitting the box Two panels. Stretching a photo to the cell size distorts portrait and landscape products differently. Fitting with one scale factor and centring keeps every product's proportions and lines them up. Stretch to cell different x and y scale portrait items squashed products look wrong Fit and centre one scale for both axes centred with offsets proportions kept

Step 3 — Build the catalogue

Write the product data with xlsxwriter, group rows by category with a heading row for each, and insert the thumbnails with object_position=1, which tells Excel to move and resize each picture with its cell:

Python
import xlsxwriter

wb = xlsxwriter.Workbook("catalogue.xlsx")
ws = wb.add_worksheet("Catalogue")
bold = wb.add_format({"bold": True, "font_color": "#FFFFFF", "bg_color": "#4338CA", "valign": "vcenter"})
cat_fmt = wb.add_format({"bold": True, "font_size": 12, "bottom": 1})
cell = wb.add_format({"valign": "vcenter", "text_wrap": True})
price = wb.add_format({"valign": "vcenter", "num_format": "£#,##0.00"})
missing = wb.add_format({"valign": "vcenter", "align": "center", "font_color": "#9CA3AF", "italic": True})

ws.set_column("A:A", 18)
ws.set_column("B:B", 12)
ws.set_column("C:C", 26)
ws.set_column("D:D", 11)
ws.write_row(0, 0, ["Photo", "SKU", "Name", "Price"], bold)
row = 1
for category, group in products.groupby("Category", sort=True):
    ws.write(row, 0, category, cat_fmt)
    row += 1
    for p in group.itertuples(index=False):
        ws.set_row(row, BOX_H * 0.75 + 6)                 # points = pixels × 0.75
        if p.SKU in photos:
            buf, size = thumbnail(photos[p.SKU])
            ws.insert_image(row, 0, f"{p.SKU}.jpg", {"image_data": buf, "object_position": 1, **placement(size)})
        else:
            ws.write(row, 0, "no photo", missing)
        ws.write(row, 1, p.SKU, cell)
        ws.write(row, 2, p.Name, cell)
        ws.write(row, 3, p.Price, price)
        row += 1
ws.autofilter(0, 0, row - 1, 3)
ws.freeze_panes(1, 0)
wb.close()
print("rows written:", row, f"| file size {Path('catalogue.xlsx').stat().st_size / 1024:.0f} KB")

image_data takes the in-memory buffer, and the file name argument only labels the picture inside the workbook. Row heights are in points, and 0.75 points make a pixel at Excel's 96 dpi, so a 90-pixel box needs about 68 points plus a little margin. The desk lamp gets a grey italic "no photo" in its cell rather than an empty gap, which makes missing photos obvious to whoever reviews the catalogue before it goes out. The whole workbook comes to a small fraction of the photo folder's size. For comparison, insert the original photos at the same displayed size:

Python
wb = xlsxwriter.Workbook("catalogue_full_size.xlsx")
ws = wb.add_worksheet()
for r, (sku, path) in enumerate(sorted(photos.items()), start=1):
    with Image.open(path) as im:
        scale = min(BOX_W / im.width, BOX_H / im.height)
    ws.set_row(r, BOX_H * 0.75 + 6)
    ws.insert_image(r, 0, str(path), {"x_scale": scale, "y_scale": scale, "object_position": 1})
wb.close()
print(f"full-size photos: {Path('catalogue_full_size.xlsx').stat().st_size / 1e6:.1f} MB")

It looks the same on screen and is several hundred times bigger — 8.9 MB against about 20 KB here — because every original is stored in full, down to pixels nobody will ever see at thumbnail size. With hundreds of products, that is the difference between a catalogue that can be emailed and one that cannot.

Step 4 — Why object_position matters

By default a picture in Excel is placed over the cells: sorting the rows or filtering some out leaves the picture where it was, now next to the wrong product. With object_position=1 the picture is attached to its cell and moves with it when the rows are sorted — and it is hidden along with its row when a filter hides that row. For a catalogue that people will sort by price or filter by category, that is the difference between a usable sheet and a misleading one:

Python
import re
import zipfile

xml = zipfile.ZipFile("catalogue.xlsx").read("xl/drawings/drawing1.xml").decode()
print("anchor tags:", sorted(set(re.findall(r"<xdr:\w*Anchor[^>]*>", xml))), "| pictures:", xml.count("<xdr:pic>"))

Every picture is stored as a plain twoCellAnchor — pinned to a start cell and an end cell — with no editAs attribute, which the file format defines as "move and size with cells". (xlsxwriter's object_position=2 writes editAs="oneCell" and 3 writes editAs="absolute".) openpyxl's add_image writes pictures with a oneCellAnchor instead, which moves with the top-left cell but does not resize with it; for catalogues that users will sort and filter, xlsxwriter's object_position is the more reliable choice. Embed Images Inside Cells with xlsxwriter covers the newer in-cell pictures of Excel 365, which behave like cell values entirely.

The catalogue sheet A worksheet with a photo column, SKU, name and price, grouped under category headings. Each photo sits centred in its cell; the desk lamp shows a no photo placeholder. catalogue.xlsx A B C D 1 Photo SKU Name Price 2 Lighting 3 [photo] LP-300 Brass floor lamp £175.00 4 no photo LP-310 Desk lamp £39.90 5 Seating 6 [photo] CH-100 Oak dining chair £89.00

Common pitfalls

  • Inserting full-size photos. Excel keeps the original resolution; shrink first or the file balloons, as the 8.9 MB comparison showed.
  • Different x and y scales. Products look distorted; use one scale factor and centre with offsets.
  • Pictures that stay put when sorting. Use object_position=1 so pictures move and size with their cells.
  • Row heights in the wrong unit. Heights are points; multiply pixels by 0.75.
  • Photos matched by position. Match by SKU, and report products without photos and photos without products.

Conclusion

Match photos to products by SKU, shrink each to about twice its display size with Pillow's thumbnail, and insert it with one scale factor and centring offsets so every product keeps its proportions in a fixed cell box. Write the catalogue with xlsxwriter using object_position=1, so pictures move and hide with their rows when the sheet is sorted or filtered, mark missing photos visibly, and check the file size against the photo folder.

Frequently asked questions

How do I insert pictures into Excel rows with Python? Use xlsxwriter's insert_image(row, col, name, {"image_data": buffer, ...}) with the picture in a BytesIO buffer, or openpyxl's add_image. Set the row height to fit the picture and scale it with x_scale and y_scale.

Why is my Excel file with images so large? Excel stores every picture at its original resolution, whatever size it is displayed at. Resize the images with Pillow before inserting them, to around twice the displayed size.

How do I make images move with the rows when sorting? In xlsxwriter pass "object_position": 1 in the insert_image options, which anchors the picture to its cell so it moves and resizes with it and is hidden when its row is filtered out.

How do I keep images from being stretched? Compute one scale factor as the smaller of box width ÷ image width and box height ÷ image height, use it for both x_scale and y_scale, and centre the image with x_offset and y_offset.

More on charts & images

More on charts & images →