xlwings is a BSD-licensed Python library that makes it easy to call Python from Excel and vice versa:
- Interact with Excel from Python using a syntax that is close to VBA yet Pythonic.
- Replace your VBA macros with Python code and still pass around your workbooks as easy as before.
xlwings fully supports NumPy arrays and Pandas DataFrames. It works with Microsoft Excel on Windows and Mac.
Note
xlwings is currently in an early stage. The API might change in backward incompatible ways.
Writing/reading values to/from Excel and adding a chart is as easy as:
>>> from xlwings as xw
>>> wb = xw.Workbook() # Creates a connection with a new workbook
>>> xw.Range('A1').value = 'Foo 1'
>>> xw.Range('A1').value
'Foo 1'
>>> xw.Range('A1').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
>>> xw.Range('A1').table.value # or: xw.Range('A1:C2').value
[['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
>>> xw.Sheet(1).name
'Sheet1'
>>> chart = xw.Chart.add(source_data=xw.Range('A1').table)
The Range and Chart objects as used above will refer to the active sheet of the current Workbook wb
. Include the
Sheet name like this:
xw.Range('Sheet1', 'A1:C3').value
xw.Range(1, (1,1), (3,3)).value # index notation
xw.Chart.add('Sheet1', source_data=xw.Range('Sheet1', 'A1').table)
Qualify the Workbook additionally like this:
xw.Range('Sheet1', 'A1', wkb=wb).value
xw.Chart.add('Sheet1', wkb=wb, source_data=xw.Range('Sheet1', 'A1', wkb=wb).table)
xw.Sheet(1, wkb=wb).name
or simply set the current workbook first:
wb.set_current()
xw.Range('Sheet1', 'A1').value
xw.Chart.add('Sheet1', source_data=xw.Range('Sheet1', 'A1').table)
xw.Sheet(1).name
These commands also work seamlessly with NumPy arrays and Pandas DataFrames.
Matplotlib figures can be shown as pictures in Excel:
import matplotlib.pyplot as plt
fig = plt.figure()
plt.plot([1, 2, 3, 4, 5])
plot = xw.Plot(fig)
plot.show('Plot1')
If, for example, you want to fill your spreadsheet with standard normally distributed random numbers, your VBA code is just one line:
Sub RandomNumbers()
RunPython ("import mymodule; mymodule.rand_numbers()")
End Sub
This essentially hands over control to mymodule.py
:
import numpy as np
from xlwings import Workbook, Range
def rand_numbers():
""" produces standard normally distributed random numbers with shape (n,n)"""
wb = Workbook.caller() # Creates a reference to the calling Excel file
n = int(Range('Sheet1', 'B1').value) # Write desired dimensions into Cell B1
rand_num = np.random.randn(n, n)
Range('Sheet1', 'C3').value = rand_num
To make this run, just import the VBA module xlwings.bas
in the VBA editor (Open the VBA editor with Alt-F11
,
then go to File > Import File...
and import the xlwings.bas
file. ). It can be found in the directory of
your xlwings
installation.
Note
Always instantiate the Workbook
within the function that is called from Excel and not outside as global
variable. Older versions of the docs/samples were showing the wrong approach.
Writing a UDF in Python is as easy as:
from xlwings import xlfunc, xlarg
@xlfunc
def double_sum(x, y):
"""Returns twice the sum of the two arguments"""
return 2 * (x + y)
Deployment is really the part where xlwings shines:
- Just zip-up your Spreadsheet with your Python code and send it around. The receiver only needs to have an installation of Python with xlwings (and obviously all the other packages you're using).
- There is no need to install any Excel add-in.
- If this still sounds too complicated, just freeze your Python code into an executable and use
RunFrozenPython
instead ofRunPython
. This gives you a standalone version of your Spreadsheet tool without any dependencies (RunFrozenPython
is currently only available on Windows).
The easiest way to install xlwings is via pip:
pip install xlwings
Alternatively it can be installed from source. From within the xlwings
directory, execute:
python setup.py install
Windows:
pywin32
,comtypes
On Windows, it is recommended to use one of the scientific Python distributions like Anaconda, WinPython or Canopy as they already include pywin32. Otherwise it needs to be installed from here.
Mac:
psutil
,appscript
On Mac, the dependencies are automatically being handled if xlwings is installed with
pip
. However, the Xcode command line tools need to be available. Mac OS X 10.4 (Tiger) or later is required. The recommended Python distribution for Mac is Anaconda.
- NumPy
- Pandas
- Matplotlib
- Pillow/PIL
These packages are not required but highly recommended as they play very nicely with xlwings.
xlwings runs on Python 2.6-2.7 and 3.1+
- Homepage: http://xlwings.org
- Documentation: http://docs.xlwings.org
- Source Code: https://github.com/zoomeranalytics/xlwings