Use Matplotlib from Excel with xlwings

Xlwings is another free and open source package allowing communication between Excel and Python.  It now incorporates ExcelPython, and is included in the Anaconda Python package, so will support my ExcelPython based spreadsheets after installation of xlwings using:
conda install xlwings

See for more details.

In this post I will look at using Matplotlib to plot graphs in Excel.  A free download of a spreadsheet with all examples and all VBA and Python code can be found at:

The first example comes from the xlwings sample files, mpl.xlsm and :


The VBA code couldn’t be simpler:

Sub Streamplot()
    RunPython ("import xlMatPlot; xlMatPlot.main()")
End Sub

The python code (that generates the graph) is also fairly straightforward:

import numpy as np
import matplotlib.pyplot as plt
import xlwings as xw
    import seaborn
except ImportError:

def get_figure(const):
    # Based on:
    Y, X = np.mgrid[-3:3:100j, -3:3:100j]
    U = -1 + const * X**2 + Y
    V = 1 - const * X - Y**2

    fig, ax = plt.subplots(figsize=(6, 4))
    strm = ax.streamplot(X, Y, U, V, color=U, linewidth=2,
    return fig

def main():
    # Create a reference to the calling Excel Workbook
    wb = xw.Workbook.caller()

    # Get the constant from Excel
    const = xw.Range('B1').value

    # Get the figure and show it in Excel
    fig = get_figure(const)
    plot = xw.Plot(fig)'MyStreamplot', sheet=1) 

The get_figure function generates a matplotlib graph, using the const value passed from the main function. The main function creates a reference to the calling Excel Workbook, then assigns the graph to the named Excel graphic object ‘MyStreamplot’. If this already exists, it will be updated to display the newly created graphic. If not, it will be created.

I have modified the xlwings code to display a range of different graphs, based on examples taken from

The VBA code has been modified to update the graph whenever there is a change to the “Samples” worksheet:


The main function in the xlMatPlot Python module now reads the required figure number from the Excel named range ‘fig_no’, and the constant (for Figure 1) from the range ‘n’.  One of 10 functions are then called, depending on the value in ‘fig_no’.

Fig_no 2 generates a plot of sine and cos functions, together with some simple formatting and addition of text:

def get_figure2():
    x =     linspace(0., 10., 200)
    y =     sin(x) 
    y2 =     cos(x)
    fig = plt.figure()   # Make a new figure
    line1=plt.plot(x, y)
    line2=plt.plot(x, y2, 'b')
    plt.setp(line1, color='r', linewidth=3.0)
    plt.setp(line2, color='b', linewidth=2.)
    xl = plt.xlabel('horizontal axis')
    yl = plt.ylabel('vertical axis')
    ttl = plt.title('sine function')
    txt = plt.text(0,1.3,'here is some text')
    ann = plt.annotate('a point on curve',xy=(4.7,-1),xytext=(3,-1.3), arrowprops=dict(arrowstyle='->'))

    return fig 


Fig_no 3 plots four functions, assigning a different line style to each:

def get_figure3():
    fig = plt.figure()
    x = arange(0.,10,0.1)
    a = cos(x)
    b = sin(x)
    c = exp(x/10)
    d = exp(-x/10)
    la = plt.plot(x,a,'b-',label='cosine')
    lb = plt.plot(x,b,'r--',label='sine')
    lc = plt.plot(x,c,'gx',label='exp(+x)')
    ld = plt.plot(x,d,'y-', linewidth = 5,label='exp(-x)')
    ll = plt.legend(loc='upper left')
    lx = plt.xlabel('xaxis')
    ly = plt.ylabel('yaxis')
    return fig 


In Fig_no 4 there are four sub-plots generated in the one image:

def get_figure4():
    fg = plt.figure(figsize=(10,8))
    adj = plt.subplots_adjust(hspace=0.4,wspace=0.4)

    sp = plt.subplot(2,2,1)
    x = linspace(0,10,101)
    y = exp(x)
    l1 = plt.semilogy(x,y,color='m',linewidth=2)
    lx = plt.xlabel("x")
    ly = plt.ylabel("y")
    tl = plt.title("y = exp(x)")

    sp = plt.subplot(2,2,2)
    y = x**-1.67
    l1 = plt.loglog(x,y)
    lx = plt.xlabel("x")
    ly = plt.ylabel("y")
    tl = plt.title("y = x$^{-5/3}$")

    sp = plt.subplot(2,2,3)
    x = arange(1001)
    y = mod(x,2.87)
    l1 = plt.hist(y,color='r',rwidth = 0.8)
    lx = plt.xlabel("y")
    ly = plt.ylabel("num(y)")
    tl = plt.title("y = mod(arange(1001),2.87)")

    sp = plt.subplot(2,2,4)
    l1 = plt.hist(y,bins=25,normed=True,cumulative=True,orientation='horizontal')
    lx = plt.xlabel("num(y)")
    ly = plt.ylabel("y")
    tl = plt.title("cumulative normed y")
    return fg ...


Figures 5 to 8 illustrate variations on contour plots, not available directly from Excel. The basic graph is generated in Figure 5:

def get_figure5():
    fig = plt.figure()
    x = linspace(0,10,51)
    y = linspace(0,8,41)
    (X,Y) = meshgrid(x,y)
    a = exp(-((X-2.5)**2 + (Y-4)**2)/4) - exp(-((X-7.5)**2 + (Y-4)**2)/4)
    c = plt.contour(x,y,a)
    l = plt.clabel(c)
    lx = plt.xlabel("x")
    ly = plt.ylabel("y")

    return fig 


Figures 6 to 8 illustrate various display options:

def get_figure6():
    fig = plt.figure()
    x = linspace(0,10,51)
    y = linspace(0,8,41)
    (X,Y) = meshgrid(x,y)
    a = exp(-((X-2.5)**2 + (Y-4)**2)/4) - exp(-((X-7.5)**2 + (Y-4)**2)/4)

    c = plt.contour(x,y,a,linspace(-1,1,11),colors='r',linewidths=4, linestyles='dotted')
    lx = plt.xlabel("x")
    ly = plt.ylabel("y")
    return fig ...


def get_figure7():
    fig = plt.figure()
    x = linspace(0,10,51)
    y = linspace(0,8,41)
    (X,Y) = meshgrid(x,y)
    a = exp(-((X-2.5)**2 + (Y-4)**2)/4) - exp(-((X-7.5)**2 + (Y-4)**2)/4)

    c = plt.contourf(x,y,a,linspace(-1,1,11))
    b = plt.colorbar(c, orientation='vertical')
    lx = plt.xlabel("x")
    ly = plt.ylabel("y")
    ax = plt.axis([0,10,0,8])
    return fig 


def get_figure8():
    fig = plt.figure()
    x = linspace(0,10,51)
    y = linspace(0,8,41)
    (X,Y) = meshgrid(x,y)
    a = exp(-((X-2.5)**2 + (Y-4)**2)/4) - exp(-((X-7.5)**2 + (Y-4)**2)/4)
    ac = 0.25*(a[:-1,:-1] + a[:-1,1:] + a[1:,:-1] + a[1:,1:])
    c = plt.pcolor(x,y,ac)
    d = plt.colorbar(c,orientation='horizontal')
    q = plt.winter()
    e = plt.contour(x,y,a,linspace(-1,1,11),colors='k')
    lx = plt.xlabel("x")
    ly = plt.xlabel("y")
    return fig 


Figure 9 illustrates a vector plot, also not available directly from Excel:

def get_figure9():
    fig = plt.figure()
    x = linspace(0,10,11)
    y = linspace(0,15,16)
    (X,Y) = meshgrid(x,y)
    u = 5*X
    v = 5*Y
    q = plt.quiver(X,Y,u,v,angles='xy',scale=1000,color='r')
    p = plt.quiverkey(q,1,16.5,50,"50 m/s",coordinates='data',color='r')
    xl = plt.xlabel("x (km)")
    yl = plt.ylabel("y (km)")
    return fig 


The final example from is a combination plot. See the link for additional background information and useful links.

def get_figure10():
    x = arange(0.,10.1,0.2)
    a = sin(x)
    b = cos(x)
    fig1 = plt.figure(figsize = (8,8))
    p1 = plt.subplot(2,1,1)
    l1 = plt.plot(x,a)
    lx = plt.xlabel("x (km)")
    ly = plt.ylabel("y (m)")
    ttl = plt.title("sine")
    p2 = plt.subplot(2,1,2)
    l2 = plt.plot(x,b)
    lx = plt.xlabel("x (km)")
    ly = plt.ylabel("y (m)")
    ttl = plt.title("cosine")
    sttl = plt.suptitle("two plots")
    return fig1 ...


The remaining examples show how Matplotlib can be used to plot a function entered as text on the spreadsheet, without having to generate a table of values in the spreadsheet. This can also be done directly from Excel (although the procedure is not obvious, see Charting a function), but using Matplotlib also provides access to all the additional functionality of this program.

Here is the Python code:

def plotfunc():
# Get data from the spreadsheet
    wb = xw.Workbook.caller()
    func = xw.Range('PlotFunction', 'func').value
    var = xw.Range('PlotFunction', 'var').value
    if var == None: var = 'x'
    xrange = xw.Range('PlotFunction', 'x_range').value
    xmin = xrange[0]
    xmax = xrange[1]
    xnum = int(xrange[2])
    yrange = xw.Range('PlotFunction', 'y_range').value
    params = xw.Range('PlotFunction', 'params').value
    vals = xw.Range('PlotFunction', 'vals').value
# Convert params from strings to variables with the value given in vals
    for param, val in zip(params, vals):
        if param != None:
            globals()[param] = val
# Create array of x values            
    x = linspace(xmin, xmax, xnum)
# Convert func to a lambda function and evaluate it for x
    lfunc = eval('lambda ' + var + ': ' + func)
    y = lfunc(x)
# Make a new figure and plot the results
    fig = plt.figure()   
    line1=plt.plot(x, y)
    plt.setp(line1, color='r', linewidth=1.0)
    if yrange[0] == None:
        ymin = amin(y)
        ymin = yrange[0]
    if yrange[1] == None:
        ymax = amax(y)
        ymax = yrange[1]
    xrng = xmax-xmin
    xmin = xmin - xrng * .05
    xmax = xmax + xrng * .05
    yrng = ymax - ymin
    if yrange[0] == None: ymin = ymin - yrng * .05
    if yrange[1] == None: ymax = ymax + yrng * .05
    plt.axis([xmin,xmax, ymin, ymax])
    xl = plt.xlabel('X')
    yl = plt.ylabel('Y')
    ttl = plt.title(func)
    plot = xw.Plot(fig)'MyFuncplot', sheet=2)

The VBA code simply calls the plotfunc function from the xlMatPlot Python code:

Private Sub Worksheet_Change(ByVal Target As Range)
    RunPython ("import xlMatPlot; xlMatPlot.plotfunc()")
End Sub

This code is located in the PlotFunction Worksheet code and will run whenever a value on the PlotFunction worksheet changes.

The first example shows a plot of a fourth order polynomial:


The remaining examples plot an oscillating function that was used as an example in recent presentations of integration functions:
exp(x) * sin(a*x**b)

With a = 2 and b = 3, plotting 1000 points with an x range of 1 to 3 produces a nice smooth result:


Increasing the maximum x to 10 greatly increases the number and range of the oscillations, with the result that 1000 points are clearly not enough to produce an accurate result.


Increasing the number of points to 100,000 gives a much better result, providing a visual illustration of the reason why it is difficult to get an accurate result when performing a numerical integration of this function.  Note that the first time any Python code is called from a newly opened worksheet there is a noticeable delay while the code is imported and compiled, but thereafter the graph with 100,000 points should recalculate and re-plot in well under one second.



This entry was posted in Drawing, Excel, Link to Python, Maths, Newton, Numerical integration, NumPy and SciPy, VBA and tagged , , , , , , . Bookmark the permalink.

6 Responses to Use Matplotlib from Excel with xlwings

  1. Paris Altidis says:

    I get an error “File Not Found:xlwings32.dll”. This file is in the Anaconda root directory that happens to be in the Path. Is there a specific location for this file so that your VBA code can use it ??


    • dougaj4 says:

      I will have to check it out. It just works on my system. I will leave a message here if I find a likely solution.
      You could also try a search at:


      • Paris Altidis says:

        *** UPDATE
        The setup works OK on my personal laptop (installed afterwards) but not at work. Same setup, Python version, etc.. Only difference is the Python installation folder name. Kinda strange.


    • dougaj4 says:

      I have now checked the spreadsheet on another computer, which had a very old copy of xlwings (which I don’t know if it was ever installed properly).

      I first downloaded the zip file, and extracted all the files to an empty folder (..\Documents\Spread\Test).
      I ran it without doing anything else, and got a similar message to you.

      I then went to the Anaconda folder, opened a command line window, and ran the following commands:
      conda update conda
      conda update python
      conda update xlwings

      I then opened the spreadsheet again and got a different error, object type None.

      Back in the Anaconda folder, from the command line I then did:
      conda update numpy
      conda update matplotlib

      Then running the spreadsheet again, it works!

      I am now doing:
      conda update anaconda

      which is probably what I should have done in the first place, since it updates everything in the Anaconda package.


  2. Paris Altidis says:

    *** UPDATE #2
    The two updates that you mentioned above DID the trick to my work laptop install as well.
    conda update numpy
    conda update matplotlib

    Paris A.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s