To plot a bokeh figure in Excel you first create the figure in exactly the same way you would
in any Python script using bokeh, 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. This is done using Selenium and so that must be installed before Bokeh can be used with PyXLL.
The easiest way to install Selenium is to use Anaconda and install it using either of the following commands:
conda install selenium geckodriver firefox -c conda-forge
or
conda install selenium python-chromedriver-binary -c conda-forge
If you are not using Anaconda you can use pip install selenium
but you will also need to install
a suitable web browser backend. See https://pypi.org/project/selenium/ for
additional details about how to install Selenium.
Note
If you have any problems with exporting plots as SVG images you can tell PyXLL
to use the PNG format instead by passing allow_svg=False
to plot
.
The code below shows an Excel worksheet function that generates a bokeh figure and displays it in Excel.
# Download the bokeh sample data first
import bokeh
bokeh.sampledata.download()
from math import pi
import pandas as pd
from bokeh.plotting import figure, output_file, show
from bokeh.sampledata.stocks import MSFT
@xl_func
def bokeh_plot():
# Get some sample data to plot
df = pd.DataFrame(MSFT)[:50]
df["date"] = pd.to_datetime(df["date"])
# Select dates based on open <> close
inc = df.close > df.open
dec = df.open > df.close
w = 12*60*60*1000 # half day in ms
# Set up the figure
p = figure(x_axis_type="datetime", plot_width=1000, title="MSFT Candlestick")
p.xaxis.major_label_orientation = pi/4
p.grid.grid_line_alpha = 0.3
# Plot lines for high/low and vbars for open/close
p.segment(df.date, df.high, df.date, df.low, color="black")
p.vbar(df.date[inc], w, df.open[inc], df.close[inc], fill_color="#D5E1DD", line_color="black")
p.vbar(df.date[dec], w, df.open[dec], df.close[dec], fill_color="#F2583E", line_color="black")
# Show the plot in Excel using pyxll.plot
plot(p)
When this function is run in Excel the plot is shown just below the calling cell. The first time you export an image from bokeh it can take a few seconds.
Warning
When exporting a figure to an image bokeh launches a Selenium subprocess to do the export.
If you have anti-virus software installed it may warn you about this subprocess being launched.
Note
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.