Writing a pandas / matplotlib image directly to an .xlsx file

Question

I am creating graphics in pandas / matplotlib and want to write them to an xlsx file. I do not want to create my own Excel charts; I just write graphics as non-interactive images. I am using the XlsxWriter library / engine .

The closest solution I found is the answer to this SO question , which suggests using XlsxWriter.write_image () . However, this method seems to accept the file name as its input. I am trying to programmatically pass the direct output from a call to pandas / matplotlib plot() , for example. something like that:

 h = results.resid.hist() worksheet.insert_image(row, 0, h) # doesn't work 

or that:

 s = df.plot(kind="scatter", x="some_x_variable", y="resid") worksheet.insert_image(row, 0, s) # doesn't work 

Is there any way to accomplish this, except bypassing the image bypass to the disk file?

Update

The answer below gave me the correct path and I accept. I needed to make a few changes, mostly (I think), because I'm using Python 3 and maybe some API changes. Here is the solution:

 from io import BytesIO import matplotlib.pyplot as plt imgdata = BytesIO() fig, ax = plt.subplots() results.resid.hist(ax=ax) fig.savefig(imgdata, format="png") imgdata.seek(0) worksheet.insert_image( row, 0, "", {'image_data': imgdata} ) 

"" in the insert_image() code should fool Excel, which is still expecting the file name / URL / etc.

Answer 1

You can save the image to memory as a file object (and not to disk), and then use it when pasting into an Excel file:

 import matplotlib.pyplot as plt from cStringIO import StringIO imgdata = StringIO() fig, ax = plt.subplots() # Make your plot here referencing ax created before results.resid.hist(ax=ax) fig.savefig(imgdata) worksheet.insert_image(row, 0, imgdata)