Python-Excel report with images

Recently I had to create an excel report consisting of many images. Putting them in one by one was not an option as it takes too much time and extremely boring task. After 10 min of searching online to check what python libraries are available for this task, I created a small script to do it automatically.

I prepared two images in color and the same images in grayscale. Let's imagine that we need to put colored images in the A column and grayscale images next to them in the B column. For fun, we will add the image name in the C column. Images and code can be found here on GitHub.

Let's start by creating a new project in IDE you are working in, I'm using PyCharm. Install some libraries which will be necessary for the code to work:

# pip install Pillow
from openpyxl import Workbook # pip install openpyxl
from openpyxl.drawing.image import Image
from openpyxl.utils import get_column_letter
import glob
import os
from natsort import natsorted # pip install natsort

Openpyxl is a Python library that is used to read from an Excel file or write to an Excel file, we will use it to write. Let's create a workbook, and active worksheet and resize some columns and rows so our images fit:

# create a workbook and grab active worksheet
workbook = Workbook()
worksheet = workbook.active

# resize cells
for row in range(1, 3):
    for col in range(1, 3):
        worksheet.row_dimensions[row].height = 170
        col_letter = get_column_letter(col)
        worksheet.column_dimensions[col_letter].width = 50

Next, we will create a function that creates an array of images in folders:

def add_images_to_array(folder):
    images = []
    for filename in natsorted(glob.glob(f"{folder}/*.jpg")):
        images.append(filename)

    return images

Now we can give a function folder path and in return, we will get an array with all image names. As I know my images will be “jpg“, I hard coded that in code, but if you have a different format you will need to change that. Let's create two lists one for colored images, which in my case is in folder “city_img” and the second list with grayscale images which are located in the folder “grayscale_city_img”:

images_city = add_images_to_array("city_img")
grayscale_images_city = add_images_to_array("grayscale_city_img")

Next, we will create two functions. One will be for image adding to excel and the second will be for image name adding to excel:

def add_images_in_excels(images, worksheet_, anchor):
    for index, image in enumerate(images):
        worksheet_.add_image(Image(image), anchor=anchor + str(index + 1))


def add_image_names_in_excel(folder):
    # titles list
    titles = []
    for title in natsorted(glob.glob(f"{folder}/*.jpg")):
        titles.append(os.path.basename(title))

    # insert titles
    for index, title in enumerate(titles):
        worksheet.cell(row=index + 1, column=3, value=title)

For adding images to excel we need to give function image lists, worksheets, and anchors. Anchors in this case will be columns A top left corner and columns B top left corner. For adding names to excel we need to give folder names and the function will take names and add them in column 3, which is C. Now we can call the function:

# add images to excel
add_images_in_excels(images_city, worksheet, "A")
add_images_in_excels(grayscale_images_city, worksheet, "B")

# add columns with image names
add_image_names_in_excel("city_img")

Now we can finish and save the excel file:

# save workbook
workbook.save('image_report.xlsx')

In my case final result looks like this:

Code and template images can be found on GitHub here: