{"id":1764,"date":"2024-05-21T17:07:37","date_gmt":"2024-05-21T17:07:37","guid":{"rendered":"https:\/\/www.pyxll.com\/blog\/?p=1764"},"modified":"2024-08-16T17:29:50","modified_gmt":"2024-08-16T17:29:50","slug":"plotting-in-excel-with-python-and-matplotlib-3","status":"publish","type":"post","link":"https:\/\/www.pyxll.com\/blog\/plotting-in-excel-with-python-and-matplotlib-3\/","title":{"rendered":"Plotting in Excel with Python and Matplotlib \u2013 #3"},"content":{"rendered":"\n<p>Wow! It&#8217;s been a long time since I wrote <a href=\"https:\/\/www.pyxll.com\/blog\/plotting-in-excel-with-pyxll-and-matplotlib-part-2\/\" target=\"_blank\" rel=\"noreferrer noopener\">Plotting in Excel with Python and Matplotlib \u2013 #2<\/a>. A <em>lot <\/em>has changed since then and this Part 3 post is long over-due!<\/p>\n\n\n\n<p>I you have followed the previous two blog posts then everything contained there will still work. But, with updates to the PyXLL add-in since those posts were written everything is now a lot easier.<\/p>\n\n\n\n<p>Python Excel integration with PyXLL makes getting your matplotlib charts (as well as others, including <a href=\"https:\/\/www.pyxll.com\/docs\/userguide\/plotting\/seaborn.html\">seaborn<\/a>, <a href=\"https:\/\/www.pyxll.com\/docs\/userguide\/plotting\/plotly.html\">plotly<\/a> and <a href=\"https:\/\/www.pyxll.com\/docs\/userguide\/plotting\/bokeh.html\">bokeh<\/a>) into Excel easy. You create your chart in exactly the same way you would normally, and then call <a href=\"https:\/\/www.pyxll.com\/docs\/api\/plotting.html#pyxll.plot\">pyxll.plot<\/a> to display the Python plot directly in Excel.<\/p>\n\n\n\n<p>All of the code from this post is available here <a href=\"https:\/\/github.com\/pyxll\/pyxll-examples\/tree\/master\/matplotlib\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/github.com\/pyxll\/pyxll-examples\/tree\/master\/matplotlib<\/a><\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_83 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a0b4b4dbecaa\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a0b4b4dbecaa\"  aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.pyxll.com\/blog\/plotting-in-excel-with-python-and-matplotlib-3\/#Embedding_Python_Charts_in_the_Excel_workbook\" >Embedding Python Charts in the Excel workbook<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.pyxll.com\/blog\/plotting-in-excel-with-python-and-matplotlib-3\/#Interactive_Python_Charts_in_Excel\" >Interactive Python Charts in Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.pyxll.com\/blog\/plotting-in-excel-with-python-and-matplotlib-3\/#Improved_Interactive_Python_Charts_in_Excel\" >Improved Interactive Python Charts in Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.pyxll.com\/blog\/plotting-in-excel-with-python-and-matplotlib-3\/#Even_More_Improved_Python_Charts_in_Excel\" >Even More Improved Python Charts in Excel!<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\" id=\"h-embedding-python-charts-in-the-excel-workbook\"><span class=\"ez-toc-section\" id=\"Embedding_Python_Charts_in_the_Excel_workbook\"><\/span>Embedding Python Charts in the Excel workbook<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>In <a href=\"https:\/\/www.pyxll.com\/blog\/plotting-in-excel-with-pyxll-and-matplotlib-part-2\/\">part 2<\/a> of this series we looked at a function that created a matplotlib chart. We then embedded that into Excel as a Picture object using the <a href=\"https:\/\/pypi.org\/project\/pywin32\/\">pywin32<\/a> package. Our function took a series of x and y values, calculated a moving average, and then plot those using matplotlib.<\/p>\n\n\n\n<p>The following function does the same (with a couple of updates as pandas has also changed in the interim!). But, instead of exporting the chart as an image and then adding it into Excel as a Picture object using pywin32, now we call <a href=\"https:\/\/www.pyxll.com\/docs\/api\/plotting.html#pyxll.plot\">pyxll.plot<\/a> instead.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; highlight: [19,20]; title: ; notranslate\" title=\"\">\nfrom pyxll import xl_func, plot\nimport pandas as pd\nfrom matplotlib import pyplot as plt\n\n\n@xl_func(&quot;numpy_column&lt;float&gt; xs, numpy_column&lt;float&gt; ys, int span: object&quot;)\ndef mpl_plot_ewma(xs, ys, span):\n    # create the figure and axes for the plot\n    fig, ax = plt.subplots()\n\n    # calculate the moving average\n    moving_average = pd.Series(ys, index=xs).ewm(span=span).mean()\n\n    # plot the data\n    ax.plot(xs, ys, alpha=0.4, label=&quot;Raw&quot;)\n    ax.plot(xs, moving_average.values, label=&quot;EWMA&quot;)\n    ax.legend()\n\n    # Show the figure in Excel\n    plot(fig)\n\n    # Return the figure as an object\n    return fig\n<\/pre><\/div>\n\n\n<p>Using <a href=\"https:\/\/www.pyxll.com\/docs\/api\/plotting.html#pyxll.plot\">pyxll.plot<\/a> save a lot of tedious Python code to get the chart into Excel. It also produces a better result! Instead of exporting a bitmap image, in newer versions of Excel it can use a vector image. This results in a better image quality. Resizing also works, redrawing the matplotlib figure after each time you resize the Excel object.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-interactive-python-charts-in-excel\"><span class=\"ez-toc-section\" id=\"Interactive_Python_Charts_in_Excel\"><\/span>Interactive Python Charts in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>In <a href=\"https:\/\/www.pyxll.com\/blog\/plotting-in-excel-with-pyxll-and-matplotlib\/\" target=\"_blank\" rel=\"noreferrer noopener\">Part 1<\/a> we looked at how to display an interactive matplotlib window from Excel. That has also become a lot simpler since PyXLL introduced <a href=\"https:\/\/www.pyxll.com\/docs\/api\/ctps.html\">Custom Task Panes<\/a> (or CTP for short). CTPs are native Excel windows that can be floating or docked. PyXLL can host a variety of Python UI toolkit window types in CTPs, including PyQt (which is what we used in Part 1).<\/p>\n\n\n\n<p>As before, we can create a matplotlib canvas using the PyQt backend. This time however, rather than worrying about the Qt message loop, we can let PyXLL do the hard work!<\/p>\n\n\n\n<p>The following worksheet function takes a Figure object (like the one returned by the function above). It then adds it to canvas using the matplotlib Qt backend and uses <a href=\"https:\/\/www.pyxll.com\/docs\/api\/ctps.html#create-ctp\">pyxll.create_ctp<\/a> to display it in Excel.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; highlight: [28,29]; title: ; notranslate\" title=\"\">\nfrom pyxll import xl_func, create_ctp, CTPDockPositionFloating\nfrom matplotlib.backends.backend_qtagg import FigureCanvasQTAgg as FigureCanvas\nfrom matplotlib.backends.backend_qtagg import NavigationToolbar2QT as NavigationToolbar\nfrom PySide6.QtWidgets import QWidget, QVBoxLayout, QApplication\n\n\n@xl_func\ndef show_matplotlib_ctp(fig):\n    # Before we can create a Qt widget the Qt App must have been initialized.\n    # Make sure we keep a reference to this until create_ctp is called.\n    app = QApplication.instance()\n    if app is None:\n        app = QApplication(&#x5B;])\n\n    # Create the widget and a layout for it\n    widget = QWidget()\n    layout = QVBoxLayout(widget)\n    widget.setLayout(layout)\n\n    # Add the matplotlib plot to the window\n    canvas = FigureCanvas(fig)\n    layout.addWidget(canvas)\n\n    # And add a toolbar\n    toolbar = NavigationToolbar(canvas)\n    layout.addWidget(toolbar)\n\n    # Create the CTP to show the widget in Excel\n    create_ctp(widget, width=800, height=800, position=CTPDockPositionFloating)\n<\/pre><\/div>\n\n\n<p>This now gives us a fully interactive matplotlib window, directly in Excel! We can zoom and pan interactively and access all the features of the usual matplotlib interface. Using <a href=\"https:\/\/www.pyxll.com\/docs\/api\/ctps.html#create-ctp\">pyxll.create_ctp<\/a> is make this much easier before. Unlike previously, the window is now a native Excel window and can be docked wherever we want it.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Screenshot-2024-05-21-161025.png\"><img decoding=\"async\" width=\"1024\" height=\"650\" data-attachment-id=\"1776\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/plotting-in-excel-with-python-and-matplotlib-3\/screenshot-2024-05-21-161025\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Screenshot-2024-05-21-161025.png\" data-orig-size=\"1768,1123\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"Matplotlib Python Excel CTP\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Screenshot-2024-05-21-161025-1024x650.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Screenshot-2024-05-21-161025-1024x650.png\" alt=\"\" class=\"wp-image-1776\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Screenshot-2024-05-21-161025-1024x650.png 1024w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Screenshot-2024-05-21-161025-300x191.png 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Screenshot-2024-05-21-161025-768x488.png 768w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Screenshot-2024-05-21-161025-1536x976.png 1536w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Screenshot-2024-05-21-161025.png 1768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-improved-interactive-python-charts-in-excel\"><span class=\"ez-toc-section\" id=\"Improved_Interactive_Python_Charts_in_Excel\"><\/span>Improved Interactive Python Charts in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The code in the section above has a problem. Whenever the function is called a new CTP is created. This is probably not what you would want!<\/p>\n\n\n\n<p>To solve this problem, one solution is to use a right click context menu instead of a worksheet function. It&#8217;s possible to add your own Python function&#8217;s to Excel&#8217;s context menu. We can use that to pop up the CTP from a context menu item instead of from a worksheet function. This avoids the problem of the CTP being created each time the worksheet recalculates.<\/p>\n\n\n\n<p>Creating context menu items is very similar to configuring the Excel ribbon toolbars. First we write a function that will be called when the menu item is selected. This will be almost identical to our function above. Instead of being passed the Figure object, it&#8217;s passed a ribbon control object. To get the Figure we have to get it from the current selection. Everything else from then is the same as before.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\ndef show_selected_matplotlib_ctp(control):\n    # Get the Excel application object\n    xl = xl_app()\n\n    # Get the current and check if it as a matplotlib Figure\n    cell = XLCell.from_range(xl.Selection)\n    fig = cell.options(type=&quot;object&quot;).value\n\n    if not isinstance(fig, Figure):\n        raise ValueError(&quot;Expected a matplotlib Figure object&quot;)\n\n    # Before we can create a Qt widget the Qt App must have been initialized.\n    # Make sure we keep a reference to this until create_ctp is called.\n    app = QApplication.instance()\n    if app is None:\n        app = QApplication(&#x5B;])\n\n    # Create the widget and a layout for it\n    widget = QWidget()\n    layout = QVBoxLayout(widget)\n    widget.setLayout(layout)\n\n    # Add the matplotlib plot to the window\n    canvas = FigureCanvas(fig)\n    widget.layout().addWidget(canvas)\n\n    # And add a toolbar\n    toolbar = NavigationToolbar(canvas)\n    widget.layout().addWidget(toolbar)\n\n    # Show as a custom task pane using PyXLL.create_ctp\n    create_ctp(widget, width=800, height=800, position=CTPDockPositionFloating)\n<\/pre><\/div>\n\n\n<p>To add that Python function to Excel&#8217;s context menu we have to edit our ribbon.xml file and include the following. Note that the customUI xmlns must be &#8220;2009\/07\/customui&#8221; or later. If you have items in your &#8220;&lt;ribbon&gt;&#8221; section already you can add the &#8220;&lt;contextMenus&gt;&#8221; section afterwards. You will also need to change the &#8220;onAction&#8221; value to match your Python module and function name, if different from mine.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: xml; title: ; notranslate\" title=\"\">\n&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;\n&lt;customUI xmlns=&quot;http:\/\/schemas.microsoft.com\/office\/2009\/07\/customui&quot;&gt;\n    &lt;ribbon&gt;\n    &lt;\/ribbon&gt;\n    &lt;contextMenus&gt;\n        &lt;contextMenu idMso=&quot;ContextMenuCell&quot;&gt;\n            &lt;button id=&quot;MplShowPlot&quot; label=&quot;Show Matplotlib Figure&quot;\n                onAction=&quot;interactiveplot.show_selected_matplotlib_ctp&quot;\/&gt;\n        &lt;\/contextMenu&gt;\n   &lt;\/contextMenus&gt;\n&lt;\/customUI&gt;\n<\/pre><\/div>\n\n\n<p>With that added and the PyXLL add-in reloaded, you will now see &#8220;Show Matplotlib Figure&#8221; when you right click on a cell. Selecting that on the cell where the Figure object was returned previously will open a new CTP with that figure!<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Screenshot-2024-05-21-162138.jpg\"><img decoding=\"async\" width=\"1024\" height=\"484\" data-attachment-id=\"1782\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/plotting-in-excel-with-python-and-matplotlib-3\/screenshot-2024-05-21-162138\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Screenshot-2024-05-21-162138.jpg\" data-orig-size=\"1377,651\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"Show Matplotlib Figure\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Screenshot-2024-05-21-162138-1024x484.jpg\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Screenshot-2024-05-21-162138-1024x484.jpg\" alt=\"\" class=\"wp-image-1782\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Screenshot-2024-05-21-162138-1024x484.jpg 1024w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Screenshot-2024-05-21-162138-300x142.jpg 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Screenshot-2024-05-21-162138-768x363.jpg 768w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Screenshot-2024-05-21-162138.jpg 1377w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-even-more-improved-python-charts-in-excel\"><span class=\"ez-toc-section\" id=\"Even_More_Improved_Python_Charts_in_Excel\"><\/span>Even More Improved Python Charts in Excel!<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The first method of displaying an interactive Python chart in Excel one problem; a new CTP would be created each time the function was called. Other than that it&#8217;s quite nice as each time an input changes, the chart is redrawn.<\/p>\n\n\n\n<p>What if the function could remember that it created a CTP previously? Then it could update that rather than create a new one.<\/p>\n\n\n\n<p>We can do exactly that by maintaining a cache of QWidget objects. The cache will be keyed by the calling cell address. Each time our function is called from the same cell, it will return the widget created previously. Then all we need to do is update that widget with the latest figure.<\/p>\n\n\n\n<p>As we don&#8217;t want the cache to keep growing indefinitely, we&#8217;ll use a <a href=\"https:\/\/docs.python.org\/3.9\/library\/weakref.html#weakref.WeakValueDictionary\" target=\"_blank\" rel=\"noreferrer noopener\">weakref.WeakValueDictionary<\/a>. A WeakValueDictionary is a really useful class in Python. Unlike a normal dictionary, it doesn&#8217;t prevent a value entered into it from being destroyed. If the value is no longer referenced by anything else then the value in the dictionary is removed. This is perfect for what we need. Once the widget is no longer in use, there&#8217;s no need for us to keep it in the cache!<\/p>\n\n\n\n<p>Here&#8217;s the updated function. To get the calling cell address we need to use <a href=\"https:\/\/www.pyxll.com\/docs\/api\/capifunctions.html#xlfcaller\" target=\"_blank\" rel=\"noreferrer noopener\">pyxll.xlfCaller<\/a>. We check the new _mpl_ctp_cache dictionary to see if there&#8217;s already a widget for our calling address. If there isn&#8217;t we create a new one and everything&#8217;s the same as before. If there is, we close and remove the canvas and toolbar added previously, and add the new canvas with the new figure.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nfrom pyxll import xl_func, xlfCaller, create_ctp, schedule_call, CTPDockPositionFloating\nfrom matplotlib.backends.backend_qtagg import FigureCanvasQTAgg as FigureCanvas\nfrom matplotlib.backends.backend_qtagg import NavigationToolbar2QT as NavigationToolbar\nfrom PySide6.QtWidgets import QWidget, QVBoxLayout, QApplication\nfrom matplotlib.figure import Figure\nfrom functools import partial\nimport weakref\n\n\n# Dictionary of calling cell addresses to Qt widgets\n_mpl_ctp_cache = weakref.WeakValueDictionary()\n\n\n@xl_func(&quot;object fig, bool enabled: var&quot;, macro=True)  # macro=True is needed for xlfCaller\ndef show_matplotlib_ctp(fig, enabled=True):\n    &quot;&quot;&quot;Display a matplotlib Figure in a Custom Task Pane.\n\n    This worksheet function takes a cell reference rather than\n    an object directly as it keeps track of the custom task pane\n    and updates it with the new figure if called again for the same\n    cell.\n    &quot;&quot;&quot;\n    if not enabled:\n        return &quot;&#x5B;DISABLED]&quot;\n\n    if not isinstance(fig, Figure):\n        raise ValueError(&quot;Expected a matplotlib Figure object&quot;)\n\n    # Get the calling cell to check if there is already a visible CTP for this cell\n    cell = xlfCaller()\n\n    # Get the widget from the cache if it exists already\n    widget = _mpl_ctp_cache.get(cell.address, None)\n    show_ctp = True if widget is None else False\n    if widget is None:\n        # Before we can create a Qt widget the Qt App must have been initialized.\n        # Make sure we keep a reference to this until create_ctp is called.\n        app = QApplication.instance()\n        if app is None:\n            app = QApplication(&#x5B;])\n\n        # Create the widget and a layout for it\n        widget = QWidget()\n        layout = QVBoxLayout(widget)\n        widget.setLayout(layout)\n\n    # Close any old widgets and remove them from the layout\n    layout = widget.layout()\n    while layout.count() &gt; 0:\n        child = layout.itemAt(0)\n        child.widget().setParent(None)\n        layout.removeItem(child)\n\n    # Add the matplotlib plot to the window\n    canvas = FigureCanvas(fig)\n    layout.addWidget(canvas)\n\n    # And add a toolbar\n    toolbar = NavigationToolbar(canvas)\n    layout.addWidget(toolbar)\n\n    # Create and show the CTP if necessary.\n    # We use pyxll.schedule_call to schedule creating the CTP after this function\n    # has finished.\n    if show_ctp:\n        create_ctp_func = partial(\n            create_ctp,\n            widget,\n            width=800,\n            height=800,\n            position=CTPDockPositionFloating)\n        schedule_call(create_ctp_func)\n\n    # We use a WeakValueDict so the item stays in this dict so long as the widget is alive.\n    # Once the CTP is closed and the widget is destroyed then the item in the cache is\n    # cleared automatically.\n    _mpl_ctp_cache&#x5B;cell.address] = widget\n\n    return fig\n<\/pre><\/div>\n\n\n<p>With this function we have a fully interactive, dockable, matplotlib widget right in Excel. Whenever an input changes causing our figure to be recreated, the widget also updates!<\/p>\n\n\n\n<p>We used pyxll.schedule_call to schedule calling create_ctp in the code above. This is because when Excel first starts up, if opening a workbook that calls this function, the function could be called before Excel is completely initialized and calling create_ctp before Excel is fully initialized will fail. Using schedule_call ensures that create_ctp doesn&#8217;t get called until Excel is ready.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Animation3.gif\"><img decoding=\"async\" width=\"698\" height=\"516\" data-attachment-id=\"1791\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/plotting-in-excel-with-python-and-matplotlib-3\/animation3\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Animation3.gif\" data-orig-size=\"698,516\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"Python Excel Charts\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Animation3.gif\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Animation3.gif\" alt=\"\" class=\"wp-image-1791\"\/><\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Wow! It&#8217;s been a long time since I wrote Plotting in Excel with Python and Matplotlib \u2013 #2. A lot has changed since then and this Part 3 post is long over-due! I you have followed the previous two blog posts then everything contained there will still work. But, with updates to the PyXLL add-in<\/p>\n","protected":false},"author":1,"featured_media":1791,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":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":"","jetpack_post_was_ever_published":false},"categories":[11,2,504,272],"tags":[],"class_list":["post-1764","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-matplotlib","category-pyxll","category-user-interface","category-visualization"],"acf":[],"jetpack_featured_media_url":"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2024\/05\/Animation3.gif","jetpack_shortlink":"https:\/\/wp.me\/p7l3LP-ss","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/1764","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=1764"}],"version-history":[{"count":1,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/1764\/revisions"}],"predecessor-version":[{"id":1805,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/1764\/revisions\/1805"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/media\/1791"}],"wp:attachment":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/media?parent=1764"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/categories?post=1764"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/tags?post=1764"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}