I'm trying to embed an interactive plotly (or bokeh) plot into excel.
To do this I've tried the following three things:
How do I embed a browser in an Excel VBA form?
This works and enables both online and offline html to be loaded
'''
import plotly
import plotly.graph_objects as go
x = [0.1, 0.5, 1.0, 1.5, 2.0, 2.5, 3.0]
y = [i**2 for i in x]
fig = go.Figure()
fig.add_trace(go.Scatter(x=x, y=x, mode='markers', name="y=x", marker=dict(color='royalblue', size=8)))
fig.add_trace(go.Scatter(x=x, y=y, name="y=x^2", line=dict(width=3)))
plotly.offline.plot(fig, filename='C:/Users/.../pythonProject/test1.html')
.Navigate
to the local plotly.html. Banner pops up with".... restricted this file from showing active content that could access your computer"
clicking on the banner, I run into this error:
The same HTML can be opened in a browser.
Is there any way to show interactive plots in excel?
To share a plot from the Chart Studio Workspace, click 'Share' button on the left-hand side after saving the plot. The Share modal will pop-up and display a link under the 'Embed' tab. You can then copy and paste this link to your website. You have the option of embedding your plot as an HTML snippet or iframe.
To plot a Plotly figure in Excel you first create the figure in exactly the same way you would in any Python script using plotly, and then use PyXLL's plot function to show it in the Excel workbook. When the figure is exported to Excel it first has to be converted to an image.
Plotly is a web-based service by default, but you can use the library offline in Python and upload plots to Plotly's free, public server or paid, private server. From there, you can embed your plots in a web page.
Though Plotly is good for plotting graphs and visualizing data for insights, it is not good for making dashboards. To make dashboards we can use bokeh and can have very fast dashboards and interactivity. In this comparison of Bokeh vs Plotly, there is no clear winner. We have to choose a library based on our purpose.
Finally, I have managed to bring the interactive plot to excel after a discussion from Microsoft QnA and Web Browser Control & Specifying the IE Version
To insert a Microsoft webpage to excel you have to change the compatibility Flag in the registry editor
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\16.0\Common\COM Compatibility{8856F961-340A-11D0-A96B-00C04FD705A2}
Change the DWord 0 instead of 400
Now you can insert the web browser object to excel, Step by step details are here
Edit the HTML File generated from plotly manually by adding a tag for Using the X-UA-Compatible HTML Meta Tag
Originally generated HTML file from plotly looks like this
<html>
<head><meta charset="utf-8" /></head>
<body>
Modified HTML with browser compatibility
<html>
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
</head>
<body>
After this, I can able to view the interactive plot in excel also able to do the interactions same as a web browser
Macro used:
Sub Button4_Click()
ActiveSheet.WebBrowser1.Navigate "file:///C:/Users/vignesh.rajendran/Desktop/test5.html"
End Sub
As mentioned by @jerlich, Excel blocks javascript. You should try the workaround they linked if you want full interactivity.
If you want at least some degree of controllability or interactivity, try using xlwings
. With excel buttons, you can still have some communication between Excel and Python (including reading data and sending graphs).
The limitations are:
Plotly guide to making interactive(ish) graphs
xlwings docs on MatPlotLib and Plotly graphs
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With