13

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.

Improve this question
5
  • 1
    Quote: The "" in the insert_image() code is to trick the API, which is still expecting a filename/URL/etc. Strictly speaking, it is Excel that is expecting a filename, and for consistency it would be best to supply one. Commented Jun 9, 2015 at 10:55
  • Also, out of curiosity, why are you using matplotlib in this case and not adding a chart to the worksheet directly? Commented Jun 9, 2015 at 10:57
  • Thanks, I'll fix that. Re 2nd comment, the reason is that I am not exporting the data that would build the charts, just some different data and the plots themselves: basically regression result summary and a whole lot of diagnostic plots like normal histogram of resids, QQ, scatters vs. resids, etc. This is all for diagnostic purposes, not final business report. Commented Jun 9, 2015 at 11:16
  • This inserts a static image, not a real Plot - kind of a crummy version of a real Excel sheet. Any way to insert a "real" graph (with editable axes etc)? Commented Aug 22, 2023 at 15:22
  • Yup that's why my question says " I am not looking to create native Excel charts; I am merely writing the plots as non-interactive images." Commented Aug 24, 2023 at 15:40

1 Answer 1

Reset to default
11

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

StringIO is gone from Python 3. Any suggestions on how to make this work in Python 3?
In python 3 StringIO and BytesIO are in the builtin io module. Please have a look at the updated OP's question with example.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.