CODEWITHSUNDEEP
pythonexcelvbaplotlyplotly-python
dusio
dusio

Reputation: 510

Plotly: How to embed a fully interactive Plotly figure in Excel?

I'm trying to embed an interactive plotly (or bokeh) plot into excel.

To do this I've tried the following three things:

  1. embed a Microsoft Web Browser UserForm into excel, following:

How do I embed a browser in an Excel VBA form?

This works and enables both online and offline html to be loaded

  1. creating a plotly html

'''

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')
  1. repointing the webbrowser object in excel using .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:

enter image description here

The same HTML can be opened in a browser.

Is there any way to show interactive plots in excel?

Upvotes: 11

Views: 7724

Answers (4)

Vignesh
Vignesh

Reputation: 1623

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

Output

Macro used:

Sub Button4_Click()
ActiveSheet.WebBrowser1.Navigate "file:///C:/Users/vignesh.rajendran/Desktop/test5.html"
End Sub

Upvotes: 1

vestland
vestland

Reputation: 61134

I like your question! And I'd wish I could give you a better answer, but It seems that the only way you can achieve anything remotely resembling an interactive plotly plot would be to use pyxll and follow the steps outlined under Charts and plotting / Plotly including a plot function like this:

from pyxll import xl_func, plot
import plotly.express as px

@xl_func
def plotly_plot():
    # Get some sample data from plotly.express
    df = px.data.gapminder()

    # Create a scatter plot figure
    fig = px.scatter(df.query("year==2007"),
                     x="gdpPercap", y="lifeExp",
                     size="pop", color="continent",
                     log_x=True, size_max=60)

    # Show the figure in Excel using pyxll.plot
    plot(fig)

This will produce the following plot:

enter image description here

Alas, this will not be a fully interactive plotly plot like we all know and love, since it's also stated on the very same page that:

The plot that you see in Excel is exported as an image so any interactive elements will not be available. To make a semi-interactive plot you can add arguments to your function to control how the plot is done and when those arguments are changed the plot will be redrawn.

But as far as I know this is as close as you'll get to achieving what you're seeking in your question. If you're not limited to Excel, but somehow limited to the realm of Microsoft, one of the commenters mentioned that you can unleash a fully interactive plotly plot in PowerBI. If that is an option, you should take a closer look at Is it possible to use R Plotly library in R Script Visual of Power BI?. This approach uses R though...

Upvotes: 1

thshea
thshea

Reputation: 1087

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:

  • Only being able to use data entries and buttons, instead of the default plotly interactive features. Many could be replicated, but it would be more work.
  • It will only work on a computer you can set up your python script on (however, it looks like xlwings pro allows you to store your program inside the file)
  • It seems you will need to pass plotly graphs by saving and then adding the figure, because directly passing them requires xlwings pro. Direct passing without pro is possible with MatPlotLib.

Plotly guide to making interactive(ish) graphs

xlwings docs on MatPlotLib and Plotly graphs

Upvotes: 1

jerlich
jerlich

Reputation: 362

Interactive plots require javascript to work. Excel, for security reasons, blocks that javascript. You can put a static image easily into excel.

The challenge of including javascript in excel has been addressed in this question: How can I use JavaScript within an Excel macro?

Upvotes: 0

Related Questions