Xlwings Guide

xlwings - Make Excel Fly!
�� dev
Zoomer Analytics LLC
2021 � 08 � 20 �
Getting Started
1 �� 1
2 �� 3
2.1 Prerequisites . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
2.2 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
2.3 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
2.4 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
2.5 How to activate xlwings PRO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
2.6 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
2.7 Update . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
2.8 Uninstall . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3 �� 7
3.1 1. Interacting with Excel from a Jupyter notebook . . . . . . . . . . . . . . . . . . . 7
3.2 2. Scripting: Automate/interact with Excel from Python . . . . . . . . . . . . . . . 7
3.3 3. Macros: Call Python from Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
3.4 4. UDFs: User Defined Functions (Windows only) . . . . . . . . . . . . . . . . . . . 9
4 ��Excel�� 11
4.1 Python�Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
4.2 Excel�Python(RunPython) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
4.3 ��(UDFs) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
5 �� 13
5.1 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
5.2 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
5.3 App context manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
5.4 ��/�� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
5.5 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
5.6 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
6 �� 17
6.1 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
i
6.2 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
6.3 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
6.4 NumPy�� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
6.5 Pandas��(DataFrame) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
6.6 Pandas��(Serie) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
6.7 Chunking: Read/Write big DataFrames etc. . . . . . . . . . . . . . . . . . . . . . . 20
7 Add-in & Settings 23

7.1 ��main . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
7.2 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
7.3 User Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
7.4 Environment Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
7.5 User Config: Ribbon/Config File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
7.6 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
7.7 ��xlwings.conf� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
7.8 ��VBA�� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
8 RunPython 29
8.1 xlwings�� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
8.2 �”RunPython”��Python . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
8.3 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
9 User Defined Functions (UDFs) 31

9.1 Excel�� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
9.2 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
9.3 ��UDF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
9.4 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
9.5 �NumPy�Pandas�� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
9.6 ��@xw.arg�@xw.ret . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
9.7 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
9.8 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
9.9 The “caller” argument . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
9.10 “vba”�� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
9.11 � . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
9.12 �VBA��UDF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
9.13 ��UDF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
10 Matplotlib & Plotly Charts 39

10.1 Matplotlib . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
10.2 Plotly static charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
11 Jupyter Notebooks: Interact with Excel 45

11.1 The view function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
11.2 The load function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
12 Command Line Client (CLI) 47
13 �� 49
13.1 Zip�� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
ii
13.2 RunFrozenPython . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
14 �� 51
14.1 ��: ��dll�� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
14.2 Issue: Couldn’t find the local location of your OneDrive or SharePoint . . . . . . . . 51
15 �� 53
15.1 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
15.2 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
15.3 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
16 �� 65
16.1 RunPython . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
16.2 UDF�� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
17 �� 69
17.1 In-Excel SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
18 Custom Add-ins 71
18.1 Quickstart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
18.2 Changing the Ribbon menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
18.3 Importing UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
18.4 Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
18.5 Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
18.6 Renaming your add-in . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
18.7 Deployment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
19 �� 77
19.1 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
19.2 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
20 �� 81
20.1 ��VBA Range.WrapText �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
21 xlwings��Office�� 83
21.1 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
21.2 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
22 xlwings PRO Overview 85

22.1 PRO Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
22.2 More Infos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
23 xlwings Reports 87
23.1 Quickstart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
23.2 DataFrames . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
23.3 Excel Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
23.4 Excel Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
23.5 Images . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
23.6 Matplotlib and Plotly Plots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
iii
23.7 Text . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
23.8 Date and Time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
23.9 Number Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
23.10 Frames: Multi-column Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
23.11 PDF Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
24 Markdown Formatting 111
25 Releasing xlwings Tools 115

25.1 Step 1: One-Click Installer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
25.2 Step 2: Release Command (CLI) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
25.3 Updating a Release . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
25.4 Embedded Code Explained . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
26 Permissioning of Code Execution 123

26.1 Prerequisites . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
26.2 Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
26.3 GET request . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
26.4 POST request . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
26.5 Implementation Details & Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . 126
27 Python API 127

27.1 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
27.2 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
27.3 UDF�� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
27.4 Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
28 REST API 171

28.1 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
28.2 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
28.3 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
28.4 �� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
28.5 Endpoint�� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
28.6 Endpoint�� . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
�� 203
iv
CHAPTER 1
��
��
https://training.xlwings.org/p/xlwings
�(��)��xlwings��:)
1
xlwings - Make Excel Fly!, �� dev
2 Chapter 1. ��
CHAPTER 2
��
2.1 Prerequisites
• xlwings requires an installation of Excel and therefore only works on Windows and ma-
cOS. Note that macOS currently does not support UDFs.
• xlwings requires at least Python 3.6.
Here are the last versions of xlwings to support:
• Python 3.5: 0.19.5
• Python 2.7: 0.16.6
2.2 ��
xlwings comes pre-installed with

• Anaconda (Windows and macOS)
• WinPython (Windows only) Make sure not to take the dot version as this only contains
Python.
If you are new to Python or have trouble installing xlwings, one of these distributions is highly
recommended. Otherwise, you can also install it manually with pip:
pip install xlwings
��conda:
3
conda install xlwings
Note that the official conda package might be a few releases behind. You can, however, use the
conda-forge channel (replace install with upgrade if xlwings is already installed):
conda install -c conda-forge xlwings
��: When you are on macOS 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. This is done automatically if you install the addin via $ xlwings addin install.
2.3 ��
To install the add-in, run the following command:
xlwings addin install
To call Excel from Python, you don’t need an add-in. Also, you can use a single file VBA module
(standalone workbook) instead of the add-in. For more details, see Add-in & Settings.
��: The add-in needs to be the same version as the Python package. Make sure to re-install the
add-in after upgrading the xlwings package.
2.4 ��
• Windows: pywin32
• Mac: psutil, appscript
The dependencies are automatically installed via conda or pip.
2.5 How to activate xlwings PRO
xlwings PRO offers access to additional functionality. All PRO features are marked with xlwings
PRO in the docs.
��: To get access to the additional functionality of xlwings PRO, you need a license key and at least
xlwings v0.19.0. Everything under the xlwings.pro subpackage is distributed under a commercial
license. See xlwings PRO Overview for more details.
4 Chapter 2. ��
To activate the license key, run the following command:
xlwings license update -k LICENSE_KEY
Make sure to replace LICENSE_KEY with your personal key. This will store the license key un-
der your xlwings.conf file (see User Config: Ribbon/Config File for where this is on your sys-
tem). Alternatively, you can also store the license key as an environment variable with the name
XLWINGS_LICENSE_KEY.
xlwings PRO requires additionally the cryptography and Jinja2 packages which come preinstalled
with Anaconda and WinPython. Otherwise, install them via pip or conda.
With pip, you can also run pip install "xlwings[pro]" which will take care of the extra depen-
dencies for xlwings PRO.
2.6 ��
• NumPy
• Pandas
• Matplotlib
• Pillow/PIL
• Flask (for REST API)
• cryptography (for xlwings.pro)
• Jinja2 (for xlwings.pro.reports)
• requests (for permissioning)
These packages are not required but highly recommended as they play very nicely with xlwings.
They are all pre-installed with Anaconda. With pip, you can install xlwings with all optional
dependencies as follows:
pip install "xlwings[all]"
2.7 Update
To update to the latest xlwings version, run the following in a command prompt:
pip install --upgrade xlwings
or:
conda update -c conda-forge xlwings
Make sure to keep your version of the Excel add-in in sync with your Python package by running
the following (make sure to close Excel first):
2.6. �� 5
2.8 Uninstall
To uninstall xlwings completely, first uninstall the add-in, then uninstall the xlwings package using
the same method (pip or conda) that you used for installing it:
xlwings addin remove
Then
pip uninstall xlwings
or:
conda remove xlwings
Finally, manually remove the .xlwings directory in your home folder if it exists.
6 Chapter 2. ��
CHAPTER 3
��
��xlwings��
3.1 1. Interacting with Excel from a Jupyter notebook
If you’re just interested in getting a pandas DataFrame in and out of your Jupyter notebook, you
can use the view and load functions, see Jupyter Notebooks: Interact with Excel.
3.2 2. Scripting: Automate/interact with Excel from Python
��(workbook)��
>>> import xlwings as xw

>>> wb = xw.Book() # this will create a new workbook
>>> wb = xw.Book('FileName.xlsx') # connect to a file that is open or in the␣
,→current working directory
>>> wb = xw.Book(r'C:\path\to\file.xlsx') # on Windows: use raw strings to␣

,→escape backslashes
��Excel��Excel��app�� xw.apps.keys() ��app��(PID)�
>>> xw.apps[10559].books['FileName.xlsx']
��(sheet)��
>>> sheet = wb.sheets['Sheet1']
7
��(range)��/��
>>> sheet.range('A1').value = 'Foo 1'

>>> sheet.range('A1').value
'Foo 1'
��
>>> sheet.range('A1').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
>>> sheet.range('A1').expand().value
[['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
�� Numpy arrays�Pandas DataFrames�
>>>import pandas as pd
>>>df = pd.DataFrame([[1,2], [3,4]], columns=['a', 'b'])
>>>sheet.range('A1').value = df
>>>sheet.range('A1').options(pd.DataFrame, expand='table').value
a b
0.0 1.0 2.0
1.0 3.0 4.0
Matplotlib ��Excel��
>>> import matplotlib.pyplot as plt

>>> fig = plt.figure()
>>> plt.plot([1, 2, 3, 4, 5])
[<matplotlib.lines.Line2D at 0x1071706a0>]
>>> sheet.pictures.add(fig, name='MyPlot', update=True)
<Picture 'MyPlot' in <Sheet [Workbook4]Sheet1>>
3.3 3. Macros: Call Python from Excel
�� Run ��(v0.16��)��Python��VBA�� RunPython ��

Run ��Python�� main �� xlsx ��
��Python�� RunPython ��:
Sub HelloWorld()
RunPython "import hello; hello.world()"
End Sub
��: Per default, RunPython expects hello.py in the same directory as the Excel file with the same
name, but you can change both of these things: if your Python file is an a different folder, add
8 Chapter 3. ��
that folder to the PYTHONPATH in the config. If the file has a different name, change the RunPython
command accordingly.
Refer to the calling Excel book by using xw.Book.caller():
# 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 or have the workbooks setup in
the standalone mode. 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.
�� Add-in & Settings �
3.4 4. UDFs: User Defined Functions (Windows only)
�Python��UDF��
@xw.func
def hello(name):
return f'Hello { name}'
UDFs��Pandas DataFrame��
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 a step-by-
step tutorial, see User Defined Functions (UDFs).
3.4. 4. UDFs: User Defined Functions (Windows only) 9

10 Chapter 3. ��
CHAPTER 4
��Excel��
��

>>> xw.Range('A1').value = 'something'
4.1 Python�Excel
xw.Book �� app��app�� app�� xw.books ��app��
>>> app = xw.App() # or something like xw.apps[10559] for existing apps, get␣
,→the available PIDs via xw.apps.keys()
>>> app.books['Book1']
Note that you usually should use App as a context manager as this will make sure that the Excel
instance is closed and cleaned up again properly:
with xw.App() as app:

book = app.books['Book1']
xw.Book xw.books
�� xw.Book() xw.books.add()
�� xw.Book('Book1') xw.books['Book1']
�� xw.Book(r'C:/path/to/file.xlsx') xw.books.open(r'C:/path/to/file.xlsx')
11
��: When specifying file 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.
4.2 Excel�Python(RunPython)
�VBA�� RunPython �� xw.Book.caller() �� ”RunPython”��Python �� Pyhton�Ex-

cel�� xw.Book.caller() ��
4.3 ��(UDFs)
Unlike RunPython, UDFs don’t need a call to xw.Book.caller(), see User Defined Functions
(UDFs). You’ll usually use the caller argument which returns the xlwings range object from
where you call the function.
12 Chapter 4. ��Excel��
CHAPTER 5
��
xlwings��VBA��
��
5.1 ��
# 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
>>> sheet = xw.sheets.active # in active book
>>> sheet = wb.sheets.active # in specific book
# Range on active sheet

>>> xw.Range('A1') # on active sheet of active book of active app
��A1��1��2��
xw.Range('A1')
xw.Range('A1:C3')
(��)
13
(��)
xw.Range((1,1))
xw.Range((1,1), (3,3))
xw.Range('NamedRange')
xw.Range(xw.Range('A1'), xw.Range('B2'))
5.2 ��
��Excel��1��Python��0�� :
xw.apps[763].books[0].sheets[0].range('A1')
xw.apps(10559).books(1).sheets(1).range('A1')
xw.apps[763].books['Book1'].sheets['Sheet1'].range('A1')
xw.apps(10559).books('Book1').sheets('Sheet1').range('A1')
��app��ID�PID�� xw.apps.keys() ��PID��
5.3 App context manager
If you want to open a new Excel instance via App(), you usually should use App as a context
manager as this will make sure that the Excel instance is closed and cleaned up again properly:

book = app.books['Book1']
5.4 ��/��
��
>>> rng = xw.Book().sheets[0].range('A1:D5')

>>> rng[0, 0]
<Range [Workbook1]Sheet1!$A$1>
>>> rng[1]
<Range [Workbook1]Sheet1!$B$1>
>>> rng[:, 3:]
<Range [Workbook1]Sheet1!$D$1:$D$5>
>>> rng[1:3, 1:3]
<Range [Workbook1]Sheet1!$B$2:$C$3>
5.5 ��
�� /�� sheet.range � sheet.cells ��
>>> sheet = xw.Book().sheets['Sheet1']

>>> sheet['A1']
<Range [Book1]Sheet1!$A$1>
>>> sheet['A1:B5']
<Range [Book1]Sheet1!$A$1:$B$5>
>>> sheet[0, 1]
<Range [Book1]Sheet1!$B$1>
>>> sheet[:10, :10]
<Range [Book1]Sheet1!$A$1:$J$10>
5.6 ��
��xlwings��app�range��range�app��
>>> rng = xw.apps[10559].books[0].sheets[0].range('A1')

>>> rng.sheet.book.app
<Excel App 10559>
5.5. �� 15
CHAPTER 6
��
��xlwings�� options ��

��
6.1 ��
��python�� float, unicode, None � datetime :
>>> import datetime as dt

>>> sheet = xw.Book().sheets[0]
>>> sheet.range('A1').value = 1
1.0
>>> sheet.range('A2').value = 'Hello'
'Hello'
>>> sheet.range('A3').value is None
True
>>> sheet.range('A4').value = dt.datetime(2000, 1, 1)
datetime.datetime(2000, 1, 1, 0, 0)
17
6.2 ��
• ��Excel��Python�� Python��

>>> sheet.range('A1').value = [[1],[2],[3],[4],[5]] # Column orientation␣
,→(nested list)
>>> sheet.range('A1:A5').value
[1.0, 2.0, 3.0, 4.0, 5.0]
>>> sheet.range('A1').value = [1, 2, 3, 4, 5]
>>> sheet.range('A1:E1').value
[1.0, 2.0, 3.0, 4.0, 5.0]
��:
>>> sheet.range('A1').options(ndim=1).value
[1.0]
��: To write a list in column orientation to Excel, use transpose: sheet.range('A1').

options(transpose=True).value = [1,2,3,4]
• ��ndim ��
>>> sheet.range('A1:A5').options(ndim=2).value
[[1.0], [2.0], [3.0], [4.0], [5.0]]
>>> sheet.range('A1:E1').options(ndim=2).value
[[1.0, 2.0, 3.0, 4.0, 5.0]]
• ��Excel��Python�
>>> sheet.range('A10').value = [['Foo 1', 'Foo 2', 'Foo 3'], [10, 20, 30]]
>>> sheet.range((10,1),(11,3)).value
[['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
��: Try to minimize the number of interactions with Excel. It is always more efficient to do sheet.
range('A1').value = [[1,2],[3,4]] than sheet.range('A1').value = [1, 2] and sheet.
range('A2').value = [3, 4].
6.3 ��
�� expand �� options �� expand �� expand ��,� options ��

>>> sheet.range('A1').value = [[1,2], [3,4]]
>>> rng1 = sheet.range('A1').expand('table') # or just .expand()
>>> rng2 = sheet.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]]
>>> sheet.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' �� 'down' (��)� 'right' (��)��
��: Using expand() together with a named Range as top left cell gives you a flexible setup in
Excel: You can move around the table and change its size without having to adjust your code, e.g.
by using something like sheet.range('NamedRange').expand().value.
6.4 NumPy��
NumPy�� nan �� None �� options �� convert=np.array �
>>> import numpy as np

>>> sheet.range('A1').value = np.eye(3)
>>> sheet.range('A1').options(np.array, expand='table').value
array([[ 1., 0., 0.],
[ 0., 1., 0.],
[ 0., 0., 1.]])
6.5 Pandas��(DataFrame)

>>> df = pd.DataFrame([[1.1, 2.2], [3.3, None]], columns=['one', 'two'])
>>> df
one two
0 1.1 2.2
1 3.3 NaN
>>> sheet.range('A1').value = df
(��)
6.4. NumPy�� 19
(��)
>>> sheet.range('A1:C3').options(pd.DataFrame).value
one two
0 1.1 2.2
1 3.3 NaN
# options: work for reading and writing
>>> sheet.range('A5').options(index=False).value = df
>>> sheet.range('A9').options(index=False, header=False).value = df
6.6 Pandas��(Serie)
>>> import pandas as pd

>>> 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
>>> sheet.range('A1').value = s
>>> sheet.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
��: You only need to specify the top left cell when writing a list, a NumPy array or a Pandas
DataFrame to Excel, e.g.: sheet.range('A1').value = np.eye(10)
6.7 Chunking: Read/Write big DataFrames etc.
When you read and write from or to big ranges, you may have to chunk them or you will hit a
timeout or a memory error. The ideal chunksize will depend on your system and size of the array,
so you will have to try out a few different chunksizes to find one that works well:
import pandas as pd
import numpy as np
sheet = xw.Book().sheets[0]
data = np.arange(75_000 * 20).reshape(75_000, 20)
df = pd.DataFrame(data=data)
sheet['A1'].options(chunksize=10_000).value = df
And the same for reading:
# As DataFrame
df = sheet['A1'].expand().options(pd.DataFrame, chunksize=10_000).value
# As list of list
df = sheet['A1'].expand().options(chunksize=10_000).value
6.7. Chunking: Read/Write big DataFrames etc. 21

CHAPTER 7
Add-in & Settings
The xlwings add-in is the preferred way to be able to use the Run main button, RunPython or
UDFs. Note that you don’t need an add-in if you just want to manipulate Excel by running a
Python script.
��: The ribbon of the add-in is compatible with Excel >= 2007 on Windows and >= 2016 on Mac.
On Mac, all UDF related functionality is not available.
��: The add-in is password protected with the password xlwings. For debugging or to add new
extensions, you need to unprotect it. Alternatively, you can also install the add-in via xlwings
addin install --unprotected.
7.1 ��main
0.16.0 ��.
The Run main button is the easiest way to run your Python code: It runs a function called main
in a Python module that has the same name as your workbook. This allows you to save your
23
workbook as xlsx without enabling macros. The xlwings quickstart command will create a
workbook that will automatically work with the Run button.
7.2 ��
To install the add-in, use the command line client:
Technically, this copies the add-in from Python’s installation directory to Excel’s XLSTART folder.
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 VBA editor). Note that when you create a workbook via xlwings quickstart, the reference
should already be set.
7.3 User Settings
When you install the add-in for the first time, it will get auto-configured and therefore, a
quickstart project should work out of the box. For fine-tuning, here are the available settings:
• Interpreter: This is the path to the Python interpreter. This works also with virtual or
conda envs on Mac. If you use conda envs on Windows, then leave this empty and use
Conda Path and Conda Env below instead. Examples: "C:\Python39\pythonw.exe" or "/
usr/local/bin/python3.9". Note that in the settings, this is stored as Interpreter_Win
or Interpreter_Mac, respectively, see below!
24 Chapter 7. Add-in & Settings

• PYTHONPATH: If the source file of your code is not found, add the path to its directory here.
• Conda Path: ��Windows��conda��Anaconda��Miniconda��
C:\Users\Username\Miniconda3 � %USERPROFILE%\Anaconda ��conda 4.6��
• Conda Env: If you are on Windows and use Anaconda or Miniconda, type here the name of
your conda env, e.g. base for the base installation or myenv for a conda env with the name
myenv.
• UDF Modules : ��UDF�Python��(��.py ��)��”;”�� UDF_MODULES = "common_udfs;
myproject" � ��Excel�� .py ��
• Debug UDFs: ��xlwings COM�� .
• RunPython: Use UDF Server: ��UDF��COM��Python��
• Restart UDF Server: This restarts the UDF Server/Python interpreter.
• Show Console: Check the box in the ribbon or set the config to TRUE if you want the command
prompt to pop up. This currently only works on Windows.
7.3.1 Anaconda/Miniconda
If you use Anaconda or Miniconda on Windows, you will need to set your Conda Path and Conda
Env settings, as you will otherwise get errors when using NumPy etc. In return, leave Interpreter
empty.
7.4 Environment Variables
With environment variables, you can set dynamic paths e.g. to your interpreter or PYTHONPATH:
• On Windows, you can use all environment variables like so: %USERPROFILE%\Anaconda.
• On macOS, the following special variables are supported: $HOME, $APPLICATIONS,
$DOCUMENTS, $DESKTOP.
7.5 User Config: Ribbon/Config File
xlwings��
• Windows: .xlwings\xlwings.conf in your home folder, that is usually
C:\Users\<username>
• macOS: ~/Library/Containers/com.microsoft.Excel/Data/xlwings.conf
The format is as follows (currently the keys are required to be all caps) - note the OS specific
Interpreter settings!
7.4. Environment Variables 25

"INTERPRETER_WIN","C:\path\to\python.exe"
"INTERPRETER_MAC","/path/to/python"
"PYTHONPATH",""
"CONDA PATH",""
"CONDA ENV",""
"UDF MODULES",""
"DEBUG UDFS",""
"USE UDF SERVER",""
"SHOW CONSOLE",""
"ONEDRIVE_WIN",""
"ONEDRIVE_MAC",""
��: The ONEDRIVE_WIN/_MAC setting has to be edited directly in the file, there is currently no
possibility to edit it via the ribbon. Usually, it is only required if you are either on macOS or if
your environment variables on Windows are not correctly set or if you have a private and corporate
location and don’t want to go with the default one. ONEDRIVE_WIN/_MAC has to point to the root
folder of your local OneDrive folder.
7.6 ��
�� xlwings.conf ��
7.7 ��xlwings.conf�
�� xlwings.conf �� xlwings quickstart ��

xlwings.conf ��

7.8 ��VBA��
Sometimes, it might be useful to run xlwings code without having to install an add-in first. 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 also include Dictionary.cls as this is required on macOS. 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.
7.8. ��VBA�� 27

CHAPTER 8
RunPython
8.1 xlwings��
�� Run main ��(v0.16��)�� RunPython VBA��xlwings��(�VBA��)�� Add-in & Settings.

��quickstart�� Command Line Client (CLI)
$ xlwings quickstart myproject
8.2 �”RunPython”��Python
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 a new module via Insert > Module.
Sub HelloWorld()
RunPython "import hello; hello.world()"
End Sub
�� hello.py ��
# hello.py
import numpy as np
def world():
(��)
29
(��)
wb.sheets[0].range('A1').value = 'Hello World!'
�� HelloWorld ��VBA�� F5 ��
��: 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 Use UDF Server = True.
8.3 ��
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 issues,
use UDFs, see User Defined Functions (UDFs) - however, this is currently limited to Windows only.
30 Chapter 8. RunPython
CHAPTER 9
User Defined Functions (UDFs)
��
��:
• ��Windows��(UDF)
• �� .
• ��API��: UDF��.
9.1 Excel��
1) Enable Trust access to the VBA project object model under File > Options >
Trust Center > Trust Center Settings > Macro Settings. You only need to do this
once. Also, this is only required for importing the functions, i.e. end users won’t need to
bother about this.
2) Install the add-in via command prompt: xlwings addin install (see Add-in & Settings).
9.2 ��
�� xlwings quickstart myproject ��(�� Command Line Client

(CLI))��xlwings��
31
9.3 ��UDF
�� ‘‘quickstart‘‘��Python��
• ��
• �� .py �� .xlsm �
��xlwings�� UDF Modules ��
�� myproject.xlsm �� myproject.py:
@xw.func
def double_sum(x, y):
"""Returns twice the sum of the two arguments"""
return 2 * (x + y)
• �xlwings�� Import Python UDFs �� myproject.py ��

• �� =double_sum(1, 2) ��
• ��(��)�Excel��
��:
• ��
• ��(�� Ctrl-Alt-F9 ��)�� Python�� Restart
UDF Server �[��Excel2013��]
• �� @xw.func ��Excel��xlwings��xlwings��VBA��Python��
9.4 ��
�Excel��
��Python��
��1:
32 Chapter 9. User Defined Functions (UDFs)

@xw.func
def add_one(data):
return [[cell + 1 for cell in row] for row in data]
��Excel��
• �� Import Python UDFs
• �� A1:B2 ��
• �� D1:E2
• �� =add_one(A1:B2)
• � Ctrl+Shift+Enter ��
9.4.1 ��: ndim
�� [[1, 2], [3, 4]] �“2�”�� : 'float' ��

��Excel��/��2��2��:
@xw.func
@xw.arg('data', ndim=2)
def add_one(data):
return [[cell + 1 for cell in row] for row in data]
9.5 �NumPy�Pandas��
��UDF��NumPy array��Pandas DataFrame��Python��

��numpy array��:
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
9.5. �NumPy�Pandas�� 33
��: ��Python >= 3.5 �� NumPy >= 1.10�� x.dot(y) �� x @ y �
�Pandas�� CORREL ��Excel�� CORREL ��2��Pandas�� CORREL2 ��:
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()
9.6 ��@xw.arg�@xw.ret
��UDF�� options �� Range ��( @xw.arg ) ��(@xw.ret )��x��pandas

DataFrame��:
@xw.func
@xw.arg('x', pd.DataFrame)
@xw.ret(index=False)
def myfunction(x):
# x is a DataFrame, do something with it
return x
��
9.7 ��
��: ��Excel�� expand �� =UNIQUE() ��2018�9��Office 365 In-

sider��Fast��
��Excel�� Ctrl-Shift-Enter ��v0.10��xlwings��UDF��

��UDF��
import numpy as np
@xw.func
@xw.ret(expand='table')
(��)

(��)
def dynamic_array(r, c):
return np.random.randn(int(r), int(c))
��:
• ��;
• v0.15.0�� =TODAY() ��v0.15.0��UDF��1��;
• �v0.15.0�� xlwings >= v0.15.0�� Ctrl-Shift-Enter
9.7. �� 35
��
9.8 ��
��x�y��Excel��
@xw.func
@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)
9.9 The “caller” argument
You often need to know which cell called the UDF. For this, xlwings offers the reserved argument
caller which returns the calling cell as xlwings range object:
@xw.func
def get_caller_address(caller):
# caller will not be exposed in Excel, so use it like so:
# =get_caller_address()
return caller.address
Note that caller will not be exposed in Excel but will be provided by xlwings behind the scenes.
9.10 “vba”��
By using the vba keyword, you can get access to any Excel VBA object in the form of a pywin32
object. For example, if you wanted to pass the sheet object in the form of its CodeName, you can
do it as follows:
@xw.func
@xw.arg('sheet1', vba='Sheet1')
def get_name(sheet1):
# call this function in Excel with:
# =get_name()
return sheet1.Name
Note that vba arguments are not exposed in the UDF but automatically provided by xlwings.

9.11 �
On Windows, as an alternative to calling macros via RunPython, you can also use the @xw.sub
decorator:
@xw.sub
def my_macro():
"""Writes the name of the Workbook into Range("A1") of Sheet 1"""
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 do 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.
9.12 �VBA��UDF
��VBA��2��:
Sub MySub()
Dim arr() As Variant

Dim i As Long, j As Long
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
9.13 ��UDF
��: This is an experimental feature
v0.14.0 ��.
9.11. � 37
xlwings��Excel�� #N/A waiting... ��Excel��

�� async_mode='threading' ��API��I/O��
��I/O�� time.sleep ��:
import time
@xw.func(async_mode='threading')
def myfunction(a):
time.sleep(5) # long running tasks
return a
You can use this function like any other xlwings function, simply by putting =myfunction("abcd")
into a cell (after you have imported the function, of course).
��xlwings��Excel 2010��xlwings��Excel��

CHAPTER 10
Matplotlib & Plotly Charts
10.1 Matplotlib
�� pictures.add() ��Matplotlib��Excel��
10.1.1 ��
��:
import matplotlib.pyplot as plt

fig = plt.figure()
plt.plot([1, 2, 3])
sheet.pictures.add(fig, name='MyPlot', update=True)
��: �� update=True,��Excel�� pictures.add() ��('MyPlot')��
10.1.2 ��Excel
� RunPython ��
��Windows�� UDF ��:
39
@xw.func
def myplot(n, caller):
fig = plt.figure()
plt.plot(range(int(n)))
caller.sheet.pictures.add(fig, name='MyPlot', update=True)
return 'Plotted with n={}'.format(n)
��UDF��B2��B1��
10.1.3 ��
�� pictures.add() �� xlwings.Picture().

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

>>> sht.pictures.add(fig, name='MyPlot', update=True,
left=sht.range('B5').left, top=sht.range('B5').top)
�:
>>> plot = sht.pictures.add(fig, name='MyPlot', update=True)

>>> plot.height /= 2
>>> plot.width /= 2
40 Chapter 10. Matplotlib & Plotly Charts

10.1. Matplotlib 41
10.1.4 ��Matplotlib��
��matplotlib figure ��

• ��PyPlot��:

fig = plt.figure()
plt.plot([1, 2, 3, 4, 5])
�:

plt.plot([1, 2, 3, 4, 5])
fig = plt.gcf()
• ��:
from matplotlib.figure import Figure

fig = Figure(figsize=(8, 6))
ax = fig.add_subplot(111)
ax.plot([1, 2, 3, 4, 5])
• ��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()
10.2 Plotly static charts
10.2.1 Prerequisites
In addition to plotly, you will need kaleido, psutil, and requests. The easiest way to get it is
via pip:
$ pip install kaleido psutil requests
or conda:
$ conda install -c conda-forge python-kaleido psutil requests
See also: https://plotly.com/python/static-image-export/

10.2.2 How to use
It works the same as with Matplotlib, however, rendering a Plotly chart takes slightly longer. Here
is a sample:
import plotly.express as px
# Plotly chart
df = px.data.iris()
fig = px.scatter(df, x="sepal_width", y="sepal_length", color="species")
# Add it to Excel
wb = xw.Book()
wb.sheets[0].pictures.add(fig, name='IrisScatterPlot', update=True)
10.2. Plotly static charts 43


CHAPTER 11
Jupyter Notebooks: Interact with Excel
When you work with Jupyter notebooks, you may use Excel as an interactive data viewer or
scratchpad from where you can load DataFrames. The two convenience functions view and load
make this really easy.
��: The view and load functions should exclusively be used for interactive work. If you write
scripts, use the xlwings API as introduced under �� and ��.
11.1 The view function
The view function accepts pretty much any object of interest, whether that’s a number, a string, a
nested list or a NumPy array or a pandas DataFrame. By default, it writes the data into an Excel
table in a new workbook. If you wanted to reuse the same workbook, provide a sheet object, e.g.
view(df, sheet=xw.sheets.active), for further options see view.
� 0.22.0 ��: Earlier versions were not formatting the output as Excel table
11.2 The load function
To load in a range in an Excel sheet as pandas DataFrame, use the load function. If you only select
one cell, it will auto-expand to cover the whole range. If, however, you select a specific range that
is bigger than one cell, it will load in only the selected cells. If the data in Excel does not have an
index or header, set them to False like this: xw.load(index=False), see also load.
0.22.0 ��.
45
46 Chapter 11. Jupyter Notebooks: Interact with Excel

CHAPTER 12
Command Line Client (CLI)
xlwings comes with a command line client. On Windows, type the commands into a Command
Prompt or Anaconda Prompt, on Mac, type them into a Terminal. To get an overview of all
commands, simply type xlwings and hit Enter:
addin Run "xlwings addin install" to install the Excel add-

in (will be copied to the XLSTART folder). Instead of
"install" you can also use "update", "remove" or
"status". Note that this command may take a while. Use
the "--unprotected" flag to install the add-in without
password protection. You can install your custom add-
in by providing the name or path via the --file flag,
e.g. "xlwings add-in install --file custom.xlam"
(New in 0.6.0, the unprotected flag was added in 0.20.4)
quickstart Run "xlwings quickstart myproject" to create a folder
called "myproject" in the current directory with an
Excel file and a Python file, ready to be used. Use
the "--standalone" flag to embed all VBA code in the
Excel file and make it work without the xlwings add-
in.
runpython macOS only: run "xlwings runpython install" if you
want to enable the RunPython calls without installing
the add-in. This will create the following file:
~/Library/Application
Scripts/com.microsoft.Excel/xlwings.applescript
(new in 0.7.0)
restapi Use "xlwings restapi run" to run the xlwings REST API
via Flask dev server. Accepts "--host" and "--port" as
(��)
47
(��)
optional arguments.
license xlwings PRO: Use "xlwings license update -k KEY" where
"KEY" is your personal (trial) license key. This will
update ~/.xlwings/xlwings.conf with the LICENSE_KEY
entry. If you have a paid license, you can run
"xlwings license deploy" to create a deploy key. This
is not available for trial keys.
config Run "xlwings config create" to create the user config
file (~/.xlwings/xlwings.conf) which is where the
settings from the Ribbon add-in are stored. It will
configure the Python interpreter that you are running
this command with. To reset your configuration, run
this with the "--force" flag which will overwrite your
current configuration.
(New in 0.19.5)
code Run "xlwings code embed" to embed all Python modules
of the workbook's dir in your active Excel file. Use
the "--file" flag to only import a single file by
providing its path. Requires xlwings PRO.
(Changed in 0.23.4)
permission "xlwings permission cwd" prints a JSON string that can
be used to permission the execution of all modules in
the current working directory via GET request.
"xlwings permission book" does the same for code that
is embedded in the active workbook.
(New in 0.23.4)
release Run "xlwings release" to configure your active
workbook to work with a one-click installer for easy
deployment. Requires xlwings PRO.
(New in 0.23.4)
48 Chapter 12. Command Line Client (CLI)

CHAPTER 13
��
13.1 Zip��
0.15.2 ��.
��Python��zip��UDF��zip��
��zip��Excel��(�� .zip )��Excel��xlwings��(��python��)�
�� PYTHONPATH �:
PYTHONPATH, "C:\path\to\myproject.zip"
13.2 RunFrozenPython
� 0.15.2 ��.
��PyInstaller�cx_Freeze�py2exe��Python��Python��
��:
• ��UDF�
• ��Windows��Mac��
• ��V0.15.6��0.15.2��
��:
49
Sub MySample()
RunFrozenPython "C:\path\to\dist\myproject\myproject.exe", "arg1 arg2"
End Sub
CHAPTER 14
��
14.1 ��: ��dll��
��:
1) xlwings32-<version>.dll � xlwings64-<version>.dll �� python.exe ��
pip � conda ��, �� .
2) �� Interpreter �� python ��Python�� 'python' is not
recognized as an internal or external command, operable program or batch
file., �� python.exe ��windows��(�� https://www.computerhope.com/issues/
ch000549.htm)� ��/��(�� C:\Users\MyUser\anaconda\pythonw.exe)��
14.2 Issue: Couldn’t find the local location of your OneDrive or Share-
Point
��:
On either the xlwings.conf sheet or on the xlwings.conf file under your home folder (for location
see User Config: Ribbon/Config File), add the following setting:
"ONEDRIVE_WIN", "C:\path\to\OneDrive"
Note: Don’t use quotes on the xlwings.conf sheet and if you are on macOS, use ONEDRIVE_MAC
instead. You need to use the ONEDRIVE setting, even if you use SharePoint.
51

CHAPTER 15
��
��v0.7.0�� Excel�� xlwings.Range �� User Defined Functions (UDFs)

��
Converters are explicitly 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 specified, 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.
��
��:
xw.Range UDFs
� xw.Range.options(convert=None, **kwargs). @arg('x', convert=None,
value **kwargs)
writ- xw.Range.options(convert=None, **kwargs). @ret(convert=None,
ing value = myvalue **kwargs)
��: ��(kwargs)�� numbers �� DataFrame �� index ��:
xw.Range('A1:C3').options(pd.DataFrame, index=False, numbers=int).value
53
15.1 ��
��:
• �� floats �� unicode �� datetime �� None �
• �/��: [None, 1.0, 'a string']
• 2�� [[None, 1.0, 'a string'], [None, 2.0, 'another string']]
��
• ndim
��1��2��

>>> 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
�� float �� int ��
>>> sht.range('A1').value = 1
>>> sht.range('A1').value
1.0
>>> sht.range('A1').options(numbers=int).value
1
��float��
��UDF��:
@xw.func
@xw.arg('x', numbers=int)
def myfunction(x):
# all numbers in x arrive as int
return x
Note: Excel�� int ��5��5��4��Python�� int �� raw

int �

• dates
�� datetime.datetime �� datetime.date ��
– Range:
>>> import datetime as dt

>>> sht.range('A1').options(dates=dt.date).value
– UDFs: @xw.arg('x', dates=dt.date)

�� datetime.datetime ��
>>> 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
�� None ��
– Range: >>> sht.range('A1').options(empty='NA').value
– UDFs: @xw.arg('x', empty='NA')
• transpose
��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
�� table , vertical � horizontal ��

>>> 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
(��)
15.1. �� 55
(��)
[[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]]
��: expand �� Range ��UDF��
• chunksize
When you read and write from or to big ranges, you may have to chunk them or you will hit
a timeout or a memory error. The ideal chunksize will depend on your system and size of
the array, so you will have to try out a few different chunksizes to find one that works well:
import pandas as pd
import numpy as np
data = np.arange(75_000 * 20).reshape(75_000, 20)
df = pd.DataFrame(data=data)
sheet['A1'].options(chunksize=10_000).value = df
And the same for reading:
# As DataFrame
df = sheet['A1'].expand().options(pd.DataFrame, chunksize=10_000).value
# As list of list
df = sheet['A1'].expand().options(chunksize=10_000).value
15.2 ��
xlwings�� dictionaries (��), NumPy arrays**(NumPy��), **Pandas Se-

ries**(Pandas��)� **DataFrames ��(��ndim��)�
It is also possible to write and register a custom converter for additional types, see below.
�� xlwings.Range ��UDF��
15.2.1 ��
��Excel�� transpose ��

>>> sht = xw.sheets.active

>>> sht.range('A1:B2').options(dict).value
{'a': 1.0, 'b': 2.0}
>>> sht.range('A4:B5').options(dict, transpose=True).value
{'a': 1.0, 'b': 2.0}
�� dict �� collections �� OrderedDict �
15.2.2 Numpy��
options: dtype=None, copy=True, order=None, ndim=None

�3�� np.array() �� ndim ��(��)��1��2��
��:

>>> 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.]])
15.2.3 Pandas��
options: dtype=None, copy=False, index=1, header=True

�2�� pd.Series() �� Pandas �� ndim ��
index: ��
��Excel��
�� True �� False �
header: ��
��Excel�� False �
15.2. �� 57
� index � header , 1 � True ��

��:

>>> 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
2001-01-05 5
2001-01-06 6
Name: series name, dtype: float64
>>> sht.range('D1', header=False).value = s
15.2.4 Pandas DataFrame��
options: dtype=None, copy=False, index=1, header=1

�2�� pd.DataFrame() �� ndim ��Pandas DataFrame�� ndim=2 �
index: ��
��Excel��
header: ��
��Excel��
� index � header , 1 � True ��
��:

15.2. �� 59

>>> df = sht.range('A1:D5').options(pd.DataFrame, header=2).value
>>> df
a b
c d e
ix
10 1 2 3
20 4 5 6
30 7 8 9
# Writing back using the defaults:

>>> sht.range('A1').value = df
# Writing back and changing some of the options, e.g. getting rid of the index:
>>> sht.range('B7').options(index=False).value = df
�� UDF ��(�� Range('A13') ��)
@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
15.2.5 xw.Range�‘�’��
��”��”�
• �� xlwings.Range ��:
@xw.func
@xw.arg('x', 'range')
def myfunction(x):
return x.formula
�� xlwings.Range ��x��

• �� (Windows�� pywin32 �Mac�� appscript )�� :
>>> 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))

15.3 ��
��
• �� xlwings.conversion.Converter �
• �� read_value � write_value ��
– � read_value �� value ��(Base converter)�� base ��
– � write_value �� value ��Excel�� base ��
� options �� xw.Range.options ��(�� xw.Range('A1').options(myoption='some
value') )��UDF� @arg � @ret ��:
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
• �� base ��( base ��)��: DictCoverter, NumpyArrayConverter,

PandasDataFrameConverter, PandasSeriesConverter
• �� (a) ��/�� (b) ��
��DataFrame��:
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:
(��)
15.3. �� 61
(��)
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
��:
# Fire up a Workbook and create a sample DataFrame

sht = xw.Book().sheets[0]
df = pd.DataFrame([[1.,10.],[2.,np.nan], [3., 30.]])
• DataFrames��:
# Write
sht.range('A1').value = df
# Read
sht.range('A1:C4').options(pd.DataFrame).value
• DataFrameDropna��:
# Write
sht.range('A7').options(DataFrameDropna, dropna=True).value = df
# Read
sht.range('A1:C4').options(DataFrameDropna, dropna=True).value
• ��(��):
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

• �DataFrameDropna��DataFrames��(��):
DataFrameDropna.register(pd.DataFrame)
# Write
sht.range('A13').options(dropna=True).value = df
# Read
sht.range('A1:C4').options(pd.DataFrame, dropna=True).value
��UDF��:
@xw.func
@arg('x', DataFrameDropna, dropna=True)
@ret(DataFrameDropna, dropna=True)
def myfunction(x):
# ...
return x
��: Python��Excel��Excel/COM��Python��
Pipelines are internally defined 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 defines how a list of lists (as delivered by the default
Accessor) should be turned into a Pandas DataFrame.
Converter (��)�� Accessor ��
15.3. �� 63

CHAPTER 16
��
��xlwings��Python��
• RunPython: �� RunPython ��Python�� mock_caller ��Excel�Python��
• UDFs: ��xlwings��
��Excel��Python��
��: �Mac�� xlwings �� (/Users/<User>/Library/Containers/
65
com.microsoft.Excel/Data/xlwings.log)�
16.1 RunPython
��Python�� my_module.py ��
# my_module.py
import os
def my_macro():
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() ��Python��Excel�� RunPython ��
Sub my_macro()
RunPython "import my_module; my_module.my_macro()"
End Sub
16.2 UDF��
��Windows��UDF��xlwings� Add-in & Settings ��VBA�� Debug UDFs ,

��Python��Python��(��PyCharm��PyDev�):
if __name__ == '__main__':
xw.serve()
��(� Ctrl-Alt-F9)��
��PyCharm��
��: ��
16.2. UDF�� 67
CHAPTER 17
��
��UDF��RunPython��xlwings��xlwings��VBA��
��UDF��
17.1 In-Excel SQL
xlwings��Excel�SQL(in-Excel SQL)��(sqlite��)��:
=sql(SQL Statement, table a, table b, ...)
��UDF��Windows��
69
CHAPTER 18
Custom Add-ins
0.22.0 ��.
Custom add-ins work on Windows and macOS and are white-labeled xlwings add-ins that include
all your RunPython functions and UDFs (as usual, UDFs work on Windows only). You can build
add-ins with and without an Excel ribbon.
The useful thing about add-in is that UDFs and RunPython calls will be available in all work-
books right out of the box without having to add any references via the VBA editor’s Tools >
References.... You can also work with standard xlsx files rather than xlsm files. This tutorial
assumes you’re familiar with how xlwings and its configuration works.
18.1 Quickstart
Start by running the following command on a command line (to create an add-in without a ribbon,
you would leave away the --ribbon flag):
$ xlwings quickstart myproject --addin --ribbon
This will create the familiar quickstart folder with a Python file and an Excel file, but this time,
the Excel file is in the xlam format.
• Double-click the Excel add-in to open it in Excel
• Add a new empty workbook (Ctrl+N on Windows or Command+N on macOS)
You should see a new ribbon tab called MyAddin like this:
The add-in and VBA project are currently always called myaddin, no matter what name you chose
in the quickstart command. We’ll see towards the end of this tutorial how we can change that, but
for now we’ll stick with it.
71
Compared to the xlwings add-in, the custom add-in offers an additional level of configuration: the
configuration sheet of the add-in itself which is the easiest way to configure simple add-ins with a
static configuration.
Let’s open the VBA editor by clicking on Alt+F11 (Windows) or Option+F11 (macOS). In our
project, select ThisWorkbook, then change the Property IsAddin from True to False, see the
following screenshot:
This will make the sheet _myaddin.conf visible (again, we’ll see how to change the name of myaddin
at the end of this tutorial):
• Activate the sheet config by renaming it from _myaddin.conf to myaddin.conf
• Set your Interpreter_Win/_Mac or Conda settings (you may want to take them over from
the xlwings settings for now)
Once done, switch back to the VBA editor, select ThisWorkbook again, and change IsAddin back
to True before you save your add-in from the VBA editor. Switch back to Excel and click the Run
button under the My Addin ribbon tab and if you’ve configured the Python interpreter correctly,
it will print Hello xlwings! into cell A1 of the active workbook.
18.2 Changing the Ribbon menu
To change the buttons and items in the ribbon menu or the Backstage View, download and install
the Office RibbonX Editor. While it is only available for Windows, the created ribbons will also
work on macOS. Open your add-in with it so you can change the XML code that defines your
buttons etc. You will find a good tutorial here. The callback function for the demo Run button is
in the RibbonMyAddin VBA module that you’ll find in the VBA editor.
18.3 Importing UDFs
To import your UDFs into the custom add-in, run the ImportPythonUDFsToAddin Sub towards the
end of the xlwings module (click into the Sub and hit F5). Remember, you only have to do this
whenever you change the function name, argument or decorator, so your end users won’t have to
deal with this.
72 Chapter 18. Custom Add-ins

18.3. Importing UDFs 73

If you are only deploying UDFs via your add-in, you probably don’t need a Ribbon menu and can
leave away the --ribbon flag in the quickstart command.
18.4 Configuration
As mentioned before, configuration works the same as with xlwings, so you could have your users
override the default configuration we did above by adding a myaddin.conf sheet on their workbook
or you could use the myaddin.conf file in the user’s home directory. For details see Add-in &
Settings.
18.5 Installation
If you want to permanently install your add-in, you can do so by using the xlwings CLI:
$ xlwings addin install --file C:\path\to\your\myproject.xlam
This, however, means that you will need to adjust the PYTHONPATH for it to find your Python code
(or move your Python code to somewhere where Python looks for it—more about that below under
deployment). The command will copy your add-in to the XLSTART folder, a special folder from
where Excel will open all files everytime you start it.
18.6 Renaming your add-in
Admittedly, this part is a bit cumbersome for now. Let’s assume, we would like to rename the
addin from MyAddin to Demo:
• In the xlwings VBA module, change Public Const PROJECT_NAME As String =
"myaddin" to Public Const PROJECT_NAME As String = "demo". You’ll find this line at
the top, right after the Declare statements.
• If you rely on the myaddin.conf sheet for your configuration, rename it to demo.conf
• Right-click the VBA project, select MyAddin Properties... and rename the Project Name
from MyAddin to Demo.
• If you use the ribbon, you want to rename the RibbonMyAddin VBA module to RibbonDemo.
To do this, select the module in the VBA editor, then rename it in the Properties window.
If you don’t see the Properties window, hit F4.
• Open the add-in in the Office RibbonX Editor (see above) and replace all occurrences of
MyAddin with Demo in the XML code.
And finally, you may want to rename your myproject.xlam file in the Windows explorer, but I
assume you have already run the quickstart command with the correct name, so this won’t be
necessary.

18.7 Deployment
By far the easiest way to deploy your add-in to your end-users is to build an installer via the
xlwings PRO offering. This will take care of everything and your end users literally just need
to double-click the installer and they are all set (no existing Python installation required and no
manual installation of the add-in or adjusting of settings required).
If you want it the free (but hard) way, you either need to build an installer yourself or you need
your users to install Python and the add-in and take care of placing the Python code in the correct
directory. This normally involves tweaking the following settings, for example in the myaddin.conf
sheet:
• Interpreter_Win/_Mac: if your end-users have a working version of Python, you can use
environment variables to dynamically resolve to the correct path. For example, if they have
Anaconda installed in the default location, you could use the following configuration:
Conda Path: %USERPROFILE%\anaconda3

Conda Env: base
Interpreter_Mac: $HOME/opt/anaconda3/bin/python
• PYTHONPATH: since you can’t have your Python source code in the XLSTART folder next to
the add-in, you’ll need to adjust the PYTHONPATH setting and add the folder to where the
Python code will be. You could point this to a shared drive or again make use of environment
variables so the users can place the file into a folder called MyAddin in their home directory,
for example. However, you can also place your Python code where Python looks for it, for
example by placing them in the site-packages directory of the Python distribution—an
easy way to achieve this is to build a Python package that you can install via pip.
18.7. Deployment 75

CHAPTER 19
��
0.13.0 ��.
19.1 ��
��xlwings��v0.13.0��xlwings��macOS��
�� :
import threading
from queue import Queue
num_threads = 4
def write_to_workbook():
while True:
rng = q.get()
rng.value = rng.address
print(rng.address)
q.task_done()
q = Queue()
for i in range(num_threads):
t = threading.Thread(target=write_to_workbook)
(��)
77
(��)
t.daemon = True
t.start()
for cell in ['A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7', 'A8', 'A9', 'A10']:
# THIS DOESN'T WORK - passing xlwings objects to threads will fail!
rng = xw.Book('Book1.xlsx').sheets[0].range(cell)
q.put(rng)
q.join()
�� Book ��:
import threading
from queue import Queue
num_threads = 4
def write_to_workbook():
while True:
cell_ = q.get()
xw.Book('Book1.xlsx').sheets[0].range(cell_).value = cell_
print(cell_)
q.task_done()
q = Queue()
for i in range(num_threads):
t = threading.Thread(target=write_to_workbook)
t.daemon = True
t.start()
for cell in ['A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7', 'A8', 'A9', 'A10']:
q.put(cell)
q.join()
19.2 ��
��: Multiprocessing is only supported on Windows!

��:
from multiprocessing import Pool

def write_to_workbook(cell):
xw.Book('Book1.xlsx').sheets[0].range(cell).value = cell
print(cell)
if __name__ == '__main__':
with Pool(4) as p:
p.map(write_to_workbook,
['A1', 'A2', 'A3', 'A4', 'A5', 'A6', 'A7', 'A8', 'A9', 'A10'])
19.2. �� 79

CHAPTER 20
��
��xlwings��:
1) Most importantly, open an issue on GitHub. Adding functionality should be user driven, so
only if you tell us about what you’re missing, it’s eventually going to find 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:

>>> sheet.api
<COMObject <unknown>> # Windows/pywin32
app(pid=2319).workbooks['Workbook1'].worksheets[1] # Mac/appscript
This works accordingly for the other objects like sheet.range('A1').api etc.
��VBA��pywin32��(�VBA��)��appscript��(��VBA)�� (!)
��2)��1)��Github��xlwings��(��Python��)�
20.1 ��VBA Range.WrapText ��
# Windows
sheet.range('A1').api.WrapText = True
# Mac
sheet.range('A1').api.wrap_text.set(True)
81

CHAPTER 21
xlwings��Office��
��Excel��Office��(�Outlook� Access�)��VBA��xlwings��Python��
��: ��v0.12.0��Windows��UDF�� RunPython ��
21.1 ��
1) ��Python��Excel�(�� User Defined Functions (UDFs))�

2) � Alt-F11 ��VBA��VBA�� xlwings_udfs �� Export File(��)... �� xlwings_udfs.
bas ��
3) ��Office��Access�� Alt-F11 ��VBA�� VBA Project�� Import File(��)...,
�� Microsoft Excel �� Microsoft Access � Microsoft Outlook
��: #Const App = "Microsoft Access"
4) ��xlwings VBA��(xlwings.bas)��xlwings��:

>>> xlwings.__path__
�� VBA��(��)Python��
21.2 ��
��Office��Excel��(��Access�Word��)��VBA��(��Outlook��office��
��Office�� PYTHONPATH ��Pyhon�� Config �
83
84 Chapter 21. xlwings��Office��

CHAPTER 22
xlwings PRO Overview
The purpose of xlwings PRO is to finance the continued maintenance and enhancement of xlwings.
This will allow you to rely on the package without being left with the dreaded “this library currently
has no active maintainers” message that happens to too many open-source packages after a couple
of years.
xlwings PRO offers access to additional functionality. All PRO features are marked with xlwings
PRO in the docs.
��: To get access to the additional functionality of xlwings PRO, you need a (trial) license key
and at least xlwings v0.19.0. Everything under the xlwings.pro subpackage is distributed under
a commercial license. To make use of xlwings PRO functionality beyond the trial, you will need to
subscribe to one of our paid plans.
22.1 PRO Features
• One-click Installer: Easily build your own Python installer including all dependencies—your
end users don’t need to know anything about Python.
• Embedded code: Store your Python source code directly in Excel for easy deployment.
• xlwings Reports: A template-based reporting mechanism, allowing business users to change
the layout of the report without having to touch the Python code.
• Markdown Formatting: Support for Markdown formatting of text in cells and shapes like e.g.,
text boxes.
• Permissioning of Code Execution: Control which users can run which Python modules via
xlwings.
85
• Table.update(): An easy way to keep an Excel table in sync with a pandas DataFrame
22.2 More Infos
• Pricing: https://www.xlwings.org/pricing
• Trial license key: https://www.xlwings.org/trial
86 Chapter 22. xlwings PRO Overview

CHAPTER 23
xlwings Reports
This feature requires xlwings PRO.

xlwings Reports is a solution for template-based Excel and PDF reporting, making the generation
of pixel-perfect factsheets really simple. xlwings Reports allows business users without Python
knowledge to create and maintain Excel templates without having to rely on a Python developer
after the intial setup has been done: xlwings Reports separates the Python code (pre- and post-
processing) from the Excel template (layout/formatting).
xlwings Reports supports all commonly required components:
• Text: Easily format your text via Markdown syntax.
• Tables: Write pandas DataFrames to Excel cells and Excel tables and format them dynam-
ically based on the number of rows.
• Charts: Use your favorite charting engine: Excel charts, Matplotlib, or Plotly.
• Images: You can include both raster (e.g., png) or vector (e.g., svg) graphics, including
dynamically generated ones, e.g., QR codes or plots.
• Multi-column Layout: Split your content up into e.g. a classic two column layout by using
Frames.
• Single Template: Generate reports in various languages, for various funds etc. based on a
single template.
• PDF Report: Generate PDF reports automatically and “print” the reports on PDFs in
your corporate layout for pixel-perfect results including headers, footers, backgrounds and
borderless graphics.
• Easy Pre-processing: Since everything is based on Python, you can connect with literally
any data source and clean it with pandas or some other library.
87
• Easy Post-processing: Again, with Python you’re just a few lines of code away from
sending an email with the reports as attachment or uploading the reports to your web server,
S3 bucket etc.
23.1 Quickstart
You can work on the workbook or the sheet level. Let’s start with rendering full workbooks!
23.1.1 Render Workbooks
If your template is a workbook, you can use the create_report function. Start by creating the
following Python script mytemplate.py:
from xlwings.pro.reports import create_report

import pandas as pd
df = pd.DataFrame(data={'one': [1, 2], 'two': [3, 4]})

book = create_report('mytemplate.xlsx', 'myreport.xlsx', title='MyTitle', df=df)
book.to_pdf()
Then create the following Excel file called mytemplate.xlsx:
Run the Python script (or run the code from a Jupyter notebook):
python mytemplate.py
This will copy the template and create the following output by replacing the variables in double
curly braces with the value from the Python variable:
In production, you’ll often want to run this in a separate and hidden Excel instance as well as use
fully qualified Path objects. It’s also often easier to collect the data into a data dictionary:
from pathlib import Path
import pandas as pd
(��)
88 Chapter 23. xlwings Reports

(��)
base_dir = Path(r'C:\Users\myuser\myreport')
data = dict(
title='MyTitle',
df=pd.DataFrame(data={'one': [1, 2], 'two': [3, 4]})
)
with xw.App(visible=False) as app:

book = app.create_report(base_dir / 'mytemplate.xlsx',
base_dir / 'myreport.xlsx',
**data)
book.to_pdf(base_dir / 'myreport.pdf')
��: By default, xlwings Reports overwrites existing values in templates if there is not enough free
space for your variable. If you want your rows to dynamically shift according to the height of your
array, use Frames.
��: By default, DataFrames don’t write out the index. If you need the index to appear in Excel,
use df.reset_index(), see DataFrames.
See also create_reports (API reference).
23.1. Quickstart 89
23.1.2 Render Sheets
Sometimes, it’s useful to render a single sheet instead of using the create_report function. This
is a workbook stored as Book1.xlsx:
Running the following code:
book = xw.Book('Book1.xlsx')
sheet = book.sheets['template'].copy(name='report')
sheet.render_template(title='A Demo!', table=[[1, 2], [3, 4]])
book.to_pdf()
Copies the template sheet first and then fills it in:

See also the mysheet.render_template (API reference).
0.22.0 ��.
23.2 DataFrames
To write DataFrames in a consistent manner to Excel, xlwings Reports ignores the DataFrame
indices. If you need to pass the index over to Excel, reset the index before passing in the DataFrame
to create_report or render_template: df.reset_index().
When working with pandas DataFrames, the report designer often needs to tweak the data. Thanks
to filters, they can do the most common operations directly in the template without the need to
write Python code. A filter is added to the placeholder in Excel by using the pipe character: {{
myplaceholder | myfilter }}. You can combine multiple filters by using multiple pipe charac-
ters: they are applied from left to right, i.e. the result from the first filter will be the input for the
next filter. Let’s start with an example before listing each filter with its details:

import pandas as pd
df = pd.DataFrame({'one': [1, 2, 3], 'two': [4, 5, 6], 'three': [7, 8, 9]})
sheet.render_template(df=df)
Available filters for DataFrames:

• noheader: Hide the column headers
23.2. DataFrames 91
Example:
{{ df | noheader }}
• header: Only return the header

Example:
{{ df | header }}
• sortasc: Sort in ascending order (indices are zero-based)

Example: sort by second, then by first column:
{{ df | sortasc(1, 0) }}
• sortdesc: Sort in descending order (indices are zero-based)

Example: sort by first, then by second column in descending order:
{{ df | sortdesc(0, 1) }}
• columns: Select/reorder columns and insert empty columns (indices are zero-based)
See also: colslice
Example: introduce an empty column (None) as the second column and switch the order of
the second and third column:
{{ df | columns(0, None, 2, 1) }}
��: Merged cells: you’ll also have to introduce empty columns if you are using merged cells
in your Excel template.
• mul, div, sum, sub: Apply an arithmetic operation (multiply, divide, sum, subtract) on a
column (indices are zero-based)
Syntax:
{{ df | operation(value, col_ix, fill_value) }}
fill_value is optional and determines whether empty cells are included in the operation or
not. To include empty values and thus make it behave like in Excel, set it to 0.
Example: multiply the first column by 100:
{{ df | mul(100, 0) }}
Example: multiply the first column by 100 and the second column by 2:

{{ df | mul(100, 0) | mul(2, 1) }}
Example: add 100 to the first column including empty cells:
{{ df | add(100, 0, 0) }}
• maxrows: Maximum number of rows (currently, only sum is supported as aggregation func-
tion)
If your DataFrame has 12 rows and you use maxrows(10, "Other") as filter, you’ll get a
table that shows the first 9 rows as-is and sums up the remaining 3 rows under the label
Other. If your data is unsorted, make sure to call sortasc/sortdesc first to make sure the
correct rows are aggregated.
See also: aggsmall, head, tail, rowslice
Syntax:
{{ df | maxrows(number_rows, label, label_col_ix) }}
label_col_ix is optional: if left away, it will label the first column of the DataFrame (index
is zero-based)
Examples:
{{ df | maxrows(10, "Other") }}
{{ df | sortasc(1)| maxrows(5, "Other") }}
{{ df | maxrows(10, "Other", 1) }}
• aggsmall: Aggregate rows with values below a certain threshold (currently, only sum is
supported as aggregation function)
If the values in the specified row are below the threshold values, they will be summed up in
a single row.
See also: maxrows, head, tail, rowslice
Syntax:
{{ df | aggsmall(threshold, threshold_col_ix, label, label_col_ix) }}
label_col_ix is optional: if left away, it will label the first column of the DataFrame (indices
are zero-based)
Examples:
{{ df | aggsmall(0.1, 2, "Other") }}
{{ df | sortasc(1) | aggsmall(0.1, 2, "Other") }}
{{ df | aggsmall(0.5, 1, "Other", 1) }}
• head: Only show the top n rows

See also: maxrows, aggsmall, tail, rowslice
23.2. DataFrames 93
Example:
{{ df | head(3) }}
• tail: Only show the bottom n rows

See also: maxrows, aggsmall, head, rowslice
Example:
{{ df | tail(5) }}
• rowslice: Slice the rows

See also: maxrows, aggsmall, head, tail
Syntax:
{{ df | rowslice(start_index, stop_index) }}
stop_index is optional: if left away, it will stop at the end of the DataFrame
Example: Show rows 2 to 4 (indices are zero-based and interval is half-open, i.e. the start is
including and the end is excluding):
{{ df | rowslice(2, 5) }}
Example: Show rows 2 to the end of the DataFrame:
{{ df | rowslice(2) }}
• colslice: Slice the columns

See also: columns
Syntax:
{{ df | colslice(start_index, stop_index) }}
stop_index is optional: if left away, it will stop at the end of the DataFrame
Example: Show columns 2 to 4 (indices are zero-based and interval is half-open, i.e. the start
is including and the end is excluding):
{{ df | colslice(2, 5) }}
Example: Show columns 2 to the end of the DataFrame:
{{ df | colslice(2) }}

23.3 Excel Tables
Using Excel tables is the recommended way to format tables as the styling can be applied dy-
namically across columns and rows. You can also use themes and apply alternating colors to
rows/columns. Go to Insert > Table and make sure that you activate My table has headers
before clicking on OK. Add the placeholder as usual on the top-left of your Excel table (note that
this example makes use of Frames):
Running the following script:

import pandas as pd
nrows, ncols = 3, 3
df = pd.DataFrame(data=nrows * [ncols * ['test']],
columns=[f'col { i}' for i in range(ncols)])
create_report('template.xlsx', 'output.xlsx', df=df)
Will produce the following report:

Headers of Excel tables are relatively strict, e.g. you can’t have multi-line headers or merged cells.
To get around these limitations, uncheck the Header Row checkbox under Table Design and use
the noheader filter (see DataFrame filters). This will allow you to design your own headers outside
of the Excel Table.
��:
• At the moment, you can only assign pandas DataFrames to tables
23.3. Excel Tables 95

23.4 Excel Charts
To use Excel charts in your reports, follow this process:

1. Add some sample/dummy data to your Excel template:
2. If your data source is dynamic, turn it into an Excel Table (Insert > Table). Make sure you
do this before adding the chart in the next step.
3. Add your chart and style it:
4. Reduce the Excel table to a 2 x 2 range and add the placeholder in the top-left corner (in our
example {{ chart_data }}) . You can leave in some dummy data or clear the values of the
Excel table:

23.4. Excel Charts 97


23.4. Excel Charts 99

5. Assuming your file is called mytemplate.xlsx and your sheet template like on the previous
screenshot, you can run the following code:
import pandas as pd
df = pd.DataFrame(data={'Q1': [1000, 2000, 3000],

'Q2': [4000, 5000, 6000],
'Q3': [7000, 8000, 9000]},
index=['North', 'South', 'West'])
book = xw.Book("mytemplate.xlsx")
sheet.render_template(chart_data=df.reset_index())
This will produce the following report, with the chart source correctly adjusted:
��: If you don’t want the source data on your report, you can place it on a separate sheet. It’s
easiest if you add and design the chart on the separate sheet, before cutting the chart and pasting
it on your report template. To prevent the data sheet from being printed when calling to_pdf, you
can give it a name that starts with # and it will be ignored.
23.5 Images
Images are inserted so that the cell with the placeholder will become the top-left corner of the
image. For example, write the following placeholder into you desired cell: {{ logo }}, then run
the following code:
from xlwings.pro.reports import Image
sheet.render_template(logo=Image(r'C:\path\to\logo.png'))
��: Image also accepts a pathlib.Path object instead of a string.
If you want to use vector-based graphics, you can use svg on Windows and pdf on macOS. You
can control the appearance of your image by applying filters on your placeholder.
Available filters for Images:
• width: Set the width in pixels (height will be scaled proportionally).

23.5. Images 101

Example:
{{ logo | width(200) }}
• height: Set the height in pixels (width will be scaled proportionally).

Example:
{{ logo | height(200) }}
• width and height: Setting both width and height will distort the proportions of the image!
Example:
{{ logo | height(200) | width(200) }}
• scale: Scale your image using a factor (height and width will be scaled proportionally).
Example:
{{ logo | scale(1.2) }}
• top: Top margin. Has the effect of moving the image down (positive pixel number) or up
(negative pixel number), relative to the top border of the cell. This is very handy to fine-tune
the position of graphics object.
See also: left
Example:
{{ logo | top(5) }}
• left: Left margin. Has the effect of moving the image right (positive pixel number) or left
(negative pixel number), relative to the left border of the cell. This is very handy to fine-tune
the position of graphics object.
See also: top
Example:
{{ logo | left(5) }}
23.6 Matplotlib and Plotly Plots
For a general introduction on how to handle Matplotlib and Plotly, see also: Matplotlib & Plotly
Charts. There, you’ll also find the prerequisites to be able to export Plotly charts as pictures.

23.6.1 Matplotlib
Write the following placeholder in the cell where you want to paste the Matplotlib plot: {{
lineplot }}. Then run the following code to get your Matplotlib Figure object:

fig = plt.figure()
plt.plot([1, 2, 3])
sheet.render_template(lineplot=fig)
23.6.2 Plotly
Plotly works practically the same:
import plotly.express as px
fig = px.line(x=["a","b","c"], y=[1,3,2], title="A line plot")

sheet.render_template(lineplot=fig)
To change the appearance of the Matplotlib or Plotly plot, you can use the same filters as with
images. Additionally, you can use the following filter:
• format: allows to change the default image format from png to e.g., vector, which will
export the plot as vector graphics (svg on Windows and pdf on macOS). As an example, to
make the chart smaller and use the vector format, you would write the following placeholder:
{{ lineplot | scale(0.8) | format("vector") }}
23.7 Text
You can work with placeholders in text that lives in cells or shapes like text boxes. If you have
more than just a few words, text boxes usually make more sense as they won’t impact the row
height no matter how you style them. Using the same gird formatting across worksheets is key to
getting a consistent multi-page report.
23.7. Text 103

23.7.1 Simple Text without Formatting
0.21.4 ��.
You can use any shapes like rectangles or circles, not just text boxes:
create_report('template.xlsx', 'output.xlsx', temperature=12.3)
This code turns this template:
into this report:
While this works for simple text, you will lose the formatting if you have any. To prevent that, use
a Markdown object, as explained in the next section.
If you will be printing on a PDF Layout with a dark background, you may need to change the font
color to white. This has the nasty side effect that you won’t see anything on the screen anymore.
To solve that issue, use the fontcolor filter:
• fontcolor: Change the color of the whole (!) cell or shape. The primary purpose of this
filter is to make white fonts visible in Excel. For most other colors, you can just change the
color in Excel itself. Note that this filter changes the font of the whole cell or shape and only
has an effect if there is just a single placeholder—if you need to manipulate single words, use
Markdown instead, see below. Black and white can be used as word, otherwise use a hex
notation of your desired color.
Example:

{{ mytitle | fontcolor("white") }}
{{ mytitle | fontcolor("#efefef") }}
23.7.2 Markdown Formatting
0.23.0 ��.
You can format text in cells or shapes via Markdown syntax. Note that you can also use place-
holders in the Markdown text that will take the values from the variables you supply via the
render_template or create_report functions:
from xlwings.pro import Markdown
mytext = """\
# Title
Text **bold** and *italic*
* A first bullet
* A second bullet
# {{ second_title }}
This paragraph has a line break.

Another line.
"""
# The first sheet requires a shape as shown on the screenshot

sheet = xw.sheets.active
sheet.render_template(myplaceholder=Markdown(mytext),
second_title='Another Title')
This will render this template with the placeholder in a cell and a shape:
Like this (this uses the default formatting):
For more details about Markdown, especially about how to change the styling, see Markdown
Formatting.
23.8 Date and Time
If a placeholder corresponds to a Python datetime object, by default, Excel will format that cell
as a date-formatted cell. This isn’t always desired as the formatting depends on the user’s regional
settings. To prevent that, format the cell in the Text format or use a TextBox and use the datetime
23.8. Date and Time 105


filter to format the date in the desired format. The datetime filter accepts the strftime syntax—for
a good reference, see e.g., strftime.org.
To control the language of month and weekday names, you’ll need to set the locale in your Python
code. For example, for German, you would use the following:
import locale
locale.setlocale(locale.LC_ALL, 'de_DE')
Example: The default formatting is December 1, 2020:
{{ mydate | datetime }}
Example: To apply a specific formatting, provide the desired format as filter argument. For
example, to get it in the 12/31/20 format:
{{ mydate | datetime("%m/%d/%y") }}
23.9 Number Format
The format filter allows you to format numbers by using the same mechanism as offered by Python’s
f-strings. For example, to format the placeholder performance=0.13 as 13.0%, you would do the
following:
{{ performance | format(".1%") }}
This corresponds to the following f-string in Python: f"{performance:0.1%}". To get an intro-

duction to the formatting string syntax, have a look at the Python String Format Cookbook.
23.10 Frames: Multi-column Layout
Frames are vertical containers in which content is being aligned according to their height. That is,
within Frames:
• Variables do not overwrite existing cell values as they do without Frames.
• Formatting is applied dynamically, depending on the number of rows your object uses in Excel
To use Frames, insert a Note with the text <frame> into row 1 of your Excel template wherever
you want a new dynamic column to start. Frames go from one <frame> to the next <frame> or the
right border of the used range.
How Frames behave is best demonstrated with an example: The following screenshot defines two
frames. The first one goes from column A to column E and the second one goes from column F to
column I, since this is the last column that is used.
You can define and format DataFrames by formatting
• one header and
23.9. Number Format 107

• one data row

If you use the noheader filter for DataFrames, you can leave the header away and format a single
data row. Alternatively, you could also use Excel Tables, as they can make formatting easier.
Running the following code:

import pandas as pd
df1 = pd.DataFrame([[1, 2, 3], [4, 5, 6], [7, 8, 9]])

df2 = pd.DataFrame([[1, 2, 3], [4, 5, 6], [7, 8, 9], [10, 11, 12], [13, 14, 15]])
data = dict(df1=df1.reset_index(), df2=df2.reset_index())
create_report('my_template.xlsx',
'my_report.xlsx',
**data)
will generate this report:

23.11 PDF Layout
Using the layout parameter in the to_pdf() command, you can “print” your Excel workbook on
professionally designed PDFs for pixel-perfect reports in your corporate layout including headers,
footers, backgrounds and borderless graphics:

import pandas as pd
df = pd.DataFrame([[1, 2, 3], [4, 5, 6], [7, 8, 9]])
book = create_report('template.xlsx',
'report.xlsx',
month_year = 'May 21',
summary_text = '...')
book.to_pdf('report.pdf', layout='monthly_layout.pdf')
Note that the layout PDF either needs to consist of a single page (will be used for each reporting
page) or will need to have the same number of pages as the report (each report page will be printed
on the corresponding layout page).
To create your layout PDF, you can use any program capable of exporting a file in PDF format such
as PowerPoint or Word, but for the best results consider using a professional desktop publishing
software such as Adobe InDesign.
23.11. PDF Layout 109


CHAPTER 24
Markdown Formatting

0.23.0 ��.
Markdown offers an easy and intuitive way of styling text components in your cells and shapes.
For an introduction to Markdown, see e.g., Mastering Markdown.
Markdown support is in an early stage and currently only supports:
• First-level headings
• Bold (i.e., strong)
• Italic (i.e., emphasis)
• Unordered lists
It doesn’t support nested objects yet such as 2nd-level headings, bold/italic within bullet points or
nested bullet points.
Let’s go through an example to see how everything works!
from xlwings.pro import Markdown, MarkdownStyle
mytext = """\
# Title
* A first bullet
* A second bullet
(��)
111
(��)
# Another Title

Another line.
"""
sheet = xw.Book("Book1.xlsx").sheets[0]
# Range
sheet['A1'].clear()
sheet['A1'].value = Markdown(mytext)
# Shape: The following expects a shape like a Rectangle on the sheet

sheet.shapes[0].text = ""
sheet.shapes[0].text = Markdown(mytext)
Running this code will give you this nicely formatted text:
But why not make things a tad more stylish? By providing a MarkdownStyle object, you can define
your style. Let’s change the previous example like this:
from xlwings.pro import Markdown, MarkdownStyle
mytext = """\
# Title
* A first bullet
* A second bullet
# Another Title
(��)
112 Chapter 24. Markdown Formatting

(��)

Another line.
"""
sheet = xw.Book("Book1.xlsx").sheets[0]
# Styling
style = MarkdownStyle()
style.h1.font.color = (255, 0, 0)
style.h1.font.size = 14
style.h1.font.name = 'Comic Sans MS' # No, that's not a font recommendation...
style.h1.blank_lines_after = 0
style.unordered_list.bullet_character = '\N{heavy black heart}' # Emojis are␣
,→fun!
# Range
sheet['A1'].clear()
sheet['A1'].value = Markdown(mytext, style) # <= provide your style object here
# Shape: The following expects a shape like a Rectangle on the sheet

sheet.shapes[0].text = ""
sheet.shapes[0].text = Markdown(mytext, style)
Here is the output of this:
You can override all properties, i.e., you can change the emphasis from italic to a red font or
anything else you want:
>>> style.strong.bold = False

>>> style.strong.color = (255, 0, 0)
>>> style.strong
strong.color: (255, 0, 0)
113
Markdown objects can also be used with template-based reporting, see xlwings Reports.
��: macOS currently doesn’t support the formatting (bold, italic, color etc.) of Markdown text
due to a bug with AppleScript/Excel. The text will be rendered correctly though, including bullet
points.
See also the API reference:

• Markdown class
• MarkdownStyle class
114 Chapter 24. Markdown Formatting

CHAPTER 25
Releasing xlwings Tools

xlwings PRO offers a simple way to deploy your xlwings tools to your end users without the usual
hassle that’s involved when installing and configuring Python and xlwings. End users don’t need
to know anything about Python as they only need to:
• Run an installer (one installer can power many different Excel workbooks)
• Use the Excel workbook as if it was a normal macro-enabled workbook
Advantages:
• Zero-config: The end user doesn’t have to configure anything throughout the whole process.
• No add-in required: No installation of the xlwings add-in required.
• Easy to update: If you want to deploy an update of your Python code, it’s often good
enough to distribute a new version of your workbook.
• No conflicts: The installer doesn’t touch any environment variables or registry keys and
will therefore not conflict with any existing Python installations.
• Deploy key: The release command will add a deploy key as your LICENSE_KEY. A deploy
key won’t expire and end users won’t need a paid subscription.
You as a developer need to create the one-click installer and run the xlwings release command
on the workbook. Let’s go through these two steps in detail!
There is a video walkthrough at: https://www.youtube.com/watch?v=yw36VT_n1qg
115
25.1 Step 1: One-Click Installer
As a subscriber of one of our paid plans, you will get access to a private GitHub repository, where
you can build your one-click installer:
1) Update your requirements.txt file with your dependencies: in your repository, start by
clicking on the requirements.txt file. This will open the following screen where you can
click on the pencil icon to edit the file (if you know your way around Git, you can also clone
the repository and use your local commit/push workflow instead):
After you’re done with your edits, click on the green Commit changes button.
��: If you are unsure about your dependencies, it’s best to work locally with a virtual
or Conda environment. In the virtual/Conda environment, only install packages that
you need, then run: pip list --format=freeze.
2) On the right-hand side of the landing page, click on Releases:
On the next screen, click on Draft a new release (note, the very first time, you will
see a green button called Create a new release instead):
This will bring up the following screen, where you’ll only have to fill in a Tag version
(e.g., 1.0.0), then click on the green button Publish release:
116 Chapter 25. Releasing xlwings Tools

25.1. Step 1: One-Click Installer 117

After 3-5 minutes (you can follow the progress under the Actions tab), you’ll find the
installer ready for download under Releases (ignore the zip and tar.gz files):
��: The one-click installer is a normal Python installation that you can use with multiple Excel
workbooks. Hence, you don’t need to create a separate installer for each workbook as long as they
all work with the same set of dependencies as defined by the requirements.txt file.
25.2 Step 2: Release Command (CLI)
The release command is part of the xlwings CLI (command-line client) and will prepare your Excel
file to work with the one-click installer generated in the previous step. Before anything else:
• Make sure that you have enabled Trust access to the VBA project object model under
File > Options > Trust Center > Trust Center Settings > Macro Settings. You
only need to do this once and since this is a developer setting, your end users won’t need to
bother about this. This setting is needed so that xlwings can update the Excel file with the
correct version of the VBA code.
• Run the installer from the previous step. This will not interfere with your existing Python
installation as it won’t touch your environment variables or registry. Instead, it will only
write to the following folder: %LOCALAPPDATA%\<installer-name>.
• Make sure that your local version of xlwings corresponds to the version of xlwings in the
requirements.txt from the installer. The easiest way to double-check this is to run pip
freeze on a Command Prompt or Anaconda Prompt. If your local version of xlwings differs,
install the same version as the installer uses via: pip install xlwings==<version from
installer>.
To work with the release command, you should have your workbook in the xlsm format and all the
Python modules in the same folder:

myworkbook.xlsm
mymodule_one.py
mymodule_two.py
...
You currently can’t organize your code in directories, but you can easily import mymodule_two
from mymodule_one.
Make sure that your Excel workbook is the active workbook, then run the following command on
a Command/Anaconda Prompt:
xlwings release
If this is the first time you are running this command, you will be asked a few questions. If you
are shown a [Y/n], you can hit Enter to accept the default as expressed by the capitalized letter:
• Name of your one-click installer? Type in the name of your one-click installer. If you
want to use a different Python distribution (e.g., Anaconda), you can leave this empty (but you
will need to update the xlwings.conf sheet with the Conda settings once the release command
has been run).
• Embed your Python code? [Y/n] This will copy the Python code into the sheets of the Excel
file. It will respect all Python files that are in the same folder as the Excel workbook.
• Hide the config sheet? [Y/n] This will hide the xlwings.conf sheet.
• Hide the sheets with the embedded Python code? [Y/n] If you embed your Python
code, this will hide all sheets with a .py ending.
• Allow your tool to run without the xlwings add-in? [Y/n] This will remove the
VBA reference to xlwings and copy in the xlwings VBA modules so that the end users don’t
need to have the xlwings add-in installed. Note that in this case, you will need to have your
RunPython calls bound to a button as you can’t use the Ribbon’s Run main button anymore.
Whatever answers you pick, you can always change them later by editing the xlwings.conf sheet
or by deleting the xlwings.conf sheet and re-running the xlwings release command. If you go
with the defaults, you only need to provide your end users with the one-click installer and the Excel
workbook, no external Python files are required.
25.3 Updating a Release
To edit your Python code, it’s easiest to work with external Python files and not with embedded
code. To stop xlwings from using the embedded code, simply delete all sheets with a .py ending
and the workbook will again use the external Python modules. Once you are done editing the
files, simply run the xlwings release command again, which will embed the updated code. If you
haven’t done any changes to your dependencies (i.e., you haven’t upgraded a package or introduced
a new one), you only need to redeploy your Excel workbook to have the end users get the update.
If you did make changes to the requirements.txt and release a new one-click installer, you will
need to have the users install the new version of the installer first.
25.3. Updating a Release 119

��: Every time you change the xlwings version in requirements.txt of your one-click installer,
make sure to upgrade your local xlwings installatino to the same version and run xlwings release
again!
25.4 Embedded Code Explained
When you run the xlwings release command, your code will be embedded automatically (except
if you switch this behavior off). You can, however, also embed code directly: on a command line,
run the following command:
xlwings code embed
This will import all Python files from the current directory and paste them into Excel sheets
of the currently active workbook. Now, you can use RunPython as usual: RunPython "import
mymodule;mymodule.myfunction()".
Note that you can have multiple Excel sheets and import them like normal Python files. Consider
this example:
You can call the main function from VBA like so:

Sub RandomNumbers()
RunPython "import random_numbers;random_numbers.main()"
End Sub
��:
• UDFs modules don’t have to be added to the UDF Modules explicitly when using embedded
code. However, in contrast to how it works with external files, you currently need to re-import
the functions when you change them.
• While you can hide your sheets with your code, they will be written to a temporary directory
in clear text.
25.4. Embedded Code Explained 121


CHAPTER 26
Permissioning of Code Execution

xlwings allows you to control which Python modules are allowed to run from Excel. In order to use
this functionality, you need to run your own web server. You can choose between an HTTP POST
and a GET request for the permissioning:
• GET: This is the simpler option as you only need to host a static JSON file that you can
generate via the xlwings CLI. You can use any web server that is capable of serving static
files (e.g., nginx) or use a free external service like GitHub pages. However, every permission
change requires you to update the JSON file on the server.
• POST: This option relies on the web server to validate the incoming payload of the POST
request. While this requires custom logic on your end, you are able to connect it with any
internal system (such as a database or LDAP server) to dynamically decide whether a user
should be able to run a specific Python module through xlwings.
Before looking at both of these options in more detail, let’s go through the prerequisites and
configuration.
��: This feature does not stop users from running arbitrary Python code through Python directly.
Rather, think of it as a mechanism to prevent accidental execution of Python code from Excel via
xlwings.
26.1 Prerequisites
• This functionality requires every end user to have the requests and cryptography libraries
installed. You can install them via pip:
123
pip install requests cryptography
or via Conda:
conda install requests cryptography
• You need to have a LICENSE_KEY in the form of a trial key, a paid license key or a deploy key.
26.2 Configuration
While xlwings offers various ways to configure your workbook (see Configuration), it will only
respect the permissioning settings in the config file in the user’s home folder (on Windows, this is
%USERPROFILE%\.xlwings\xlwings.conf):
• To prevent end users from overwriting xlwings.conf, you’ll need to make sure that the file
is owned by the Administrator while giving end users read-only permissions.
• Add the following settings while replacing the PERMISSION_CHECK_URL and
PERMISSION_CHECK_METHOD (POST or GET) with the appropriate value for your case:
"LICENSE_KEY","YOUR_LICENSE_OR_DEPLOY_KEY"
"PERMISSION_CHECK_ENABLED","True"
"PERMISSION_CHECK_URL","https://myurl.com"
"PERMISSION_CHECK_METHOD","POST"
26.3 GET request
You can generate the static JSON file by using the xlwings CLI:
• Print the JSON string for all Python modules in a certain folder:
cd myfolder
xlwings permission cwd
• Print the JSON string for all embedded modules of the active workbook:
xlwings permission book
Both commands will print a JSON string similar to this one:
{
"modules": [
{
"file_name": "myfile.py",
"sha256": "cea259922207049a734c88930b5c09109deb6b55f692fd0832f4e57052d85896
,→",
(��)
124 Chapter 26. Permissioning of Code Execution

(��)
"machine_names": [
"DESKTOP-QQ27RP3"
]
},
{
"file_name": "myfile2.py",
"sha256": "355200bb9ae00fcec1d7b660e7dd95fb3dbf246a9db397a6daa2471458a8e6cb
,→ ",
"machine_names": [
"DESKTOP-QQ27RP3"
]
}
]
}
All you need to do at this point is:

• Add additional machines names e.g., "machine_names: [""DESKTOP-QQ27RP3",
"DESKTOP-XY12AS2"]. Alternatively, you can use the "*" wildcard if you want to al-
low the module to be used on all end user’s computers. In case of the wildcard, it will still
make sure that the file’s content hasn’t been changed by looking at its sha256 hash. xlwings
uses import socket;socket.gethostname() as the machine name.
• Make this JSON file accessible via your web server and update the settings in the xlwings.
conf file accordingly (see above).
26.4 POST request
If you work with POST requests, xlwings will post a payload similar to the following:
{
"machine_name": "DESKTOP-QQ27RP3",
"modules": [
{
"file_name": "myfile.py",
"sha256":
,→"cea259922207049a734c88930b5c09109deb6b55f692fd0832f4e57052d85896"
},
{
"file_name": "myfile2.py",
"sha256":
,→"355200bb9ae00fcec1d7b660e7dd95fb3dbf246a9db397a6daa2471458a8e6cb"
}
]
}
26.4. POST request 125

It is now up to you to validate this request and:

• Return the HTTP status code 200 (“success”) if the user is allowed to run the code of these
modules
• Return the HTTP status code 403 (“forbidden”) if the user is not allowed to run the code of
these modules
Note that xlwings only checks for HTTP status code 200, so any other status code will fail.
26.5 Implementation Details & Limitations
• Currently, RunPython and user-defined functions (UDFs) are supported. RunFrozenPython

is not supported.
• Permissions checks are only done when the Python module is run via Excel/xlwings, it has
no effect on Python code that is run from Python directly.
• RunPython won’t allow you to run code that uses the from x import y syntax. Use import
x;x.y instead.
• The answer of the permissioning server is cached for the duration of the Python session. For
UDFs, this means until the functions are re-imported or the Restart UDF Server button is
clicked or until Excel is restarted. The same is true if you run RunPython with the Use UDF
Server option. By default, however, RunPython starts a new Python session every time, so
it will contact the server whenever you call RunPython.
• Only top-level modules are checked, i.e. modules that are imported as UDFs or run via
RunPython call. Any modules that are imported as dependencies of these modules are not
checked.
• RunPython with external Python source files depends on logic in the VBA part of xlwings.
UDFs and RunPython calls that use embedded code will only rely on Python to perform the
permissioning check.
126 Chapter 26. Permissioning of Code Execution

CHAPTER 27
Python API
27.1 ��
xlwings.view(obj, sheet=None, table=True, chunksize=5000)

��
��: Only use this in an interactive context like e.g. a Jupyter notebook! Don’t use this in a
script as it depends on the active book.
��
• obj (any type with built-in converter) – ��numpy arrays�
pandas dataframes
• sheet (Sheet, default None) – ��
• table (bool, default True) – If your object is a pandas DataFrame, by
default it is formatted as an Excel Table
• chunksize (int, default 5000) – Chunks the loading of big arrays.
��

>>> import pandas as pd
(��)
127
(��)
>>> df = pd.DataFrame(np.random.rand(10, 4), columns=['a', 'b', 'c', 'd'])
>>> xw.view(df)
See also: load

� 0.22.0 ��.
xlwings.load(index=1, header=1, chunksize=5000)
Loads the selected cell(s) of the active workbook into a pandas DataFrame. If you select a
single cell that has adjacent cells, the range is auto-expanded (via current region) and turned
into a pandas DataFrame. If you don’t have pandas installed, it returns the values as nested
lists.
��: Only use this in an interactive context like e.g. a Jupyter notebook! Don’t use this in a
script as it depends on the active book.
��
• index (bool or int, default 1) – Defines the number of columns on
the left that will be turned into the DataFrame’s index
• header (bool or int, default 1) – Defines the number of rows at the
top that will be turned into the DataFrame’s columns
• chunksize (int, default 5000) – Chunks the loading of big arrays.
��

>>> xw.load()
See also: view

� 0.23.1 ��.
27.2 ��
27.2.1 Apps
class xlwings.main.Apps(impl)
�� app ��:

>>> xw.apps
Apps([<Excel App 1668>, <Excel App 1644>])
128 Chapter 27. Python API

active
��app�
0.9.0 ��.
add()
��App��App��app��App��
count
��app��
0.9.0 ��.
keys()
��PID��App��Excel��
0.13.0 ��.
27.2.2 App
class xlwings.App(visible=None, spec=None, add_book=True, impl=None)

An app corresponds to an Excel instance and should normally be used as context manager
to make sure that everything is properly cleaned uup again and to prevent zombie processes.
New Excel instances can be fired up like so:

print(app.books)
��app��apps��:
>>> xw.apps
Apps([<Excel App 1668>, <Excel App 1644>])
>>> xw.apps[1668] # get the available PIDs via xw.apps.keys()
<Excel App 1668>
>>> xw.apps.active
<Excel App 1668>
��
• visible (bool, default None) – ��ap-
p��visible=True�
• spec (str, default None) – ��Mac��, ��Excel�� /Applications/
Microsoft Office 2011/Microsoft Excel � /Applications/Microsoft
Excel``�Windows��, ��xlwings��Excel�� ``�� > �� Office ��
��: �Mac��, ��xlwings��Excel��Mac��Excel��Windows��Excel��Ex
27.2. �� 129

activate(steal_focus=False)
��Excel��
�� steal_focus (bool, default False) – ��True, �Ex-
cel��Python��Excel�
0.9.0 ��.
api
��( pywin32 � appscript ��)�
0.9.0 ��.
books
��
0.9.0 ��.
calculate()
��
0.3.6 ��.
calculation
��calculation��: 'manual'(��) , 'automatic'(��) , 'semiautomatic'(��)
��

>>> wb = xw.Book()
>>> wb.app.calculation = 'manual'
� 0.9.0 ��.
create_report(template=None, output=None, book_settings=None, **data)
This function requires xlwings PRO.
This is a convenience wrapper around mysheet.render_template
Writes the values of all key word arguments to the output file according to the template
and the variables contained in there (Jinja variable syntax). Following variable types
are supported:
strings, numbers, lists, simple dicts, NumPy arrays, Pandas DataFrames, pictures and
Matplotlib/Plotly figures.
��
• template (str or path-like object) – Path to your Excel template,
e.g. r'C:\Path\to\my_template.xlsx'
• output (str or path-like object) – Path to your Report, e.g.
r'C:\Path\to\my_report.xlsx'

• book_settings (dict, default None) – A dictionary of xlwings.

Book parameters, for details see: xlwings.Book. For example:
book_settings={'update_links': False}.
• data (kwargs) – All key/value pairs that are used in the template.
��
• wb (xlwings Book)
• .. versionadded:: 0.24.4
cut_copy_mode
Gets or sets the status of the cut or copy mode. Accepts False for setting and returns
None, copy or cut when getting the status.
0.24.0 ��.
display_alerts
��True��False��Excel��
0.9.0 ��.
enable_events
True if events are enabled. Read/write boolean.
0.24.4 ��.
hwnd
��Window��(��Windows)�
0.9.0 ��.
interactive
True if Excel is in interactive mode. If you set this property to False, Excel blocks all
input from the keyboard and mouse (except input to dialog boxes that are displayed by
your code). Read/write Boolean. Note: Not supported on macOS.
0.24.4 ��.
kill()
��Excel app��
0.9.0 ��.
macro(name)
��Excel VBA��(sub)��
�� name (Name of Sub or Function with or without module name, e.g. 'Module1.
MyMacro' or 'MyMacro') –
��
��VBA��:
27.2. �� 131

Function MySum(x, y)
MySum = x + y
End Function
��:

>>> app = xw.App()
>>> my_sum = app.macro('MySum')
>>> my_sum(1, 2)
3
��: Book.macro()
0.9.0 ��.
pid
��app�PID�
0.9.0 ��.
properties(**kwargs)
Context manager that allows you to easily change the app’s properties temporarily. Once
the code leaves the with block, the properties are changed back to their previous state.
Note: Must be used as context manager or else will have no effect. Also, you can only
use app properties that you can both read and write.
��
app = App()
# Sets app.display_alerts = False

with app.properties(display_alerts=False):
# do stuff
# Sets app.calculation = 'manual' and app.enable_events = True

with app.properties(calculation='manual', enable_events=True):
# do stuff
# Makes sure the status bar is reset even if an error happens in the␣
,→with block
with app.properties(status_bar='Calculating...'):
# do stuff
0.24.4 ��.
quit()
��

0.3.3 ��.
range(cell1, cell2=None)
��, �� Range() �
0.9.0 ��.
screen_updating
��(�� False )��screen_updating�� True�
0.3.3 ��.
selection
��
0.9.0 ��.
startup_path
Returns the path to XLSTART which is where the xlwings add-in gets copied to by doing
xlwings addin install.
0.19.4 ��.
status_bar
Gets or sets the value of the status bar. Returns False if Excel has control of it.
0.20.0 ��.
version
��Excel��
��

>>> xw.App().version
VersionNumber('15.24')
>>> xw.apps[10559].version.major
15
� 0.9.0 ��.
visible
��Excel�visible�� True � False �
0.3.3 ��.
27.2.3 Books
class xlwings.main.Books(impl)
� �� :
27.2. �� 133


>>> xw.books # active app
Books([<Book [Book1]>, <Book [Book2]>])
>>> xw.apps[10559].books # specific app, get the PIDs via xw.apps.keys()
Books([<Book [Book1]>, <Book [Book2]>])
0.9.0 ��.
active
��
add()
��
open(fullname, update_links=None, read_only=None, format=None, password=None,
write_res_password=None, ignore_read_only_recommended=None, ori-
gin=None, delimiter=None, editable=None, notify=None, converter=None,
add_to_mru=None, local=None, corrupt_load=None)
��
��
• fullname (str or path-like object) – �� r'C:\path\to\file.
xlsx' � 'file.xlsm' ��
• Parameters (Other) – �� xlwings.Book()
�� Book
�� Book that has been opened.
27.2.4 Book
class xlwings.Book(fullname=None, update_links=None, read_only=None, for-

mat=None, password=None, write_res_password=None, ig-
nore_read_only_recommended=None, origin=None, delim-
iter=None, editable=None, notify=None, converter=None,
add_to_mru=None, local=None, corrupt_load=None, impl=None)
��book��books��:

>>> xw.books[0]
<Book [Book1]>
�� xw.Book ��: ��app��book��book��app�� xw.books

��app, ��:
>>> app = xw.App() # or something like xw.apps[10559] for existing apps,␣

,→get the PIDs via xw.apps.keys()
>>> app.books['Book1']

xw.Book xw.books
�� xw.Book() xw.books.add()
�� xw.Book('Book1') xw.books['Book1']
�� xw.Book(r'C:/path/to/file. xw.books.open(r'C:/path/to/file.
xlsx') xlsx')
��
• fullname (str or path-like object, default None) – ��(��
xlsx , xlsm �)��
• update_links (bool, default None) – ��
• read_only (bool, default False) – �� True ��
• format (str) – ��
• password (str) – ��
• write_res_password (str) – ��
• ignore_read_only_recommended (bool, default False) – �� True
��
• origin (int) – ��XlPlatform��[��1��Mac��2��Windows��3��Dos��]
• delimiter (str) – ��format��6��
• editable (bool, default False) – ��Excel4.0��
• notify (bool, default False) – ��
• converter (int) – ��
• add_to_mru (bool, default False) – ��
• local (bool, default False) – If True, saves files against the language
of Excel, otherwise against the language of VBA. Not supported on macOS.
• corrupt_load (int, default xlNormalLoad) – ��xlNormal-
Load�xlRepairFile�xlExtractData��macOS��
activate(steal_focus=False)
��
�� steal_focus (bool, default False) – ��True, ��Python��Excel�
api
0.9.0 ��.
app
��app��
0.9.0 ��.
27.2. �� 135

classmethod caller()
References the calling book when the Python function is called from Excel via
RunPython. Pack it into the function being called from Excel, e.g.:
def my_macro():
wb.sheets[0].range('A1').value = 1
��Python��, �� xw.Book.set_mock_caller() �

0.3.0 ��.
close()
��
0.1.1 ��.
fullname
��
macro(name)
�Excel VBA��
�� name (Name of Sub or Function with or without module name, e.g. 'Module1.
MyMacro' or 'MyMacro') –
��
��VBA��:
Function MySum(x, y)
MySum = x + y
End Function
��:

>>> wb = xw.books.active
>>> my_sum = wb.macro('MySum')
>>> my_sum(1, 2)
3
�� App.macro()
0.7.1 ��.
name
��

names
��
� 0.9.0 ��.
save(path=None)
��Excel��SaveAs()��
�� path (str or path-like object, default None) – ��
��

>>> wb = xw.Book()
>>> wb.save()
>>> wb.save(r'C:\path\to\new_file_name.xlsx')
0.3.1 ��.
selection
��
0.9.0 ��.
set_mock_caller()
��Pyhton��Excel��RunPyton�� xw.Book.caller() ��
��
# This code runs unchanged from Excel via RunPython and from Python␣
,→directly
import os
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()
0.3.1 ��.
sheets
��
0.9.0 ��.
27.2. �� 137

to_pdf(path=None, include=None, exclude=None, layout=None, ex-

clude_start_string=’#’, show=False)
Exports the whole Excel workbook or a subset of the sheets to a PDF file. If you want
to print hidden sheets, you will need to list them explicitely under include.
��
• path (str or path-like object, default None) – Path to the PDF
file, defaults to the same name as the workbook, in the same directory. For
unsaved workbooks, it defaults to the current working directory instead.
• include (int or str or list, default None) – Which sheets to in-
clude: provide a selection of sheets in the form of sheet indices (1-based
like in Excel) or sheet names. Can be an int/str for a single sheet or a
list of int/str for multiple sheets.
• exclude (int or str or list, default None) – Which sheets to ex-
clude: provide a selection of sheets in the form of sheet indices (1-based
like in Excel) or sheet names. Can be an int/str for a single sheet or a
list of int/str for multiple sheets.
• layout (str or path-like object, default None) – This argument
requires xlwings PRO.
Path to a PDF file on which the report will be printed. This is ideal for
headers and footers as well as borderless printing of graphics/artwork.
The PDF file either needs to have only 1 page (every report page uses the
same layout) or otherwise needs the same amount of pages as the report
(each report page is printed on the respective page in the layout PDF).
0.24.3 ��.
• exclude_start_string (str, default '#') – Sheet names that start
with this character/string will not be printed.
0.24.4 ��.
• show (bool, default False) – Once created, open the PDF file with
the default application.
0.24.6 ��.
��
>>> wb = xw.Book()
>>> wb.sheets[0]['A1'].value = 'PDF'
>>> wb.to_pdf()
See also xlwings.Sheet.to_pdf()

0.21.1 ��.

27.2.5 PageSetup
class xlwings.main.PageSetup(impl)
api
0.24.2 ��.
print_area
Gets or sets the range address that defines the print area.
��
>>> mysheet.page_setup.print_area = '$A$1:$B$3'

>>> mysheet.page_setup.print_area
'$A$1:$B$3'
>>> mysheet.page_setup.print_area = None # clear the print_area
0.24.2 ��.
27.2.6 Sheets
class xlwings.main.Sheets(impl)
�� sheet ��:

>>> xw.sheets # active book
Sheets([<Sheet [Book1]Sheet1>, <Sheet [Book1]Sheet2>])
>>> xw.Book('Book1').sheets # specific book
Sheets([<Sheet [Book1]Sheet1>, <Sheet [Book1]Sheet2>])
0.9.0 ��.
active
��(Sheet)�
add(name=None, before=None, after=None)
��
��
• name (str, default None) – ��Excel��
• before (Sheet, default None) – ��
• after (Sheet, default None) – ��
27.2. �� 139

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

��sheet��sheets��:

>>> wb = xw.Book()
>>> wb.sheets[0]
<Sheet [Book1]Sheet1>
>>> wb.sheets['Sheet1']
>>> wb.sheets.add()
� 0.9.0 ��.
activate()
��sheet��
api
0.9.0 ��.
autofit(axis=None)
��
�� axis (string, default None) –
• �� rows � r
• �� columns � c
• ��
��

>>> wb = xw.Book()
>>> wb.sheets['Sheet1'].autofit('c')
>>> wb.sheets['Sheet1'].autofit('r')
>>> wb.sheets['Sheet1'].autofit()
0.2.3 ��.
book
��
cells
��(��)�
0.9.0 ��.

charts
�� Charts
0.9.0 ��.
clear()
��
clear_contents()
��
copy(before=None, after=None, name=None)
Copy a sheet to the current or a new Book. By default, it places the copied sheet after
all existing sheets in the current Book. Returns the copied sheet.
0.22.0 ��.
��
• before (sheet object, default None) – The sheet object before which
you want to place the sheet
• after (sheet object, default None) – The sheet object after which
you want to place the sheet, by default it is placed after all existing
sheets
• name (str, default None) – The sheet name of the copy
�� Sheet object – The copied sheet
�� Sheet
��
# Create two books and add a value to the first sheet of the first book
first_book = xw.Book()
second_book = xw.Book()
first_book.sheets[0]['A1'].value = 'some value'
# Copy to same Book with the default location and name

first_book.sheets[0].copy()
# Copy to same Book with custom sheet name

first_book.sheets[0].copy(name='copied')
# Copy to second Book requires to use before or after

first_book.sheets[0].copy(after=second_book.sheets[0])
delete()
��
index
��(��Excel��1��)�
27.2. �� 141

name
��
names
��(��”SheetName!” (��!)��)�
0.9.0 ��.
page_setup
Returns a PageSetup object.
0.24.2 ��.
pictures
�� Pictures
0.9.0 ��.
range(cell1, cell2=None)
��, �� Range() �
0.9.0 ��.
render_template(**data)
This method requires xlwings PRO.
Replaces all Jinja variables (e.g {{ myvar }}) in the sheet with the keyword argument
that has the same name. Following variable types are supported:
strings, numbers, lists, simple dicts, NumPy arrays, Pandas DataFrames, PIL Image
objects that have a filename and Matplotlib figures.
0.22.0 ��.
�� data (kwargs) – All key/value pairs that are used in the template.
�� sheet
�� xlwings Sheet
��

>>> book = xw.Book()
>>> book.sheets[0]['A1:A2'].value = '{{ myvar }}'
>>> book.sheets[0].render_template(myvar='test')
See also xlwings.pro.reports.create_report()

select()
��
0.9.0 ��.

shapes
�� Shapes
0.9.0 ��.
tables
See Tables
0.21.0 ��.
to_pdf(path=None, layout=None, show=False)
Exports the sheet to a PDF file.
��
• path (str or path-like object, default None) – Path to the PDF
file, defaults to the name of the sheet in the same directory of the work-
book. For unsaved workbooks, it defaults to the current working directory
instead.
• layout (str or path-like object, default None) – This argument
requires xlwings PRO.
Path to a PDF file on which the report will be printed. This is ideal for
headers and footers as well as borderless printing of graphics/artwork.
The PDF file either needs to have only 1 page (every report page uses the
same layout) or otherwise needs the same amount of pages as the report
(each report page is printed on the respective page in the layout PDF).
0.24.3 ��.
• show (bool, default False) – Once created, open the PDF file with
the default application.
0.24.6 ��.
��
>>> wb = xw.Book()
>>> sheet = wb.sheets[0]
>>> sheet['A1'].value = 'PDF'
>>> sheet.to_pdf()
See also xlwings.Book.to_pdf()

0.22.3 ��.
used_range
��
��
�� xw.Range
0.13.0 ��.
27.2. �� 143

visible
Gets or sets the visibility of the Sheet (bool).
0.21.1 ��.
27.2.8 Range
class xlwings.Range(cell1=None, cell2=None, **options)

��
��
• cell1 (str or tuple or Range) – ��A1��xw.Range��(��
‘A1:B2’ )��
• cell2 (str or tuple or Range, default None) – ��A1��
xw.Range ��
��
��:
xw.Range('A1')
xw.Range('A1:C3')
xw.Range((1,1))
xw.Range((1,1), (3,3))
xw.Range('NamedRange')
xw.Range(xw.Range('A1'), xw.Range('B2'))
��:
xw.books['MyBook.xlsx'].sheets[0].range('A1')
add_hyperlink(address, text_to_display=None, screen_tip=None)

��(��)��
��
• address (str) – ��
• text_to_display (str, default None) – ��
• screen_tip (str, default None) – ��’<address> -
��’
0.3.0 ��.
address
�� get_address() ��
0.9.0 ��.

api
0.9.0 ��.
autofit()
��
• �� xw.Range('A1:B2').columns.autofit()
• �� xw.Range('A1:B2').rows.autofit()
� 0.9.0 ��.
clear()
��
clear_contents()
��
color
��
To set the color, either use an RGB tuple (0, 0, 0) or a hex string like #efefef or an
Excel color constant. To remove the background, set the color to None, see Examples.
�� RGB
�� tuple
��

>>> wb = xw.Book()
>>> xw.Range('A1').color = (255, 255, 255) # or '#ffffff'
>>> xw.Range('A2').color
(255, 255, 255)
>>> xw.Range('A2').color = None
>>> xw.Range('A2').color is None
True
0.3.0 ��.
column
��
��
�� Integer
0.3.5 ��.
column_width
��(��)� Normal �� 0(��0)��
27.2. �� 145

�� None �
��: 0 <= �� <= 255
��: ��
��
�� float
0.4.0 ��.
columns
�� RangeColumns ��
0.9.0 ��.
copy(destination=None)
��
�� destination (xlwings.Range) – ��xlwings Range��
��
�� None
count
��
current_region
��(��)��Windows�� Ctrl-* �Mac�� shift-Ctrl-Space ��
��
�� Range object
delete(shift=None)
��
�� shift (str, default None) – �� left � up ��Excel��
��
�� None
end(direction)
�� Ctrl+Up , Ctrl+down , Ctrl+left , � Ctrl+right ��
�� direction (One of 'up', 'down', 'right', 'left') –
��

>>> wb = xw.Book()
>>> xw.Range('A1:B2').value = 1
>>> xw.Range('A1').end('down')
(��)

(��)
<Range [Book1]Sheet1!$A$2>
>>> xw.Range('B2').end('right')
<Range [Book1]Sheet1!$B$2>
0.9.0 ��.
expand(mode=’table’)
��(�� Range.end() ).
�� mode (str, default 'table') – �� 'down' , 'right' ,‘‘’table’‘‘ (=down +
right)�
��
�� Range
��

>>> 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
0.9.0 ��.
formula
��
formula2
Gets or sets the formula2 for the given Range.
formula_array
��
0.7.1 ��.
get_address(row_absolute=True, column_absolute=True, include_sheetname=False,
external=False)
�� address ��
��
• row_absolute (bool, default True) – �� True ��
• column_absolute (bool, default True) – �� True ��
• include_sheetname (bool, default False) – �� True ��
external=True ��
• external (bool, default False) – �� True ��
27.2. �� 147

��
�� str
��

>>> 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'
0.2.3 ��.
has_array
Are we part of an Array formula?
height
��
��
�� float
0.4.0 ��.
hyperlink
��(��)
��

>>> wb = xw.Book()
>>> xw.Range('A1').value
'www.xlwings.org'
>>> xw.Range('A1').hyperlink
'http://www.xlwings.org'
0.3.0 ��.
insert(shift=None, copy_origin=’format_from_left_or_above’)
��
��
• shift (str, default None) – �� right or down ��Excel��

• copy_origin (str, default format_from_left_or_above) – ��

format_from_left_or_above � format_from_right_or_below ��ma-
cOS��
��
�� None
last_cell
��
��
�� Range
��

>>> wb = xw.Book()
>>> rng = xw.Range('A1:E4')
>>> rng.last_cell.row, rng.last_cell.column
(4, 5)
0.3.5 ��.
left
��A��(point)��
��
�� float
0.6.0 ��.
merge(across=False)
Creates a merged cell from the specified Range object.
�� across (bool, default False) – True to merge cells in each row of the
specified Range as separate merged cells.
merge_area
Returns a Range object that represents the merged Range containing the specified cell.
If the specified cell isn’t in a merged range, this property returns the specified cell.
merge_cells
Returns True if the Range contains merged cells, otherwise False
name
��
0.4.0 ��.
note
Returns a Note object. Before the introduction of threaded comments, a Note was called
a Comment.
27.2. �� 149

0.24.2 ��.
number_format
��( number_format )�
��

>>> wb = xw.Book()
>>> xw.Range('A1').number_format
'General'
>>> xw.Range('A1:C3').number_format = '0.00%'
>>> xw.Range('A1:C3').number_format
'0.00%'
0.2.3 ��.
offset(row_offset=0, column_offset=0)
��
��
�� Range
0.3.0 ��.
options(convert=None, **options)
��Excel��(base converter)��
�� convert (object, default None) – ��dict, np.array, pd.DataFrame,
pd.Series �, ��
��
• ndim (int, default None) – ��
• numbers (type, default None) – �� int
• dates (type, default None) – �� datetime.date �� datetime.
datetime �
• empty (object, default None) – ��
• transpose (Boolean, default False) – ��
• expand (str, default None) – �� 'table' , 'down' , 'right' �
• chunksize (int) –
Use a chunksize, e.g. 10000 to prevent timeout or memory issues when reading o
of data. Works with all formats, including DataFrames, NumPy arrays,
and list of lists.
=> ��
��

�� Range object

0.7.0 ��.
paste(paste=None, operation=None, skip_blanks=False, transpose=False)
��
��
• paste (str, default None) – �� all_merging_conditional_formats,
all, all_except_borders, all_using_source_theme, column_widths,
comments, formats, formulas, formulas_and_number_formats,
validation, values, values_and_number_formats.
• operation (str, default None) – �� “add”, “divide”, “multiply”,
“subtract”�
• skip_blanks (bool, default False) – �� True ��
• transpose (bool, default False) – �� True ��
��
�� None
raw_value
��xlwings��(pywin32 � appscript)��
resize(row_size=None, column_size=None)
��
��
• row_size (int > 0) – ��(�� None , ��)�
• column_size (int > 0) – ��(�� None , ��)�
��
�� Range
0.3.0 ��.
row
��
��
�� Integer
0.3.5 ��.
row_height
�� point � �� None �
row_height��: 0 <= row_height <= 409.5
��
��
27.2. �� 151

�� float
0.4.0 ��.
rows
�� RangeRows ��
0.9.0 ��.
select()
��
0.9.0 ��.
shape
��
0.3.0 ��.
sheet
��
0.9.0 ��.
size
��
0.3.0 ��.
table
Returns a Table object if the range is part of one, otherwise None.
0.21.0 ��.
top
�� point ��
��
�� float
0.6.0 ��.
unmerge()
Separates a merged area into individual cells.
value
Gets and sets the values for the given Range. See see xlwings.Range.options() about
how to set options, e.g. to transform it into a DataFrame or how to set a chunksize.
��
�� : xlwings.Range.options() �
width
�� point ��
��
�� float

0.4.0 ��.
wrap_text
Returns True if the wrap_text property is enabled and False if it’s disabled. If not all
cells have the same value in a range, on Windows it returns None and on macOS False.
0.23.2 ��.
27.2.9 RangeRows
class xlwings.RangeRows(rng)
�� Range.rows �
��
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)
autofit()
��
count
��
0.9.0 ��.
27.2.10 RangeColumns
class xlwings.RangeColumns(rng)
�� Range.columns �
��
27.2. �� 153

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()
��
count
��
0.9.0 ��.
27.2.11 Shapes
class xlwings.main.Shapes(impl)
��( shape )��:

>>> xw.books['Book1'].sheets[0].shapes
Shapes([<Shape 'Oval 1' in <Sheet [Book1]Sheet1>>, <Shape 'Rectangle 1' in
,→<Sheet [Book1]Sheet1>>])
0.9.0 ��.
api
count
��
27.2.12 Shape
class xlwings.Shape(*args, **options)

� shapes ��:

>>> sht = xw.books['Book1'].sheets[0]
(��)

(��)
>>> sht.shapes[0] # or sht.shapes['ShapeName']
<Shape 'Rectangle 1' in <Sheet [Book1]Sheet1>>
� 0.9.0 ��.
activate()
��(shape)�
0.5.0 ��.
api
0.19.2 ��.
delete()
��
0.5.0 ��.
height
�� point �
0.5.0 ��.
left
�� point �
0.5.0 ��.
name
��
0.5.0 ��.
parent
��
0.9.0 ��.
scale_height(factor, relative_to_original_size=False, scale=’scale_from_top_left’)
factor [float] For example 1.5 to scale it up to 150%
relative_to_original_size [bool, optional] If False, it scales relative to current
height (default). For True must be a picture or OLE object.
scale [str, optional] One of scale_from_top_left (default),
scale_from_bottom_right, scale_from_middle
0.19.2 ��.
scale_width(factor, relative_to_original_size=False, scale=’scale_from_top_left’)
factor [float] For example 1.5 to scale it up to 150%
27.2. �� 155

relative_to_original_size [bool, optional] If False, it scales relative to current

width (default). For True must be a picture or OLE object.
scale [str, optional] One of scale_from_top_left (default),
scale_from_bottom_right, scale_from_middle
0.19.2 ��.
text
Returns or sets the text of a shape.
0.21.4 ��.
top
�� point �
0.5.0 ��.
type
��
0.9.0 ��.
width
�� point �
0.5.0 ��.
27.2.13 Charts
class xlwings.main.Charts(impl)
��( chart )��:

>>> xw.books['Book1'].sheets[0].charts
Charts([<Chart 'Chart 1' in <Sheet [Book1]Sheet1>>, <Chart 'Chart 1' in
0.9.0 ��.
add(left=0, top=0, width=355, height=211)
��
��
• left (float, default 0) – �� point �
• top (float, default 0) – �� point �
• width (float, default 355) – �� point �
• height (float, default 211) – �� point �
��
�� Chart

��

>>> 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
count
��
27.2.14 Chart
class xlwings.Chart(name_or_index=None, impl=None)

chart�� charts ��:

>>> sht.charts[0] # or sht.charts['ChartName']
<Chart 'Chart 1' in <Sheet [Book1]Sheet1>>
api
0.9.0 ��.
chart_type
Returns and sets the chart type of the chart. The following chart types are available:
3d_area, 3d_area_stacked, 3d_area_stacked_100, 3d_bar_clustered,
3d_bar_stacked, 3d_bar_stacked_100, 3d_column, 3d_column_clustered,
3d_column_stacked, 3d_column_stacked_100, 3d_line, 3d_pie, 3d_pie_exploded,
area, area_stacked, area_stacked_100, bar_clustered, bar_of_pie,
bar_stacked, bar_stacked_100, bubble, bubble_3d_effect, column_clustered,
column_stacked, column_stacked_100, combination, cone_bar_clustered,
cone_bar_stacked, cone_bar_stacked_100, cone_col, cone_col_clustered,
cone_col_stacked, cone_col_stacked_100, cylinder_bar_clustered,
cylinder_bar_stacked, cylinder_bar_stacked_100, cylinder_col,
cylinder_col_clustered, cylinder_col_stacked, cylinder_col_stacked_100,
doughnut, doughnut_exploded, line, line_markers, line_markers_stacked,
line_markers_stacked_100, line_stacked, line_stacked_100, pie,
pie_exploded, pie_of_pie, pyramid_bar_clustered, pyramid_bar_stacked,
pyramid_bar_stacked_100, pyramid_col, pyramid_col_clustered,
27.2. �� 157

pyramid_col_stacked, pyramid_col_stacked_100, radar, radar_filled,

radar_markers, stock_hlc, stock_ohlc, stock_vhlc, stock_vohlc, surface,
surface_top_view, surface_top_view_wireframe, surface_wireframe, xy_scatter,
xy_scatter_lines, xy_scatter_lines_no_markers, xy_scatter_smooth,
xy_scatter_smooth_no_markers
0.1.1 ��.
delete()
��
height
�� point �
left
�� point �
name
��
parent
��
0.9.0 ��.
set_source_data(source)
��
�� source (Range) – �� xw.books['Book1'].sheets[0].range('A1')
top
�� point �
width
�� point �
27.2.15 Pictures
class xlwings.main.Pictures(impl)
��( picture )��:

>>> xw.books['Book1'].sheets[0].pictures
Pictures([<Picture 'Picture 1' in <Sheet [Book1]Sheet1>>, <Picture 'Picture␣
,→2' in <Sheet [Book1]Sheet1>>])
0.9.0 ��.
add(image, link_to_file=False, save_with_document=True, left=None, top=None,
width=None, height=None, name=None, update=False, scale=None, for-
mat=None,
�� anchor=None)
��

• image (str or path-like object or matplotlib.figure.Figure) –

��Matplotlib��
• left (float, default None) – Left position in points, defaults to 0. If
you use top/left, you must not provide a value for anchor.
• top (float, default None) – Top position in points, defaults to 0. If
you use top/left, you must not provide a value for anchor.
• width (float, default None) – Width in points. Defaults to original
width.
• height (float, default None) – Height in points. Defaults to original
height.
• name (str, default None) – Excel��Excel�� ‘Picture 1’ �
• update (bool, default False) – ��
• scale (float, default None) – Scales your picture by the provided fac-
tor.
• format (str, default None) – Only used if image is a Matplotlib
or Plotly plot. By default, the plot is inserted in the “png” format,
but you may want to change this to a vector-based format like “svg”
on Windows (may require Microsoft 365) or “eps” on macOS for
better print quality. If you use 'vector', it will be using 'svg' on
Windows and 'eps' on macOS. To find out which formats your ver-
sion of Excel supports, see: https://support.microsoft.com/en-us/topic/
support-for-eps-images-has-been-turned-off-in-office-a069d664-4bcf-415e-a1b5-cbb0c334a840
• anchor (xw.Range, default None) – The xlwings Range object of where
you want to insert the picture. If you use anchor, you must not provide
values for top/left.
0.24.3 ��.
��
�� Picture
��
1. Picture

>>> sht.pictures.add(r'C:\path\to\file.png')
<Picture 'Picture 1' in <Sheet [Book1]Sheet1>>
2. Matplotlib
27.2. �� 159

>>> import matplotlib.pyplot as plt

>>> fig = plt.figure()
>>> plt.plot([1, 2, 3, 4, 5])
>>> sht.pictures.add(fig, name='MyPlot', update=True)
<Picture 'MyPlot' in <Sheet [Book1]Sheet1>>
api
count
��
27.2.16 Picture
class xlwings.Picture(impl=None)
�� pictures ��

>>> sht.pictures[0] # or sht.charts['PictureName']
<Picture 'Picture 1' in <Sheet [Book1]Sheet1>>
� 0.9.0 ��.
api
0.9.0 ��.
delete()
��
0.5.0 ��.
height
�� point�
0.5.0 ��.
left
�� point�
0.5.0 ��.
lock_aspect_ratio
True will keep the original proportion, False will allow you to change height and width
independently of each other (read/write).
0.24.0 ��.
name
��

0.5.0 ��.
parent
��
0.9.0 ��.
top
�� point�
0.5.0 ��.
update(image, format=None)
��
�� image (str or path-like object or matplotlib.figure.Figure) –
��Matplotlib��
0.5.0 ��.
width
�� point�
0.5.0 ��.
27.2.17 Names
class xlwings.main.Names(impl)
��( name )��:

>>> sht.names
[<Name 'MyName': =Sheet1!$A$3>]
0.9.0 ��.
add(name, refers_to)
��
��
• name (str) – ��
• refers_to (str) – �� A1��
��
�� Name
0.9.0 ��.
api
0.9.0 ��.
27.2. �� 161

count
��
27.2.18 Name
class xlwings.Name(impl)
��name��names��:

>>> sht.names[0] # or sht.names['MyName']
<Name 'MyName': =Sheet1!$A$3>
0.9.0 ��.
api
0.9.0 ��.
delete()
��
0.9.0 ��.
name
��
0.9.0 ��.
refers_to
�� A1��
0.9.0 ��.
refers_to_range
��
0.9.0 ��.
27.2.19 Note
class xlwings.main.Note(impl)
api
0.24.2 ��.
delete()
Delete the note.
0.24.2 ��.

text
Gets or sets the text of a note. Keep in mind that the note must already exist!
��
>>> sheet = xw.Book(...).sheets[0]

>>> sheet['A1'].note.text = 'mynote'
>>> sheet['A1'].note.text
>>> 'mynote'
0.24.2 ��.
27.2.20 Tables
class xlwings.main.Tables(impl)
A collection of all table objects on the specified sheet:

>>> xw.books['Book1'].sheets[0].tables
Tables([<Table 'Table1' in <Sheet [Book11]Sheet1>>, <Table 'Table2' in
0.21.0 ��.
add(source=None, name=None, source_type=None, link_source=None,
has_headers=True, destination=None, table_style_name=’TableStyleMedium2’)
Creates a Table to the specified sheet.
��
• source (xlwings range, default None) – An xlwings range object,
representing the data source.
• name (str, default None) – The name of the Table. By default, it uses
the autogenerated name that is assigned by Excel.
• source_type (str, default None) – This currently defaults to
xlSrcRange, i.e. expects an xlwings range object. No other options are
allowed at the moment.
• link_source (bool, default None) – Currently not implemented as
this is only in case source_type is xlSrcExternal.
• has_headers (bool or str, default True) – Indicates whether the
data being imported has column labels. Defaults to True. Possible values:
True, FAlse, 'guess'
• destination (xlwings range, default None) – Currently not imple-
mented as this is used in case source_type is xlSrcExternal.
27.2. �� 163

• table_style_name (str, default 'TableStyleMedium2') – Possible

strings: 'TableStyleLightN'' (where N is 1-21), 'TableStyleMediumN'
(where N is 1-28), 'TableStyleDarkN' (where N is 1-11)
��
�� Table
��

>>> sheet['A1'].value = [['a', 'b'], [1, 2]]
>>> table = sheet.tables.add(source=sheet['A1'].expand(), name='MyTable
,→')
>>> table
<Table 'MyTable' in <Sheet [Book1]Sheet1>>
27.2.21 Table
class xlwings.main.Table(*args, **options)

The table object is a member of the tables collection:

>>> sht.tables[0] # or sht.tables['TableName']
<Table 'Table 1' in <Sheet [Book1]Sheet1>>
0.21.0 ��.
api
data_body_range
Returns an xlwings range object that represents the range of values, excluding the header
row
display_name
Returns or sets the display name for the specified Table object
header_row_range
Returns an xlwings range object that represents the range of the header row
insert_row_range
Returns an xlwings range object representing the row where data is going to be inserted.
This is only available for empty tables, otherwise it’ll return None
name
Returns or sets the name of the Table.

parent
Returns the parent of the table.
range
Returns an xlwings range object of the table.
resize(range)
Resize a Table by providing an xlwings range object
0.24.4 ��.
show_autofilter
Turn the autofilter on or off by setting it to True or False (read/write boolean)
show_headers
Show or hide the header (read/write)
show_table_style_column_stripes
Returns or sets if the Column Stripes table style is used for (read/write boolean)
show_table_style_first_column
Returns or sets if the first column is formatted (read/write boolean)
show_table_style_last_column
Returns or sets if the last column is displayed (read/write boolean)
show_table_style_row_stripes
Returns or sets if the Row Stripes table style is used (read/write boolean)
show_totals
Gets or sets a boolean to show/hide the Total row.
table_style
Gets or sets the table style. See Tables.add for possible values.
totals_row_range
Returns an xlwings range object representing the Total row
update(data, index=True)
This method requires xlwings PRO
Updates the Excel table with the provided data. Currently restricted to DataFrames.
� 0.24.0 ��.
��
• data (pandas DataFrame) – Currently restricted to pandas DataFrames.
If you want to hide the index, set the first column as the index, e.g.
df.set_index('column_name').
• index (bool, default True) – Whether or not the index of a pandas
DataFrame should be written to the Excel table.
��
�� Table
27.2. �� 165

��
import pandas as pd
sheet = xw.Book('Book1.xlsx').sheets[0]
table_name = 'mytable'
# Sample DataFrame
nrows, ncols = 3, 3
df = pd.DataFrame(data=nrows * [ncols * ['test']],
columns=['col ' + str(i) for i in range(ncols)])
# Hide the index, then insert a new table if it doesn't exist yet,
# otherwise update the existing one
df = df.set_index('col 0')
if table_name in [table.name for table in sheet.tables]:
sheet.tables[table_name].update(df)
else:
mytable = sheet.tables.add(source=sheet['A1'], name=table_name).
,→update(df)
27.2.22 Font
class xlwings.main.Font(impl)
The font object can be accessed as an attribute of the range or shape object.
• mysheet['A1'].font
• mysheet.shapes[0].font
0.23.0 ��.
api
0.23.0 ��.
bold
Returns or sets the bold property (boolean).
>>> sheet['A1'].font.bold = True

>>> sheet['A1'].font.bold
True
0.23.0 ��.
color
Returns or sets the color property (tuple).

>>> sheet['A1'].font.color = (255, 0, 0) # or '#ff0000'

>>> sheet['A1'].font.color
(255, 0, 0)
0.23.0 ��.
italic
Returns or sets the italic property (boolean).
>>> sheet['A1'].font.italic = True

>>> sheet['A1'].font.italic
True
0.23.0 ��.
name
Returns or sets the name of the font (str).
>>> sheet['A1'].font.name = 'Calibri'

>>> sheet['A1'].font.name
Calibri
0.23.0 ��.
size
Returns or sets the size (float).
>>> sheet['A1'].font.size = 13
>>> sheet['A1'].font.size
13
0.23.0 ��.
27.2.23 Characters
class xlwings.main.Characters(impl)
The characters object can be accessed as an attribute of the range or shape object.
• mysheet['A1'].characters
• mysheet.shapes[0].characters
��: On macOS, characters are currently not supported due to bugs/lack of support in
AppleScript.
0.23.0 ��.
api
27.2. �� 167

0.23.0 ��.
font
Returns or sets the text property of a characters object.
>>> sheet['A1'].characters[1:3].font.bold = True

>>> sheet['A1'].characters[1:3].font.bold
True
0.23.0 ��.
text
Returns or sets the text property of a characters object.
>>> sheet['A1'].value = 'Python'

>>> sheet['A1'].characters[:3].text
Pyt
0.23.0 ��.
27.2.24 Markdown
27.2.25 MarkdownStyle
27.3 UDF��
xlwings.func(category=”xlwings”, volatile=False, call_in_wizard=True)

�� “Import Python UDFs” �� xlwings.func �� Function(��) ��Excel�
category [int or str, default “xlwings”] 1-14��
0.10.3 ��.
volatile [bool, default False] ��
0.10.3 ��.
call_in_wizard [bool, default True] ��False��
0.10.3 ��.
xlwings.sub()
�� xlwings.sub ��”Import Python UDFs”�� Sub (��)��Excel�
xlwings.arg(arg, convert=None, **options)
�� Range.options() �
��
� x ��2�numpy��:

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)
�� Range.options() �
��
1) ��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) ��
��: If your version of Excel supports the new native dynamic arrays, then you don’t have to
do anything special, and you shouldn’t use the expand decorator! To check if your version of
Excel supports it, see if you have the =UNIQUE() formula available. Native dynamic arrays
were introduced in Office 365 Insider Fast at the end of September 2018.
expand='table' �UDF�� =TODAY() ��(��)��

��Excel��:
import numpy as np
@xw.func
@xw.ret(expand='table')
def dynamic_array(n, m):
return np.arange(n * m).reshape((n, m))
0.10.0 ��.
27.4 Reports
27.4. Reports 169


CHAPTER 28
REST API
0.13.0 ��.
28.1 ��
xlwings offers an easy way to expose an Excel workbook via REST API both on Windows and
macOS. This can be useful when you have a workbook running on a single computer and want to
access it from another computer. Or you can build a Linux based web app that can interact with
a legacy Excel application while you are in the progress of migrating the Excel functionality into
your web app (if you need help with that, give us a shout).
��REST API��(��Flask>=1.0, �� pip install Flask):
xlwings restapi run
��Windows�PowerShell��Mac��GET��(�� “Book1” ��)� ��GET��(��

Postman � Insomnia ��REST API):
$ curl "http://127.0.0.1:5000/book/book1/sheets/0/range/A1:B2"
{
"address": "$A$1:$B$2",
"color": null,
"column": 1,
"column_width": 10.0,
"count": 4,
"current_region": "$A$1:$B$2",
"formula": [
[
(��)
171
(��)
"1",
"2"
],
[
"3",
"4"
]
],
"formula_array": null,
"height": 32.0,
"last_cell": "$B$2",
"left": 0.0,
"name": null,
"number_format": "General",
"row": 1,
"row_height": 16.0,
"shape": [
2,
2
],
"size": 4,
"top": 0.0,
"value": [
[
1.0,
2.0
],
[
3.0,
4.0
]
],
"width": 130.0
}
�� Ctrl-C ��

��xlwings��xlwings REST API�� Python API ��REST API��Ex-
cel��Excel��REST API��
��: ��GET��POST��GitHub��
172 Chapter 28. REST API

28.2 ��
xlwings restapi run ��Flask��http://127.0.0.1:5000�� --host � --port ��Flask��

FLASK_ENV=development ��
��Flask�� Flask docs
set FLASK_APP=xlwings.rest.api
flask run
�Mac��, � export FLASK_APP=xlwings.rest.api �� set FLASK_APP=xlwings.rest.api �

��, ��WSGI HTTP�� gunicorn (Mac��) � waitress (Mac/Windows��) ��API��,�gunicorn��:
gunicorn xlwings.rest.api:api ��waitress��(��API��):
from xlwings.rest.api import api

from waitress import serve
serve(wsgiapp, host='127.0.0.1', port=5000)
28.3 ��
��Python API��Python��0��(�� xw.books[0])��Excel��1��(�� xw.books(1)),REST API��0��, ��

/books/0.
28.4 ��
REST API�� xlwings.Range.options() ��

/book/book1/sheets/0/range/A1?expand=table&transpose=true
�� options �� value ��
28.5 Endpoint��
Endpoint ��
/book Book ��Excel��workbook(��)��workbook
/books Books ��Excel��
/apps Apps ��Excel��
28.2. �� 173

28.6 Endpoint��
28.6.1 /book
GET /book/<fullname_or_name>
��:
{
"app": 1104,
"fullname": "C:\\Users\\felix\\DEV\\xlwings\\scripts\\Book1.xlsx",
"name": "Book1.xlsx",
"names": [
"Sheet1!myname1",
"myname2"
],
"selection": "Sheet2!$A$1",
"sheets": [
"Sheet1",
"Sheet2"
]
}
GET /book/<fullname_or_name>/names
��:
{
"names": [
{
"name": "Sheet1!myname1",
"refers_to": "=Sheet1!$B$2:$C$3"
},
{
"name": "myname2",
"refers_to": "=Sheet1!$A$1"
}
]
}
GET /book/<fullname_or_name>/names/<name>
��:
{
"name": "myname2",
}

GET /book/<fullname_or_name>/names/<name>/range
��:
{
"address": "$A$1",
"color": null,
"column": 1,
"count": 1,
"formula": "=1+1.1",
"formula_array": "=1+1,1",
"height": 14.25,
"last_cell": "$A$1",
"left": 0.0,
"name": "myname2",
"row": 1,
"row_height": 14.3,
"shape": [
1,
1
],
"size": 1,
"top": 0.0,
"value": 2.1,
"width": 51.0
}
GET /book/<fullname_or_name>/sheets
��:
{
"sheets": [
{
"charts": [
"Chart 1"
],
"name": "Sheet1",
"names": [
"Sheet1!myname1"
],
"pictures": [
"Picture 3"
],
"shapes": [
(��)
28.6. Endpoint�� 175

(��)
"Chart 1",
"Picture 3"
],
"used_range": "$A$1:$B$2"
},
{
"charts": [],
"name": "Sheet2",
"names": [],
"pictures": [],
"shapes": [],
"used_range": "$A$1"
}
]
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>
��:
{
"charts": [
"Chart 1"
],
"name": "Sheet1",
"names": [
"Sheet1!myname1"
],
"pictures": [
"Picture 3"
],
"shapes": [
"Chart 1",
"Picture 3"
],
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/charts
��:
{
"charts": [
{
"chart_type": "line",
"height": 211.0,
(��)

(��)
"left": 0.0,
"name": "Chart 1",
"top": 0.0,
"width": 355.0
}
]
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/charts/<chart_name_or_ix>
��:
{
"height": 211.0,
"left": 0.0,
"name": "Chart 1",
"top": 0.0,
"width": 355.0
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/names
��:
{
"names": [
{
}
]
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/names/<sheet_scope_name>
��:
{
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/names/<sheet_scope_name>/range
��:

{
"address": "$B$2:$C$3",
"color": null,
"column": 2,
"count": 4,
"formula": [
[
"",
""
],
[
"",
""
]
],
"formula_array": "",
"height": 28.5,
"last_cell": "$C$3",
"left": 51.0,
"row": 2,
"row_height": 14.3,
"shape": [
2,
2
],
"size": 4,
"top": 14.25,
"value": [
[
null,
null
],
[
null,
null
]
],
"width": 102.0
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/pictures
��:

{
"pictures": [
{
"height": 100.0,
"left": 0.0,
"name": "Picture 3",
"top": 0.0,
"width": 100.0
}
]
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/pictures/<picture_name_or_ix>
��:
{
"height": 100.0,
"left": 0.0,
"top": 0.0,
"width": 100.0
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/range
��:
{
"address": "$A$1:$B$2",
"color": null,
"column": 1,
"count": 4,
"formula": [
[
"=1+1.1",
"a string"
],
[
"43395.0064583333",
""
]
],
"height": 28.5,
(��)

(��)
"left": 0.0,
"name": null,
"number_format": null,
"row": 1,
"row_height": 14.3,
"shape": [
2,
2
],
"size": 4,
"top": 0.0,
"value": [
[
2.1,
"a string"
],
[
"Mon, 22 Oct 2018 00:09:18 GMT",
null
]
],
"width": 102.0
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/range/<address>
��:
{
"address": "$A$1:$B$2",
"color": null,
"column": 1,
"count": 4,
"formula": [
[
"=1+1.1",
"a string"
],
[
"43395.0064583333",
""
]
],
(��)

(��)
"height": 28.5,
"left": 0.0,
"name": null,
"row": 1,
"row_height": 14.3,
"shape": [
2,
2
],
"size": 4,
"top": 0.0,
"value": [
[
2.1,
"a string"
],
[
"Mon, 22 Oct 2018 00:09:18 GMT",
null
]
],
"width": 102.0
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/shapes
��:
{
"shapes": [
{
"height": 211.0,
"left": 0.0,
"name": "Chart 1",
"top": 0.0,
"type": "chart",
"width": 355.0
},
{
"height": 100.0,
"left": 0.0,
"top": 0.0,
"type": "picture",
(��)

(��)
"width": 100.0
}
]
}
GET /book/<fullname_or_name>/sheets/<sheet_name_or_ix>/shapes/<shape_name_or_ix>
��:
{
"height": 211.0,
"left": 0.0,
"name": "Chart 1",
"top": 0.0,
"type": "chart",
"width": 355.0
}
28.6.2 /books
GET /books
��:
{
"books": [
{
"app": 1104,
"fullname": "Book1",
"name": "Book1",
"names": [],
"sheets": [
"Sheet1"
]
},
{
"app": 1104,
"names": [
"Sheet1!myname1",
"myname2"
],
"sheets": [
(��)

(��)
"Sheet1",
"Sheet2"
]
},
{
"app": 1104,
"name": "Book4",
"names": [],
"sheets": [
"Sheet1"
]
}
]
}
GET /books/<book_name_or_ix>
��:
{
"app": 1104,
"names": [
"Sheet1!myname1",
"myname2"
],
"sheets": [
"Sheet1",
"Sheet2"
]
}
GET /books/<book_name_or_ix>/names
��:
{
"names": [
{
},
{
(��)

(��)
"name": "myname2",
}
]
}
GET /books/<book_name_or_ix>/names/<name>
��:
{
"name": "myname2",
}
GET /books/<book_name_or_ix>/names/<name>/range
��:
{
"address": "$A$1",
"color": null,
"column": 1,
"count": 1,
"formula": "=1+1.1",
"height": 14.25,
"left": 0.0,
"name": "myname2",
"row": 1,
"row_height": 14.3,
"shape": [
1,
1
],
"size": 1,
"top": 0.0,
"value": 2.1,
"width": 51.0
}
GET /books/<book_name_or_ix>/sheets
��:

{
"sheets": [
{
"charts": [
"Chart 1"
],
"name": "Sheet1",
"names": [
"Sheet1!myname1"
],
"pictures": [
"Picture 3"
],
"shapes": [
"Chart 1",
"Picture 3"
],
},
{
"charts": [],
"name": "Sheet2",
"names": [],
"pictures": [],
"shapes": [],
}
]
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>
��:
{
"charts": [
"Chart 1"
],
"name": "Sheet1",
"names": [
"Sheet1!myname1"
],
"pictures": [
"Picture 3"
],
"shapes": [
"Chart 1",
(��)

(��)
"Picture 3"
],
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/charts
��:
{
"charts": [
{
"height": 211.0,
"left": 0.0,
"name": "Chart 1",
"top": 0.0,
"width": 355.0
}
]
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/charts/<chart_name_or_ix>
��:
{
"height": 211.0,
"left": 0.0,
"name": "Chart 1",
"top": 0.0,
"width": 355.0
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/names
��:
{
"names": [
{
}
]
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/names/<sheet_scope_name>

��:
{
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/names/<sheet_scope_name>/range
��:
{
"address": "$B$2:$C$3",
"color": null,
"column": 2,
"count": 4,
"formula": [
[
"",
""
],
[
"",
""
]
],
"height": 28.5,
"left": 51.0,
"row": 2,
"row_height": 14.3,
"shape": [
2,
2
],
"size": 4,
"top": 14.25,
"value": [
[
null,
null
],
[
(��)

(��)
null,
null
]
],
"width": 102.0
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/pictures
��:
{
"pictures": [
{
"height": 100.0,
"left": 0.0,
"top": 0.0,
"width": 100.0
}
]
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/pictures/<picture_name_or_ix>
��:
{
"height": 100.0,
"left": 0.0,
"top": 0.0,
"width": 100.0
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/range
��:
{
"address": "$A$1:$B$2",
"color": null,
"column": 1,
"count": 4,
"formula": [
[
(��)

(��)
"=1+1.1",
"a string"
],
[
"43395.0064583333",
""
]
],
"height": 28.5,
"left": 0.0,
"name": null,
"row": 1,
"row_height": 14.3,
"shape": [
2,
2
],
"size": 4,
"top": 0.0,
"value": [
[
2.1,
"a string"
],
[
"Mon, 22 Oct 2018 00:09:18 GMT",
null
]
],
"width": 102.0
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/range/<address>
��:
{
"address": "$A$1:$B$2",
"color": null,
"column": 1,
"count": 4,
(��)

(��)
"formula": [
[
"=1+1.1",
"a string"
],
[
"43395.0064583333",
""
]
],
"height": 28.5,
"left": 0.0,
"name": null,
"row": 1,
"row_height": 14.3,
"shape": [
2,
2
],
"size": 4,
"top": 0.0,
"value": [
[
2.1,
"a string"
],
[
"Mon, 22 Oct 2018 00:09:18 GMT",
null
]
],
"width": 102.0
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/shapes
��:
{
"shapes": [
{
"height": 211.0,
"left": 0.0,
(��)

(��)
"name": "Chart 1",
"top": 0.0,
"type": "chart",
"width": 355.0
},
{
"height": 100.0,
"left": 0.0,
"top": 0.0,
"type": "picture",
"width": 100.0
}
]
}
GET /books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/shapes/<shape_name_or_ix>
��:
{
"height": 211.0,
"left": 0.0,
"name": "Chart 1",
"top": 0.0,
"type": "chart",
"width": 355.0
}
28.6.3 /apps
GET /apps
��:
{
"apps": [
{
"books": [
"Book1",
"C:\\Users\\felix\\DEV\\xlwings\\scripts\\Book1.xlsx",
"Book4"
],
"calculation": "automatic",
"display_alerts": true,
"pid": 1104,
(��)

(��)
"screen_updating": true,
"selection": "[Book1.xlsx]Sheet2!$A$1",
"version": "16.0",
"visible": true
},
{
"books": [
"Book2",
"Book5"
],
"pid": 7920,
"selection": "[Book5]Sheet2!$A$1",
"version": "16.0",
"visible": true
}
]
}
GET /apps/<pid>
��:
{
"books": [
"Book1",
"C:\\Users\\felix\\DEV\\xlwings\\scripts\\Book1.xlsx",
"Book4"
],
"pid": 1104,
"selection": "[Book1.xlsx]Sheet2!$A$1",
"version": "16.0",
"visible": true
}
GET /apps/<pid>/books
��:
{
"books": [
{
(��)

(��)
"app": 1104,
"name": "Book1",
"names": [],
"sheets": [
"Sheet1"
]
},
{
"app": 1104,
"names": [
"Sheet1!myname1",
"myname2"
],
"sheets": [
"Sheet1",
"Sheet2"
]
},
{
"app": 1104,
"name": "Book4",
"names": [],
"sheets": [
"Sheet1"
]
}
]
}
GET /apps/<pid>/books/<book_name_or_ix>
��:
{
"app": 1104,
"names": [
"Sheet1!myname1",
(��)

(��)
"myname2"
],
"sheets": [
"Sheet1",
"Sheet2"
]
}
GET /apps/<pid>/books/<book_name_or_ix>/names
��:
{
"names": [
{
},
{
"name": "myname2",
}
]
}
GET /apps/<pid>/books/<book_name_or_ix>/names/<name>
��:
{
"name": "myname2",
}
GET /apps/<pid>/books/<book_name_or_ix>/names/<name>/range
��:
{
"address": "$A$1",
"color": null,
"column": 1,
"count": 1,
"formula": "=1+1.1",
(��)

(��)
"height": 14.25,
"left": 0.0,
"name": "myname2",
"row": 1,
"row_height": 14.3,
"shape": [
1,
1
],
"size": 1,
"top": 0.0,
"value": 2.1,
"width": 51.0
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets
��:
{
"sheets": [
{
"charts": [
"Chart 1"
],
"name": "Sheet1",
"names": [
"Sheet1!myname1"
],
"pictures": [
"Picture 3"
],
"shapes": [
"Chart 1",
"Picture 3"
],
},
{
"charts": [],
"name": "Sheet2",
"names": [],
"pictures": [],
"shapes": [],
(��)

(��)
}
]
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>
��:
{
"charts": [
"Chart 1"
],
"name": "Sheet1",
"names": [
"Sheet1!myname1"
],
"pictures": [
"Picture 3"
],
"shapes": [
"Chart 1",
"Picture 3"
],
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/charts
��:
{
"charts": [
{
"height": 211.0,
"left": 0.0,
"name": "Chart 1",
"top": 0.0,
"width": 355.0
}
]
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/charts/<chart_name_or_ix>
��:

{
"height": 211.0,
"left": 0.0,
"name": "Chart 1",
"top": 0.0,
"width": 355.0
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/names
��:
{
"names": [
{
}
]
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/names/<sheet_scope_name>
��:
{
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/names/<sheet_scope_name>/rang
��:
{
"address": "$B$2:$C$3",
"color": null,
"column": 2,
"count": 4,
"formula": [
[
"",
""
],
[
(��)

(��)
"",
""
]
],
"height": 28.5,
"left": 51.0,
"row": 2,
"row_height": 14.3,
"shape": [
2,
2
],
"size": 4,
"top": 14.25,
"value": [
[
null,
null
],
[
null,
null
]
],
"width": 102.0
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/pictures
��:
{
"pictures": [
{
"height": 100.0,
"left": 0.0,
"top": 0.0,
"width": 100.0
}
]
}

GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/pictures/<picture_name_or_ix>
��:
{
"height": 100.0,
"left": 0.0,
"top": 0.0,
"width": 100.0
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/range
��:
{
"address": "$A$1:$B$2",
"color": null,
"column": 1,
"count": 4,
"formula": [
[
"=1+1.1",
"a string"
],
[
"43395.0064583333",
""
]
],
"height": 28.5,
"left": 0.0,
"name": null,
"row": 1,
"row_height": 14.3,
"shape": [
2,
2
],
"size": 4,
"top": 0.0,
"value": [
(��)

(��)
[
2.1,
"a string"
],
[
"Mon, 22 Oct 2018 00:09:18 GMT",
null
]
],
"width": 102.0
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/range/<address>
��:
{
"address": "$A$1:$B$2",
"color": null,
"column": 1,
"count": 4,
"formula": [
[
"=1+1.1",
"a string"
],
[
"43395.0064583333",
""
]
],
"height": 28.5,
"left": 0.0,
"name": null,
"row": 1,
"row_height": 14.3,
"shape": [
2,
2
],
"size": 4,
(��)

(��)
"top": 0.0,
"value": [
[
2.1,
"a string"
],
[
"Mon, 22 Oct 2018 00:09:18 GMT",
null
]
],
"width": 102.0
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/shapes
��:
{
"shapes": [
{
"height": 211.0,
"left": 0.0,
"name": "Chart 1",
"top": 0.0,
"type": "chart",
"width": 355.0
},
{
"height": 100.0,
"left": 0.0,
"top": 0.0,
"type": "picture",
"width": 100.0
}
]
}
GET /apps/<pid>/books/<book_name_or_ix>/sheets/<sheet_name_or_ix>/shapes/<shape_name_or_ix>
��:
{
"height": 211.0,
"left": 0.0,
"name": "Chart 1",
(��)

(��)
"top": 0.0,
"type": "chart",
"width": 355.0
}

��
A app (xlwings.Book ��), 135

activate() (xlwings.App ��), 129 Apps (xlwings.main ��), 128
activate() (xlwings.Book ��), 135 autofit() (xlwings.Range ��), 145
activate() (xlwings.Shape ��), 155 autofit() (xlwings.RangeColumns ��), 154
activate() (xlwings.Sheet ��), 140 autofit() (xlwings.RangeRows ��), 153
active (xlwings.main.Apps ��), 128 autofit() (xlwings.Sheet ��), 140
active (xlwings.main.Books ��), 134
active (xlwings.main.Sheets ��), 139
B
add() (xlwings.main.Apps ��), 129 bold (xlwings.main.Font ��), 166
add() (xlwings.main.Books ��), 134 Book (xlwings ��), 134
add() (xlwings.main.Charts ��), 156 book (xlwings.Sheet ��), 140
add() (xlwings.main.Names ��), 161 books (xlwings.App ��), 130
add() (xlwings.main.Pictures ��), 158 Books (xlwings.main ��), 133
add() (xlwings.main.Sheets ��), 139
C
add() (xlwings.main.Tables ��), 163
add_hyperlink() (xlwings.Range ��), 144 calculate() (xlwings.App ��), 130
address (xlwings.Range ��), 144 calculation (xlwings.App ��), 130
api (xlwings.App ��), 130 caller() (xlwings.Book ��), 135
api (xlwings.Book ��), 135 cells (xlwings.Sheet ��), 140
api (xlwings.Chart ��), 157 Characters (xlwings.main ��), 167
api (xlwings.main.Characters ��), 167 Chart (xlwings ��), 157
api (xlwings.main.Charts ��), 157 chart_type (xlwings.Chart ��), 157
api (xlwings.main.Font ��), 166 Charts (xlwings.main ��), 156
api (xlwings.main.Names ��), 161 charts (xlwings.Sheet ��), 140
api (xlwings.main.Note ��), 162 clear() (xlwings.Range ��), 145
api (xlwings.main.PageSetup ��), 139 clear() (xlwings.Sheet ��), 141
api (xlwings.main.Pictures ��), 160 clear_contents() (xlwings.Range ��), 145
api (xlwings.main.Shapes ��), 154 clear_contents() (xlwings.Sheet ��), 141
api (xlwings.main.Table ��), 164 close() (xlwings.Book ��), 136
api (xlwings.Name ��), 162 color (xlwings.main.Font ��), 166
api (xlwings.Picture ��), 160 color (xlwings.Range ��), 145
api (xlwings.Range ��), 144 column (xlwings.Range ��), 145
api (xlwings.Shape ��), 155 column_width (xlwings.Range ��), 145
api (xlwings.Sheet ��), 140 columns (xlwings.Range ��), 146
App (xlwings ��), 129 copy() (xlwings.Range ��), 146
203
copy() (xlwings.Sheet ��), 141 hwnd (xlwings.App ��), 131

count (xlwings.main.Apps ��), 129 hyperlink (xlwings.Range ��), 148
count (xlwings.main.Charts ��), 157
count (xlwings.main.Names ��), 161 I
count (xlwings.main.Pictures ��), 160 index (xlwings.Sheet ��), 141
count (xlwings.main.Shapes ��), 154 insert() (xlwings.Range ��), 148
count (xlwings.Range ��), 146 insert_row_range (xlwings.main.Table ��), 164
count (xlwings.RangeColumns ��), 154 interactive (xlwings.App ��), 131
count (xlwings.RangeRows ��), 153 italic (xlwings.main.Font ��), 167
create_report() (xlwings.App ��), 130
current_region (xlwings.Range ��), 146 K
cut_copy_mode (xlwings.App ��), 131 keys() (xlwings.main.Apps ��), 129
kill() (xlwings.App ��), 131
D
data_body_range (xlwings.main.Table ��), 164 L
delete() (xlwings.Chart ��), 158 last_cell (xlwings.Range ��), 149
delete() (xlwings.main.Note ��), 162 left (xlwings.Chart ��), 158
delete() (xlwings.Name ��), 162 left (xlwings.Picture ��), 160
delete() (xlwings.Picture ��), 160 left (xlwings.Range ��), 149
delete() (xlwings.Range ��), 146 left (xlwings.Shape ��), 155
delete() (xlwings.Shape ��), 155 load() (� xlwings ��), 128
delete() (xlwings.Sheet ��), 141 lock_aspect_ratio (xlwings.Picture ��), 160
display_alerts (xlwings.App ��), 131
display_name (xlwings.main.Table ��), 164 M
macro() (xlwings.App ��), 131
E macro() (xlwings.Book ��), 136
enable_events (xlwings.App ��), 131 merge() (xlwings.Range ��), 149
end() (xlwings.Range ��), 146 merge_area (xlwings.Range ��), 149
expand() (xlwings.Range ��), 147 merge_cells (xlwings.Range ��), 149
F N
Font (xlwings.main ��), 166 Name (xlwings ��), 162
font (xlwings.main.Characters ��), 168 name (xlwings.Book ��), 136
formula (xlwings.Range ��), 147 name (xlwings.Chart ��), 158
formula2 (xlwings.Range ��), 147 name (xlwings.main.Font ��), 167
formula_array (xlwings.Range ��), 147 name (xlwings.main.Table ��), 164
fullname (xlwings.Book ��), 136 name (xlwings.Name ��), 162
name (xlwings.Picture ��), 160
G name (xlwings.Range ��), 149
get_address() (xlwings.Range ��), 147 name (xlwings.Shape ��), 155
name (xlwings.Sheet ��), 142
H names (xlwings.Book ��), 136
has_array (xlwings.Range ��), 148 Names (xlwings.main ��), 161
header_row_range (xlwings.main.Table ��), 164 names (xlwings.Sheet ��), 142
height (xlwings.Chart ��), 158 Note (xlwings.main ��), 162
height (xlwings.Picture ��), 160 note (xlwings.Range ��), 149
height (xlwings.Range ��), 148 number_format (xlwings.Range ��), 150
height (xlwings.Shape ��), 155
204 ��
O selection (xlwings.Book ��), 137

offset() (xlwings.Range ��), 150 set_mock_caller() (xlwings.Book ��), 137
open() (xlwings.main.Books ��), 134 set_source_data() (xlwings.Chart ��), 158
options() (xlwings.Range ��), 150 Shape (xlwings ��), 154
shape (xlwings.Range ��), 152
P Shapes (xlwings.main ��), 154
page_setup (xlwings.Sheet ��), 142 shapes (xlwings.Sheet ��), 142
PageSetup (xlwings.main ��), 139 Sheet (xlwings ��), 140
parent (xlwings.Chart ��), 158 sheet (xlwings.Range ��), 152
parent (xlwings.main.Table ��), 164 sheets (xlwings.Book ��), 137
parent (xlwings.Picture ��), 161 Sheets (xlwings.main ��), 139
parent (xlwings.Shape ��), 155 show_autofilter (xlwings.main.Table ��), 165
paste() (xlwings.Range ��), 151 show_headers (xlwings.main.Table ��), 165
Picture (xlwings ��), 160 show_table_style_column_stripes (xl-
Pictures (xlwings.main ��), 158 wings.main.Table ��), 165
pictures (xlwings.Sheet ��), 142 show_table_style_first_column (xl-
pid (xlwings.App ��), 132 wings.main.Table ��), 165
print_area (xlwings.main.PageSetup ��), 139 show_table_style_last_column (xl-
properties() (xlwings.App ��), 132 wings.main.Table ��), 165
show_table_style_row_stripes (xl-
Q wings.main.Table ��), 165
quit() (xlwings.App ��), 132 show_totals (xlwings.main.Table ��), 165
size (xlwings.main.Font ��), 167
R size (xlwings.Range ��), 152
Range (xlwings ��), 144 startup_path (xlwings.App ��), 133
range (xlwings.main.Table ��), 165 status_bar (xlwings.App ��), 133
range() (xlwings.App ��), 133
range() (xlwings.Sheet ��), 142 T
RangeColumns (xlwings ��), 153 Table (xlwings.main ��), 164
RangeRows (xlwings ��), 153 table (xlwings.Range ��), 152
raw_value (xlwings.Range ��), 151 table_style (xlwings.main.Table ��), 165
refers_to (xlwings.Name ��), 162 Tables (xlwings.main ��), 163
refers_to_range (xlwings.Name ��), 162 tables (xlwings.Sheet ��), 143
render_template() (xlwings.Sheet ��), 142 text (xlwings.main.Characters ��), 168
resize() (xlwings.main.Table ��), 165 text (xlwings.main.Note ��), 163
resize() (xlwings.Range ��), 151 text (xlwings.Shape ��), 156
row (xlwings.Range ��), 151 to_pdf() (xlwings.Book ��), 137
row_height (xlwings.Range ��), 151 to_pdf() (xlwings.Sheet ��), 143
rows (xlwings.Range ��), 152 top (xlwings.Chart ��), 158
top (xlwings.Picture ��), 161
S top (xlwings.Range ��), 152
save() (xlwings.Book ��), 137 top (xlwings.Shape ��), 156
scale_height() (xlwings.Shape ��), 155 totals_row_range (xlwings.main.Table ��), 165
scale_width() (xlwings.Shape ��), 155 type (xlwings.Shape ��), 156
screen_updating (xlwings.App ��), 133
select() (xlwings.Range ��), 152 U
select() (xlwings.Sheet ��), 142 unmerge() (xlwings.Range ��), 152
selection (xlwings.App ��), 133 update() (xlwings.main.Table ��), 165
�� 205
update() (xlwings.Picture ��), 161

used_range (xlwings.Sheet ��), 143
V
value (xlwings.Range ��), 152
version (xlwings.App ��), 133
view() (� xlwings ��), 127
visible (xlwings.App ��), 133
visible (xlwings.Sheet ��), 143
W
width (xlwings.Chart ��), 158
width (xlwings.Picture ��), 161
width (xlwings.Range ��), 152
width (xlwings.Shape ��), 156
wrap_text (xlwings.Range ��), 153
X
xlwings (��), 127
xlwings.arg() (� xlwings ��), 168
xlwings.func() (� xlwings ��), 168
xlwings.ret() (� xlwings ��), 169
xlwings.sub() (� xlwings ��), 168
206 ��

Xlwings Guide

Uploaded by

Copyright:

Available Formats

Xlwings Guide

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Xlwings Guide

Uploaded by

Copyright:

Available Formats

What are the different ways to interact with Excel from Python?

What are the different ways to interact with Excel from Python?

What are some of the main objects and methods available in xlwings?

What are some of the main objects and methods available in xlwings?

xlwings - Make Excel Fly!

Zoomer Analytics LLC

7 Add-in & Settings 23

9 User Defined Functions (UDFs) 31

10 Matplotlib & Plotly Charts 39

11 Jupyter Notebooks: Interact with Excel 45

12 Command Line Client (CLI) 47

22 xlwings PRO Overview 85

24 Markdown Formatting 111

25 Releasing xlwings Tools 115

26 Permissioning of Code Execution 123

27 Python API 127

28 REST API 171

xlwings comes pre-installed with

pip install xlwings

conda install xlwings

conda install -c conda-forge xlwings

To install the add-in, run the following command:

xlwings addin install

2.5 How to activate xlwings PRO

To activate the license key, run the following command:

xlwings license update -k LICENSE_KEY

pip install "xlwings[all]"

pip install --upgrade xlwings

conda update -c conda-forge xlwings

xlwings addin install

xlwings addin remove

pip uninstall xlwings

conda remove xlwings

3.1 1. Interacting with Excel from a Jupyter notebook

3.2 2. Scripting: Automate/interact with Excel from Python

>>> import xlwings as xw

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

�����Excel���������Excel���������app����������������� xw.apps.keys() ���app����(PID)�

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

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

���� ����� ��������

������ �������������������Numpy arrays�Pandas DataFrames�

>>> import matplotlib.pyplot as plt

3.3 3. Macros: Call Python from Excel

���������� Run ��(v0.16�����)���Python�������VBA��� RunPython ���

Refer to the calling Excel book by using xw.Book.caller():

3.4 4. UDFs: User Defined Functions (Windows only)

3.4. 4. UDFs: User Defined Functions (Windows only) 9

>>> import xlwings as xw

xw.Book ����������������� �����app�����������������������app����������������� ����app�������� xw.books �����app���������

with xw.App() as app:

�VBA�� RunPython ����������� xw.Book.caller() ��� �”RunPython”��Python ��� �� �����Pyhton�Ex-

>>> import xlwings as xw

# Active app (i.e. Excel instance)

# Range on active sheet

��app��������ID�PID���������������� xw.apps.keys() ��PID���

5.3 App context manager

with xw.App() as app:

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

������������������������������������ �������������/�������� sheet.range � sheet.cells ���

��Excel��Excel��app�� xw.apps.keys() ��app��(PID)�

��

�� Numpy arrays�Pandas DataFrames�

�� Run ��(v0.16��)��Python��VBA�� RunPython ��

xw.Book �� app��app�� app�� xw.books ��app��

�VBA�� RunPython �� xw.Book.caller() �� ”RunPython”��Python �� Pyhton�Ex-

��app��ID�PID�� xw.apps.keys() ��PID��

�� /�� sheet.range � sheet.cells ��

��xlwings�� options ��

��python�� float, unicode, None � datetime :

• ��Excel��Python�� Python��

�� expand �� options �� expand �� expand ��,� options ��

�� 'table' �� 'down' (��)� 'right' (��)��

NumPy�� nan �� None �� options �� convert=np.array �

�� xlwings.conf ��

�� xlwings.conf �� xlwings quickstart ��

�� Run main ��(v0.16��)�� RunPython VBA��xlwings��(�VBA��)�� Add-in & Settings.

�� hello.py ��

�� HelloWorld ��VBA�� F5 ��

�� xlwings quickstart myproject ��(�� Command Line Client

• �xlwings�� Import Python UDFs �� myproject.py ��

9.4.1 ��: ndim

�� [[1, 2], [3, 4]] �“2�”�� : 'float' ��

��UDF��NumPy array��Pandas DataFrame��Python��

��: ��Python >= 3.5 �� NumPy >= 1.10�� x.dot(y) �� x @ y �

�Pandas�� CORREL ��Excel�� CORREL ��2��Pandas�� CORREL2 ��:

��UDF�� options �� Range ��( @xw.arg ) ��(@xw.ret )��x��pandas

��: ��Excel�� expand �� =UNIQUE() ��2018�9��Office 365 In-

xlwings��Excel�� #N/A waiting... ��Excel��

�� pictures.add() ��Matplotlib��Excel��

��: �� update=True,��Excel�� pictures.add() ��('MyPlot')��

�� pictures.add() �� xlwings.Picture().

��matplotlib figure ��