Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 599
dusio Avatar asked Dec 13 '20 04:12

dusio


People also ask

How do you share a Plotly interactive plot?

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.

How do you Plotly in Excel?

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.

Which creates interactive plot that can be embedded in the web browser?

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.

Which is better Plotly or bokeh?

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.


2 Answers

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
like image 114
Vignesh Avatar answered Sep 21 '22 13:09

Vignesh


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

like image 30
thshea Avatar answered Sep 18 '22 13:09

thshea