In the previous couple parts of the openpyxl series we were talking about styling spreadsheets, in particular about conditional styling. In the previous part we were adding data bars and before that we were adding icons. These are all graphical elements that represent data in one way or another. But sometimes you may want to add a graphical element that doesn’t represent data, or, to be more precise, is independent of the data. In this part we’ll see how to add simple images in openpyxl to our workbook.
We’re still going to be working with our wb2.xlsx file. It contains the following data:
company | city | category | income | number_of_employees |
Roses and Pansies | Toronto | gardening | 456845.00 | 26 |
Reader’s Paradise | Sydney | publisher | 911500.00 | 44 |
Dogs and Cats | Madrid | pets | 458784.00 | 36 |
Enjoy Your Meal | New Orleans | restaurant | 447884.00 | 18 |
Bloom Store | London | gardening | 1254654.00 | 84 |
Tastes of the South | Miami | restaurant | 587454.00 | 42 |
Pets for Everyone | Athens | pets | 987887.00 | 21 |
Eat ‘Em Up | Helsinki | restaurant | 1254477.00 | 53 |
Mr. Bunny | Vienna | pets | 254478.00 | 12 |
Bookworms Rule | Rome | publisher | 124474.00 | 40 |
SUM | 376 | |||
MIN | 12 | |||
MAX | 84 | |||
AVERAGE | 37.6 | |||
BIG COMPANIES | 4 |
As usual, let’s start by loading the workbook:
>>> from openpyxl import load_workbook
>>> workbook = load_workbook(filename="wb2.xlsx")
>>> sheet = workbook.active
I’m going to add the following image to my workbook:
The image file is saved in the same folder where the workbook is. Its name is ‘Exploding Planet.png’. You can use whatever image you want.
Adding an image is easy. There are just a few steps to follow. First of all, we’ll need the Image class, so let’s import it:
>>> from openpyxl.drawing.image import Image
Next we have to create an image instance and pass the name of the image file to the constructor:
>>> image = Image('Exploding Planet.png')
The size of my image is 540 by 540 pixels. This is a bit more than I need. So, let’s set the width and height both to 90 px:
>>> image.width, image.height = 90, 90
Now we have to actually add the image. We have to specify the image that we want to add and the location of its top left corner:
>>> sheet.add_image(image, 'C15')
Finally, let’s save the workbook with the image as a new file:
>>> workbook.save(filename="wb2i.xlsx")
After that we can open the wb2i.xlsx file and see the image in it:
Now that we know how to add styles, small graphical items like icons and data bars and images, there’s still one important graphical element that we often want to add to our spreadsheets, a chart. This element by all means depicts the data. There are several types of charts. We’ll start talking about charts in the next part of the series.