Images in Excel

In this example I am going to show you how to insert images in excel file using Python script. You may have a requirement that needs to insert one or more images into an excel file. Either to represent something using the images in the excel file or you may need both text and images into the excel file to represent something.

I am using openpyxl package to insert images in excel files. openpyxl package allows you to to read/write Excel 2010 xlsx/xlsm files. You also need to install pillow package or library in order to be able to insert images into excel file.

Prerequisites

Python 3.9.1, openpyxl 3.0.10 (pip install openpyxl), pillow (pip install pillow)

Insert Images

Now I am going to create an example to insert two images in the excel sheet.

from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()

img1 = Image('1.jpg')
img2 = Image('2.jpg')

ws = wb.active

#ws.add_image(img1)
#ws.add_image(img2)

#ws.add_image(img1, 'B2')
#ws.add_image(img2, 'B14')

ws.add_image(img1, 'A2')
ws.add_image(img2, 'G2')

wb.save('excel-image.xlsx')

There is no need to create a file on the filesystem to get started with openpyxl. Just import the Workbook class and start work with it.

from openpyxl import Workbook
wb = Workbook()

A workbook is always created with at least one worksheet. You can get it by using the Workbook.active property. This is set to 0 by default. Unless you modify its value, you will always get the first worksheet by using this method.

ws = wb.active

If you want, you can create new worksheets using the Workbook.create_sheet() method:

ws1 = wb.create_sheet("Mysheet") # insert at the end (default)
ws2 = wb.create_sheet("Mysheet", 0) # insert at first position
ws3 = wb.create_sheet("Mysheet", -1) # insert at the penultimate position

I have created image objects by using the Image() constructor:

img1 = Image('1.jpg')
img2 = Image('2.jpg')

Using the add_image() function I am inserting image to the excel sheet. The following lines will overlap one image over another image as I have not mentioned the row and column position of the excel sheet:

ws.add_image(img1)
ws.add_image(img2)

For example, the output will be similar to the following image:

The following two lines will place images above and below. The img1 will be placed at B column and starts at row number 2, whereas the img2 image will be placed at B column and starts at row number 14.

ws.add_image(img1, 'B2')
ws.add_image(img2, 'B14')

For example, the output will be similar to the following image:

The following lines will place images side by side. One at row 2 and column A and another at row 2 and column G.

ws.add_image(img1, 'A2')
ws.add_image(img2, 'G2')

Hope you got an idea how to insert images in excel file.

Source Code

Download