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.
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. expired domains 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<float> xs, " "numpy_column<float> 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.