xlwings - Make Excel Fly!

Release dev

Zoomer Analytics LLC

Oct 06, 2018

Contents

1 Migrate to v0.11 (Add-in) 1

1.1 Upgrade the xlwings Python package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

1.2 Install the add-in . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

1.3 Upgrade existing workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2

2 Migrate to v0.9 3

2.1 Full qualiﬁcation: Using collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

2.2 Connecting to Books . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

2.3 Active Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

2.4 Round vs. Square Brackets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

2.5 Access the underlying Library/Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

2.6 Cheat sheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

3 Installation 7

3.1 Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

3.2 Optional Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

3.3 Add-in . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

3.4 Python version support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

4 Quickstart 9

4.1 1. Scripting: Automate/interact with Excel from Python . . . . . . . . . . . . . . . . . . . 9

4.2 2. Macros: Call Python from Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

4.3 3. UDFs: User Deﬁned Functions (Windows only) . . . . . . . . . . . . . . . . . . . . . . 11

5 Connect to a Book 13

5.1 Python to Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

5.2 Excel to Python (RunPython) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

5.3 User Deﬁned Functions (UDFs) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

6 Syntax Overview 15

6.1 Active Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

6.2 Full qualiﬁcation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

6.3 Range indexing/slicing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

6.4 Range Shortcuts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

6.5 Object Hierarchy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

7 Data Structures Tutorial 19

7.1 Single Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

7.2 Lists . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

7.3 Range expanding . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

7.4 NumPy arrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

7.5 Pandas DataFrames . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

7.6 Pandas Series . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

8 Add-in 23

8.1 Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

8.2 Global Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

8.3 Global Conﬁg: Ribbon/Conﬁg File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

8.4 Workbook Directory Conﬁg: Conﬁg ﬁle . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

8.5 Workbook Conﬁg: xlwings.conf Sheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

8.6 Alternative: Standalone VBA module . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

8.7 Log File default locations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

9 VBA: RunPython 27

9.1 xlwings add-in . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

9.2 Call Python with “RunPython” . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

9.3 Function Arguments and Return Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28

10 VBA: User Deﬁned Functions (UDFs) 29

10.1 One-time Excel preparations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

10.2 Workbook preparation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

10.3 A simple UDF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

10.4 Array formulas: Get efﬁcient . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

10.5 Array formulas with NumPy and Pandas . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

10.6 @xw.arg and @xw.ret decorators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32

10.7 Dynamic Array Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32

10.8 Docstrings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33

10.9 The “vba” keyword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

10.10 Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

10.11 Call UDFs from VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

11 Debugging 37

11.1 RunPython . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

11.2 UDF debug server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

12 Matplotlib 41

12.1 Getting started . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

12.2 Full integration with Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

12.3 Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42

12.4 Getting a Matplotlib ﬁgure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44

13 Converters and Options 45

13.1 Default Converter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46

13.2 Built-in Converters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48

13.3 Custom Converter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52

14 Command Line Client 55

14.1 Quickstart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

14.2 Add-in . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

14.3 RunPython . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

15 Missing Features 57

15.1 Example: Workaround to use VBA’s Range.WrapText . . . . . . . . . . . . . . . . . . 57

16 xlwings with R and Julia 59

16.1 R . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59

16.2 Julia . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61

17 Extensions 63

17.1 In-Excel SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

18 Troubleshooting 65

18.1 Issue: dll not found . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

19 API Documentation 67

19.1 Top-level functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67

19.2 Object model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

19.3 UDF decorators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93

20 License 97

iii

CHAPTER 1

Migrate to v0.11 (Add-in)

This migration guide shows you how you can start using the new xlwings add-in as opposed to the old

xlwings VBA module (and the old add-in that consisted of just a single import button).

1.1 Upgrade the xlwings Python package

1. Check where xlwings is currently installed

>>> import xlwings

>>> xlwings.__path__

2. If you installed xlwings with pip, for once, you should ﬁrst uninstall xlwings: pip uninstall

xlwings

3. Check the directory that you got under 1): if there are any ﬁles left over, delete the xlwings folder

and the remaining ﬁles manually

4. Install the latest xlwings version: pip install xlwings

5. Verify that you have >= 0.11 by doing

>>> import xlwings

>>> xlwings.__version__

1.2 Install the add-in

1. If you have the old xlwings addin installed, ﬁnd the location and remove it or overwrite it with the

new version (see next step). If you installed it via the xlwings command line client, you should be

xlwings - Make Excel Fly!, Release dev

able to do: xlwings addin remove.

2. Close Excel. Run xlwings addin install from a command prompt. Reopen Excel and check

if the xlwings Ribbon appears. If not, copy xlwings.xlam (from your xlwings installation folder

under addin\xlwings.xlam manually into the XLSTART folder. You can ﬁnd the location of

this folder under Options > Trust Center > Trust Center Settings. . . > Trusted Locations, under the

description Excel default location: User StartUp. Restart Excel and you should see

the add-in.

1.3 Upgrade existing workbooks

1. Make a backup of your Excel ﬁle

2. Open the ﬁle and go to the VBA Editor (Alt-F11)

3. Remove the xlwings VBA module

4. Add a reference to the xlwings addin, see Installation

5. If you want to use workbook speciﬁc settings, add a sheet xlwings.conf, see Workbook Conﬁg:

xlwings.conf Sheet

Note: To import UDFs, you need to have the reference to the xlwings add-in set!

2 Chapter 1. Migrate to v0.11 (Add-in)

CHAPTER 2

Migrate to v0.9

The purpose of this document is to enable you a smooth experience when upgrading to xlwings v0.9.0 and

above by laying out the concept and syntax changes in detail. If you want to get an overview of the new

features and bug ﬁxes, have a look at the release notes. Note that the syntax for User Deﬁned Functions

(UDFs) didn’t change.

2.1 Full qualiﬁcation: Using collections

The new object model allows to specify the Excel application instance if needed:

• old: xw.Range('Sheet1', 'A1', wkb=xw.Workbook('Book1'))

• new: xw.apps[0].books['Book1'].sheets['Sheet1'].range('A1')

See Syntax Overview for the details of the new object model.

2.2 Connecting to Books

• old: xw.Workbook()

• new: xw.Book() or via xw.books if you need to control the app instance.

See Connect to a Book for the details.

xlwings - Make Excel Fly!, Release dev

2.3 Active Objects

# Active app (i.e. Excel instance)

>>> app = xw.apps.active

# Active book

>>> wb = xw.books.active # in active app

>>> wb = app.books.active # in specific app

# Active sheet

>>> sht = xw.sheets.active # in active book

>>> sht = wb.sheets.active # in specific book

# Range on active sheet

>>> xw.Range('A1') # on active sheet of active book of active app

2.4 Round vs. Square Brackets

Round brackets follow Excel’s behavior (i.e. 1-based indexing), while square brackets use Python’s 0-based

indexing/slicing.

As an example, the following all reference the same range:

xw.apps[0].books[0].sheets[0].range('A1')

xw.apps(1).books(1).sheets(1).range('A1')

xw.apps[0].books['Book1'].sheets['Sheet1'].range('A1')

xw.apps(1).books('Book1').sheets('Sheet1').range('A1')

2.5 Access the underlying Library/Engine

• old: xw.Range('A1').xl_range and xl_sheet etc.

• new: xw.Range('A1').api, same for all other objects

This returns a pywin32 COM object on Windows and an appscript object on Mac.

2.6 Cheat sheet

Note that sht stands for a sheet object, like e.g. (in 0.9.0 syntax): sht = xw.books['Book1'].

sheets[0]

v0.9.0 v0.7.2

Active Excel instance xw.apps.active unsupported

New Excel instance app = xw.App() unsupported

Continued on next page

4 Chapter 2. Migrate to v0.9

xlwings - Make Excel Fly!, Release dev

Table 1 – continued from previous page

v0.9.0 v0.7.2

Get app from book app = wb.app app = xw.Application(wb)

Target installation (Mac) app = xw.App(spec=...) wb = xw.Workbook(app_target=...)

Hide Excel Instance app = xw.App(visible=False) wb = xw.Workbook(app_visible=False)

Selected Range app.selection wb.get_selection()

Calculation mode app.calculation = 'manual' app.calculation = xw.constants.Calculation.xlCalculationManual

All books in app app.books unsupported

Fully qualiﬁed book app.books['Book1'] unsupported

Active book in active app xw.books.active xw.Workbook.active()

New book in active app wb = xw.Book() wb = xw.Workbook()

New book in speciﬁc app wb = app.books.add() unsupported

All sheets in book wb.sheets xw.Sheet.all(wb)

Call a macro in an addin app.macro('MacroName') unsupported

First sheet of book wb wb.sheets[0] xw.Sheet(1, wkb=wb)

Active sheet wb.sheets.active xw.Sheet.active(wkb=wb) or wb.active_sheet

Add sheet wb.sheets.add() xw.Sheet.add(wkb=wb)

Sheet count wb.sheets.count or len(wb.sheets) xw.Sheet.count(wb)

Add chart to sheet chart = wb.sheets[0].charts.add() chart = xw.Chart.add(sheet=1, wkb=wb)

Existing chart wb.sheets['Sheet 1'].charts[0] xw.Chart('Sheet 1', 1)

Chart Type chart.chart_type = '3d_area' chart.chart_type = xw.constants.ChartType.xl3DArea

Add picture to sheet wb.sheets[0].pictures.add('path/to/pic') xw.Picture.add('path/to/pic', sheet=1, wkb=wb)

Existing picture wb.sheets['Sheet 1'].pictures[0] xw.Picture('Sheet 1', 1)

Matplotlib sht.pictures.add(fig, name='x', update=True) xw.Plot(fig).show('MyPlot', sheet=sht, wkb=wb)

Table expansion sht.range('A1').expand('table') xw.Range(sht, 'A1', wkb=wb).table

Vertical expansion sht.range('A1').expand('down') xw.Range(sht, 'A1', wkb=wb).vertical

Horizontal expansion sht.range('A1').expand('right') xw.Range(sht, 'A1', wkb=wb).horizontal

Set name of range sht.range('A1').name = 'name' xw.Range(sht, 'A1', wkb=wb).name = 'name'

Get name of range sht.range('A1').name.name xw.Range(sht, 'A1', wkb=wb).name

mock caller xw.Book('file.xlsm').set_mock_caller() xw.Workbook.set_mock_caller('file.xlsm')

2.6. Cheat sheet 5

xlwings - Make Excel Fly!, Release dev

6 Chapter 2. Migrate to v0.9

CHAPTER 3

Installation

The easiest way to install xlwings is via pip:

pip install xlwings

or conda:

conda install xlwings

Note that the ofﬁcial conda package might be few releases behind. You can, however, use the

conda-forge channel (see: https://anaconda.org/conda-forge/xlwings) which should usually be up to

date (but might still be a day or so behind the pip release):

conda install -c conda-forge xlwings

Note: When you are using Mac Excel 2016 and are installing xlwings with conda (or use the version

that comes with Anaconda), you’ll need to run $ xlwings runpython install once to enable the

RunPython calls from VBA. Alternatively, you can simply install xlwings with pip.

3.1 Dependencies

• Windows: pywin32, comtypes

On Windows, the dependencies are automatically being handled if xlwings is installed with conda

or pip.

• Mac: psutil, appscript

xlwings - Make Excel Fly!, Release dev

On Mac, the dependencies are automatically being handled if xlwings is installed with conda or

pip. However, with pip, 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.

3.2 Optional Dependencies

• NumPy

• Pandas

• Matplotlib

• Pillow/PIL

These packages are not required but highly recommended as they play very nicely with xlwings.

3.3 Add-in

Please see Add-in on how to install the xlwings add-in.

3.4 Python version support

xlwings is tested on Python 2.7 and 3.3+

8 Chapter 3. Installation

CHAPTER 4

Quickstart

This guide assumes you have xlwings already installed. If that’s not the case, head over to Installation.

4.1 1. Scripting: Automate/interact with Excel from Python

Establish a connection to a workbook:

>>> import xlwings as xw

>>> wb = xw.Book() # this will create a new workbook

>>> wb = xw.Book('FileName.xlsx') # connect to an existing file in the

˓→current working directory

>>> wb = xw.Book(r'C:\path\to\file.xlsx') # on Windows: use raw strings to

˓→escape backslashes

If you have the same ﬁle open in two instances of Excel, you need to fully qualify it and include the app

instance:

>>> xw.apps[0].books['FileName.xlsx']

Instantiate a sheet object:

>>> sht = wb.sheets['Sheet1']

Reading/writing values to/from ranges is as easy as:

>>> sht.range('A1').value = 'Foo 1'

>>> sht.range('A1').value

'Foo 1'

There are many convenience features available, e.g. Range expanding:

xlwings - Make Excel Fly!, Release dev

>>> sht.range('A1').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]

>>> sht.range('A1').expand().value

[['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]

Powerful converters handle most data types of interest, including Numpy arrays and Pandas DataFrames

in both directions:

>>> import pandas as pd

>>> df = pd.DataFrame([[1,2], [3,4]], columns=['a', 'b'])

>>> sht.range('A1').value = df

>>> sht.range('A1').options(pd.DataFrame, expand='table').value

a b

0.0 1.0 2.0

1.0 3.0 4.0

Matplotlib ﬁgures can be shown as pictures in Excel:

>>> import matplotlib.pyplot as plt

>>> fig = plt.figure()

>>> plt.plot([1, 2, 3, 4, 5])

[<matplotlib.lines.Line2D at 0x1071706a0>]

>>> sht.pictures.add(fig, name='MyPlot', update=True)

Shortcut for the active sheet: xw.Range

If you want to quickly talk to the active sheet in the active workbook, you don’t need instantiate a workbook

and sheet object, but can simply do:

>>> import xlwings as xw

>>> xw.Range('A1').value = 'Foo'

>>> xw.Range('A1').value

'Foo'

Note: You should only use xw.Range when interacting with Excel. In scripts, you should always go via

book and sheet objects as shown above.

4.2 2. Macros: Call Python from Excel

You can call Python functions from VBA using the RunPython function:

Sub HelloWorld()

RunPython ("import hello; hello.world()")

End Sub

Per default, RunPython expects hello.py in the same directory as the Excel ﬁle. Refer to the calling

Excel book by using xw.Book.caller:

10 Chapter 4. Quickstart

xlwings - Make Excel Fly!, Release dev

# hello.py

import numpy as np

import xlwings as xw

def world():

wb = xw.Book.caller()

wb.sheets[0].range('A1').value = 'Hello World!'

To make this run, you’ll need to have the xlwings add-in installed. The easiest way to get everything set

up is to use the xlwings command line client from either a command prompt on Windows or a terminal on

Mac: xlwings quickstart myproject.

For details about the addin, see Add-in.

4.3 3. UDFs: User Deﬁned Functions (Windows only)

Writing a UDF in Python is as easy as:

import xlwings as xw

@xw.func

def hello(name):

return 'Hello {0}'.format(name)

Converters can be used with UDFs, too. Again a Pandas DataFrame example:

import xlwings as xw

import pandas as pd

@xw.func

@xw.arg('x', pd.DataFrame)

def correl2(x):

# x arrives as DataFrame

return x.corr()

Import this function into Excel by clicking the import button of the xlwings add-in: For further details, see

VBA: User Deﬁned Functions (UDFs).

4.3. 3. UDFs: User Deﬁned Functions (Windows only) 11

xlwings - Make Excel Fly!, Release dev

12 Chapter 4. Quickstart

CHAPTER 5

Connect to a Book

When reading/writing data to the active sheet, you don’t need a book object:

>>> import xlwings as xw

>>> xw.Range('A1').value = 'something'

5.1 Python to Excel

The easiest way to connect to a book is offered by xw.Book: it looks for the book in all app instances and

returns an error, should the same book be open in multiple instances. To connect to a book in the active app

instance, use xw.books and to refer to a speciﬁc app, use:

>>> app = xw.App() # or something like xw.apps[0] for existing apps

>>> app.books['Book1']

xw.Book xw.books

New book xw.Book() xw.books.add()

Unsaved book xw.Book('Book1') xw.books['Book1']

Book by

(full)name

xw.Book(r'C:/path/to/

file.xlsx')

xw.books.open(r'C:/path/to/

file.xlsx')

Note: When specifying ﬁle paths on Windows, you should either use raw strings by putting an r in front

of the string or use double back-slashes like so: C:\\path\\to\\file.xlsx.

xlwings - Make Excel Fly!, Release dev

5.2 Excel to Python (RunPython)

To reference the calling book when using RunPython in VBA, use xw.Book.caller(), see Call

Python with “RunPython”. Check out the section about Debugging to see how you can call a script from

both sides, Python and Excel, without the need to constantly change between xw.Book.caller() and

one of the methods explained above.

5.3 User Deﬁned Functions (UDFs)

Unlike RunPython, UDFs don’t need a call to xw.Book.caller(), see VBA: User Deﬁned Functions

(UDFs). However, it’s available (restricted to read-only though), which sometimes proofs to be useful.

14 Chapter 5. Connect to a Book

CHAPTER 6

Syntax Overview

The xlwings object model is very similar to the one used by VBA.

All code samples below depend on the following import:

>>> import xlwings as xw

6.1 Active Objects

# Active app (i.e. Excel instance)

>>> app = xw.apps.active

# Active book

>>> wb = xw.books.active # in active app

>>> wb = app.books.active # in specific app

# Active sheet

>>> sht = xw.sheets.active # in active book

>>> sht = wb.sheets.active # in specific book

# Range on active sheet

>>> xw.Range('A1') # on active sheet of active book of active app

A Range can be instantiated with A1 notation, a tuple of Excel’s 1-based indices, a named range or two

Range objects:

xw.Range('A1')

xw.Range('A1:C3')

xw.Range((1,1))

(continues on next page)

xlwings - Make Excel Fly!, Release dev

(continued from previous page)

xw.Range((1,1), (3,3))

xw.Range('NamedRange')

xw.Range(xw.Range('A1'), xw.Range('B2'))

6.2 Full qualiﬁcation

Round brackets follow Excel’s behavior (i.e. 1-based indexing), while square brackets use Python’s 0-based

indexing/slicing. As an example, the following expressions all reference the same range:

xw.apps[0].books[0].sheets[0].range('A1')

xw.apps(1).books(1).sheets(1).range('A1')

xw.apps[0].books['Book1'].sheets['Sheet1'].range('A1')

xw.apps(1).books('Book1').sheets('Sheet1').range('A1')

6.3 Range indexing/slicing

Range objects support indexing and slicing, a few examples:

>>> rng = xw.Book().sheets[0].range('A1:D5')

>>> rng[0, 0]

>>> rng[1]

>>> rng[:, 3:]

>>> rng[1:3, 1:3]

6.4 Range Shortcuts

Sheet objects offer a shortcut for range objects by using index/slice notation on the sheet object. This evalu-

ates to either sheet.range or sheet.cells depending on whether you pass a string or indices/slices:

>>> sht = xw.Book().sheets['Sheet1']

>>> sht['A1']

>>> sht['A1:B5']

>>> sht[0, 1]

>>> sht[:10, :10]

16 Chapter 6. Syntax Overview

xlwings - Make Excel Fly!, Release dev

6.5 Object Hierarchy

The following shows an example of the object hierarchy, i.e. how to get from an app to a range object and

all the way back:

>>> rng = xw.apps[0].books[0].sheets[0].range('A1')

>>> rng.sheet.book.app

6.5. Object Hierarchy 17

xlwings - Make Excel Fly!, Release dev

18 Chapter 6. Syntax Overview

CHAPTER 7

Data Structures Tutorial

This tutorial gives you a quick introduction to the most common use cases and default behaviour of xlwings

when reading and writing values. For an in-depth documentation of how to control the behavior using the

options method, have a look at Converters and Options.

All code samples below depend on the following import:

>>> import xlwings as xw

7.1 Single Cells

Single cells are by default returned either as float, unicode, None or datetime objects, depending

on whether the cell contains a number, a string, is empty or represents a date:

>>> import datetime as dt

>>> sht = xw.Book().sheets[0]

>>> sht.range('A1').value = 1

>>> sht.range('A1').value

1.0

>>> sht.range('A2').value = 'Hello'

>>> sht.range('A2').value

'Hello'

>>> sht.range('A3').value is None

True

>>> sht.range('A4').value = dt.datetime(2000, 1, 1)

>>> sht.range('A4').value

datetime.datetime(2000, 1, 1, 0, 0)

xlwings - Make Excel Fly!, Release dev

7.2 Lists

• 1d lists: Ranges that represent rows or columns in Excel are returned as simple lists, which means

that once they are in Python, you’ve lost the information about the orientation. If that is an issue, the

next point shows you how to preserve this info:

>>> sht = xw.Book().sheets[0]

>>> sht.range('A1').value = [[1],[2],[3],[4],[5]] # Column orientation

˓→(nested list)

>>> sht.range('A1:A5').value

[1.0, 2.0, 3.0, 4.0, 5.0]

>>> sht.range('A1').value = [1, 2, 3, 4, 5]

>>> sht.range('A1:E1').value

[1.0, 2.0, 3.0, 4.0, 5.0]

To force a single cell to arrive as list, use:

>>> sht.range('A1').options(ndim=1).value

[1.0]

Note: To write a list in column orientation to Excel, use transpose: sht.range('A1').

options(transpose=True).value = [1,2,3,4]

• 2d lists: If the row or column orientation has to be preserved, set ndim in the Range options. This

will return the Ranges as nested lists (“2d lists”):

>>> sht.range('A1:A5').options(ndim=2).value

[[1.0], [2.0], [3.0], [4.0], [5.0]]

>>> sht.range('A1:E1').options(ndim=2).value

[[1.0, 2.0, 3.0, 4.0, 5.0]]

• 2 dimensional Ranges are automatically returned as nested lists. When assigning (nested) lists to a

Range in Excel, it’s enough to just specify the top left cell as target address. This sample also makes

use of index notation to read the values back into Python:

>>> sht.range('A10').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10, 20, 30]]

>>> sht.range((10,1),(11,3)).value

[['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]

Note: Try to minimize the number of interactions with Excel. It is always more efﬁcient to do

sht.range('A1').value = [[1,2],[3,4]] than sht.range('A1').value = [1, 2]

and sht.range('A2').value = [3, 4].

20 Chapter 7. Data Structures Tutorial

xlwings - Make Excel Fly!, Release dev

7.3 Range expanding

You can get the dimensions of Excel Ranges dynamically through either the method expand or through the

expand keyword in the options method. While expand gives back an expanded Range object, options

are only evaluated when accessing the values of a Range. The difference is best explained with an example:

>>> sht = xw.Book().sheets[0]

>>> sht.range('A1').value = [[1,2], [3,4]]

>>> rng1 = sht.range('A1').expand('table') # or just .expand()

>>> rng2 = sht.range('A1').options(expand='table')

>>> rng1.value

[[1.0, 2.0], [3.0, 4.0]]

>>> rng2.value

[[1.0, 2.0], [3.0, 4.0]]

>>> sht.range('A3').value = [5, 6]

>>> rng1.value

[[1.0, 2.0], [3.0, 4.0]]

>>> rng2.value

[[1.0, 2.0], [3.0, 4.0], [5.0, 6.0]]

'table' expands to 'down' and 'right', the other available options which can be used for column or

row only expansion, respectively.

Note: Using expand() together with a named Range as top left cell gives you a ﬂexible setup in Excel:

You can move around the table and change it’s size without having to adjust your code, e.g. by using

something like sht.range('NamedRange').expand().value.

7.4 NumPy arrays

NumPy arrays work similar to nested lists. However, empty cells are represented by nan instead of None.

If you want to read in a Range as array, set convert=np.array in the options method:

>>> import numpy as np

>>> sht = xw.Book().sheets[0]

>>> sht.range('A1').value = np.eye(3)

>>> sht.range('A1').options(np.array, expand='table').value

array([[ 1., 0., 0.],

[ 0., 1., 0.],

[ 0., 0., 1.]])

7.5 Pandas DataFrames

>>> sht = xw.Book().sheets[0]

>>> df = pd.DataFrame([[1.1, 2.2], [3.3, None]], columns=['one', 'two'])

>>> df

(continues on next page)

7.3. Range expanding 21

xlwings - Make Excel Fly!, Release dev

(continued from previous page)

one two

0 1.1 2.2

1 3.3 NaN

>>> sht.range('A1').value = df

>>> sht.range('A1:C3').options(pd.DataFrame).value

one two

0 1.1 2.2

1 3.3 NaN

# options: work for reading and writing

>>> sht.range('A5').options(index=False).value = df

>>> sht.range('A9').options(index=False, header=False).value = df

7.6 Pandas Series

>>> import pandas as pd

>>> import numpy as np

>>> sht = xw.Book().sheets[0]

>>> s = pd.Series([1.1, 3.3, 5., np.nan, 6., 8.], name='myseries')

>>> s

0 1.1

1 3.3

2 5.0

3 NaN

4 6.0

5 8.0

Name: myseries, dtype: float64

>>> sht.range('A1').value = s

>>> sht.range('A1:B7').options(pd.Series).value

0 1.1

1 3.3

2 5.0

3 NaN

4 6.0

5 8.0

Name: myseries, dtype: float64

Note: You only need to specify the top left cell when writing a list, a NumPy array or a Pandas DataFrame

to Excel, e.g.: sht.range('A1').value = np.eye(10)

22 Chapter 7. Data Structures Tutorial

CHAPTER 8

Add-in

The xlwings add-in is the preferred way to be able to use RunPython or UDFs. Note that you don’t need

an add-in if you just want to manipulate Excel from Python via xlwings.

Note: The ribbon of the add-in is compatible with Excel >= 2007 on Windows and >= 2016 on Mac. You

could, however, use the add-in with earlier versions but you would need to change the settings directly in

the conﬁg ﬁle, see below. On Mac, all UDF related functionality is not available.

Note: The add-in is password protected with the password xlwings. For debugging or to add new

extensions, you need to unprotect it.

8.1 Installation

To install the add-in, it’s easiest to use the command line client: xlwings addin install.

Then, to use RunPython or UDFs in a workbook, you need to set a reference to xlwings in the VBA

editor, see screenshot (Windows: Tools > References..., Mac: it’s on the lower left corner of the

xlwings - Make Excel Fly!, Release dev

VBA editor). Note that when you create a workbook via xlwings quickstart, the reference is already

set.

8.2 Global Settings

While the defaults will often work out-of-the box, you can change the global settings directly in the add-in:

• Interpreter: This is the path to the Python interpreter (works also with virtual or conda envs),

e.g. "C:\Python35\pythonw.exe" or "/usr/local/bin/python3.5". An empty

ﬁeld defaults to pythonw that expects the interpreter to be set in the PATH on Windows or .

bash_profile on Mac.

• PYTHONPATH: If the source ﬁle of your code is not found, add the path here.

• UDF_MODULES: Names of Python modules (without .py extension) from which the UDFs are be-

ing imported. Separate multiple modules by “;”. Example: UDF_MODULES = "common_udfs;

myproject" The default imports a ﬁle in the same directory as the Excel spreadsheet with the same

name but ending in .py.

• Debug UDFs: Check this box if you want to run the xlwings COM server manually for debugging,

see Debugging.

• Log File: Leave empty for default location (see below) or provide the full path, e.g. .

• RunPython: Use UDF Server: Uses the same COM Server for RunPython as for UDFs. This

will be faster, as the interpreter doesn’t shut down after each call.

• Restart UDF Server: This shuts down the UDF Server/Python interpreter. It’ll be restarted

upon the next function call.

8.3 Global Conﬁg: Ribbon/Conﬁg File

The settings in the xlwings Ribbon are stored in a conﬁg ﬁle that can also be manipulated externally. The

location is

• Windows: .xlwings\xlwings.conf in your user folder

• Mac Excel 2016: ~/Library/Containers/com.microsoft.Excel/Data/xlwings.

conf

24 Chapter 8. Add-in

xlwings - Make Excel Fly!, Release dev

# Mac Excel 2011: ~/.xlwings/xlwings.conf

The format is as follows (keys are uppercase):

"INTERPRETER","pythonw"

"PYTHONPATH",""

Note: Mac Excel 2011 users have to create and edit the conﬁg ﬁle manually under ~/.xlwings/

xlwings.conf as the ribbon is not supported.

8.4 Workbook Directory Conﬁg: Conﬁg ﬁle

The global settings of the Ribbon/Conﬁg ﬁle can be overridden for one or more workbooks by creating a

xlwings.conf ﬁle in the workbook’s directory.

8.5 Workbook Conﬁg: xlwings.conf Sheet

Workbook speciﬁc settings will override global (Ribbon) and workbook directory conﬁg ﬁles: Workbook

speciﬁc settings are set by listing the conﬁg key/value pairs in a sheet with the name xlwings.conf.

When you create a new project with xlwings quickstart, it’ll already have such a sheet but you need

to rename it to xlwings.conf to make it active.

8.6 Alternative: Standalone VBA module

Sometimes it might be useful to run xlwings code without having to install an add-in ﬁrst. To do so, you need

to use the standalone option when creating a new project: xlwings quickstart myproject

--standalone.

This will add the content of the add-in as a single VBA module so you don’t need to set a reference to the

add-in anymore. It will still read in the settings from your xlwings.conf if you don’t override them by

using a sheet with the name xlwings.conf.

8.7 Log File default locations

These log ﬁles are used for the error pop-up windows:

8.4. Workbook Directory Conﬁg: Conﬁg ﬁle 25

xlwings - Make Excel Fly!, Release dev

• Windows: %APPDATA%\xlwings.log

• Mac with Excel 2011: /tmp/xlwings.log

• Mac with Excel 2016: ~/Library/Containers/com.microsoft.Excel/Data/

xlwings.log

26 Chapter 8. Add-in

CHAPTER 9

VBA: RunPython

9.1 xlwings add-in

To get access to the RunPython function, you’ll need the xlwings addin (or VBA module), see Add-in.

For new projects, the easiest way to get started is by using the command line client with the quickstart

command, see Command Line Client for details:

$ xlwings quickstart myproject

9.2 Call Python with “RunPython”

In the VBA Editor (Alt-F11), write the code below into a VBA module. xlwings quickstart

automatically adds a new module with a sample call. If you rather want to start from scratch, you can add

new module via Insert > Module.

Sub HelloWorld()

RunPython ("import hello; hello.world()")

End Sub

This calls the following code in hello.py:

# hello.py

import numpy as np

import xlwings as xw

def world():

wb = xw.Book.caller()

wb.sheets[0].range('A1').value = 'Hello World!'

xlwings - Make Excel Fly!, Release dev

You can then attach HelloWorld to a button or run it directly in the VBA Editor by hitting F5.

Note: Place xw.Book.caller() within the function that is being called from Excel and not outside as

global variable. Otherwise it prevents Excel from shutting down properly upon exiting and leaves you with

a zombie process when you use OPTIMIZED_CONNECTION = True.

9.3 Function Arguments and Return Values

While it’s technically possible to include arguments in the function call within RunPython, it’s not very

convenient. Also, RunPython does not allow you to return values. To overcome these issue, use UDFs,

see VBA: User Deﬁned Functions (UDFs) - however, this is currently limited to Windows only.

28 Chapter 9. VBA: RunPython

CHAPTER 10

VBA: User Deﬁned Functions (UDFs)

This tutorial gets you quickly started on how to write User Deﬁned Functions.

Note:

• UDFs are currently only available on Windows.

• For details of how to control the behaviour of the arguments and return values, have a look at Con-

verters and Options.

• For a comprehensive overview of the available decorators and their options, check out the correspond-

ing API docs: UDF decorators.

10.1 One-time Excel preparations

1) Enable Trust access to the VBA project object model under File > Options >

Trust Center > Trust Center Settings > Macro Settings

2. Install the add-in via command prompt: xlwings addin install (see Add-in).

10.2 Workbook preparation

The easiest way to start a new project is to run xlwings quickstart myproject on a command

prompt (see Command Line Client). This automatically adds the xlwings reference to the generated work-

book.

xlwings - Make Excel Fly!, Release dev

10.3 A simple UDF

The default addin settings expect a Python source ﬁle in the way it is created by quickstart:

• in the same directory as the Excel ﬁle

• with the same name as the Excel ﬁle, but with a .py ending instead of .xlsm.

Alternatively, you can point to a speciﬁc module via UDF Modules in the xlwings ribbon.

Let’s assume you have a Workbook myproject.xlsm, then you would write the following code in

myproject.py:

import xlwings as xw

@xw.func

def double_sum(x, y):

"""Returns twice the sum of the two arguments"""

return 2

(x + y)

• Now click on Import Python UDFs in the xlwings tab to pick up the changes made to

myproject.py.

• Enter the formula =double_sum(1, 2) into a cell and you will see the correct result:

• The docstring (in triple-quotes) will be shown as function description in Excel.

Note:

• You only need to re-import your functions if you change the function arguments or the function name.

• Code changes in the actual functions are picked up automatically (i.e. at the next calculation of the

formula, e.g. triggered by Ctrl-Alt-F9), but changes in imported modules are not. This is the

very behaviour of how Python imports work. If you want to make sure everything is in a fresh state,

click Restart UDF Server.

• The @xw.func decorator is only used by xlwings when the function is being imported into Excel. It

tells xlwings for which functions it should create a VBA wrapper function, otherwise it has no effect

on how the functions behave in Python.

10.4 Array formulas: Get efﬁcient

Calling one big array formula in Excel is much more efﬁcient than calling many single-cell formulas, so it’s

generally a good idea to use them, especially if you hit performance problems.

30 Chapter 10. VBA: User Deﬁned Functions (UDFs)

xlwings - Make Excel Fly!, Release dev

You can pass an Excel Range as a function argument, as opposed to a single cell and it will show up in

Python as list of lists.

For example, you can write the following function to add 1 to every cell in a Range:

@xw.func

def add_one(data):

return [[cell + 1 for cell in row] for row in data]

To use this formula in Excel,

• Click on Import Python UDFs again

• Fill in the values in the range A1:B2

• Select the range D1:E2

• Type in the formula =add_one(A1:B2)

• Press Ctrl+Shift+Enter to create an array formula. If you did everything correctly, you’ll see

the formula surrounded by curly braces as in this screenshot:

10.4.1 Number of array dimensions: ndim

The above formula has the issue that it expects a “two dimensional” input, e.g. a nested list of the form [[1,

2], [3, 4]]. Therefore, if you would apply the formula to a single cell, you would get the following

error: TypeError: 'float' object is not iterable.

To force Excel to always give you a two-dimensional array, no matter whether the argument is a single cell,

a column/row or a two-dimensional Range, you can extend the above formula like this:

@xw.func

@xw.arg('data', ndim=2)

def add_one(data):

return [[cell + 1 for cell in row] for row in data]

10.5 Array formulas with NumPy and Pandas

Often, you’ll want to use NumPy arrays or Pandas DataFrames in your UDF, as this unlocks the full power

of Python’s ecosystem for scientiﬁc computing.

To deﬁne a formula for matrix multiplication using numpy arrays, you would deﬁne the following function:

10.5. Array formulas with NumPy and Pandas 31

xlwings - Make Excel Fly!, Release dev

import xlwings as xw

import numpy as np

@xw.func

@xw.arg('x', np.array, ndim=2)

@xw.arg('y', np.array, ndim=2)

def matrix_mult(x, y):

return x @ y

Note: If you are not on Python >= 3.5 with NumPy >= 1.10, use x.dot(y) instead of x @ y.

A great example of how you can put Pandas at work is the creation of an array-based CORREL formula.

Excel’s version of CORREL only works on 2 datasets and is cumbersome to use if you want to quickly

get the correlation matrix of a few time-series, for example. Pandas makes the creation of an array-based

CORREL2 formula basically a one-liner:

import xlwings as xw

import pandas as pd

@xw.func

@xw.arg('x', pd.DataFrame, index=False, header=False)

@xw.ret(index=False, header=False)

def CORREL2(x):

"""Like CORREL, but as array formula for more than 2 data sets"""

return x.corr()

10.6 @xw.arg and @xw.ret decorators

These decorators are to UDFs what the options method is to Range objects: they allow you to apply

converters and their options to function arguments (@xw.arg) and to the return value (@xw.ret). For

example, to convert the argument x into a pandas DataFrame and suppress the index when returning it, you

would do the following:

@xw.func

@xw.arg('x', pd.DataFrame)

@xw.ret(index=False)

def myfunction(x):

# x is a DataFrame, do something with it

return x

For further details see the Converters and Options documentation.

10.7 Dynamic Array Formulas

As seen above, to use Excel’s array formulas, you need to specify their dimensions up front by selecting

the result array ﬁrst, then entering the formula and ﬁnally hitting Ctrl-Shift-Enter. While this makes

32 Chapter 10. VBA: User Deﬁned Functions (UDFs)

xlwings - Make Excel Fly!, Release dev

sense from a data integrity point of view, in practice, it often turns out to be a cumbersome limitation,

especially when working with dynamic arrays such as time series data. Since v0.10, xlwings offers dynamic

UDF expansion:

This is a simple example that demonstrates the syntax and effect of UDF expansion:

import numpy as np

@xw.func

@xw.ret(expand='table')

def dynamic_array(r, c):

return np.random.randn(int(r), int(c))

Note:

• Expanding array formulas will overwrite cells without prompting and leave an empty border around

them, i.e. they will clear the row to the bottom and the column to the right of the array.

• The way that dynamic array formulas are currently implemented doesn’t allow them to have volatile

functions as arguments, e.g. you cannot use functions like =TODAY() as arguments.

10.8 Docstrings

The following sample shows how to include docstrings both for the function and for the arguments x and y

that then show up in the function wizard in Excel:

import xlwings as xw

@xw.func

(continues on next page)

10.8. Docstrings 33

xlwings - Make Excel Fly!, Release dev

(continued from previous page)

@xw.arg('x', doc='This is x.')

@xw.arg('y', doc='This is y.')

def double_sum(x, y):

"""Returns twice the sum of the two arguments"""

return 2

(x + y)

10.9 The “vba” keyword

It’s often helpful to get the address of the calling cell. Right now, one of the easiest ways to accomplish

this is to use the vba keyword. vba, in fact, allows you to access any available VBA expression e.g.

Application. Note, however, that currently you’re acting directly on the pywin32 COM object:

@xw.func

@xw.arg('xl_app', vba='Application')

def get_caller_address(xl_app):

return xl_app.Caller.Address

10.10 Macros

On Windows, as alternative to calling macros via RunPython, you can also use the @xw.sub decorator:

import xlwings as xw

@xw.sub

def my_macro():

"""Writes the name of the Workbook into Range("A1") of Sheet 1"""

wb = xw.Book.caller()

wb.sheets[0].range('A1').value = wb.name

After clicking on Import Python UDFs, you can then use this macro by executing it via Alt + F8 or

by binding it e.g. to a button. To to the latter, make sure you have the Developer tab selected under File

> Options > Customize Ribbon. Then, under the Developer tab, you can insert a button via

Insert > Form Controls. After drawing the button, you will be prompted to assign a macro to it

and you can select my_macro.

10.11 Call UDFs from VBA

Imported functions can also be used from VBA. They return a 2d array:

Sub MySub()

Dim arr() As Variant

Dim i As Long, j As Long

(continues on next page)

34 Chapter 10. VBA: User Deﬁned Functions (UDFs)

xlwings - Make Excel Fly!, Release dev

(continued from previous page)

arr = my_imported_function(...)

For j = LBound(arr, 2) To UBound(arr, 2)

For i = LBound(arr, 1) To UBound(arr, 1)

Debug.Print "(" & i & "," & j & ")", arr(i, j)

Next i

Next j

End Sub

10.11. Call UDFs from VBA 35

xlwings - Make Excel Fly!, Release dev

36 Chapter 10. VBA: User Deﬁned Functions (UDFs)

CHAPTER 11

Debugging

Since xlwings runs in every Python environment, you can use your preferred way of debugging.

• RunPython: When calling Python through RunPython, you can set a mock_caller to make it

easy to switch back and forth between calling the function from Excel and Python.

• UDFs: For debugging User Deﬁned Functions, xlwings offers a convenient debugging server

To begin with, Excel will show Python errors in a Message Box:

Note: On Mac, if the import of a module/package fails before xlwings is imported, the popup will not

be shown and the StatusBar will not be reset. However, the error will still be logged in the log ﬁle. For the

location of the logﬁle, see Log File default locations.

xlwings - Make Excel Fly!, Release dev

11.1 RunPython

Consider the following sample code of your Python source code my_module.py:

# my_module.py

import os

import xlwings as xw

def my_macro():

wb = xw.Book.caller()

wb.sheets[0].range('A1').value = 1

if __name__ == '__main__':

# Expects the Excel file next to this source file, adjust accordingly.

xw.Book('myfile.xlsm').set_mock_caller()

my_macro()

my_macro() can now easily be run from Python for debugging and from Excel via RunPython without

having to change the source code:

Sub my_macro()

RunPython ("import my_module; my_module.my_macro()")

End Sub

11.2 UDF debug server

Windows only: To debug UDFs, just check the Debug UDFs in the Add-in, at the top of the xlwings VBA

module. Then add the following lines at the end of your Python source ﬁle and run it. Depending on which

IDE you use, you might need to run the code in “debug” mode (e.g. in case you’re using PyCharm or

PyDev):

if __name__ == '__main__':

xw.serve()

When you recalculate the Sheet (Ctrl-Alt-F9), the code will stop at breakpoints or output any print calls

that you may have.

The following screenshot shows the code stopped at a breakpoint in the community version of PyCharm:

Note: When running the debug server from a command prompt, there is currently no gracious way to

terminate it, but closing the command prompt will kill it.

38 Chapter 11. Debugging

xlwings - Make Excel Fly!, Release dev

11.2. UDF debug server 39

xlwings - Make Excel Fly!, Release dev

40 Chapter 11. Debugging

CHAPTER 12

Matplotlib

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

12.1 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)

Note: 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.

12.2 Full integration with Excel

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

platform.

xlwings - Make Excel Fly!, Release dev

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:

12.3 Properties

Size, position and other properties can either be set as arguments within pictures.add(), or by manip-

ulating 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

42 Chapter 12. Matplotlib

xlwings - Make Excel Fly!, Release dev

12.3. Properties 43

xlwings - Make Excel Fly!, Release dev

12.4 Getting a Matplotlib ﬁgure

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()

44 Chapter 12. Matplotlib

CHAPTER 13

Converters and Options

Introduced with v0.7.0, converters deﬁne how Excel ranges and their values are converted both during

reading and writing operations. They also provide a consistent experience across xlwings.Range objects

and User Deﬁned Functions (UDFs).

Converters are explicitely set in the options method when manipulating Range objects or in the @xw.

arg and @xw.ret decorators when using UDFs. If no converter is speciﬁed, the default converter is

applied when reading. When writing, xlwings will automatically apply the correct converter (if available)

according to the object’s type that is being written to Excel. If no converter is found for that type, it falls

back to the default converter.

All code samples below depend on the following import:

>>> import xlwings as xw

Syntax:

xw.Range UDFs

read-

ing

xw.Range.options(convert=None,

kwargs).value

@arg('x',

convert=None,

kwargs)

writ-

ing

xw.Range.options(convert=None,

kwargs).value = myvalue

@ret(convert=None,

kwargs)

Note: Keyword arguments (kwargs) may refer to the speciﬁc converter or the default converter. For

example, to set the numbers option in the default converter and the index option in the DataFrame

converter, you would write:

xlwings - Make Excel Fly!, Release dev

xw.Range('A1:C3').options(pd.DataFrame, index=False, numbers=int).value

13.1 Default Converter

If no options are set, the following conversions are performed:

• single cells are read in as floats in case the Excel cell holds a number, as unicode in case it holds

text, as datetime if it contains a date and as None in case it is empty.

• columns/rows are read in as lists, e.g. [None, 1.0, 'a string']

• 2d cell ranges are read in as list of lists, e.g. [[None, 1.0, 'a string'], [None, 2.0,

'another string']]

The following options can be set:

• ndim

Force the value to have either 1 or 2 dimensions regardless of the shape of the range:

>>> import xlwings as xw

>>> sht = xw.Book().sheets[0]

>>> sht.range('A1').value = [[1, 2], [3, 4]]

>>> sht.range('A1').value

1.0

>>> sht.range('A1').options(ndim=1).value

[1.0]

>>> sht.range('A1').options(ndim=2).value

[[1.0]]

>>> sht.range('A1:A2').value

[1.0 3.0]

>>> sht.range('A1:A2').options(ndim=2).value

[[1.0], [3.0]]

• numbers

By default cells with numbers are read as float, but you can change it to int:

>>> sht.range('A1').value = 1

>>> sht.range('A1').value

1.0

>>> sht.range('A1').options(numbers=int).value

Alternatively, you can specify any other function or type which takes a single ﬂoat argument.

Using this on UDFs looks like this:

@xw.func

@xw.arg('x', numbers=int)

def myfunction(x):

(continues on next page)

46 Chapter 13. Converters and Options

xlwings - Make Excel Fly!, Release dev

(continued from previous page)

# all numbers in x arrive as int

return x

Note: Excel always stores numbers internally as ﬂoats, which is the reason why the int converter

rounds numbers ﬁrst before turning them into integers. Otherwise it could happen that e.g. 5 might be

returned as 4 in case it is represented as a ﬂoating point number that is slightly smaller than 5. Should

you require Python’s original int in your converter, use raw int instead.

• dates

By default cells with dates are read as datetime.datetime, but you can change it to datetime.

date:

– Range:

>>> import datetime as dt

>>> sht.range('A1').options(dates=dt.date).value

– UDFs: @xw.arg('x', dates=dt.date)

Alternatively, you can specify any other function or type which takes the same keyword arguments as

datetime.datetime, for example:

>>> my_date_handler = lambda year, month, day,

kwargs: "%04i-%02i-%02i

˓→" % (year, month, day)

>>> sht.range('A1').options(dates=my_date_handler).value

'2017-02-20'

• empty

Empty cells are converted per default into None, you can change this as follows:

– Range: >>> sht.range('A1').options(empty='NA').value

– UDFs: @xw.arg('x', empty='NA')

• transpose

This works for reading and writing and allows us to e.g. write a list in column orientation to Excel:

– Range: sht.range('A1').options(transpose=True).value = [1, 2, 3]

– UDFs:

@xw.arg('x', transpose=True)

@xw.ret(transpose=True)

def myfunction(x):

# x will be returned unchanged as transposed both when reading

˓→and writing

return x

• expand

This works the same as the Range properties table, vertical and horizontal but is only

evaluated when getting the values of a Range:

13.1. Default Converter 47

xlwings - Make Excel Fly!, Release dev

>>> import xlwings as xw

>>> sht = xw.Book().sheets[0]

>>> sht.range('A1').value = [[1,2], [3,4]]

>>> rng1 = sht.range('A1').expand()

>>> rng2 = sht.range('A1').options(expand='table')

>>> rng1.value

[[1.0, 2.0], [3.0, 4.0]]

>>> rng2.value

[[1.0, 2.0], [3.0, 4.0]]

>>> sht.range('A3').value = [5, 6]

>>> rng1.value

[[1.0, 2.0], [3.0, 4.0]]

>>> rng2.value

[[1.0, 2.0], [3.0, 4.0], [5.0, 6.0]]

Note: The expand method is only available on Range objects as UDFs only allow to manipulate

the calling cells.

13.2 Built-in Converters

xlwings offers several built-in converters that perform type conversion to dictionaries, NumPy arrays,

Pandas Series and DataFrames. These build on top of the default converter, so in most cases the options

described above can be used in this context, too (unless they are meaningless, for example the ndim in the

case of a dictionary).

It is also possible to write and register custom converter for additional types, see below.

The samples below can be used with both xlwings.Range objects and UDFs even though only one

version may be shown.

13.2.1 Dictionary converter

The dictionary converter turns two Excel columns into a dictionary. If the data is in row orientation, use

transpose:

>>> sht = xw.sheets.active

>>> sht.range('A1:B2').options(dict).value

{'a': 1.0, 'b': 2.0}

(continues on next page)

48 Chapter 13. Converters and Options

xlwings - Make Excel Fly!, Release dev

(continued from previous page)

>>> sht.range('A4:B5').options(dict, transpose=True).value

{'a': 1.0, 'b': 2.0}

13.2.2 Numpy array converter

options: dtype=None, copy=True, order=None, ndim=None

The ﬁrst 3 options behave the same as when using np.array() directly. Also, ndim works the same

as shown above for lists (under default converter) and hence returns either numpy scalars, 1d arrays or 2d

arrays.

Example:

>>> import numpy as np

>>> sht = xw.Book().sheets[0]

>>> sht.range('A1').options(transpose=True).value = np.array([1, 2, 3])

>>> sht.range('A1:A3').options(np.array, ndim=2).value

array([[ 1.],

[ 2.],

[ 3.]])

13.2.3 Pandas Series converter

options: dtype=None, copy=False, index=1, header=True

The ﬁrst 2 options behave the same as when using pd.Series() directly. ndim doesn’t have an effect

on Pandas series as they are always expected and returned in column orientation.

index: int or Boolean

When reading, it expects the number of index columns shown in Excel.

When writing, include or exclude the index by setting it to True or False.

header: Boolean

When reading, set it to False if Excel doesn’t show either index or series names.

When writing, include or exclude the index and series names by setting it to True or False.

For index and header, 1 and True may be used interchangeably.

Example:

>>> sht = xw.Book().sheets[0]

>>> s = sht.range('A1').options(pd.Series, expand='table').value

>>> s

date

2001-01-01 1

2001-01-02 2

2001-01-03 3

2001-01-04 4

(continues on next page)

13.2. Built-in Converters 49

xlwings - Make Excel Fly!, Release dev

(continued from previous page)

2001-01-05 5

2001-01-06 6

Name: series name, dtype: float64

>>> sht.range('D1', header=False).value = s

13.2.4 Pandas DataFrame converter

options: dtype=None, copy=False, index=1, header=1

The ﬁrst 2 options behave the same as when using pd.DataFrame() directly. ndim doesn’t have an

effect on Pandas DataFrames as they are automatically read in with ndim=2.

index: int or Boolean

When reading, it expects the number of index columns shown in Excel.

When writing, include or exclude the index by setting it to True or False.

header: int or Boolean

When reading, it expects the number of column headers shown in Excel.

When writing, include or exclude the index and series names by setting it to True or False.

For index and header, 1 and True may be used interchangeably.

Example:

>>> sht = xw.Book().sheets[0]

>>> df = sht.range('A1:D5').options(pd.DataFrame, header=2).value

>>> df

a b

c d e

10 1 2 3

20 4 5 6

30 7 8 9

# Writing back using the defaults:

>>> sht.range('A1').value = df

(continues on next page)

50 Chapter 13. Converters and Options

xlwings - Make Excel Fly!, Release dev

(continued from previous page)

# Writing back and changing some of the options, e.g. getting rid of the

˓→index:

>>> sht.range('B7').options(index=False).value = df

The same sample for UDF (starting in Range('A13') on screenshot) looks like this:

@xw.func

@xw.arg('x', pd.DataFrame, header=2)

@xw.ret(index=False)

def myfunction(x):

# x is a DataFrame, do something with it

return x

13.2.5 xw.Range and ‘raw’ converters

Technically speaking, these are “no-converters”.

• If you need access to the xlwings.Range object directly, you can do:

@xw.func

@xw.arg('x', xw.Range)

def myfunction(x):

return x.formula

This returns x as xlwings.Range object, i.e. without applying any converters or options.

• The raw converter delivers the values unchanged from the underlying libraries (pywin32 on Win-

dows and appscript on Mac), i.e. no sanitizing/cross-platform harmonizing of values are being

made. This might be useful in a few cases for efﬁciency reasons. E.g:

13.2. Built-in Converters 51

xlwings - Make Excel Fly!, Release dev

>>> sht.range('A1:B2').value

[[1.0, 'text'], [datetime.datetime(2016, 2, 1, 0, 0), None]]

>>> sht.range('A1:B2').options('raw').value # or sht.range('A1:B2').raw_

˓→value

((1.0, 'text'), (pywintypes.datetime(2016, 2, 1, 0, 0,

˓→tzinfo=TimeZoneInfo('GMT Standard Time', True)), None))

13.3 Custom Converter

Here are the steps to implement your own converter:

• Inherit from xlwings.conversion.Converter

• Implement both a read_value and write_value method as static- or classmethod:

– In read_value, value is what the base converter returns: hence, if no base has been spec-

iﬁed it arrives in the format of the default converter.

– In write_value, value is the original object being written to Excel. It must be returned

in the format that the base converter expects. Again, if no base has been speciﬁed, this is the

default converter.

The options dictionary will contain all keyword arguments speciﬁed in the xw.Range.options

method, e.g. when calling xw.Range('A1').options(myoption='some value') or as

speciﬁed in the @arg and @ret decorator when using UDFs. Here is the basic structure:

from xlwings.conversion import Converter

class MyConverter(Converter):

@staticmethod

def read_value(value, options):

myoption = options.get('myoption', default_value)

return_value = value # Implement your conversion here

return return_value

@staticmethod

def write_value(value, options):

myoption = options.get('myoption', default_value)

return_value = value # Implement your conversion here

return return_value

• Optional: set a base converter (base expects a class name) to build on top of an ex-

isting converter, e.g. for the built-in ones: DictCoverter, NumpyArrayConverter,

PandasDataFrameConverter, PandasSeriesConverter

• Optional: register the converter: you can (a) register a type so that your converter becomes the default

for this type during write operations and/or (b) you can register an alias that will allow you to explicitly

call your converter by name instead of just by class name

52 Chapter 13. Converters and Options

xlwings - Make Excel Fly!, Release dev

The following examples should make it much easier to follow - it deﬁnes a DataFrame converter that extends

the built-in DataFrame converter to add support for dropping nan’s:

from xlwings.conversion import Converter, PandasDataFrameConverter

class DataFrameDropna(Converter):

base = PandasDataFrameConverter

@staticmethod

def read_value(builtin_df, options):

dropna = options.get('dropna', False) # set default to False

if dropna:

converted_df = builtin_df.dropna()

else:

converted_df = builtin_df

# This will arrive in Python when using the DataFrameDropna converter

˓→for reading

return converted_df

@staticmethod

def write_value(df, options):

dropna = options.get('dropna', False)

if dropna:

converted_df = df.dropna()

else:

converted_df = df

# This will be passed to the built-in PandasDataFrameConverter when

˓→writing

return converted_df

Now let’s see how the different converters can be applied:

# Fire up a Workbook and create a sample DataFrame

sht = xw.Book().sheets[0]

df = pd.DataFrame([[1.,10.],[2.,np.nan], [3., 30.]])

• Default converter for DataFrames:

# Write

sht.range('A1').value = df

# Read

sht.range('A1:C4').options(pd.DataFrame).value

• DataFrameDropna converter:

# Write

sht.range('A7').options(DataFrameDropna, dropna=True).value = df

# Read

sht.range('A1:C4').options(DataFrameDropna, dropna=True).value

13.3. Custom Converter 53

xlwings - Make Excel Fly!, Release dev

• Register an alias (optional):

DataFrameDropna.register('df_dropna')

# Write

sht.range('A12').options('df_dropna', dropna=True).value = df

# Read

sht.range('A1:C4').options('df_dropna', dropna=True).value

• Register DataFrameDropna as default converter for DataFrames (optional):

DataFrameDropna.register(pd.DataFrame)

# Write

sht.range('A13').options(dropna=True).value = df

# Read

sht.range('A1:C4').options(pd.DataFrame, dropna=True).value

These samples all work the same with UDFs, e.g.:

@xw.func

@arg('x', DataFrameDropna, dropna=True)

@ret(DataFrameDropna, dropna=True)

def myfunction(x):

# ...

return x

Note: Python objects run through multiple stages of a transformation pipeline when they are being written

to Excel. The same holds true in the other direction, when Excel/COM objects are being read into Python.

Pipelines are internally deﬁned by Accessor classes. A Converter is just a special Accessor which converts

to/from a particular type by adding an extra stage to the pipeline of the default Accessor. For example, the

PandasDataFrameConverter deﬁnes how a list of list (as delivered by the default Accessor) should

be turned into a Pandas DataFrame.

The Converter class provides basic scaffolding to make the task of writing a new Converter easier. If you

need more control you can subclass Accessor directly, but this part requires more work and is currently

undocumented.

54 Chapter 13. Converters and Options

CHAPTER 14

Command Line Client

xlwings comes with a command line client that makes it easy to set up workbooks and install the add-in. On

Windows, type the commands into a Command Prompt, on Mac, type them into a Terminal.

14.1 Quickstart

• xlwings quickstart myproject

This command is by far the fastest way to get off the ground: It creates a new folder myproject with an

Excel workbook that already has the reference to the xlwings addin and a Python ﬁle, ready to be used right

away:

myproject

|--myproject.xlsm

|--myproject.py

If you want to use xlwings via VBA module instead of addin, use the --standalone or -s ﬂag:

xlwings quickstart myproject --standalone

14.2 Add-in

The addin command makes it easy on Windows to install/remove the addin. On Mac, you need to install it

manually, but xlwings addin install will show you how to do it.

Note: Excel needs to be closed before installing/updating the add-in via command line. If you’re still

getting an error, start the Task Manager and make sure there are no EXCEL.EXE processes left.

xlwings - Make Excel Fly!, Release dev

• xlwings addin install: Copies the xlwings add-in to the XLSTART folder

• xlwings addin update: Replaces the current add-in with the latest one

• xlwings addin remove: Removes the add-in from the XLSTART folder

• xlwings addin status: Shows if the add-in is installed together with the installation path

After installing the add-in, it will be available as xlwings tab on the Excel Ribbon.

New in version 0.6.0.

14.3 RunPython

Only required if you are on Mac, are using Excel 2016 and have xlwings installed via conda or as part of

Anaconda. To enable the RunPython calls in VBA, run this one time:

xlwings runpython install

Alternatively, install xlwings with pip.

New in version 0.7.0.

56 Chapter 14. Command Line Client

CHAPTER 15

Missing Features

If you’re missing a feature in xlwings, do the following:

1. Most importantly, open an issue on GitHub. If it’s something bigger or if you want support from other

users, consider opening a feature request. Adding functionality should be user driven, so only if you

tell us about what you’re missing, it’s eventually going to ﬁnd its way into the library. By the way, we

also appreciate pull requests!

2. Workaround: in essence, xlwings is just a smart wrapper around pywin32 on Windows and appscript

on Mac. You can access the underlying objects by calling the api property:

>>> sht = xw.Book().sheets[0]

>>> sht.api

<COMObject <unknown>> # Windows/pywin32

app(pid=2319).workbooks['Workbook1'].worksheets[1] # Mac/appscript

This works accordingly for the other objects like sht.range('A1').api etc.

The underlying objects will offer you pretty much everything you can do with VBA, using the syntax

of pywin32 (which pretty much feels like VBA) and appscript (which doesn’t feel like VBA). But

apart from looking ugly, keep in mind that it makes your code platform speciﬁc (!), i.e. even if you

go for option 2), you should still follow option 1) and open an issue so the feature ﬁnds it’s way into

the library (cross-platform and with a Pythonic syntax).

15.1 Example: Workaround to use VBA’s Range.WrapText

# Windows

sht.range('A1').api.WrapText = True

(continues on next page)

xlwings - Make Excel Fly!, Release dev

(continued from previous page)

# Mac

sht.range('A1').api.wrap_text.set(True)

58 Chapter 15. Missing Features

CHAPTER 16

xlwings with R and Julia

While xlwings is a pure Python package, there are cross-language packages that allow for a relative straight-

forward use from/with other languages. This means, however, that you’ll always need to have Python with

xlwings installed in addition to R or Julia. We recommend the Anaconda distribution, see also Installation.

16.1 R

The R instructions are for Windows, but things work accordingly on Mac except that calling the R functions

as User Deﬁned Functions is not supported at the moment (but RunPython works, see Call Python with

“RunPython”).

Setup:

• Install R and Python

• Add R_HOME environment variable to base directory of installation, .e.g C:\Program

Files\R\R-x.x.x

• Add R_USER environment variable to user folder, e.g. C:\Users\<user>

• Add C:\Program Files\R\R-x.x.x\bin to PATH

• Restart Windows because of the environment variables (!)

16.1.1 Simple functions with R

Original R function that we want to access from Excel (saved in r_file.R):

xlwings - Make Excel Fly!, Release dev

myfunction <- function(x, y){

return(x

}

Python wrapper code:

import xlwings as xw

import rpy2.robjects as robjects

# you might want to use some relative path or place the file in R's current

˓→working dir

robjects.r.source(r"C:\path\to\r_file.R")

@xw.func

def myfunction(x, y):

myfunc = robjects.r['myfunction']

return tuple(myfunc(x, y))

After importing this function (see: VBA: User Deﬁned Functions (UDFs)), it will be available as UDF from

Excel.

16.1.2 Array functions with R

Original R function that we want to access from Excel (saved in r_file.R):

array_function <- function(m1, m2){

# Matrix multiplication

return(m1 %

% m2)

}

Python wrapper code:

import xlwings as xw

import numpy as np

import rpy2.robjects as robjects

from rpy2.robjects import numpy2ri

robjects.r.source(r"C:\path\to\r_file.R")

numpy2ri.activate()

@xw.func

@xw.arg("x", np.array, ndim=2)

@xw.arg("y", np.array, ndim=2)

def array_function(x, y):

array_func = robjects.r['array_function']

return np.array(array_func(x, y))

After importing this function (see: VBA: User Deﬁned Functions (UDFs)), it will be available as UDF from

Excel.

60 Chapter 16. xlwings with R and Julia

xlwings - Make Excel Fly!, Release dev

16.2 Julia

Setup:

• Install Julia and Python

• Run Pkg.add("PyCall") from an interactive Julia interpreter

xlwings can then be called from Julia with the following syntax (the colons take care of automatic type

conversion):

julia> using PyCall

julia> @pyimport xlwings as xw

julia> xw.Book()

PyObject <Book [Workbook1]>

julia> xw.Range("A1")[:value] = "Hello World"

julia> xw.Range("A1")[:value]

"Hello World"

16.2. Julia 61

xlwings - Make Excel Fly!, Release dev

62 Chapter 16. xlwings with R and Julia

CHAPTER 17

Extensions

It’s easy to extend the xlwings add-in with own code like UDFs or RunPython macros, so that they can

be deployed without end users having to import or write the functions themselves. Just add another VBA

module to the xlwings addin with the respective code.

UDF extensions can be used from every workbook without having to set a reference.

17.1 In-Excel SQL

The xlwings addin comes with a built-in extension that adds in-Excel SQL syntax (sqlite dialect):

=sql(SQL Statement, table a, table b, ...)

xlwings - Make Excel Fly!, Release dev

As this extension uses UDFs, it’s only available on Windows right now.

64 Chapter 17. Extensions

CHAPTER 18

Troubleshooting

18.1 Issue: dll not found

Solution:

1. xlwings32-<version>.dll and xlwings64-<version>.dll are both in the same direc-

tory as your python.exe. If not, something went wrong with your installation. Reinstall it with

pip or conda, see Installation.

2. Check your Interpreter in the add-in or conﬁg sheet. If it is empty, then you need to be

able to open a windows command prompt and type python to start an interactive Python session.

If you get the error 'python' is not recognized as an internal or external

command, operable program or batch file., then you have two options: Either add

the path of where your python.exe lives to your Windows path (see https://www.computerhope.

com/issues/ch000549.htm) or set the full path to your interpreter in the add-in or your conﬁg sheet,

e.g. C:\Users\MyUser\anaconda\pythonw.exe

xlwings - Make Excel Fly!, Release dev

66 Chapter 18. Troubleshooting

CHAPTER 19

API Documentation

19.1 Top-level functions

xlwings.view(obj, sheet=None)

Opens a new workbook and displays an object on its ﬁrst sheet by default. If you provide a sheet

object, it will clear the sheet before displaying the object on the existing sheet.

Parameters

• obj (any type with built-in converter) – the object to display,

e.g. numbers, strings, lists, numpy arrays, pandas dataframes

• sheet (Sheet, default None) – Sheet object. If none provided, the ﬁrst

sheet of a new workbook is used.

Examples

>>> import xlwings as xw

>>> import pandas as pd

>>> import numpy as np

>>> df = pd.DataFrame(np.random.rand(10, 4), columns=['a', 'b', 'c', 'd

˓→'])

>>> xw.view(df)

New in version 0.7.1.

xlwings - Make Excel Fly!, Release dev

19.2 Object model

19.2.1 Apps

class xlwings.main.Apps(impl)

A collection of all app objects:

>>> import xlwings as xw

>>> xw.apps

Apps([<Excel App 1668>, <Excel App 1644>])

active

Returns the active app.

New in version 0.9.0.

add()

Creates a new App. The new App becomes the active one. Returns an App object.

count

Returns the number of apps.

New in version 0.9.0.

19.2.2 App

class xlwings.App(visible=None, spec=None, add_book=True, impl=None)

An app corresponds to an Excel instance. New Excel instances can be ﬁred up like so:

>>> import xlwings as xw

>>> app1 = xw.App()

>>> app2 = xw.App()

An app object is a member of the apps collection:

>>> xw.apps

Apps([<Excel App 1668>, <Excel App 1644>])

>>> xw.apps[0]

>>> xw.apps.active

Parameters

• visible (bool, default None) – Returns or sets a boolean value that

determines whether the app is visible. The default leaves the state unchanged or

sets visible=True if the object doesn’t exist yet.

• spec (str, default None) – Mac-only, use the full path to the Excel appli-

cation, e.g. /Applications/Microsoft Office 2011/Microsoft

Excel or /Applications/Microsoft Excel

68 Chapter 19. API Documentation

xlwings - Make Excel Fly!, Release dev

On Windows, if you want to change the version of Excel that xlwings talks to, go

to Control Panel > Programs and Features and Repair the Of-

ﬁce version that you want as default.

Note: On Mac, while xlwings allows you to run multiple instances of Excel, it’s a feature that is

not ofﬁcially supported by Excel for Mac: Unlike on Windows, Excel will not ask you to open a

read-only version of a ﬁle if it is already open in another instance. This means that you need to watch

out yourself so that the same ﬁle is not being overwritten from different instances.

activate(steal_focus=False)

Activates the Excel app.

Parameters steal_focus (bool, default False) – If True, make front-

most application and hand over focus from Python to Excel.

New in version 0.9.0.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

New in version 0.9.0.

books

A collection of all Book objects that are currently open.

New in version 0.9.0.

calculate()

Calculates all open books.

New in version 0.3.6.

calculation

Returns or sets a calculation value that represents the calculation mode. Modes: 'manual',

'automatic', 'semiautomatic'

Examples

>>> import xlwings as xw

>>> wb = xw.Book()

>>> wb.app.calculation = 'manual'

Changed in version 0.9.0.

display_alerts

The default value is True. Set this property to False to suppress prompts and alert messages

while code is running; when a message requires a response, Excel chooses the default response.

New in version 0.9.0.

19.2. Object model 69

xlwings - Make Excel Fly!, Release dev

hwnd

Returns the Window handle (Windows-only).

New in version 0.9.0.

kill()

Forces the Excel app to quit by killing its process.

New in version 0.9.0.

macro(name)

Runs a Sub or Function in Excel VBA that are not part of a speciﬁc workbook but e.g. are part

of an add-in.

Parameters name (Name of Sub or Function with or without module name, e.g.

'Module1.MyMacro' or 'MyMacro') –

Examples

This VBA function:

Function MySum(x, y)

MySum = x + y

End Function

can be accessed like this:

>>> import xlwings as xw

>>> app = xw.App()

>>> my_sum = app.macro('MySum')

>>> my_sum(1, 2)

See also: Command Line Client

New in version 0.3.3.

save(path=None)

Saves the Workbook. If a path is being provided, this works like SaveAs() in Excel. If no path

is speciﬁed and if the ﬁle hasn’t been saved previously, it’s being saved in the current working

directory with the current ﬁlename. Existing ﬁles are overwritten without prompting.

Parameters path (str, default None) – Full path to the workbook

Example

>>> import xlwings as xw

>>> wb = xw.Book()

>>> wb.save()

>>> wb.save(r'C:\path\to\new_file_name.xlsx')

New in version 0.3.1.

selection

Returns the selected cells as Range.

New in version 0.9.0.

set_mock_caller()

Sets the Excel ﬁle which is used to mock xw.Book.caller() when the code is called from

Python and not from Excel via RunPython.

Examples

# This code runs unchanged from Excel via RunPython and from Python

˓→directly

import os

import xlwings as xw

def my_macro():

sht = xw.Book.caller().sheets[0]

sht.range('A1').value = 'Hello xlwings!'

if __name__ == '__main__':

xw.Book('file.xlsm').set_mock_caller()

my_macro()

New in version 0.3.1.

74 Chapter 19. API Documentation

xlwings - Make Excel Fly!, Release dev

sheets

Returns a sheets collection that represents all the sheets in the book.

New in version 0.9.0.

19.2.5 Sheets

class xlwings.main.Sheets(impl)

A collection of all sheet objects:

>>> import xlwings as xw

>>> xw.sheets # active book

Sheets([<Sheet [Book1]Sheet1>, <Sheet [Book1]Sheet2>])

>>> xw.apps[0].books['Book1'].sheets # specific book

Sheets([<Sheet [Book1]Sheet1>, <Sheet [Book1]Sheet2>])

New in version 0.9.0.

active

Returns the active Sheet.

add(name=None, before=None, after=None)

Creates a new Sheet and makes it the active sheet.

Parameters

• name (str, default None) – Name of the new sheet. If None, will de-

fault to Excel’s default name.

• before (Sheet, default None) – An object that speciﬁes the sheet be-

fore which the new sheet is added.

• after (Sheet, default None) – An object that speciﬁes the sheet after

which the new sheet is added.

19.2.6 Sheet

class xlwings.Sheet(sheet=None, impl=None)

A sheet object is a member of the sheets collection:

>>> import xlwings as xw

>>> wb = xw.Book()

>>> wb.sheets[0]

>>> wb.sheets['Sheet1']

>>> wb.sheets.add()

Changed in version 0.9.0.

19.2. Object model 75

xlwings - Make Excel Fly!, Release dev

activate()

Activates the Sheet and returns it.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

New in version 0.9.0.

autofit(axis=None)

Autoﬁts the width of either columns, rows or both on a whole Sheet.

Parameters axis (string, default None) –

• To autoﬁt rows, use one of the following: rows or r

• To autoﬁt columns, use one of the following: columns or c

• To autoﬁt rows and columns, provide no arguments

Examples

>>> import xlwings as xw

>>> wb = xw.Book()

>>> wb.sheets['Sheet1'].autofit('c')

>>> wb.sheets['Sheet1'].autofit('r')

>>> wb.sheets['Sheet1'].autofit()

New in version 0.2.3.

book

Returns the Book of the speciﬁed Sheet. Read-only.

cells

Returns a Range object that represents all the cells on the Sheet (not just the cells that are

currently in use).

New in version 0.9.0.

charts

See Charts

New in version 0.9.0.

clear()

Clears the content and formatting of the whole sheet.

clear_contents()

Clears the content of the whole sheet but leaves the formatting.

delete()

Deletes the Sheet.

index

Returns the index of the Sheet (1-based as in Excel).

76 Chapter 19. API Documentation

xlwings - Make Excel Fly!, Release dev

name

Gets or sets the name of the Sheet.

names

Returns a names collection that represents all the sheet-speciﬁc names (names deﬁned with the

“SheetName!” preﬁx).

New in version 0.9.0.

pictures

See Pictures

New in version 0.9.0.

range(cell1, cell2=None)

Returns a Range object from the active sheet of the active book, see Range().

New in version 0.9.0.

select()

Selects the Sheet. Select only works on the active book.

New in version 0.9.0.

shapes

See Shapes

New in version 0.9.0.

19.2.7 Range

class xlwings.Range(cell1=None, cell2=None, **options)

Returns a Range object that represents a cell or a range of cells.

Parameters

• cell1 (str or tuple or Range) – Name of the range in the upper-left

corner in A1 notation or as index-tuple or as name or as xw.Range object. It can

also specify a range using the range operator (a colon), .e.g. ‘A1:B2’

• cell2 (str or tuple or Range, default None) – Name of the

range in the lower-right corner in A1 notation or as index-tuple or as name or

as xw.Range object.

Examples

Active Sheet:

import xlwings as xw

xw.Range('A1')

xw.Range('A1:C3')

xw.Range((1,1))

(continues on next page)

19.2. Object model 77

xlwings - Make Excel Fly!, Release dev

(continued from previous page)

xw.Range((1,1), (3,3))

xw.Range('NamedRange')

xw.Range(xw.Range('A1'), xw.Range('B2'))

Speciﬁc Sheet:

xw.books['MyBook.xlsx'].sheets[0].range('A1')

add_hyperlink(address, text_to_display=None, screen_tip=None)

Adds a hyperlink to the speciﬁed Range (single Cell)

Parameters

• address (str) – The address of the hyperlink.

• text_to_display (str, default None) – The text to be displayed for

the hyperlink. Defaults to the hyperlink address.

• screen_tip (str, default None) – The screen tip to be displayed

when the mouse pointer is paused over the hyperlink. Default is set to ‘<ad-

dress> - Click once to follow. Click and hold to select this cell.’

New in version 0.3.0.

address

Returns a string value that represents the range reference. Use get_address() to be able to

provide paramaters.

New in version 0.9.0.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

New in version 0.9.0.

autofit()

Autoﬁts the width and height of all cells in the range.

• To autoﬁt only the width of the columns use xw.Range('A1:B2').columns.

autofit()

• To autoﬁt only the height of the rows use xw.Range('A1:B2').rows.autofit()

Changed in version 0.9.0.

clear()

Clears the content and the formatting of a Range.

clear_contents()

Clears the content of a Range but leaves the formatting.

color

Gets and sets the background color of the speciﬁed Range.

To set the color, either use an RGB tuple (0, 0, 0) or a color constant. To remove the

background, set the color to None, see Examples.

78 Chapter 19. API Documentation

xlwings - Make Excel Fly!, Release dev

Returns RGB

Return type tuple

Examples

>>> import xlwings as xw

>>> wb = xw.Book()

>>> xw.Range('A1').color = (255,255,255)

>>> xw.Range('A2').color

(255, 255, 255)

>>> xw.Range('A2').color = None

>>> xw.Range('A2').color is None

True

New in version 0.3.0.

column

Returns the number of the ﬁrst column in the in the speciﬁed range. Read-only.

Returns

Return type Integer

New in version 0.3.5.

column_width

Gets or sets the width, in characters, of a Range. One unit of column width is equal to the width

of one character in the Normal style. For proportional fonts, the width of the character 0 (zero)

is used.

If all columns in the Range have the same width, returns the width. If columns in the Range

have different widths, returns None.

column_width must be in the range: 0 <= column_width <= 255

Note: If the Range is outside the used range of the Worksheet, and columns in the Range have

different widths, returns the width of the ﬁrst column.

Returns

Return type ﬂoat

New in version 0.4.0.

columns

Returns a RangeColumns object that represents the columns in the speciﬁed range.

New in version 0.9.0.

count

Returns the number of cells.

current_region

This property returns a Range object representing a range bounded by (but not including) any

19.2. Object model 79

xlwings - Make Excel Fly!, Release dev

combination of blank rows and blank columns or the edges of the worksheet. It corresponds to

Ctrl-

on Windows and Shift-Ctrl-Space on Mac.

Returns

Return type Range object

end(direction)

Returns a Range object that represents the cell at the end of the region that contains the source

range. Equivalent to pressing Ctrl+Up, Ctrl+down, Ctrl+left, or Ctrl+right.

Parameters direction (One of 'up', 'down', 'right', 'left') –

Examples

>>> import xlwings as xw

>>> wb = xw.Book()

>>> xw.Range('A1:B2').value = 1

>>> xw.Range('A1').end('down')

>>> xw.Range('B2').end('right')

New in version 0.9.0.

expand(mode=’table’)

Expands the range according to the mode provided. Ignores empty top-left cells (unlike Range.

end()).

Parameters mode (str, default 'table') – One of 'table' (=down and

right), 'down', 'right'.

Returns

Return type Range

Examples

>>> import xlwings as xw

>>> wb = xw.Book()

>>> xw.Range('A1').value = [[None, 1], [2, 3]]

>>> xw.Range('A1').expand().address

$A$1:$B$2

>>> xw.Range('A1').expand('right').address

$A$1:$B$1

New in version 0.9.0.

formula

Gets or sets the formula for the given Range.

80 Chapter 19. API Documentation

xlwings - Make Excel Fly!, Release dev

formula_array

Gets or sets an array formula for the given Range.

New in version 0.7.1.

get_address(row_absolute=True, column_absolute=True, include_sheetname=False, ex-

ternal=False)

Returns the address of the range in the speciﬁed format. address can be used instead if none

of the defaults need to be changed.

Parameters

• row_absolute (bool, default True) – Set to True to return the row

part of the reference as an absolute reference.

• column_absolute (bool, default True) – Set to True to return the

column part of the reference as an absolute reference.

• include_sheetname (bool, default False) – Set to True to include

the Sheet name in the address. Ignored if external=True.

• external (bool, default False) – Set to True to return an external

reference with workbook and worksheet name.

Returns

Return type str

Examples

>>> import xlwings as xw

>>> wb = xw.Book()

>>> xw.Range((1,1)).get_address()

'$A$1'

>>> xw.Range((1,1)).get_address(False, False)

'A1'

>>> xw.Range((1,1), (3,3)).get_address(True, False, True)

'Sheet1!A$1:C$3'

>>> xw.Range((1,1), (3,3)).get_address(True, False, external=True)

'[Book1]Sheet1!A$1:C$3'

New in version 0.2.3.

height

Returns the height, in points, of a Range. Read-only.

Returns

Return type ﬂoat

New in version 0.4.0.

hyperlink

Returns the hyperlink address of the speciﬁed Range (single Cell only)

19.2. Object model 81

xlwings - Make Excel Fly!, Release dev

Examples

>>> import xlwings as xw

>>> wb = xw.Book()

>>> xw.Range('A1').value

'www.xlwings.org'

>>> xw.Range('A1').hyperlink

'http://www.xlwings.org'

New in version 0.3.0.

last_cell

Returns the bottom right cell of the speciﬁed range. Read-only.

Returns

Return type Range

Example

>>> import xlwings as xw

>>> wb = xw.Book()

>>> rng = xw.Range('A1:E4')

>>> rng.last_cell.row, rng.last_cell.column

(4, 5)

New in version 0.3.5.

left

Returns the distance, in points, from the left edge of column A to the left edge of the range.

Read-only.

Returns

Return type ﬂoat

New in version 0.6.0.

name

Sets or gets the name of a Range.

New in version 0.4.0.

number_format

Gets and sets the number_format of a Range.

Examples

>>> import xlwings as xw

>>> wb = xw.Book()

(continues on next page)

82 Chapter 19. API Documentation

xlwings - Make Excel Fly!, Release dev

(continued from previous page)

>>> xw.Range('A1').number_format

'General'

>>> xw.Range('A1:C3').number_format = '0.00%'

>>> xw.Range('A1:C3').number_format

'0.00%'

New in version 0.2.3.

offset(row_offset=0, column_offset=0)

Returns a Range object that represents a Range that’s offset from the speciﬁed range.

Returns Range object

Return type Range

New in version 0.3.0.

options(convert=None, **options)

Allows you to set a converter and their options. Converters deﬁne how Excel Ranges and their

values are being converted both during reading and writing operations. If no explicit converter

is speciﬁed, the base converter is being applied, see Converters and Options.

Parameters convert (object, default None) – A converter, e.g. dict,

np.array, pd.DataFrame, pd.Series, defaults to default converter

Keyword Arguments

• ndim (int, default None) – number of dimensions

• numbers (type, default None) – type of numbers, e.g. int

• dates (type, default None) – e.g. datetime.date defaults to

datetime.datetime

• empty (object, default None) – transformation of empty cells

• transpose (Boolean, default False) – transpose values

• expand (str, default None) – One of 'table', 'down', 'right'

=> For converter-speciﬁc options, see Converters and Options.

Returns

Return type Range object

New in version 0.7.0.

raw_value

Gets and sets the values directly as delivered from/accepted by the engine that is being used

(pywin32 or appscript) without going through any of xlwings’ data cleaning/converting.

This can be helpful if speed is an issue but naturally will be engine speciﬁc, i.e. might remove

the cross-platform compatibility.

resize(row_size=None, column_size=None)

Resizes the speciﬁed Range

19.2. Object model 83

xlwings - Make Excel Fly!, Release dev

Parameters

• row_size (int > 0) – The number of rows in the new range (if None, the

number of rows in the range is unchanged).

• column_size (int > 0) – The number of columns in the new range (if

None, the number of columns in the range is unchanged).

Returns Range object

Return type Range

New in version 0.3.0.

row

Returns the number of the ﬁrst row in the speciﬁed range. Read-only.

Returns

Return type Integer

New in version 0.3.5.

row_height

Gets or sets the height, in points, of a Range. If all rows in the Range have the same height,

returns the height. If rows in the Range have different heights, returns None.

row_height must be in the range: 0 <= row_height <= 409.5

Note: If the Range is outside the used range of the Worksheet, and rows in the Range have

different heights, returns the height of the ﬁrst row.

Returns

Return type ﬂoat

New in version 0.4.0.

rows

Returns a RangeRows object that represents the rows in the speciﬁed range.

New in version 0.9.0.

select()

Selects the range. Select only works on the active book.

New in version 0.9.0.

shape

Tuple of Range dimensions.

New in version 0.3.0.

sheet

Returns the Sheet object to which the Range belongs.

New in version 0.9.0.

84 Chapter 19. API Documentation

xlwings - Make Excel Fly!, Release dev

size

Number of elements in the Range.

New in version 0.3.0.

top

Returns the distance, in points, from the top edge of row 1 to the top edge of the range. Read-

only.

Returns

Return type ﬂoat

New in version 0.6.0.

value

Gets and sets the values for the given Range.

Returns object

Return type returned object depends on the converter being used, see xlwings.

Range.options()

width

Returns the width, in points, of a Range. Read-only.

Returns

Return type ﬂoat

New in version 0.4.0.

19.2.8 RangeRows

class xlwings.RangeRows(rng)

Represents the rows of a range. Do not construct this class directly, use Range.rows instead.

Example

import xlwings as xw

rng = xw.Range('A1:C4')

assert len(rng.rows) == 4 # or rng.rows.count

rng.rows[0].value = 'a'

assert rng.rows[2] == xw.Range('A3:C3')

assert rng.rows(2) == xw.Range('A2:C2')

for r in rng.rows:

print(r.address)

19.2. Object model 85

xlwings - Make Excel Fly!, Release dev

autofit()

Autoﬁts the height of the rows.

count

Returns the number of rows.

New in version 0.9.0.

19.2.9 RangeColumns

class xlwings.RangeColumns(rng)

Represents the columns of a range. Do not construct this class directly, use Range.columns in-

stead.

Example

import xlwings as xw

rng = xw.Range('A1:C4')

assert len(rng.columns) == 3 # or rng.columns.count

rng.columns[0].value = 'a'

assert rng.columns[2] == xw.Range('C1:C4')

assert rng.columns(2) == xw.Range('B1:B4')

for c in rng.columns:

print(c.address)

autofit()

Autoﬁts the width of the columns.

count

Returns the number of columns.

New in version 0.9.0.

19.2.10 Shapes

class xlwings.main.Shapes(impl)

A collection of all shape objects on the speciﬁed sheet:

>>> import xlwings as xw

>>> xw.books['Book1'].sheets[0].shapes

Shapes([<Shape 'Oval 1' in <Sheet [Book1]Sheet1>>, <Shape 'Rectangle 1'

˓→in <Sheet [Book1]Sheet1>>])

New in version 0.9.0.

86 Chapter 19. API Documentation

xlwings - Make Excel Fly!, Release dev

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

count

Returns the number of objects in the collection.

19.2.11 Shape

class xlwings.Shape(*args, **options)

The shape object is a member of the shapes collection:

>>> import xlwings as xw

>>> sht = xw.books['Book1'].sheets[0]

>>> sht.shapes[0] # or sht.shapes['ShapeName']

Changed in version 0.9.0.

activate()

Activates the shape.

New in version 0.5.0.

delete()

Deletes the shape.

New in version 0.5.0.

height

Returns or sets the number of points that represent the height of the shape.

New in version 0.5.0.

left

Returns or sets the number of points that represent the horizontal position of the shape.

New in version 0.5.0.

name

Returns or sets the name of the shape.

New in version 0.5.0.

parent

Returns the parent of the shape.

New in version 0.9.0.

top

Returns or sets the number of points that represent the vertical position of the shape.

New in version 0.5.0.

type

Returns the type of the shape.

19.2. Object model 87

xlwings - Make Excel Fly!, Release dev

New in version 0.9.0.

width

Returns or sets the number of points that represent the width of the shape.

New in version 0.5.0.

19.2.12 Charts

class xlwings.main.Charts(impl)

A collection of all chart objects on the speciﬁed sheet:

>>> import xlwings as xw

>>> xw.books['Book1'].sheets[0].charts

Charts([<Chart 'Chart 1' in <Sheet [Book1]Sheet1>>, <Chart 'Chart 1' in

˓→<Sheet [Book1]Sheet1>>])

New in version 0.9.0.

add(left=0, top=0, width=355, height=211)

Creates a new chart on the speciﬁed sheet.

Parameters

• left (float, default 0) – left position in points

• top (float, default 0) – top position in points

• width (float, default 355) – width in points

• height (float, default 211) – height in points

Returns

Return type Chart

Examples

>>> import xlwings as xw

>>> sht = xw.Book().sheets[0]

>>> sht.range('A1').value = [['Foo1', 'Foo2'], [1, 2]]

>>> chart = sht.charts.add()

>>> chart.set_source_data(sht.range('A1').expand())

>>> chart.chart_type = 'line'

>>> chart.name

'Chart1'

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

count

Returns the number of objects in the collection.

88 Chapter 19. API Documentation

xlwings - Make Excel Fly!, Release dev

19.2.13 Chart

class xlwings.Chart(name_or_index=None, impl=None)

The chart object is a member of the charts collection:

>>> import xlwings as xw

>>> sht = xw.books['Book1'].sheets[0]

>>> sht.charts[0] # or sht.charts['ChartName']

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

New in version 0.9.0.

chart_type

Returns and sets the chart type of the chart.

New in version 0.1.1.

delete()

Deletes the chart.

height

Returns or sets the number of points that represent the height of the chart.

left

Returns or sets the number of points that represent the horizontal position of the chart.

name

Returns or sets the name of the chart.

parent

Returns the parent of the chart.

New in version 0.9.0.

set_source_data(source)

Sets the source data range for the chart.

Parameters source (Range) – Range object, e.g. xw.books['Book1'].

sheets[0].range('A1')

top

Returns or sets the number of points that represent the vertical position of the chart.

width

Returns or sets the number of points that represent the width of the chart.

19.2.14 Pictures

class xlwings.main.Pictures(impl)

A collection of all picture objects on the speciﬁed sheet:

19.2. Object model 89

xlwings - Make Excel Fly!, Release dev

>>> import xlwings as xw

>>> xw.books['Book1'].sheets[0].pictures

Pictures([<Picture 'Picture 1' in <Sheet [Book1]Sheet1>>, <Picture

˓→'Picture 2' in <Sheet [Book1]Sheet1>>])

New in version 0.9.0.

add(image, link_to_ﬁle=False, save_with_document=True, left=0, top=0, width=None,

height=None, name=None, update=False)

Adds a picture to the speciﬁed sheet.

Parameters

• image (str or matplotlib.figure.Figure) – Either a ﬁlepath or a

Matplotlib ﬁgure object.

• left (float, default 0) – Left position in points.

• top (float, default 0) – Top position in points.

• width (float, default None) – Width in points. If PIL/Pillow is in-

stalled, it defaults to the width of the picture. Otherwise it defaults to 100 points.

• height (float, default None) – Height in points. If PIL/Pillow is in-

stalled, it defaults to the height of the picture. Otherwise it defaults to 100

points.

• name (str, default None) – Excel picture name. Defaults to Excel stan-

dard name if not provided, e.g. ‘Picture 1’.

• update (bool, default False) – Replace an existing picture with the

same name. Requires name to be set.

Returns

Return type Picture

Examples

1. Picture

>>> import xlwings as xw

>>> sht = xw.Book().sheets[0]

>>> sht.pictures.add(r'C:\path\to\file.jpg')

2. Matplotlib

>>> import matplotlib.pyplot as plt

>>> fig = plt.figure()

>>> plt.plot([1, 2, 3, 4, 5])

(continues on next page)

90 Chapter 19. API Documentation

xlwings - Make Excel Fly!, Release dev

(continued from previous page)

>>> sht.pictures.add(fig, name='MyPlot', update=True)

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

count

Returns the number of objects in the collection.

19.2.15 Picture

class xlwings.Picture(impl=None)

The picture object is a member of the pictures collection:

>>> import xlwings as xw

>>> sht = xw.books['Book1'].sheets[0]

>>> sht.pictures[0] # or sht.charts['PictureName']

Changed in version 0.9.0.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

New in version 0.9.0.

delete()

Deletes the picture.

New in version 0.5.0.

height

Returns or sets the number of points that represent the height of the picture.

New in version 0.5.0.

left

Returns or sets the number of points that represent the horizontal position of the picture.

New in version 0.5.0.

name

Returns or sets the name of the picture.

New in version 0.5.0.

parent

Returns the parent of the picture.

New in version 0.9.0.

top

Returns or sets the number of points that represent the vertical position of the picture.

19.2. Object model 91

xlwings - Make Excel Fly!, Release dev

New in version 0.5.0.

update(image)

Replaces an existing picture with a new one, taking over the attributes of the existing picture.

Parameters image (str or matplotlib.figure.Figure) – Either a

ﬁlepath or a Matplotlib ﬁgure object.

New in version 0.5.0.

width

Returns or sets the number of points that represent the width of the picture.

New in version 0.5.0.

19.2.16 Names

class xlwings.main.Names(impl)

A collection of all name objects in the workbook:

>>> import xlwings as xw

>>> sht = xw.books['Book1'].sheets[0]

>>> sht.names

[<Name 'MyName': =Sheet1!$A$3>]

New in version 0.9.0.

add(name, refers_to)

Deﬁnes a new name for a range of cells.

Parameters

• name (str) – Speciﬁes the text to use as the name. Names cannot include

spaces and cannot be formatted as cell references.

• refers_to (str) – Describes what the name refers to, in English, using A1-

style notation.

Returns

Return type Name

New in version 0.9.0.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

New in version 0.9.0.

count

Returns the number of objects in the collection.

92 Chapter 19. API Documentation

xlwings - Make Excel Fly!, Release dev

19.2.17 Name

class xlwings.Name(impl)

The name object is a member of the names collection:

>>> import xlwings as xw

>>> sht = xw.books['Book1'].sheets[0]

>>> sht.names[0] # or sht.names['MyName']

New in version 0.9.0.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

New in version 0.9.0.

delete()

Deletes the name.

New in version 0.9.0.

name

Returns or sets the name of the name object.

New in version 0.9.0.

refers_to

Returns or sets the formula that the name is deﬁned to refer to, in A1-style notation, beginning

with an equal sign.

New in version 0.9.0.

refers_to_range

Returns the Range object referred to by a Name object.

New in version 0.9.0.

19.3 UDF decorators

xlwings.func(category="xlwings", volatile=False, call_in_wizard=True)

Functions decorated with xlwings.func will be imported as Function to Excel when running

“Import Python UDFs”.

category [int or str, default “xlwings”] 1-14 represent built-in categories, for user-deﬁned categories

use strings

New in version 0.10.3.

volatile [bool, default False] Marks a user-deﬁned function as volatile. A volatile function must be

recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function

is recalculated only when the input variables change. This method has no effect if it’s not inside

a user-deﬁned function used to calculate a worksheet cell.

19.3. UDF decorators 93

xlwings - Make Excel Fly!, Release dev

New in version 0.10.3.

call_in_wizard [bool, default True] Set to False to suppress the function call in the function wizard.

New in version 0.10.3.

xlwings.sub()

Functions decorated with xlwings.sub will be imported as Sub (i.e. macro) to Excel when run-

ning “Import Python UDFs”.

xlwings.arg(arg, convert=None, **options)

Apply converters and options to arguments, see also Range.options().

Examples:

Convert x into a 2-dimensional numpy array:

import xlwings as xw

import numpy as np

@xw.func

@xw.arg('x', np.array, ndim=2)

def add_one(x):

return x + 1

xlwings.ret(convert=None, **options)

Apply converters and options to return values, see also Range.options().

Examples

1. Suppress the index and header of a returned DataFrame:

import pandas as pd

@xw.func

@xw.ret(index=False, header=False)

def get_dataframe(n, m):

return pd.DataFrame(np.arange(n

m).reshape((n, m)))

2. Dynamic array:

expand='table' turns the UDF into a dynamic array. Currently you must not use volatile func-

tions as arguments of a dynamic array, e.g. you cannot use =TODAY() as part of a dynamic array.

Also note that a dynamic array needs an empty row and column at the bottom and to the right and will

overwrite existing data without warning.

Unlike standard Excel arrays, dynamic arrays are being used from a single cell like a standard function

and auto-expand depending on the dimensions of the returned array:

import xlwings as xw

import numpy as np

@xw.func

@xw.ret(expand='table')

(continues on next page)

94 Chapter 19. API Documentation

xlwings - Make Excel Fly!, Release dev

(continued from previous page)

def dynamic_array(n, m):

return np.arange(n

m).reshape((n, m))

New in version 0.10.0.

19.3. UDF decorators 95

xlwings - Make Excel Fly!, Release dev

96 Chapter 19. API Documentation

CHAPTER 20

License

xlwings is distributed under a BSD 3-clause license.

Redistribution and use in source and binary forms, with or without modiﬁcation, are permitted provided that

the following conditions are met:

• Redistributions of source code must retain the above copyright notice, this list of conditions and the

following disclaimer.

• Redistributions in binary form must reproduce the above copyright notice, this list of conditions and

the following disclaimer in the documentation and/or other materials provided with the distribution.

• Neither the name of the copyright holder nor the names of its contributors may be used to endorse or

promote products derived from this software without speciﬁc prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS “AS IS”

AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IM-

PLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE

ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS

BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSE-

QUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE

GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOW-

EVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIA-

BILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF

THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

xlwings - Make Excel Fly!, Release dev

98 Chapter 20. License

Index

activate() (xlwings.App method), 69

activate() (xlwings.Book method), 72

activate() (xlwings.Shape method), 87

activate() (xlwings.Sheet method), 75

active (xlwings.main.Apps attribute), 68

active (xlwings.main.Books attribute), 71

active (xlwings.main.Sheets attribute), 75

add() (xlwings.main.Apps method), 68

add() (xlwings.main.Books method), 71

add() (xlwings.main.Charts method), 88

add() (xlwings.main.Names method), 92

add() (xlwings.main.Pictures method), 90

add() (xlwings.main.Sheets method), 75

add_hyperlink() (xlwings.Range method), 78

address (xlwings.Range attribute), 78

api (xlwings.App attribute), 69

api (xlwings.Book attribute), 72

api (xlwings.Chart attribute), 89

api (xlwings.main.Charts attribute), 88

api (xlwings.main.Names attribute), 92

api (xlwings.main.Pictures attribute), 91

api (xlwings.main.Shapes attribute), 86

api (xlwings.Name attribute), 93

api (xlwings.Picture attribute), 91

api (xlwings.Range attribute), 78

api (xlwings.Sheet attribute), 76

App (class in xlwings), 68

app (xlwings.Book attribute), 72

Apps (class in xlwings.main), 68

autoﬁt() (xlwings.Range method), 78

autoﬁt() (xlwings.RangeColumns method), 86

autoﬁt() (xlwings.RangeRows method), 85

autoﬁt() (xlwings.Sheet method), 76

Book (class in xlwings), 72

book (xlwings.Sheet attribute), 76

Books (class in xlwings.main), 71

books (xlwings.App attribute), 69

calculate() (xlwings.App method), 69

calculation (xlwings.App attribute), 69

caller() (xlwings.Book class method), 72

cells (xlwings.Sheet attribute), 76

Chart (class in xlwings), 89

chart_type (xlwings.Chart attribute), 89

Charts (class in xlwings.main), 88

charts (xlwings.Sheet attribute), 76

clear() (xlwings.Range method), 78

clear() (xlwings.Sheet method), 76

clear_contents() (xlwings.Range method), 78

clear_contents() (xlwings.Sheet method), 76

close() (xlwings.Book method), 73

color (xlwings.Range attribute), 78

column (xlwings.Range attribute), 79

column_width (xlwings.Range attribute), 79

columns (xlwings.Range attribute), 79

count (xlwings.main.Apps attribute), 68

count (xlwings.main.Charts attribute), 88

count (xlwings.main.Names attribute), 92

count (xlwings.main.Pictures attribute), 91

count (xlwings.main.Shapes attribute), 87

count (xlwings.Range attribute), 79

count (xlwings.RangeColumns attribute), 86

count (xlwings.RangeRows attribute), 86

current_region (xlwings.Range attribute), 79

xlwings - Make Excel Fly!, Release dev

delete() (xlwings.Chart method), 89

delete() (xlwings.Name method), 93

delete() (xlwings.Picture method), 91

delete() (xlwings.Shape method), 87

delete() (xlwings.Sheet method), 76

display_alerts (xlwings.App attribute), 69

end() (xlwings.Range method), 80

expand() (xlwings.Range method), 80

formula (xlwings.Range attribute), 80

formula_array (xlwings.Range attribute), 80

fullname (xlwings.Book attribute), 73

get_address() (xlwings.Range method), 81

height (xlwings.Chart attribute), 89

height (xlwings.Picture attribute), 91

height (xlwings.Range attribute), 81

height (xlwings.Shape attribute), 87

hwnd (xlwings.App attribute), 69

hyperlink (xlwings.Range attribute), 81

index (xlwings.Sheet attribute), 76

kill() (xlwings.App method), 70

last_cell (xlwings.Range attribute), 82

left (xlwings.Chart attribute), 89

left (xlwings.Picture attribute), 91

left (xlwings.Range attribute), 82

left (xlwings.Shape attribute), 87

macro() (xlwings.App method), 70

macro() (xlwings.Book method), 73

Name (class in xlwings), 93

name (xlwings.Book attribute), 73

name (xlwings.Chart attribute), 89

name (xlwings.Name attribute), 93

name (xlwings.Picture attribute), 91

name (xlwings.Range attribute), 82

name (xlwings.Shape attribute), 87

name (xlwings.Sheet attribute), 76

Names (class in xlwings.main), 92

names (xlwings.Book attribute), 73

names (xlwings.Sheet attribute), 77

number_format (xlwings.Range attribute), 82

offset() (xlwings.Range method), 83

open() (xlwings.main.Books method), 71

open_template() (xlwings.Book static method), 73

options() (xlwings.Range method), 83

parent (xlwings.Chart attribute), 89

parent (xlwings.Picture attribute), 91

parent (xlwings.Shape attribute), 87

Picture (class in xlwings), 91

Pictures (class in xlwings.main), 89

pictures (xlwings.Sheet attribute), 77

pid (xlwings.App attribute), 70

quit() (xlwings.App method), 70

Range (class in xlwings), 77

range() (xlwings.App method), 70

range() (xlwings.Sheet method), 77

RangeColumns (class in xlwings), 86

RangeRows (class in xlwings), 85

raw_value (xlwings.Range attribute), 83

refers_to (xlwings.Name attribute), 93

refers_to_range (xlwings.Name attribute), 93

resize() (xlwings.Range method), 83

row (xlwings.Range attribute), 84

row_height (xlwings.Range attribute), 84

rows (xlwings.Range attribute), 84

save() (xlwings.Book method), 74

screen_updating (xlwings.App attribute), 70

select() (xlwings.Range method), 84

select() (xlwings.Sheet method), 77

100 Index

xlwings - Make Excel Fly!, Release dev

selection (xlwings.App attribute), 71

selection (xlwings.Book attribute), 74

set_mock_caller() (xlwings.Book method), 74

set_source_data() (xlwings.Chart method), 89

Shape (class in xlwings), 87

shape (xlwings.Range attribute), 84

Shapes (class in xlwings.main), 86

shapes (xlwings.Sheet attribute), 77

Sheet (class in xlwings), 75

sheet (xlwings.Range attribute), 84

Sheets (class in xlwings.main), 75

sheets (xlwings.Book attribute), 75

size (xlwings.Range attribute), 84

top (xlwings.Chart attribute), 89

top (xlwings.Picture attribute), 91

top (xlwings.Range attribute), 85

top (xlwings.Shape attribute), 87

type (xlwings.Shape attribute), 87

update() (xlwings.Picture method), 92

value (xlwings.Range attribute), 85

version (xlwings.App attribute), 71

view() (in module xlwings), 67

visible (xlwings.App attribute), 71

width (xlwings.Chart attribute), 89

width (xlwings.Picture attribute), 92

width (xlwings.Range attribute), 85

width (xlwings.Shape attribute), 88

xlwings (module), 67

xlwings.arg() (in module xlwings), 94

xlwings.func() (in module xlwings), 93

xlwings.ret() (in module xlwings), 94

xlwings.sub() (in module xlwings), 94

Index 101