Matplotlib & Plotly Charts

Matplotlib

Using pictures.add(), it is easy to paste a Matplotlib plot as picture in Excel.

Getting started

The easiest sample boils down to:

>>> import matplotlib.pyplot as plt
>>> import xlwings as xw

>>> fig = plt.figure()
>>> plt.plot([1, 2, 3])

>>> sht = xw.Book().sheets[0]
>>> sht.pictures.add(fig, name='MyPlot', update=True)
_images/mpl_basic.png

備註

If you set update=True, you can resize and position the plot on Excel: subsequent calls to pictures.add() with the same name ('MyPlot') will update the picture without changing its position or size.

Full integration with Excel

Calling the above code with RunPython and binding it e.g. to a button is straightforward and works cross-platform.

However, on Windows you can make things feel even more integrated by setting up a UDF along the following lines:

@xw.func
def myplot(n):
    sht = xw.Book.caller().sheets.active
    fig = plt.figure()
    plt.plot(range(int(n)))
    sht.pictures.add(fig, name='MyPlot', update=True)
    return 'Plotted with n={}'.format(n)

If you import this function and call it from cell B2, then the plot gets automatically updated when cell B1 changes:

_images/mpl_udf.png

Properties

Size, position and other properties can either be set as arguments within pictures.add(), or by manipulating the picture object that is returned, see xlwings.Picture().

For example:

>>> sht = xw.Book().sheets[0]
>>> sht.pictures.add(fig, name='MyPlot', update=True,
                     left=sht.range('B5').left, top=sht.range('B5').top)

or:

>>> plot = sht.pictures.add(fig, name='MyPlot', update=True)
>>> plot.height /= 2
>>> plot.width /= 2

Getting a Matplotlib figure

Here are a few examples of how you get a matplotlib figure object:

  • via PyPlot interface:

    import matplotlib.pyplot as plt
    fig = plt.figure()
    plt.plot([1, 2, 3, 4, 5])
    

    or:

    import matplotlib.pyplot as plt
    plt.plot([1, 2, 3, 4, 5])
    fig = plt.gcf()
    
  • via object oriented interface:

    from matplotlib.figure import Figure
    fig = Figure(figsize=(8, 6))
    ax = fig.add_subplot(111)
    ax.plot([1, 2, 3, 4, 5])
    
  • via Pandas:

    import pandas as pd
    import numpy as np
    
    df = pd.DataFrame(np.random.rand(10, 4), columns=['a', 'b', 'c', 'd'])
    ax = df.plot(kind='bar')
    fig = ax.get_figure()
    

Plotly static charts

This feature requires xlwings PRO.

Prerequisites

In addition to plotly you will need orca. The easiest way to get it is via conda:

$ conda install -c plotly plotly-orca psutil requests

For alternative ways of installation, see: https://plotly.com/python/static-image-export/

How to use

It works the same as with Matplotlib, however, rendering a Plotly chart takes slightly longer. Here is a sample:

import xlwings as xw
import plotly.express as px

# Plotly chart
df = px.data.iris()
fig = px.scatter(df, x="sepal_width", y="sepal_length", color="species")

# Add it to Excel
wb = xw.Book()
wb.sheets[0].pictures.add(fig, name='IrisScatterPlot', update=True)
_images/plotly.png