I am trying to learn how to place an image in an Excel worksheet but I am having a problem with wb.save. My program ends with the following error:

"C:\Users\Don\PycharmProjects\Test 2\venv\Scripts\python.exe" "C:/Users/Don/PycharmProjects/Test 2/Test 2.py"
Traceback (most recent call last):File "C:/Users/Don/PycharmProjects/Test 2/Test 2.py", line 20, in <module>wb.save('POKENO Cards new.xlsx')File "C:\Users\Don\PycharmProjects\Test 2\venv\lib\site-packages\openpyxl\workbook\workbook.py", line 392, in savesave_workbook(self, filename)File "C:\Users\Don\PycharmProjects\Test 2\venv\lib\site-packages\openpyxl\writer\excel.py", line 293, in save_workbookwriter.save()File "C:\Users\Don\PycharmProjects\Test 2\venv\lib\site-packages\openpyxl\writer\excel.py", line 275, in saveself.write_data()File "C:\Users\Don\PycharmProjects\Test 2\venv\lib\site-packages\openpyxl\writer\excel.py", line 75, in write_dataself._write_worksheets()File "C:\Users\Don\PycharmProjects\Test 2\venv\lib\site-packages\openpyxl\writer\excel.py", line 218, in _write_worksheetsself._write_drawing(ws._drawing)File "C:\Users\Don\PycharmProjects\Test 2\venv\lib\site-packages\openpyxl\writer\excel.py", line 141, in _write_drawingself._archive.writestr(drawing.path[1:], tostring(drawing._write()))File "C:\Users\Don\PycharmProjects\Test 2\venv\lib\site-packages\openpyxl\drawing\spreadsheet_drawing.py", line 295, in _writeself._rels.append(rel)
UnboundLocalError: local variable 'rel' referenced before assignmentProcess finished with exit code 1

This is my code. It works until the last statement.

import openpyxl as xl
from PIL import Image
#im = Image.open(r"Playing Cards/2C.jpg")
im = Image.open(r"C:/Users/Don/Pictures/resized Nyle.jpg")
wb = xl.load_workbook(filename="C:/Users/Don/PycharmProjects/Test 2/POKENO   Cards.xlsx")
ws1 = wb.worksheets[0]
im.show()
img = im.resize((78, 100))
img.show()
wb2 = xl.load_workbook(filename="C:/Users/Don/PycharmProjects/Test 2/POKENO Cards new.xlsx")
ws2 = wb2.active
ws2.add_image(im, 'A4')
wb2.save(filename="C:/Users/Don/PycharmProjects/Test 2/POKENO Cards new.xlsx")

The error is a bit cryptic, but the Traceback is quite clear enough to trace the cause of the problem: that openpyxl's add_image method expects a openpyxl.drawing.image.Image object, and not a PIL Image.

If you check the lines in openpyxl / drawing / spreadsheet_drawing.py that raised the error, there's a check there for the image type:

for idx, obj in enumerate(self.charts + self.images, 1):anchor = _check_anchor(obj)if isinstance(obj, ChartBase):rel = Relationship(type="chart", Target=obj.path)...elif isinstance(obj, Image):rel = Relationship(type="image", Target=obj.path)...anchors.append(anchor)self._rels.append(rel)

The problem is that your code

im = Image.open(r"C:/Users/Don/Pictures/resized Nyle.jpg")
...
ws2.add_image(im, 'A4')

passed in a 'PIL.PngImagePlugin.PngImageFile' type. It is not a ChartBase type or openpyxl's own Image type. So rel will never be initialized leading to the error that rel was used before being assigned something, leading to "local variable 'rel' referenced before assignment...".

If you check openpyxl / drawing / image, you might notice that openpyxl's Image is just a wrapper for PIL's Image. It needs a PIL.Image.Image object and internally calls PILImage.open(img) on the passed image object if it isn't one.

So you might think that converting img to openpyxl's Image might work.

import openpyxl as xl
from openpyxl import Workbook
from openpyxl.drawing.image import Image as XLImage
from PIL import Image as PILImageim = PILImage.open("my_input_image.png")
pil_img = im.resize((78, 100))
xl_img = XLImage(pil_img)wb2 = Workbook()
ws2 = wb2.active
ws2.add_image(xl_img, 'A1')
wb2.save(filename="output.xlsx")

That would solve the original error, but then you'll get a new error, "'Image' object has no attribute 'fp'", because openpyxl's Image module expects that the image object has a fp attribute, which is only available if the Image was created from a file (i.e. open(filename)).

So, the solution I can come up with is to:

1. Create the image object as PIL Image
2. Do image pre-processing (ex. resize)
3. Save the pre-processed image back to a file
4. Create the image object as openpyxl's image
5. Add that image to the workbook

from openpyxl import Workbook
from openpyxl.drawing.image import Image as XLImage
from PIL import Image as PILImageim = PILImage.open("my_input_image.png")pil_img = im.resize((78, 100))
pil_img.save("my_resized_image.png")xl_img = XLImage("my_resized_image.png")wb2 = Workbook()
ws2 = wb2.active
ws2.add_image(xl_img, 'A1')
wb2.save(filename="output.xlsx")

If you don't need to do any image processing before adding the image, just open the file directly using openpyxl's Image class:

from openpyxl import Workbook
from openpyxl.drawing.image import Image as XLImagexl_img = XLImage("my_input_image.png")wb2 = Workbook()
ws2 = wb2.active
ws2.add_image(xl_img, 'A1')
wb2.save(filename="output.xlsx")