The pyxll documentation has many examples of plotting in Excel using Matplotlib and other packages, but I find the multiple options confusing and hard to follow, so this post works through the examples in the Matplotlib Users Guide tutorial. The sample spreadsheet and python code can be downloaded from:
Note that all the examples below were taken from the manual for Release 2.0.2. The current release is 3.4.3, and includes several additional examples, and considerably more background information.
The first example plots a simple line graph using hard coded data. I have added a modified version that will transfer the data from a selected row on the spreadsheet.
import matplotlib.pyplot as plt
from matplotlib import colors
import pyxll
from pyxll import xl_func, xl_arg, xl_return
from pyxll import plot
@xl_func
def MPLTute_1():
# Create the figure
fig = plt.subplots()[0]
# Plot the data
plt.plot([1,2,3,4])
plt.plot([0,2,4,6])
plt.ylabel('some numbers')
# Display the figure in Excel
plot(fig)
@xl_func
@xl_arg('data','float[]')
def MPLTute_1a(data):
fig, ax = plt.subplots()
ax.plot(data)
ax.set(ylabel='some numbers')
plot(fig)
The first function follows the code in the tutorial as closely as possible, with the addition of a second plotted line. The second function, in addition to reading the data to be plotted from the spreadsheet, follows the coding used in the pyxll examples more closely.
The next example plots XY data, read from the spreadsheet as a list of lists. The string ‘ro’ plots the line as red circles for each data point (see the manual for details):
@xl_func
@xl_arg('data','float[][]')
def MPLTute_2(data):
fig, ax = plt.subplots()
ax.plot(data[0], data[1], 'ro')
ax.set_xbound(0, 6)
ax.set_ybound(0, 20)
ax.set(ylabel='some numbers')
plot(fig)
Function 3 plots 3 lines, each with a different format string. The lines are hard coded functions of the range “t”. The lines are created with a single .plot, as a sequence of 3 sets of X values, Y values, line format string:
@xl_func
def MPLTute_3():
t = np.arange(0., 5., 0.2)
fig, ax = plt.subplots()
ax.plot(t, t, 'r--', t, t**2, 'bs', t, t**3, 'g^')
plot(fig)
Function 4 plots one of the lines from the previous example, but instead of using a single string to format the line, a dictionary is passed from the spreadsheet, allowing multiple format properties to be specified:
@xl_func
@xl_arg('props','dict<str, var>')
def MPLTute_4(props):
x = np.arange(0., 5., 0.2)
y = x**2
fig, ax = plt.subplots()
ax.plot(x, y, **props)
plot(fig)
The spreadsheet includes a list of available properties, but see the manual for full details:
Function 5 plots a histogram, using the plt.hist method:
@xl_func
def MPLTute_5():
np.random.seed(19680801)
mu, sigma = 100, 15
x = mu + sigma * np.random.randn(10000)
fig, ax = plt.subplots()
# the histogram of the data
# Changed from the tutorial example: "n, bins, patches = plt.hist(x, 50, normed=1, facecolor='g', alpha=0.75)"
# because use of normed now raises an error.
n, bins, patches = plt.hist(x, 50, density=1, facecolor='g', alpha=0.75)
plt.xlabel('Smarts')
plt.ylabel('Probability')
plt.title('Histogram of IQ')
plt.text(60, .025, r'$\mu=100,\ \sigma=15$')
plt.axis([40, 160, 0, 0.03])
plt.grid(True)
plot(fig)
The comment noting the change from the code in the tutorial refers to the document for Release 2.02. The current tutorial (Release 3.4.3) has been corrected.
Function 6 illustrates the addition of text and graphics to the graph:
@xl_func
def MPLTute_6():
fig, ax = plt.subplots()
t = np.arange(0.0, 5.0, 0.01)
s = np.cos(2*np.pi*t)
line, = plt.plot(t, s, lw=2)
plt.annotate('local max', xy=(2, 1), xytext=(3, 1.5),
arrowprops=dict(facecolor='black', shrink=0.05),)
plt.ylim(-2,2)
plot(fig)
Finally Functions 7 and 7a illustrate the use of different axis types, and returning multiple graphs from a single function:
@xl_func
@xl_arg('scaletype', 'int')
def MPLTute_7(scaletype):
fig, ax = plt.subplots()
from matplotlib.ticker import NullFormatter # useful for `logit` scale
# Fixing random state for reproducibility
np.random.seed(19680801)
# make up some data in the interval ]0, 1[
y = np.random.normal(loc=0.5, scale=0.4, size=1000)
y = y[(y > 0) & (y < 1)]
y.sort()
x = np.arange(len(y))
# plot with selected axes scale
if scaletype == 1:
# plt.figure(1)
# # linear
# plt.subplot(221)
plt.plot(x, y)
plt.yscale('linear')
plt.title('linear')
plt.grid(True)
# log
elif scaletype == 2:
# plt.subplot(222)
plt.plot(x, y)
plt.yscale('log')
plt.title('log')
plt.grid(True)
# symmetric log
elif scaletype == 3:
# plt.subplot(223)
plt.plot(x, y - y.mean())
plt.yscale('symlog', linthreshy=0.01)
plt.title('symlog')
plt.grid(True)
# logit
elif scaletype == 4:
# plt.subplot(224)
plt.plot(x, y)
plt.yscale('logit')
plt.title('logit')
plt.grid(True)
else:
return 'scaletype must be between 1 and 4'
# Format the minor tick labels of the y-axis into empty strings with
# `NullFormatter`, to avoid cumbering the axis with too many labels.
plt.gca().yaxis.set_minor_formatter(NullFormatter())
# Adjust the subplot layout, because the logit one may take more space
# than usual, due to y-tick labels like "1 - 10^{-3}"
# Not required, Excel version returns only 1 chart
# plt.subplots_adjust(top=0.92, bottom=0.08, left=0.10, right=0.95, hspace=0.25,
# wspace=0.35)
plot(fig)
@xl_func
def MPLTute_7a():
# Multiplot version
fig, ax = plt.subplots()
from matplotlib.ticker import NullFormatter # useful for `logit` scale
# Fixing random state for reproducibility
np.random.seed(19680801)
# make up some data in the interval ]0, 1[
y = np.random.normal(loc=0.5, scale=0.4, size=1000)
y = y[(y > 0) & (y < 1)]
y.sort()
x = np.arange(len(y))
# plot with various axes scales
fig = plt.figure(1)
# # linear
plt.subplot(221)
plt.plot(x, y)
plt.yscale('linear')
plt.title('linear')
plt.grid(True)
# log
plt.subplot(222)
plt.plot(x, y)
plt.yscale('log')
plt.title('log')
plt.grid(True)
# symmetric log
plt.subplot(223)
plt.plot(x, y - y.mean())
plt.yscale('symlog', linthreshy=0.01)
plt.title('symlog')
plt.grid(True)
# logit
plt.subplot(224)
plt.plot(x, y)
plt.yscale('logit')
plt.title('logit')
plt.grid(True)
# Format the minor tick labels of the y-axis into empty strings with
# `NullFormatter`, to avoid cumbering the axis with too many labels.
plt.gca().yaxis.set_minor_formatter(NullFormatter())
# Adjust the subplot layout, because the logit one may take more space
# than usual, due to y-tick labels like "1 - 10^{-3}"
plt.subplots_adjust(top=0.92, bottom=0.08, left=0.10, right=0.95, hspace=0.25,
wspace=0.35)
plot(fig)
Newton Excel Bach, not (just) an Excel Blog 