send an in-memory chart object to Excel file

Question

Sorry, I'm a newbie so please be gentle. Is there a way to send an in-memory file (generated using Matplotlib) to an Excel file? I'm trying to do this using openpyxl but no luck. Any suggestions? Thanks!

I hacked on the following code to produce in-memory chart object:

import io
from PIL import Image
import matplotlib.pyplot as plt
plt.figure()
plt.plot([1, 2])
plt.title("test")
buf = io.BytesIO()
plt.savefig(buf, format='png')
buf.seek(0)
im = Image.open(buf)

wb = openpyxl.load_workbook('input.xlsx')
ws = wb.active


ws.add_image(im, 'A1')
wb.save('output.xlsx')

Unfortunately, this produced the following error message: UnboundLocalError: local variable 'rel' referenced before assignment

Any suggestions would be greatly appreciated. Thanks!

Note: the following works but is inefficient.

wb = openpyxl.load_workbook('input.xlsx')
ws = wb.active
img = openpyxl.drawing.image.Image('my_chart.png')
ws.add_image(img, 'A1')
wb.save('output.xlsx')

Answer 1

At least since v2.6.2 of openpyxl it is totally possible to insert in-memory images without requiring temporary file saving to fool the handling code. The problem was that add_image() needs the right Image object.
Here is the working code sample:

import io
#from PIL import Image
import matplotlib.pyplot as plt
from openpyxl.drawing.image import Image
plt.figure()
plt.plot([1, 2])
plt.title("test")
buf = io.BytesIO()
plt.savefig(buf, format='png')
buf.seek(0)
#im = Image.open(buf)
im = Image(buf)
im.anchor = 'A1'
wb = openpyxl.load_workbook('input.xlsx')
ws = wb.active
ws.add_image(im)
wb.save('output.xlsx')