PyXLL User Guide
Release 5.3.0
PyXLL Ltd.
Oct 07, 2021
CONTENTS
1 Introduction to PyXLL 1
1.1 What is PyXLL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.2 How does it work? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.3 Before You Start . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.4 Next Steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
2 What’s new in PyXLL 5 5
2.1 New Features and Improvements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
2.2 Important notes for upgrading from previous versions . . . . . . . . . . . . . . . . . . . . . . . 7
3 User Guide 9
3.1 Installing PyXLL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
3.2 Configuring PyXLL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
3.3 Worksheet Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
3.4 Real Time Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
3.5 Cell Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
3.6 Charts and Plotting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
3.7 Custom User Interfaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
3.8 Customizing the Ribbon . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
3.9 Context Menu Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
3.10 Macro Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
3.11 Python as a VBA Replacement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
3.12 Using Pandas in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
3.13 Menu Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
3.14 Reloading and Rebinding . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
3.15 Error Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
3.16 Distributing Python Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
3.17 Workbook Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
4 API Reference 135
4.1 Function Decorators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
4.2 Plotting Functions and Classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
4.3 Custom Task Panes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
4.4 Utility Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
4.5 Ribbon Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
4.6 Cell Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152
4.7 Event Handlers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
4.8 Excel C API Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
4.9 Other Classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
5 Examples 167
5.1 UDF Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
5.2 Pandas Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
5.3 Cached Objects Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
5.4 Custom Type Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
i
5.5 Menu Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
5.6 Macros and Excel Scripting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
5.7 Event Handler Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Index 184
ii
CHAPTER
ONE
INTRODUCTION TO PYXLL
• What is PyXLL?
• How does it work?
• Before You Start
• Next Steps
– Install PyXLL
– Calling a Python Function in Excel
– Additional Resources
1.1 What is PyXLL?
PyXLL is an Excel Add-In that enables developers to extend Excel’s capabilities with Python code.
PyXLL makes Python a productive, flexible back-end for Excel worksheets, and lets you use the familiar Excel
user interface to interact with other parts of your information infrastructure.
With PyXLL, your Python code runs in Excel using any common Python distribution(e.g. Anaconda, Enthought’s
Canopy or any other CPython distribution from 2.3 to 3.10).
Because PyXLL runs your own full Python distribution you have access to all third party Python packages such
as NumPy, Pandas and SciPy and can call them from Excel.
Example use cases include:
• Calling existing Python code to perform calculations in Excel
• Data processing and analysis that’s too slow or cumbersome to do in VBA
• Pulling in data from external systems such as databases
• Querying large datasets to present summary level data in Excel
• Exposing internal or third party libraries to Excel users
1
PyXLL User Guide, Release 5.3.0
1.2 How does it work?
PyXLL runs Python code in Excel according to the specifications in its config file, in which you configure how
Python is run and which modules PyXLL should load. When PyXLL starts up it loads those modules and exposes
certain functions that have been tagged with PyXLL decorators.
For example, an Excel user defined function (UDF) to compute the n
th
Fibonacci number can be written in Python
as follows:
from pyxll import xl_func
@xl_func
def fib(n):
"Naiive Fibonacci implementation."
if n == 0:
return 0
elif n == 1:
return 1
return fib(n-1) + fib(n-2)
The xl_func-decorated function fib is detected by PyXLL and exposed to Excel as a user-defined function.
Excel types are automatically converted to Python types based on an optional function signature. Where there is
no simple conversion (e.g. when returning an arbitrary class instance from a method) PyXLL stores the Python
object reference as a cell value in Excel. When another function is called with a reference to that cell PyXLL
retrieves the object and passes it to the method. PyXLL keeps track of cells referencing objects so that once an
object is no longer referenced by Excel it can be dereferenced in Python.
1.3 Before You Start
Existing users might want to study What’s new in PyXLL 5. Those upgrading from earlier versions will should
read “Important notes for upgrading from previous versions”. If you prefer to learn by watching, perhaps you
would prefer our video guides and tutorials.
Note that you cannot mix 32-bit and 64-bit versions of Excel, Python and PyXLL – they all must be the same.
Install the add-in according to the installation instructions, making sure to update the configuration file if neces-
sary. For specific instructions about installing with Anaconda or Miniconda see Using PyXLL with Anaconda.
Once PyXLL is installed you will be able to try out the examples workbook that is included in the download. All
the code used in the examples workbook is also included in the download.
Note that any errors will be written to the log file, so if you are having difficulties always look in the log file to see
what’s going wrong, and if in doubt please contact us.
1.4 Next Steps
After you’ve installed PyXLL below is an exercise to show you how to write your first Python user-defined func-
tion.
1.2. How does it work? 2
PyXLL User Guide, Release 5.3.0
1.4.1 Install PyXLL
To begin with follow the instructions for first time users to install PyXLL.
You can use PyXLL’s command line tool to install the PyXLL add-in into Excel:
>> pip install pyxll
>> pyxll install
1.4.2 Calling a Python Function in Excel
One of the main features of PyXLL is being able to call a Python function from a formula in an Excel workbook.
First start by creating a new Python module and writing a simple Python function. To expose that function to
Excel all you have to do is to apply the xl_func decorator to it.:
from pyxll import xl_func
@xl_func
def hello(name):
return "Hello, %s" % name
Save your module and edit the pyxll.cfg file again to add your new module to the list of modules to load and add
the directory containing your module to the pythonpath.
[PYXLL]
modules = <add the name of your new module here>
[PYTHON]
pythonpath = <add the folder containing your Python module here>
Go to the Addins menu in Excel and select PyXLL -> Reload. This causes PyXLL to reload the config and Python
modules, allowing new and updated modules to be discovered.
Now in a worksheet you will find you can type a formula using your new Python function.:
=hello("me")
Using PyCharm, Eclipse or Visual Studio?
You can interactively debug Python code running in PyXLL with Eclipse, PyCharm, Visual Studio and other
IDEs by attaching them as a debugger to a running PyXLL. See our blog post Debugging Your Python Excel
Add-In for details.
If you make any mistakes in your code or your function returns an error you can check the log file to find out what
the error was, make the necessary changes to your code and reload PyXLL again.
1.4. Next Steps 3
PyXLL User Guide, Release 5.3.0
1.4.3 Additional Resources
The documentation explains how to use all the features of PyXLL, and contains a complete API reference.
PyXLL’s features are also well demonstrated in the examples included in download. These are a good place
to start to learn more about what PyXLL can do.
More example code can be found on PyXLL’s GitHub page.
If there is anything specifically you’re trying to achieve and can’t find an example or help in the documentation
please contact us and we will do our best to help.
1.4. Next Steps 4
CHAPTER
TWO
WHAT’S NEW IN PYXLL 5
Looking for an earlier version?
See 4.x/whatsnew for a detailed overview of the features added in PyXLL 4.
• New Features and Improvements
– Easier Installation
– Custom Task Panes
– Plotting Integrations
– Serialized Cached Objects
– Entry Points
– Composite Ribbon Toolbars
– Auto-Rebinding
– Improved Cell Formatting
– Log Rolling
• Important notes for upgrading from previous versions
– Updated Software License Agreement
– Deep reloading is now enabled by default
– RTD functions no longer recalculate on open by default
– async_func has been replaced with schedule_call
2.1 New Features and Improvements
2.1.1 Easier Installation
The PyXLL Excel add-in can now be installed and uninstalled via a new command line tool.
To install the PyXLL Excel add-in first use pip install to install the PyXLL wheel, eg
> pip install "pyxll >= 5.0.0"
Once the PyXLL wheel is installed the new pyxll command line tool can be used to install, configure and uninstall
the PyXLL Excel add-in, eg
5
PyXLL User Guide, Release 5.3.0
> pyxll install
See PyXLL Command Line Tool.
2.1.2 Custom Task Panes
Task Panes are Excel windows that can be floating or docked as part of the Excel user interface.
PyXLL 5 adds the capability to write custom task panes in Python using any of the following Python UI toolkits:
• PySide2 and PySide6
• PyQt5 and PyQt5
• wxWindows
• tkinter
See Custom User Interfaces.
2.1.3 Plotting Integrations
PyXLL 5 adds integration with the following Python plotting and charting packages:
• matplotlib
• plotly
• bokeh
• altair
See Charts and Plotting.
2.1.4 Serialized Cached Objects
Cached objects can be serialized and saved as part of the Excel workbook. When a workbook containing saved
objects is opened they are deserialized and loaded into PyXLL’s object cache.
To specify that an object should be saved use the save parameter to the object return type.
See Saving Objects in the Workbook.
2.1.5 Entry Points
Python packages can now be loaded by PyXLL via setuptools’ entry-points.
This allows package developers to distribute functionality to other PyXLL users more easily as no additional
PyXLL configuration is required when installing a package with PyXLL entry points.
See Setuptools Entry Points.
2.1. New Features and Improvements 6
PyXLL User Guide, Release 5.3.0
2.1.6 Composite Ribbon Toolbars
The ribbon toolbar can now be composed of multiple xml files instead of a single file.
The ribbon setting can now be a list of files, which PyXLL will merge into a single ribbon.
This can be used by package authors who want to add a ribbon to their package via an entry point without needing
changes to be made to the main PyXLL configuration or ribbon xml file.
Images specified in the ribbon xml can now also be package resources as well as files.
2.1.7 Auto-Rebinding
When using the xl_func, xl_macro or xl_menu decorators outside of the usual module imports as PyXLL
is starting, PyXLL will automatically reflect these functions in Excel without needing to call rebind.
This simplifies working with adhoc worksheet functions from an interactive Python prompt in Excel, such as a
Jupyter notebook.
2.1.8 Improved Cell Formatting
• Cell formatting can now be applied to RTD functions as well as standard worksheet functions.
• The DataFrameFormatter can now do conditional formatting based on the values in the returned
DataFrame.
See Conditional Formatting.
2.1.9 Log Rolling
New in PyXLL 5.2
PyXLL can now automatically roll its log file when it reaches a certain size or after a specific interval has elapsed.
This avoids long running Excel processes from generating huge log files. Old log files can be kept for a while and
then later automatically cleaned up to avoid using excessive disk space.
See Logging for more details.
2.2 Important notes for upgrading from previous versions
PyXLL 5.0 contains some changes that may require you to make changes to your code and/or config before
upgrading from previous versions.
2.2.1 Updated Software License Agreement
The PyXLL software license agreement has been updated.
See terms-and-conditions or the software license agreement PDF file included in the PyXLL download for details.
2.2. Important notes for upgrading from previous versions 7
PyXLL User Guide, Release 5.3.0
2.2.2 Deep reloading is now enabled by default
This can be disabled for backwards compatibility
Deep reloading is now enabled by default. See Reloading and Rebinding for details about how PyXLL reloads
modules.
To disable deep reloading set the following in your PyXLL config file.
[PYXLL]
deep_reload = 0
2.2.3 RTD functions no longer recalculate on open by default
This can be disabled for backwards compatibility
In previous versions of PyXLL RTD functions were implicitly marked as needed to be recalulated when opening a
workbook. This was done to be consistent with earlier behaviour where RTD functions were registered as volatile.
As of PyXLL 5 RTD and standard functions behave in the same consistent way. That is, unless the
recalc_on_open=True is passed to xl_func, or defaulted via the config, RTD functions will not recal-
culate and start ticking when a workbook is opened automatically.
To enable recalculation on open as the default for all RTD functions you may set the following in your config file.
[PYXLL]
recalc_rtd_on_open = 1
2.2.4 async_func has been replaced with schedule_call
If your code uses async_call you should replace it with the new schedule_call. The old async_call
is still available but has been deprecated and will log a warning if used.
2.2. Important notes for upgrading from previous versions 8
CHAPTER
THREE
USER GUIDE
3.1 Installing PyXLL
Before you start you will need to have Microsoft Excel for Windows installed, as well as a compatible version of
Python.
PyXLL works with any Python distribution, including Anaconda.
3.1.1 First Time Users
The easiest way to get started with PyXLL is to use the PyXLL Command Line Tool.
1. To install PyXLL from scratch open a Python command line prompt and install the PyXLL package using
pip.
If you are using conda or a virtual environment then activate it before doing this step.
>> pip install pyxll
2. Next you need to install the PyXLL Excel add-in into Excel.
• If you haven’t yet downloaded PyXLL from the download page then you can let the PyXLL command
line tool do that for you. Run pyxll install and follow the prompts to let it download and install
everything for you:
>> pyxll install
• Follow the on screen instructions to complete the installation.
Note: If you have already downloaded PyXLL you can drag and drop the zip file from Windows Explorer
onto the command prompt when asked for the path!
If you have any trouble using the installer please contact us to let us know. You can find additional instructions for
how to use the command line tool here.
It is also possible to install the PyXLL add-in manually as described in the next section.
9
PyXLL User Guide, Release 5.3.0
Next Steps
In the folder you’ve installed PyXLL into you will find an example workbook, examples.xlsx. This contains a
number of examples to demonstrate some of the features of PyXLL. You can find the Python code for these
examples in the examples folder in your PyXLL installation.
Try adding your own modules (.py files) and writing your own functions.
To add your own modules you will need to add them to the modules list that you can find in the pyxll.cfg file. Use
pyxll configure to quickly open the config file.
You can include modules from other folders too, not just the examples folder. Add your own folders to the
pythonpath setting in the pyxll.cfg file.
See Worksheet Functions for details of how you can expose your own Python functions to Excel as worksheet
functions, or browse the User Guide for information about the other features of PyXLL.
3.1.2 PyXLL Command Line Tool
The PyXLL command line tool automates tasks around installing, updating and switching between different ver-
sions of PyXLL.
In order to use the PyXLL command line tool you first need to install it using pip:
>> pip install pyxll
If you are using a conda or virtual env you should activate the environment you want to use first.
To get the latest version of the PyXLL command line too you should update the package using pip:
>> pip install --upgrade pyxll
The PyXLL wheel file is also included in the PyXLL download and may be installed from there.
After installing, the following commands are available:
• pyxll install
• pyxll configure
• pyxll status
• pyxll update
• pyxll activate
• pyxll install-certificate
• pyxll uninstall
pyxll install
The install command installs the PyXLL Excel add-in into Excel. It is necessary to either perform this step or to
install PyXLL manually before the PyXLL Excel add-in can be used.
>> pyxll install [OPTIONS] [PATH]
Options:
--version Version of PyXLL to install.
--debug / -d Output more information when running the command.
3.1. Installing PyXLL 10
PyXLL User Guide, Release 5.3.0
• Can be run with or without PATH.
– If PATH is not specified then either the latest version of PyXLL or the version specified will be down-
loaded. You will be prompted for some details in order to complete the download.
– If PATH is specified it can be a zip file downloaded from the download page, or a folder containing
the extracted downloaded zip file.
• If you already have PyXLL installed you will be warned but may continue.
– Any existing files that will be over-written will be backed up.
– You will be given the choice to change the location of the installation, allowing you to maintain
multiple copies of PyXLL.
– If installing in the same folder as your existing installation, your existing config file will be backed up
and a new one will be created with the default configuration.
• PyXLL will be configured automatically to use the active Python environment.
Further configuration can be performed by editing the pyxll.cfg file included in the installation or by using
the pyxll configure command.
pyxll configure
The configure command opens the pyxll.cfg configuration file for the currently active PyXLL addin.
>> pyxll configure [OPTIONS]
Options:
--debug / -d Output more information when running the command.
• The default editor for the file type .cfg will be used to open the config file.
• PyXLL can have been installed using pyxll install or manually.
pyxll status
The status command checks the status of the active PyXLL installation and reports information about it.
>> pyxll status [OPTIONS]
Options:
--debug / -d Output more information when running the command.
If there are any issues with your current PyXLL installation this command may help identify what the problem is.
pyxll update
The update command updates your active PyXLL installation to the latest version of PyXLL.
>> pyxll update [OPTIONS] [PATH]
Options:
--version Version of PyXLL to update to.
--force For the update, even if the installed version is newer.
--debug / -d Output more information when running the command.
• Your existing pyxll.cfg file will not be modified.
3.1. Installing PyXLL 11
PyXLL User Guide, Release 5.3.0
• The previous pyxll.xll file will be backed up.
• Can be run with or without PATH.
– If PATH is not specified then either the latest version of PyXLL or the version specified will be down-
loaded. You will be prompted for some details in order to complete the download.
– If PATH is specified it can be a zip file downloaded from the download page, or a folder containing
the extracted downloaded zip file.
• If you want to try out a new version of PyXLL before upgrading use the pyxll install command and specify
a different folder to install it to. You can use the pyxll activate command to switch between installs easily.
pyxll activate
The activate command switches between different PyXLL installations quickly.
>> pyxll activate [OPTIONS] [PATH]
Options:
--debug / -d Output more information when running the command.
--non-interactive / -ni Don’t prompt the user for any input. [#ni]
• You can maintain multiple versions of PyXLL at the same time by installing PyXLL into different folders.
• This command selects which PyXLL add-in is active in Excel and does not change any files or configuration.
• Can be run with or without PATH.
– If PATH is not specified then it will look for pyxll.xll in the current working directory and activate that,
or prompt for a path if pyxll.xll is not found.
– If PATH is specified it should be a folder containing the PyXLL add-in to be activated.
pyxll install-certificate
Installs the PyXLL certificate into the ‘Trusted Publishers’ certificate store.
>> pyxll install-certificate [OPTIONS]
Options:
--debug / -d Output more information when running the command.
Installing the certificate is done as part of installing PyXLL but can also be done using this command (for example,
if installing the certificate failed during the initial install).
If the certificate can’t be installed then Excel may prompt the user that the add-in is unsafe or prevent it from
loading, depending on Excel’s Trust Center Settings.
pyxll uninstall
The uninstall command uninstalls the PyXLL Add-In from Excel.
>> pyxll uninstall
• This command only uninstalls the PyXLL add-in from Excel.
• No files will be deleted.
• To reinstall the same PyXLL add-in run pyxll activate.
3.1. Installing PyXLL 12
PyXLL User Guide, Release 5.3.0
[#ni]
The --non-interactive option is new in PyXLL 5.3.0 and enables pyxll activate to be used from a
script more easily for automated deployment of PyXLL environments.
3.1.3 Manual Installation
Before you start you will need to have Microsoft Excel for Windows installed, as well as a compatible version of
Python.
PyXLL works with any Python distribution, including Anaconda. For specific instructions about installing with
Anaconda or Miniconda see Using PyXLL with Anaconda.
Warning: These instructions are for manually installing the PyXLL Excel Add-In.
You may find it more convenient to use our command line tool for installing or upgrading PyXLL.
1. Download the PyXLL Zipfile
PyXLL comes as a zipfile you download from the download page. Select and download the correct version
depending on the versions of Python and Excel you want to use and agree to the terms and conditions.
Warning: Excel, Python and PyXLL all come in 64-bit and 32-bit versions.
The three products must be all 32-bit or all 64-bit.
2. Unpack the Zipfile
PyXLL is packaged as a zip file. Unpack the zip file where you want PyXLL to be installed.
There is no installer to run; you complete the installation in Excel after any necessary configuration changes.
3. Edit the Config File
You configure PyXLL by editing the pyxll.cfg file. Any text editor will do.
Set the executable setting in the PYTHON section of your config file to the full path to your Python executable.
pythonw.exe or python.exe
You may have noticed we’ve used pythonw.exe instead of python.exe.
The only difference between the two is that pythonw.exe doesn’t open a console window and so using that
means that we don’t see a console window is a Python subprocess is started (e.g. if using the subprocess or
multiprocessing Python packages).
If you prefer to use python.exe then that will work fine too.
[PYTHON]
executable = <path to your pythonw.exe>
PyXLL uses this setting to determine where the Python runtime libraries and Python packages are located.
You can determine where the executable for an installed Python interpreter with the command:
3.1. Installing PyXLL 13
PyXLL User Guide, Release 5.3.0
pythonw -c "import sys; print(sys.executable)"
While you have the pyxll.cfg file open take look through and see what other options are available.
You can find documentation for all available options in the Configuring PyXLL section of the user guide.
One important section of the config file is the LOG section. In there you can set where PyXLL should log to and
the logging level. If you are having trouble, set the log verbosity to debug to get more detailed logging.
[LOG]
verbosity = debug
Warning: The “;” character is used to comment out lines in the config file.
If a line starts with “;” then it will not be read by PyXLL.
4. Install the Add-In in Excel
DLL not found
If you get an error saying that Python is not installed or the Python dll can’t be found you may need to set the
Python executable in the config.
If setting the executable doesn’t resolve the problem then it’s possible your Python dll is in a non-standard
location. You can set the dll location in the config to tell PyXLL where to find it.
Once you’re happy with the configuration you can install the add-in in Excel by following the instructions below.
• Excel 2010 - 2019 / Office 365 Select the File menu in Excel and go to Options -> Add-Ins -> Manage
Excel Addins and browse for the folder you unpacked PyXLL to and select pyxll.xll.
• Excel 2007 Click the large circle at the top left of Excel and go to Options -> Add-Ins -> Manage Excel
Addins and browse for the folder you unpacked PyXLL to and select pyxll.xll.
• Excel 97 - 2003 Go to Tools -> Add-Ins -> Browse and locate pyxll.xll in the folder you unpacked the zip
file to.
Warning: If Excel prompts you to ask if you want to copy the add-in to your local add-ins folder then select
No.
When PyXLL loads it expects its config file to be in the same folder as the add-in, and if Excel copies it to
your local add-ins folder then it won’t be able to find its config file.
3.1. Installing PyXLL 14
PyXLL User Guide, Release 5.3.0
5. Install the PyXLL Stubs Package (Optional)
If you are using a Python IDE that provides autocompletion or code checking or if you want to execute your
code outside Excel, say for testing purposes, you will need to install the pyxll module to avoid your code raising
ImportError exceptions.
In the downloaded zip file you will find a .whl file whose exact filename depends on the version of PyXLL. That’s
a Python Wheel containing a dummy pyxll module that you can import when testing without PyXLL. You can
then use code that depends on the pyxll module outside of Excel (e.g. when unit testing).
To install the wheel run the following command (substituting the actual wheel filename) from a command line:
> cd C:\Path\Where\You\Unpacked\PyXLL
> pip install "pyxll-wheel-filename.whl"
The real pyxll module is compiled into the pyxll.xll addin, and so is always available when your code is running
inside Excel.
If you are using a version of Python that doesn’t support pip you can instead unzip the .whl file into your Python
site-packages folder (the wheel file is simply a zip file with a different file extension).
Next Steps
Now you have PyXLL installed you can start adding your own Python code to Excel.
See Worksheet Functions for details of how you can expose your own Python functions to Excel as worksheet
functions, or browse the User Guide for information about the other features of PyXLL.
3.1. Installing PyXLL 15
PyXLL User Guide, Release 5.3.0
3.1.4 Using PyXLL with Anaconda
• What is Anaconda
• Which Anaconda Distribution to Choose
• Creating a Virtual Environment (optional)
• Installing PyXLL with Anaconda
• Switching Virtual Environments
What is Anaconda
Anaconda is an open source Python distribution that aims to simplify Package management and distribution.
The Anaconda distribution includes over a thousand Python packages as well as its own package and virtual
environment manager, Conda.
For users wanting just the package and virtual environment manager, Conda, without the large download and
install size of the full Anaconda distribution, there is also Miniconda.
Both Anaconda and Miniconda work well with PyXLL.
Which Anaconda Distribution to Choose
PyXLL will work fine with any Anaconda or Miniconda distribution for Windows. Note that PyXLL only supports
Microsoft Windows and will not work on macOS.
When downloading Anaconda you are given the choice between Python 2 and Python 3. All current Python
versions are supported by PyXLL, and so you are free to choose whichever version is right for you.
The Anaconda download page also offers the choice between a 64 bit installer and a 32 bit installed. The 64 bit
installer is the default selection, but which one you need depends on the version of Excel you are using.
It is not possible to use a 64 bit Python environment with the 32 bit version of Excel.
To determine which version of Excel you are using, in Excel go to File -> Account -> About.
If your Excel version does not inclide “64-bit” as shown above, you are using the 32 bit version of Excel and will
need to download the 32 bit version of Anaconda or Miniconda.
Creating a Virtual Environment (optional)
When using Anaconda or Miniconda it’s recommended to work within a virtual environment.
A virtual environment is a Python environment where you can install and update packages without modifying the
base Python install. You can have multiple environments at any time, so you could have a virtual environment
dedicated to everything you do in Excel with PyXLL without having to change any other environments you might
have for other tasks.
Virtual environments are created using the “conda create” command.
For example, to create a Python 3.7 environment for use with PyXLL named “pyxll”, start an Anaconda command
prompt and run the following:
3.1. Installing PyXLL 16
PyXLL User Guide, Release 5.3.0
>> conda create -n pyxll python=3.7
This will create a new Python 3.7 environment called “pyxll” (the name can be anything, it doesn’t have to be
pyxll).
You then have to activate that environment and install the packages you want:
>> activate env
>> conda install pandas
To see what environments you have, use “conda info –envs”. That will give you the path to where the new pyxll
environment has been created.
Installing PyXLL with Anaconda
PyXLL can be used with the Anaconda and Miniconda distributions. Use of either a virtual env or the base Python
environment is supported.
Follow the installation instructions to install PyXLL.
If you are using the PyXLL Command Line Tool then be sure to activate your conda environment first.
(base) >> activate env
(env) >> pip install pyxll
(env) >> pyxll install
If you are installing the PyXLL add-in manually then edit your pyxll.cfg file so that the executable setting refer-
ences the Python executable from your conda environment:
[PYTHON]
executable = C:\Program Files\Anaconda\envs\pyxll\pythonw.exe
To determine what Python executable to use, open an Anaconda Command prompt and activate the virtual envi-
ronment you want to use and type “where pythonw”:
(base) >> activate env
(env) >> where pythonw
C:/Program Files/Anaconda/envs/env/pythonw.exe
Switching Virtual Environments
To change the virtual environment that PyXLL uses from the one your originally configured, simply update your
pyxll.cfg config file to use the new virtual env and restart Excel.
Don’t forget that you may also need to install the pyxll stubs package in the new virtual environment if you require
code completion in your IDE, or if you are importing pyxll outside of Excel for any other reason.
3.2 Configuring PyXLL
Finding the config file
In PyXLL’s About dialog it displays the full path to the config file in use. Clicking on the path will open the
config file in your default editor.
The PyXLL config is available to your addin code at run-time via get_config.
3.2. Configuring PyXLL 17
PyXLL User Guide, Release 5.3.0
If you add your own sections to the config file they will be ignored by PyXLL but accessible to your code via
the config object.
If you’ve not installed the PyXLL addin yet, see Installing PyXLL.
The config file is a plain text file that should be kept in the same folder as the PyXLL addin .xll file, and should
have the same name as the addin but with a .cfg extension. In most cases it will simply be pyxll.cfg.
You can load the config file from an alternative location by setting the environment variable
PYXLL_CONFIG_FILE to the full path of the config file you wish to load before starting Excel.
Paths used in the config file may be absolute or relative. The latter (those not beginning with a slash) are interpreted
relative to the directory containing the config file.
Warning: Lines beginning with a semicolon are ignored as comments.
When setting a value in the configuration file, make sure there is no leading semicolon or your changes will
have no effect.
THIS WILL HAVE NO EFFECT
;setting = value
SETTING IS EFFECTIVE WITH NO SEMICOLON
setting = value
3.2.1 Python Settings
[PYTHON]
;
; Python settings
;
pythonpath = semi-colon or new line delimited list of directories
executable = full path to the Python executable (python.exe)
dll = full path to the Python dynamic link library (pythonXX.dll)
pythonhome = location of the standard Python libraries
ignore_environment = ignore environment variables when initializing Python
The Python settings determine which Python interpreter will be used, and some Python settings. Generally speak-
ing, when your system responds to the python command by running the correct interpreter there is usally no
need to alter this part of your configuration.
Sometimes you may want to specify options that differ from your system default; for example, when using a
Python virtual environment or if the Python you want to use is not installed as your system default Python.
• pythonpath The pythonpath is a list of directories that Python will search in when importing modules.
When writing your own code to be used with PyXLL you will need to change this to include the
directories where that code can be imported from.
[PYTHON]
pythonpath =
c:\path\to\your\code
c:\path\to\some\more\of\your\code
.\relative\path\relative\to\config\file
• executable If you want to use a different version of Python than your system default Python then setting
this option will allow you to do that.
Note that the Python version (e.g. 2.7 or 3.5) must still match whichever Python version you selected
when downloading PyXLL, but this allows you to switch between different virtual environments or
different Python distributions.
3.2. Configuring PyXLL 18
PyXLL User Guide, Release 5.3.0
PyXLL does not actually use the executable for anything, but this setting tells PyXLL where it can
expect to find the other files it needs as they will be installed relative to this file (e.g. the Python dll
and standard libraries).
[PYTHON]
executable = c:\path\to\your\python\installation\pythonw.exe
If you wish to set the executable globally outside of the config file, the environment variable
PYXLL_PYTHON_EXECUTABLE can be used. The value set in the config file is used in preference
over this environment variable.
• dll PyXLL can usually locate the necessary Python dll without further help, but if your installation is
non-standard or you wish to use a specific dll for any reason then you can use this setting to indform
PyXLL of its location..
[PYTHON]
dll = c:\path\to\your\python\installation\pythonXX.dll
If you wish to set the dll globally outside of the config file, the environment variable
PYXLL_PYTHON_DLL can be used. The value set in the config file is used in preference over this
environment variable.
• pythonhome The location of the standard libraries is usually determined by the location of the Python
executable.
If for any reason the standard libraries are not installed relative to the chosen or default executable
then setting this option will tell PyXLL where to find them.
Usually if this setting is set at all it should be set to whatever sys.prefix evaluates to in a Python
prompt from the relevant interpreter.
[PYTHON]
pythonhome = c:\path\to\your\python\installation
If you wish to set the pythonhome globally outside of the config file, the environment variable
PYXLL_PYTHONHOME can be used. The value set in the config file is used in preference over this
environment variable.
• ignore_environment New in PyXLL 3.5
When this option is set to any value, any standard Python environment variables such as PYTHONPATH
are ignored when initializing Python.
This is advisable so that any global environment variables that might conflict with the settings in the
pyll.cfg file do not affect how Python is initialized.
This must be set if using FINCAD, as FINCAD sets PYTHONPATH to it’s own internal Python
distribution.
3.2.2 PyXLL Settings
• Common Settings
• Reload Settings
• Abort Settings
• Array Settings
• Object Cache Settings
• AsyncIO Settings
3.2. Configuring PyXLL 19
PyXLL User Guide, Release 5.3.0
• win32com Settings
• Error Handling
• RTD Settings
• CTP Settings
• Metadata
• Other Settings
[PYXLL]
;
modules = comma or new line delimited list of python modules
ribbon = filename (or list of filenames) of a ribbon xml documents
developer_mode = 1 or 0 indicating whether or not to use the developer mode
name = name of the addin visible in Excel
category = default category for functions registered with :py:func: xl_func
external_config = paths or URLs of additional config files to load
;
; reload settings
;
auto_reload = 1 or 0 to enable or disable automatic reloading (off by default)
auto_rebind = 1 or 0 to enable or disable automatic rebinding (on by default)
deep_reload = 1 or 0 to activate or deactivate the deep reload feature
deep_reload_include = modules and packages to include when reloading (only when
˓→deep_reload is set)
deep_reload_exclude = modules and packages to exclude when reloading (only when
˓→deep_reload is set)
deep_reload_include_site_packages = 1 or 0 to include site-packages when deep
˓→reloading
deep_reload_disable = 1 or 0 to disable all deep reloading functionality
;
; allow abort settings
;
allow_abort = 1 or 0 to set the default value for the allow_abort kwarg
abort_throttle_time = minimum time in seconds between checking abort status
abort_throttle_count = minimum number of calls to trace function between checking
˓→abort status
;
; array settings
;
auto_resize_arrays = 1 or 0 to enable automatic resizing of all array functions
always_use_2d_arrays = disable 1d array types and use [] to mean a 2d array
allow_auto_resizing_with_dynamic_arrays = Resize CSE array formulas even when
˓→dynamic arrays are available
disable_array_formula_check = Don't check whether an array formula is a CSE array
˓→formula or not
;
; object cache settings
;
get_cached_object_id = function to get the id to use for cached objects
clear_object_cache_on_reload = clear the object cache when reloading PyXLL
recalc_cached_objects_on_open = recalculate cached object functions when opening
˓→workbooks (default=1)
disable_loading_objects = disable loading cached objects saved in the workbook
˓→(default=0)
;
; asyncio event loop settings
;
stop_event_loop_on_reload = 1 or 0 to stop the event loop when reloading PyXLL
start_event_loop = fully qualified function name if providing your own event loop
(continues on next page)
3.2. Configuring PyXLL 20
PyXLL User Guide, Release 5.3.0
(continued from previous page)
stop_event_loop = fully qualified function name to stop the event loop
;
; win32com settings
;
win32com_gen_path = path to use for win32com's __gen_path__ for generated wrapper
˓→classes
win32com_delete_gen_path = 1 or 0. If set, win32com's __gen_path__ folder will be
˓→deleted when starting
win32com_no_dynamic_dispatch = 1 or 0. If set, don't use win32com's dynamic
˓→wrappers
win32com_mutex_disable= 1 or 0. If set, don't use a global mutex to prevent
˓→concurrent access to gen_py wrappers.
win32com_mutex_timeout = Timeout in seconds for global mutex. Use -1 for an
˓→infinite timeout.
win32com_mutex_name = Name of the global mutex to prevent concurrent access to gen_
˓→py wrappers.
;
; error handling
;
error_handler = function for handling uncaught exceptions
error_cache_size = maximum number of exceptions to cache for failed function calls
;
; RTD settings
;
recalc_rtd_on_open = recalculate RTD functions when opening workbooks (default=1)
rtd_volatile_default = make RTD functions volatile by default (default=0)
;
; CTP settings
;
ctp_timer_interval = time in seconds between calls to CTPBridge.on_timer
˓→(default=0.1)
;
; metadata
;
metadata_custom_xml_namespace = namespace to use instead of the default for saved
˓→CustomXMLPart metadata
disable_saving_metadata = disable saving any metadata with the workbook
;
; other settings
;
disable_com_addin = 1 or 0 to disable the COM addin component of PyXLL
disable_recalc_on_open = 1 or 0 to disable recalculating any cells on the opening
˓→of a workbook.
ignore_entry_points = 1 or 0 to ignore entry points
quiet = 1 or 0 to disable all start up messages
Common Settings
• modules When PyXLL starts or is reloaded this list of modules will be imported automatically.
Any code that is to be exposed to Excel should be added to this list, or imported from modules in this
list.
The interpreter will look for the modules usings its standard import mechanism. By adding folders
using the pythonpath setting, which can be set in the [PYTHON] config section, you can cause it to
look in specific folders where your software can be found.
• ribbon If set, the ribbon setting should be the file name (or list of files) of custom ribbon user interface
XML file. The file names may be absolute paths or relative to the config file.
The XML files should conform to the Microsoft CustomUI XML schema (customUI.xsd) which
3.2. Configuring PyXLL 21
PyXLL User Guide, Release 5.3.0
may be downloaded from Microsoft here https://www.microsoft.com/en-gb/download/details.aspx?
id=1574.
If a list of files is given then all of those files will be loaded. Any tabs or groups with the same ids
found in the files will be merged.
See the Customizing the Ribbon chapter for more details.
• developer_mode When the developer mode is active a PyXLL menu with a Reload menu item will be
added to the Addins toolbar in Excel.
If the developer mode is inactive then no menu items will be automatically created so the only ones
visible will be the ones declared in the imported user modules.
This setting defaults to off (0) if not set.
• name The name setting, if set, changes the name of the addin as it appears in Excel.
When using this setting the addin in Excel is indistinguishable from any other addin, and there is no
reference to the fact it was written using PyXLL. If there are any menu items in the default menu, that
menu will take the name of the addin instead of the default ‘PyXLL’.
• category The category setting changes the default category used when registering worksheet functions
with xl_func.
• external_config This setting may be used to reference another config file (or files) located elsewhere,
either as a relative or absolute path or as a URL.
For example, if you want to have the main pyxll.cfg installed on users’ local PCs but want to control
the configuration via a shared file on the network you can use this to reference that external config file.
Multiple external config files can be used by setting this value to a list of file names (comma or newline
separated) or file patterns.
Values in external config files override what’s in the parent config file, apart from pythonpath, modules
and external_config which get appended to.
In addition to setting this in the config file, the environment variable
PYXLL_EXTERNAL_CONFIG_FILE can be used. Any external configs set by this environment
variable will be added to those specified in the config.
Reload Settings
• auto_reload When set PyXLL will detect when any Python modules, config or ribbon files have been
modified and automatically trigger a reload.
This setting defaults to off (0) if not set.
• auto_rebind If any of the decorators xl_func, xl_macro or xl_menu are called after PyXLL has
started PyXLL can automatically re-create the function bindings in Excel. This is useful if dynamically
importing modules after PyXLL has started.
This setting defaults to on (1) if not set.
• deep_reload Reloading PyXLL reloads all the modules listed in the modules config setting. When
working on more complex projects often you need to make changes not just to those modules, but also
to modules imported by those modules.
PyXLL keeps track of anything imported by the modules listed in the modules config setting (both
imported directly and indirectly) and when the deep_reload feature is enabled it will automatically
reload the module dependencies prior to reloading the main modules.
Standard Python modules and any packages containing C extensions are never reloaded.
It should be set to 1 to enable deep reloading 0 (the default) to disable it.
3.2. Configuring PyXLL 22
PyXLL User Guide, Release 5.3.0
• deep_reload_include Optional list of modules or packages to restrict reloading to when deep reload-
ing is enabled.
If not set, everything excluding the standard Python library and packages with C extensions will be
considered for reloading.
This can be useful when working with code in only a few packages, and you don’t want to reload
everything each time you reload. For example, you might have a package like:
my_package \
- __init__.py
- business_logic.py
- data_objects.py
- pyxll_functions.py
In your config you would add my_package.pyxll_function to the modules to import, but when reloading
you would like to reload everything in my_package but not any other modules or packages that it might
also import (either directly or indirectly). By adding my_package to deep_reload_include the deep
reloading is restricted to only reload modules in that package (in this case, my_package.business_logic
and my_package.data_objects).
[PYXLL]
modules = my_package
deep_reload = 1
deep_reload_include = my_package
• deep_reload_exclude Optional list of modules or packages to exclude from deep reloading when
deep_reload is set.
If not set, only modules in the standard Python library and modules with C extensions will be ignored
when doing a deep reload.
Reloading Python modules and packages doesn’t work for all modules. For example, if a module
modifies the global state in another module when its imported, or if it contains a circular dependency,
then it can be problematic trying to reload it.
Because the deep_reload feature will attempt to reload all modules that have been imported, if you
have a module that cannot be reloaded and is causing problems you can add it to this list to be ignored.
Excluding a package (or sub-package) has the effect of also excluding anything within that package
or sub-package. For example, if there are modules a.b.x and a.b.y then excluding a.b will also
exclude a.b.x and a.b.y.
deep_reload_exclude can be set when deep_reload_include is set to restrict the set of modules that
will be reloaded. For example, if there are modules ‘a.b and ‘a.b.c’, and everything in ‘a’ should be
reloaded except for ‘a.b.c’ then ‘a’ would be added to deep_reload_include and ‘a.b.c’ would be added
to deep_reload_exclude.
• deep_reload_include_site_packages When deep_reload is set, any modules inside the site-
packages folder will be ignored unless this option is enabled.
This setting defaults to off (0) if not set.
• deep_reload_disable Deep reloading works by installing an import hook that tracks the dependen-
cies between imported modules. Even when deep_reload is turned off this import hook is enabled, as
it is sometimes convenient to be able to turn it on to do a deep reload without restarting Excel.
When deep_reload_disable is set to 1 then this import hook is not enabled and setting deep_reload
will have no effect. .. warning:: Changing this setting requires Excel to be restarted.
3.2. Configuring PyXLL 23
PyXLL User Guide, Release 5.3.0
Abort Settings
• allow_abort (defaults to 0) The allow_abort setting is optional and sets the default value for the al-
low_abort keyword argument to the decorators xl_func, xl_macro and xl_menu.
It should be set to 1 for True or 0 for False. If unset the default is 0.
Using this feature enables a Python trace function which will impact the performance of Python code
while running a UDF. The exact performance impact will depend on what code is being run.
• abort_throttle_time When a UDF has been registered as abort-able, a trace function is used that
gets called frequently as the Python code is run by the Python interpreter.
To reduce the impact of the trace function Excel can be queried less often to see if the user has aborted
the function.
abort_throttle_time is the minimum time in seconds between checking Excel for the abort status.
• abort_throttle_count When a UDF has been registered as abort-able, a trace function is used that
gets called frequently as the Python code is run by the Python interpreter.
To reduce the impact of the trace function Excel can be queried less often to see if the user has aborted
the function.
abort_throttle_count is the minimum number of call to the trace function between checking Excel for
the abort status.
Array Settings
• auto_resize_arrays (defaults to 0) The auto_resize_arrays setting can be used to enable automatic
resizing of array formulas for all array function. It is equivalent to the auto_resize keyword argument
to xl_func and applies to all array functions that don’t explicitly set auto_resize.
It should be set to 1 for True or 0 for False (the default).
• always_use_2d_arrays (defaults to 0) Before PyXLL 4.0, all array arguments and return types were
2d arrays (list of lists). The type suffix [] was used to mean a 2d array type (e.g. a float[] argument
would receive a list of lists).
Since PyXLL 4.0, 1d arrays have been added and [][] should now be used when a 2d array is
required. To make upgrading easier, this setting disables 1d arrays and any array types specified with
[] will be 2d arrays as they were prior to version 4.
• allow_auto_resizing_with_dynamic_arrays (defaults to 1) In 2019 Excel added a new “Dy-
namic Arrays” feature to Excel. This replaces the need for auto resized arrays in PyXLL.
It is still possible to enter old-style Ctrl+Shift+Enter (CSE) arrays however, and these will continue to
be resized automatically by PyXLL if auto_resize is set for the function.
PyXLL’s auto-resizing can be disabled completely if Excel has the new dynamic arrays feature by
setting this option to 0.
New in PyXLL 4.4
• disable_array_formula_check (defaults to 0) PyXLL checks the formula of array functions to
determine whether the function is an old style Ctrl+Shift+Enter (CSE) formula or a new style dynamic
array.
It uses this to determine whether or not to use its own auto-resizing for the the array function.
This check can be disabled by setting this to 1.
New in PyXLL 4.4
3.2. Configuring PyXLL 24
PyXLL User Guide, Release 5.3.0
Object Cache Settings
• get_cached_object_id When Python objects are returned from an Excel worksheet function and no
suitable converter is found (or the return type object is specified) the object is added to an internal
object cache and a handle to that cached object is returned.
The format of the cached object handle can be customized by setting get_cached_object_id to a custom
function, e,g
[PYXLL]
get_cached_object_id = module_name.get_custom_object_id
def get_custom_object_id(obj):
return "[Cached %s <0x%x>]" % (type(obj), id(obj))
The computed id must be unique as it’s used when passing these objects to other functions, which
retrieves them from the cache by the id.
• clear_object_cache_on_reload Clear the object cache when reloading the PyXLL add-in.
Defaults to 1, but if using cached objects that are instances of classes that aren’t reloaded then this can
be set to 0 to avoid having to recreate them when reloading.
• recalc_cached_objects_on_open If set, default all functions that return cached objects as need-
ing to be recalculated when opening a workbook.
This is the equivalent to setting recalc_on_open=True in the xl_func decorator. Disabling
it does not prevent cells that have already been saved with this flag set from be calculated when a
workbook opens. For that, set disable_recalc_on_open=1 in your config.
This setting can be overridden on specific functions by setting recalc_on_open in the xl_func
decorator.
Defaults to 0.
See Recalculating On Open.
• disable_loading_objects If set, any cached objects saved as part of a workbook will be ignored
when opening the workbook.
Defaults to 0.
See Saving Objects in the Workbook.
AsyncIO Settings
• stop_event_loop_on_reload If set to ‘1’, the asyncio Event Loop used for async user defined
functions and RTD methods will be stopped when PyXLL is reloaded.
See Asynchronous Functions.
New in PyXLL 4.2.0.
• start_event_loop Used to provide an alternative implementation of the asyncio event loop used by
PyXLL.
May be set to the fully qualified name of a function that takes no arguments and returns a started
asyncio.AbstractEventLoop.
If this option is set then stop_event_loop should also be set.
See Asynchronous Functions.
New in PyXLL 4.2.0.
3.2. Configuring PyXLL 25
PyXLL User Guide, Release 5.3.0
• stop_event_loop Used to provide an alternative implementation of the asyncio event loop used by
PyXLL.
May be set to the fully qualified name of a function that stops the event loop started by the function
specified by the option start_event_loop.
If this option is set then start_event_loop should also be set.
See Asynchronous Functions.
New in PyXLL 4.2.0.
win32com Settings
• win32com_gen_path This sets the win32com.__gen_path__ path used for win32com’s gener-
ated wrapper classes.
By default win32com uses the user’s Temp folder, but this is shared between all Python sessions, not
just PyXLL. If this becomes corrupted or updated by an external Python script then it can stop the
win32com package from functioning correctly, and setting it to a folder specifically for PyXLL can
avoid that problem.
• win32com_delete_gen_path If set the win32com.__gen_path__ folder used for generated
wrapper classes will be deleted when PyXLL starts.
This is not usually necessary as setting win32com_gen_path will ensure that no other Python code
will use the same generated wrapper classes, however it can be set if you are experiencing problems
with the wrapper classes becoming corrupted or invalid.
If using this option you will also want to set win32com_gen_path so the wrapper classes are
created somewhere other than the default location. The folder referenced by win32com_gen_path
is the one that will be deleted.
Care should be taken to ensure that there is nothing in the folder you do not want to be deleted before
setting this option, although the folder can be recovered from the recycle bin.
• win32com_no_dynamic_dispatch When returning a COM object using the win32com package,
PyXLL will attempt to use a static wrapper generated by win32com. If that fails and this setting is not
set then it will fallback to using a dynamic dispatch wrapper.
Dynamic wrappers are suitable in most cases and behave in the same way as the static wrappers, but
the win32com.client.constants set of constants only contains constants included by static wrappers,
and so falling back to dynamic dispatch can result in missing constants.
• win32com_mutex_disable PyXLL uses a global mutex to prevent multiple Excel sessions from at-
tempting to create the win32com wrapper modules at the same time when calling xl_app.
This is to prevent multiple Excel sessions from creating the wrappers at the same time and overwriting
each other, leading to corrupt wrapper files.
This can be disabled by setting this setting to 1 but if you think you need to disable this then please
contact PyXLL support before doing so.
New in PyXLL 5.1
• win32com_mutex_timeout This setting is only provided as a precaution and you should contact
PyXLL support if you think you need to change it.
New in PyXLL 5.1
• win32com_mutex_name This setting is only provided as a precaution and you should contact PyXLL
support if you think you need to change it.
New in PyXLL 5.1
3.2. Configuring PyXLL 26
PyXLL User Guide, Release 5.3.0
Error Handling
• error_handler If a function raises an uncaught exception, the error handler specified here will be
called and the result of the error handler is returned to Excel.
If not set, uncaught exceptions are returned to Excel as error codes.
See Error Handling.
• error_cache_size If a worksheet function raises an uncaught exception it is cached for retrieval via
the get_last_error function.
This setting sets the maximum number of exceptions that will be cached. The least recently raised
exceptions are removed from the cache when the number of cached exceptions exceeds this limit.
The default is 500.
RTD Settings
• recalc_rtd_on_open Default all RTD functions as needing to be recalculated when opening a work-
book.
This is the equivalent to setting recalc_on_open=True in the xl_func decorator. Disabling
it does not prevent cells that have already been saved with this flag set from be calculated when a
workbook opens. For that, set disable_recalc_on_open=1 in your config.
This setting can be overridden on specific functions by setting recalc_on_open in the xl_func
decorator.
Defaults to 1.
See Recalculating On Open.
• rtd_volatile_default Make all RTD functions volatile by default. This restores the behaviour prior
to PyXLL 4.5.0.
When enabled RTD functions are volatile so they will be calculated when opening a workbook, but
the wrapped Python function will only be called if the arguments to the function are actually changed.
Usually this should be left disabled as RTD functions are now calculated when the workbook opens
using the Recalculating On Open feature of PyXLL instead.
Defaults to 0.
CTP Settings
• ctp_timer_interval Time in seconds between calls to CTPBridgeBase.on_timer.
The CTP bridge classes are what integrate the Python UI toolkit with the Excel Windows message loop.
They use on_timer to poll their own message queues. If you are finding the panel is not responsive
enough you can reduce the timer interval with this setting.
This can also be set for each CTP by passing timer_interval to :py:func create_ctp .
New in PyXLL 5.1
3.2. Configuring PyXLL 27
PyXLL User Guide, Release 5.3.0
Metadata
• metadata_custom_xml_namespace Custom metadata is saved in order to support certain features
of PyXLL such as recalculating cells when a workbook opens.
This is saved in the workbook as a CustomXMLPart using an XML namespace specific to the PyXLL
add-in so as not to conflict with data saved by other add-ins. If you have specified a name for your
add-in using the name setting that will be used to avoid conflict with any other PyXLL add-ins you
may have loaded.
If you prefer to specify the namespace to use instead of having PyXLL use it’s own namespace you
can do so by setting this option.
[PYXLL]
metadata_custom_xml_namespace = urn:your_name:metadata
• disable_saving_metadata Set this option to disable writing any metadata.
Note that this will affect all PyXLL features that require metadata such as recalculating on open, as
well as formatting dynamic arrays.
The default is 0 (not disabled).
Other Settings
• startup_script Path or URL of a batch or Powershell script to run when Excel starts.
This script will be run when Excel starts, but before Python is initialized. This is so that the script can
install anything required by the add-in on demand when Excel runs.
See Startup Script.
• disable_com_addin PyXLL is packaged as a single Excel addin (the pyxll.xll file), but it actually
implements both a standard XLL addin and COM addin in the same file.
Setting disable_com_addin to 1 stops the COM addin from being used.
The COM addin is used for ribbon customizations and RTD functions and if disabled these features
will not be available.
• disable_recalc_on_open Disable any automatic recalculations when a workbook is opened that
would otherwise be caused by the Recalculating On Open feature.
This does not stop Excel from calculating anything else, such as volatile functions or other dirty cells
in the saved workbook.
See Recalculating On Open.
• ignore_entry_points If your Python packages are on a network drive it can be slow to look for entry
points, which may result in slow start times for Excel.
This setting stops PyXLL from looking for entry points.
See Setuptools Entry Points.
• quiet The quiet setting is for use in enterprise settings where the end user has no knowledge that the
functions they’re provided with are via a PyXLL addin.
When set PyXLL won’t raise any message boxes when starting up, even if errors occur and the addin
can’t load correctly. Instead, all errors are written to the log file.
3.2. Configuring PyXLL 28
PyXLL User Guide, Release 5.3.0
3.2.3 Logging
PyXLL redirects all stdout and stderr to a log file. All logging is done using the standard logging python module.
The [LOG] section of the config file determines where logging information is redirected to, and the verbosity of
the information logged.
[LOG]
path = directory of where to write the log file
file = filename of the log file
verbosity = logging level (debug, info, warning, error or critical)
format = format string
max_size = maximum size the log file can get to before rolling to a new file.
roll_interval = period before the log file will be rolled and a new log will be
˓→started.
backup_count = number of old log files to keep.
encoding = encoding to use when writing the logfile (defaults to 'utf-8')
PyXLL creates some configuration substitution values that are useful when setting up logging.
Substitution Variable Description
pid process id
date current date
xl_version
1
Excel version
py_version
2
Python version
pyxll_version
2
PyXLL version
• path Path where the log file will be written to.
This may include substitution variables as listed above, e.g.
[LOG]
path = C:/Temp/pyxll-logs-%(date)s
• file Filename of the log file.
This may include substitution variables as listed above, e.g.
[LOG]
file = pyxll-log-%(pid)s-%(xlversion)s-%(date)s.log
• verbosity The logging verbosity can be used to filter out or show warning and errors. It sets the log
level for the root logger in the logging module, as well as setting PyXLL’s internal log level.
It may be set to any of the following
– debug (most verbose level, show all log messages including debugging messages)
– info
– warning
– error
– critical (least verbose level, only show the most critical errors)
If you are having any problems with PyXLL it’s recommended to set the log verbosity to debug as that
will give a lot more information about what PyXLL is doing.
• format The format string is used by the logging module to format any log messages. An example format
string is:
1
xlversion was renamed to xl_version in PyXLL 5.2 but both forms will continue to work.
2
Both the py_version and pyxll_version substitution variables are new in PyXLL 5.2.
3.2. Configuring PyXLL 29
PyXLL User Guide, Release 5.3.0
[LOG]
format = "%(asctime)s - %(name)s - %(levelname)s - %(message)s"
For more information about log formatting, please see the logging module documentation from the
Python standard library.
• max_size
3
Maximum size the log file is allowed to grow to.
Once the log file goes over this size it will be renamed to add a timestamp to the file and a new log file
will be started.
The size can be in Kb, Mb or Gb, for example to set it to 100Mb use max_size = 100Mb.
If zero, the log file will be allowed to grow indefinitely.
• roll_interval
3
If set the log file will be rolled periodically.
This setting can be used alongside max_size and if both are set the log will be rolled either either
the roll period is reached or the file size goes over the maximum allowed size.
The interval can be any of:
– a number of days, hours, minutes or seconds using the form Nd for days (eg 7d), Nm, and Ns
respectively.
– midnight to indicate the log should be rolled after midnight.
– W0-6 to roll on a specific day of the week, eg W0 for Sunday and W6 for Saturday.
• backup_count
3
The number of backup log files to keep after rolling the log.
If set, only the last N rolled log files will be kept.
Instead of setting a fixed number a period can be specified, eg 7d to keep log files for 7 days.
• encoding Encoding to use when writing the log file.
Defaults to ‘utf-8’.
New in PyXLL 4.2.0.
3.2.4 License Key
[LICENSE]
key = license key
file = path to shared license key file
If you have a PyXLL license key you should set it in [LICENSE] section of the config file.
The license key may be embedded in the config as a plain text string, or it can be referenced as an external file
containing the license key. This can be useful for group licenses so that the license key can be managed centrally
without having to update each user’s configuration when it is renewed.
• key Plain text license key as provided when you purchased PyXLL.
This does not need to be set if you are setting file.
The environment variable PYXLL_LICENSE_KEY can be used instead of setting this in the config file.
• file Path or URL of a plain text file containing the license key as provided when you purchased PyXLL.
The file may contain comment lines starting with #.
This does not need to be set if you are setting key.
The environment variable PYXLL_LICENSE_FILE can be used instead of setting this in the config
file.
3
Log rolling is new in PyXLL 5.2.
3.2. Configuring PyXLL 30
PyXLL User Guide, Release 5.3.0
3.2.5 Environment Variables
Config values may include references to environment variables. To substitute an environment variable into your
value use
%(ENVVAR_NAME)s
When the variable has not been set, (since PyXLL 4.1) you can assert a default value using the following format
%(ENVVAR_NAME:default_value)s
For example:
[LOG]
path = %(TEMP:./logs)s
file = %(LOG_FILE:pyxll.log)s
It’s possible to set environment variables in the [ENVIRONMENT] section of the config file.
[ENVIRONMENT]
NAME = VALUE
For each environment variable you would like set, add a line to the [ENVIRONMENT] section.
3.2.6 Startup Script
New in PyXLL 4.4.0
• Introduction
• Example
• Script Commands
Introduction
The startup_script option can be used to run a batch or Powershell script when Excel starts, and again each
time PyXLL is reloaded.
This can be useful for ensuring the Python environment is installed correctly and any Python packages are up to
date, or for any other tasks you need to perform when starting Excel.
The script runs before Python is initialized, and can therefore be used to set up a Python environment if one doesn’t
already exist. The PyXLL config can be manipulated from the startup script so any settings such as the modules
list, pythonpath or even the Python executable can be set on startup rather than being fixed in the pyxll.cfg
file.
The startup script can be a local file, a file on a network drive, or even a URL. Using a network drive or a URL
can be a good option when deploying PyXLL to multiple users where you want to have control over what’s run on
startup without having to update each PC.
Batch files (.bat or .cmd) and Powershell files (.ps1) are supported. Script files must use one of these file exten-
sions.
The script is run with the current working directory (CWD) set to the same folder as the PyXLL add-in itself, and
so relative paths can be used relative to the xll file.
If successful the script should exit with exit code 0. Any other exit code will be interpreted as the script not having
been run successfully by PyXLL.
See also Using a startup script to install and update Python code.
3.2. Configuring PyXLL 31
PyXLL User Guide, Release 5.3.0
Example
A startup script could be used to download a Python environment and configure PyXLL.
REM startup-script.bat
@ECHO OFF
REM If the Python env already exists no need to download it
IF EXIST ./python-env-xx GOTO SKIPDOWNLOAD
REM Download and unpack a Python environment to ./python-env-xx/
wget https://intranet/python/python-env-xx.tar.gz
tar -xzf python-env-xx.tar.gz --directory python-env-xx
:SKIPDOWNLOAD
REM Update the PyXLL settings with the executable
ECHO pyxll-set-option PYTHON executable ./python-venv-xx/pythonw.exe
The script is configured in the pyxll.cfg file, and could be on a remote network drive or web server.
[PYXLL]
startup_script = https://intranet/pyxll/startup-script.bat
Script Commands
When PyXLL runs the startup script (either a batch or Powershell script) it monitors the stdout of the script for
special commands. These commands can be used by your script to get information from PyXLL, update settings,
and give the user information.
To call one of the commands from your script you echo it to the stdout. For example, the command
pyxll-set-option can be used to set one of PyXLL’s configuration options. In a batch file, to set the
LOG/verbosity setting to debug it would be called as follows:
ECHO pyxll-set-option LOG verbosity debug
Calling the command from Powershell is the same:
Echo "pyxll-set-option LOG verbosity debug"
Some commands return results back to the script. They do this by writing the result to the script’s stdin. To read
the result from a command that returns something you need to read it from the stdin into a variable. The command
pyxll-get-command is one that returns a result and can be used from a batch file as follows:
ECHO pyxll-get-option PYTHON executable
SET /p EXECUTABLE=
REM The PYTHON executable setting is now in the variable %EXECUTABLE%
Or in Powershell it would look like:
Echo "pyxll-get-option PYTHON executable"
$executable = Read-Host
Below is a list of the available commands.
• pyxll-get-option
• pyxll-set-option
• pyxll-unset-option
3.2. Configuring PyXLL 32
PyXLL User Guide, Release 5.3.0
• pyxll-set-progress
• pyxll-show-progress
• pyxll-set-progress-status
• pyxll-set-progress-title
• pyxll-set-progress-caption
• pyxll-get-version
• pyxll-get-python-version
• pyxll-get-arch
• pyxll-get-pid
• pyxll-restart-excel
pyxll-get-option
Gets the value of any option from the config.
Takes two arguments, SECTION and OPTION, and returns the option’s value.
• Batch File
ECHO pyxll-get-option SECTION OPTION
SET /p VALUE=
• Powershell
Echo "pyxll-get-option SECTION OPTION"
$value = Read-Host
If used on a multi-line option (e.g. PYTHON/modules and PYTHON/pythonpath) the value returned will be a list
of value delimited by the separator documented for the setting.
pyxll-set-option
Sets a config option.
Takes three arguments, SECTION, OPTION and VALUE. Doesn’t return a value.
• Batch File
ECHO pyxll-set-option SECTION OPTION VALUE
• Powershell
Echo "pyxll-set-option SECTION OPTION VALUE"
When used with multi-line options (e.g. PYTHON/modules and PYTHON/pythonpath) this command appends to
the list of values. Use pyxll-unset-option to clear the list first if you want to overwrite any current value.
3.2. Configuring PyXLL 33
PyXLL User Guide, Release 5.3.0
pyxll-unset-option
Unsets the specified option.
Takes two arguments, SECTION and OPTION. Doesn’t return value.
• Batch File
ECHO pyxll-unset-option SECTION OPTION
• Powershell
Echo "pyxll-unset-option SECTION OPTION"
pyxll-set-progress
Display or update a progress indicator dialog to inform the user of the current progress.
This is useful for potentially long running start up scripts, such as when downloading files from a network location
or installing a large number of files.
Takes one argument, the current progress as a number between 0 and 100. Doesn’t return a value.
• Batch File
ECHO pyxll-set-progress PERCENT_COMPLETE
• Powershell
Echo "pyxll-set-progress PERCENT_COMPLETE"
pyxll-show-progress
Displays the progress indicator without setting the current progress.
This shows the progress indicator in ‘marquee’ style where it animates continuously rather than showing any
specific progress.
If the progress indicator is already shown this command does nothing.
Takes no arguments and doesn’t return a value.
• Batch File
ECHO pyxll-show-progress
• Powershell
Echo "pyxll-show-progress"
3.2. Configuring PyXLL 34
PyXLL User Guide, Release 5.3.0
pyxll-set-progress-status
Sets the status text of the progress indicator dialog.
This does not show the progress indicator if it is not already shown. Use pyxll-show-progress or
pyxll-set-progress to show the progress indicator.
Takes one argument, STATUS, and doesn’t return a value.
• Batch File
ECHO pyxll-set-progress-status STATUS
• Powershell
Echo "pyxll-set-progress-status STATUS"
pyxll-set-progress-title
Sets the title of the progress indicator dialog.
This does not show the progress indicator if it is not already shown. Use pyxll-show-progress or
pyxll-set-progress to show the progress indicator.
Takes one argument, TITLE, and doesn’t return a value.
• Batch File
ECHO pyxll-set-progress-title TITLE
• Powershell
Echo "pyxll-set-progress-title TITLE"
pyxll-set-progress-caption
Sets the caption text of the progress indicator dialog.
This does not show the progress indicator if it is not already shown. Use pyxll-show-progress or
pyxll-set-progress to show the progress indicator.
Takes one argument, CAPTION, and doesn’t return a value.
• Batch File
ECHO pyxll-set-progress-caption CAPTION
• Powershell
Echo "pyxll-set-progress-caption CAPTION"
3.2. Configuring PyXLL 35
PyXLL User Guide, Release 5.3.0
pyxll-get-version
Gets the version of the installed PyXLL add-in.
Takes no arguments and returns the version.
• Batch File
ECHO pyxll-get-version
SET /p VERSION=
• Powershell
Echo "pyxll-get-version"
$version = Read-Host
pyxll-get-python-version
Gets the version of Python the installed PyXLL add-in is compatible with in the form
PY_MAJOR_VERSION.PY_MINOR_VERSION.
Takes no arguments and returns the Python version.
• Batch File
ECHO pyxll-get-python-version
SET /p VERSION=
• Powershell
Echo "pyxll-get-python-version"
$version = Read-Host
pyxll-get-arch
Gets the machine architecture of the Excel process and PyXLL add-in.
Takes no arguments and returns either ‘x86’ for 32 bit or ‘x64’ for a 64 bit.
• Batch File
ECHO pyxll-get-arch
SET /p ARCH=
• Powershell
Echo "pyxll-get-arch"
$arch = Read-Host
3.2. Configuring PyXLL 36
PyXLL User Guide, Release 5.3.0
pyxll-get-pid
Gets the process id of the Excel process.
Takes no arguments and the process id.
• Batch File
ECHO pyxll-get-pid
SET /p PID=
• Powershell
Echo "pyxll-get-pid"
$pid = Read-Host
pyxll-restart-excel
Displays a message box to the user informing them Excel needs to restart. If the user selects ‘Ok’ then Excel will
restart. The user can cancel this and if they do so the script will be terminated.
This can be used if your script needs to install something that would require Excel to be restarted. When Excel
restarts your script will be run again and so you should ensure that it doesn’t repeatedly request to restart Excel.
One possible use case is if you want to upgrade the PyXLL add-in itself. You can rename the existing one (it can’t
be deleted while Excel is using it, but it can be renamed) and copy a new one in its place and then request to restart
Excel.
Takes one optional argument, MESSAGE, which will be disaplyed to the user. Doesn’t return a result.
• Batch File
ECHO pyxll-restart-excel MESSAGE
• Powershell
Echo "pyxll-restart-excel MESSAGE"
3.2.7 Menu Ordering
Menu items added via the xl_menu decorator can specify what order they should appear in the menus. This can
be also be set, or overridden, in the config file.
To specify the order of sub-menus and items within the sub-menus use a “.” between the menu name, sub-menu
name and item name.
The example config below shows how to order menus with menu items and sub-menus.
[MENUS]
menu_1 = 1 # order of the top level menu menu_1
menu_1.menu_item_1 = 1 # order of the items within menu_1
menu_1.menu_item_2 = 2
menu_1.menu_item_3 = 3
menu_2 = 2 # order of the top level menu menu_2
menu_2.sub_menu_1 = 1 # order of the sub-menu sub_menu_1 within menu_2
menu_2.sub_menu_1.menu_item_1 = 1 # order of the items within sub_menu_1
menu_2.sub_menu_1.menu_item_2 = 2
menu_2.menu_item_1 = 2 # order of item within menu_2
menu_2.sub_menu_2 = 3
menu_2.sub_menu_2.menu_item_1 = 1
menu_2.sub_menu_2.menu_item_2 = 2
3.2. Configuring PyXLL 37
PyXLL User Guide, Release 5.3.0
Here’s how the menus appear in Excel:
3.2.8 Shortcuts
Macros can have keyboard shortcuts assigned to them by using the shortcut keyword argument to xl_macro.
Alternatively, these keyboard shortcuts can be assigned, or overridden, in the config file.
Shortcuts should be one or more modifier key names (Ctrl, Shift or Alt) and a key, separated by the ‘+’ symbol.
For example, ‘Ctrl+Shift+R’. If the same key combination is already in use by Excel it may not be possible to
assign a macro to that combination.
The PyXLL developer macros (reload and rebind) can also have shortcuts assigned to them.
[SHORTCUTS]
pyxll.reload = Ctrl+Shift+R
module.macro_function = Alt+F3
See Keyboard Shortcuts for more details.
3.3 Worksheet Functions
3.3.1 Introduction
“argument” vs. “parameter”
To avoid confusion we use the term parameter(s) to describe the formal argument specifications in the function
definition, and argument(s) to indicate the actual values provided in a function call.
This section explains how to write PyXLL functions that handle simple values, and make those functions available
in Excel.
If you’ve not installed the PyXLL addin yet, see Installing PyXLL.
PyXLL user defined functions (UDFs) written in Python are exactly the same as any other Excel worksheet
function. They are called from formulas in an Excel worksheet in the same way, and appear in Excel’s function
wizard just like Excel’s native functions (see Function Documentation).
3.3. Worksheet Functions 38
PyXLL User Guide, Release 5.3.0
Here’s a simple example. Suppose you had the following file stored at C:\Users\pyxll\modules\my_module.py:
from pyxll import xl_func
@xl_func
def hello(name):
return "Hello, %s" % name
The decorator xl_func tells PyXLL to register the immediately following Python function as a worksheet func-
tion in Excel.
Once you have saved that code you need to ensure the interpreter can find it by adding the containing directory to
the pythonpath setting and the module name to the modules setting in the pyxll.cfg config file:
Tip: No Need to Restart Excel!
Use the ‘Reload’ menu item under the PyXLL menu to reload your Python code without restarting Excel -
this causes all Python modules to be reloaded, making updated code available without thr need to restart Excel
itself.
[PYXLL]
;
; Make sure that PyXLL imports the module when loaded.
;
modules = my_module
[PYTHON]
;
; Ensure that PyXLL can find the module.
; Multiple modules can come from a single directory.
;
pythonpath = C:\Users\pyxll\modules
If you make these changes and reload the PyXLL addin, or restart Excel, you can use the PyXLL function you
have just added in formulas in any Excel worksheet, because the function was decorated with xl_func.
=hello("me")
3.3. Worksheet Functions 39
PyXLL User Guide, Release 5.3.0
Worksheet functions can take simple values, as in the example above, or more complex arguments such as arrays
of data. In particular, function arguments can be cell references, in which case the value passed to the function
will be the value of the cell.
Examples of more complex types supported by PyXLL include NumPy arrays, Pandas DataFrames and Series
and Python objects. You can add support for other types using PyXLL’s custom type system.
In order for PyXLL to apply to correct type conversion the Python function must have a function signature.
3.3.2 Argument and Return Types
• Specifying the Argument and Return Types
– @xl_func Function Signature
– Python Function Annotations
– @xl_arg and @xl_return Decorators
• Standard Types
– Simple Types
– Array Types
– Dictionary Types
– Union Types
• Using Python Objects Directly
• Custom Types
• Manual Type Conversion
3.3. Worksheet Functions 40
PyXLL User Guide, Release 5.3.0
Specifying the Argument and Return Types
When you started using PyXLL you probably discovered how easy it is to register a Python function in Excel. To
improve efficiency and reduce the chance of errors, you can also specify what types the arguments to that function
are expected to be, and what the return type is. This information is commonly known as a function’s signature.
There are three common ways to add a signature to a function, described in the following sections.
@xl_func Function Signature
The most common way to provide the signature is to provide a function signature as the first argument to
xl_func:
from pyxll import xl_func
from datetime import date, timedelta
@xl_func("date d, int i: date")
def add_days(d, i):
return d + timedelta(days=i)
When adding a function signature string it is written as a comma separated list of each argument type followed
by the argument name, ending with a colon followed by the return type. The signature above specifies that the
function takes two arguments called d, a date, and i, and integer, and returns a value of type date. You may omit
the return type; PyXLL automatically converts it into the most appropriate Excel type.
Adding type information is useful as it means that any necessary type conversion is done automatically, before
your function is called.
Python Function Annotations
Type information can also be provided using type annotations in Python 3. This example shows how you pass
dates to Python functions from Excel using type annotations:
from pyxll import xl_func
from datetime import date, timedelta
@xl_func
def add_days(d: date, i: int) -> date:
return d + timedelta(days=i)
Internally, an Excel date is just a floating-point a number. If you pass a date to a Python function with no type
information then that argument will just be a Python float when it is passed to your Python function. Adding a
signature removes the need to convert from a float to a date in every function that expects a date. The annotation
on your Python function (or the signature argument to xl_func) tells PyXLL and Excel what type you expect,
and the the conversion is done automatically.
3.3. Worksheet Functions 41
PyXLL User Guide, Release 5.3.0
@xl_arg and @xl_return Decorators
The final way type information can be added to a function is by using specific argument and return type decorators.
These are particularly useful for more complex types that require parameters, such as NumPy arrays and Pandas
types. Parameterized types can be specified as part of the function signature, or using xl_arg and xl_return.
For example, the following function takes two 1-dimensional NumPy arrays, using a function signature:
from pyxll import xl_func
import numpy as np
@xl_func("numpy_array<ndim=1> a, numpy_array<ndim=1> b: var")
def add_days(a, b):
return np.correlate(a, b)
But this could be re-written using xl_arg as follows:
from pyxll import xl_func, xl_arg
import numpy as np
@xl_func
@xl_arg("a", "numpy_array", ndim=1)
@xl_arg("b", "numpy_array", ndim=1)
def add_days(a, b):
return np.correlate(a, b)
Standard Types
Simple Types
Several standard types may be used in the signature specified when exposing a Python worksheet function. These
types have a straightforward conversion between PyXLL’s Excel-oriented types and Python types. Arrays and
more complex objects are discussed later.
Below is a list of these basic types. Any of these can be specified as an argument type or return type in a function
signature.
PyXLL type Python type
bool bool
datetime datetime.datetime
1
date datetime.date
float float
int int
object object
2
rtd RTD
3
str str
time datetime.time
unicode unicode
4
var object
5
xl_cell XLCell
6
range Excel Range COM Wrapper
7
1
Excel represents dates and times as numbers. PyXLL will convert dates and times to and from Excel’s number representation, but in
Excel they will look like numbers unless formatted. When returning a date or time from a Python function you will need to change the Excel
cell formatting to a date or time format.
2
The object type in PyXLL lets you pass Python objects between functions as object handles that reference the real objects in an internal
object cache. You can store object references in spreadsheet cells and use those cell references as function arguments.
3.3. Worksheet Functions 42
PyXLL User Guide, Release 5.3.0
Array Types
See Array Functions for more details about array functions.
Ranges of cells can be passed from Excel to Python as a 1d or 2d array.
Any type can be used as an array type by appending [] for a 1d array or [][] for a 2d array:
from pyxll import xl_func
@xl_func("float[][] array: float")
def py_sum(array):
"""return the sum of a range of cells"""
total = 0.0
# 2d array is a list of lists of floats
for row in array:
for cell_value in row:
total += cell_value
return total
A 1d array is represented in Python as a simple list, and when a simple list is returned to Excel it will be returned
as a column of data. A 2d array is a list of lists (list of rows) in Python. To return a single row of data, return it as
a 2d list of lists with only a single row.
When returning a 2d array remember that it* must* be a list of lists. This is why you woud return a single a row
of data as [[1, 2, 3, 4]], for example. To enter an array formula in Excel you select the cells, enter the
formula and then press Ctrl+Shift+Enter.
Any type can be used as an array type, but float[] and float[][] require the least marshalling between
Excel and python and are therefore the fastest of the array types.
If you a function argument has no type specified or is using the var type, if it is passed a range of data that will
be converted into a 2d list of lists of values and passed to the Python function.
See NumPy Array Types and Pandas Types for details of how to pass numpy and pandas types between Excel
and Python functions.
Dictionary Types
Python functions can be passed a dictionary, converted from an Excel range of values. Dicts in a spreadsheet are
represented as a 2xN range of keys and their associated values. The keys are in the columns unless the range’s
transpose argument (see below) is true.
The following is a simple function that accepts an dictionary of integers keyed by strings. Note that the key and
value types are optional and default to var if not specified.
@xl_func("dict<str, int>: str") # Keys are strings, values are integers
def dict_test(x):
return str(x)
For Python’s primitive types, use the var type instead.
3
rtd is for functions that return Real Time Data.
4
Unicode was only introduced in Excel 2007 and is not available in earlier versions. Use xl_version to check what version of Excel is
being used if in doubt.
5
The var type can be used when the argument or return type isn’t fixed. Using the more a specific type has the advantage that arguments
passed from Excel will get coerced correctly. For example if your function takes an int you’ll always get an int and there’s no need to do type
checking in your function. If you use a var, you may get a float if a number is passed to your function, and if the user passes a non-numeric
value your function will still get called so you need to check the type and raise an exception yourself.
If no type information is provided for a function it will be assumed that all arguments and the return type are the var type. PyXLL will do
its best to perform the necessary conversions, but providing specific information about typing is the best way to ensure that type conversions
are correct.
6
Specifying xl_cell as an argument type passes an XLCell instance to your function instead of the value of the cell. This is useful if
you need to know the location or some other data about the cell used as an argument as well as its value.
7
New in PyXLL 4.4*
The range argument type is the same as xl_cell except that instead of passing an XLCell instance a Range COM object is used
instead.
The default Python COM package used is win32com, but this can be changed via an argument to the range type. For example, to use
xlwings instead of win32com you would use range<xlwings>.
3.3. Worksheet Functions 43
PyXLL User Guide, Release 5.3.0
The dict type can be parameterized so that you can also specify the key and value types, and some other options.
• dict, when used as an argument type
dict<key=var, value=var, transpose=False, ignore_missing_keys=True>
– key Type used for the dictionary keys.
– value Type used for the dictionary values.
– transpose - False (the default): Expect the dictionary with the keys on the first column of data
and the values on the second. - True: Expect the dictionary with the keys on the first row of data
and the values on the second. - None: Try to infer the orientation from the data passed to the
function.
– ignore_missing_keys If True, ignore any items where the key is missing.
• dict, when used as an return type
dict<key=var, value=var, transpose=False, order_keys=True>
– key Type used for the dictionary keys.
– value Type used for the dictionary values.
– transpose - False (the default): Return the dictionary as a 2xN range with the keys on the first
column of data and the values on the second. - True: Return the dictionary as an Nx2 range with
the keys on the first row of data and the values on the second.
– order_keys Sort the dictionary by its keys before returning it to Excel.
Union Types
Union types can be used for functions that accept arguments or return objects of more than one types. In such
examples, the var type can be used, but then the value may need to be converted in the function when it’s more
convenient to let PyXLL do the conversion.
Note: Union types are new in PyXLL 5.1
For example, support you have a function that can take either a date or a string. This is a common situation
for functions that can either take an absolute date or a time period. Using the var type, Excel dates are passed
as numbers since that is how Excel represents dates, and so the conversion from a number to a date must be done
using get_type_converter (see Manual Type Conversion). Using a union type removes the need for this
extra conversion step.
Union types may be specified in the xl_func function signature using the union<...> type, with the possible
types passed as parameters. They can also be specified using Python type annotations using the typing.Union
type annotation.
The following is a function that will accept either a date or a str. The conversion from an Excel date to a
Python date is performed automatically if a date is passed, and the function can also accept a string argument from
Excel.
3.3. Worksheet Functions 44
PyXLL User Guide, Release 5.3.0
from pyxll import xl_func
import datetime as dt
@xl_func("union<date, str> value: str")
def date_or_string(value):
if isinstance(value, dt.date):
return "Value is a date: %s" % value
return "Value is a string: %s" % value
The same can be written using Python type annotations as follows
from pyxll import xl_func
import datetime as dt
import typing
@xl_func
def date_or_string(value: typing.Union[dt.date, str]) -> str:
if isinstance(value, dt.date):
return "Value is a date: %s" % value
return "Value is a string: %s" % value
The order the union type arguments are specified in matters. PyXLL will attempt to convert the Excel value to
the Python value in the order the arguments are specified from left to right. In the above example, if str was
placed before date then it would not work as intended since the conversion to str would take precedence over
the conversion to date.
Using Python Objects Directly
Not all Python types can be conveniently converted to a type that can be represented in Excel.
Even for types that can be represented in Excel it is not always desirable to do so (for example, and Pandas
DataFrame with millions of rows could be returned to Excel as a range of data, but it would not be very useful and
would make Excel very slow).
For cases like these, PyXLL can return a handle to the Python object to Excel instead of trying to convert the
object to an Excel friendly representation. The actual object is held in PyXLL’s object cache until it is no longer
needed. This allows for Python objects to be passed between Excel functions easily, without the complexity or
possible performance problems of converting them between the Python and Excel representations.
For more information about how PyXLL can automatically cache objects to be passed between Excel functions as
object handles, see Cached Objects.
Custom Types
As well as the standard types listed above you can also define your own argument and return types, which can
then be used in your function signatures.
Custom argument types need a function that will convert a standard Python type to the custom type, which will
then be passed to your function. For example, if you have a function that takes an instance of type X, you can
declare a function to convert from a standard type to X and then use X as a type in your function signature. When
called from Excel, your conversion function will be called with an instance of the base type, and then your exposed
UDF will be called with the result of that conversion.
To declare a custom type, you use the xl_arg_type decorator on your conversion function. The
xl_arg_type decorator takes at least two arguments, the name of your custom type and the base type.
Here’s an example of a simple custom type:
from pyxll import xl_arg_type, xl_func
(continues on next page)
3.3. Worksheet Functions 45
PyXLL User Guide, Release 5.3.0
(continued from previous page)
class CustomType:
def __init__(self, x):
self.x = x
@xl_arg_type("CustomType", "string")
def string_to_customtype(x):
return CustomType(x)
@xl_func("CustomType x: bool")
def test_custom_type_arg(x):
# this function is called from Excel with a string, and then
# string_to_customtype is called to convert that to a CustomType
# and then this function is called with that instance
return isinstance(x, CustomType)
You can now use CustomType as an argument type in a function signature. The Excel UDF will take a string, but
when your Python function is called the conversion function will have been used invisibly to automatically convert
that string to a CustomType instance.
To use a custom type as a return type you also have to specify the conversion function from your custom type to a
base type. This is exactly the reverse of the custom argument type conversion described previously.
The custom return type conversion function must be decorated with the xl_return_type decorator.
For the previous example the return type conversion function could look like:
from pyxll import xl_return_type, xl_func
@xl_return_type("CustomType", "string")
def customtype_to_string(x):
# x is an instance of CustomType
return x.x
@xl_func("string x: CustomType")
def test_returning_custom_type(x):
# the returned object will get converted to a string
# using customtype_to_string before being returned to Excel
return CustomType(x)
Any recognized type can be used as a base type. That can be a standard Python type, an array type or another
custom type (or even an array of a custom type!). The only restriction is that it must resolve to a standard type
eventually.
Custom types can be parameterized by adding additional keyword arguments to the conversion functions. Values
for these arguments are passed in from the type specification in the function signature, or using xl_arg and
xl_return:
from pyxll import xl_arg_type, xl_func
class CustomType2:
def __init__(self, x, y):
self.x = x
self.y = y
@xl_arg_type("CustomType2", "string", y=None)
def string_to_customtype2(x):
return CustomType(x, y)
@xl_func("CustomType2<y=1> x: bool")
def test_custom_type_arg2(x):
assert x.y == 1
return isinstance(x, CustomType)
3.3. Worksheet Functions 46
PyXLL User Guide, Release 5.3.0
Manual Type Conversion
Sometimes it’s useful to be able to convert from one type to another, but it’s not always convenient to have to
determine the chain of functions to call to convert from one type to another.
For example, you might have a function that takes an array of var types, but some of those may actually be
datetimes, or one of your own custom types.
To convert them to those types you would have to check what type has actually been passed to your function and
then decide what to call to get it into exactly the type you want.
PyXLL includes the function get_type_converter to do this for you. It takes the names of the source and
target types and returns a function that will perform the conversion, if possible.
Here’s an example that shows how to get a datetime from a var parameter:
from pyxll import xl_func, get_type_converter
from datetime import datetime
@xl_func("var x: string")
def var_datetime_func(x):
var_to_datetime = get_type_converter("var", "datetime")
dt = var_to_datetime(x)
# dt is now of type 'datetime'
return "%s : %s" % (dt, type(dt))
3.3.3 Cached Objects
PyXLL can pass Python objects between Excel functions even if the Python object can’t be converted to a type
that can be represented in Excel. It does this by maintaining an object cache and returning handles to objects in the
cache. The cached object is automatically retrieved when an object handle is passed to another PyXLL function.
Even for types that can be represented in Excel it is not always desirable to do so (for example, and Pandas
DataFrame with millions of rows could be returned to Excel as a range of data but it would not be very useful and
would make Excel slow).
Instead of trying to convert the object to an Excel friendly representation, PyXLL can cache the Python object
and return a handle to that cached object to Excel. The actual object is held in PyXLL’s object cache until it is
no longer needed. This allows for Python objects to be passed between Excel functions easily, and without the
complexity or possible performance problems of converting them between the Python and Excel representations.
• Example
• Accessing Cached Objects in Macros
• Populating the Cache On Loading
• Saving Objects in the Workbook
• Custom Object Handles
• Mixing Primitive Values and Objects
• Clearing the Cache on Reloading
3.3. Worksheet Functions 47
PyXLL User Guide, Release 5.3.0
Example
The following example shows one function that returns a Python object, and another that takes that Python object
as an argument:
from pyxll import xl_func
class CustomObject:
def __init__(self, name):
self.name = name
@xl_func("string name: object")
def create_object(x):
return CustomObject(x)
@xl_func("object x: string")
def get_object_name(x):
assert isinstance(x, CustomObject)
return x.name
Note that the object is not copied. This means if you modify the object passed to your function then you will be
modifying the object in the cache.
When an object is returned in this way it is added to an internal object cache. This cache is managed by PyXLL
so that objects are evicted from the cache when they are no longer needed.
When using the var type, if an object of a type that has no converter is returned then the object type is used.
When passing an object handle to a function where the argument type is the var type (or unspecified) then the
object will be retrieved from the cache and passed to the function automatically.
3.3. Worksheet Functions 48
PyXLL User Guide, Release 5.3.0
Accessing Cached Objects in Macros
When writing an Excel macro, if you need to access a cached object from a cell or set a cell value and cache an
object you can use the XLCell class. Using the XLCell.options method you can set the type to object
before getting or setting the cell value. For example:
from pyxll import xl_macro, xl_app, XLCell
@xl_macro
def get_cached_object():
"""Get an object from the cache and print it to the log"""
# Get the Excel.Application object
xl = xl_app()
# Get the current selection Range object
selection = xl.Selection
# Get the cached object stored at the selection
cell = XLCell.from_range(selection)
obj = cell.options(type="object").value
# 'value' is the actual Python object, not the handle
print(obj)
@xl_macro
def set_cached_object():
"""Cache a Python object by setting it on a cell"""
# Get the Excel.Application object
xl = xl_app()
# Get the current selection Range object
selection = xl.Selection
# Create our Python object
obj = object()
# Cache the object in the selected cell
cell = XLCell.from_range(selection)
cell.options(type="object").value = obj
Instead of using a cell reference it is also possible to fetch an object from the cache by its handle. To do this use
get_type_converter to convert the str handle to an object, e.g.:
from pyxll import xl_func, get_type_converter
@xl_macro("str handle: bool")
def check_object_handle(handle):
# Get the function to lookup and object from its handle
get_cached_object = get_type_converter("str", "object")
# Get the cached object from the handle
obj = get_cached_object(handle)
# Check the returned object is of the expected type
return isinstance(obj, MyClass)
3.3. Worksheet Functions 49
PyXLL User Guide, Release 5.3.0
Populating the Cache On Loading
When Excel first starts the cache is empty and so functions returning objects must be run to populate the cache.
PyXLL has a feature that enables functions to be called automatically when loading a workbook (Recalculating
On Open). When a workbook is opened any cell containing a function that has been set to recalculate on open will
be recalculated when that workbook is opened and calculated.
By default, all functions that return the type object are marked as needing to be recalculated when a saved
workbook is opened. This ensures that the cache is populated at the time the workbook opens and is first calculated
and avoids the need to fully recalculate the entire workbook.
For functions that take some time to run, or any other functions that should not be recalculated as soon as the
workbook opens, use recalc_on_open=False in the xl_func decorator, eg:
from pyxll import xl_func
@xl_func(": object", recalc_on_open=False)
def dont_calc_on_open():
# long running task
return obj
You can change the default behaviour so that recalc_on_open is False for object functions unless explicitly
marked otherwise by setting recalc_cached_objects_on_open = 0, e.g.
[PYXLL]
recalc_cached_objects_on_open = 0
Note: This feature is new in PyXLL 4.5. For prior versions, or for workbooks saved using prior versions, the
workbook will need to be recalculated by pressing Ctrl+Alt+F9 to populate the cache.
Saving Objects in the Workbook
Rather than having to recalculate functions to recreate the cached objects PyXLL can serialize and save cached
objects as part of the Excel Workbook Metadata.
This is useful if you have objects that take a long time to calculate and they don’t need to be recreated each time
the workbook is open.
Caution should be used when deciding whether or not to use this function. It is usually better to source data from
an external data source and load it in each time. Referencing an external data source ensures that you always see a
consistent, up to date view of the data. There are times when saving objects in the Workbook is more convenient
and we only advise that you consider which option is right for your use-case.
Saving objects as part of the workbook will inevitably increase the size of the workbook file, and so you should
also consider how large the objects to be saved are. Excel should never be used as a replacement for a database!
To have PyXLL save the result of a function use the save parameter to the object return type:
from pyxll import xl_func
@xl_func(": object<save=True>")
def function_with_saved_result():
# Construct an object and return it
return obj
When calling a function like the one above the object handle will be slightly different to a normal object handle.
For objects that are saved the object handle needs to be globally unique and not just unique in the current Excel
session. This is because when the object is loaded it will keep the same id and that must not conflict with any other
objects that may already exist in the Excel session. If you are using your own custom object handle you must take
this into consideration.
3.3. Worksheet Functions 50
PyXLL User Guide, Release 5.3.0
Objects that are to be saved must be pickle-able. This means that they must be able to be serialized and de-
serialized using Python’s pickle module. They are serialized and added to the workbook metadata when the
workbook is saved.
See https://docs.python.org/3/library/pickle.html for details about Python’s pickle module.
Note that the Python code required to reconstruct the pickled objects must be available when opening a workbook
containing saved objects in order for those objects to be deserialized and entered into the object cache.
Loading saved objects can be disabled by setting disable_loading_objects = 1 in the PYXLL section
of the pyxll.cfg config file.
[PYXLL]
disable_loading_objects = 1
Note: This feature is new in PyXLL 5.0.
Custom Object Handles
The method of generating object handles can be customized by setting get_cached_object_id in the
PYXLL section of the config file.
The generated object handles must be unique as each object is stored in the cache keyed by its object handle. For
objects that will be saved as part of the workbook it’s important to use a globally unique identifier as those objects
will be loaded with the same id later and must not conflict with other objects that may have already been loaded
into the object cache.
Only one function can be registered for generating object handles for all cached objects. Different formats of
object handles for different object types can be generated by inspecting the type of the object being cached.
The following example shows a simple function that returns an object handle from an object. Note that it uses the
‘id’ function to ensure that no two objects can have the same handle. When the kwarg save is set to True that
indicates that the object may be serialized and saved as part of the workbook and so a globally unique identifier is
used in that case.
def get_custom_object_id(obj, save=False):
if save:
return str(uuid.uuid4())
return "[Cached %s <0x%x>]" % (type(obj), id(obj))
To use the above function to generate the object handles for PyXLL’s object cache it needs to be configured in the
pyxll.cfg config file. This is done using the fully qualified function name, including the module the function
is declared in.
Listing 1: module_name.py
[PYXLL]
get_cached_object_id = module_name.get_custom_object_id
The save kwarg in the custom object id function indicates whether or not the object may be saved in the work-
book. When objects are saved the same id is reused when loading the workbook later, and so these ids should be
globally unique to avoid conflicts with other existing objects. See Saving Objects in the Workbook.
Note: Prior to PyXLL 5.0 the custom object handle function did not take the save kwarg.
3.3. Worksheet Functions 51
PyXLL User Guide, Release 5.3.0
Mixing Primitive Values and Objects
If you have a function that you want to return an object in some cases and a primitive value, like a number or
string, in other cases then you can use the skip_primitives parameter to the object return type.
Listing 2: pyxll.cfg
from pyxll import xl_func
from random import random
@xl_func("int x: object<skip_primitives=True>")
def func(x):
if x == 0:
# returned as a number to Excel
return 0
# return a list of values as an 'object'
array = [random() for i in range(x)]
return array
When skip_parameters is set to True then the following types will not be returned as object handles:
• int
• float
• str
• bool
• Exception
• datetime.date
• datetime.datetime
• datetime.time
If you need more control over what types are considered primitive you can pass a tuple of types as the
skip_primitives parameter.
Clearing the Cache on Reloading
Whenever PyXLL is reloaded the object cache is cleared. This is because the cached objects may be instances of
old class definitions that have since been reloaded. Using instances of old class definitions may lead to unexpected
behaviour.
If you know that you are not reloading any classes used by cached objects, or if you are comfortable knowing that
the cached objects may be instances of old classes, then you can disable PyXLL from clearing the cache when
reloading. To do this, set clear_object_cache_on_reload = 0 in your pyxll.cfg file.
This is only recommended if you completely understand the above and are aware of the implications of potentially
using instances of old classes that have since been reloaded. One common problem is that methods that have been
changed are not updated for these instances, and isinstance will fail if checked using the new reloaded class.
[PYXLL]
clear_object_cache_on_reload = 0
3.3. Worksheet Functions 52
PyXLL User Guide, Release 5.3.0
3.3.4 Array Functions
• Array Functions in Python
• Ctrl+Shift+Enter (CSE) Array Functions
• Auto Resizing Array Functions
• Dynamic Array Functions
Any function that returns an array (or range) of data in Excel is called an array function.
Depending on what version of Excel you are using, array functions are either entered as a Ctrl+Shift+Enter (CSE)
formula, or as a dynamic array formula. Dynamic array formulas have the advantage over CSE formulas that they
automatically resize according to the size of the result.
To help users of older Excel versions, PyXLL array function results can be automatically re-sized .
The #SPILL! error indicates that the array would overwrite other data.
Array Functions in Python
Any function exposed to Excel using the xl_func decorator that returns a list of values is an array function.
If a function returns a list of simple values (not lists) then it will be returned to Excel as a column of data.
Rectanguler ranges of data can be returned by returning a list of lists, eg:
from pyxll import xl_func
@xl_func
def array_function():
return [
[1, 2, 3],
[4, 5, 6],
[7, 8, 9]
]
An optional function signature passed to xl_func can be used to specify the return type. The suffix [] is used
for a 1d array (column), e.g. float[], and [][] is used for a 2d array, e.g. float[][].
For example, the following function takes 1d array (list of values) and returns a 2d array of values (list of lists):
from pyxll import xl_func
@xl_func("float[]: float[][]")
def diagonal(v):
d = []
for i, x in enumerate(v):
d.append([x if j == i else 0.0 for j in range(len(v))])
return d
NumPy arrays and Pandas types (DataFrames, Series etc) can also be returned as arrays to Excel by specifying the
relevant type in the function signature. See NumPy Array Types and Pandas Types for more details.
When entering an array formula in Excel it should be entered as a Ctrl+Shift+Enter (CSE) formula, or if using
Dynamic Arrays or PyXLL’s array auto-sizing feature then they can be entered in the same way as any other
formula.
3.3. Worksheet Functions 53
PyXLL User Guide, Release 5.3.0
Ctrl+Shift+Enter (CSE) Array Functions
Ctrl+Shift+Enter or CSE formulas are what Excel used for static array formulas in versions of Excel before
Dynamic Arrays were added. PyXLL has an array auto-sizing feature that can emulate dynamic arrays in earlier
versions of Excel that do not implement them.
To enter an array formula in Excel you should do the following:
• Select the range you want the array formula to occupy.
• Enter the formula as normal, but don’t press enter.
• Press Ctrl+Shift+Enter to enter the formula.
Note that unless you are using Dynamic Arrays or PyXLL’s array auto-sizing feature then if the result is larger
than the range you choose then you will only see part of the result. Similarly, if the result is smaller than the
selected range you will see errors for the cells with no value.
To make changes to an array formula, change the formula as normal but use Ctrl+Shift+Enter to enter the new
formula.
Auto Resizing Array Functions
Often selecting a range the exact size of the result of an array formula is not practical. You might not know the
size before calling the function, or it may even change when the inputs change.
PyXLL can automatically resize array functions to match the result. To enable this feature you just add
‘auto_resize=True’ to the options passed to xl_func. For example:
from pyxll import xl_func
@xl_func("float[]: float[][]", auto_resize=True)
def diagonal(v):
d = []
for i, x in enumerate(v):
d.append([x if j == i else 0.0 for j in range(len(v))])
return d
You can apply this to all array functions by setting the following option in your pyxll.cfg config file
[PYXLL]
;
; Have all array functions resize automatically
;
auto_resize_arrays = 1
If you are using a version of Excel that has Dynamic Arrays then the auto_resize option will have no effect by
default. The native dynamic arrays are superior in most cases, but not yet widely available.
Warning: Auto-resizing is not available for RTD functions. If you are returning an array from an RTD
function and need it to resize you can use ref:Dynamic Arrays <dynamic> in Excel from Excel 2016 onwards.
If you are not able to update to a newer version of Excel, another solution is to return the array from your RTD
function as an object, and then have a second non-RTD function to expand that returned object to an array
using PyXLL’s auto-resize feature.
3.3. Worksheet Functions 54
PyXLL User Guide, Release 5.3.0
Dynamic Array Functions
Dynamic arrays were announced as a new feature of Excel towards the end of 2018. This feature will be rolled
out to Office 365 from early 2019. If you are not using Office 365, dynamic arrays are expected to be available in
Excel 2022.
If you are not using a version of Excel with the dynamic arrays feature, you can still have array functions that
re-size automatically using PyXLL. See Auto Resizing Array Functions.
Excel functions written using PyXLL work with the dynamic arrays feature of Excel. If you return an array from
a function, it will automatically re-size without you having to do anything extra.
If you are using PyXLL’s own auto resize feature, PyXLL will detect whether Excel’s dynamic arrays are available
and if they are it will use those in preference to its own re-sizing. This means that you can write code to work in
older versions of Excel that are future-proof and will ‘just work’ when you upgrade to a newer version of Office.
If you want to keep using PyXLL’s auto resize feature even when dynamic arrays are available, you can do so by
specifying the following in your pyxll.cfg config file
[PYXLL]
;
; Use resizing in preference to dynamic arrays
;
allow_auto_resizing_with_dynamic_arrays = 1
Dynamic arrays are a great new feature in Excel and offer some advantages over CSE functions and PyXLL’s
auto-resize feature:
Character-
istic
Advantage
Native to
Excel
Dynamic arrays are deeply integrated into Excel and so the array resizing works with all array
functions, not just ones written with PyXLL.
Spilling If the results of an array formula would cause data to be over-written you will get a new #SPILL
error to tell you there was not enough room. When you select the #SPILL error Excelwill
highlight the spill region in blue so you can see what space it needs.
Referencing
the spill
range in A1#
notation
Dynamic arrays may seamlessly resize as your data changes. When referencing a resizing
dynamic arrays you can reference the whole array in a dependable, resilient way by following
the cell reference with the # symbol. For example, the reference A1# references the entire
spilled range for a dynamic array in A1.
3.3.5 Pandas Types
Pandas DataFrames and Series can be used as function arguments and return types for Excel worksheet functions.
When used as an argument, the range specified in Excel will be converted into a Pandas DataFrame or Series as
specified by the function signature.
When returning a DataFrame or Series, a range of data will be returned to Excel. PyXLL will automatically resize
the range of the array formula to match the returned data if auto_resize=True is set in xl_func.
The following shows returning a random dataframe, including the index:
from pyxll import xl_func
import pandas as pd
import numpy as np
@xl_func("int rows, int columns: dataframe<index=True>", auto_resize=True)
def random_dataframe(rows, columns):
data = np.random.rand(rows, columns)
column_names = [chr(ord('A') + x) for x in range(columns)]
return pd.DataFrame(data, columns=column_names)
3.3. Worksheet Functions 55
PyXLL User Guide, Release 5.3.0
The following options are available for the dataframe and series argument and return types:
• dataframe, when used as an argument type
dataframe<index=0, columns=1, dtype=None, dtypes=None,
index_dtype=None>
index Number of columns to use as the DataFrame’s index. Specifying more than one will result in a
DataFrame where the index is a MultiIndex.
columns Number of rows to use as the DataFrame’s columns. Specifying more than one will result in
a DataFrame where the columns is a MultiIndex. If used in conjunction with index then any column
headers on the index columns will be used to name the index.
dtype Datatype for the values in the dataframe. May not be set with dtypes.
dtypes Dictionary of column name -> datatype for the values in the dataframe. May not be set with dtype.
index_dtype Datatype for the values in the dataframe’s index.
multi_sparse
1
Return sparse results for MultiIndexes. Can be set to True or False, or 'index' or
'columns' if it should only apply to one or the other.
• dataframe, when used as a return type
dataframe<index=None, columns=True>
index If True include the index when returning to Excel, if False don’t. If None, only include if the index
is named.
columns If True include the column headers, if False don’t.
• series, when used as an argument type
series<index=1, transpose=None, dtype=None, index_dtype=None>
index Number of columns (or rows, depending on the orientation of the Series) to use as the Series index.
transpose Set to True if the Series is arranged horizontally or False if vertically. By default the orienta-
tion will be guessed from the structure of the data.
dtype Datatype for the values in the Series.
index_dtype Datatype for the values in the Series’ index.
multi_sparse
1
Return sparse results for MultiIndexes.
• series, when used as a return type
series<index=True, transpose=False>
index If True include the index when returning to Excel, if False don’t.
transpose Set to True if the Series should be arranged horizontally, or False if vertically.
When passing large DataFrames between Python functions, it is not always necessary to return the full DataFrame
to Excel and it can be expensive reconstructing the DataFrame from the Excel range each time. In those cases you
can use the object return type to return a handle to the Python object. Functions taking the dataframe and
series types can accept object handles.
See Using Pandas in Excel for more information.
1
The multi_sparse parameter is new in PyXLL 5.3.0.
3.3. Worksheet Functions 56
PyXLL User Guide, Release 5.3.0
3.3.6 NumPy Array Types
To be able to use numpy arrays you must first have installed the numpy package..
You can use numpy 1d and 2d arrays as argument types to pass ranges of data into your function, and as return
types for returing for array functions. A maximum of two dimensions are supported, as higher dimension arrays
don’t fit well with how data is arranged in a spreadsheet. You can, however, work with higher-dimensional arrays
as Python objects.
To specify that a function should accept a numpy array as an argument or as its return type, use the
numpy_array, numpy_row or numpy_column types in the xl_func function signature.
These types can be parameterized, meaning you can set some additional options when specifying the type in the
function signature.
numpy_array<dtype=float, ndim=2, casting='unsafe'>
• dtype Data type of the items in the array (e.g. float, int, bool etc.).
• ndim Array dimensions, must be 1 or 2.
• casting Controls what kind of data casting may occur. Default is ‘unsafe’.
– 'unsafe' Always convert to chosen dtype. Will fail if any input can’t be converted.
– 'nan' If an input can’t be converted, replace it with NaN.
– 'no' Don’t do any type conversion.
numpy_row<dtype=float, casting='unsafe'>
• dtype Data type of the items in the array (e.g. float, int, bool etc.).
• casting Controls what kind of data casting may occur. Default is ‘unsafe’.
– 'unsafe' Always convert to chosen dtype. Will fail if any input can’t be converted.
– 'nan' If an input can’t be converted, replace it with NaN.
– 'no' Don’t do any type conversion.
numpy_column<dtype=float, casting='unsafe'>
• dtype Data type of the items in the array (e.g. float, int, bool etc.).
• casting Controls what kind of data casting may occur. Default is ‘unsafe’.
– 'unsafe' Always convert to chosen dtype. Will fail if any input can’t be converted.
– 'nan' If an input can’t be converted, replace it with NaN.
– 'no' Don’t do any type conversion.
For example, a function accepting two 1d numpy arrays of floats and returning a 2d array would look like:
from pyxll import xl_func
import numpy
@xl_func("numpy_array<float, ndim=1> a, numpy_array<float, ndim=1> b: numpy_array
˓→<float>")
def numpy_outer(a, b):
return numpy.outer(a, b)
The ‘float’ dtype isn’t strictly necessary as it’s the default. If you don’t want to set the type parameters in the
signature, use the xl_arg and xl_return decorators instead.
PyXLL will automatically resize the range of the array formula to match the returned data if you specify
auto_resize=True in your py:func:xl_func call.
3.3. Worksheet Functions 57
PyXLL User Guide, Release 5.3.0
Floating point numpy arrays are the fastest way to get data out of Excel into Python. If you are work-
ing on performance sensitive code using a lot of data, try to make use of numpy_array<float> or
numpy_array<float, casting='nan'> for the best performance.
See Array Functions for more details about array functions.
3.3.7 Asynchronous Functions
• Asynchronous Worksheet Functions
• The asyncio Event Loop
• Before Python 3.5
Excel has supported asynchronous worksheet functions since Office 2010. To be able to use asynchronous work-
sheet functions with PyXLL you will need to be using at least that version of Office.
Excel asynchronous worksheet functions are called as part of Excel’s calculation in the same way as other func-
tions, but rather than return a result, they can schedule some work and return immediately, allowing Excel’s
calculation to progress while the scheduled work for the asynchronous function continues concurrently. When the
asynchronous work has completed, Excel is notified.
Asynchronous functions still must be completed as part of Excel’s normal calculation phase. Using asynchronous
functions means that many more functions can be run concurrently, but Excel will still show as calculating until
all asynchronous functions have returned.
Functions that use IO, such as requesting results from a database or web server, are well suited to being made
into asynchronous functions. For CPU intensive tasks
1
using the thread_safe option to xl_func may be a better
alternative.
If your requirement is to return the result of a very long running function back to Excel after recalculating has
completed, you may want to consider using an RTD (Real Time Data) function instead. An RTD function doesn’t
have to keep updating Excel, it can just notify Excel once when a single calculation is complete. Also, it can be
used to notify the user of progress which for very long running tasks can be helpful.
1
For CPU intensive problems that can be solved using multiple threads (i.e. the CPU intensive part is done without the Python Global
Interpreter Lock, or GIL, being held) use the thread_safe argument to xl_func to have Excel automatically schedule your functions
using a thread pool.
3.3. Worksheet Functions 58
PyXLL User Guide, Release 5.3.0
Asynchronous Worksheet Functions
Python 3.5 Required
Using the async keyword requires a minimum of Python 3.5.1 and PyXLL 4.2. If you do not have these
minimum requirements see Before Python 3.5.
If you are using a modern version of Python, version 3.5.1 or higher, writing asynchronous Excel worksheet
functions is as simple as adding the async keyword to your function definition. For earlier versions of Python,
or for PyXLL versions before 4.2, or if you just don’t want to use coroutines, see Before Python 3.5.
The following example shows how the asynchronous http package aiohttp can be used with PyXLL to fetch
stock prices without blocking the Excel’s calculation while it waits for a response
2
from pyxll import xl_func
import aiohttp
import json
endpoint = "https://api.iextrading.com/1.0/"
@xl_func
async def iex_fetch(symbol, key):
"""returns a value for a symbol from iextrading.com"""
url = endpoint + f"stock/{symbol}/batch?types=quote"
async with aiohttp.ClientSession() as session:
async with session.get(url) as response:
assert response.status == 200
data = await response.read()
(continues on next page)
2
Asynchronous functions are only available in Excel 2010. Attempting to use them in an earlier version will result in an error.
3.3. Worksheet Functions 59
PyXLL User Guide, Release 5.3.0
(continued from previous page)
data = json.loads(data)["quote"]
return data.get(key, "#NoData")
The function above is marked async. In Python, as async function like this is called a coroutine. When the
coroutine decorated with xl_func is called from Excel, PyXLL schedules it to run on an asyncio event loop.
The coroutine uses await when calling response.read() which causes it to yield to the asyncio event loop
while waiting for results from the server. This allows other coroutines to continue rather than blocking the event
loop.
Note that if you do not yield to the event loop while waiting for IO or another request to complete, you will be
blocking the event loop and so preventing other coroutines from running.
If you are not already familiar with how the async and await keywords work in Python, we recommend you
read the following sections of the Python documentation:
• Coroutines and Tasks
• asyncio — Asynchronous I/O
Warning: Async functions cannot be automatically resized using the “auto_resize” parameter to xl_func.
If you need to return an array using an async function and have it be resized, it is recommended to return the
array from the async function as an object by specifying object as the return type of your function, and then
use a second non-async function to expand the array.
For example:
@xl_func("var x: object")
async def async_array_function(x):
# do some work that creates an array
return array
@xl_func("object: var", auto_resize=True)
def expand_array(array):
# no need to do anything here, PyXLL will do the conversion
return array
The asyncio Event Loop
Using the asyncio event loop with PyXLL requires a minimum of Python 3.5.1 and PyXLL 4.2. If you do not
have these minimum requirements see Before Python 3.5.
When a coroutine (async function) is called from Excel, it is scheduled on the asyncio event loop. PyXLL starts
this event loop on demand, the first time an asynchronous function is called.
For most cases, PyXLL default asyncio event loop is well suited. However the event loop that PyXLL uses can be
replaced by setting start_event_loop and stop_event_loop in the PYXLL section of the pyxll.cfg file.
See PyXLL Settings for more details.
To schedule tasks on the event loop outside of an asynchronous function, the utility function get_event_loop
can be used. This will create and start the event loop, if it’s not already started, and return it.
By default, the event loop runs on a single background thread. To schedule a function it is therefore recommended
to use loop.call_soon_threadsafe, or loop.create_task to schedule a coroutine.
3.3. Worksheet Functions 60
PyXLL User Guide, Release 5.3.0
Before Python 3.5
Or with Python >= 3.5. . .
Everything in this section still works with Python 3.5 onwards.
If you are using an older version of Python than 3.5.1, of if you have not yet upgraded to PyXLL 4.2 or later, you
can still use asynchronous worksheet functions but you will not be able to use the async keyword to do so.
Asynchronous worksheet functions are declared in the same way as regular worksheet functions by using the
xl_func decorator, but with one difference. To be recognised as an asynchronous worksheet function, one of
the function argument must be of the type async_handle.
The async_handle parameter will be a unique handle for that function call, represented by the class
XLAsyncHandle and it must be used to return the result when it’s ready. A value must be returned to
Excel using xlAsyncReturn or (new in PyXLL 4.2) the methods XLAsyncHandle.set_value and
XLAsyncHandle.set_error. Asynchronous functions themselves should not return a value.
The XLAsyncHandle instance is only valid during the worksheet recalculation cycle in which that the function
was called. If the worksheet calculation is cancelled or interrupted then calling xlAsyncReturn with an expired
handle will fail. For example, when a worksheet calculated (by pressing F9, or in response to a cell being updated
if automatic calculation is enabled) and some asynchronous calculations are invoked, if the user interrupts the
calculation before those asynchronous calculations complete then calling xlAsyncReturn after the worksheet
calculation has stopped will result in a exception being raised.
For long running calculations that need to pass results back to Excel after the sheet recalculation is complete you
should use a Real Time Data function.
Here’s an example of an asynchronous function
2
from pyxll import xl_func, xlAsyncReturn
from threading import Thread
import time
import sys
class MyThread(Thread):
def __init__(self, async_handle, x):
Thread.__init__(self)
self.__async_handle = async_handle
self.__x = x
def run(self):
try:
# here would be your call to a remote server or something like that
time.sleep(5)
xlAsyncReturn(self.__async_handle, self.__x)
except:
self.__async_handle.set_error(
*
sys.exc_info()) # New in PyXLL 4.2
# no return type required as Excel async functions don't return a value
# the excel function will just take x, the async_handle is added automatically by
˓→Excel
@xl_func("async_handle<int> h, int x")
def my_async_function(h, x):
# start the request in another thread (note that starting hundreds of threads
˓→isn't advisable
# and for more complex cases you may wish to use a thread pool or another
˓→strategy)
thread = MyThread(h, x)
thread.start()
(continues on next page)
3.3. Worksheet Functions 61
PyXLL User Guide, Release 5.3.0
(continued from previous page)
# return immediately, the real result will be returned by the thread function
return
The type parameter to async_handle (e.g. async_handle<date>) is optional. When provided, it is used
to convert the value returned via xlAsyncReturn to an Excel value. If omitted, the var type is used.
3.3.8 Handling Errors
• Exceptions raised by a UDF
• Passing Errors as Values
• Retrieving Error Information
Exceptions raised by a UDF
Whenever an unhandled exception is raised in a Python function, PyXLL will write it to the log file and return an
error to Excel.
If no error handler is set, an Excel error code will be returned. The exact error code returned depends on the
exception type as follows:
Excel error Python exception type
#NULL! LookupError
#DIV/0! ZeroDivisionError
#VALUE! ValueError
#REF! ReferenceError
#NAME! NameError
#NUM! ArithmeticError
#NA! RuntimeError
You can customize PyXLL’s error handling with the error-handler setting in the PYXLL section of the pyxll.cfg
config file. You specify the function as a list of dotted references, much as you would for a Python import
statement.
[PYXLL]
;
; Set custom error handler function
;
error_handler = your_module.error_handler_function
The error handler should be a function that takes three arguments: the exception type, the exception value and
traceback of the uncaught exception, e.g.:
def error_handler_function(exc_type, exc_value, exc_traceback):
"""
Convert a Python exception to a string value
of form "<exception_type_name>: <value".
"""
error = "##" + getattr(exc_type, "__name__", "Error")
msg = str(exc_value)
if msg:
error += ": " + msg
return error
3.3. Worksheet Functions 62
PyXLL User Guide, Release 5.3.0
When the error handler is executed, the return value of the error handler becomes the value of the cell from which
it was referenced. See Error Handling for details.
Passing Errors as Values
Sometimes it is useful to be able to pass a cell value from Excel to python (or vice-versa) when the cell value is
actually an error.
1. Any function with return type var (or a type that derives from it) will return an error code to Excel if an
Exception is returned. The exact error code depends on the type of the exception, following the table in the
section above.
This is useful when you want to return an array of data (or other array-like data, e.g. a pandas DataFrame)
and where only some values should be returned as errors. By setting the values that should be errors to
instances of exceptions they will appear in Excel as errors.
2. Alternatively, the special type: float_nan can be used.
float_nan behaves in almost exactly the same way as the normal float type. It can be used as an array type, or
as an element type in a numpy array, e.g. numpy_array<float_nan>. The only difference is that if the Excel
value is an error or a non-numeric type (e.g. an empty cell), the value passed to python will be float(‘nan’)
or #QNAN!, which is equivalent to numpy.nan.
The two different float types exist because sometimes you don’t want your function to be called if there’s an
error with the inputs, but sometimes you do. There is a slight performance penalty for using the float_nan
type when compared to a plain float.
Retrieving Error Information
When a Python function is called from an Excel worksheet, if an uncaught exception is raised PyXLL caches the
exception and traceback as well as logging it to the log file.
The last exception raised while evaluating a cell can be retrieved by calling PyXLL’s get_last_error func-
tion.
get_last_error takes a cell reference and returns the last error for that cell as a tuple of (exception type,
exception value, traceback). The cell reference may either be a XLCell or a COM Range object (the exact type
of which depend on the com_package setting in the config.
The cache used by PyXLL to store thrown exceptions is limited to a maximum size, and so if there are more cells
with errors than the cache size the least recently thrown exceptions are discarded. The cache size may be set via
the error_cache_size setting in the config.
When a cell returns a value and no exception is thrown any previous error is not discarded, because to do so would
add additional performance overhead to every function call.
3.3. Worksheet Functions 63
PyXLL User Guide, Release 5.3.0
from pyxll import xl_func, xl_menu, xl_version, get_last_error
import traceback
@xl_func("xl_cell: string")
def python_error(cell):
"""Call with a cell reference to get the last Python error"""
exc_type, exc_value, exc_traceback = get_last_error(cell)
if exc_type is None:
return "No error"
return "".join(traceback.format_exception_only(exc_type, exc_value))
@xl_menu("Show last error")
def show_last_error():
"""Select a cell and then use this menu item to see the last error"""
selection = xl_app().Selection
exc_type, exc_value, exc_traceback = get_last_error(selection)
if exc_type is None:
xlcAlert("No error found for the selected cell")
return
msg = "".join(traceback.format_exception(exc_type, exc_value, exc_traceback))
if xl_version() < 12:
msg = msg[:254]
xlcAlert(msg)
3.3. Worksheet Functions 64
PyXLL User Guide, Release 5.3.0
3.3.9 Function Documentation
When a python function is exposed to Excel with the xl_func decorator the docstring of that function is visible
in Excel’s function wizard dialog.
Parameter documentation may also be provided help the user know how to call the function. The most convenient
way to add parameter documentation is to add it to the docstring as shown in the following example:
from pyxll import xl_func
@xl_func
def py_round(x, n):
"""
Return a number to a given precision in decimal digits.
:param x: floating point number to round
:param n: number of decimal digits
"""
return round(x, n)
PyXLL automatically detects parameter documentation written in the commonly used Sphinx style shown above.
They will appear in the function wizard as help strings for the parameters when selected. The first line will be
used as the function description.
Parameter documentation may also be added by passing a dictionary of parameter names to help strings to
xl_func as the keyword argument arg_descriptions if it is not desirable to add it to the docstring for any reason.
As you can see, the arguments and documentation you provide are fully integrated with Excel’s function wizard:
3.3. Worksheet Functions 65
PyXLL User Guide, Release 5.3.0
3.3.10 Variable Arguments
In Python it is possible to declare a function that takes a variable number of arguments using the special
*
args
notation. These functions can be exposed to Excel as worksheet functions that also take a variable number of
arguments.
The function shown below uses the first argument as a separator ond returns a string made up of the string values
of all other arguments separated by the separator.
from pyxll import xl_func
@xl_func
def py_join(sep,
*
args):
"""Joins a number of args with a separator"""
return sep.join(map(str, args))
You can also set the type of the args in the function signature. When doing that the type for all of the variable
arguments must be the same. For mixed types, use the var type.
3.3. Worksheet Functions 66
PyXLL User Guide, Release 5.3.0
from pyxll import xl_func
@xl_func("str sep, str
*
args: str")
def py_join(sep,
*
args):
"""Joins a number of args with a separator"""
return sep.join(args)
Unlike Python, Excel has some limits on the number of arguments that can be provided to a function. For practical
purposes the limit is high enough that it is unlikely to be a problem. The absolute limit for the number of arguments
is 255, however the actual limit for a function may be very slightly lower
1
.
3.3.11 Recalculating On Open
It can be useful to have worksheet functions (UDFs) that are automatically called when a workbook is opened.
Typically this is achieved by making the function volatile, but making a function volatile means that it is recalcu-
lated every time Excel calculates and not just when the workbook is opened.
PyXLL functions can be made to automatically recalculate when a workbook is opened by passing
recalc_on_open=True to xl_func.
The recalculating on open feature makes use of Workbook Metadata.
• Use-Cases
• Example
• Default Behaviour
• Disabling Completely
Use-Cases
Use-cases for wanting to recalculate a function when a workbook is opened include:
• Opening a database connection to be used by other functions.
• Loading market data or other data required by the workbook.
• Values that depend on the current date or time, but should not constantly update.
• Functions returning objects, as the object needs to be created in order for the sheet to calculate.
• RTD functions, as they need to be called once to start ticking.
Example
The below function uses the recalc_on_open=True option to tell PyXLL that it should be recalcu-
lated when the saved workbook is opened. For this to work, this function is called from a cell (eg
=recalc_on_open_func()) and then the workbook is saved. The next time that workbook is opened, the
cell containing the function will be marked as dirty and if automatic calculations are enabled it will be recalculated.
from pyxll import xl_func
@xl_func(recalc_on_open=True)
def recalc_on_open_func():
(continues on next page)
1
The technical reason this limit is lower is because when the function is registered with Excel, a string is used to tell Excel all the argument
and return types, as well as any modifiers for things like whether the function is thread safe or not. The total length of this string cannot exceed
255 characters so, even though Excel might be able to handle 255 arguments, it may not be possible to register a function with 255 arguments
because of the length limit on that string.
3.3. Worksheet Functions 67
PyXLL User Guide, Release 5.3.0
(continued from previous page)
print("recalc_on_open_func called!")
return "OK!"
Warning: When a workbook is opened cells in that workbook will be recalculated if the function in that cell
was marked as needing to be recalculated on open at the time the workbook was saved.
Changing the recalc_on_open option for a function after the workbook has been saved will have no effect
until the workbook has been recalculated and saved again.
Similarly, if opening a workbook saved with a version of PyXLL prior to 4.5, the workbook will need to be
recalculated and saved before it will recalculate on being opened.
Default Behaviour
The default behaviour for non-volatile worksheet functions is not to recalculate on open unless the
recalc_on_open option is set in xl_func.
The recalc_on_open feature is especially useful for RTD functions and for functions returning Python objects.
The default behaviour for these two types of functions can be modified such that the recalc_on_open feature
applies by default.
• Real Time Data (RTD) Functions
For RTD functions the option recalc_rtd_on_open can be set in the PYXLL section of the pyxll.cfg
config file. If set, all RTD functions will recalculate on opening unless specifically disabled by setting
recalc_on_open=False in the xl_func decorator.
[PYXLL]
; Enable recalc on open for all RTD functions
recalc_rtd_on_open = 1
• Functions Returning Objects
Similarly, any worksheet function that explicitly returns an object can be set to recalculate on opening
via the config setting recalc_cached_objects_on_open.
To enable recalculating all object functions on open set recalc_cached_objects_on_open to 1.
[PYXLL]
; Eenable recalc on open for all functions returning objects
recalc_cached_objects_on_open = 1
With this setting enabled the following function would be recalculated when a workbook using it was
opened, without needing to explicitly set recalc_on_open=True in xl_func.
from pyxll import xl_func
@xl_func("int x: object")
def create_object(x)
obj = SomeClass(x)
return obj
This can be overridden for individual functions by passing recalc_on_open=False to xl_func.
As with the recalc_on_open setting, these settings only affect what metadata gets saved in the workbook.
Changing the recalc_cached_objects_on_open option after the workbook has been saved will have no
effect until the workbook has been recalculated and saved again.
3.3. Worksheet Functions 68
PyXLL User Guide, Release 5.3.0
Disabling Completely
If you do not want any functions to be recalculated when opening a workbook set disable_recalc_on_open
= 1 in your pyxll.cfg file.
This setting prevents any cells marked by PyXLL as needing to be recalculated from being recalculated, regardless
of what settings were used at the time the file was saved. It does not prevent Excel from calculating other cells
that need recalculating, such as volatile cells.
[PYXLL]
disable_recalc_on_open = 1
3.3.12 Interrupting Functions
Long running functions can cause Excel to become unresponsive and sometimes it’s desirable to allow the user to
interrupt functions before they are complete.
Excel allows the user to signal they want to interrupt any currently running functions by pressing the Esc
key. If a Python function has been registered with allow_abort=True (see xl_func) PyXLL will raise a
KeyboardInterrupt exception if the user presses Esc during execution of the function.
This will usually cause the function to exit, but if the KeyboardInterrupt exception is caught then notrmal
Python exception handling takes place. Also, as it is a Python exception that’s raised, if the Python function is
calling out to something else (e.g. a C extension library) the exception may not be registered until control is
returned to Python.
Enabling allow_abort registers a Python trace function for the duration of the call to the function. This can have
a negative impact on performance and so it may not be suitable for all functions. The Python interpreter calls the
trace function very frequently, and PyXLL checks Excel’s abort status during this trace function. To reduce the
performance overhead of calling this trace function, PyXLL throttles how often it checks Excel’s abort status and
this throttling can be fine-tuned with the config settings abort_throttle_time and abort_throttle_count. See PyXLL
Settings for more details.
The allow_abort feature can be enabled for all functions by setting it in the configuration. This feature should be
used with caution because of the performance implications outlined above.
[PYXLL]
;
; Make all Excel UDFs inherently interruptable
;
allow_abort = 1
It is not enabled by default because of the performance impact, and also because it can interfere with the operation
of some remote debugging tools that use the same Python trace mechanism.
3.4 Real Time Data
• Introduction
• Streaming Data From Python
• Example Usage
• RTD Data Types
• Using the asyncio Event Loop
• Throttle Interval
3.4. Real Time Data 69
PyXLL User Guide, Release 5.3.0
• Starting RTD Functions Automatically
3.4.1 Introduction
Real Time Data (or RTD) is data that updates asynchronously, according to its own schedule rather than just when
it is re-evaluated (as is the case for a regular Excel worksheet function).
Examples of real time data include stock prices and other live market data, server loads or the progress of an
external task.
Real Time Data has been a first-class feature of Excel since Excel 2002. It uses a hybrid push-pull mechanism
where the source of the real time data notifies Excel that new data is available, and then some small time later
Excel queries the real time data source for its current value and updates the value displayed.
3.4.2 Streaming Data From Python
PyXLL provides a convenient and simple way to stream real time data to Excel without the complexity of writing
(and registering) a Real Time Data COM server.
Real Time Data functions are registered in the same way as other worksheet functions using the xl_func deco-
rator. Instead of returning a single fixed value, however, they return an instance of an class derived from RTD.
RTD functions have the return type rtd.
When a function returns a RTD instance PyXLL sets up the real time data subscription in Excel and each time the
value property of the RTD instance is set Excel is notified that new data is ready.
If multiple function calls from different cells return the same instance of an RTD class then those cells are sub-
scribed to the same real time data source, so they will all update whenever the value property is set.
3.4.3 Example Usage
The following example shows a class derived from RTD that periodically updates its value to the current time.
3.4. Real Time Data 70
PyXLL User Guide, Release 5.3.0
It uses a separate thread to set the value property, which notifies Excel that new data is ready.
from pyxll import xl_func, RTD
from datetime import datetime
import threading
import logging
import time
_log = logging.getLogger(__name__)
class CurrentTimeRTD(RTD):
"""CurrentTimeRTD periodically updates its value with the current
date and time. Whenever the value is updated Excel is notified and
when Excel refreshes the new value will be displayed.
"""
def __init__(self, format):
initial_value = datetime.now().strftime(format)
super(CurrentTimeRTD, self).__init__(value=initial_value)
self.__format = format
self.__running = True
self.__thread = threading.Thread(target=self.__thread_func)
self.__thread.start()
def connect(self):
# Called when Excel connects to this RTD instance, which occurs
# shortly after an Excel function has returned an RTD object.
_log.info("CurrentTimeRTD Connected")
def disconnect(self):
# Called when Excel no longer needs the RTD instance. This is
# usually because there are no longer any cells that need it
# or because Excel is shutting down.
self.__running = False
_log.info("CurrentTimeRTD Disconnected")
def __thread_func(self):
while self.__running:
# Setting 'value' on an RTD instance triggers an update in Excel
new_value = datetime.now().strftime(self.__format)
if self.value != new_value:
self.value = new_value
time.sleep(0.5)
In order to access this real time data in Excel all that’s required is a worksheet function that returns an instance of
this CurrentTimeRTD class.
@xl_func("string format: rtd")
def rtd_current_time(format="%Y-%m-%d %H:%M:%S"):
"""Return the current time as 'real time data' that
updates automatically.
:param format: datetime format string
"""
return CurrentTimeRTD(format)
Note that the return type of this function is rtd.
When this function is called from Excel the value displayed will periodically update, even though the function
rtd_current_time isn’t volatile and only gets called once.
3.4. Real Time Data 71
PyXLL User Guide, Release 5.3.0
=rtd_current_time()
3.4.4 RTD Data Types
RTD functions can return all the same data types as normal Worksheet Functions, including array types and cached
Python objects.
By default, the rtd return type will use the same logic as a worksheet function with no return type specified or
the var type.
To specify the return type explicity you have to include it in the function signature as a parameter to the rtd type.
For example, the following is how an RTD function that returns Python objects via the internal object cache would
be declared:
@xl_func("string x: rtd<object>")
def rtd_object_func(x):
# MyRTD sets self.value to a non-trivial Python object
return MyRTD(x)
Although RTD functions can return array types, they cannot be automatically resized and so the array formula
needs to be entered manually using Ctrl+Shift+Enter (see Array Functions).
3.4.5 Using the asyncio Event Loop
Instead of managing your own background threads and thread pools when writing RTD functions, you can use
PyXLL’s asyncio event loop instead (new in PyXLL 4.2 and requires Python 3.5.1 or higher).
This can be useful if you have RTD functions that are waiting on IO a lot of the time. If you can take advantage
of Python’s async and await keywords so as not to block the event loop then making your RTD function run
on the asyncio event loop can make certain things much simpler.
The methods RTD.connect and RTD.disconnect can both be async methods. If they are then PyXLL
will schedule them automatically on it’s asyncio event loop.
The example below shows how using the event loop can eliminate the need for your own thread management.
See The asyncio Event Loop for more details.
from pyxll import RTD, xl_func
import asyncio
class AsyncRTDExample(RTD):
def __init__(self):
super().__init__(value=0)
self.__stopped = False
async def connect(self):
while not self.__stopped:
# Yield to the event loop for 1s
await asyncio.sleep(1)
# Update value (which notifies Excel)
self.value += 1
async def disconnect(self):
self.__stopped = True
@xl_func(": rtd<int>")
(continues on next page)
3.4. Real Time Data 72
PyXLL User Guide, Release 5.3.0
(continued from previous page)
def async_rtd_example():
return AsyncRTDExample()
3.4.6 Throttle Interval
Excel throttles the rate of updates made via RTD functions. Instead of updating every time it is notified of new
data it waits for a period of time and then updates all cells with new data at once.
The default throttle time is 2,000 milliseconds (2 seconds). This means that even if you are setting value on
an RTD instance more frequently you will not see the value in Excel updating more often than once every two
seconds.
The throttle interval can be changed by setting Application.RTD.ThrottleInterval (in milliseconds). Setting the
throttle interval is persistent across Excel sessions (meaning that if you close and restart Excel then the value you
set the interval to will be remembered).
The following code shows how to set the throttle interval in Python.
from pyxll import xl_func, xl_app
@xl_func("int interval: string")
def set_throttle_interval(interval):
xl = xl_app()
xl.RTD.ThrottleInterval = interval
return "OK"
Alternatively it can be set in the registry by modifying the following key. It is a DWORD in milliseconds.
HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options\RTDThrottleInterval
3.4.7 Starting RTD Functions Automatically
When you enter an RTD function in an Excel formula is begins ticking automatically because the function has
been called. When loading a workbook containing RTD functions however, they will not start ticking until the
function is called.
To enable RTD functions to begin ticking as soon as a workbook is opened PyXLL RTD functions can be marked
as needed to be recalculated when the workbook opens by using the Recalculating On Open feature of PyXLL.
To make a function recalculate when the workbook is loaded pass recalc_on_open=True to xl_func. If
applied to an RTD function this will cause the RTD function to start ticking when the workbook is loaded.
You can change the default behaviour so that recalc_on_open is True by default for RTD functions (unless
explicitly marked otherwise) by setting recalc_rtd_on_open = 1, e.g.
[PYXLL]
recalc_rtd_on_open = 1
Warning: The default behaviour for RTD functions has changed between PyXLL 4 and PyXLL 5.
From PyXLL 5 onwards RTD functions will no longer start automatically when a workbook is opened unless
configured as above. This is consistent with other UDFs that are not called automatically when workbooks
open by default.
3.4. Real Time Data 73
PyXLL User Guide, Release 5.3.0
3.5 Cell Formatting
When returning values or arrays from a worksheet function, or when setting values on a sheet using a macro
function, often you will also want to set the formatting of the values in Excel. This can be to make sure a returned
value has the correct date or number format, or styling a whole table.
Standard formatters are provided for common cases, and you can also write your own formatters to achieve the
exact style you need.
3.5.1 Formatting Worksheet Functions
Worksheet functions registered using xl_func can format their results using a Formatter.
To specify what formatter should be used for a function use the formatter kwarg to the xl_func decorator.
For example:
from pyxll import xl_func, Formatter
import datetime as dt
date_formatter = Formatter(number_format="yyyy-mm-dd")
@xl_func(formatter=date_formatter)
def get_date():
return dt.date.today()
When the function is called from Excel, any previous formatting is cleared and the formatter is applied to the cell.
The standard Formatter handles many common formatting requirements and takes the following options:
For-
matter
kwargs
inte-
rior_color
Color value to set the interior color to.
text_color Color value to set the text color to.
bold If True, set the text style to bold.
italic If True, set the text style to italic.
font_size Value to set the font size to.
num-
ber_format
Excel number format to use.
auto_fit Auto-fit to the content of the cells.
May be any of: True (fit column width); False (don’t fit); “columns” (fit column width); “rows”
(fit row width); “both” (fit column and row width);
Color values can be obtained using the static method Formatter.rgb.
More complex formatting can be done using a custom formatter.
The Formatter clears all formatting before applying the new formatting, but you can also control how the
formatting is cleared using a custom formatter.
Note: When formatting is applied to Dynamic Array functions PyXLL will keep track of the current array size
and save it in the Workbook Metadata.
This is so the previous range can be cleared before re-applying formatting. Without doing this the formatting
would remain if the array contracted.
3.5. Cell Formatting 74
PyXLL User Guide, Release 5.3.0
3.5.2 Pandas DataFrame Formatting
Array formulas can also be formatted, and PyXLL provides the DataFrameFormatter class specifically for
functions that return pandas DataFrames.
from pyxll import xl_func, xl_return, Formatter, DataFrameFormatter
import pandas as pd
df_formatter = DataFrameFormatter(
index=Formatter(bold=True, interior_color=Formatter.rgb(0xA9, 0xD0, 0x8E)),
header=Formatter(bold=True, interior_color=Formatter.rgb(0xA9,0xD0,0x8E)),
rows=[
Formatter(interior_color=Formatter.rgb(0xE4, 0xF1, 0xDB)),
Formatter(interior_color=Formatter.rgb(0xF4, 0xF9, 0xF1)),
],
columns={
"C": Formatter(number_format="0.00%")
}
)
@xl_func(formatter=df_formatter, auto_resize=True)
@xl_return("dataframe<index=True>")
def get_dataframe():
df = pd.DataFrame({
"A": [1, 2, 3],
"B": [4, 5, 6],
"C": [0.3, 0.6, 0.9]
})
return df
When the function is called from Excel, any previous formatting is cleared and the formatter is applied to the
range for the DataFrame.
The DataFrameFormatter class handles many common formatting requirements, but more complex format-
ting can be done by a custom formatter.
If the size of the DataFrame changes when inputs change, as long as the formula stays the same the previous range
will be cleared before formatting the new range. This allows the returned range to contract without the formatting
being left behind.
Conditional Formatting
As well as formatting specific rows and columns based on their position in the DataFrame as shown above, it is
also possible to apply formatting that is conditional on the values in the DataFrame.
This is done using the ConditionalFormatter class.
The ConditionalFormatter class is constructed with an expression string and a formatter object. The ex-
pression string is passed to the DataFrame.eval method which returns a Series where that expression evaluates
to True. The formatter will be applied to the rows where that expression is True. The formatting can be further
restricted to only apply to specific columns.
A list of ConditionalFormatter objects can be passed as the conditional_formatters argument to
DataFrameFormatter. The conditional formatters are applied in order after any other formatting has been
applied.
The following example shows how to color rows green where column A is greater than 0 and red where column
A is less than 0.
from pyxll import DataFrameFormatter, ConditionalFormatter, Formatter, xl_func
import pandas as pd
(continues on next page)
3.5. Cell Formatting 75
PyXLL User Guide, Release 5.3.0
(continued from previous page)
green_formatter = Formatter(interior_color=Formatter.rgb(0x00, 0xff, 0x00))
red_formatter = Formatter(interior_color=Formatter.rgb(0xff, 0x00, 0x00))
a_gt_zero = ConditionalFormatter("A > 0", formatter=green_formatter)
b_lt_zero = ConditionalFormatter("A < 0", formatter=red_formatter)
df_formatter = DataFrameFormatter(conditional_formatters=[
a_gt_zero,
b_lt_zero])
@xl_func(": dataframe<index=False>", formatter=df_formatter, auto_resize=True)
def get_dataframe():
df = pd.DataFrame({
"A": [-1, 0, 1],
"B": [1, 2, 3],
"C": [4, 5, 6]
})
return df
To restrict the formatting to certain columns the columns argument to ConditionalFormatter can be
used. This can be a list of column names or a function that takes a DataFrame and returns a list of columns.
For more complex conditional formatting a custom conditional formatter class can be derived from
ConditionalFormatterBase. The method ConditionalFormatterBase.get_formatters
should be implemented to return a DataFrame of Formatter objects where any formatting is to be applied.
The returned DataFrame must have the same index and columns as the DataFrame being formatted.
3.5.3 Formatting in Macros Functions
Formatters can also be used from macro functions, as well as from worksheet functions.
To apply a formatter in a macro function use the formatter option to when setting XLCell.value.
For example, to use the standard DataFrameFormatter when setting a DataFrame to a range from an Excel
macro you would do the following:
from pyxll import xl_macro, xl_app, XLCell, DataFrameFormatter
import pandas as pd
@xl_macro
def set_dataframe():
# Get the current selected cell
xl = xl_app()
selection = xl.Selection
# Get an XLCell instance for the selection
cell = XLCell.from_range(selection)
# Create a DataFrame
df = pd.DataFrame({
"A": [1, 2, 3],
"B": [4, 5, 6],
"C": [0.3, 0.6, 0.9]
})
# Construct the formatter to be applied
formatter = DataFrameFormatter()
# Set the 'value' on the current cell with the formatter
# and using the auto-resize option
(continues on next page)
3.5. Cell Formatting 76
PyXLL User Guide, Release 5.3.0
(continued from previous page)
cell.options(type="dataframe<index=True>",
auto_resize=True,
formatter=formatter).value = df
The same method can be used from a menu function or ribbon action.
3.5.4 Custom Formatters
Although the standard formatters provide basic functionality to handle many common cases, you may want to
apply your own formatting. This can be achieved using a custom formatter derived from Formatter.
For applying basic styles in your own formatter you can use Formatter.apply_style, but for everything
else you can use the Excel Object Model.
With VBA it’s possible to style cells and ranges by changing the background color, adding borders, and changing
the font among other things. In Python it’s no different as the entire Excel Object Model is available to you in
Python, just as it is in VBA.
To write a custom formatter create a class that inherits from Formatter. The methods Formatter.apply,
Formatter.apply_cell and Formatter.clear can be overridden to apply any formatting you require.
For example, if you wanted to apply borders using a formatter you would do the following:
from pyxll import Formatter, xl_func
# Needed to get VBA constants
from win32com.client import constants
class BorderFormatter(Formatter):
def apply(self, cell,
*
args,
**
kwargs):
# get the Excel.Range COM object from the XLCell
xl_range = cell.to_range()
# add a border to each edge
for edge in (constants.xlEdgeLeft,
constants.xlEdgeRight,
constants.xlEdgeTop,
constants.xlEdgeBottom):
border = xl_range.Borders[edge]
border.LineStyle = constants.xlContinuous
border.ColorIndex = 0
border.TintAndShade = 0
border.Weight = constants.xlThin
# call the super class to apply any other styles
super().apply(cell,
*
args,
**
kwargs)
border_formatter = BorderFormatter()
@xl_func(formatter=border_formatter, auto_resize=True)
def func_with_borders():
return [
[1, 2, 3],
[4, 5, 6],
[7, 8, 9]
]
Formatters can be combined so you do not have to implement every combination in a single formatter. For
example, to combine the above formatter with the standard DataFrameFormatter you add them together.
3.5. Cell Formatting 77
PyXLL User Guide, Release 5.3.0
from pyxll import xl_func, DataFrameFormatter
df_formatter = DataFrameFormatter()
add_borders = BorderFormatter()
df_formatter_with_borders = df_formatter + add_borders
@xl_func(formatter=df_formatter_with_borders, auto_resize=True)
@xl_return("dataframe<index=True>")
def get_dataframe():
df = pd.DataFrame({
"A": [1, 2, 3],
"B": [4, 5, 6],
"C": [0.3, 0.6, 0.9]
})
return df
You can use the VBA Macro Recorder to record a VBA Macro to apply any style you want, and then examine the
recorded VBA code to see what you need to do. The recorded VBA code can be transformed into Python code.
For example, the following VBA code was recorded setting the left edge border. From the recorded code we can
see what needs to be done and translate that into the required Python code as demonstrated above.
Sub Macro1()
Range("D4:G8").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Sub
See Python as a VBA Replacement for more information on how to translate VBA code to Python.
Custom formatters applied to Dynamic Array functions make use of Workbook Metadata to keep track of format-
ting applied in order to clear it if the array later contracts.
Warning: Formatting cells in Excel uses an Excel macro. Macros in Excel do not preseve the “UnDo” list,
and so after any formatting has been applied you will not be able to undo your recent actions.
Warning: Formatting is new in PyXLL 4.5.
For prior versions formatting can be applied using the Excel Object Model.
Calls to Excel cannot be made from an xl_func function, but can be scheduled using schedule_call.
3.5. Cell Formatting 78
PyXLL User Guide, Release 5.3.0
3.6 Charts and Plotting
As well as using Excel’s own charting capabilities, PyXLL allows you to use Python’s other plotting libraries
within Excel.
PyXLL has support for the following Python plotting libraries, and can be extended to support other via custom
code.
3.6.1 Matplotlib
To plot a Matplotlib figure in Excel you first create the figure in exactly the same way you would in any Python
script using matplotlib, and then use PyXLL’s plot function to show it in the Excel workbook.
Note: Using matplotlib with PyXLL requires matplotlib to be installed. This can be done using pip install
matplotlib, or conda install matplotlib if you are using Anaconda.
For example, the code below is an Excel worksheet function that generates a matplotlib chart and then displays it
in Excel.
from pyxll import xl_func, plot
import matplotlib
import matplotlib.pyplot as plt
import numpy as np
@xl_func
def simple_plot():
# Data for plotting
t = np.arange(0.0, 2.0, 0.01)
s = 1 + np.sin(2
*
np.pi
*
t)
# Create the figure and plot the data
fig, ax = plt.subplots()
ax.plot(t, s)
ax.set(xlabel='time (s)', ylabel='voltage (mV)',
title='About as simple as it gets, folks')
ax.grid()
# Display the figure in Excel
plot(fig)
Note: There is no need to select a backend using matplotlib.use. PyXLL will select the backend automat-
ically.
When this function is called from Excel the matplotlib figure is drawn below the cell the function was called from.
The plotting code above was taken from the matplotlib examples. You can find many more examples on the
matplotlib website as well as documentation on how to use all of matplotlib’s features.
3.6. Charts and Plotting 79
PyXLL User Guide, Release 5.3.0
pyplot
Pyplot is part of matplotlib and provides a convenient layer for interactive work. If you are more familiar with
pyplot and want to use it with PyXLL then that is no problem!
Instead of calling pyplot.show() to show the current plot, use plot without passing a figure and it will show
the current plot in Excel. After plotting the current pyplot figure is closed.
from pyxll import xl_func, plot
import numpy as np
import matplotlib.pyplot as plt
@xl_func
def pyplot():
# Draw a plot using pyplot
x = np.arange(0, 5, 0.1);
y = np.sin(x)
plt.plot(x, y)
# Display it in Excel using pyxll.plot
plot()
As with the previous example when this function is called from Excel the plot is shown below the calling cell.
3.6. Charts and Plotting 80
PyXLL User Guide, Release 5.3.0
Pandas
If you are familiar with pandas you may know that it provides some convenient plotting capabilities. You can use
these to plot charts in Excel as pandas also uses matplotlib.
The DataFrame.plot plots using matplotlib.pyplot and plots to the current pyplot figure. This can
then be displayed in Excel using plot. When passed no arguments, plot plots the current matplotlib.
pyplot figure and closes it.
from pyxll import xl_func, plot
import pandas as pd
@xl_func
def pandas_plot():
# Create a DataFrame to plot
df = pd.DataFrame({
'name':['john','mary','peter','jeff','bill','lisa','jose'],
'age':[23,78,22,19,45,33,20],
'gender':['M','F','M','M','M','F','M'],
'state':['california','dc','california','dc','california','texas','texas'],
'num_children':[2,0,0,3,2,1,4],
'num_pets':[5,1,0,5,2,2,3]
})
# A simple bar chart, plotted using matplotlib.pyplot
df.plot(kind='bar',x='name',y='age')
# Show the current matplotlib.pyplot figure using pyxll.plot
plot()
As with the previous examples when this function is called from Excel the plot is shown below the calling cell.
3.6. Charts and Plotting 81
PyXLL User Guide, Release 5.3.0
3.6.2 Plotly
To plot a Plotly figure in Excel you first create the figure in exactly the same way you would in any Python script
using plotly, and then use PyXLL’s plot function to show it in the Excel workbook.
When the figure is exported to Excel it first has to be converted to an image. This is done by PyXLL using plotly’s
write_image method. This requires an additional package kaleido to be installed.
To install kaleido use pip install -U kaleido, or conda install -c plotly
python-kaleido if you are using Anaconda.
PyXLL also supports using the legacy orca package, but from plotly 4.9 onwards it is recommended that you use
kaleido.
Note: If you have any problems with exporting plots as SVG images you can tell PyXLL to use the PNG format
instead by passing allow_svg=False to plot.
The code below shows an Excel worksheet function that generates a plotly figure displayed it in Excel.
from pyxll import xl_func, plot
import plotly.express as px
@xl_func
def plotly_plot():
# Get some sample data from plotly.express
df = px.data.gapminder()
# Create a scatter plot figure
fig = px.scatter(df.query("year==2007"),
x="gdpPercap", y="lifeExp",
(continues on next page)
3.6. Charts and Plotting 82
PyXLL User Guide, Release 5.3.0
(continued from previous page)
size="pop", color="continent",
log_x=True, size_max=60)
# Show the figure in Excel using pyxll.plot
plot(fig)
When this function is run in Excel the plot is shown just below the calling cell. The first time you export an image
from plotly it can take a few seconds.
Warning: When exporting a figure to an image plotly launches a kaleido or orca subprocess to do the export.
If you have anti-virus software installed it may warn you about this subprocess being launched.
Note: The plot that you see in Excel is exported as an image so any interactive elements will not be available.
To make a semi-interactive plot you can add arguments to your function to control how the plot is done and when
those arguments are changed the plot will be redrawn.
3.6. Charts and Plotting 83
PyXLL User Guide, Release 5.3.0
3.6.3 Bokeh
To plot a bokeh figure in Excel you first create the figure in exactly the same way you would in any Python script
using bokeh, and then use PyXLL’s plot function to show it in the Excel workbook.
When the figure is exported to Excel it first has to be converted to an image. This is done using Selenium and so
that must be installed before Bokeh can be used with PyXLL.
The easiest way to install Selenium is to use Anaconda and install it using either of the following commands:
conda install selenium geckodriver firefox -c conda-forge
or
conda install selenium python-chromedriver-binary -c conda-forge
If you are not using Anaconda you can use pip install selenium but you will also need to install a suitable
web browser backend. See https://pypi.org/project/selenium/ for additional details about how to install Selenium.
Note: If you have any problems with exporting plots as SVG images you can tell PyXLL to use the PNG format
instead by passing allow_svg=False to plot.
The code below shows an Excel worksheet function that generates a bokeh figure and displays it in Excel.
# Download the bokeh sample data first
import bokeh
bokeh.sampledata.download()
from math import pi
import pandas as pd
from bokeh.plotting import figure, output_file, show
from bokeh.sampledata.stocks import MSFT
@xl_func
def bokeh_plot():
# Get some sample data to plot
df = pd.DataFrame(MSFT)[:50]
df["date"] = pd.to_datetime(df["date"])
# Select dates based on open <> close
inc = df.close > df.open
dec = df.open > df.close
w = 12
*
60
*
60
*
1000 # half day in ms
# Set up the figure
p = figure(x_axis_type="datetime", plot_width=1000, title="MSFT Candlestick")
p.xaxis.major_label_orientation = pi/4
p.grid.grid_line_alpha = 0.3
# Plot lines for high/low and vbars for open/close
p.segment(df.date, df.high, df.date, df.low, color="black")
p.vbar(df.date[inc], w, df.open[inc], df.close[inc], fill_color="#D5E1DD",
˓→line_color="black")
p.vbar(df.date[dec], w, df.open[dec], df.close[dec], fill_color="#F2583E",
˓→line_color="black")
# Show the plot in Excel using pyxll.plot
plot(p)
When this function is run in Excel the plot is shown just below the calling cell. The first time you export an image
from bokeh it can take a few seconds.
3.6. Charts and Plotting 84
PyXLL User Guide, Release 5.3.0
Warning: When exporting a figure to an image bokeh launches a Selenium subprocess to do the export.
If you have anti-virus software installed it may warn you about this subprocess being launched.
Note: The plot that you see in Excel is exported as an image so any interactive elements will not be available.
To make a semi-interactive plot you can add arguments to your function to control how the plot is done and when
those arguments are changed the plot will be redrawn.
3.6.4 Altair
To plot a altair figure in Excel you first create the figure in exactly the same way you would in any Python script
using altair, and then use PyXLL’s plot function to show it in the Excel workbook.
When the figure is exported to Excel it first has to be converted to an image. This is done using altair_saver which
also requires Selenium. Both of these must be installed before Altair can be used with PyXLL.
• altair_saver can be installed using pip install altair_saver or conda install -c
conda-forge altair_saver.
• The easiest way to install Selenium is to use Anaconda and install it using either of the following commands:
conda install selenium geckodriver firefox -c conda-forge
or
3.6. Charts and Plotting 85
PyXLL User Guide, Release 5.3.0
conda install selenium python-chromedriver-binary -c conda-forge
If you are not using Anaconda you can use pip install selenium but you will also need to install
a suitable web browser backend. See https://pypi.org/project/selenium/ for additional details about how to
install Selenium.
Note: If you have any problems with exporting plots as SVG images you can tell PyXLL to use the PNG format
instead by passing allow_svg=False to plot.
The code below shows an Excel worksheet function that generates a altair figure and displays it in Excel.
# This example requies vega_datasets.
# Install using 'pip install vega_datasets'
from vega_datasets import data
from pyxll import xl_func, plot
import altair as alt
@xl_func
def altair_plot():
# Get the sample data set
source = data.cars()
# Create the chart
chart = alt.Chart(source).mark_circle(size=60).encode(
x='Horsepower',
y='Miles_per_Gallon',
color='Origin'
)
# Show it in Excel using pyxll.plot
plot(chart)
When this function is run in Excel the plot is shown just below the calling cell. The first time you export an image
from altair it can take a few seconds.
Warning: When exporting a chart to an image altair launches a Selenium subprocess to do the export.
If you have anti-virus software installed it may warn you about this subprocess being launched.
Note: The plot that you see in Excel is exported as an image so any interactive elements will not be available.
To make a semi-interactive plot you can add arguments to your function to control how the plot is done and when
those arguments are changed the plot will be redrawn.
3.6.5 Other Plotting Packages
PyXLL provides support for matplotlib (including pyplot and pandas), plotly, bokeh and altair.
If you want to use another Python plotting package that’s not already supported then you can. To do so you need
to provide you own implementation of PyXLL’s PlotBridgeBase class.
The Plot Bridge is what PyXLL uses to export the chart or figure to an image, and so long as the plotting library
you want to use can export to SVG or PNG format you can write a plot bridge class to use it in PyXLL.
See the API reference for PlotBridgeBase for details of the methods you need to implement.
3.6. Charts and Plotting 86
PyXLL User Guide, Release 5.3.0
3.6. Charts and Plotting 87
PyXLL User Guide, Release 5.3.0
Once you have implemented your Plot Bridge you pass it to plot as the bridge_cls keyword argument.
Whatever object you pass as the figure to plot will be used to construct your Plot Bridge object, which will be
used to export the figure to an image. PyXLL will take care of the rest of inserting or updating that image in Excel.
Using Python’s plotting packages is preferable to using Excel’s own charts in some situations.
• You can plot directly from Python and so this can reduce the need to return a lot of data to Excel and make
your sheets smaller and simpler.
• Using the Python plotting libraries gives you more control over how your charts appear and gives you access
to chart types that are not available using Excel’s own chart types.
Fig. 1: Matplotlib charts in Excel
To show a plot or chart in Excel you use whichever Python plotting library you prefer to generate the chart and then
use PyXLL’s plot function to render it to Excel. See the individual guides linked above for specific instructions
for each.
Regardless of which plotting library you use the plot itself will be inserted into Excel as an image. This means
that it will not be interactive in the way that one hosted on a website or in a Jupyter notebook might be.
You can plot directly from an Excel worksheet function decorated with xl_func, and so you can provide your
own inputs to your plotting function. These can be used to let the user of your function have some control over
how the chart is plotted to make it interactive. Each time they change an input the plot will be re-drawn.
Note: Depending on the version of Excel you are using and the plotting library, the chart may be exported as an
SVG image when plotting to Excel.
Some plotting libraries can occasionally show problems when plotting to SVG. If you see any visual errors (for
example, borders being too thick or the background color showing through) you can set allow_svg=False
when calling plot. This will cause it to export the image in a bitmap format instead.
3.6. Charts and Plotting 88
PyXLL User Guide, Release 5.3.0
3.6.6 Plotting from Worksheet Functions
When you use plot from an Excel worksheet function using xl_func the image inserted into the Excel work-
book will be placed just below the cell the function is being called from.
Additionally, subsequent calls to the same function will replace the image rather than creating a new one each
time the function is called. This is done by giving the image a unique name for the calling cell. If you perform
multiple plots from the same function you will need to pass a name for each to the plot function.
3.6.7 Plotting from Menus, Macros and Elsewhere
The plot function when called from anywhere other than a worksheet function will always add a new image to
the Excel workbook. By default, the location of the image will be just underneath the currently selected cell.
If you want to replace an existing image rather than add a new one, use the name argument to plot and when
plotting an image with the same name multiple times the existing image in Excel will be replaced instead of
creating a new one.
3.7 Custom User Interfaces
PyXLL enables you to integrate sophisticated user interfaces directly into Excel.
Python UI controls can be embedded into Excel Custom Task Panes so they seamlessly fit in with the rest of the
Excel user interface.
PyXLL has support for the following Python UI tookits.
3.7.1 PySide and PyQt
PySide and PyQt are both Python packages wrapping the popular Qt UI toolkit. They are quite similar but have
different licenses and so which one you choose will be down to your own preference. Both work equally well with
PyXLL.
This document is not a guide to use PySide or PyQt. It is only intended to instruct you on how to use PySide and
PyQt with the Custom Task Pane feature of PyXLL. You should refer to the relevant package documentation for
details of how to use each package.
Both PySide and PyQt can be installed using pip or conda, for example:
> pip install pyside2
# or
> pip install pyqt5
# or
> conda install pyside2
# or
> conda install "pyqt>=5"
Typically you will only want to install one or the other, and you should install it using pip or conda and not both.
You can find more information about PySide and PyQt on the websites, https://wiki.qt.io/Qt_for_Python and
https://www.riverbankcomputing.com/software/pyqt/ respectively.
Note: Qt6 support was added in PyXLL 5.1, for both PySide6 and PyQt6.
Any of PySide2, PySide6, PyQt5 and PyQt6 can be used with PyXLL.
3.7. Custom User Interfaces 89
PyXLL User Guide, Release 5.3.0
Creating a Qt Widget
One of the main classes in Qt5 is the QWidget class. To create your own user interface it is this QWidget class
that you will use, and it’s what PyXLL will embed into Excel as a Custom Task Pane.
The following code demonstrates how to create simple Qt widget. If you run this code as a Python script then you
will see the widget being shown.
from PySide2 import QtWidgets
# or from PyQt5 import QtWidgets
import sys
class ExampleWidget(QtWidgets.QWidget):
def __init__(self):
super().__init__()
self.initUI()
def initUI(self):
"""Initialize the layout and child controls for this widget."""
# Give the widget a title
self.setWindowTitle("Example Qt Widget")
# Create a "Layout" object to help layout the child controls.
# A QVBoxLayout lays out controls vertically.
vbox = QtWidgets.QVBoxLayout(self)
# Create a QLineEdit control and add it to the layout
self.line_edit = QtWidgets.QLineEdit(self)
vbox.addWidget(self.line_edit)
# Create a QLabel control and add it to the layout
self.label = QtWidgets.QLabel(self)
vbox.addWidget(self.label)
# Connect the 'textChanged' event to our 'onChanged' method
self.line_edit.textChanged.connect(self.onChanged)
# Set the layout for this widget
self.setLayout(vbox)
def onChanged(self, text):
"""Called when the QLineEdit's text is changed"""
# Set the text from the QLineEdit control onto the label control
self.label.setText(text)
self.label.adjustSize()
if __name__ == "__main__":
# Create the Qt Application
app = QtWidgets.QApplication(sys.argv)
# Create our example widget and show it
widget = ExampleWidget()
widget.show()
# Run the Qt app
sys.exit(app.exec_())
When you run this code you will see our example widget being display, and as you enter text into the line edit
control the label below will be updated.
Next we’ll see how we can use this widget in Excel.
3.7. Custom User Interfaces 90
PyXLL User Guide, Release 5.3.0
Creating a Custom Task Pane from a Qt Widget
To show a QWidget in Excel using PyXLL we use the create_ctp function.
As above, before we can create the widget we have to make sure the QApplication has been initialized. Unlike the
above script, our function may be called many times and so we don’t want to create a new application each time
and so we check to see if one already exists.
The QApplication object must still exist when we call create_ctp. If it has gone out of scope and been released
then it will cause problems later so always make sure to keep a reference to it.
We can create the Custom Task Pane from many different places, but usually it will be from a ribbon function or
a menu function.
The following code shows how we would create a custom task pane from an Excel menu function, using the
ExampleWidget control from the example above.
from pyxll import xl_menu, create_ctp, CTPDockPositionFloating
from PySide2 import QtWidgets
# or from PyQt5 import QtWidgets
@xl_menu("Example Qt CTP")
def example_qt_ctp():
# Before we can create a Qt widget the Qt App must have been initialized.
# Make sure we keep a reference to this until create_ctp is called.
app = QtWidgets.QApplication.instance()
if app is None:
app = QtWidgets.QApplication([])
# Create our example Qt widget from the code above
widget = ExampleWidget()
# Use PyXLL's 'create_ctp' function to create the custom task pane.
# The width, height and position arguments are optional, but for this
# example we'll create the CTP as a floating widget rather than the
# default of having it docked to the right.
create_ctp(widget,
width=400,
height=400,
position=CTPDockPositionFloating)
When we add this code to PyXLL and reload the new menu function “Example Qt CTP” will be available, and
when that menu function is run the ExampleWidget is opened as a Custom Task Pane in Excel.
3.7. Custom User Interfaces 91
PyXLL User Guide, Release 5.3.0
Unlike a modal dialog, a Custom Task Pane does not block Excel from functioning. It can be moved and resized,
and even docked into the current Excel window in exactly the same way as the native Excel tools.
See the API reference for create_ctp for more details.
3.7.2 wxPython
wxPython is a Python packages that wraps the UI toolkit wxWindows.
This document is not a guide to use wxPython or wxWindows. It is only intended to instruct you on how to use
wxPython with the Custom Task Pane feature of PyXLL. You should refer to the relevant package documentation
for details of how to use wxPython and wxWindows.
Both wxWindows can be installed using pip or conda, for example:
> pip install wxpython
# or
> conda install wxpython
You should install it using pip or conda and not both.
You can find more information about wxPython on the website https://www.wxpython.org/.
3.7. Custom User Interfaces 92
PyXLL User Guide, Release 5.3.0
Creating a wx Frame
Two of the main classes we’ll use in wxPython are the wx.Frame and wx.Panel classes.
A wx.Frame is the main window type, and it’s this that you’ll create to contain your user interface that will be
embedded into Excel as a Custom Task Panel. Frames typically host a single wx.Panel which is where all the
controls that make up your user interface will be placed.
The following code demonstrates how to create simple wx.Frame and corresponding wx.Panel. If you run this
code as a Python script then you will see the frame being shown.
import wx
class ExamplePanel(wx.Panel):
def __init__(self, parent):
super().__init__(parent=parent)
# Create a sizer that will lay everything out in the panel.
# A BoxSizer can arrange controls horizontally or vertically.
sizer = wx.BoxSizer(orient=wx.VERTICAL)
# Create a TextCtrl control and add it to the layout
self.text_ctrl = wx.TextCtrl(self)
sizer.Add(self.text_ctrl)
# Create a StaticText control and add it to the layout
self.static_text = wx.StaticText(self)
sizer.Add(self.static_text)
# Connect the 'EVT_TEXT' event to our 'onText' method
self.text_ctrl.Bind(wx.EVT_TEXT, self.onText)
# Set the sizer for this panel and layout the controls
self.SetSizer(sizer)
self.Layout()
def onText(self, event):
"""Called when the TextCtrl's text is changed"""
# Set the text from the event onto the static_text control
text = event.GetString()
self.static_text.SetLabel(text)
class ExampleFrame(wx.Frame):
def __init__(self):
super().__init__(parent=None)
# Give this frame a title
self.SetTitle("Wx Example")
# Create the panel that contains the controls for this frame
self.panel = ExamplePanel(parent=self)
if __name__ == "__main__":
# Create the wx Application object
app = wx.App()
# Construct our example Frame and show it
frame = ExampleFrame()
frame.Show()
(continues on next page)
3.7. Custom User Interfaces 93
PyXLL User Guide, Release 5.3.0
(continued from previous page)
# Run the application's main event loop
app.MainLoop()
When you run this code you will see our example frame being display, and as you enter text into the text control
the static text below will be updated.
Next we’ll see how we can use this frame in Excel.
Creating a Custom Task Pane from a wx.Frame
To show a wx.Frame in Excel using PyXLL we use the create_ctp function.
As above, before we can create the frame we have to make sure the wx.App application object has been initialized.
Unlike the above script, our function may be called many times and so we don’t want to create a new application
each time and so we check to see if one already exists.
The wx.App object must still exist when we call create_ctp. If it has gone out of scope and been released then
it will cause problems later so always make sure to keep a reference to it.
We can create the Custom Task Pane from many different places, but usually it will be from a ribbon function or
a menu function.
The following code shows how we would create a custom task pane from an Excel menu function, using the
ExampleFrame control from the example above.
from pyxll import xl_menu, create_ctp, CTPDockPositionFloating
import wx
@xl_menu("Example wx CTP")
def example_wx_ctp():
# Before we can create a wx.Frame the wx.App must have been initialized.
# Make sure we keep a reference to this until create_ctp is called.
app = wx.App.Get()
if app is None:
app = wx.App()
# Create our example frame from the code above
frame = ExampleFrame()
# Use PyXLL's 'create_ctp' function to create the custom task pane.
(continues on next page)
3.7. Custom User Interfaces 94
PyXLL User Guide, Release 5.3.0
(continued from previous page)
# The width, height and position arguments are optional, but for this
# example we'll create the CTP as a floating window rather than the
# default of having it docked to the right.
create_ctp(frame,
width=400,
height=400,
position=CTPDockPositionFloating)
When we add this code to PyXLL and reload the new menu function “Example wx CTP” will be available, and
when that menu function is run the ExampleFrame is opened as a Custom Task Pane in Excel.
Unlike a modal dialog, a Custom Task Pane does not block Excel from functioning. It can be moved and resized,
and even docked into the current Excel window in exactly the same way as the native Excel tools.
See the API reference for create_ctp for more details.
3.7.3 Tkinter
tkinter is a Python packages that wraps the Tk GUI toolkit.
tkinter is included with Python and so is available to use without needing to install any additional packages.
This document is not a guide to use tkinter. It is only intended to instruct you on how to use Tkinter with the
Custom Task Pane feature of PyXLL. You should refer to the tkinter documentation for details of how to use
tkinter.
You can find more information about tkinter in the Python docs website https://docs.python.org/3/library/tkinter.
html.
3.7. Custom User Interfaces 95
PyXLL User Guide, Release 5.3.0
Creating a tk Frame
One of the main classes in tkinter is the Frame class. To create your own user interface it is this Frame class that
you will use, and it’s what PyXLL will embed into Excel as a Custom Task Pane.
The following code demonstrates how to create simple tkinter.Frame. If you run this code as a Python script
then you will see the frame being shown.
import tkinter as tk
class ExampleFrame(tk.Frame):
def __init__(self, master):
super().__init__(master)
self.initUI()
def initUI(self):
# allow the widget to take the full space of the root window
self.pack(fill=tk.BOTH, expand=True)
# Create a tk.Entry control and place it using the 'grid' method
self.entry_value = tk.StringVar()
self.entry = tk.Entry(self, textvar=self.entry_value)
self.entry.grid(column=0, row=0, padx=10, pady=10, sticky="ew")
# Create a tk.Label control and place it using the 'grid' method
self.label_value = tk.StringVar()
self.label = tk.Label(self, textvar=self.label_value)
self.label.grid(column=0, row=1, padx=10, pady=10, sticky="w")
# Bind write events on the 'entry_value' to our 'onWrite' method
self.entry_value.trace("w", self.onWrite)
# Allow the first column in the grid to stretch horizontally
self.columnconfigure(0, weight=1)
def onWrite(self,
*
args):
"""Called when the tk.Entry's text is changed"""
# Update the label's value to be the same as the entry value
self.label_value.set(self.entry_value.get())
if __name__ == "__main__":
# Create the root Tk object
root = tk.Tk()
# Give the root window a title
root.title("Tk Example")
# Construct our frame object
ExampleFrame(master=root)
# Run the tk main loop
root.mainloop()
When you run this code you will see our example frame being display, and as you enter text into the text entry
control the static text label below will be updated.
Next we’ll see how we can use this frame in Excel.
3.7. Custom User Interfaces 96
PyXLL User Guide, Release 5.3.0
Creating a Custom Task Pane from a tkinter.Frame
To show a tkinter.Frame in Excel using PyXLL we use the create_ctp function.
As above, before we can create the frame we have to create a root object to add it to. Unlike the above script,
our function may be called many times and so we don’t want to use the tk.Tk root object. Instead we use a
tk.Toplevel object.
We can create the Custom Task Pane from many different places, but usually it will be from a ribbon function or
a menu function.
The following code shows how we would create a custom task pane from an Excel menu function, using the
ExampleFrame control from the example above.
from pyxll import xl_menu, create_ctp, CTPDockPositionFloating
import tkinter as tk
@xl_menu("Example Tk CTP")
def example_tk_ctp():
# Create the top level Tk window and give it a title
window = tk.Toplevel()
window.title("Tk Example")
# Create our example frame from the code above and add
# it to the top level window.
frame = ExampleFrame(master=window)
# Use PyXLL's 'create_ctp' function to create the custom task pane.
# The width, height and position arguments are optional, but for this
# example we'll create the CTP as a floating window rather than the
# default of having it docked to the right.
create_ctp(window,
width=400,
height=400,
position=CTPDockPositionFloating)
When we add this code to PyXLL and reload the new menu function “Example Tk CTP” will be available, and
when that menu function is run the ExampleFrame is opened as a Custom Task Pane in Excel.
Unlike a modal dialog, a Custom Task Pane does not block Excel from functioning. It can be moved and resized,
and even docked into the current Excel window in exactly the same way as the native Excel tools.
See the API reference for create_ctp for more details.
3.7. Custom User Interfaces 97
PyXLL User Guide, Release 5.3.0
3.7.4 Other UI Toolkits
PyXLL provides support for PySide2 and PyQt5, wxPython, and Tkinter.
If you want to use another Python UI toolkit that’s not already supported then you still may be able to. To do so
you need to provide you own implementation of PyXLL’s CTPBridgeBase class.
The CTP Bridge is what PyXLL uses to manage getting certain properties of the Python UI toolkit’s window or
frame objects in a consistent way and passing events from Excel to Python.
See the API reference for CTPBridgeBase for details of the methods you need to implement.
Once you have implemented your CTP Bridge you pass it to create_ctp as the bridge_cls keyword argu-
ment. Whatever object you pass as the widget to create_ctp will be used to construct your CTP Bridge object.
PyXLL will take care of the rest of embedding your widget into Excel.
Warning: Writing a CTP Bridge requires detailed knowledge of the UI toolkit you are working with.
This is an expert topic and PyXLL can only offer support limited to the functionality of PyXLL and not third
party packages.
Custom Task Panes (CTPs) are created using a control or widget from any of the supported Python UI toolkits by
calling the PyXLL function create_ctp. All CTPs can be docked into the main Excel window and the initial
position and size can be set when calling create_ctp.
For specific details of creating a custom task pane with any of the supported Python UI toolkits see the links above.
Examples are provided in the examples/custom_task_panes folder in the PyXLL download.
3.7. Custom User Interfaces 98
PyXLL User Guide, Release 5.3.0
Fig. 2: A Python user interface in Excel
3.8 Customizing the Ribbon
• Introduction
• Creating a Custom Tab
• Action Functions
• Using Images
• Modifying the Ribbon
• Merging Ribbon Files
3.8.1 Introduction
The Excel Ribbon interface can be customized using PyXLL. This enables you to add features to Excel in Python
that are properly integrated with Excel for an intuitive user experience.
The ribbon customization is defined using an XML file, referenced in the config with the ribbon setting. This can
be set to a filename relative to the config file, or as as absolute path. If multiple files are listed they will all be read
and merged.
The ribbon XML file uses the standard Microsoft CustomUI schema. This is the same schema you would use
if you were customizing the ribbon using COM, VBA or VSTO and there are various online resources from
Microsoft that document it
1
.
1
Microsoft Ribbon Resources
• Ribbon XML
3.8. Customizing the Ribbon 99
PyXLL User Guide, Release 5.3.0
Actions referred to in the ribbon XML file are resolved to Python functions. The full path to the function must be
included (e.g. “module.function”) and the module must be on the python path so it can be imported. Often it’s
useful to include the modules used by the ribbon in the modules list in the config so that when PyXLL is reloaded
those modules are also reloaded, but that is not strictly necessary.
3.8.2 Creating a Custom Tab
• Create a new ribbon xml file. The one below contains a single tab Custom Tab and a single button.
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="CustomTab" label="Custom Tab">
<group id="ContentGroup" label="Content">
<button id="textButton" label="Text Button"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
• Set ribbon in the config file to the filename of the newly created ribbon XML file.
[PYXLL]
ribbon = <full path to xml file>
• Start Excel (or reload PyXLL if Excel is already started).
The tab appears in the ribbon with a single text button as specified in the XML file. Clicking on the button doesn’t
do anything yet.
• Walkthrough: Creating a Custom Tab by Using Ribbon XML
• XML Schema Reference
3.8. Customizing the Ribbon 100
PyXLL User Guide, Release 5.3.0
3.8.3 Action Functions
Anywhere a callback method is expected in the ribbon XML you can use the name of a Python function.
Many of the controls used in the ribbon have an onAction attribute. This should be set to the name of a Python
function that will handle the action.
• To add an action handler to the example above first modify the XML file to add the onAction attribute to the
text button
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="CustomTab" label="Custom Tab">
<group id="ContentGroup" label="Content">
<button id="textButton" label="Text Button"
onAction="ribbon_functions.on_text_button"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
• Create the ribbon_functions module with the filename ribbon_functions.py and add the on_text_button func-
tion
2
. Note that the module name isn’t important, only that it matches the one used in the xml file.
from pyxll import xl_app
def on_text_button(control):
xl = xl_app()
xl.Selection.Value = "This text was added by the Ribbon."
• Add the module to the pyxll config
3
.
[PYXLL]
modules = ribbon_functions
• Reload PyXLL. The custom tab looks the same but now clicking on the text button calls the Python function.
3.8.4 Using Images
Some controls can use an image to give the ribbon whatever look you like. These controls have an image attribute
and a getImage attribute.
The image attribute is set to the filename of an image you want to load. The getImage attribute is a function that
will return a COM object that implements the IPicture interface.
PyXLL provides a function, load_image, that loads an image from disk and returns a COM Picture object. This
can be used instead of having to do any COM programming in Python to load images.
When images are referenced by filename using the image attribute Excel will load them using a basic image
handler. This basic image handler is rather limited and doesn’t handle PNG files with transparency, so it’s recom-
mended to use load_image instead. The image handler can be set as the loadImage attribute on the customUI
element.
The following shows the example above with a new button added and the loadImage handler set.
2
The name of the module and function is unimportant, it just has to match the onAction attribute in the XML and be on the pythonpath so
it can be imported.
3
This isn’t strictly necessary but is helpful as it means the module will be reloaded when PyXLL is reloaded.
3.8. Customizing the Ribbon 101
PyXLL User Guide, Release 5.3.0
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"
loadImage="pyxll.load_image">
<ribbon>
<tabs>
<tab id="CustomTab" label="Custom Tab">
<group id="ContentGroup" label="Content">
<button id="textButton" label="Text Button"
onAction="ribbon_functions.on_text_button"/>
</group>
<group id="Tools" label="Tools">
<button id="Reload"
size="large"
label="Reload PyXLL"
onAction="pyxll.reload"
image="reload.png"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
If using the load_image image loader package resources can also be used as well as filenames. To specify a
package resource use for the format module:resource.
3.8. Customizing the Ribbon 102
PyXLL User Guide, Release 5.3.0
3.8.5 Modifying the Ribbon
Sometimes its convenient to be able to update the ribbon after Excel has started, without having to change the
pyxll.cfg config file.
For example, if your addin is used by multiple users with different roles then one single ribbon may not be
applicable for each user. Or, you may want to allow the user to switch between different ribbons depending on
what they’re working on.
There are some Python functions you can use from your code to update the ribbon:
• get_ribbon_xml
• set_ribbon_xml
• set_ribbon_tab
• remove_ribbon_tab
These functions can be used to completely replace the current ribbon (set_ribbon_xml) or just to add, replace
or remove tabs (set_ribbon_tab, remove_ribbon_tab).
The ribbon can be updated anywhere from Python code running in PyXLL. Typically this would be when Excel
starts up using the xl_on_open and xl_on_reload event handlers, or from an action function from the
current ribbon.
3.8.6 Merging Ribbon Files
If multiple ribbon files are found, either because there are multiple listed using the ribbon setting in the pyxll.cfg
file or because additional ones have been found via some entry points they will be merged automatically.
When merging, any tabs with the same id will be merged into a single tab. Similarly, any groups within those tabs
with the same ids will also be merged. You should be careful to use unique ids for all elements so that they do not
conflict with any other ribbon elements that might get merged.
The order in which tabs, groups and other elements in groups are merged can be influenced by setting the attributes
insertBefore and insertAfter. These attributes are not part of the ribbon schema but PyXLL will use
them when merging the ribbon files. They can be set on tab and group elements, or any child element of a
group element. One or the other may be set, but not both. Use these to have elements inserted before or after
other elements by their ids.
3.9 Context Menu Functions
• Introduction
• Adding a Python Function to the Context Menu
• Creating Sub-Menus
• Dynamic Menus
• References
3.9. Context Menu Functions 103
PyXLL User Guide, Release 5.3.0
3.9.1 Introduction
Context menus are the menus that appear in Excel when your right-click on something, most usually a cell in the
current workbook.
These context menus have become a standard way for users to interact with their spreadsheets and are an efficient
way to get to often used functions.
With PyXLL you can add your own Python functions to the context menus.
The context menu customizations are defined using the same XML file used when customizing the Excel ribbon
(see Customizing the Ribbon). The XML file is referenced in the config with the ribbon setting. This can be set to
a filename relative to the config file, or as an absolute path.
The ribbon XML file uses the standard Microsoft CustomUI schema. This is the same schema you would use
if you were customizing the ribbon using COM, VBA or VSTO and there are various online resources from
Microsoft that document it
1
. For adding context menus, you must use the 2009 version of the schema or later.
Actions referred to in the ribbon XML file are resolved to Python functions. The full path to the function must be
included (e.g. “module.function”) and the module must be on the python path so it can be imported. Often it’s
useful to include the modules used by the ribbon in the modules list in the config so that when PyXLL is reloaded
those modules are also reloaded, but that is not strictly necessary.
3.9.2 Adding a Python Function to the Context Menu
• Create a new ribbon xml file, or add the contextMenus section from below to your existing ribbon xml
file.
Note that you must use the 2009 version of the schema in the customUI element, and the contextMenus
element must be placed after the ribbon element.
<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<!-- The ribbon and context menus can be specified in the same file -->
</ribbon>
<contextMenus>
<contextMenu idMso="ContextMenuCell">
<button id="MyButton" label="Toggle Case Upper/Lower/Proper"
insertBeforeMso="Cut"
onAction="context_menus.toggle_case"
imageMso="HappyFace"/>
</contextMenu>
</contextMenus>
</customUI>
In the xml above, insertBeforeMso is used to insert the menu item before the existing “Cut” menu item. This
may be removed if you want the item placed at the end of the menu. Also, imageMso may be replaced with
image and set to the path of an image file rather than using one of Excel’s built in bitmaps (see load_image).
• If you’ve not already done so, set ribbon in the config file to the filename of the ribbon XML file.
[PYXLL]
ribbon = <full path to xml file>
• Create the context_menus module with the filename context_menus.py and add the toggle_case function.
Note that the module name isn’t important, only that it matches the one referenced in the onAction
handler in the xml file above.
1
XML Schema Reference
3.9. Context Menu Functions 104
PyXLL User Guide, Release 5.3.0
from pyxll import xl_app
def toggle_case(control):
"""Toggle the case of the currently selected cells"""
# get the Excel Application object
xl = xl_app()
# iterate over the currently selected cells
for cell in xl.Selection:
# get the cell value
value = cell.Value
# skip any cells that don't contain text
if not isinstance(value, str):
continue
# toggle between upper, lower and proper case
if value.isupper():
value = value.lower()
elif value.islower():
value = value.title()
else:
value = value.upper()
# set the modified value on the cell
cell.Value = value
• Add the module to the pyxll config
2
.
[PYXLL]
modules = context_menus
• Start Excel (or reload PyXLL if Excel is already started).
If everything has worked, you will now see the “Toggle Case” item in the context menu when you right
click on a cell.
3.9.3 Creating Sub-Menus
Sub-menus can be added to the context menu using the menu tag.
The following adds a sub-menu after the “Toggle Case” button added above.
<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<!-- The ribbon and context menus can be specified in the same file -->
</ribbon>
<contextMenus>
<contextMenu idMso="ContextMenuCell">
<button id="MyButton" label="Toggle Case Upper/Lower/Proper"
insertBeforeMso="Cut"
onAction="context_menus.toggle_case"
imageMso="HappyFace"/>
<menu id="MySubMenu" label="Case Menu" insertBeforeMso="Cut" >
<button id="Menu1Button1" label="Upper Case"
imageMso="U"
onAction="context_menus.toupper"/>
<button id="Menu1Button2" label="Lower Case"
(continues on next page)
2
This isn’t strictly necessary but is helpful as it means the module will be reloaded when PyXLL is reloaded.
3.9. Context Menu Functions 105
PyXLL User Guide, Release 5.3.0
(continued from previous page)
imageMso="L"
onAction="context_menus.tolower"/>
<button id="Menu1Button3" label="Proper Case"
imageMso="P"
onAction="context_menus.toproper"/>
</menu>
</contextMenu>
</contextMenus>
</customUI>
The additional buttons use the following code, which you can copy to your context_menus.py module.:
def tolower(control):
"""Set the currently selected cells to lower case"""
# get the Excel Application object
xl = xl_app()
# iterate over the currently selected cells
for cell in xl.Selection:
# get the cell value
value = cell.Value
# skip any cells that don't contain text
if not isinstance(value, str):
continue
cell.Value = value.lower()
def toupper(control):
"""Set the currently selected cells to upper case"""
# get the Excel Application object
xl = xl_app()
# iterate over the currently selected cells
for cell in xl.Selection:
# get the cell value
value = cell.Value
# skip any cells that don't contain text
if not isinstance(value, str):
continue
cell.Value = value.upper()
def toproper(control):
"""Set the currently selected cells to 'proper' case"""
# get the Excel Application object
xl = xl_app()
# iterate over the currently selected cells
for cell in xl.Selection:
# get the cell value
value = cell.Value
# skip any cells that don't contain text
if not isinstance(value, str):
continue
cell.Value = value.title()
3.9. Context Menu Functions 106
PyXLL User Guide, Release 5.3.0
3.9.4 Dynamic Menus
As well as statically declaring menus as above, you can also generate menus on the fly in your Python code.
A dynamic menu calls a Python function to get a xml fragment that tells Excel how to display the menu. This can
be useful when the items you want to appear in a menu might change.
The following shows how to declare a dynamic menu.
<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<!-- The ribbon and context menus can be specified in the same file -->
</ribbon>
<contextMenus>
<contextMenu idMso="ContextMenuCell">
<dynamicMenu id="MyDynamicMenu"
label= "My Dynamic Menu"
imageMso="ChangeCase"
insertBeforeMso="Cut"
getContent="context_menus.dynamic_menu"/>
</contextMenu>
</contextMenus>
</customUI>
The getContent callback references the dynamic_menu function in the context_menus module.:
def dynamic_menu(control):
"""Return an xml fragment for the dynamic menu"""
xml = """
<menu xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<button id="Menu2Button1" label="Upper Case"
imageMso="U"
onAction="context_menus.toupper"/>
<button id="Menu2Button2" label="Lower Case"
imageMso="L"
onAction="context_menus.tolower"/>
<button id="Menu2Button3" label="Proper Case"
imageMso="P"
onAction="context_menus.toproper"/>
</menu>
"""
return xml
3.9.5 References
• XML Schema Reference
• https://msdn.microsoft.com/en-us/library/dd926324(v=office.12).aspx
• http://interoperability.blob.core.windows.net/files/MS-CUSTOMUI2/{[}MS-CUSTOMUI2{]}-150904.
pdf
3.9. Context Menu Functions 107
PyXLL User Guide, Release 5.3.0
3.10 Macro Functions
• Introduction
• Exposing Functions as Macros
• Keyboard Shortcuts
• Calling Macros From Excel
3.10.1 Introduction
You can write an Excel macro in python to do whatever you would previously have used VBA for. Macros work
in a very similar way to worksheet functions. To register a function as a macro you use the xl_macro decorator.
Macros are useful as they can be called when GUI elements (buttons, checkboxes etc.) fire events. They can also
be called from VBA.
Macro functions can call back into Excel using the Excel COM API (which is identical to the VBA Excel object
model). The function xl_app can be used to get the Excel.Application COM object (using either win32com or
comtypes), which is the COM object corresponding to the Application object in VBA.
See also Python as a VBA Replacement.
3.10.2 Exposing Functions as Macros
Python functions to be exposed as macros are decorated with the xl_macro decorator imported from the pyxll
module.
from pyxll import xl_macro, xl_app, xlcAlert
@xl_macro
def popup_messagebox():
xlcAlert("Hello")
@xl_macro
def set_current_cell(value):
xl = xl_app()
xl.Selection.Value = value
@xl_macro("string n: int")
def py_strlen(n):
return len(x)
3.10.3 Keyboard Shortcuts
You can assign keyboard shortcuts to your macros by using the ‘shortcut’ keyword argument to the xl_macro
decorator, or by setting it in the SHORTCUTS section in the config.
Shortcuts should be one or more modifier key names (Ctrl, Shift or Alt) and a key, separated by the ‘+’ symbol.
For example, ‘Ctrl+Shift+R’.
from pyxll import xl_macro, xl_app
@xl_macro(shortcut="Alt+F3")
def macro_with_shortcut():
xlcAlert("Alt+F3 pressed")
3.10. Macro Functions 108
PyXLL User Guide, Release 5.3.0
If a key combination is already in use by Excel it may not be possible to assign a macro to that combination.
In addition to letter, number and function keys, the following special keys may also be used (these are not case
sensitive and cannot be used without a modifier key):
• Backspace
• Break
• CapsLock
• Clear
• Delete
• Down
• End
• Enter
• Escape
• Home
• Insert
• Left
• NumLock
• PgDn
• PgUp
• Right
• ScrollLock
• Tab
3.10.4 Calling Macros From Excel
Macros defined with PyXLL can be called from Excel the same way as any other Excel macros.
The most usual way is to assign a macro to a control. To do that, first add the Forms toolbox by going to the Tools
Customize menu in Excel and check the Forms checkbox. This will present you with a panel of different controls
which you can add to your worksheet. For the message box example above, add a button and then right click and
select ‘Assign macro. . . ’. Enter the name of your macro, in this case popup_messagebox. Now when you click
that button the macro will be called.
3.10. Macro Functions 109
PyXLL User Guide, Release 5.3.0
Warning: The Assign Macro dialog in Excel will only list macros defined in workbooks. Any macro defined
in Python using xl_macro will not show up in this list. Instead, you must enter the name of your macro
manually and Excel will accept it.
It is also possible to call your macros from VBA. While PyXLL may be used to reduce the need for VBA in your
projects, sometimes it is helpful to be able to call python functions from VBA.
For the py_strlen example above, to call that from VBA you would use the Run VBA function, e.g.
Sub SomeVBASubroutine
x = Run("py_strlen", "my string")
End Sub
3.11 Python as a VBA Replacement
• The Excel Object Model
• Accessing the Excel Object Model in Python
• Differences between VBA and Python
– Case Sensitivity
– Calling Methods
– Named Arguments
– Properties
3.11. Python as a VBA Replacement 110
PyXLL User Guide, Release 5.3.0
– Properties with Arguments
– Implicit Objects and ‘With’
– Indexing Collections
• Enums and Constant Values
• Excel and Threading
• Notes on Debugging
Everything you can write in VBA can be done in Python. This page contains information that will help you
translate your VBA code into Python.
Please note that the Excel Object Model is part of Excel and documented by Microsoft. The classes and methods
from that API used in this documentation are not part of PyXLL, and so please refer to the Excel Object Model
documentation for more details about their use.
See also Macro Functions.
3.11.1 The Excel Object Model
When programming in VBA you interact with the Excel Object Model. For example, when writing
Sub Macro1()
Range("B11:K11").Select
EndSub
what you are doing is constructing a Range object and calling the Select method on it. The Range object is part of
the Excel Object Model.
Most of what people talk about in reference to VBA in Excel is actually the Excel Object Model, rather than
the VBA language itself. Once you understand how to interact with the Excel Object Model from Python then
replacing your VBA code with Python code becomes straightforward.
The Excel Object Model is well documented by Microsoft as part of the Office VBA Reference.
The first hurdle people often face when starting to write Excel macros in Python is finding documentation for the
Excel Python classes. Once you realise that the Object Model is the same across Python and VBA you will see
that the classes documented in the Office VBA Reference are the exact same classes that you use from Python,
and so you can use the same documentation even though the example code may be written in VBA.
3.11.2 Accessing the Excel Object Model in Python
The Excel Object Model is made available to all languages using COM. Python has a couple of packages that
make calling COM interfaces very easy. If you know nothing about COM then there’s no need to worry as you
don’t need to in order to call the Excel COM API from Python.
The top-level object in the Excel Object Model is the Application object. This represents the Excel application,
and all other objects are accessed via this object.
PyXLL provides a helper function, xl_app, for retrieving the Excel Application object. By default, it uses the
Python package win32com, which is part of the pywin32 package
1
.
If you don’t already have the pywin32 package installed you can do so using pip:
pip install pywin32
Or if you are using Anaconda you can use conda:
1
If you prefer to use comtypes instead of win32com you can still use xl_app by passing com_package='comtypes'.
3.11. Python as a VBA Replacement 111
PyXLL User Guide, Release 5.3.0
conda install pywin32
You can use xl_app to access the Excel Application object from an Excel macro. The following example shows
how to re-write the Macro1 VBA code sample from the section above.
Note that in VBA there is an implicit object, which related to where the VBA Sub (macro) was written. Commonly,
VBA code is written directly on a sheet, and the sheet is implied in various calls. In the Macro1 example above,
the Range is actually a method on the sheet that macro was written on. In Python, we need to explicitly get the
current active sheet instead.
from pyxll import xl_macro, xl_app
@xl_macro
def macro1():
xl = xl_app()
# 'xl' is an instance of the Excel.Application object
# Get the current ActiveSheet (same as in VBA)
sheet = xl.ActiveSheet
# Call the 'Range' method on the Sheet
xl_range = sheet.Range('B11:K11')
# Call the 'Select' method on the Range.
# Note the parentheses which are not required in VBA but are in Python.
xl_range.Select()
You can call into Excel using the Excel Object Model from macros and menu functions, and use a sub-set of the
Excel functionality from worksheet functions, where more care must be taken because the functions are called
during Excel’s calculation process.
You can remove these restrictions by calling the PyXLL schedule_call function to schedule a Python function
to be called in a way that lets you use the Excel Object Model safely. For example, it’s not possible to update
worksheet cell values from a worksheet function, but it is possible to schedule a call using schedule_call and
have that call update the worksheet after Excel has finished calculating.
For testing, it can also be helpful to call into Excel from a Python prompt (or a Jupyter notebook). This can also
be done using xl_app, and in that case the first open Excel instance found will be returned.
You might try this using win32com directly rather than xl_app. We do not advise this when calling your
Python code from Excel however, as it may return an Excel instance other than the one you expect.
from win32com.client.gencache import EnsureDispatch
# Get the first open Excel.Application found, or launch a new one
xl = EnsureDispatch('Excel.Application')
3.11.3 Differences between VBA and Python
Case Sensitivity
Python is case sensitive. This means that code fragments like r.Value and r.value are different (note the
capital V in the first case. In VBA they would be treated the same, but in Python you have to pay attention to the
case you use in your code.
If something is not working as expected, check the PyXLL log file. Any uncaught exceptions will be logged
there, and if you have attempted to access a property using the wrong case then you will probably see an
AttributeError exception.
3.11. Python as a VBA Replacement 112
PyXLL User Guide, Release 5.3.0
Calling Methods
In Python, parentheses (()) are always used when calling a method. In VBA, they may be omitted. Neglecting
to add parentheses in Python will result in the method not being called, so it’s important to be aware of which
class attributes are methods (and must therefore be called) and which are properties (whose values are available
by reference).
For example, the method Select on the Range type is a method and so must be called with parentheses in
Python, but in VBA they can be, and usually are, omitted.
' Select is a method and is called without parentheses in VBA
Range("B11:K11").Select
from pyxll import xl_app
xl = xl_app()
# In Python, the parentheses are necessary to call the method
xl.Range('B11:K11').Select()
Keyword arguments may be passed in both VBA and Python, but in Python keyword arguments use = instead of
the := used in VBA.
Accessing properties does not require parentheses, and doing so will give unexpected results! For example, the
range.Value property will return the value of the range. Adding () to it will attempt to call that value, and as
the value will not be callable it will result in an error.
from pyxll import xl_app
xl = xl_app()
# Value is a property and so no parentheses are used
value = xl.Range('B11:K11').Value
Named Arguments
In VBA, named arguments are passed using Name := Value. In Python, the syntax is slightly different and
only the equals sign is used. One other important difference is that VBA is not case-sensitive but Python is. This
applies to argument names as well as method and property names.
In VBA, you might write
Set myRange = Application.InputBox(prompt := "Sample", type := 8)
If you look at the documentation for Application.InputBox you will see that the argument names are cased different
from this, and are actually ‘Prompt’ and ‘Type’. In Python, you can’t get away with getting the case wrong like
you can in VBA.
In Python, this same method would be called as
from pyxll import xl_app
xl = xl_app()
my_range = xl.InputBox(Prompt='Sample', Type=8)
3.11. Python as a VBA Replacement 113
PyXLL User Guide, Release 5.3.0
Properties
Both VBA and Python support properties. Accessing a property from an object is similar in both languages. For
example, to fetch ActiveSheet property from the Application object you would do the following in VBA:
Set mySheet = Application.ActiveSheet
In Python, the syntax used is identical:
from pyxll import xl_app
xl = xl_app()
my_sheet = xl.ActiveSheet
Properties with Arguments
In VBA, the distinction between methods and properties is somewhat blurred as properties in VBA can take
arguments. In Python, a property never takes arguments. To get around this difference, the win32com Excel
classes have Get and Set methods for properties that take arguments, in addition to the property.
The Range.Offset property is an example of a property that takes optional arguments. If called with no arguments
it simply returns the same Range object. To call it with arguments in Python, the GetOffset method must be used
instead of the Offset property.
The following code activates the cell three columns to the right of and three rows down from the active cell on
Sheet1:
Worksheets("Sheet1").Activate
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate
To convert this to Python we must make the following changes:
• Replace the Offset property with the GetOffset method in order to pass the arguemnts.
• Replace rowOffset and columnOffset RowOffset and ColumnOffset as specified in the Range.Offset docu-
mentation.
• Call the Activate method by adding parentheses in both places it’s used.
from pyxll import xl_app
xl = xl_app()
xl.Worksheets('Sheet1').Activate()
xl.ActiveCell.GetOffset(RowOffset=3, ColumnOffset=3).Activate()
Note: You may wonder, what would happen if you were to use the Offset property in Python? As you may by
now expect, it would fail - but not perhaps in the way you might think.
If you were to call xl.ActiveCell.Offset(RowOffset=3, ColumnOffset=3) the the result would
be that the parameter RowOffset is invalid. What’s actually happening is that when xl.ActiveCell.Offset
is evaluated, the Offset property returns a Range equivalent to ActiveCell, and that Range is then called.
Range has a default method. In Python this translates to the Range class being callable, and calling it calls the
default method.
The default method for Range is Item, and so this bit of code is actually equivalent to xl.ActiveCell.
Offset.Item(RowOffset=3, ColumnOffset=3). The Item method doesn’t expect a RowOffset ar-
gument, and so that’s why it fails in this way.
3.11. Python as a VBA Replacement 114
PyXLL User Guide, Release 5.3.0
Implicit Objects and ‘With’
When writing VBA code, the code is usually written ‘on’ an object like a WorkBook or a Sheet. That object is
used implicitly when writing VBA code.
If using a ‘With..End’ statement in VBA, the target of the ‘With’ statement becomes the implicit object.
If a property is not found on the current implicit object (e.g. the one specified in a ‘With..End’ statement) then the
next one is tried (e.g. the Worksheet the Sub routine is associated with). Finally, the Excel Application object is
implicitly used.
In Python there is no implicit object and the object you want to reference must be specified explicitly.
For example, the following VBA code selects a range and alters the column width.
Sub Macro2()
' ActiveSheet is a property of the Application
Set ws = ActiveSheet
With ws
' Range is a method of the Sheet
Set r = Range("A1:B10")
' Call Select on the Range
r.Select
End With
' Selection is a property of the Application
Selection.ColumnWidth = 4
End Sub
To write the same code in Python each object has to be referenced explicitly.
from pyxll import xl_macro, xl_app
@xl_macro
def macro2():
# Get the Excel.Application instance
xl = xl_app()
# Get the active sheet
ws = xl.ActiveSheet
# Get the range from the sheet
r = ws.Range('A1:B10')
# Call Select on the Range
r.Select()
# Change the ColumnWidth property on the selection
xl.Selection.ColumnWidth = 4
3.11. Python as a VBA Replacement 115
PyXLL User Guide, Release 5.3.0
Indexing Collections
VBA uses parentheses (()) for calling methods and for indexing into collections.
In Python, square braces ([]) are used for indexing into collections.
Care should be taken when indexing into Excel collections, as Excel uses an index offset of 1 whereas Python uses
0. This means that to get the first item in a normal Python collection you would use index 0, but when accessing
collections from the Excel Object Model you would use 1.
3.11.4 Enums and Constant Values
When writing VBA enum values are directly accessible in the global scope. For example, you can write
Set cell = Range("A1")
Set cell2 = cell.End(Direction:=xlDown)
In Python, these enum values are available as constants in the win32com.client.constants package. The
code above would be re-written in Python as follows
from pyxll import xl_app
from win32com.client import constants
xl = xl_app()
cell = xl.Range('A1')
cell2 = cell.End(Direction=constants.xlDown)
3.11.5 Excel and Threading
In VBA everything always runs on Excel’s main thread. In Python we have multi-threading support and sometimes
to perform a long running task you may want to run code on a background thread.
The standard Python threading module is a convenient way to run code on a background thread in Python.
However, we have to be careful about how we call back into Excel from a background thread. As VBA has no
ability to use threads the Excel objects are not written in a such a way that they can be used across different threads.
Attempting to do so may result in serious problems and even cause Excel to crash!
In order to be able to work with multiple threads and still call back into Excel PyXLL has the schedule_call
function. This is used to schedule a Python function to run on Excel’s main thread in such a way that the Excel
objects can be used safely. Whenever you are working with threads and need to use the Excel API you should use
schedule_call.
For example, you might use an Excel macro to start a long running task and when that task is complete write the re-
sult back to Excel. Instead of writing the result back to Excel from the background thread, use schedule_call
instead.
from pyxll import xl_macro, xl_app, schedule_call
import threading
@xl_macro
def start_task():
# Here we're being called from a macro on the main thread
# so it's safe to use pyxll.xl_app.
xl = xl_app()
value = float(xl.Selection.Value)
# Use a background thread for a long running task.
# Be careful not to pass any Excel objects to the background thread!
thread = threading.Thread(target=long_running_task, args=(value,))
(continues on next page)
3.11. Python as a VBA Replacement 116
PyXLL User Guide, Release 5.3.0
(continued from previous page)
thread.start()
# This runs on a background thread
def long_running_task(value):
# Do some work that takes some time
result = ...
# We shouldn't write the result back to Excel here as we are on
# a background thread. Instead use pyxll.schedule_call to write
# the result back to Excel.
schedule_call(write_result, result, "A1")
# This is called via pyxll.schedule_call
def write_result(result, address):
# Now we're back on the main thread and it's safe to use pyxll.xl_app
xl = xl_app()
cell = xl.Range(address)
cell.Value = result
3.11.6 Notes on Debugging
The Excel VBA editor has integrating debugging so you can step through the code and see what’s happening at
each stage.
When writing Python code it is sometimes easier to write the code outside of Excel in your Python IDE before
adapting it to be called from Excel as a macro or menu function etc.
When calling your code from Excel, remember that any uncaught exceptions will be printed to the PyXLL log file
and so that should always be the first place you look to find what’s going wrong.
If you find that you need to be able to step through your Python code as it is being executed in Excel you will
need a Python IDE that supports remote debugging. Remote debugging is how debuggers connect to an external
process that they didn’t start themselves.
You can find instructions for debugging Python code running in Excel in this blog post Debugging Your Python
Excel Add-In.
3.12 Using Pandas in Excel
• Pandas Types Options
• Passing as Python objects instead of Excel arrays
• Using the Pandas type converters outside of a UDF
Pandas DataFrames and Series can be used as function arguments and return types for Excel worksheet functions
using the decorator xl_func.
When used as an argument, the range specified in Excel will be converted into a Pandas DataFrame or Series as
specified by the function signature.
When returning a DataFrame or Series, a range of data will be returned to Excel. PyXLL can automatically resize
the range of the array formula to match the returned data by setting auto_resize=True in xl_func.
The following code shows a function that returns a random dataframe, including the index:
3.12. Using Pandas in Excel 117
PyXLL User Guide, Release 5.3.0
from pyxll import xl_func
import pandas as pd
import numpy as np
@xl_func("int rows, int columns: dataframe<index=True>", auto_resize=True)
def random_dataframe(rows, columns):
data = np.random.rand(rows, columns)
column_names = [chr(ord('A') + x) for x in range(columns)]
return pd.DataFrame(data, columns=column_names)
A function can also take a DataFrame or Series as one its arguments. When passing a DataFrame or Series to a
function the whole data area must be selected in Excel and used as the argument to the function.
The following function takes a DataFrame including the column headers row, but not including the index column
and returns the sum of a single column.:
from pyxll import xl_func
@xl_func("dataframe<index=False, columns=True>, str: float")
def sum_column(df, col_name):
col = df[col_name]
return col.sum()
See also Pandas DataFrame Formatting.
3.12.1 Pandas Types Options
The following options are available for the dataframe and series argument and return types:
• dataframe, when used as an argument type
dataframe<index=0, columns=1, dtype=None, dtypes=None,
index_dtype=None, multi_sparse=True>
index Number of columns to use as the DataFrame’s index. Specifying more than one will result in a
DataFrame where the index is a MultiIndex.
columns Number of rows to use as the DataFrame’s columns. Specifying more than one will result in
a DataFrame where the columns is a MultiIndex. If used in conjunction with index then any column
headers on the index columns will be used to name the index.
dtype Datatype for the values in the dataframe. May not be set with dtypes.
dtypes Dictionary of column name -> datatype for the values in the dataframe. May not be set with dtype.
index_dtype Datatype for the values in the dataframe’s index.
multi_sparse
1
Return sparse results for MultiIndexes. Can be set to True or False, or 'index' or
'columns' if it should only apply to one or the other.
• dataframe, when used as a return type
dataframe<index=None, columns=True>
index If True include the index when returning to Excel, if False don’t. If None, only include if the index
is named.
columns If True include the column headers, if False don’t.
• series, when used as an argument type
series<index=1, transpose=None, dtype=None, index_dtype=None,
multi_sparse=True>
index Number of columns (or rows, depending on the orientation of the Series) to use as the Series index.
1
The multi_sparse parameter is new in PyXLL 5.3.0.
3.12. Using Pandas in Excel 118
PyXLL User Guide, Release 5.3.0
transpose Set to True if the Series is arranged horizontally or False if vertically. By default the orienta-
tion will be guessed from the structure of the data.
dtype Datatype for the values in the Series.
index_dtype Datatype for the values in the Series’ index.
multi_sparse
1
Return sparse results for MultiIndexes.
• series, when used as a return type
series<index=True, transpose=False>
index If True include the index when returning to Excel, if False don’t.
transpose Set to True if the Series should be arranged horizontally, or False if vertically.
3.12.2 Passing as Python objects instead of Excel arrays
When passing large DataFrames between Python functions, it is not always necessary to return the full DataFrame
to Excel and it can be expensive reconstructing the DataFrame from the Excel range each time. In those cases you
can use the object return type to return a handle to the Python object. Functions taking the dataframe and
series types can accept object handles.
The following returns a random DataFrame as a Python object, so will appear in Excel as a single cell with a
handle to that object:
from pyxll import xl_func
import pandas as pd
import numpy as np
@xl_func("int rows, int columns: object")
def random_dataframe(rows, columns):
data = np.random.rand(rows, columns)
column_names = [chr(ord('A') + x) for x in range(columns)]
return pd.DataFrame(data, columns=column_names)
The result of a function like this can be passed to another function that expects a DataFrame:
@xl_func("dataframe, int: dataframe<index=True>", auto_resize=True)
def dataframe_head(df, num_rows):
return df.head(num_rows)
This allows for large datasets to be used in Excel efficiently, especially where the data set would be cumbersome
to deal with in Excel when unpacked.
3.12.3 Using the Pandas type converters outside of a UDF
Sometimes it’s useful to be able to convert a range of data into a DataFrame, or a DataFrame into a range of data
for Excel, in a context other than function decorated with xl_func. Or, you might have a function that takes the
var type, which could be a DataFrame depending on other arguments.
In these cases the function get_type_converter can be used. For example:
from pyxll import get_type_converter
to_dataframe = get_type_converter("var", "dataframe<index=True>")
df = to_dataframe(data)
Or the other way:
to_array = get_type_converter("dataframe", "var")
data = to_array(df)
3.12. Using Pandas in Excel 119
PyXLL User Guide, Release 5.3.0
All the parameters for the dataframe and series types can be used to control how the conversion is per-
formed.
3.13 Menu Functions
• Custom Menu Items
• New Menus
• Sub-Menus
3.13.1 Custom Menu Items
The xl_menu decorator is used to expose a python function as a menu callback. PyXLL creates the menu item
for you, and when it’s selected your python function is called. That python function can call back into Excel using
win32com or comtypes to make changes to the current sheet or workbook.
Different menus can be created and you can also create submenus. The order in which the items appear is con-
trolled by optional keyword arguments to the xl_menu decorator.
Here’s a very simple example that displays a message box when the user selects the menu item:
from pyxll import xl_menu, xlcAlert
@xl_menu("Hello!")
def on_hello():
xlcAlert("Hello!")
Menu items may modify the current workbook, or in fact do anything that you can do via the Excel COM API.
This allows you to do anything in Python that you previously would have had to have done in VBA.
Below is an example that uses xl_app to get the Excel Application COM object and modify the current selection.
You will need to have win32com or comtypes installed for this.
from pyxll import xl_menu, xl_app
@xl_menu("win32com menu item")
(continues on next page)
3.13. Menu Functions 120
PyXLL User Guide, Release 5.3.0
(continued from previous page)
def win32com_menu_item():
# get the Excel Application object
xl = xl_app()
# get the current selected range
selection = xl.Selection
# set some text to the selection
selection.Value = "Hello!"
3.13.2 New Menus
As well as adding menu items to the main PyXLL addin menu it’s possible to create entirely new menus.
To create a new menu, use the menu keyword argument to the xl_menu decorator.
In addition, if you want to control the order in which menus are added you may use the menu_order integer
keyword argument. The higher the value, the later in the ordering the menu will be added. The menu order my
also be set in the config (see configuration).
Below is a modification of an earlier menu example that puts the menu item in a new menu, called “New Menu”:
from pyxll import xl_menu, xlcAlert
@xl_menu("My menu item", menu="New Menu")
def my_menu_item():
xlcAlert("new menu example")
3.13. Menu Functions 121
PyXLL User Guide, Release 5.3.0
3.13.3 Sub-Menus
Sub-menus may also be created. To add an item to a sub-menu, use the sub_menu keyword argument to the
xl_menu decorator.
All sub-menu items share the same sub_menu argument. The ordering of the items within the submenu is con-
trolled by the sub_order integer keyword argument. In the case of sub-menus, the order keyword argument
controls the order of the sub-menu within the parent menu. The menu order my also be set in the config (see
configuration).
For example, to add the sub-menu item “TEST” to the sub-menu “Sub Menu” of the main menu “My Menu”, you
would use a decorator as illustrated by the following code:
from pyxll import xl_menu, xlcAlert
@xl_menu("TEST", menu="New Menu", sub_menu="Sub Menu")
def my_submenu_item():
xlcAlert("sub menu example")
3.14 Reloading and Rebinding
• Introduction
• How to Reload PyXLL
– Reload Manually
– Automatic Reloading
– Programmatic Reloading
• Deep Reloading
• Rebinding
3.14. Reloading and Rebinding 122
PyXLL User Guide, Release 5.3.0
3.14.1 Introduction
When writing Python code to be used in Excel, there’s no need to shut down Excel and restart it every time you
make a change to your code.
Instead, you can simply tell PyXLL to reload your Python code so you can test it out immediately.
When reloading, the default behaviour is for PyXLL to only reload the Python modules listed in the modules
list on your pyxll.cfg config file. Optionally, PyXLL can also reload all the modules that those modules depend
on - this is called deep reloading. Deep reloading can take a bit longer than just reloading the modules listed in
the config, but can be helpful when working on larger projects.
There are different options that affect how and when your Python code is reloaded, which are explained in this
document. The different configuration options are also documented in the Configuring PyXLL section of the
documentation.
3.14.2 How to Reload PyXLL
Before you can reload your Python modules with PyXLL, you need to make sure you have developer_mode
enabled in your pyxll.cfg file.
[PYXLL]
developer_mode = 1
This setting enables reloading and adds the “Reload PyXLL” menu item to Excel. It is enabled by default.
Reload Manually
After working on some changes to your code you can tell PyXLL to reload your modules by selecting “Reload
PyXLL” from the PyXLL menu in the Add-Ins tab.
You can also configure the Excel ribbon to have a “Reload” button. This is done for you in the example ribbon.xml
file.
A simple ribbon file with just the “Reload” button would look like this
3.14. Reloading and Rebinding 123
PyXLL User Guide, Release 5.3.0
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="PyXLL" label="PyXLL">
<group id="Tools" label="Tools">
<button id="Reload"
size="large"
label="Reload PyXLL"
onAction="pyxll.reload" />
</group>
</tab>
</ribbon>
</customUI>
Note the “onAction” attribute is set to “pyxll.reload”. This binds that ribbon button to PyXLL’s reload function.
You can read more about configuring the ribbon here.
Automatic Reloading
Rather than have to reload manually every time you make a change to your code, PyXLL can watch and reload
automatically as soon as any of your files are saved.
To enable automatic reloading, set auto_reload = 1 in the [PYXLL] section of your config file.
[PYXLL]
auto_reload = 1
When automatic reloading is enabled, changes to the following files will cause PyXLL to reload:
• Python modules
• PyXLL config files
• Ribbon XML files
Automatic reloading works with deep reloading. If deep reloading is enabled, then any change to a Python
module that be reloaded will cause PyXLL to trigger a reload. If deep reloading is not enabled, then only the
Python modules listed in the PyXLL config will trigger a reload.
Warning: Automatic reloading is only available from PyXLL 4.3 onwards.
Programmatic Reloading
It is possible to reload PyXLL programmatically via the Python function reload or by calling the Excel macro
pyxll_reload.
Calling either the Python function or the Excel macro will cause PyXLL to reload shortly after. The reload does
not happen immediately, but after the current function or macro has completed.
3.14. Reloading and Rebinding 124
PyXLL User Guide, Release 5.3.0
3.14.3 Deep Reloading
The default behaviour when reloading is that only the modules listed in the pyxll.cfg config file are reloaded.
When working on more complex projects it is normal to have Python code organized into packages, and to have
PyXLL functions in many different Python modules. Instead of listing all of them in the config file they can be
imported from a single module.
For example, you might have a directory structure something like the following
my_excel_addin
__init__.py
functions.py
macros.py
And in my_excel_addin/__init__.py you might import functions and macros.
from . import functions
from . import macros
In your pyxll.cfg file, you would only need to list my_excel_addin.
Listing 3: my_excel_addin/__init__.py
[PYXLL]
modules =
my_excel_addin
When you reload PyXLL, only my_excel_addin would be reloaded, and so changes to my_excel_addin.functions
or my_excel_addin.macros or any other imported modules wouldn’t be discovered.
With deep reloading, PyXLL determines the dependencies between your imported modules and reloads all of the
module dependencies, in the correct order.
To enable deep reloading, set deep_reload = 1 in the [PYXLL] section of your config file.
[PYXLL]
deep_reload = 1
Not all modules can be reloaded. Sometimes because of the way some modules are written, they won’t reload
cleanly. Circular dependencies between modules is a common reason for packages to not reload cleanly, and
Python cannot reload C extension modules.
If you are having trouble with a particular package or module not reloading cleanly, you can exclude it from being
reloaded during the deep reload. To do so, list the modules you want excluded in the deep_reload_exclude
list in your PyXLL config file.
As deep reloading can take longer than normal reloading, you can limit what modules and packages are in-
cluded by setting deep_reload_include in your PyXLL config file. In the example above, because
everything we’re interested in is contained in the my_excel_addin package, adding my_excel_addin to the
deep_reload_include list would limit reloading to modules in that package.
Warning: Starting with PyXLL 4.3 onwards, packages in the site-packages folder are no longer included
when deep reloading.
To include modules in site-packages, set deep_reload_include_site_packages = 1 in the
[PYXLL] section of your config file.
3.14. Reloading and Rebinding 125
PyXLL User Guide, Release 5.3.0
3.14.4 Rebinding
As well as reloading, it is also possible to tell PyXLL to re-create its bindings between the imported Python code
and Excel. This is referred to as rebinding.
Rebinding can be useful, for example, when importing modules dynamically and updating the Excel functions
after the import is complete, without reloading.
By default rebinding occurs automatically whenever a new xl_func, xl_macro or xl_menu decorator is
called.
Automatic rebinding can be disabled by setting the following in your pyxll.cfg file:
[PYXLL]
auto_rebind = 0
If automatic rebinding has been disabled you can still tell PyXLL to rebind by calling the rebind function.
For example:
from pyxll import xl_macro, rebind
@xl_macro
def import_new_functions():
"""Import a new module and then call 'rebind' to tell PyXLL to update"""
module = __import__("...")
# Now the module has been imported and declared new UDFs using @xl_func
# tell PyXLL to update it's Excel bindings.
rebind()
PyXLL also declares an Excel macro pyxll_rebind that you can call from VBA to do the same as the Python
rebind function.
3.15 Error Handling
• Introduction
• Standard Error Handlers
• Custom Error Handlers
3.15.1 Introduction
Any time a PyXLL function raises an uncaught Exception, it will be written to the log file as an error.
If you need to figure out what is going wrong, the log file should be your first piece of evidence. The location of
the log file is set in the PyXLL config file, and by default it is in the logs folder alongside the PyXLL add-in.
In addition to the log file, PyXLL provides ways of handling errors to present them to the user directly when they
occur. The full exception and stack trace are always written to the log file, but in many cases providing the user
with some details of the error is sufficient to let them understand the problem without having to resort to the log
file.
For example, if a worksheet function fails Excel’s default behaviour is to show an error like #NA. Consider the
following function:
3.15. Error Handling 126
PyXLL User Guide, Release 5.3.0
@xl_func
def my_udf(x, y):
if not 1 <= x <= 100:
raise ValueError("Expected x to be between 1 and 100")
return do_something(x, y)
If you call this from Excel with x outside of 1 and 100, without an error handler the user will see #VALUE!. They
can look in the log file to see the full error, but an error handler can be used to return something more helpful.
Using the standard error handler pyxll.error_handler ##ValueError: Expected x to be between
1 and 100 would be returned
1
.
The configured error handler will be called for all types of functions when an uncaught Exception is raised, not
simply worksheet functions.
3.15.2 Standard Error Handlers
PyXLL provides two standard error handlers to choose from.
• pyxll.error_handler
• pyxll.quiet_error_handler
These are configured by setting error_handler in the configuration file, e.g.:
[PYXLL]
error_handler = pyxll.error_handler
The following table shows how the two different error handlers behave for the different sources of errors:
Error Source pyxll.error_handler pyxll.quiet_error_handler No Handler
Worksheet Func-
tion
Return error as string Return error as string Nothing (returns #NA!
etc.)
Macro Return error as string Return error as string Nothing (returns #NA!
etc.)
Menu Item Show error in message box Do nothing Do nothing
Ribbon Action Show error in message box Do nothing Do nothing
Module Import Show error in message box Do nothing Do nothing
3.15.3 Custom Error Handlers
For cases where the provided error handling isn’t suitable, you can provide your own error handler.
An error handler is simply a Python function that you reference from your configuration file, including the module
name, for example:
[PYXLL]
error_handler = my_error_handler.error_handler
The error handler takes four
2
arguments, context (ErrorContext), exc_type, exc_value and exc_traceback.
context is a ErrorContext object that contains additional information about the error that has occurred, such
as the type of function that was being called.
The following shows a custom error handler that returns a string if the function type was a worksheet function
(UDF) or macro. For all other types, it calls pyxll.error_handler, delegating error handling to PyXLL’s
standard handler.
1
Sometimes it’s useful to actually return an error code (eg #VALUE!) to Excel. For example, if using the =ISERROR Excel function. In
those cases, you should not set an error handler, or use a custom error handler that returns a Python Exception.
2
Prior to PyXLL 4.3, error handlers only took three arguments and didn’t have the context argument.
PyXLL is backwards compatible with older versions. If you have an old error handler that only takes three arguments, this will be handled
automatically and that error handler will only be called for worksheet functions (UDFs) and macros.
3.15. Error Handling 127
PyXLL User Guide, Release 5.3.0
from pyxll import error_handler as standard_error_handler
def error_handler(context, exc_type, exc_value, exc_traceback):
"""Custom PyXLL error handler"""
# For UDFs return a preview of the error as a single line
if context.error_type in (ErrorContext.Type.UDF, ErrorContext.Type.MACRO):
error = "##" + getattr(exc_type, "__name__", "Error")
msg = str(exc_value)
if msg:
error += ": " + msg
return error
# For all other error types call the standard error handler
return standard_error_handler(context, exc_type, exc_value, exc_traceback)
PyXLL will still log the exception, so there is no need to do that in your handler.
If you want your error handler to return an error code to Excel instead of a string, return the Exception value.
Python Exceptions are converted to Excel errors as per the following table.
Excel error Python Exception type
#NULL! LookupError
#DIV/0! ZeroDivisionError
#VALUE! ValueError
#REF! ReferenceError
#NAME! NameError
#NUM! ArithmeticError
#NA! RuntimeError
3.16 Distributing Python Code
For other users to be able to share your Python code and PyXLL based Excel add-in they will need to have:
1. The PyXLL add-in installed and configured
2. Access to your Python code
3. A Python environment with any dependencies installed
One of the benefits of using PyXLL is that the code is separated from the Excel workbooks so that updates to the
code can be deployed without having to change each workbook that depends on it.
So, the question is, how can the Python code be distributed to each user that needs it, and how can updates be
deployed?
• Sharing Python code on a network drive
• Using a startup script to install and update Python code
• Using a common pyxll.cfg file
• Deploying the Python Environment
• Building an installer
• Setuptools Entry Points
– modules entry point
– ribbon entry point
3.16. Distributing Python Code 128
PyXLL User Guide, Release 5.3.0
3.16.1 Sharing Python code on a network drive
This is a simple solution that allows everyone to read the same Python code at all times. The Python code is copied
to a location on the network drive and PyXLL is configured to load its Python modules from there.
PyXLL can be configured to load Python code from a network drive by changing the pythonpath setting in
pyxll.cfg to the network location. For example, if you were to deploy your Python code to a folder X:\Python\
PyXLL\v1 then you would update your pyxll.cfg file as follows:
Listing 4: my_error_handler.py
[PYTHON]
pythonpath = X:\Python\PyXLL\v1
To deploy changes, rather than updating the code in-place it is better to create another copy of your code in a new
folder. You can then update the pythonpath in a shared pyxll.cfg file (see Using a common pyxll.cfg file) to
point to the new folder. This way if you need to rollback to the previous version you can do, and it avoids any
problems with some files (e.g. .dll and .pyd files) that may be locked while in use by your users.
It is advisable to make the shared folder read-only to prevent any accidental modifications to the code by your end
users. You can pre-compile your .py Python files to .pyc files using python -m compileall before copying your
code to the shared folder.
3.16.2 Using a startup script to install and update Python code
Importing Python code from a network drive can have some disadvantages. It requires a fast network, and even
then it can be slow to import the modules. It may also be against your coroprate IT policy to deploy code via a
network drive because it lacks sufficient control, or it just may not suit your deployment needs.
Using a startup script you can check what version of your Python code is currently deployed and download the
latest if necessary. Once downloaded the code is on the local PC and so importing it will be fast. When updates
are needed the script will detect there’s a newer version of the code available and download it.
Such a script might look something like this:
SET VERSION=v1
SET PYTHON_FOLDER=.\python-code-%VERSION%
REM No need to download anything if we already have the latest
IF EXIST %PYTHON_FOLDER% THEN GOTO END
REM Download and unzip the latest code
wget https://intranet/pyxll/python-code-%VERSION%.tar.gz
tar -xzf python-code-%VERSION%.tar.gz --directory %PYTHON_FOLDER%
ECHO Latest code has been downloaded to .\python-code-%VERSION%
:END
The above script is just an illustration and your script would be different depending on your needs. It could also
be a Powershell script rather than a plain batch script.
To get this script to run when Excel starts we use the startup_script option in the pyxll.cfg file. This is set to the
the path of the script to run, or it can be a URL. By using a URL (or a location on a network drive) whenever we
want to deploy a different version of the code to all of our users we only have to update the version number in the
script.
[PYXLL]
startup_script = https://intranet/pyxll/startup-script.cmd
Now the script runs when Excel starts, but the code downloaded isn’t on our Python Path and so won’t be able to
be imported. Because we’re using a different folder for each version of the code we can’t hard-code the path in
our pyxll.cfg file.
3.16. Distributing Python Code 129
PyXLL User Guide, Release 5.3.0
Within a startup script run by PyXLL you can run various commands, including getting and setting PyXLL options.
There’s a command pyxll-set-option that we can use to set the pythonpath option to the correct folder:
SET VERSION=v1
SET PYTHON_FOLDER=.\python-code-%VERSION%
ECHO pyxll-set-option PYTHON pythonpath %PYTHON_FOLDER%
The pyxll-set-option command is run by echoing it from the batch script. PyXLL sees this in the output
from the script and updates the pythonpath option. Calling pyxll-set-option for a multi-line option like
pythonpath appends to it rather than replacing it.
There are several other commands available from a startup script. See Startup Script for more details.
3.16.3 Using a common pyxll.cfg file
The PyXLL config be shared so that each user gets the same configuration, and so updates to the config can be
made once rather than on each PC. This is done by setting the external_config option in the pyxll.cfg file.
Each user still has their own pyxll.cfg file with any settings specific to them (if any), but they also use the exter-
nal_config option to source in one or more shared configs.
The external config can be a file on a network drive or a URL.
[PYXLL]
external_config = https://intranet/pyxll/pyxll-shared.cfg
If more than one external config is required the external_config setting accepts a list of files and URLs.
If it is not desirable for each user to have their own pyxll.cfg file then the environment variable
PYXLL_CONFIG_FILE can be set to tell PyXLL where to load the config from. This could be a path on a
network drive or a URL.
When using a shared config typically you don’t want the log file to be written to the same place for every user.
You can use environment variables in the config file to avoid this, eg
[LOG]
path = %(USERPROFILE)s/pyxll/logs
See Environment Variables for more details.
3.16.4 Deploying the Python Environment
The Python environment and many of the Python packages your code depends on are likely to change less often
than your main Python code. They do still need to be available to PyXLL for it to work however.
This doesn’t mean that Python actually needs to be installed on the local PC.
PyXLL can be configured to use any Python environment as long as it is accessible by the user. This means
you can take a Python environment and copy it to a network drive and have PyXLL reference it from there. For
example, where below X: is a mapped network drive:
[PYTHON]
executable = X:\PyXLL\Python\pythonw.exe
As long as the Python environment on the network drive is complete, this will work fine.
A very useful tool for creating a Python environment suitable for being relocated to a network drive is conda-pack.
Note, using a venv doesn’t create a complete Python environment and still requires the base Python install and
so cannot be used in this way.
3.16. Distributing Python Code 130
PyXLL User Guide, Release 5.3.0
Referencing the Python environment from a network drive will not be as fast to load as if it was installed on the
local PC. Another option is to use the startup_script option and copy a Python environment locally on demand
when Excel starts.
A startup script that downloads a Python environment would look something along the lines of the following:
SET VERSION=v1
SET PYTHON_ENV=.\python37-%VERSION%
REM No need to download anything if we already have the latest
IF EXIST %PYTHON_ENV% THEN GOTO DONE
REM Download and unzip the Python environment
wget https://intranet/pyxll/python37-%VERSION%.tar.gz
tar -xzf python37-%PYTHON_ENV%.tar.gz --directory %PYTHON_ENV%
ECHO Latest Python environment has been downloaded to .\python37-%VERSION%
:DONE
REM Set the PyXLL executable option
ECHO pyxll-set-option PYTHON executable %PYTHON_ENV%\pythonw.exe
3.16.5 Building an installer
If you need to deploy to PCs that might not have fast or reliable access to your network, and so accessing a shared
drive or using a deployment script is not feasible, building an install can be a solution.
The Python runtime can be bundled with PyXLL into a single standalone installer.
For detailed instructions and an example project for building an MSI installer, see the pyxll-installer project on
GitHub.
3.16.6 Setuptools Entry Points
When distributing Python code it is usual to package it up into a wheel file using setuptools. This allows
consumers of your package to install it easily using pip (the Python package manager).
You can distribute a Python package containing PyXLL functionality in the same way. To avoid the end user
of your package from having to manually configure their pyxll.cfg file, PyXLL looks for its entry points in any
installed packages.
The entry points are configured in your setup.py file used to build your package. PyXLL supports two entry points,
pyxll.modules and pyxll.ribbon.
A simple setup.py file to build a package called your_package might look as follows:
from setuptools import setup, find_packages
setup(
name="your_package",
description="Your package description",
version="0.0.1",
packages=find_packages(),
entry_points={
"pyxll": [
"modules = your_package:pyxll_modules",
"ribbon = your_package:pyxll_ribbon"
]
}
)
3.16. Distributing Python Code 131
PyXLL User Guide, Release 5.3.0
To build a wheel using your setup.py file you run python setup.py bdist_wheel.
The user of your package would install the wheel by running pip install <wheel file>.
The entry points listed in this setup.py file are your_package:pyxll_modules for the pyxll/modules
entry point and your_package:pyxll_ribbon for the pyxll/ribbon entry point.
Each entry point is a reference to a function. It’s these functions that PyXLL will call to configure itself to load
your package automatically without the consumer of your package having to modify their pyxll.cfg file.
modules entry point
The modules entry point is a function that returns a list of module names for PyXLL to import when it loads.
In the above your_package example, suppose your_package contained two sub-modules
your_package.xlfuncs and your_package.xlmacros that you want to be loaded when PyXLL
starts. To make that happen you would write the your_package.pyxll_modules entry point function
return both packages.
Listing 5: setup.py
def pyxll_modules():
"""entry point referenced in setup.py"""
return [
"your_package.xlfuncs",
"your_package.xlmacros",
]
This is of course just an example. The entry point function could be in any package (including a subpackage) that
you configure in your setup.py file.
ribbon entry point
The ribbon entry point can be used to add ribbon controls to the Excel ribbon in addition to whatever ribbon
controls are configured in the pyxll.cfg file.
The ribbon entry point function should return either a single ribbon xml resource or a list of ribbon xml resources.
These will be merged with any other ribbon files loaded and combined to create the custom ribbon UI in Excel.
See Customizing the Ribbon for the specifics of how to create a ribbon xml file.
In the above your_package example, suppose you had also included a “ribbon.xml” resource in the wheel and
you wanted to add that to the Excel ribbon. Your ribbon entry point would load the XML data from the resource
(or it could load it from a file) and return that for PyXLL to use when building the ribbon.
Listing 6: your_package/__init__.py
import pkg_resources
def pyxll_ribbon():
"""entry point referenced in setup.py"""
# Load the XML resource
ribbon_xml = pkg_resources.resource_string(__name__, "ribbon.xml")
# Return the ribbon XML resource for PyXLL to load
return ribbon_xml
3.16. Distributing Python Code 132
PyXLL User Guide, Release 5.3.0
If you are using files instead of package resources then you can also tell PyXLL the filename of the XML file. If
you have images referenced in your ribbon xml using relative paths then providing the filename will ensure that
PyXLL can load the images relative to the correct path.
Listing 7: your_package/__init__.py
import os
def pyxll_ribbon():
"""entry point referenced in setup.py"""
# Get the ribbon XML filename
ribbon_file = os.path.join(os.path.dirname(__file__), "ribbon.xml")
# Load the xml data
with open(ribbon_file) as fh:
ribbon_xml = fh.read()
# Return the ribbon XML resource with its file name for PyXLL to load
return (ribbon_file, ribbon_xml)
When using the load_image function as your image loaded in the ribbon xml file, images can be referenced
either by filename or as a package resource name if you are building them into your package.
If you have multiple ribbon resources then the pyxll.ribbon entry point function may return a list of resources
or a list of (filename, resource) tuples.
Warning: If your Python packages are on a network drive it can be slow to look for entry points, which may
result in slow start times for Excel.
You can prevent PyXLL from looking for entry points by setting the following in your pyxll.cfg file:
Listing 8: your_package/__init__.py
[PYXLL]
ignore_entry_points = 1
3.17 Workbook Metadata
Some PyXLL features will add XML metadata to the Excel workbook when saving.
Features that use this metadata are:
• Recalculating On Open
• Saving Objects in the Workbook
• Cell Formatting
1
Metadata used by PyXLL is added to the workbook as a CustomXMLPart, which is part of the workbook docu-
ment.
The CustomXMLPart is saved in the workbook using an XML namespace specific to the PyXLL add-in so as not
to conflict with data saved by other add-ins. If you have specified a name for your add-in using the name setting
that will be used to avoid conflict with any other PyXLL add-ins you may have loaded.
If you prefer to specify the namespace to use instead of having PyXLL use it’s own namespace you can do so by
setting metadata_custom_xml_namespace in the PYXLL section of your pyxll.cfg file.
[PYXLL]
metadata_custom_xml_namespace = urn:your_name:metadata
1
Custom formatting only requires metadata when a custom formatter is applied to a Dynamic Array function.
3.17. Workbook Metadata 133
PyXLL User Guide, Release 5.3.0
To disable writing any metadata you can set disable_saving_metadata = 1 in the PYXLL section of
your pyxll.cfg file. Note that this will affect all PyXLL features that require metadata.
[PYXLL]
disable_saving_metadata = 1
3.17. Workbook Metadata 134
CHAPTER
FOUR
API REFERENCE
4.1 Function Decorators
These decorators are used to expose Python functions to Excel as worksheet functions, menu functions and macros.
• xl_func
• xl_menu
• xl_macro
• xl_arg_type
• xl_return_type
• xl_arg
• xl_return
4.1.1 xl_func
xl_func(signature=None, category=PyXLL, help_topic='', thread_safe=False, macro=False,
allow_abort=None, volatile=False, disable_function_wizard_calc=False, dis-
able_replace_calc=False, name=None, auto_resize=False, hidden=False, transpose=False,
recalc_on_open=None, formatter=None)
xl_func is decorator used to expose python functions to Excel. Functions exposed in this way can be called
from formulas in an Excel worksheet and appear in the Excel function wizard.
Parameters
• signature (string) – string specifying the argument types and, optionally, their
names and the return type. If the return type isn’t specified the var type is assumed. eg:
"int x, string y: double" for a function that takes two arguments, x and y
and returns a double.
"float x" or "float x: var" for a function that takes a float x and returns a
variant type.
If no signature is provided the argument and return types will be inferred from any type
annotations, and if there are no type annotations then the types will be assumed to be
var.
See Simple Types for the built-in types that can be used in the signature.
• category (string) – String that sets the category in the Excel function wizard the
exposed function will appear under.
• help_topic (string) – Path of the help file (.chm) or URL that will be available
from the function wizard in Excel.
135
PyXLL User Guide, Release 5.3.0
• thread_safe (boolean) – Indicates whether the function is thread-safe or not. If
True the function may be called from multiple threads in Excel 2007 or later
• macro (boolean) – If True the function will be registered as a macro sheet equivalent
function. Macro sheet equivalent functions are less restricted in what they can do, and
in particular they can call Excel macro sheet functions such as xlfCaller.
• allow_abort (boolean) – If True the function may be cancelled by the user press-
ing Esc. A KeyboardInterrupt exception is raised when Esc is pressed. If not specified
the behavior is determined by the allow_abort setting in the config (see PyXLL Settings).
Enabling this option has performance implications. See Interrupting Functions for more
details.
• volatile (boolean) – if True the function will be registered as a volatile function,
which means it will be called every time Excel recalculates regardless of whether any
of the parameters to the function have changed or not
• disable_function_wizard_calc (boolean) – Don’t call from the Excel
function wizard. This is useful for functions that take a long time to complete that
would otherwise make the function wizard unresponsive
• disable_replace_calc (boolean) – Set to True to stop the function being
called from Excel’s find and replace dialog.
• arg_descriptions – dict of parameter names to help strings.
• name (string) – The Excel function name. If None, the Python function name is
used.
• auto_resize (boolean) – When returining an array, PyXLL can automatically
resize the range used by the formula to match the size of the result.
• hidden (boolean) – If True the UDF is hidden and will not appear in the Excel
Function Wizard.
@Since PyXLL 3.5.0
• transpose (boolean) – If true, if an array is returned it will be transposed before
being returned to Excel. This can be used for returning 1d lists as rows.
@Since PyXLL 4.2.0
• recalc_on_open (boolean) – If true, when saved and re-opened the cell calling
this function will be recalculated. The default is True for functions returning cached
objects and RTD functions, and False otherwise.
See Recalculating On Open.
@Since PyXLL 4.5.0
• formatter (pyxll.Formatter) – Formatter object to use to format the result
of the function. For brevity a dict may be used, in which case a Formatter will be
constructed from that dict.
See Cell Formatting.
@Since PyXLL 4.5.0
Example usage:
from pyxll import xl_func
@xl_func
def hello(name):
"""return a familiar greeting"""
return "Hello, %s" % name
(continues on next page)
4.1. Function Decorators 136
PyXLL User Guide, Release 5.3.0
(continued from previous page)
# Python 3 using type annotations
@xl_func
def hello2(name: str) -> str:
"""return a familiar greeting"""
return "Hello, %s" % name
# Or a signature may be provided as string
@xl_func("int n: int", category="Math", thread_safe=True)
def fibonacci(n):
"""naive iterative implementation of fibonacci"""
a, b = 0, 1
for i in xrange(n):
a, b = b, a + b
return a
See Worksheet Functions for more details about using the xl_func decorator, and Array Functions for more
details about array functions.
4.1.2 xl_menu
xl_menu(name, menu=None, sub_menu=None, order=0, menu_order=0, allow_abort=None, short-
cut=None)
xl_menu is a decorator for creating menu items that call Python functions. Menus appear in the ‘Addins’
section of the Excel ribbon from Excel 2007 onwards, or as a new menu in the main menu bar in earlier
Excel versions.
Parameters
• name (string) – name of the menu item that the user will see in the menu
• menu (string) – name of the menu that the item will be added to. If a menu of that
name doesn’t already exist it will be created. By default the PyXLL menu is used
• sub_menu (string) – name of the submenu that this item belongs to. If a submenu
of that name doesn’t exist it will be created
• order (int) – influences where the item appears in the menu. The higher the number,
the further down the list. Items with the same sort order are ordered lexographically. If
the item is a sub-menu item, this order influences where the sub-menu will appear in
the main menu. The menu order my also be set in the config (see configuration).
• sub_order (int) – similar to order but it is used to set the order of items within a
sub-menu
• menu_order (int) – used when there are multiple menus and controls the order in
which the menus are added
• allow_abort (boolean) – If True the function may be cancelled by the user press-
ing Esc. A KeyboardInterrupt exception is raised when Esc is pressed. If not specified
the behavior is determined by the allow_abort setting in the config (see PyXLL Settings).
• shortcut (string) – Assigns a keyboard shortcut to the menu item. Shortcuts
should be one or more modifier key names (Ctrl, Shift or Alt) and a key, separated by
the ‘+’ symbol. For example, ‘Ctrl+Shift+R’.
If the same key combination is already in use by Excel it may not be possible to assign
a menu item to that combination.
Example usage:
from pyxll import xl_menu, xlcAlert
@xl_menu("My menu item")
(continues on next page)
4.1. Function Decorators 137
PyXLL User Guide, Release 5.3.0
(continued from previous page)
def my_menu_item():
xlcAlert("Menu button example")
See Menu Functions for more details about using the xl_menu decorator.
4.1.3 xl_macro
xl_macro(signature=None, allow_abort=None, name=None, shortcut=None)
xl_macro is a decorator for exposing python functions to Excel as macros. Macros can be triggered from
controls, from VBA or using COM.
Parameters
• signature (str) – An optional string that specifies the argument types and, option-
ally, their names and the return type.
The format of the signature is identical to the one used by xl_func.
If no signature is provided the argument and return types will be inferred from any type
annotations, and if there are no type annotations then the types will be assumed to be
var.
• allow_abort (bool) – If True the function may be cancelled by the user pressing
Esc. A KeyboardInterrupt exception is raised when Esc is pressed. If not specified the
behavior is determined by the allow_abort setting in the config (see PyXLL Settings).
• name (string) – The Excel macro name. If None, the Python function name is used.
• shortcut (string) – Assigns a keyboard shortcut to the macro. Shortcuts should
be one or more modifier key names (Ctrl, Shift or Alt) and a key, separated by the ‘+’
symbol. For example, ‘Ctrl+Shift+R’.
If the same key combination is already in use by Excel it may not be possible to assign
a macro to that combination.
Macros can also have keyboard shortcuts assigned in the config file (see configuration).
• transpose (boolean) – If true, if an array is returned it will be transposed before
being returned to Excel.
Example usage:
from pyxll import xl_macro, xlcAlert
@xl_macro
def popup_messagebox():
"""pops up a message box"""
xlcAlert("Hello")
@xl_macro
def py_strlen(s):
"""returns the length of s"""
return len(s)
See Macro Functions for more details about using the xl_macro decorator.
4.1. Function Decorators 138
PyXLL User Guide, Release 5.3.0
4.1.4 xl_arg_type
xl_arg_type(name, base_type [, allow_arrays=True] [, macro=None] [, thread_safe=None])
Returns a decorator for registering a function for converting from a base type to a custom type.
Parameters
• name (string) – custom type name
• base_type (string) – base type
• allow_arrays (boolean) – custom type may be passed in an array using the stan-
dard [] notation
• macro (boolean) – If True all functions using this type will automatically be regis-
tered as a macro sheet equivalent function
• thread_safe (boolean) – If False any function using this type will never be
registered as thread safe
4.1.5 xl_return_type
xl_return_type(name, base_type [, allow_arrays=True] [, macro=None] [, thread_safe=None])
Returns a decorator for registering a function for converting from a custom type to a base type.
Parameters
• name (string) – custom type name
• base_type (string) – base type
• allow_arrays (boolean) – custom type may be returned as an array using the
standard [] notation
• macro (boolean) – If True all functions using this type will automatically be regis-
tered as a macro sheet equivalent function
• thread_safe (boolean) – If False any function using this type will never be
registered as thread safe
4.1.6 xl_arg
xl_arg(_name [, _type=None] [, **kwargs])
Decorator for providing type information for a function argument. This can be used instead of providing a
function signature to xl_func.
Parameters
• _name (string) – Argument name. This should match the argument name in the
function definition.
• _type – Optional argument type. This should be a recognized type name or the name
of a custom type.
• kwargs – Type parameters for parameterized types (eg NumPy arrays and Pandas
types).
4.1. Function Decorators 139
PyXLL User Guide, Release 5.3.0
4.1.7 xl_return
xl_return([_type=None] [, **kwargs])
Decorator for providing type information for a function’s return value. This can be used instead of providing
a function signature to xl_func.
Parameters
• _type – Optional argument type. This should be a recognized type name or the name
of a custom type.
• kwargs – Type parameters for parameterized types (eg NumPy arrays and Pandas
types).
4.2 Plotting Functions and Classes
See Charts and Plotting for more information about plotting Python charts in Excel.
• plot
• PlotBridgeBase
4.2.1 plot
plot(figure=None, name=None, width=None, height=None, top=None, left=None, sheet=None, al-
low_svg=True, bridge_cls=None, **kwargs)
Plots a figure to Excel as an embedded image.
This can be called from an Excel worksheet function, or from anywhere else such as a macro or menu
function. If called from a worksheet function the image will be placed below the calling cell, and repeated
calls will update the image rather than create new ones. If called from elsewhere the image will be placed
below the current selection, and each call will create a new image.
The figure can be any of the following:
• A matplotlib Figure, Subplot or Artist object
• A plotly Figure object
• A bokeh Plot object
• An altair Chart object
If no figure is provided the current matplotlib.pyplot figure is used.
Parameters
• figure – Figure to plot. This can be an instance of any of the following:
– matplotlib.figure.Figure
– matplotlib.artist.Artist
– matplotlib.axes._subplots.SubplotBase
– plotly.graph_objects.Figure
– bokeh.models.plots.Plot
– altair.vegalite.v4.api.Chart
If None, the active matplotlib.pyplot figure is used.
4.2. Plotting Functions and Classes 140
PyXLL User Guide, Release 5.3.0
• name – Name of Picture object in Excel. If this is None then a name will be chosen,
and if called from a UDF then repeated calls will re-use the same name.
• width – Initial width of the picture in Excel, in points. If set then height must also be
set. If None the width will be taken from the figure.
• height – Initial height of the picture in Excel, in points. If set then width must also
be set. If None the height will be taken from the figure.
• top – Initial location of the top of the plot in Excel, in points. If set then left must also
be set. If None, the picture will be placed below the current or selected cell.
• left – Initial location of the left of the plot in Excel, in points. If set then top must
also be set. If None, the picture will be placed below the current or selected cell.
• sheet – Name of the sheet to add the picture to. If none, the current sheet is used.
• allow_svg – Some figures may be rendered as SVG, if the plotting library and the
version of Excel being used allows. This can be disabled by setting this option to False.
• bridge_cls – Class to use for exporting the plot as an image. If None this will be
selected automatically based on the type of figure.
• kwargs – Additional arguments will be called to the implementation specific method
for exporting the figure to an image.
Note: The options width, height, top and left only affect the image when it is initially created. If a
subsequent call to plot updates an existing image (for example, if called from a worksheet function or if a
name is passed) then it will not be resized or moved from its current location.
4.2.2 PlotBridgeBase
class PlotBridgeBase
Base class for plotting bridges used by plot.
This can be used to add support for plotting libraries other than the standard ones supported by PyXLL.
All methods must be implemented by the derived class.
__init__(self, figure)
Construct the plot bridge for exporting a figure. The figure is the object passed to plot.
can_export(self, format)
Return True if the figure can be exported in a specific format.
Valid formats are ‘svg’ and ‘png’.
get_size_hint(self, dpi)
Return (width, height) tuple the figure should be exported as or None.
Width and height are in points (72th of an inch).
If no size hint is available return None.
export(self, width, height, dpi, format, filename, **kwargs)
Export the figure to a file as a given size and format.
Parameters
• width – Width of the image to export in points.
• height – Height of the image to export in points.
• dpi – DPI to use to export the image.
• format – Format to export the image to. Valid formats are ‘svg’ and ‘png’.
4.2. Plotting Functions and Classes 141
PyXLL User Guide, Release 5.3.0
• filename – Filename to export the image to.
• kwargs – Additional kwargs passed to plot.
4.3 Custom Task Panes
See Custom User Interfaces for more information about Custom Task Panels in PyXLL.
• create_ctp
• CTPBridgeBase
4.3.1 create_ctp
create_ctp(control, title=None, width=None, height=None, position=CTPDockPositionRight,
top=None, left=None, timer_interval=0.1, bridge_cls=None)
Creates a Custom Task Pane from a UI control object.
The control object can be any of the following:
• tkinter.Toplevel
• PySide2.QtWidgets.QWidget
• PySide6.QtWidgets.QWidget
• PyQt5.QtWidgets.QWidget
• PyQt6.QtWidgets.QWidget
• wx.Frame
Parameters
• control – UI control of one of the supported types.
• title – Title of the custom task pane to be created.
• width – Initial width of the custom task pane in points.
• height – Initial height of the custom task pane in points.
• position – Where to display the custom task pane. Can be any of:
– CTPDockPositionLeft
– CTPDockPositionTop
– CTPDockPositionRight
– CTPDockPositionBottom
– CTPDockPositionFloating
• top – Initial top position of custom task pane (only used if floating). New in PyXLL 5.2
• left – Initial left position of custom task pane (only used if floating). New in PyXLL
5.2
• timer_interval – Time in seconds between calls to CTPBridgeBase.
on_timer.
The CTP bridge classes are what integrate the Python UI toolkit with the Excel Windows
message loop. They use on_timer to poll their own message queues. If you are finding
the panel is not responsive enough you can reduce the timer interval with this setting.
4.3. Custom Task Panes 142
PyXLL User Guide, Release 5.3.0
This can also be defaulted by setting ctp_timer_interval in the PYXLL section
of the pyxll.cfg config file.
New in PyXLL 5.1
• bridge_cls – Class to use for integrating the control into Excel. If None this will be
selected automatically based on the type of control.
CTPDockPositionLeft = 0
CTPDockPositionTop = 1
CTPDockPositionRight = 2
CTPDockPositionBottom = 3
CTPDockPositionFloating = 4
4.3.2 CTPBridgeBase
class CTPBridgeBase
Base class of bridges between the Python UI toolkits and PyXLL’s Custom Task Panes.
This can be used to add support for UI toolkits other than the standard ones supported by PyXLL.
__init__(self, control)
Construct the custom task pane bridge. The control is the object passed to create_ctp.
close(self )
Close the host CTP window.
Do not override
get_hwnd(self )
Return the window handle as an integer.
Required: Override in subclass
get_title(self )
Return the window title as a string.
Optional: Can be overridden in subclass
pre_attach(self, hwnd)
Called before the window is attached to the Custom Task Pane host window.
Optional: Can be overridden in subclass
post_attach(self, hwnd)
Called after the window is attached to the Custom Task Pane host window.
Optional: Can be overridden in subclass
on_close(self )
Called when the Custom Task Pane host window is closed.
Optional: Can be overridden in subclass
on_window_closed(self )
Called when control window received a WM_CLOSE Windows message.
Optional: Can be overridden in subclass
on_window_destroyed(self )
Called when control window received a WM_DESTROY Windows message.
Optional: Can be overridden in subclass
4.3. Custom Task Panes 143
PyXLL User Guide, Release 5.3.0
process_message(self, hwnd, msg, wparam, lparam)
Called when the Custom Task Panel host window received a Windows message.
Should return a tuple of (result, handled) where the result is an integer and handled is a bool indicating
whether the message has been handled or not. Messages that have been handled will not be passed to
the default message handler.
Returning None is equivalent to returning (0, False).
Parameters
• hwnd – Window handle
• msg – Windows message id
• wparam – wparam passed with the message
• lparam – lparam passed with the message
Optional: Can be overridden in subclass
translate_accelerator(self, hwnd, msg, wparam, lparam, modifier)
Called when the Custom Task Panel host control’s TranslateAccelerator Windows method is called.
This can be used to convert key presses into commands or events to pass to the UI toolkit control.
Should return a tuple of (result, handled) where the result is an integer and handled is a bool indicating
whether the message has been handled or not. Messages that have been handled will not be passed to
the default message handler.
Returning None is equivalent to returning (0, False).
Parameters
• hwnd – Window handle
• msg – Windows message id
• wparam – wparam passed with the message
• lparam – lparam passed with the message
• modifier – If the message is a WM_KEYDOWN message, the modifier will be set
to indicate any key modifiers currently pressed. 0x0 = no key modifiers, 0x1 = Shift
key pressed, 0x2 = Control key pressed, 0x4 = Alt key pressed.
Optional: Can be overridden in subclass
on_timer(self )
If this method is overridden then it will be called periodically and can be used to poll the UI toolkit’s
message loop.
The interval between calls can be set by passing timer_interval to create_ctp or by setting
ctp_timer_interval in the PYXLL section of the pyxll.cfg config file.
Optional: Can be overridden in subclass
4.4 Utility Functions
• xl_app
• schedule_call
• reload
• rebind
4.4. Utility Functions 144
PyXLL User Guide, Release 5.3.0
• xl_version
• get_config
• get_dialog_type
• get_last_error
• get_type_converter
• cached_object_count
• get_event_loop
4.4.1 xl_app
xl_app(com_package=None)
Gets the Excel Application COM object and returns it as a win32com.Dispach, comtypes.
POINTER(IUknown), pythoncom.PyIUnknown or xlwings.App object, depending on which
COM package is being used.
Many methods and properties Excel Application COM object will fail if called from outside of an Excel
macro context. Generally, xl_app should only be used from Python code called from an Excel macro
1
,
menu
1
, or worksheet function
12
. To use it from any other context, or from a background thread, schedule a
call using schedule_call.
Parameters com_package (string) – The Python package to use when returning the COM
object. It should be None, ‘win32com’, ‘comtypes’, ‘pythoncom’ or ‘xlwings’. If None the
com package set in the configuration file will be used, or ‘win32com’ if nothing is set.
Returns The Excel Application COM object using the requested COM package.
Warning: Excel COM objects should never be passed between threads. Only use a COM object
in the same thread it was created in. Doing otherwise is likely to crash Excel! If you are using a
background thread the safest thing to do is to only call into Excel using COM via functions scheduled
using schedule_call.
4.4.2 schedule_call
schedule_call(func, *args, delay=0, nowait=False, retries=0, retry_delay=0, retry_backoff=1.0,
retry_filter=None, disable_calculation=False, disable_screen_updating=False)
Schedule a function to be called after the current Excel calculation cycle has completed.
The function is called in an Excel macro context so it is safe to use xl_app and other COM and macro
functions.
This can be used by worksheet functions that need to modify the worksheet where calling back into Excel
would fail or cause a deadlock.
From Python 3.7 onwards when called from the PyXLL asyncio event loop and ‘nowait’ is not set this
function returns an asyncio.Future. This future can be awaited on to get the result of the call (see
warning about awaiting in an async UDF below).
NOTE: In the stubs version (not embedded in PyXLL) the function is always called immediately and will
not retry.
1
Do not use async functions when using xl_app as async functions run in the asyncio event loop on a background thread. If you need to
use xl_app from an async function, schedule it using schedule_call.
2
Certain things will not work when trying to call back into Excel with COM from an Excel worksheet function as some operations are not
allowed while Excel is calculating. For example, trying to set the value of a cell will fail. For these cases, use schedule_call to schedule
a call after Excel has finished calculating.
4.4. Utility Functions 145
PyXLL User Guide, Release 5.3.0
Parameters
• func – Function or callable object to call in an Excel macro context at some time in
the near future.
• args – Arguments to be passed to the the function.
• delay – Delay in seconds to wait before calling the function.
• nowait – Do not return a Future even if called from the asyncio event loop.
• retries – Integer number of times to retry.
• retry_delay – Time in seconds to wait between retries.
• retry_backoff – Multiplier to apply to ‘retry_delay’ after each retry. This can be
used to increase the time between each retry by setting ‘retry_backoff’ to > 1.0.
• retry_filter – Callable that receives the exception value in the case of an error. It
should return True if a retry should be attempted or False otherwise.
• disable_calculation – Disable automatic calculations while the callback is be-
ing called. This switches the Excel calculation mode to manual and restores it to its
previous mode after the call is complete.
New in PyXLL 5.2
• disable_screen_updating – Disable Excel’s screen updating while the callback
is being called. Screen updating is restored to its previous mode after the call is com-
plete.
New in PyXLL 5.2
Example usage:
from pyxll import xl_func, xl_app, xlfCaller, schedule_call
@xl_func(macro=True)
def set_values(rows, cols, value):
"""Copies 'value' to a range of rows x cols below the calling cell."""
# Get the address of the calling cell
caller = xlfCaller()
address = caller.address
# The update is done asynchronously so as not to block Excel
# by updating the worksheet from a worksheet function.
def update_func():
xl = xl_app()
xl_range = xl.Range(address)
# get the cell below and expand it to rows x cols
xl_range = xl.Range(range.Resize(2, 1), range.Resize(rows+1, cols))
# and set the range's value
xl_range.Value = value
# Schedule calling the update function
pyxll.schedule_call(update_func)
return address
Note: This function doesn’t allow passing keyword arguments to the schedule function. To do that, use
functools.partial().
4.4. Utility Functions 146
PyXLL User Guide, Release 5.3.0
# Will schedule "print("Hello", flush=True)"
schedule_call(functools.partial(print, "Hello", flush=True))
Warning: If called from an async UDF it is important not to await on the result of this call! Doing so
is likely to cause a deadlock resulting in the Excel process hanging.
This is because the scheduled call won’t run until the current calculation has completed, so your function
will not complete if awaiting for the result.
4.4.3 reload
reload()
Causes the PyXLL addin and any modules listed in the config file to be reloaded once the calling function
has returned control back to Excel.
If the ‘deep_reload’ configuration option is turned on then any dependencies of the modules listed in the
config file will also be reloaded.
The Python interpreter is not restarted.
4.4.4 rebind
rebind()
Causes the PyXLL addin to rebuild the bindings between the exposed Python functions and Excel once the
calling function has returned control back to Excel.
This can be useful when importing modules or declaring new Python functions dynamically and you want
newly imported or created Python functions to be exposed to Excel without reloading.
Example usage:
from pyxll import xl_macro, rebind
@xl_macro
def load_python_modules():
import another_module_with_pyxll_functions
rebind()
4.4.5 xl_version
xl_version()
Returns the version of Excel the addin is running in, as a float.
• 8.0 => Excel 97
• 9.0 => Excel 2000
• 10.0 => Excel 2002
• 11.0 => Excel 2003
• 12.0 => Excel 2007
• 14.0 => Excel 2010
• 15.0 => Excel 2013
• 16.0 => Excel 2016
4.4. Utility Functions 147
PyXLL User Guide, Release 5.3.0
4.4.6 get_config
get_config()
Returns the PyXLL config as a ConfigParser.SafeConfigParser instance
See also Configuring PyXLL.
4.4.7 get_dialog_type
get_dialog_type()
Returns
the type of the current dialog that initiated the call into the current Python function
xlDialogTypeNone
or xlDialogTypeFunctionWizard
or xlDialogTypeSearchAndReplace
xlDialogTypeNone = 0
xlDialogTypeFunctionWizard = 1
xlDialogTypeSearchAndReplace = 2
4.4.8 get_last_error
get_last_error(xl_cell)
When a Python function is called from an Excel worksheet, if an uncaught exception is raised PyXLL caches
the exception and traceback as well as logging it to the log file.
The last exception raised while evaluating a cell can be retrieved using this function.
The cache used by PyXLL to store thrown exceptions is limited to a maximum size, and so if there are more
cells with errors than the cache size the least recently thrown exceptions are discarded. The cache size may
be set via the error_cache_size setting in the config.
When a cell returns a value and no exception is thrown any previous error is not discarded. This is because
doing so would add additional performance overhead to every function call.
Parameters xl_cell – An XLCell instance or a COM Range object (the exact type depends
on the com_package setting in the config.
Returns The last exception raised by a Python function evaluated in the cell, as a tuple (type,
value, traceback).
Example usage:
from pyxll import xl_func, xl_menu, xl_version, get_last_error
import traceback
@xl_func("xl_cell: string")
def python_error(cell):
"""Call with a cell reference to get the last Python error"""
exc_type, exc_value, exc_traceback = pyxll.get_last_error(cell)
if exc_type is None:
return "No error"
return "".join(traceback.format_exception_only(exc_type, exc_value))
(continues on next page)
4.4. Utility Functions 148
PyXLL User Guide, Release 5.3.0
(continued from previous page)
@xl_menu("Show last error")
def show_last_error():
"""Select a cell and then use this menu item to see the last error"""
selection = xl_app().Selection
exc_type, exc_value, exc_traceback = get_last_error(selection)
if exc_type is None:
xlcAlert("No error found for the selected cell")
return
msg = "".join(traceback.format_exception(exc_type, exc_value, exc_
˓→traceback))
if xl_version() < 12:
msg = msg[:254]
xlcAlert(msg)
4.4.9 get_type_converter
get_type_converter(src_type, dest_type [, src_kwargs=None] [, dest_kwargs=None])
Returns a function to convert objects of type src_type to dest_type.
Even if there is no function registered that converts exactly from src_type to dest_type, as long
as there is a way to convert from src_type to dest_type using one or more intermediate types this
function will create a function to do that.
Parameters
• src_type (string) – Signature of type to convert from.
• dest_type (string) – Signature of type to convert to.
• src_kwargs (dict) – Parameters for the source type (e.g. {'dtype'=float}
for numpy_array).
• dest_kwargs (dict) – Parameters for the destination type (e.g.
{'index'=True} for dataframe).
Returns Function to convert from src_type to dest_type.
Example usage:
from pyxll import xl_func, get_type_converter
@xl_func("var x: var")
def py_function(x):
# if x is a number, convert it to a date
if isinstance(x, float):
to_date = get_type_converter("var", "date")
x = to_date(x)
return "%s : %s" % (x, type(x))
4.4. Utility Functions 149
PyXLL User Guide, Release 5.3.0
4.4.10 cached_object_count
cached_object_count()
Returns the current number of cached objects.
When objects are returns from worksheet functions using the object or var type they are stored in an
internal object cache and a handle is returned to Excel. Once the object is no longer referenced in Excel the
object is removed from the cache automatically.
See Cached Objects.
4.4.11 get_event_loop
get_event_loop()
New in PyXLL 4.2
Get the async event loop used by PyXLL for scheduling async tasks.
If called in Excel and the event loop is not already running it is started.
If called outside of Excel then the event loop is returned without starting it.
Returns asyncio.AbstractEventLoop
See Asynchronous Functions.
4.5 Ribbon Functions
These functions can be used to manipulate the Excel ribbon.
The ribbon can be updated at any time, for example as PyXLL is loading via the xl_on_open and
xl_on_reload event handlers, or from a menu using using xl_menu.
See the section on customizing the ribbon for more details.
• load_image
• get_ribbon_xml
• set_ribbon_xml
• set_ribbon_tab
• remove_ribbon_tab
4.5.1 load_image
load_image(name)
Loads an image file and returns it as a COM IPicture object suitable for use when customizing the ribbon.
This function can be set at the Ribbon image handler by setting the loadImage attribute on the customUI
element in the ribbon XML file.
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
loadImage="pyxll.load_image">
<ribbon>
<tabs>
<tab id="CustomTab" label="Custom Tab">
<group id="Tools" label="Tools">
<button id="Reload"
(continues on next page)
4.5. Ribbon Functions 150
PyXLL User Guide, Release 5.3.0
(continued from previous page)
size="large"
label="Reload PyXLL"
onAction="pyxll.reload"
image="reload.png"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
Or it can be used when returning an image from a getImage callback.
Parameters name (string) – Filename or resource location of the image file to load. This
may be an absolute path or a resource location in the form module:resource.
Returns A COM IPicture object (the exact type depends on the com_package setting in the
config.
4.5.2 get_ribbon_xml
get_ribbon_xml()
Returns the XML used to customize the Excel ribbon bar, as a string.
See the section on customizing the ribbon for more details.
4.5.3 set_ribbon_xml
set_ribbon_xml(xml, reload=True)
Sets the XML used to customize the Excel ribbon bar.
Parameters
• xml – XML to set, as a string.
• reload – If True, the ribbon bar will be reloaded using the new XML (does not reload
PyXLL).
See the section on customizing the ribbon for more details.
4.5.4 set_ribbon_tab
set_ribbon_tab(xml, tab_id=None, reload=True)
Sets a single tab in the ribbon using an XML fragment.
Instead of replacing the whole ribbon XML this function takes a tab element from the input XML and
updates the ribbon XML with that tab.
If multiple tabs exist in the input XML, the first who’s id attribute matches tab_id is used (or simply the first
tab element if tab_id is None).
If a tab already exists in the ribbon XML with the same id attribute then it is replaced, otherwise the new
tab is appended to the tabs element.
Parameters
• xml – XML document containing at least on tab element.
• tab_id – id of the tab element to set (or None to use the first tab element in the
document).
• reload – If True, the ribbon bar will be reloaded using the new XML (does not reload
PyXLL).
4.5. Ribbon Functions 151
PyXLL User Guide, Release 5.3.0
4.5.5 remove_ribbon_tab
remove_ribbon_tab(tab_id, reload=True)
Removes a single tab from the ribbon XML where the tab element’s id attribute matches tab_id.
Parameters
• tab_id – id of the tab element to remove.
• reload – If True, the ribbon bar will be reloaded using the new XML (does not reload
PyXLL).
Returns True if a tab was removed, False otherwise.
4.6 Cell Formatting
See Cell Formatting for more information about cell formatting in PyXLL.
• Formatter
• DataFrameFormatter
• DateFormatter
• ConditionalFormatter
• ConditionalFormatterBase
4.6.1 Formatter
class Formatter
Formatter for formatting values returned via xl_func, or using XLCell.options and XLCell.
value.
Use Formatter.rgb for constructing color values.
Formatters may be combined by adding them together.
Custom formatters should use this class as their base class.
See Cell Formatting for more details.
Parameters
• interior_color – Value to set the interior color to.
• text_color – Value to set the text color to.
• bold – If true, set the text style to bold.
• italic – If true, set the text style to italic.
• font_size – Value to set the font size to.
• number_format – Excel number format to use.
• auto_fit – Auto-fit to the content of the cells. May be True (fit column width), False
(don’t fit), ‘columns’ (fit column width), ‘rows’ (fit row width), ‘both’ (fit column and
row width).
apply(self, cell, value=None, datatype=None, datatype_ndim=0, datatype_kwargs={}, trans-
pose=False)
The apply method is called to apply a formatter to a cell or range of cells.
4.6. Cell Formatting 152
PyXLL User Guide, Release 5.3.0
It is called after a worksheet function decorated with xl_func has returned if using the formatter
kwarg. It can also be used directly with an XLCell instance from a macro function.
This method may be implemented by a sub-class for custom formatting. For array functions, if the
formatter should be applied cell by cell for each cell in the range, use apply_cell instead.
Parameters
• cell – Instance of an XLCell the formatting is to be applied to.
• value – The value returned from the xl_func or XLCell.value.
• datatype – The datatype of the value being formatted.
• datatye_ndim – The number of dimensions (0, 1 or 2) of the value being format-
ted.
• datatype_kwargs – The parameters of the datatype of the value being formatted.
• transpose – The transpose option from the xl_func decorator.
When a value is returned from an xl_func the formatter is applied after Excel has finished calculat-
ing.
The apply method is called with the value returned, and any details about the datatype of the returned
value. This allows the formatter to apply formatting relevant to the returned datatype, and can be
conditional on the returned value.
apply_cell(self, cell, value=None, datatype=None, datatype_kwargs={})
For use by custom formatters.
If you need the formatter to be called for each individual cell when formatting an array formula,
override this method instead of Formatter.apply.
Unlike Formatter.apply this method is called for each item in the returned value. If you need to
apply formatting at the array level and the item level you may override both, but ensure you call the
super-class method Formatter.apply from your override apply method.
Parameters
• cell – Instance of an XLCell the formatting is to be applied to.
• value – The value returned from the xl_func or XLCell.value.
• datatype – The datatype of the value being formatted.
• datatype_kwargs – The parameters of the datatype of the value being formatted.
clear(self, cell)
Clear any formatting from a cell, or range of cells.
This is called before applying the formatter.
For a resizing array function, the cell passed to this clear method is the previous range that was
formatted, allowing arrays to contract without leaving formatting of empty cells behind.
The default implementation clears all formatting, but this may be overridden in a sub-class if more
selective clearing is required.
Parameters cell – Instance of XLCell that should have its formatting cleared.
apply_style(cell, style)
Apply a style dictionary to an instance of an XLCell.
This can be used to apply basic styling to a cell without having to use XLCell.to_range and
win32com.
The style dictionary may have the following entries:
• interior_color: Interior color of the cell (see Formatter.rgb).
• text_color: Text color (see Formatter.rgb).
4.6. Cell Formatting 153
PyXLL User Guide, Release 5.3.0
• bold: Set to True for bold text, False otherwise.
• italic: Set to True for italic text, False otherwise.
• font_size: Font size in points (int).
• number_format: Excel number format to apply to the cell.
• auto_fit: Auto-fit to the content of the cells. May be True (fit column width), False (don’t fit),
‘columns’ (fit column width), ‘rows’ (fit row width), ‘both’ (fit column and row width).
Parameters
• cell – Instance of XLCell to apply the style to.
• style – Dict specifying the style to be applied.
rgb(red, green, blue)
Return a color value understood by Excel.
Excel colors are in the form ‘BGR’ instead of the usual ‘RGB’ and this utility method constructs color
values from their RGB components.
Parameters
• red (int) – Red component between 0 and 255.
• green (int) – Green component between 0 and 255.
• blue (int) – Blue component between 0 and 255.
4.6.2 DataFrameFormatter
class DataFrameFormatter(Formatter)
Formatter for DataFrames.
For each argument expecting a Formatter, a dict may also be provided.
When a list of formatters is used (e.g. for the row or index formatters) the formatters will cycle through the
list and repeat. For example, to format a table with striped rows only two row formatters are needed.
__init__(rows=default_row_formatters, header=default_header_formatter, in-
dex=default_index_formatter, columns=none, conditional_formatters=None,
**kwargs)
Parameters
• rows – Formatter or list of formatters to be applied to the rows.
• header – Formatter to use for the header column names.
• index – Formatter or list of formatters to be applied to the index.
• columns – Dict of column name to formatter or list of formatters to be applied for
specific columns (in addition to the any row formatters).
• conditional_formatters – A list of :py:class ConditionalFormatters to be
applied in order after any other formatting has been applied.
• kwargs – Additional Formatter kwargs that will affect the entire formatted range.
default_row_formatters
List of two default formatters for alternating row formats.
default_header_formatter
Default formatter used for any column headers.
default_index_formatter
Default formatter used for any index columns.
4.6. Cell Formatting 154
PyXLL User Guide, Release 5.3.0
4.6.3 DateFormatter
class DateFormatter(Formatter)
Formatter for dates, times and datetimes.
All formats are in the standard Python datetime format.
This formatter tests the values and applies the relevant number format according to the type.
__init__(date_format='%Y-%m-%d', time_format='%H:%M:%S', datetime_format=None)
Parameters
• date_format – Format used for date values.
• time_format – Format used for time values.
• datetime_format – Format used for datetime values.
If datetime_format is not specified then it is constructed by combining date_format and time_format.
4.6.4 ConditionalFormatter
class ConditionalFormatter(ConditionalFormatterBase)
Conditional formatter for use with DataFrameFormatter.
This can be used to apply formatting to a DataFrame that is conditional on the values in the DataFrame.
The ConditionalFormatter works by evaluating an expression on the DataFrame using DataFrame.eval.
Rows where the expression returns True have a formatter applied to them. The formatting can be further
restricted to one or more columns.
To apply different formats to different values use multiple ConditionalFormatters.
__init__(expr, formatter, columns=None, **kwargs)
Parameters
• expr – Boolean expression for selecting rows to which the formatter will apply.
• formatter – Formatter that will be applied to the selected cells.
• columns – Column name or list of columns that the formatter will be applied to. May
also be a callable, in which case it should accept a DataFrame and return a column or
list of columns.
• kwargs – Additional arguments passed to DataFrame.eval when selecting the
rows to apply the formatter to.
The following example shows how to color rows green where column A is greater than 0 and red where
column A is less than 0.
from pyxll import DataFrameFormatter, ConditionalFormatter, Formatter, xl_func
green_formatter = Formatter(interior_color=Formatter.rgb(0, 0xff, 0))
red_formatter = Formatter(interior_color=Formatter.rgb(0xff, 0, 0))
a_gt_zero = ConditionalFormatter("A > 0", formatter=green_formatter)
a_lt_zero = ConditionalFormatter("A < 0", formatter=red_formatter)
df_formatter = DataFrameFormatter(conditional_formatters=[
a_gt_zero,
a_lt_zero])
@xl_func("var x: dataframe", formatter=df_formatter, auto_resize=True)
def load_dataframe(x):
(continues on next page)
4.6. Cell Formatting 155
PyXLL User Guide, Release 5.3.0
(continued from previous page)
# load a dataframe with column 'A'
return df
4.6.5 ConditionalFormatterBase
class ConditionalFormatterBase
Base class for conditional formatters.
Subclass this class to create your own custom conditional formatters for use with
DataFrameFormatter.
get_formatters(self, df )
Parameters df – DataFrame to be formatted.
Returns
A new DataFrame with the same index and columns as ‘df’ with values being instances
of the Formatter class.
Where no formatting is to be applied the returned DataFrame value should be None.
4.7 Event Handlers
These decorators enable the user to register functions that will be called when certain events occur in the PyXLL
addin.
• xl_on_open
• xl_on_reload
• xl_on_close
• xl_license_notifier
4.7.1 xl_on_open
xl_on_open(func)
Decorator for callbacks that should be called after PyXLL has been opened and the user modules have been
imported.
The callback takes a list of tuples of three three items: (modulename, module, exc_info)
When a module has been loaded successfully, exc_info is None.
When a module has failed to load, module is None and exc_info is the exception information
(exc_type, exc_value, exc_traceback).
Example usage:
from pyxll import xl_on_open
@xl_on_open
def on_open(import_info):
for modulename, module, exc_info in import_info:
if module is None:
exc_type, exc_value, exc_traceback = exc_info
... do something with this error ...
4.7. Event Handlers 156
PyXLL User Guide, Release 5.3.0
4.7.2 xl_on_reload
xl_on_reload(func)
Decorator for callbacks that should be called after a reload is attempted.
The callback takes a list of tuples of three three items: (modulename, module, exc_info)
When a module has been loaded successfully, exc_info is None.
When a module has failed to load, module is None and exc_info is the exception information
(exc_type, exc_value, exc_traceback).
Example usage:
from pyxll import xl_on_reload, xlcCalculateNow
@xl_on_reload
def on_reload(reload_info):
for modulename, module, exc_info in reload_info:
if module is None:
exc_type, exc_value, exc_traceback = exc_info
... do something with this error ...
# recalcuate all open workbooks
xlcCalculateNow()
4.7.3 xl_on_close
xl_on_close(func)
Decorator for registering a function that will be called when Excel is about to close.
This can be useful if, for example, you’ve created some background threads and need to stop them cleanly
for Excel to shutdown successfully. You may have other resources that you need to release before Excel
closes as well, such as COM objects, that would prevent Excel from shutting down. This callback is the
place to do that.
This callback is called when the user goes to close Excel. However, they may choose to then cancel the
close operation but the callback will already have been called. Therefore you should ensure that anything
you clean up here will be re-created later on-demand if the user decides to cancel and continue using Excel.
To get a callback when Python is shutting down, which occurs when Excel is finally quitting, you should
use the standard atexit Python module. Python will not shut down in some circumstances (e.g. when a
non-daemonic thread is still running or if there are any handles to Excel COM objects that haven’t been
released) so a combination of the two callbacks is sometimes required.
Example usage:
from pyxll import xl_on_close
@xl_on_close
def on_close():
print("closing...")
4.7. Event Handlers 157
PyXLL User Guide, Release 5.3.0
4.7.4 xl_license_notifier
xl_license_notifier(func)
Decorator for registering a function that will be called when PyXLL is starting up and checking the license
key.
It can be used to alert the user or to email a support or IT person when the license is coming up for renewal,
so a new license can be arranged in advance to minimize any disruption.
The registered function takes 4 arguments: string name, datetime.date expdate, int days_left, bool
is_perpetual.
If the license is perpetual (doesn’t expire) expdate will be the end date of the maintenance agreement (when
maintenance builds are available until) and days_left will be the days between the PyXLL build date and
expdate.
Example usage:
from pyxll import xl_license_notifier
@xl_license_notifier
def my_license_notifier(name, expdate, days_left, is_perpetual):
if days_left < 30:
... do something here...
4.8 Excel C API Functions
PyXLL exposes certain functions from the Excel C API. These mostly should only be called from a worksheet,
menu or macro functions, and some should only be called from macro-sheet equivalent functions
1
.
Functions that can only be called from a macro or menu can be called from elsewhere using async_call. This
allows these C API functions to be called as async_call schedules a function call on Excel’s main thread and
in a macro context.
• xlfCaller
• xlfSheetId
• xlfGetWorkspace
• xlfGetWorkbook
• xlfGetWindow
• xlfWindows
• xlfVolatile
• xlcAlert
• xlcCalculation
• xlcCalculateNow
• xlcCalculateDocument
• xlAsyncReturn
• xlAbort
• xlSheetNm
1
A macro sheet equivalent function is a function exposed using xl_func with macro=True.
4.8. Excel C API Functions 158
PyXLL User Guide, Release 5.3.0
4.8.1 xlfCaller
xlfCaller()
Returns calling cell as an XLCell instance.
Callable from any function, but most properties of XLCell are only accessible from macro sheet equivalent
functions
1
4.8.2 xlfSheetId
xlSheetId(sheet_name)
Returns integer sheet id from a sheet name (e.g. ‘[Book1.xls]Sheet1’)
4.8.3 xlfGetWorkspace
xlfGetWorkspace(arg_num)
Parameters arg_num (int) – number of 1 to 72 specifying the type of workspace information
to return
Returns depends on arg_num
4.8.4 xlfGetWorkbook
xlfGetWorkbook(arg_num workbook=None)
Parameters
• arg_num (int) – number from 1 to 38 specifying the type of workbook information
to return
• workbook (string) – workbook name
Returns depends on arg_num
4.8.5 xlfGetWindow
xlfGetWindow(arg_num, window=None)
Parameters
• arg_num (int) – number from 1 to 39 specifying the type of window information to
return
• window (string) – window name
Returns depends on arg_num
4.8. Excel C API Functions 159
PyXLL User Guide, Release 5.3.0
4.8.6 xlfWindows
xlfWindows(match_type=0, mask=None)
Parameters
• match_type (int) – a number from 1 to 3 specifying the type of windows to match
1 (or omitted) = non-add-in windows
2 = add-in windows
3 = all windows
• mask (string) – window name mask
Returns list of matching window names
4.8.7 xlfVolatile
xlfVolatile(volatile)
Parameters volatile (bool) – boolean indicating whether the calling function is volatile or
not.
Usually it is better to declare a function as volatile via the xl_func decorator. This function can be used
to make a function behave as a volatile or non-volatile function regardless of how it was declared, which
can be useful in some cases.
Callable from a macro equivalent function only
1
4.8.8 xlcAlert
xlcAlert(alert)
Pops up an alert window.
Callable from a macro or menu function only
2
Parameters alert (string) – text to display
4.8.9 xlcCalculation
xlcCalculation(calc_type)
set the calculation type to automatic or manual.
Callable from a macro or menu function only
2
Parameters calc_type (int) – xlCalculationAutomatic
or xlCalculationSemiAutomatic
or xlCalculationManual
xlCalculationAutomatic = 1
xlCalculationSemiAutomatic = 2
xlCalculationManual = 3
2
Some Excel functions can only be called from a macro or menu. To call them from another context use async_call.
4.8. Excel C API Functions 160
PyXLL User Guide, Release 5.3.0
4.8.10 xlcCalculateNow
xlcCalculateNow()
recalculate all cells that have been marked as dirty (i.e. have dependencies that have changed) or that are
volatile functions.
Equivalent to pressing F9.
Callable from a macro or menu function only
2
4.8.11 xlcCalculateDocument
xlcCalculateDocument()
recalculate all cells that have been marked as dirty (i.e. have dependencies that have changed) or that are
volatile functions for the current worksheet only
Callable from a macro or menu function only
2
4.8.12 xlAsyncReturn
xlAsyncReturn(handle, value)
Used by asynchronous functions to return the result to Excel see Asynchronous Functions
This function can be called from any thread and doesn’t have to be from a macro sheet equivalent function
Parameters
• handle (object) – async handle passed to the worksheet function
• value (object) – value to return to Excel
4.8.13 xlAbort
xlAbort(retain=True)
Yields the processor to other tasks in the system and checks whether the user has pressed ESC to cancel a
macro or workbook recalculation.
Parameters retain (bool) – If False and a break condition has been set it is reset, otherwise
don’t change the break condition.
Returns True if the user has pressed ESC, False otherwise.
4.8.14 xlSheetNm
xlSheetNm(sheet_id)
Returns sheet name from a sheet id (as returned by xlSheetId or XLCell.sheet_id).
xlfGetDocument(arg_num[, name ])
Parameters
• arg_num (int) – number from 1 to 88 specifying the type of document information
to return
• name (string) – sheet or workbook name
Returns depends on arg_num
4.8. Excel C API Functions 161
PyXLL User Guide, Release 5.3.0
4.9 Other Classes
• RTD
• XLCell
• XLRect
• XLAsyncHandle
• ErrorContext
4.9.1 RTD
class RTD
RTD is a base class that should be derived from for use by functions wishing to return real time ticking data
instead of a static value.
See Real Time Data for more information.
value
Current value. Setting the value notifies Excel that the value has been updated and the new value will
be shown when Excel refreshes.
connect(self )
Called when Excel connects to this RTD instance, which occurs shortly after an Excel function has
returned an RTD object.
May be overridden in the sub-class.
@Since PyXLL 4.2.0: May be an async method.
disconnect(self )
Called when Excel no longer needs the RTD instance. This is usually because there are no longer any
cells that need it or because Excel is shutting down.
May be overridden in the sub-class.
@Since PyXLL 4.2.0: May be an async method.
set_error(self, exc_type, exc_value, exc_traceback)
Update Excel with an error. E.g.:
def update(self):
try:
self.value = get_new_value()
except:
self.set_error(
*
sys.exc_info())
4.9.2 XLCell
class XLCell
XLCell represents the data and metadata for a cell (or range of cells) in Excel.
XLCell instances are passed as an xl_cell argument to a function registered with xl_func, or may be
constructed using from_range.
Some of the properties of XLCell instances can only be accessed if the calling function has been registered
as a macro sheet equivalent function
1
.
1
A macro sheet equivalent function is a function exposed using xl_func with macro=True.
4.9. Other Classes 162
PyXLL User Guide, Release 5.3.0
Example usage:
from pyxll import xl_func
@xl_func("xl_cell cell: string", macro=True)
def xl_cell_test(cell):
return "[value=%s, address=%s, formula=%s, note=%s]" % (
cell.value,
cell.address,
cell.formula,
cell.note)
value
Get or set the value of the cell.
The type conversion when getting or setting the cell content is determined by the type passed to
options. If no type is specified then the type conversion will be done using the var type.
Must be called from a macro or macro sheet equivalent function
1
address
String representing the address of the cell, or None if a value was passed to the function and not a cell
reference.
Must be called from a macro or macro sheet equivalent function
1
formula
Formula of the cell as a string, or None if a value was passed to the function and not a cell reference
or if the cell has no formula.
Must be called from a macro or macro sheet equivalent function
1
note
Note on the cell as a string, or None if a value was passed to the function and not a cell reference
or if the cell has no note.
Must be called from a macro or macro sheet equivalent function
1
sheet_name
Name of the sheet this cell belongs to.
sheet_id
Integer id of the sheet this cell belongs to.
rect
XLRect instance with the coordinates of the cell.
is_calculated
True or False indicating whether the cell has been calculated or not. In almost all cases this will always
be True as Excel will automatically have recalculated the cell before passing it to the function.
options(self, type=None, auto_resize=None, type_kwargs=None)
Sets the options on the XLCell instance.
Parameters
• type – Data type to use when converting values to or from Excel. The default type is
var, but any recognized types may be used, including object for getting or setting
cached objects.
• auto_resize – When setting the cell value in Excel, if auto_resize is set and the
value is an array, the cell will be expanded automatically to fit the size of the Python
array.
• type_kwargs – If setting type, type_kwargs can also be set as the options for that
type.
4.9. Other Classes 163
PyXLL User Guide, Release 5.3.0
Returns self. The cell options are modified and the same instance is returned, for easier
method chaining.
Example usage:
cell.options(type='dataframe', auto_resize=True).value = df
from_range(range)
Static method to construct an XLCell from an Excel Range instance. The ‘Range’ class is part of the
Excel Object Model, and can be obtained via xl_app.
See Python as a VBA Replacement.
By getting an XLCell from a Range, values can be set in Excel using PyXLL type converters and
object cache.
Parameters range – Excel Range object obtained via xl_app.
Example usage:
xl = xl_app()
range = xl.Selection
cell = XLCell.from_range(range)
cell.options(type='object').value = x
Must be called from a macro or macro sheet equivalent function
1
to_range(self, com_wrapper=None)
Return an Excel Range COM object using the COM package specified.
Parameters com_package – COM package to use to return the COM Range object.
com_package may be any of:
• win32com (default)
• comtypes
• xlwings
@Since PyXLL 4.4.0
4.9.3 XLRect
class XLRect
XLRect instances are accessed via XLCell.rect to get the coordinates of the cell.
first_row
First row of the range as an integer.
last_row
Last row of the range as an integer.
first_col
First column of the range as an integer.
last_col
Last column of the range as an integer.
4.9. Other Classes 164
PyXLL User Guide, Release 5.3.0
4.9.4 XLAsyncHandle
class XLAsyncHandle
XLAsyncHandle instances are passed to Asynchronous Functions as the async_handle argument.
They are passed to xlAsyncReturn to return the result from an asynchronous function.
set_value(value)
Set the value on the handle and return it to Excel.
Equivalent to xlAsyncReturn.
@Since PyXLL 4.2.0
set_error(exc_type, exc_value, exc_traceback)
Return an error to Excel.
@Since PyXLL 4.2.0
Example usage:
from pyxll import xl_func
import threading
import sys
@xl_func("async_handle h, int x")
def async_func(h, x):
def thread_func(h, x):
try:
result = do_calculation(x)
h.set_value(result)
except:
result.set_error(
*
sys.exc_info())
thread = threading.Thread(target=thread_func, args=(h, x))
thread.start()
New in PyXLL 4.2
For Python 3.5.1 and later, asynchronous UDFs can be simplified by simply using the async keyword on the
function declaration and dropping the async_handle argument.
Async functions written in this way run in an asyncio event loop on a background thread.
4.9.5 ErrorContext
class ErrorContext
An ErrorContext is passed to any error handler specified in the pyxll.cfg file.
When an unhandled exception is raised, the error handler is called with a context object and the exception
details.
type
Type of function where the exception occurred.
Can be any of the attributes of the ErrorContext.Type class.
function_name
Name of the function being called when the error occurred.
This may be none if the error was not the result of calling a function (eg when type ==
ErrorContext.Type.IMPORT).
import_errors
Only applicable when type == ErrorContext.Type.IMPORT.
4.9. Other Classes 165
PyXLL User Guide, Release 5.3.0
A list of (modulename, (exc_type, exc_value, exc_traceback)) for all modules
that failed to import.
class ErrorContext.Type
Enum-style type to indicate the origination of the error.
UDF
Used to indicate the error was raised while calling a UDF.
MACRO
Used to indicate the error was raised while calling a macro.
MENU
Used to indicate the error was raised while calling a menu function.
RIBBON
Used to indicate the error was raised while calling a ribbon function.
IMPORT
Used to indicate the error was raised while importing a Python module.
4.9. Other Classes 166
CHAPTER
FIVE
EXAMPLES
5.1 UDF Examples
All examples are included in the PyXLL download.
Plain text version
"""
PyXLL Examples: Worksheet functions
The PyXLL Excel Addin is configured to load one or more
python modules when it's loaded. Functions are exposed
to Excel as worksheet functions by decorators declared in
the pyxll module.
Functions decorated with the xl_func decorator are exposed
to Excel as UDFs (User Defined Functions) and may be called
from cells in Excel.
"""
#
# 1) Basics - exposing functions to Excel
#
#
# xl_func is the main decorator and is used for exposing
# python functions to excel.
#
from pyxll import xl_func
#
# Decorating a function with xl_func is all that's required
# to make it callable in Excel as a worksheet function.
#
@xl_func
def basic_pyxll_function_1(x, y, z):
"""returns (x
*
y)
**
z """
return (x
*
y)
**
z
#
# xl_func takes an optional signature of the function to be exposed to excel.
# There are a number of basic types that can be used in
# the function signature. These include:
# int, float, bool and string
# There are more types that we'll come to later.
#
@xl_func("int x, float y, bool z: float")
(continues on next page)
167
PyXLL User Guide, Release 5.3.0
(continued from previous page)
def basic_pyxll_function_2(x, y, z):
"""if z return x, else return y"""
if z:
# we're returning an integer, but the signature
# says we're returning a float.
# PyXLL will convert the integer to a float for us.
return x
return y
#
# You can change the category the function appears under in
# Excel by using the optional argument 'category'.
#
@xl_func(category="My new PyXLL Category")
def basic_pyxll_function_3(x):
"""docstrings appear as help text in Excel"""
return x
#
# 2) The var type
#
#
# A basic type is the var type. This can represent any
# of the basic types, depending on what type is passed to the
# function, or what type is returned.
#
# When no type information is given the var type is used.
#
@xl_func("var x: string")
def var_pyxll_function_1(x):
"""takes an float, bool, string, None or array"""
# we'll return the type of the object passed to us, pyxll
# will then convert that to a string when it's returned to
# excel.
return type(x)
#
# If var is the return type. PyXll will convert it to the
# most suitable basic type. If it's not a basic type and
# no suitable conversion can be found, it will be converted
# to a string and the string will be returned.
#
@xl_func("bool x: var")
def var_pyxll_function_2(x):
"""if x return string, else a number"""
if x:
return "var can be used to return different types"
return 123.456
#
# 3) Date and time types
#
#
(continues on next page)
5.1. UDF Examples 168
PyXLL User Guide, Release 5.3.0
(continued from previous page)
# There are three date and time types: date, time, datetime
#
# Excel represents dates and times as floating point numbers.
# The pyxll datetime types convert the excel number to a
# python datetime.date, datetime.time and datetime.datetime
# object depending on what type you specify in the signature.
#
# dates and times may be returned using their type as the return
# type in the signature, or as the var type.
#
import datetime
@xl_func("date x: string")
def datetime_pyxll_function_1(x):
"""returns a string description of the date"""
return "type=%s, date=%s" % (type(x), x)
@xl_func("time x: string")
def datetime_pyxll_function_2(x):
"""returns a string description of the time"""
return "type=%s, time=%s" % (type(x), x)
@xl_func("datetime x: string")
def datetime_pyxll_function_3(x):
"""returns a string description of the datetime"""
return "type=%s, datetime=%s" % (type(x), x)
@xl_func("datetime[][] x: datetime")
def datetime_pyxll_function_4(x):
"""returns the max datetime"""
m = datetime.datetime(1900, 1, 1)
for row in x:
m = max(m, max(row))
return m
#
# 4) xl_cell
#
# The xl_cell type can be used to receive a cell
# object rather than a plain value. The cell object
# has the value, address, formula and note of the
# reference cell passed to the function.
#
# The function must be a macro sheet equivalent function
# in order to access the value, address, formula and note
# properties of the cell.
#
@xl_func("xl_cell cell : string", macro=True)
def xl_cell_example(cell):
"""a cell has a value, address, formula and note"""
return "[value=%s, address=%s, formula=%s, note=%s]" % (cell.value,
cell.address,
cell.formula,
cell.note)
(continues on next page)
5.1. UDF Examples 169
PyXLL User Guide, Release 5.3.0
(continued from previous page)
#
# 5) recalc_on_open
#
# Functions can be marked to be recalculated when the workbook opens.
# With this set, when the workbook is saved some metadata is written
# with the workbook and then the cell containing the function is marked
# as dirty when the workbook is loaded, causing it to be recalculated.
#
@xl_func(recalc_on_open=True)
def recalc_on_open_test():
now = datetime.datetime.now()
return now.strftime("Updated at %Y-%m-%d %H:%M:%S")
#
# 6) Formatting
#
# PyXLL can automatically apply a formatter to the range the function is called
˓→from.
#
from pyxll import Formatter
date_formatter = Formatter(number_format="YYYY-mm-dd")
@xl_func(formatter=date_formatter, recalc_on_open=True)
def formatted_datetime_pyxll_function():
return datetime.date.today()
# Formatters can be combined by adding them
highlight_formatter = Formatter(interior_color=Formatter.rgb(255, 255, 0),
˓→bold=True)
@xl_func(formatter=date_formatter + highlight_formatter, recalc_on_open=True)
def formatted_datetime_pyxll_function_2():
return datetime.date.today()
5.2 Pandas Examples
All examples are included in the PyXLL download.
Plain text version
"""
PyXLL Examples: Pandas
This module contains example functions that show how pandas DataFrames and Series
can be passed to and from Excel to Python functions using PyXLL.
Pandas needs to be installed for this example to work correctly.
See also the included examples.xlsx file.
"""
from pyxll import xl_func, DataFrameFormatter
@xl_func(volatile=True)
def pandas_is_installed():
"""returns True if pandas is installed"""
try:
import pandas
(continues on next page)
5.2. Pandas Examples 170
PyXLL User Guide, Release 5.3.0
(continued from previous page)
return True
except ImportError:
return False
# The DataFrameFormatter object can be used for format DataFrames returned to
˓→Excel from PyXLL.
df_formatter = DataFrameFormatter()
@xl_func("int, int: dataframe<index=True>",
auto_resize=True,
formatter=df_formatter)
def random_dataframe(rows, columns):
"""
Creates a DataFrame of random numbers.
:param rows: Number of rows to create the DataFrame with.
:param columns: Number of columns to create the DataFrame with.
"""
import pandas as pa
import numpy as np
data = np.random.rand(rows, columns)
column_names = [chr(ord('A') + x) for x in range(columns)]
df = pa.DataFrame(data, columns=column_names)
return df
@xl_func("dataframe<index=True>, float[], str[], str[]: dataframe<index=True>",
auto_resize=True,
formatter=df_formatter)
def describe_dataframe(df, percentiles=[], include=[], exclude=[]):
"""
Generates descriptive statistics that summarize the central tendency,
˓→dispersion and shape of a dataset's
distribution, excluding NaN values.
:param df: DataFrame to describe.
:param percentiles: The percentiles to include in the output. All should fall
˓→between 0 and 1.
:param include: dtypes to include.
:param exclude: dtypes to exclude.
:return:
"""
# filter out any blanks
percentiles = list(filter(None, percentiles))
include = list(filter(None, include))
exclude = list(filter(None, exclude))
return df.describe(percentiles=percentiles or None,
include=include or None,
exclude=exclude or None)
5.2. Pandas Examples 171
PyXLL User Guide, Release 5.3.0
5.3 Cached Objects Examples
All examples are included in the PyXLL download.
Plain text version
"""
PyXLL Examples: Object Cache Example
This module contains example functions that make use of the PyXLL
object cache.
When Python objects that can't be transformed into a basic type that
Excel can display are returned, PyXLL inserts them into a global
object cache and returns a reference id for the object. When this reference
id is passed to another PyXLL function the object is retrieved from the
cache and passed to the PyXLL function.
PyXLL keeps track of uses of the cached objects and removes items from the
cache when they are no longer needed.
See also the included examples.xlsx file.
"""
from pyxll import xl_func
class MyTestClass(object):
"""A basic class for testing the cached_object type"""
def __init__(self, x):
self.__x = x
def __str__(self):
return "%s(%s)" % (self.__class__.__name__, self.__x)
@xl_func("var: object")
def cached_object_return_test(x):
"""returns an instance of MyTestClass"""
return MyTestClass(x)
@xl_func("object: string")
def cached_object_arg_test(x):
"""takes a MyTestClass instance and returns a string"""
return str(x)
class MyDataGrid(object):
"""
A second class for demonstrating cached_object types.
This class is constructed with a grid of data and has
some basic methods which are also exposed as worksheet
functions.
"""
def __init__(self, grid):
self.__grid = grid
def sum(self):
"""returns the sum of the numbers in the grid"""
total = 0
(continues on next page)
5.3. Cached Objects Examples 172
PyXLL User Guide, Release 5.3.0
(continued from previous page)
for row in self.__grid:
total += sum(row)
return total
def __len__(self):
total = 0
for row in self.__grid:
total += len(row)
return total
def __str__(self):
return "%s(%d values)" % (self.__class__.__name__, len(self))
@xl_func("float[][]: object")
def make_datagrid(x):
"""returns a MyDataGrid object"""
return MyDataGrid(x)
@xl_func("object: int")
def datagrid_len(x):
"""returns the length of a MyDataGrid object"""
return len(x)
@xl_func("object: float")
def datagrid_sum(x):
"""returns the sum of a MyDataGrid object"""
return x.sum()
@xl_func("object: string")
def datagrid_str(x):
"""returns the string representation of a MyDataGrid object"""
return str(x)
5.4 Custom Type Examples
All examples are included in the PyXLL download.
Plain text version
"""
PyXLL Examples: Custom types
Worksheet functions can use a number of standard types
as shown in the worksheetfuncs example.
It's also possible to define custom types that
can be used in the PyXLL function signatures
as shown by these examples.
For a more complicated custom type example see the
object cache example.
"""
#
(continues on next page)
5.4. Custom Type Examples 173
PyXLL User Guide, Release 5.3.0
(continued from previous page)
# xl_arg_type and xl_return type are decorators that can
# be used to declare types that our excel functions
# can use in addition to the standard types
#
from pyxll import xl_func, xl_arg_type, xl_return_type
#
# 1) Custom types
#
#
# All variables are passed to and from excel as the basic types,
# but it's possible to register conversion functions that will
# convert those basic types to whatever types you like before
# they reach your function, (or after you function returns them
# in the case of returned values).
#
#
# CustomType1 is a very simple class used to demonstrate
# custom types.
#
class CustomType1:
def __init__(self, name):
self.name = name
def greeting(self):
return "Hello, my name is %s" % self.name
#
# To use CustomType1 as an argument to a pyxll function you have to
# register a function to convert from a basic type to our custom type.
#
# xl_arg_type takes two arguments, the new custom type name, and the
# base type.
#
@xl_arg_type("custom1", "string")
def string_to_custom1(name):
return CustomType1(name)
#
# now the type 'custom1' can be used as an argument type
# in a function signature.
#
@xl_func("custom1 x: string")
def customtype_pyxll_function_1(x):
"""returns x.greeting()"""
return x.greeting()
#
# To use CustomType1 as a return type for a pyxll function you have
# to register a function to convert from the custom type to a basic type.
#
# xl_return_type takes two arguments, the new custom type name, and
# the base type.
#
@xl_return_type("custom1", "string")
(continues on next page)
5.4. Custom Type Examples 174
PyXLL User Guide, Release 5.3.0
(continued from previous page)
def custom1_to_string(x):
return x.name
#
# now the type 'custom1' can be used as the return type.
#
@xl_func("custom1 x: custom1")
def customtype_pyxll_function_2(x):
"""check the type and return the same object"""
assert isinstance(x, CustomType1), "expected an CustomType1 object"""
return x
#
# CustomType2 is another example that caches its instances
# so they can be referred to from excel functions.
#
class CustomType2:
__instances__ = {}
def __init__(self, name, value):
self.value = value
self.id = "%s-%d" % (name, id(self))
# overwrite any existing instance with self
self.__instances__[name] = self
def getValue(self):
return self.value
@classmethod
def getInstance(cls, id):
name, unused = id.split("-")
return cls.__instances__[name]
def getId(self):
return self.id
@xl_arg_type("custom2", "string")
def string_to_custom2(x):
return CustomType2.getInstance(x)
@xl_return_type("custom2", "string")
def custom2_to_string(x):
return x.getId()
@xl_func("string name, float value: custom2")
def customtype_pyxll_function_3(name, value):
"""returns a new CustomType2 object"""
return CustomType2(name, value)
@xl_func("custom2 x: float")
def customtype_pyxll_function_4(x):
"""returns x.getValue()"""
return x.getValue()
(continues on next page)
5.4. Custom Type Examples 175
PyXLL User Guide, Release 5.3.0
(continued from previous page)
#
# custom types may be base types of other custom types, as
# long as the ultimate base type is a basic type.
#
# This means you can chain conversion functions together.
#
class CustomType3:
def __init__(self, custom2):
self.custom2 = custom2
def getValue(self):
return self.custom2.getValue()
*
2
@xl_arg_type("custom3", "custom2")
def custom2_to_custom3(x):
return CustomType3(x)
@xl_return_type("custom3", "custom2")
def custom3_to_custom2(x):
return x.custom2
#
# when converting from an excel cell to a CustomType3 object,
# the string will first be used to get a CustomType2 object
# via the registed function string_to_custom2, and then
# custom2_to_custom3 will be called to get the final
# CustomType3 object.
#
@xl_func("custom3 x: float")
def customtype_pyxll_function_5(x):
"""return x.getValue()"""
return x.getValue()
5.5 Menu Examples
All examples are included in the PyXLL download.
Plain text version
"""
PyXLL Examples: Menus
The PyXLL Excel Addin is configured to load one or more
python modules when it's loaded.
Menus can be added to Excel via the pyxll xl_menu decorator.
"""
import traceback
import logging
_log = logging.getLogger(__name__)
# the webbrowser module is used in an example to open the log file
(continues on next page)
5.5. Menu Examples 176
PyXLL User Guide, Release 5.3.0
(continued from previous page)
try:
import webbrowser
except ImportError:
_log.warning("
***
webbrowser could not be imported
***
")
_log.warning("
***
the menu examples will not work correctly
***
")
import os
#
# 1) Basics - adding a menu items to Excel
#
#
# xl_menu is the decorator used for addin menus to Excel.
#
from pyxll import xl_menu, get_config, xl_app, xl_version, get_last_error, xlcAlert
#
# The only required argument is the menu item name.
# The example below will add a new menu item to the
# addin's default menu.
#
@xl_menu("Example Menu Item 1")
def on_example_menu_item_1():
xlcAlert("Hello from PyXLL")
#
# menu items are normally sorted alphabetically, but the order
# keyword can be used to influence the ordering of the items
# in a menu.
#
# The default value for all sort keyword arguments is 0, so positive
# values will result in the item appearing further down the list
# and negative numbers result in the item appearing further up.
#
@xl_menu("Another example menu item", order=1)
def on_example_menu_item_2():
xlcAlert("Hello again from PyXLL")
#
# It's possible to add items to menus other than the default menu.
# The example below creates a new menu called 'My new menu' with
# one item 'Click me' in it.
#
# The menu_order keyword is optional, but may be used to influence
# the order that the custom menus appear in.
#
@xl_menu("Click me", menu="PyXLL example menu", menu_order=1)
def on_example_menu_item_3():
xlcAlert("Adding multiple menus is easy")
#
# 2) Sub-menus
#
# it's possible to add sub-menus just by using the sub_menu
# keyword argument. The example below adds a new sub menu
# 'Sub Menu' to the default menu.
(continues on next page)
5.5. Menu Examples 177
PyXLL User Guide, Release 5.3.0
(continued from previous page)
#
# The order keyword argument affects where the sub menu will
# appear in the parent menu, and the sub_order keyword argument
# affects where the item will appear in the sub menu.
#
@xl_menu("Click me", sub_menu="More Examples", order=2)
def on_example_submenu_item_1():
xlcAlert("Sub-menus can be created easily with PyXLL")
#
# When using Excel 2007 and onwards the Excel functions accept unicode strings
#
@xl_menu("Unicode Test", sub_menu="More Examples")
def on_unicode_test():
xlcAlert(u"\u01d9ni\u0186\u020dde")
#
# A simple menu item to show how to get the PyXLL config
# object and open the log file.
#
@xl_menu("Open log file", order=3)
def on_open_logfile():
# the PyXLL config is accessed as a ConfigParser.ConfigParser object
config = get_config()
if config.has_option("LOG", "path") and config.has_option("LOG", "file"):
path = os.path.join(config.get("LOG", "path"), config.get("LOG", "file"))
webbrowser.open("file://%s" % path)
#
# If a cell returns an error it is written to the log file
# but can also be retrieved using 'get_last_error'.
# This menu item displays the last error captured for the
# current active cell.
#
@xl_menu("Show last error")
def show_last_error():
selection = xl_app().Selection
exc_type, exc_value, exc_traceback = get_last_error(selection)
if exc_type is None:
xlcAlert("No error found for the selected cell")
return
msg = "".join(traceback.format_exception(exc_type, exc_value, exc_traceback))
if xl_version() < 12:
msg = msg[:254]
xlcAlert(msg)
5.5. Menu Examples 178
PyXLL User Guide, Release 5.3.0
5.6 Macros and Excel Scripting
All examples are included in the PyXLL download.
Plain text version
"""
PyXLL Examples: Automation
PyXLL worksheet and menu functions can call back into Excel
using the Excel COM API
*
.
In addition to the COM API there are a few Excel functions
exposed via PyXLL that allow you to query information about
the current state of Excel without using COM.
Excel uses different security policies for different types
of functions that are registered with it. Depending on
the type of function, you may or may not be able to make
some calls to Excel.
Menu functions and macros are registered as 'commands'.
Commands are free to call back into Excel and make changes to
documents. These are equivalent to the VBA Sub routines.
Worksheet functions are registered as 'functions'. These
are limited in what they can do. You will be able to
call back into Excel to read values, but not change
anything. Most of the Excel functions exposed via PyXLL
will not work in worksheet functions. These are equivalent
to VBA Functions.
There is a third type of function - macro-sheet equivalent
functions. These are worksheet functions that are allowed to
do most things a macro function (command) would be allowed
to do. These shouldn't be used lightly as they may break
the calculation dependencies between cells if not
used carefully.
*
Excel COM support was added in Office 2000. If you are
using an earlier version these COM examples won't work.
"""
import pyxll
from pyxll import xl_menu, xl_func, xl_macro
import logging
_log = logging.getLogger(__name__)
#
# Getting the Excel COM object
#
# PyXLL has a function 'xl_app'. This returns the Excel application
# instance either as a win32com.client.Dispatch object or a
# comtypes object (which com package is used may be set in the
# config file). The default is to use win32com.
#
# It is better to use this than
# win32com.client.Dispatch("Excel.Application")
# as it will always be the correct handle - ie the handle
# to the correct instance of Excel.
#
(continues on next page)
5.6. Macros and Excel Scripting 179
PyXLL User Guide, Release 5.3.0
(continued from previous page)
# For more information on win32com see the pywin32 project
# on sourceforge.
#
# The Excel object model is the same from COM as from VBA
# so usually it's straightforward to write something
# in python if you know how to do it in VBA.
#
# For more information about the Excel object model
# see MSDN or the object browser in the Excel VBA editor.
#
from pyxll import xl_app
#
# A simple example of a menu function that modifies
# the contents of the selected range.
#
@xl_menu("win32com test", sub_menu="More Examples")
def win32com_menu_test():
# get the current selected range and set some text
selection = xl_app().Selection
selection.Value = "Hello!"
pyxll.xlcAlert("Some text has been written to the current cell")
#
# Macros can also be used to call back into Excel when
# a control is activated.
#
# These work in the same way as VBA macros, you just assign
# them to the control in Excel by name.
#
@xl_macro
def button_example():
xl = xl_app()
range = xl.Range("button_output")
range.Value = range.Value + 1
@xl_macro
def checkbox_example():
xl = xl_app()
check_box = xl.ActiveSheet.CheckBoxes(xl.Caller)
if check_box.Value == 1:
xl.Range("checkbox_output").Value = "CHECKED"
else:
xl.Range("checkbox_output").Value = "Click the check box"
@xl_macro
def scrollbar_example():
xl = xl_app()
caller = xl.Caller
scrollbar = xl.ActiveSheet.ScrollBars(xl.Caller)
xl.Range("scrollbar_output").Value = scrollbar.Value
#
# Worksheet functions can also call back into Excel.
#
# The function 'schedule_call' must be used to do the
(continues on next page)
5.6. Macros and Excel Scripting 180
PyXLL User Guide, Release 5.3.0
(continued from previous page)
# actual work of calling back into Excel after Excel has
# finished calculating. Otherwise Excel may lock waiting for
# the function to complete before allowing the COM object
# to modify the sheet, which will cause a dead-lock.
#
# To be able to call xlfCaller from the worksheet function,
# the function must be declared as a macro sheet equivalent
# function by passing macro=True to xl_func.
#
# If your function modifies the Excel worksheet it may trigger
# a recalculation, and so you have to take care not to
# cause an infinite loop that will hang Excel.
#
# Accessing the 'address' property of the XLCell returned
# by xlfCaller requires this function to be a macro sheet
# equivalent function.
#
@xl_func(macro=True)
def automation_example(rows, cols, value):
"""copies value to a range of rows x cols below the calling cell"""
# Get the address of the calling cell using xlfCaller
caller = pyxll.xlfCaller()
address = caller.address
# The update is done asynchronously so as not to block Excel by
# updating the worksheet from a worksheet function
def update_func():
# Get the Excel.Application COM object
xl = xl_app()
# Get an Excel.Range object from the XLCell instance
range = caller.to_range(com_package="win32com")
# get the cell below and expand it to rows x cols
range = xl.Range(range.Resize(2, 1), range.Resize(rows+1, cols))
# and set the range's value
range.Value = value
# kick off the asynchronous call the update function
pyxll.schedule_call(update_func)
return address
5.7 Event Handler Examples
All examples are included in the PyXLL download.
Plain text version
"""
PyXLL Examples: Callbacks
The PyXLL Excel Addin is configured to load one or more
python modules when it's loaded.
Moldules can register callbacks with PyXLL that will be
(continues on next page)
5.7. Event Handler Examples 181
PyXLL User Guide, Release 5.3.0
(continued from previous page)
called at various times to inform the user code of
certain events.
"""
from pyxll import xl_on_open, \
xl_on_reload, \
xl_on_close, \
xl_license_notifier, \
xlcAlert, \
xlcCalculateNow
import logging
_log = logging.getLogger(__name__)
@xl_on_open
def on_open(import_info):
"""
on_open is registered to be called by PyXLL when the addin
is opened via the xl_on_open decorator.
This happens each time Excel starts with PyXLL installed.
"""
# Check to see which modules didn't import correctly.
for modulename, module, exc_info in import_info:
if module is None:
exc_type, exc_value, exc_traceback = exc_info
_log.error("Error loading '%s' : %s" % (modulename, exc_value))
@xl_on_reload
def on_reload(import_info):
"""
on_reload is registered to be called by PyXLL whenever a
reload occurs via the xl_on_reload decorator.
"""
# Check to see if any modules didn't import correctly.
errors = 0
for modulename, module, exc_info in import_info:
if module is None:
exc_type, exc_value, exc_traceback = exc_info
_log.error("Error loading '%s' : %s" % (modulename, exc_value))
errors += 1
# Report if everything reloaded OK.
# If there are errors they will be dealt with by the error_handler.
if errors == 0:
xlcAlert("Everything reloaded OK!\n\n(Message from callbacks.py example)")
# Recalculate all open workbooks.
xlcCalculateNow()
@xl_on_close
def on_close():
"""
on_close will get called as Excel is about to close.
This is a good time to clean up any globals and stop
any background threads so that the python interpretter
can be closed down cleanly.
The user may cancel Excel closing after this has been
(continues on next page)
5.7. Event Handler Examples 182
PyXLL User Guide, Release 5.3.0
(continued from previous page)
called, so your code should make sure that anything
that's been cleaned up here will get recreated again
if it's needed.
"""
_log.info("callbacks.on_close: PyXLL is closing")
@xl_license_notifier
def license_notifier(name, expdate, days_left, is_perpetual):
"""
license_notifier will be called when PyXLL is starting up, after
it has read the config and verified the license.
If there is no license name will be None and days_left will be less than 0.
"""
if days_left >= 0 or is_perpetual:
_log.info("callbacks.license_notifier: "
"This copy of PyXLL is licensed to %s" % name)
if not is_perpetual:
_log.info("callbacks.license_notifier: "
"%d days left before the license expires (%s)" % (days_
˓→left, expdate))
elif expdate is not None:
_log.info("callbacks.license_notifier: License key expired on %s" %
˓→expdate)
else:
_log.info("callbacks.license_notifier: Invalid license key")
5.7. Event Handler Examples 183
INDEX
Symbols
__init__() (CTPBridgeBase method), 143
__init__() (ConditionalFormatter method), 155
__init__() (DataFrameFormatter method), 154
__init__() (DateFormatter method), 155
__init__() (PlotBridgeBase method), 141
A
address (XLCell attribute), 163
apply() (Formatter method), 152
apply_cell() (Formatter method), 153
apply_style() (Formatter method), 153
C
cached_object_count() (in module pyxll), 150
can_export() (PlotBridgeBase method), 141
clear() (Formatter method), 153
close() (CTPBridgeBase method), 143
ConditionalFormatter (class in pyxll), 155
ConditionalFormatterBase (class in pyxll),
156
connect() (RTD method), 162
create_ctp() (in module pyxll), 142
CTPBridgeBase (class in pyxll), 143
D
DataFrameFormatter (class in pyxll), 154
DateFormatter (class in pyxll), 155
default_header_formatter (DataFrameFor-
matter attribute), 154
default_index_formatter (DataFrameFormat-
ter attribute), 154
default_row_formatters (DataFrameFormat-
ter attribute), 154
disconnect() (RTD method), 162
E
ErrorContext (class in pyxll), 165
ErrorContext.Type (class in pyxll), 166
export() (PlotBridgeBase method), 141
F
first_col (XLRect attribute), 164
first_row (XLRect attribute), 164
Formatter (class in pyxll), 152
formula (XLCell attribute), 163
from_range() (XLCell method), 164
function_name (ErrorContext attribute), 165
G
get_config() (in module pyxll), 148
get_dialog_type() (in module pyxll), 148
get_event_loop() (in module pyxll), 150
get_formatters() (ConditionalFormatterBase
method), 156
get_hwnd() (CTPBridgeBase method), 143
get_last_error() (in module pyxll), 148
get_ribbon_xml() (in module pyxll), 151
get_size_hint() (PlotBridgeBase method), 141
get_title() (CTPBridgeBase method), 143
get_type_converter() (in module pyxll), 149
I
IMPORT (ErrorContext.Type attribute), 166
import_errors (ErrorContext attribute), 165
is_calculated (XLCell attribute), 163
L
last_col (XLRect attribute), 164
last_row (XLRect attribute), 164
load_image() (in module pyxll), 150
M
MACRO (ErrorContext.Type attribute), 166
MENU (ErrorContext.Type attribute), 166
N
note (XLCell attribute), 163
O
on_close() (CTPBridgeBase method), 143
on_timer() (CTPBridgeBase method), 144
on_window_closed() (CTPBridgeBase method),
143
on_window_destroyed() (CTPBridgeBase
method), 143
options() (XLCell method), 163
P
plot() (in module pyxll), 140
PlotBridgeBase (class in pyxll), 141
184
PyXLL User Guide, Release 5.3.0
post_attach() (CTPBridgeBase method), 143
pre_attach() (CTPBridgeBase method), 143
process_message() (CTPBridgeBase method),
143
R
rebind() (in module pyxll), 147
rect (XLCell attribute), 163
reload() (in module pyxll), 147
remove_ribbon_tab() (in module pyxll), 152
rgb() (Formatter method), 154
RIBBON (ErrorContext.Type attribute), 166
RTD (class in pyxll), 162
S
schedule_call() (in module pyxll), 145
set_error() (RTD method), 162
set_error() (XLAsyncHandle method), 165
set_ribbon_tab() (in module pyxll), 151
set_ribbon_xml() (in module pyxll), 151
set_value() (XLAsyncHandle method), 165
sheet_id (XLCell attribute), 163
sheet_name (XLCell attribute), 163
T
to_range() (XLCell method), 164
translate_accelerator() (CTPBridgeBase
method), 144
type (ErrorContext attribute), 165
U
UDF (ErrorContext.Type attribute), 166
V
value (RTD attribute), 162
value (XLCell attribute), 163
X
xl_app() (in module pyxll), 145
xl_arg() (in module pyxll), 139
xl_arg_type() (in module pyxll), 139
xl_func() (in module pyxll), 135
xl_license_notifier() (in module pyxll), 158
xl_macro() (in module pyxll), 138
xl_menu() (in module pyxll), 137
xl_on_close() (in module pyxll), 157
xl_on_open() (in module pyxll), 156
xl_on_reload() (in module pyxll), 157
xl_return() (in module pyxll), 140
xl_return_type() (in module pyxll), 139
xl_version() (in module pyxll), 147
xlAbort() (in module pyxll), 161
XLAsyncHandle (class in pyxll), 165
xlAsyncReturn() (in module pyxll), 161
xlcAlert() (in module pyxll), 160
xlcCalculateDocument() (in module pyxll), 161
xlcCalculateNow() (in module pyxll), 161
xlcCalculation() (in module pyxll), 160
XLCell (class in pyxll), 162
xlfCaller() (in module pyxll), 159
xlfGetDocument() (in module pyxll), 161
xlfGetWindow() (in module pyxll), 159
xlfGetWorkbook() (in module pyxll), 159
xlfGetWorkspace() (in module pyxll), 159
xlfVolatile() (in module pyxll), 160
xlfWindows() (in module pyxll), 160
XLRect (class in pyxll), 164
xlSheetId() (in module pyxll), 159
xlSheetNm() (in module pyxll), 161
Index 185
