Introduction to xlwings

  • xlwings provides a Python interface to and from Excel.
  • Excel can be extended with VBA but...
  • Python is modern, faster, and has many built-in numerics packages that make it superior to VBA for data analysis and visualization.
  • xlwings provides this functionality with minimal effort.
  • Quickstart guide
In [1]:
import xlwings as xw

import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline

Open a workbook and select a worksheet

In [2]:
wb  = xw.Book('data.xlsx')
sht = wb.sheets['my data']

Get from Excel and push data to Excel

In [3]:
ft_per_m = sht.range('B4').value
ft_per_m
Out[3]:
3.28084
In [4]:
lbm_per_kg = 2.20462
sht.range('A6').color = (255,253,66)
sht.range('B6').color = (148,206,88)
sht.range('A6').value = "lbm_per_kg"
sht.range('B6').value = lbm_per_kg

Get arrays

In [6]:
t = sht.range('E2:E42').value                    # --> list 
t = np.array( sht.range('E2:E42').value )        # --> numpy array
v = sht.range('F2:F42').options(np.array).value  # --> numpy array alternative

# OR THIS

tv = np.array(sht.range('E2').expand().value)    # expand() --> smart sizing --> matrix
t = tv[:,0]
v = tv[:,1]

# OR THIS

t = np.array(sht.range('E2').expand('vertical').value)    # expand() --> smart sizing --> matrix
v = np.array(sht.range('F2').expand('vertical').value)    # expand() --> smart sizing --> matrix
In [7]:
plt.plot(t,v);

Put arrays

In [8]:
data = np.array([1,2,3])

sht.range('a10').value = data                          # --> horizontal
sht.range('a12').options(transpose=True).value = data  # --> vertical
sht.range('a16').value = data[:,np.newaxis]            # --> vertial alternative
In [9]:
T = np.linspace(300,1000,len(t))

sht.range('J1').value = "t (s)"
sht.range('K1').value = "T (K)"
sht.range('J2').value = t[:,np.newaxis]    
sht.range('K2').value = T[:,np.newaxis]

Add an Excel formula

In [10]:
sht.range('H1').value = "sum(t)"
sht.range('H2').formula = "=sum(E:E)"

Make a plot and include it in Excel as a picture

In [18]:
fig = plt.figure()

plt.rc("font", size=14)
plt.plot(t,T**3)
plt.xlabel('t (s)')
plt.ylabel('T (K)')

sht.pictures.add(fig, name="T_vs_t", update=True, left=720, top=150)
plt.close()          # keeps the plot from showing right here

Sheets

In [19]:
wb.sheets.add("New Sheet", after="my data")

for s in wb.sheets:
    print(s.name)
    
wb.sheets[1].name
my data
New Sheet
Out[19]:
'New Sheet'

Save and close

In [ ]:
wb.save()
wb.close()

Run Python from Excel

  • You can call Python functions from Excel
  • Click here for details
  • Here are initial, one-time setup instructions that are tested on a Mac:

    • In a terminal type xlwings runpython install
    • In a terminal type xlwings addin install Then follow the instructions.
    • Open Excel, and in the xlwings tab, I set the PYTHONPATH environment variable to be what I get when I type env | grep PYTHONPATH in a terminal (without the PYTHONPATH= part).
  • Then, to create a workbook already setup for interfacing with Python, type in a terminal: xlwings quickstart some_project_name. This will create a folder some_project_name with two files: some_project_name.xlsm, and some_project_name.py. You can edit both of these.

  • On Windows, (but not Mac, unfortunately), you can also setup user defined functions (UDFs) that allow you to write Python functions that can be called directly from within Excel code cells.

In [ ]: