{"id":137,"date":"2015-01-18T13:39:18","date_gmt":"2015-01-18T13:39:18","guid":{"rendered":"http:\/\/pyxll.com\/blog\/?p=137"},"modified":"2020-11-02T14:36:44","modified_gmt":"2020-11-02T14:36:44","slug":"plotting-in-excel-with-pyxll-and-matplotlib-part-2","status":"publish","type":"post","link":"https:\/\/www.pyxll.com\/blog\/plotting-in-excel-with-pyxll-and-matplotlib-part-2\/","title":{"rendered":"Plotting in Excel with Python and Matplotlib \u2013 #2"},"content":{"rendered":"\n

NOTE: PyXLL 5 includes matplotlib integration.<\/strong>
See Charts and Plotting<\/a> for details.
This post can be used for earlier versions of PyXLL, but we recommend updating the PyXLL 5 and using that instead.<\/p>\n\n\n

Part 1<\/a> 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.<\/span><\/p>\n

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.<\/p>\n

Embedded Plotting<\/h1>\n

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.<\/p>\n

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

\nfrom pyxll import xl_func, xlfCaller\nfrom pandas.stats.moments import ewma\nimport os\n\n# matplotlib imports\nfrom matplotlib.backends.backend_agg import FigureCanvasAgg as FigureCanvas\nfrom matplotlib.figure import Figure\n\n# For interacting with Excel from Python\nfrom pyxll import get_active_object\nimport win32com.client\n\n@xl_func("string figname, "\n         "numpy_column&amp;lt;float&amp;gt; xs, "\n         "numpy_column&amp;lt;float&amp;gt; ys, "\n         "int span: string",\n         macro=True)\ndef mpl_plot_ewma_embedded(figname, xs, ys, span):\n    # create the figure and axes for the plot\n    fig = Figure(figsize=(8, 6), dpi=75, facecolor=(1, 1, 1), edgecolor=(0, 0, 0))\n    ax = fig.add_subplot(111)\n\n    # calculate the moving average\n    ewma_ys = ewma(ys, span=span)\n\n    # plot the data\n    ax.plot(xs, ys, alpha=0.4, label="Raw")\n    ax.plot(xs, ewma_ys, label="EWMA")\n    ax.legend()\n\n    # write the figure to a temporary image file\n    filename = os.path.join(os.environ["TEMP"], "xlplot_%s.png" % figname)\n    canvas = FigureCanvas(fig)\n    canvas.draw()\n    canvas.print_png(filename)\n<\/pre><\/pre>\n
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.<\/p>\n
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<\/a>).<\/p>\n
The code below uses the PyXLL function xlfCaller<\/em> 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<\/em> argument type.<\/p>\n
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.<\/p>\n
When the picture is created Excel takes a copy of the image data and so the temporary image file may be deleted afterwards.<\/p>\n
\n    # continued from above...\n\n    # Show the figure in Excel as a Picture object on the same sheet\n    # the function is being called from.\n    xl = xl_app()\n    caller = xlfCaller()\n    sheet = xl.Range(caller.address).Worksheet\n\n    # if a picture with the same figname already exists then get the position\n    # and size from the old picture and delete it.\n    for old_picture in sheet.Pictures():\n        if old_picture.Name == figname:\n            height = old_picture.Height\n            width = old_picture.Width\n            top = old_picture.Top\n            left = old_picture.Left\n            old_picture.Delete()\n            break\n    else:\n        # otherwise place the picture below the calling cell.\n        top_left = sheet.Cells(caller.rect.last_row+2, caller.rect.last_col+1)\n        top = top_left.Top\n        left = top_left.Left\n        width, height = fig.bbox.bounds[2:]\n\n    # insert the picture\n    # Ref: http:\/\/msdn.microsoft.com\/en-us\/library\/office\/ff198302%28v=office.15%29.aspx\n    picture = sheet.Shapes.AddPicture(Filename=filename,\n                                      LinkToFile=0,  # msoFalse\n                                      SaveWithDocument=-1,  # msoTrue\n                                      Left=left,\n                                      Top=top,\n                                      Width=width,\n                                      Height=height)\n\n    # set the name of the new picture so we can find it next time\n    picture.Name = figname\n\n    # delete the temporary file\n    os.unlink(filename)\n\n    return "[Plotted '%s']" % figname\n<\/pre><\/pre>\n
All the code from this blog is available on github https:\/\/github.com\/pyxll\/pyxll-examples\/tree\/master\/matplotlib<\/a>.<\/p>","protected":false},"excerpt":{"rendered":"
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.<\/p>\n","protected":false},"author":1,"featured_media":151,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[11,2],"tags":[6,7,3,8],"class_list":["post-137","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-matplotlib","category-pyxll","tag-excel","tag-matplotlib","tag-python","tag-pyxll"],"acf":[],"jetpack_featured_media_url":"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2015\/01\/matplotlib-embedded.gif","jetpack_shortlink":"https:\/\/wp.me\/p7l3LP-2d","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/137","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/comments?post=137"}],"version-history":[{"count":1,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/137\/revisions"}],"predecessor-version":[{"id":1434,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/137\/revisions\/1434"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/media\/151"}],"wp:attachment":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/media?parent=137"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/categories?post=137"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/tags?post=137"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}