Writing pandas/matplotlib image directly into XLSX

问题:

I am generating plots in pandas/matplotlib and wish to write them to an XLSX file. I am not looking to create native Excel charts; I am merely writing the plots as non-interactive images. I am using the XlsxWriter library/engine.

The closest solution I have found is the answer to this SO question, which suggests using the XlsxWriter.write_image() method. However, this method appears to take a filename as its input. I am trying to programmatically pass the direct output from a pandas/matplotlib plot() call, e.g. something like this:

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

or this:

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, short of the workaround of writing the image to a disk file first?

Update

Answer below got me on the right track and am accepting. I needed to make a few changes, mainly (I think) because I am using Python 3 and perhaps 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}
)

The "" in the insert_image() code is to trick Excel, which is still expecting a filename/URL/etc.

回答1:

You can save the image to memory as a file object (not to disk) and then use that when inserting to 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)