NOTE: PyXLL 5 includes matplotlib integration.
See Charts and Plotting for details.
This post can be used for earlier versions of PyXLL, but we recommend updating the PyXLL 5 and using that instead.

Part 1 of this blog series demonstrated how to use matplotlib to plot charts and display them from Excel using the matplotlib Qt backend. That method opened the charts as new windows on top of the existing Excel workbook in the same way as if you were displaying them using an interactive Python prompt like IPython. They were made non-blocking so that Excel could still be used while the charts were displayed.

This second part will show another way to use matplotlib in Excel using PyXLL, but will embed the charts into the Excel worksheet rather than open new windows. This is preferable in some situations, such as when creating reports where the charts should be in-line alongside the data.

Embedded Plotting

Embedding a matplotlib chart into Excel is reasonably straightforward. Previously we used the Qt backend to render on to an interactive window, but for embedding we will use the ability of matplotlib to plot to an image file and insert that into Excel as a picture. By naming the picture in Excel we can find it and replace it when the source data for that chart is changed. This will have the effect of making the chart update live as the data in the spreadsheet changes.

We’ll start off with some code similar to that from Part 1 to plot the exponentially weighted moving average of a series.

from pyxll import xl_func, xlfCaller
from pandas.stats.moments import ewma
import os
 
# matplotlib imports
from matplotlib.backends.backend_agg import FigureCanvasAgg as FigureCanvas
from matplotlib.figure import Figure
 
# For interacting with Excel from Python
from pyxll import get_active_object
import win32com.client
 
@xl_func("string figname, "
         "numpy_column&amp;lt;float&amp;gt; xs, "
         "numpy_column&amp;lt;float&amp;gt; ys, "
         "int span: string",
         macro=True)
def mpl_plot_ewma_embedded(figname, xs, ys, span):
    # create the figure and axes for the plot
    fig = Figure(figsize=(8, 6), dpi=75, facecolor=(1, 1, 1), edgecolor=(0, 0, 0))
    ax = fig.add_subplot(111)
 
    # calculate the moving average
    ewma_ys = ewma(ys, span=span)
 
    # plot the data
    ax.plot(xs, ys, alpha=0.4, label="Raw")
    ax.plot(xs, ewma_ys, label="EWMA")
    ax.legend()
 
    # write the figure to a temporary image file
    filename = os.path.join(os.environ["TEMP"], "xlplot_%s.png" % figname)
    canvas = FigureCanvas(fig)
    canvas.draw()
    canvas.print_png(filename)

Now we have an image file containing the matplotlib chart. To display that in Excel we use the AddPicture method on the Shapes property of the Worksheet.

The Excel Object API is the same when accessed in Python via COM as when programmed in VBA so it may already be familiar to you. It is well documented on the Microsoft Developer Network (MSDN) web pages and, although not in Python, the information there can be helpful (see http://msdn.microsoft.com/en-us/library/office/ff194068(v=office.15).aspx).

The code below uses the PyXLL function xlfCaller to get the calling cell’s address and adds the picture below that cell. If you wanted to add the chart to another sheet you could pass the sheet name to the function and use that, or pass a cell reference using the xl_cell argument type.

Before adding the picture we look for any existing picture with the same name on the sheet and if one exists the position and dimensions of the existing picture are used when creating the new one and the existing one is deleted.

When the picture is created Excel takes a copy of the image data and so the temporary image file may be deleted afterwards.

# continued from above...
 
# Show the figure in Excel as a Picture object on the same sheet
# the function is being called from.
xl = xl_app()
caller = xlfCaller()
sheet = xl.Range(caller.address).Worksheet
 
# if a picture with the same figname already exists then get the position
# and size from the old picture and delete it.
for old_picture in sheet.Pictures():
    if old_picture.Name == figname:
        height = old_picture.Height
        width = old_picture.Width
        top = old_picture.Top
        left = old_picture.Left
        old_picture.Delete()
        break
else:
    # otherwise place the picture below the calling cell.
    top_left = sheet.Cells(caller.rect.last_row+2, caller.rect.last_col+1)
    top = top_left.Top
    left = top_left.Left
    width, height = fig.bbox.bounds[2:]
 
# insert the picture
# Ref: http://msdn.microsoft.com/en-us/library/office/ff198302%28v=office.15%29.aspx
picture = sheet.Shapes.AddPicture(Filename=filename,
                                  LinkToFile=0,  # msoFalse
                                  SaveWithDocument=-1,  # msoTrue
                                  Left=left,
                                  Top=top,
                                  Width=width,
                                  Height=height)
 
# set the name of the new picture so we can find it next time
picture.Name = figname
 
# delete the temporary file
os.unlink(filename)
 
return "[Plotted '%s']" % figname

All the code from this blog is available on github https://github.com/pyxll/pyxll-examples/tree/master/matplotlib.