{"id":879,"date":"2019-02-11T19:29:50","date_gmt":"2019-02-11T19:29:50","guid":{"rendered":"https:\/\/www.pyxll.com\/blog\/?p=879"},"modified":"2023-01-05T10:06:31","modified_gmt":"2023-01-05T10:06:31","slug":"interactive-charts-in-excel-with-highcharts","status":"publish","type":"post","link":"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/","title":{"rendered":"Interactive Charts in Excel with Highcharts"},"content":{"rendered":"\n<p>Good charts make a spreadsheet. Excel has its own charts and pivot charts, but have you ever wished you could have properly <em>&#8220;fancy&#8221;<\/em> charts in your spreadsheet like you see on modern websites?<\/p>\n\n\n\n<p>Highcharts is a professional javascript charting package for creating interactive charts for websites. This blog shows how you can embed these same charts into Excel. We&#8217;ll cover plotting data both from an Excel spreadsheet and from external sources.<\/p>\n\n\n\n<p><strong>Note:&nbsp;<\/strong>Highcharts is a commercial product and requires a license for non-commercial use. See <a href=\"https:\/\/shop.highcharts.com\/\">https:\/\/shop.highcharts.com\/<\/a> for licensing information.<\/p>\n\n\n\n<p>All the code related to this blog post can be found in the highcharts folder in the <a href=\"https:\/\/github.com\/pyxll\/pyxll-examples\">PyXLL examples github repo<\/a>.<\/p>\n\n\n\n<p><strong>Related:<\/strong> <a href=\"https:\/\/www.pyxll.com\/blog\/plotting-in-excel-with-pyxll-and-matplotlib\/\">Plotting in Excel with Python and Matplotlib<\/a>&nbsp;and <a href=\"https:\/\/www.pyxll.com\/blog\/plotting-in-excel-with-pyxll-and-matplotlib-part-2\/\">Plotting with Matplotlib Part 2<\/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-69fea8700083d\" 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-69fea8700083d\"  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\/interactive-charts-in-excel-with-highcharts\/#A_Web_Browser_in_Excel\" >A Web Browser in Excel<\/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\/interactive-charts-in-excel-with-highcharts\/#Generating_the_HTML_for_a_Chart\" >Generating the HTML for a Chart<\/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\/interactive-charts-in-excel-with-highcharts\/#Adding_the_HTML_Chart_to_Excel\" >Adding the HTML Chart to Excel<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/#Problem_1_Trust_Settings\" >Problem 1: Trust Settings<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/#Problem_2_Browser_Compatibility\" >Problem 2: Browser Compatibility<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/#Highcharts_Working_in_Excel\" >Highcharts Working in Excel<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/#Refactoring_for_Re-use\" >Refactoring for Re-use<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/#1_Refactor_the_plotting_function_for_re-use\" >1. Refactor the plotting function for re-use<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/#2_Use_a_named_control_instead_of_%E2%80%9CWebBrowser1%E2%80%9D\" >2. Use a named control instead of &#8220;WebBrowser1&#8221;<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/#3_Get_the_sheet_the_function_is_called_from\" >3. Get the sheet the function is called from<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/#4_Resize_the_chart_to_fit_the_web_browser\" >4. Resize the chart to fit the web browser<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/#5_Use_a_temporary_HTML_file\" >5. Use a temporary HTML file<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/#Putting_it_all_together_The_complete_%E2%80%9Chc_plot%E2%80%9D_function\" >Putting it all together: The complete &#8220;hc_plot&#8221; function<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/#Handling_Larger_Data_Sets\" >Handling Larger Data Sets<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"A_Web_Browser_in_Excel\"><\/span>A Web Browser in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Since Highcharts is a javascript library, we&#8217;ll need a web browser capable of rendering the charts in Excel. Fortunately Excel already has exactly that!<\/p>\n\n\n\n<figure class=\"wp-block-image is-resized\"><img decoding=\"async\" data-attachment-id=\"884\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/image-3\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-2.png\" data-orig-size=\"847,542\" 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=\"image\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-2.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-2.png\" alt=\"Add a control to Excel\" class=\"wp-image-884\" width=\"635\" height=\"407\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-2.png 847w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-2-300x192.png 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-2-768x491.png 768w\" sizes=\"(max-width: 635px) 100vw, 635px\" \/><\/figure>\n\n\n\n<p>Under the &#8220;Developer&#8221; tab, go to &#8220;Insert&#8221; and select &#8220;More Controls&#8221;. This will bring up a list of other controls. Find the one called &#8220;Microsoft Web Browser&#8221;. That&#8217;s the control we&#8217;ll use to host our interactive chart.<\/p>\n\n\n\n<figure class=\"wp-block-image is-resized\"><img decoding=\"async\" data-attachment-id=\"885\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/image-4\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-3.png\" data-orig-size=\"542,492\" 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=\"Web Browser Control\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-3.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-3.png\" alt=\"Web Browser Control in Excel\" class=\"wp-image-885\" width=\"407\" height=\"369\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-3.png 542w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-3-300x272.png 300w\" sizes=\"(max-width: 407px) 100vw, 407px\" \/><\/figure>\n\n\n\n<p>When you try and add the Web Browser control, if you&#8217;re using Excel 2013 or newer you may run into a problem. This is because by default the Web Browser control has been disabled in Excel. However, it can easily be re-enabled with a registry setting.<\/p>\n\n\n\n<p>You can read about why the Web Browser control is disabled by default here https<a href=\"https:\/\/support.microsoft.com\/en-us\/help\/4058123\/security-settings-for-com-objects-in-office\">:\/\/support.microsoft.com\/en-us\/help\/4058123\/security-settings-for-com-objects-in-office<\/a>.&nbsp;That page also covers in more detail how you can enable the control.<\/p>\n\n\n\n<p>To enable the Web Browser control, first of all open &#8220;regedit&#8221; by clicking the start menu and typing &#8220;regedit&#8221;. Find the following key in the registry:<\/p>\n\n\n\n<p>For&nbsp;Office 2013&nbsp;and&nbsp;Office 2010:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>For 64 bit Office on 64 bit Windows (or 32 bit Office on 32 bit Windows).<br><strong>HKEY_LOCAL_MACHINE\\Software\\Microsoft\\Office\\Common\\COM Compatibility\\{8856F961-340A-11D0-A96B-00C04FD705A2}<\/strong><\/li><li>For 32 bit Office on 64 bit Windows.<br><strong>HKEY_LOCAL_MACHINE\\Software\\Wow6432Node\\Microsoft\\Office\\Common\\COM Compatibility\\<\/strong> <br><strong>{8856F961-340A-11D0-A96B-00C04FD705A2}\ufeff<\/strong> <\/li><\/ol>\n\n\n\n<p>For&nbsp;Office 2016:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>For 64 bit Office on 64 bit Windows (or 32 bit Office on 32 bit Windows).<br><strong>HKEY_LOCAL_MACHINE\\Software\\Microsoft\\Office\\16.0\\Common\\COM Compatibility\\<\/strong> <br><strong>{8856F961-340A-11D0-A96B-00C04FD705A2}\ufeff<\/strong>  <\/li><li>For 32 bit Office on 64 bit Windows.<br><strong>HKEY_LOCAL_MACHINE\\Software\\Wow6432Node\\Microsoft\\Office\\16.0\\Common\\COM Compatibility<\/strong> <br><strong>{8856F961-340A-11D0-A96B-00C04FD705A2}\ufeff<\/strong>  <strong>}<\/strong><\/li><\/ol>\n\n\n\n<p>If the Web Browser control has been disabled,  you will see a subkey called&nbsp;<strong>Compatibility Flags<\/strong>&nbsp;with its value set to&nbsp;<strong>0x00000400<\/strong>.&nbsp;&nbsp;Either delete this key or set its value to <strong>0<\/strong> to enable the control.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Generating_the_HTML_for_a_Chart\"><\/span>Generating the HTML for a Chart<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>There are a few different Python packages that will generate Highcharts HTML. For this post we&#8217;ll use python-highcharts (<a href=\"https:\/\/github.com\/kyper-data\/python-highcharts\">https:\/\/github.com\/kyper-data\/python-highcharts<\/a>) which can be installed using pip:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>pip install python-highcharts<\/code><\/pre>\n\n\n\n<p>Using PyXLL, <a href=\"https:\/\/support.pyxll.com\/hc\/en-gb\/articles\/360036821973-How-can-I-call-a-Python-Function-from-Excel-\" target=\"_blank\" rel=\"noreferrer noopener\" label=\" (opens in a new tab)\">we can call a Python function from Excel<\/a>. The following function takes a 2d array of data and constructs a Highchart object. It then writes that out to an html file.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nfrom pyxll import xl_func\nfrom highcharts import Highchart\n\n@xl_func\ndef hc_basic_line(data, filename):\n    H = Highchart()\n\n    # transform the data from a list of rows to a list of columns\n    data_t = list(zip(*data))\n\n    # add the data to the chart\n    for i, series in enumerate(data_t):\n        H.add_data_set(series,\n                       series_type=&#039;line&#039;,\n                       name=&quot;series_%d&quot; % i)\n\n    # write out the html\n    with open(filename, &quot;wt&quot;) as fh:\n        fh.write(H.htmlcontent)\n\n    return True\n\n<\/pre><\/div>\n\n\n<p>By adding this code to a new module and adding that module to our <em>pyxll.cfg<\/em> file, the function can be called from Excel. It doesn&#8217;t actually add the chart to Excel yet, it only writes out an html file.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" width=\"994\" height=\"646\" data-attachment-id=\"890\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/image-5\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-4.png\" data-orig-size=\"994,646\" 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=\"image\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-4.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-4.png\" alt=\"Generate HTML for Highcharts from Excel\" class=\"wp-image-890\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-4.png 994w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-4-300x195.png 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-4-768x499.png 768w\" sizes=\"(max-width: 994px) 100vw, 994px\" \/><\/figure>\n\n\n\n<p>Opening the resulting html file in a browser reveals that it has created a plot of the data from the sheet, as we expected.<\/p>\n\n\n\n<figure class=\"wp-block-image is-resized\"><img decoding=\"async\" data-attachment-id=\"893\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/image-6\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-5.png\" data-orig-size=\"774,577\" 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=\"image\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-5.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-5.png\" alt=\"\" class=\"wp-image-893\" width=\"387\" height=\"289\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-5.png 774w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-5-300x224.png 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-5-768x573.png 768w\" sizes=\"(max-width: 387px) 100vw, 387px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Adding_the_HTML_Chart_to_Excel\"><\/span>Adding the HTML Chart to Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Now we&#8217;ve got everything we need to add an interactive Highchart to Excel. Earlier we saw how to add a Web Browser control to an Excel sheet, and in the previous section we generated an HTML chart. The next step is to tie the two together so we can have our chart embedded in the Excel sheet.<\/p>\n\n\n\n<p>If you haven&#8217;t already, add a Web Browser control to your sheet. By default, it will be named &#8220;WebBrowser1&#8221;. We&#8217;ll stick with that name for now, but later we&#8217;ll see how to change it.<\/p>\n\n\n\n<p>In our function that generates the HTML file, we can find the Web Browser object and tell it to navigate to our generated file as follows.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; highlight: [19,20,21,22]; title: ; notranslate\" title=\"\">\nfrom pyxll import xl_func, xl_app\nfrom highcharts import Highchart\n\n@xl_func\ndef hc_basic_line(data, filename):\n    H = Highchart()\n\n    # transform the data from a list of rows to a list of columns\n    data_t = list(zip(*data))\n\n    # add the data to the chart\n    for i, series in enumerate(data_t):\n        H.add_data_set(series, series_type=&#039;line&#039;, name=&quot;series_%d&quot; % i)\n\n    # write out the html\n    with open(filename, &quot;wt&quot;) as fh:\n        fh.write(H.htmlcontent)\n\n    xl = xl_app()\n    control = xl.ActiveSheet.OLEObjects(&quot;WebBrowser1&quot;)\n    browser = control.Object\n    browser.Navigate(&quot;file:\/\/&quot; + filename)\n\n    return True\n\n<\/pre><\/div>\n\n\n<p>Above, we&#8217;re using <a href=\"https:\/\/www.pyxll.com\/docs\/api\/utils.html#pyxll.xl_app\">PyXLL&#8217;s &#8220;xl_app&#8221; function<\/a> to get the &#8220;Excel.Application&#8221; object. This is Excel&#8217;s COM interface, which has exactly the same API as Excel&#8217;s VBA object model.<\/p>\n\n\n\n<p>When you run the above code, you might find that the Web Browser control just says &#8220;Loading&#8230;&#8221;. There are a couple of reasons for this that we will have to fix.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Problem_1_Trust_Settings\"><\/span>Problem 1: Trust Settings<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Content might be restricted by your trust and privacy settings. Toggle out of &#8220;Developer Mode&#8221; in the &#8220;Developer&#8221; menu in Excel. If you see a yellow bar at the top of the browser saying &#8220;To help protect your security&#8230;&#8221; then the javascript has been blocked from being run. Click on the yellow bar and select &#8220;Allow Blocked Content&#8221;.<\/p>\n\n\n\n<p>To avoid this warning, you would have to host the html file on a webserver. This could be running on <em>http:\/\/localhost<\/em> with a Python webserver running in the Excel process, using PyXLL.<\/p>\n\n\n\n<p>You might also find that ActiveX controls are blocked from being used in Excel. If that&#8217;s the case, you&#8217;ll need to enable them in Excel. You should be careful when doing this that you are not violating your company&#8217;s IT policy as it could potentially be a security vulnerability. To change your ActiveX settings in the Excel Trust Center:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Go to <strong>File&nbsp;&gt;&nbsp;Options.<\/strong><\/li><li>Click <strong>Trust Center&nbsp;&gt;&nbsp;Trust Center Settings&nbsp;&gt;&nbsp;ActiveX Settings.<\/strong> <\/li><\/ol>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Problem_2_Browser_Compatibility\"><\/span>Problem 2: Browser Compatibility<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>When you get the javascript to run after trusting the content, you may then get a &#8220;Script Error&#8221;.<\/p>\n\n\n\n<figure class=\"wp-block-image is-resized\"><img decoding=\"async\" data-attachment-id=\"897\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/image-7\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-6.png\" data-orig-size=\"672,439\" 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=\"image\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-6.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-6.png\" alt=\"\" class=\"wp-image-897\" width=\"336\" height=\"220\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-6.png 672w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-6-300x196.png 300w\" sizes=\"(max-width: 336px) 100vw, 336px\" \/><\/figure>\n\n\n\n<p>The reason you might see this error is that by default, the Web Browser control runs in Internet Explorer 7 compatibility mode. Fortunately, this is easy enough to work around. We just need to add a <em>meta<\/em> tag to the html document to tell the control to use the latest version of IE.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&lt;meta http-equiv=\"X-UA-Compatible\" content=\"IE=edge\" \/><\/code><\/pre>\n\n\n\n<p>The easiest way I found to add this meta tag to the Highcharts html document was to parse it using <a href=\"https:\/\/pypi.org\/project\/beautifulsoup4\/\">BeautifulSoup<\/a> and add the tag that way. You can install BeautifySoup using pip.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>pip install beautifulsoup4<\/code><\/pre>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; highlight: [16,17,18,19,20,21,25]; title: ; notranslate\" title=\"\">\nfrom pyxll import xl_func, xl_app\nfrom highcharts import Highchart\nfrom bs4 import BeautifulSoup\n\n@xl_func\ndef hc_basic_line(data, filename):\n    H = Highchart()\n\n    # transform the data from a list of rows to a list of columns\n    data_t = list(zip(*data))\n\n    # add the data to the chart\n    for i, series in enumerate(data_t):\n        H.add_data_set(series, series_type=&#039;line&#039;, name=&quot;series_%d&quot; % i)\n\n    # get the html and add the &#039;X-UA-Compatible&#039; meta-tag\n    soup = BeautifulSoup(H.htmlcontent)\n    metatag = soup.new_tag(&quot;meta&quot;)\n    metatag.attrs&#x5B;&quot;http-equiv&quot;] = &quot;X-UA-Compatible&quot;\n    metatag.attrs&#x5B;&#039;content&#039;] = &quot;IE=edge&quot;\n    soup.head.insert(0, metatag)\n\n    # write out the html\n    with open(filename, &quot;wt&quot;) as fh:\n        fh.write(soup.prettify())\n\n    xl = xl_app()\n    control = xl.ActiveSheet.OLEObjects(&quot;WebBrowser1&quot;)\n    browser = control.Object\n    browser.Navigate(&quot;file:\/\/&quot; + filename)\n\n    return True\n<\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Highcharts_Working_in_Excel\"><\/span>Highcharts Working in Excel<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<figure class=\"wp-block-image is-resized\"><img decoding=\"async\" data-attachment-id=\"898\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/image-8\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-7.png\" data-orig-size=\"992,1036\" 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=\"image\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-7-981x1024.png\" src=\"https:\/\/i1.wp.com\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-7.png?fit=700%2C731&amp;ssl=1\" alt=\"\" class=\"wp-image-898\" width=\"525\" height=\"548\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-7.png 992w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-7-287x300.png 287w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-7-768x802.png 768w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-7-981x1024.png 981w\" sizes=\"(max-width: 525px) 100vw, 525px\" \/><\/figure>\n\n\n\n<p>After addressing the problems of the trust settings and the browser compatibility, we now have a chart rendered using Highcharts in Excel!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Refactoring_for_Re-use\"><\/span>Refactoring for Re-use<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>So far we&#8217;ve demonstrated that it is possible to embed Highcharts in Excel using PyXLL and the Web Browser control. There are a few problems with the current code, however.<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>We can&#8217;t re-use this code for other functions to plot charts.<\/li><li>It only works if there&#8217;s a Web Browser control called &#8220;WebBrowser1&#8221;.<\/li><li>It expects the sheet the function is on to be the current active sheet.<\/li><li>The chart isn&#8217;t always sized correctly.<\/li><li>The html files aren&#8217;t cleaned up.<\/li><\/ol>\n\n\n\n<p>Let&#8217;s pull out the part of the function that handles writing out the html file and updating the Web Browser control into a separate function and address these problems.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"1_Refactor_the_plotting_function_for_re-use\"><\/span>1. Refactor the plotting function for re-use<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>We&#8217;ll start by splitting out just the part of the code that generates the html and displays it in Excel. Then we can focus on that new function to address the remaining improvements.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nfrom pyxll import xl_func, xl_app\nfrom highcharts import Highchart\nfrom bs4 import BeautifulSoup\n\n@xl_func\ndef hc_basic_line(data, filename):\n    H = Highchart()\n\n    # transform the data from a list of rows to a list of columns\n    data_t = list(zip(*data))\n\n    # add the data to the chart\n    for i, series in enumerate(data_t):\n        H.add_data_set(series, series_type=&#039;line&#039;, name=&quot;series_%d&quot; % i)\n\n    return hc_plot(H, filename)\n\n\ndef hc_plot(chart, filename):\n    # get the html and add the &#039;X-UA-Compatible&#039; meta-tag\n    soup = BeautifulSoup(chart.htmlcontent)\n    metatag = soup.new_tag(&quot;meta&quot;)\n    metatag.attrs&#x5B;&quot;http-equiv&quot;] = &quot;X-UA-Compatible&quot;\n    metatag.attrs&#x5B;&#039;content&#039;] = &quot;IE=edge&quot;\n    soup.head.insert(0, metatag)\n\n    # write out the html\n    with open(filename, &quot;wt&quot;) as fh:\n        fh.write(soup.prettify())\n\n    xl = xl_app()\n    control = xl.ActiveSheet.OLEObjects(&quot;WebBrowser1&quot;)\n    browser = control.Object\n    browser.Navigate(&quot;file:\/\/&quot; + filename)\n\n    return True\n<\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"2_Use_a_named_control_instead_of_%E2%80%9CWebBrowser1%E2%80%9D\"><\/span>2. Use a named control instead of &#8220;WebBrowser1&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Rather than assume the Web Browser control is always called &#8220;WebBrowser1&#8221;, we add the control name as a parameter. Also, rather than fail if the named control can&#8217;t be found, create a new browser control. This helps when adding new charts so we don&#8217;t have to set up a browser control manually each time.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; highlight: [13,14,15,16,17,18,19,20,21,22,23,24,25,26,27]; title: ; notranslate\" title=\"\">\ndef hc_plot(chart, filename, control_name=&quot;WebBrowser1&quot;):\n    # get the html and add the &#039;X-UA-Compatible&#039; meta-tag\n    soup = BeautifulSoup(chart.htmlcontent)\n    metatag = soup.new_tag(&quot;meta&quot;)\n    metatag.attrs&#x5B;&quot;http-equiv&quot;] = &quot;X-UA-Compatible&quot;\n    metatag.attrs&#x5B;&#039;content&#039;] = &quot;IE=edge&quot;\n    soup.head.insert(0, metatag)\n\n    # write out the html\n    with open(filename, &quot;wt&quot;) as fh:\n        fh.write(soup.prettify())\n\n    # find the existing webbrowser control, or create a new one\n    xl = xl_app()\n    sheet = xl.ActiveSheet\n\n    try:\n        control = sheet.OLEObjects(control_name&#x5B;:31])\n        browser = control.Object\n    except:\n        control = sheet.OLEObjects().Add(ClassType=&quot;Shell.Explorer.2&quot;,\n                                         Left=147,\n                                         Top=60.75,\n                                         Width=400,\n                                         Height=400)\n        control.Name = control_name&#x5B;:31]\n        browser = control.Object\n\n    browser.Navigate(&quot;file:\/\/&quot; + filename)\n\n    return True\n<\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"3_Get_the_sheet_the_function_is_called_from\"><\/span>3. Get the sheet the function is called from<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>When recalculating a workbook containing multiple sheets, the active sheet isn&#8217;t always the right one to use. Instead the <a href=\"https:\/\/www.pyxll.com\/docs\/api\/capifunctions.html#xlfcaller\">PyXLL function &#8220;xlfCaller&#8221;<\/a> can be used to get the sheet that&#8217;s calling the function. Assuming the function is on the same sheet we want the chart, this will work better than always using the active sheet.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nfrom pyxll import xlfCaller\nimport re\n\ndef get_calling_sheet():\n    caller = xlfCaller()\n    sheet_name = caller.sheet_name\n\n    # split into workbook and sheet name\n    match = re.match(&quot;^\\&#x5B;(.+?)\\](.*)$&quot;, sheet_name.strip(&quot;&#039;\\&quot;&quot;))\n    if not match:\n        raise Exception(&quot;Unexpected sheet name &#039;%s&#039;&quot; % sheet_name)\n    workbook, sheet = match.groups()\n\n    # get the Worksheet object\n    xl = xl_app()\n    workbook = xl.Workbooks(workbook)\n    return workbook.Sheets(sheet)\n<\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"4_Resize_the_chart_to_fit_the_web_browser\"><\/span>4. Resize the chart to fit the web browser<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>The highcharts charts will automatically resize to match the size of the Web Browser control. However, the default aspect ratio used won&#8217;t match the aspect ratio of the browser control. That can be fixed by setting the chart.height or chart.width option to a percentage string. Below, the height or width percentage is calculated from the control size.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; highlight: [16,17,18,19,20,21,22,23,24]; title: ; notranslate\" title=\"\">\ndef hc_plot(chart, filename, control_name=&quot;WebBrowser1&quot;):\n    # find the existing webbrowser control, or create a new one\n    sheet = get_calling_sheet()\n    try:\n        control = sheet.OLEObjects(control_name&#x5B;:31])\n        browser = control.Object\n    except:\n        control = sheet.OLEObjects().Add(ClassType=&quot;Shell.Explorer.2&quot;,\n                                         Left=147,\n                                         Top=60.75,\n                                         Width=400,\n                                         Height=400)\n        control.Name = control_name&#x5B;:31]\n        browser = control.Object\n\n    # set the chart aspect ratio to match the browser\n    if control.Width &gt; control.Height:\n        chart.set_options(&quot;chart&quot;, {\n             &quot;height&quot;: &quot;%d%%&quot; % (100. * control.Height \/ control.Width)\n         })\n    else:\n        chart.set_options(&quot;chart&quot;, {\n             &quot;width&quot;: &quot;%d%%&quot; % (100. * control.Width \/ control.Height)\n         })\n\n    # get the html and add the &#039;X-UA-Compatible&#039; meta-tag\n    soup = BeautifulSoup(chart.htmlcontent)\n    metatag = soup.new_tag(&quot;meta&quot;)\n    metatag.attrs&#x5B;&quot;http-equiv&quot;] = &quot;X-UA-Compatible&quot;\n    metatag.attrs&#x5B;&#039;content&#039;] = &quot;IE=edge&quot;\n    soup.head.insert(0, metatag)\n\n    # write out the html\n    with open(filename, &quot;wt&quot;) as fh:\n        fh.write(soup.prettify())\n\n    browser.Navigate(&quot;file:\/\/&quot; + filename)\n\n    return True\n<\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"5_Use_a_temporary_HTML_file\"><\/span>5. Use a temporary HTML file<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Expecting the user to pass in a filename to plot a chart in Excel is counter-intuitive. The fact that a file is used is just an internal implementation detail. This detail could be hidden from the user so all they see is the chart in Excel without having to know about any files.<\/p>\n\n\n\n<p>The Python <em>tempfile<\/em> package is a convenient way to create temporary file. We need it to be named so that the web browser can load it. It can&#8217;t be deleted immediately as it takes a second for the web browser to load the file. Instead we use the <em>timer<\/em> module (part of the <a href=\"https:\/\/pypi.org\/project\/pypiwin32\/\">pywin32 <\/a>package) to delete the file after a few seconds.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nimport tempfile\nimport timer\nimport os\n\ndef render_html(browser, html):\n    # Create a temporary file to write to\n    fh = tempfile.NamedTemporaryFile(&quot;wt&quot;, suffix=&quot;.html&quot;, delete=False)\n    filename = fh.name\n\n    # Clean up the file after 10 seconds to give the browser time to load\n    def on_timer(timer_id, time):\n        timer.kill_timer(timer_id)\n        os.unlink(filename)\n    timer.set_timer(10000, on_timer)\n\n    fh.write(html)\n    fh.close()\n\n    # Navigate to the temporary file\n    browser.Navigate(&quot;file:\/\/%s&quot; % filename)\n<\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Putting_it_all_together_The_complete_%E2%80%9Chc_plot%E2%80%9D_function\"><\/span>Putting it all together: The complete &#8220;hc_plot&#8221; function<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>This <em>hc_plot<\/em> function can be used from any <a href=\"https:\/\/www.pyxll.com\/docs\/userguide\/udfs.html\">PyXLL <\/a><em><a href=\"https:\/\/www.pyxll.com\/docs\/userguide\/udfs.html\">@xl_func<\/a><\/em><a href=\"https:\/\/www.pyxll.com\/docs\/userguide\/udfs.html\"> function<\/a> to create or update a highcharts plot, embedded in Excel.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nfrom pyxll import xl_app, xlfCaller\nfrom bs4 import BeautifulSoup\nimport tempfile\nimport timer\nimport os\nimport re\n\n\ndef hc_plot(chart, control_name):\n    # find the existing webbrowser control, or create a new one\n    sheet = get_calling_sheet()\n    try:\n        control = sheet.OLEObjects(control_name&#x5B;:31])\n        browser = control.Object\n    except:\n        control = sheet.OLEObjects().Add(ClassType=&quot;Shell.Explorer.2&quot;,\n                                         Left=147,\n                                         Top=60.75,\n                                         Width=400,\n                                         Height=400)\n        control.Name = control_name&#x5B;:31]\n        browser = control.Object\n\n    # set the chart aspect ratio to match the browser\n    if control.Width &gt; control.Height:\n        chart.set_options(&quot;chart&quot;, {\n             &quot;height&quot;: &quot;%d%%&quot; % (100. * control.Height \/ control.Width)\n         })\n    else:\n        chart.set_options(&quot;chart&quot;, {\n             &quot;width&quot;: &quot;%d%%&quot; % (100. * control.Width \/ control.Height)\n         })\n\n    # get the html and add the &#039;X-UA-Compatible&#039; meta-tag\n    soup = BeautifulSoup(chart.htmlcontent)\n    metatag = soup.new_tag(&quot;meta&quot;)\n    metatag.attrs&#x5B;&quot;http-equiv&quot;] = &quot;X-UA-Compatible&quot;\n    metatag.attrs&#x5B;&#039;content&#039;] = &quot;IE=edge&quot;\n    soup.head.insert(0, metatag)\n\n    # write out the html\n    render_html(browser, soup.prettify())\n\n\ndef get_calling_sheet():\n    # get the calling sheet\n    caller = xlfCaller()\n    sheet_name = caller.sheet_name\n\n    # split into workbook and sheet name\n    match = re.match(&quot;^\\&#x5B;(.+?)\\](.*)$&quot;, sheet_name.strip(&quot;&#039;\\&quot;&quot;))\n    if not match:\n        raise Exception(&quot;Unexpected sheet name &#039;%s&#039;&quot; % sheet_name)\n    workbook, sheet = match.groups()\n\n    # get the Worksheet object\n    xl = xl_app()\n    workbook = xl.Workbooks(workbook)\n    return workbook.Sheets(sheet)\n\n\ndef render_html(browser, html):\n    # Create a temporary file to write to\n    fh = tempfile.NamedTemporaryFile(&quot;wt&quot;, suffix=&quot;.html&quot;, delete=False)\n    filename = fh.name\n\n    # Clean up the file after 10 seconds to give the browser time to load\n    def on_timer(timer_id, time):\n        timer.kill_timer(timer_id)\n        os.unlink(filename)\n    timer.set_timer(10000, on_timer)\n\n    fh.write(html)\n    fh.close()\n\n    # Navigate to the temporary file\n    browser.Navigate(&quot;file:\/\/%s&quot; % filename)\n<\/pre><\/div>\n\n\n<p>This <em>hc_plot<\/em> function can now be called from our previous <em>hc_basic_line<\/em> function.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; highlight: [16,17]; title: ; notranslate\" title=\"\">\n@xl_func\ndef hc_basic_line(data, title):\n    H = Highchart()\n\n    H.set_options(&quot;title&quot;, {\n        &quot;text&quot;: title\n    })\n\n    # transform the data from a list of rows to a list of columns\n    data_t = list(zip(*data))\n\n    # add the data to the chart\n    for i, series in enumerate(data_t):\n        H.add_data_set(series, series_type=&#039;line&#039;, name=&quot;series_%d&quot; % i)\n\n    # plot the chart in Excel, using the chart title as the control name\n    hc_plot(H, title)\n\n    return True\n<\/pre><\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Handling_Larger_Data_Sets\"><\/span>Handling Larger Data Sets<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Highcharts are great for interactive data visualisation that is hard to match with Excel&#8217;s charts. They are capable of rendering large data sets, and sometimes it&#8217;s not convenient or practical to pull so much data into an Excel sheet.<\/p>\n\n\n\n<p>PyXLL can pass Python objects around using object handles. This can be used to load large arrays into Python and pass them between functions <em>without<\/em> having to unpack them in Excel. You can read more about <a href=\"https:\/\/www.pyxll.com\/docs\/userguide\/udfs\/argtypes.html#using-python-objects-directly\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\" (opens in a new tab)\">cached Python objects in the PyXLL user guide<\/a>.<\/p>\n\n\n\n<p>For really large data sets, you may even want to serve the data via a web server directly to your highcharts instead of loading the data in Python.<\/p>\n\n\n\n<p>The following function loads a sample data set from highcharts.com.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: python; title: ; notranslate\" title=\"\">\nfrom pyxll import xl_func\nfrom highcharts.highstock.highstock_helper import jsonp_loader\n\n@xl_func(&quot;string: object&quot;)\ndef hc_load_sample_data(name):\n    url = &quot;https:\/\/www.highcharts.com\/samples\/data\/jsonp.php?filename=%s.json&amp;callback=?&quot; % name\n    return jsonp_loader(url)\n<\/pre><\/div>\n\n\n<p>The returned object from this function can be passed to another plotting function to plot the data.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" width=\"1004\" height=\"802\" data-attachment-id=\"914\" data-permalink=\"https:\/\/www.pyxll.com\/blog\/interactive-charts-in-excel-with-highcharts\/image-9\/\" data-orig-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-8.png\" data-orig-size=\"1004,802\" 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=\"image\" data-image-description=\"\" data-image-caption=\"\" data-large-file=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-8.png\" src=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-8.png\" alt=\"\" class=\"wp-image-914\" srcset=\"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-8.png 1004w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-8-300x240.png 300w, https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image-8-768x613.png 768w\" sizes=\"(max-width: 1004px) 100vw, 1004px\" \/><\/figure>\n\n\n\n<p>Here the data is loaded using <em>hc_load_sample_data<\/em> and passed to <em>hc_line_time_series<\/em>. The source code for these functions can be found in highcharts folder in the <a href=\"https:\/\/github.com\/pyxll\/pyxll-examples\">PyXLL examples github repo<\/a>.<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Integrate HTML5 charts into your Excel workbooks using PyXLL and Highcharts. Go beyond static charts with interactive plots.<\/p>\n","protected":false},"author":1,"featured_media":880,"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":[272],"tags":[],"class_list":["post-879","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-visualization"],"acf":[],"jetpack_featured_media_url":"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/02\/image.png","jetpack_shortlink":"https:\/\/wp.me\/p7l3LP-eb","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/879","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=879"}],"version-history":[{"count":1,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/879\/revisions"}],"predecessor-version":[{"id":1679,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/879\/revisions\/1679"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/media\/880"}],"wp:attachment":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/media?parent=879"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/categories?post=879"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/tags?post=879"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}