Batch Insert Images into Excel Cells with Python and openpyxl

Introduction

The author received a question in a Python automation group about inserting images into Excel based on the image name stored in a cell. The goal is to match each image file with its corresponding cell in column B and insert it automatically.

Solution Code

The following Python script uses openpyxl to load the workbook, collect image files from a folder, resize them, and insert each image into the appropriate row in column B.

from openpyxl.drawing.image import Image
import os
from openpyxl import load_workbook

pic_path = r'C:\Users\Administrator\Desktop\图片练习'
wb = load_workbook(r'C:\Users\Administrator\Desktop\练习2.xlsx')
ws = wb.active
pic_list = [os.path.join(pic_path, i) for i in os.listdir(pic_path) if i.endswith('.jpg')]

new_size = (90, 90)
num = 2
for i, pic in enumerate(pic_list, start=1):
    image = Image(pic)
    image.width, image.height = new_size
    ws.row_dimensions[num].height = 80
    ws.column_dimensions['b'].width = 12
    ws.add_image(image, 'b' + str(num))
    num += 2
wb.save(r'C:\Users\Administrator\Desktop\练习2.xlsx')

The script resizes each image to 90x90 pixels, adjusts row height and column width, and inserts the image into column B of the corresponding row.

Implementation Details

The idea was inspired by a community member who suggested that a simple name‑matching approach could be done directly in Excel or VBA. The Python solution fixes the image size to the cell dimensions, making batch insertion straightforward.

Additional screenshots illustrate the result of the batch insertion and the code layout.

Conclusion

The article provides a complete, ready‑to‑use script for anyone needing to batch‑insert images into Excel based on cell names. It also shares practical advice for asking technical questions, such as providing small demo data, error screenshots, and keeping code snippets concise.

Readers are encouraged to adapt the script for their own automation tasks and to share feedback with the community.