# PyXLL Complete Documentation > This file contains the full text of the PyXLL documentation for use by AI > assistants and language models. For a curated index of key pages see [llms.txt](https://www.pyxll.com/llms.txt). --- ## PyXLL Documentation - The Python Add-In for Microsoft Excel [docs/index.md](https://www.pyxll.com/docs/index.md) ### PyXLL Documentation PyXLL is a Python add-in for Microsoft Excel — write worksheet functions, macros, real-time data feeds, and ribbon toolbars entirely in Python. ### This documentation is for PyXLL Version 5. Other versions are still available: - PyXLL 4 - PyXLL 3 - PyXLL 2 Machine-readable documentation for AI tools is available at llms-full.txt. Migrating from an older version? See the changelog. If you can’t find what you’re looking for, contact us. --- ## Getting Started with PyXLL [docs/introduction.md](https://www.pyxll.com/docs/introduction.md) PyXLL is an Excel add-in that lets you write Excel worksheet functions, macros, and more using Python. This guide takes you from a fresh install to your first working Python function in Excel. ### Install PyXLL Open a command prompt, activate your Python virtual environment if necessary, and run: ```bat pip install pyxll ``` Followed by: ```bat pyxll install ``` and follow the on-screen instructions. - When asked whether you have already downloaded PyXLL, enter n and the installer will download the right version for you. - You do not need a license key. When asked if you have a license key, enter n and the free 30 day trial will begin automatically when the add-in loads. For other installation options (including manual installation) see the installation guide. > **Tip:** pyxll command not recognized? pip install pyxll installs the pyxll command into your Python scripts folder, but if the Python scripts folder isn’t on your path then the command won’t be found. Instead, run python -m pyxll install. This will do the same thing, without needing the pyxll command to be installed on your system path. ### Write your first Excel function Once PyXLL is installed you can expose a Python function to Excel in four steps. #### Step 1: Create a Python module Create a new file, for example my_functions.py, containing a function decorated with @xl_func: ```python from pyxll import xl_func @xl_func def hello(name): """Say hello to someone.""" return "Hello, " + name ``` #### Step 2: Tell PyXLL to load your module Open the PyXLL config file. The config file is the file named pyxll.cfg in the folder where you installed the PyXLL add-in. You can edit this with any text editor, such as notepad, or your Python code editor. If you are not sure where the file is located, you can run pyxll status and that will tell you, or you can run pyxll config to open the config file using the associated editor. The file is split into multiple sections. Look for the [PYTHON] section and in there you will find the pythonpath setting. This is a list of folders where Python will look for modules. Add the folder where you saved your new my_functions.py file so that it can be imported. Next, look for the [PYXLL] section. In that section you will find the modules list. This is the list of Python modules (or packages) that will be loaded by the PyXLL add-in. Add your module name (my_functions) to this list. Note that a Python module name does not include the .py file extension. ```ini [PYTHON] pythonpath = C:\path\to\folder\containing\my_functions [PYXLL] modules = my_functions ``` #### Step 3: Reload PyXLL in Excel In Excel, open the PyXLL menu and select Reload. This picks up your new module without restarting Excel. Each time your open Excel, your configured modules are loaded automatically. > **Tip:** If there are any errors at this stage, check the PyXLL log file By default it is in the logs folder inside your PyXLL installation directory. You can run pyxll status to find where PyXLL is installed if you are not sure where that is. #### Step 4: Call your function from a cell In any worksheet cell type: ```none =hello("World") ``` The worksheet_functions section of the user guide explains the full range of what you can do with @xl_func, including type hints, array functions, and optional arguments. ### Explore the built-in examples The PyXLL installation includes an examples folder with an Excel workbook and Python source files demonstrating all major features — worksheet functions, macros, real-time data, plotting, ribbon toolbars, and more. Open examples.xlsx to see them in action, and read the corresponding .py files to understand how each one works. Use pyxll status if you cannot remember where PyXLL was installed. ### Troubleshooting **pip or the download fails due to a corporate firewall or proxy:** Follow the manual installation instructions instead. **“The ‘pyxll’ command is not recognised” after pip install:** Your Python Scripts folder may not be on your system path. Use python -m pyxll install instead - it works identically. **“pythonXXX.dll not found” when starting Excel:** The PyXLL add-in was built for a different Python version. Let the command-line installer download the correct version by entering n when asked if you have already downloaded PyXLL. **Other issues:** Check the PyXLL log file for the full error and Python stack trace. Search the FAQ or contact us for further help. ### Next Steps - User Guide — complete documentation of all PyXLL features - videos — video tutorials covering installation, worksheet functions, and more - api-reference — full Python API reference --- ## User Guide - The Python Add-In for Microsoft Excel [docs/userguide/index.md](https://www.pyxll.com/docs/userguide/index.md) ### User Guide New to PyXLL? Start with our Quickstart Guide! --- ## Installing PyXLL [docs/userguide/installation/index.md](https://www.pyxll.com/docs/userguide/installation/index.md) 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. --- ## First Time Users [docs/userguide/installation/firsttime.md](https://www.pyxll.com/docs/userguide/installation/firsttime.md) This page has moved. See Getting Started with PyXLL. --- ## PyXLL Command Line Tool [docs/userguide/installation/cli.md](https://www.pyxll.com/docs/userguide/installation/cli.md) The PyXLL command line tool automates tasks around installing, updating and switching between different versions of PyXLL. In order to use the PyXLL command line tool you first need to install it using pip: ```bat >> 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: ```bat >> pip install --upgrade pyxll ``` Many commands take a --user switch, which can be used if installing or managing the Excel add-in on behalf of a different user (3). 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 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. ```bat >> pyxll install [OPTIONS] [PATH] ``` Options: | | | |--- | ---| |--version | Version of PyXLL to install.| |--user | Install the Excel add-in for a different user. (3)| |--debug / -d | Output more information when running the command.| |--install-first | Install the PyXLL add-in as the first add-in to be loaded. (2)| **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 downloaded. 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. ```bat >> pyxll configure [OPTIONS] ``` Options: | | | |--- | ---| |--user | Use the Excel add-in installed for a different user. (3)| |--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. ```bat >> pyxll status [OPTIONS] ``` Options: | | | |--- | ---| |--user | Use the Excel add-in installed for a different user. (3)| |--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. ```bat >> pyxll update [OPTIONS] [PATH] ``` Options: | | | |--- | ---| |--version | Version of PyXLL to update to.| |--user | Update the Excel add-in for a different user. (3)| |--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. - 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 downloaded. 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. ```bat >> pyxll activate [OPTIONS] [PATH] ``` Options: | | | |--- | ---| |--user | Activate the Excel add-in for a different user. (3)| |--debug / -d | Output more information when running the command.| |--non-interactive / -ni | Don’t prompt the user for any input. (1)| |--install-first | Install the PyXLL add-in as the first add-in to be loaded. (2)| - 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. ```bat >> 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. ```bat >> pyxll uninstall ``` Options: | | | |--- | ---| |--user | Uninstall the Excel add-in for a different user. (3)| |--force | Uninstall without any confirmation.| |--dry-run | Log what would happen without actually uninstalling.| |--debug / -d | Output more information when running the command.| - 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. **Footnotes** 1. 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. 2. The --install-first option was added in PyXLL 5.10.0. 3. The --user option was added in PyXLL 5.12.0. --- ## Manual Installation [docs/userguide/installation/manual.md](https://www.pyxll.com/docs/userguide/installation/manual.md) 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 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. ```ini [PYTHON] executable = ``` 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: ```bat 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 config-file 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. ```ini [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. ### 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 userguide for information about the other features of PyXLL. --- ## Using PyXLL with Anaconda [docs/userguide/installation/anaconda.md](https://www.pyxll.com/docs/userguide/installation/anaconda.md) ### 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: ``` >> 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. ```bat (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 references the Python executable from your conda environment: ```ini [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 environment you want to use and type “where pythonw”: ```bat (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. --- ## Configuring PyXLL [docs/userguide/config/index.md](https://www.pyxll.com/docs/userguide/config/index.md) ## 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. 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. There are various functions that can be used in config settings, as well as variable substitions. > **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. ```ini THIS WILL HAVE NO EFFECT ;setting = value SETTING IS EFFECTIVE WITH NO SEMICOLON setting = value ``` --- ## Python Settings [docs/userguide/config/python.md](https://www.pyxll.com/docs/userguide/config/python.md) ```ini [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 inspect = see sys.flags.inspect (default is 1) optimize = see sys.flags.optimize (default is 0) debug = see sys.flags.debug (default is 0) verbose = see sys.flags.verbose (default is 0) dont_write_bytecode = see sys.flags.dont_write_bytecode (default is 0) no_user_site = see sys.flags.no_user_site (default is 0) no_site = see sys.flags.no_site (default is 0) xoptions = list of Python *X* options (requires Python >= 3.11) ``` The Python settings determine which Python interpreter will be used, and some Python settings. Generally speaking, 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. ```ini [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. 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). ```ini [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.. ```ini [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. ```ini [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. **inspect:** New in PyXLL 4.5 This should typically left unset, and has a default value of 1. Setting to 1 is equivalent to starting Python with the “-i” switch. If not set, raising a SystemExit exception will cause the Excel process to exit. See sys.flags.inspect in the Python documentation for more information. **optimize:** New in PyXLL 4.5 Setting to 1 is equivalent to starting Python with the “-O” switch. This should typically left unset, and has a default value of 0. See sys.flags.optimize in the Python documentation for more information. **debug:** New in PyXLL 4.5 Setting to 1 is equivalent to starting Python with the “-d” switch. This should typically left unset, and has a default value of 0. See sys.flags.debug in the Python documentation for more information. **verbose:** New in PyXLL 4.5 Setting to 1 is equivalent to starting Python with the “-v” switch. This should typically left unset, and has a default value of 0. See sys.flags.verbose in the Python documentation for more information. **dont_write_bytecode:** New in PyXLL 4.5 Setting to 1 is equivalent to starting Python with the “-B” switch. This should typically left unset, and has a default value of 0. See sys.flags.dont_write_bytecode in the Python documentation for more information. **no_user_site:** New in PyXLL 4.5 Setting to 1 is equivalent to starting Python with the “-s” switch. This can be set if you do not want Python to import user installed site packages. See sys.flags.no_user_site in the Python documentation for more information. **no_site:** New in PyXLL 4.5 Setting to 1 is equivalent to starting Python with the “-S” switch. This should typically left unset, and has a default value of 0. See sys.flags.no_site in the Python documentation for more information. **xoptions:** New in PyXLL 5.11 Requires Python >= 3.11 List of Python X options, equivalent to starting Python with the “-X” switch. --- ## PyXLL Settings [docs/userguide/config/pyxll.md](https://www.pyxll.com/docs/userguide/config/pyxll.md) ```ini [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:deco:`xl_func` external_config = paths or URLs of additional config files to load optional_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) ; ; plotting settings ; plot_allow_html = 1 or 0 to disable or enable html plots by default (see :py:func:`plot`) plot_allow_svg = 1 or 0 to disable or enable svg plots by default (see :py:func:`plot`) plot_allow_resize = 1 or 0 to disable or enable resizing of plot images (see :py:func:`plot`) plot_temp_path = path of a directory to use to save temporary images when plotting plot_alt_text = Default alt text to use for plot images webview2_userdata_folder = Folder to use for the web control for html plots ; ; Number conversion settings ; nan_value = value to use if NaN is returned by a Python function posinf_value = value to use if +Inf is returned by a Python function neginf_value = value to use if -Inf is returned by a Python function decimals_to_float = 1 or 0 to automatically convert returned Python ``Decimal`` objects to floating point numbers. ; ; 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 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. disable_function_wizard_calc = 1 or 0 to disable calculating in the function wizard. disable_replace_calc = 1 or 0 to disable calculating in the find and replace dialog. disable_load_library_hook = 1 or 0 to disable a fix for loading problematic DLLs. 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 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 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. **optional_external_config:** This setting is identical to external_config except that if a file does not exist or cannot be read then a warning will be logged rather than an error. This can be useful if specifying a user config in a standard location and your users may or may not have that file. ### 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. **deep_reload_include:** Optional list of modules or packages to restrict reloading to when deep reloading 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). ```ini [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 dependencies 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. ### Abort Settings **allow_abort (defaults to 0)** The allow_abort setting is optional and sets the default value for the allow_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 “Dynamic 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. ### 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 ```ini [PYXLL] get_cached_object_id = module_name.get_custom_object_id ``` ```python 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 needing 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 recalc-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 save-objects. ### Plotting Settings **plot_allow_html:** New in Python 5.9 For plotting libraries that support html plots, PyXLL will try to use an interactive web control to display the plot. Setting plot_allow_html = 0 changes the default behaviour of the plot function so that if allow_html is not specified, by default html plots will be disabled and a static image format will be used instead (if available). **plot_allow_svg:** For plotting libraries that support exporting SVG files, PyXLL will use that instead of a bitmap format. Setting plot_allow_svg = 0 changes the default behaviour of the plot function so that if allow_svg is not specified, by default SVG plots will be disabled and a bitmap format will be used instead (if available). **plot_allow_resize:** For plots displayed in Excel as static images, when resizing the image PyXLL can re-plot the figure and update the image to the new size automatically. Setting plot_allow_resize = 0 changes the default behaviour of the plot function so that if allow_resize is not specified, by default if the image in Excel is resized the figure will not be re-plotted to match the new size. **plot_temp_path:** When exporting plots as images they are exported to a temporary file. This option can be used to change where the temporary files will be saved. **webview2_userdata_folder:** New in Python 5.9 For HTML plots displayed using a web control, the web control needs a folder to store its user data. The same folder is also used for temporarily writing the exported html for the plots. This setting can be used to change where the web control should store its data. By default, a folder named .webview2_userdata will be used in the same location as the PyXLL add-in. ### NaN Return Settings New in PyXLL 5.5. Values to use when NaN, +Inf and -Inf are returned from Python to Excel. These can be overridden for individual functions when using @xl_func, @xl_macro or XLCell.options, but if not specified the values set in the config file will be used instead. Valid values for these settings are: | | | |--- | ---| |#NULL! | | |#DIV/0! | | |#VALUE! | | |#REF! | | |#NAME! | | |#NUM! | | |#N/A | | |INF | | |None | | Or any numeric or string value can also be used. **nan_value:** Value to use if NaN is returned from Python to Excel. Defaults to #NUM! **posinf_value:** Value to use if +Inf is returned from Python to Excel. Defaults to +INF. **neginf_value:** Value to use if -Inf is returned from Python to Excel. Defaults to -INF. **none_value:** Value to use if None is returned from Python to Excel. Defaults to None. ### 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 async-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 async-functions. New in PyXLL 4.2.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 async-functions. New in PyXLL 4.2.0. ### win32com Settings **win32com_gen_path:** This sets the win32com.__gen_path__ path used for win32com’s generated 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 attempting 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. ### 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 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 1. See recalc-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 recalc-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 ### 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. ```ini [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). ### Web Control Settings New in PyXLL 5.5 PyXLL can make web requests to fetch files from web servers instead of using a file path. For example, the license_file, external_config and startup_script settings can all use a URL instead of a path to download a file. PyXLL will also attempt to ping a license server and download the latest license information, if possible. The web control used to make these requests can be configured using the options in this section. Usually these options should not be set as PyXLL will detect the correct settings automatically. **webclient_disable_autoproxy:** PyXLL will attempt to detect your proxy settings automatically if you have AutoProxy configured. This can be disabled by setting this option to 1. **webclient_autoproxy_logon_if_challenged:** If your proxy server requires authentication PyXLL will attempt to auto-logon if challenged. This can be disabled by setting this option to 0. **webclient_proxy:** If you connect to the internet through a manually configured proxy server, and PyXLL is not detecting that proxy server automatically, this can be used to set the proxy server. **webclient_proxy_bypass:** If you connect to the internet through a proxy, and you are manually specifying the proxy server using the webclient_proxy setting above, you can use this setting to configure some sites to bypass the proxy server. The proxy server will not be used for addresses beginning with entries in this list. Use semicolons (;) to separate entries. **webclient_disable_autologon:** If connecting to an Intranet site or trusted URL that supports auto-logon PyXLL can try to authenticate automatically. By default, auto-logon is only used for Intranet requests or Trusted Sites. This can be overridden using the webclient_autologon_security_level setting. Auto-logon can be disabled by setting this option to 1. **webclient_autologon_security_level:** This can be set to low, medium or high and determines what type of sites can be sent credentials for authentication. - low: Default credentials may be sent to all servers. - medium: Default credentials may be sent for Intranet requests. - high: Default credentials cannot be used for authentication. By default, auto-logon is used for Intranet requests and Trusted Sites and it is recommended in most cases that this setting is not set. **webclient_retry_count:** > New in PyXLL 5.11 > > Retry a number of times if a request fails. > **webclient_retry_delay:** > New in PyXLL 5.11 > > Delay in milliseconds between each retry after a request has failed. > ### 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. **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 recalc-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 recalc-on-open. **disable_function_wizard_calc:** If set, the default behaviour of functions registered with @xl_func is for them to not calculate when the Excel function wizard is active. This can be overriden using the disable_function_wizard_calc argument to @xl_func. New in PyXLL 5.8.0 **disable_replace_calc:** If set, the default behaviour of functions registered with @xl_func is for them to not calculate when the Excel find and replace dialog is active. This can be overriden using the disable_replace_calc argument to @xl_func. New in PyXLL 5.8.0 **disable_load_library_hook:** > There can be issues loading certain DLLs, such as some OpenSSL DLLs used by the packages ssl, cryptography, and others with some Python distributions (Anaconda). > > PyXLL attempts to work around these issues by modifying how Python loads DLLs. This option disables that work around. > > New in PyXLL 5.10.0 > **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 entry-points. **decimals_to_float:** > If 1 (default) then any Python Decimal objects will automatically be converted to a floating point number before being returned to Excel. > > Excel has no support for decimals and so if this is disabled any returned Decimal objects will be returned as a Python object handle. > > New in PyXLL 5.11.0 > **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. --- ## License Key [docs/userguide/config/license.md](https://www.pyxll.com/docs/userguide/config/license.md) ```ini [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. --- ## Logging [docs/userguide/config/logging.md](https://www.pyxll.com/docs/userguide/config/logging.md) 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. The config-varibles are useful when configuring the log file as they allow including the current date, process id, and other variables in the log file name and/or path. ```ini [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 = log format template debug_format = optional format template (defaults to format) info_format = optional format template (defaults to same as debug_format) warning_format = optional format template (defaults to same as info_format) error_format = optional format template (defaults to same as warning_format) critical_format = optional format template (defaults to same as error_format) 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') redirect_stdout = 1 or 0 to enable or disable redirection of native (C) stdout to log file. redirect_stderr = 1 or 0 to enable or disable redirection of native (C) stderr to log file. ``` **path:** Path where the log file will be written to. This may include substitution variables as in the section config-varibles, e.g. ```ini [LOG] path = C:/Temp/pyxll-logs-%(date)s ``` **file:** Filename of the log file. This may include substitution variables as in the section config-varibles, e.g. ```ini [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: ```ini [LOG] format = "%(asctime)s - %(name)s - %(levelname)s - %(message)s" ``` In addition to the standard LogRecord attrbitues provided by the logging module, the following may also be used: |Attribute Name | Format | Description | PyXLL Version| |--- | --- | --- | ---| |address | %(address)s | Full address of the calling cell, if known. | >= 5.10.0| |sheet | %(sheet)s | Sheet name of the calling cell, if known. | >= 5.10.0| |cell | %(cell)s | Cell reference of the calling cell, if known. | >= 5.10.0| For more information about log formatting, please see the logging module documentation from the Python standard library. **debug_format (3)** Log format to use for log messages of level DEBUG. Defaults to the format option if not set (default). **info_format (3)** Log format to use for log messages of level INFO. Defaults to the same as debug_format if not set (default). **warning_format (3)** Log format to use for log messages of level WARNING. Defaults to the same as info_format if not set (default). **error_format (3)** Log format to use for log messages of level ERROR. Defaults to the same as warning_format if not set (default). **critical_format (3)** Log format to use for log messages of level CRITICAL. Defaults to the same as error_format if not set (default). **max_size (1)** 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 (1)** 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. **roll_backoff_interval (2)** If rolling the log file fails a retry won’t be attempted for a short period of time. The default time between retries is 5 minutes. The interval can be number of days, hours, minutes or seconds using the form Nd for days (eg 7d), Nm, and Ns respectively. **backup_count (1)** 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. **redirect_stdout:** Enables or disabled redirecting the native (C runtime) stdout to the PyXLL log file. Some Python extensions, and the Python interpreter itself, sometimes log to the native C stdout instead of to the redirected Python stdout or to a log file. When this option is set the native stdout is redirected to the PyXLL log file. Defaults to 1. New in PyXLL 5.11.0. **redirect_stderr:** Enables or disabled redirecting the native (C runtime) stderr to the PyXLL log file. Some Python extensions, and the Python interpreter itself, sometimes log to the native C stderr instead of to the redirected Python stderr or to a log file. When this option is set the native stderr is redirected to the PyXLL log file. Defaults to 1. New in PyXLL 5.11.0. **Footnotes** 1. Log rolling is new in PyXLL 5.2. 2. New in PyXLL 5.6. 3. New in PyXLL 5.10 --- ## Warnings [docs/userguide/config/warnings.md](https://www.pyxll.com/docs/userguide/config/warnings.md) The Python warnings package is part of the Python standard library and used to alert the user of a problem. Typically these warnings are logged to the log file, but the warnings module can be configured to elevate these warnings to exceptions or to ignore them completely. For full details of the warnings package please see the Python documentation here `https://docs.python.org/3/library/warnings.html`_. The warnings package can be configured in the [LOG] section of the PyXLL config file with the following options. **capture_warnings:** Write warnings to the log file. If this is set to 0 then warnings will not be captured and written to the log file. Defaults to 1. **warnings_filters:** Warnings filters to control whether warnings are ignored, dispayed, or turned into errors. Multiple warning filters can be configured on multiple lines. Individual warnings filters are specified as a sequence of fields separated by colons: ```ini action:message:category:module:line ``` Example: ```ini [LOG] warnings_filters = default::DeprecationWarning:__main__ ignore::DeprecationWarning ignore::PendingDeprecationWarning ignore::ImportWarning ignore::ResourceWarning ``` --- ## Config Variables [docs/userguide/config/variables.md](https://www.pyxll.com/docs/userguide/config/variables.md) PyXLL creates some configuration substitution variables that can be used in any config values. Variable substitution in the config file follows the same format as Python’s configparser module, which is %(name)s, where name is the variable name. For example, including today’s date in the log file name would be specified as: ```ini [LOG] file = pyxll-log-%(date)s.txt ``` The following sustitution variables are available: |Variable Name | Format | Description | PyXLL Version| |--- | --- | --- | ---| |pid | %(pid)s | Process id | All| |date | %(date)s | Current date in YYYYMMDD format | All| |time | %(time)s | Current time in HHMMSS format | >= 5.6| |xl_version (1) | %(xl_version)s | Excel version | All| |py_version | %(py_version)s | Python version | >= 5.2| |pyxll_version | %(pyxll_version)s | PyXLL version | >= 5.2| |xll_path | %(xll_path)s | Full path of the pyxll.xll add-in | >= 5.6| |xll_dir | %(xll_dir)s | Directory containing the pyxll.xll add-in | >= 5.6| |cfg_path | %(cfg_path)s | Full path of the config file being processed | >= 5.6| |cfg_dir | %(cfg_dir)s | Directory containing the config file being processed | >= 5.6| |basecfg_path (2) | %(basecfg_path)s | Full path of the base config file | >= 5.6| |basecfg_dir (2) | %(basecfg_dir)s | Directory containing the base config file | >= 5.6| In addition to these standard substition variables, environment variables can also be used as substitution variables in the config. See envvars_in_config for more details about using environment variables in the config file. See also config-functions. **Footnotes** 1. xlversion was renamed to xl_version in PyXLL 5.2 but both forms will continue to work. 2. The base config is the first config file loaded by the add-in. This can be different from the config file being processed if the base config specifies additional config files using the external_config option. --- ## Config Functions [docs/userguide/config/functions.md](https://www.pyxll.com/docs/userguide/config/functions.md) New in PyXLL 5.11 A few pre-defined functions can be used within the config to simplify some scenarios. Function calls are made using the same syntax as variable substition, but with arguments added. A function call is specified using the form %(function arg1, arg2, ...)s. Note the trailing s which is the same as for substituted variables. The available functions are: Functions can be nested, and substitution and environment variables can be used as arguments. For example: ```ini [SECTION] ; Get the directory of the absulte path of some file. ; The environment variable 'USER_OVERRIDE_PATH' can be set, otherwise ./default.txt is used. abs_dir = %(dirname %(abspath %(USER_OVERRIDE_PATH:./default.txt)s)s)s ``` ### abspath Converts a relative path (relative to the config file being parsed) to an absolute path. Example: ```ini [SECTION] option = %(abspath ./file.txt)s ``` ### dirname Gets the directory component of a path. Example: ```ini [SECTION] ; option will be set to `C:\path\to` option = %(dirname C:\path\to\file.txt)s ``` ### basename Gets the base name of a path. The base name is the full file name, including any extension. Combine with filename if the file extension is not required. Example: ```ini [SECTION] ; option will be set to 'file.txt' option = %(basename C:\path\to\file.txt)s ``` ### filename Strips any file extension from a path and returns only the filename component (including any directory). Combine with basename if only the file name without any path component is required. Example: ```ini [SECTION] ; option will be set to 'file' option = %(filename %(basename C:\path\to\file.txt)s)s ``` --- ## Environment Variables [docs/userguide/config/envvars.md](https://www.pyxll.com/docs/userguide/config/envvars.md) 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 set a default value using the following format %(ENVVAR_NAME:default_value)s For example: ```ini [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. ```ini [ENVIRONMENT] NAME = VALUE ``` For each environment variable you would like set, add a line to the [ENVIRONMENT] section. --- ## Startup Script [docs/userguide/config/startup.md](https://www.pyxll.com/docs/userguide/config/startup.md) New in PyXLL 4.4.0 ### 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 extensions. 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 startup-script-for-deployment. ### Example A startup script could be used to download a Python environment and configure PyXLL. ```bat 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. ```ini [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: ```bat ECHO pyxll-set-option LOG verbosity debug ``` Calling the command from Powershell is the same: ```ps1 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: ```bat 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: ```ps1 Echo "pyxll-get-option PYTHON executable" $executable = Read-Host ``` Below is a list of the available commands. #### 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:** ```bat ECHO pyxll-get-option SECTION OPTION SET /p VALUE= ``` **Powershell:** ```ps1 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:** ```bat ECHO pyxll-set-option SECTION OPTION VALUE ``` **Powershell:** ```ps1 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. #### pyxll-unset-option Unsets the specified option. Takes two arguments, SECTION and OPTION. Doesn’t return value. **Batch File:** ```bat ECHO pyxll-unset-option SECTION OPTION ``` **Powershell:** ```ps1 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:** ```bat ECHO pyxll-set-progress PERCENT_COMPLETE ``` **Powershell:** ```ps1 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:** ```bat ECHO pyxll-show-progress ``` **Powershell:** ```ps1 Echo "pyxll-show-progress" ``` #### 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:** ```bat ECHO pyxll-set-progress-status STATUS ``` **Powershell:** ```ps1 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:** ```bat ECHO pyxll-set-progress-title TITLE ``` **Powershell:** ```ps1 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:** ```bat ECHO pyxll-set-progress-caption CAPTION ``` **Powershell:** ```ps1 Echo "pyxll-set-progress-caption CAPTION" ``` #### pyxll-get-version Gets the version of the installed PyXLL add-in. Takes no arguments and returns the version. **Batch File:** ```bat ECHO pyxll-get-version SET /p VERSION= ``` **Powershell:** ```ps1 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:** ```bat ECHO pyxll-get-python-version SET /p VERSION= ``` **Powershell:** ```ps1 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:** ```bat ECHO pyxll-get-arch SET /p ARCH= ``` **Powershell:** ```ps1 Echo "pyxll-get-arch" $arch = Read-Host ``` #### pyxll-get-pid Gets the process id of the Excel process. Takes no arguments and the process id. **Batch File:** ```bat ECHO pyxll-get-pid SET /p PID= ``` **Powershell:** ```ps1 Echo "pyxll-get-pid" $pid = Read-Host ``` #### pyxll-reload-config Stops the script and reloads the pyxll.cfg config file from scratch. The startup script will be re-run if the config file contains a startup script. This is useful when applying updates to an environment that include changes to the config file. Care should be taken so that this command is only used if the config has changed, as otherwise it can result in a loop where the config is continually reloaded and the script is re-run. The number of reloads is limited to avoid an infinite loop, but it will result in an error. This command takes no arguments and doesn’t return a value. **Batch File:** ```bat ECHO pyxll-reload-config ``` **Powershell:** ```ps1 Echo "pyxll-reload-config" ``` #### 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:** ```bat ECHO pyxll-restart-excel MESSAGE ``` **Powershell:** ```ps1 Echo "pyxll-restart-excel MESSAGE" ``` #### pyxll-set-error-message New in PyXLL 5.6 Sets the error message to be displayed to the user if the script fails. This can be used to customize what the user sees in the message box if the script exits with a non-zero exit code. **Batch File:** ```bat ECHO pyxll-set-error-message MESSAGE ``` **Powershell:** ```ps1 Echo "pyxll-set-error-message MESSAGE" ``` --- ## Menu Ordering [docs/userguide/config/menus.md](https://www.pyxll.com/docs/userguide/config/menus.md) 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. ```ini [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 ``` Here’s how the menus appear in Excel: --- ## Shortcuts [docs/userguide/config/macros.md](https://www.pyxll.com/docs/userguide/config/macros.md) 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. ```ini [SHORTCUTS] pyxll.reload = Ctrl+Shift+R module.macro_function = Alt+F3 ``` See shortcuts for more details. --- ## Default Decorator Parameters [docs/userguide/config/defaults.md](https://www.pyxll.com/docs/userguide/config/defaults.md) New in PyXLL 5.10 The decorators @xl_func, @xl_macro, and @xl_menu take various parameters to control their behaviour. The default parameters for these decorators can be modified through the following configuration options: ```ini [PYXLL] xl_func_defaults = ... xl_macro_defaults = ... xl_menu_defaults = ... ``` Each can be set to a function that returns the defaults to be used. The function is specified using its fully qualified function name, including the module or package containing the function. For example, to specify that the function my_xl_func_defaults from the Python module custom_defaults.py should be used to fetch default values for the @xl_func decorator the following config would be used: ```ini [PYXLL] xl_func_defaults = custom_defaults.my_xl_func_defaults ``` The specified function is called with the decorated function, and should return a dictionary of default parameter values. ### Example Use Suppose we wanted to change the default value for the name parameter the @xl_func. We might want to do this in order to use an all uppercase name for the Excel function, instead of simply using the function name is it is in Excel. The following funcition gets the function name in uppercase and returns a dictionary of the defaults to use for the @xl_func decorator: ```python def my_xl_func_defaults(func) # 'func' is the Python function that will be # called from Excel. name = func.__name__ # We want to use the funtion name in all uppercase # in Excel. xl_name = name.uppercase() # Here we return a dictionary of default values # for the @xl_func parameters. return { "name": xl_name } ``` We configure PyXLL to use this function for the @xl_func decorator default parameters as above. With this function configured, any function using decorated with the @xl_func decorator will now have the name parameter defaulted to the upper case function name. The following Python function would appear in Excel with the name A_PYTHON_FUNCTION. ```python @xl_func def a_python_function(): return "In Excel!" ``` ### Different Defaults For Different Dackages Usually you will not want to apply the same defaults to all modules and packages. You may want to only apply your defaults to your own packages if you are using some other third-party packages that also use PyXLL, or you may want to use different defaults for your own different packages. The defaults can be configured on a per-module or per-package basis to accomodate this. Suppose your had two Python modules my_math_functions and my_string_functions and you wanted all the functions in my_math_functions to have the category Math and all functions in my_string_functions to have the category String. We can write a couple of user default functions that return the default category parameter for @xl_func as follows: ```python def math_xl_func_defaults(func) return { "category": "Math" } def string_xl_func_defaults(func) return { "category": "String" } ``` We use the following config to configure those for the two modules my_math_functions and my_string_functions: ```ini [PYXLL] xl_func_defaults = my_math_functions: custom_defaults.math_xl_func_defaults my_string_functions: custom_defaults.string_xl_func_defaults ``` If you are using packages and want to include all modules and sub-packages within a package you use * as a wildcard in the module specification in the config: ```ini [PYXLL] xl_func_defaults = my_math_package.*: custom_defaults.math_xl_func_defaults my_string_package.*: custom_defaults.string_xl_func_defaults ``` --- ## Worksheet Functions [docs/userguide/udfs/index.md](https://www.pyxll.com/docs/userguide/udfs/index.md) --- ## Introduction [docs/userguide/udfs/introduction.md](https://www.pyxll.com/docs/userguide/udfs/introduction.md) ### Writing an Excel Worksheet Function in Python 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 documenting-functions). To tell the PyXLL add-in to expose a Python function so that we can call it from Excel, all that is needed is to add the @xl_func decorator to a Python function: ``` from pyxll import xl_func @xl_func def hello(name): return "Hello, %s" % name ``` This function takes just a single argument, name, which can be passed in when we call the function from Excel. PyXLL supports passing arguments and returning values of many different types, which is covered in detail in the next section. ### Configuring PyXLL with your Python Module Once you have saved that code you need to ensure the interpreter can find it by modifying the following settings in your pyxll.cfg config file: **[PYXLL] / modules:** The list of Python modules that PyXLL will import. **[PYTHON] / pythonpath:** The list of folders that Python will look for modules in. If you saved the above code into a new file called my_module.py in a folder C:\Users\pyxll\modules you would add the Python module my_module to the modules list, and C:\Users\pyxll\modules to the pythonpath. Note that Python module file names end in .py, but the Python module names do not. ```ini [PYXLL] ; ; Make sure that PyXLL imports the module when loaded. ; ; We use the module name here, not the file name, ; and so the ".py" file extension is omitted. ; modules = my_module [PYTHON] ; ; Ensure that PyXLL can find the module. ; Multiple modules can come from a single directory. ; pythonpath = C:\Users\pyxll\modules ``` ### Calling your Python Function from Excel ### 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 the need to restart Excel itself. After making these changes 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") ``` > **Tip:** If your function does not appear in Excel or you get an error message, check the PyXLL log file. By default, the log file will be in the logs folder next to the PyXLL add-in. ### Different Argument and Return Types Worksheet functions can take simple values, as in the example above, or more complex arguments including Pandas DataFrames and Numpy arrays. This is covered in detail in the next section. --- ## Argument and Return Types [docs/userguide/udfs/argtypes.md](https://www.pyxll.com/docs/userguide/udfs/argtypes.md) ### 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. Also see type-hints. #### @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 Type Hints Type information can also be provided using type annotations, or hints, in Python 3. See type-hints for more detailed information about using type hints. 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. #### @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 a, numpy_array 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) ``` #### Type Parameters Many types can be parameterised to further control the type conversion between Excel and Python. An example of this is in the section above where we see the numpy_array type accepts a type parameter ndim. Type parameters can be specified when using a function signature, or when using the @xl_arg and @xl_return decorators. For details of the type parameters available see the specific documentation for the type you are interested in. Type parameters can be different depending on whether it is the argument conversion or return conversion that is being specified. ### Standard 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. For some types, Python type hints or annotations can be used. |PyXLL Type | Python Type | Python Type Hint| |--- | --- | ---| |float | float | float| |int | int | int| |str | str | str| |unicode | unicode (4) | N/A| |bool | bool | bool| |datetime | datetime.datetime (1) | datetime.datetime| |date | datetime.date | datetime.date| |time | datetime.time | datetime.time| |var | object (5) | typing.Any| |object | object (2) | object| |rtd | RTD (3) | RTD| |xl_cell | XLCell (6) | XLCell| |range | Excel Range COM Wrapper (7) | N/A| |function | function (8) | typing.Callable| **Footnotes** 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. 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. 8. New in PyXLL 5.4 The function argument type can be used to pass other @xl_func functions to Python functions in Excel. This can be useful for functions that require a callback function and is cleaner than specifying the function name as a string and then having to look up the Python function. #### Array Types See arrayfuncs 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-arrays and pandas-types for details of how to pass numpy and pandas types between Excel and Python functions. #### The ‘var’ Type The var type can be used when your function accepts any type. It is also the default type used if no other type is specified. When an argument is passed from Excel to Python using the var type the most appropriate conversion is chosen automatically from the primiative types natively supported by Excel. The following examples all use the var type: ```python from pyxll import xl_func @xl_func def my_function(x): # As no type was specified, both 'x' and return type type will # default to 'var' return str(type(x)) # return type is also 'var' as unspecified ``` ```python from pyxll import xl_func @xl_func("x var: str") def my_function(x): # x can be of any type as 'var' was specified as the argument # type in the function signature above. return str(type(x)) # return type is 'str' from the signature ``` ```python from pyxll import xl_func import typing @xl_func def my_function(x: typing.Any) -> str: # x will use the 'var' type because the 'Any' type hint was used return str(type(x)) # return type is 'str' from the type hint ``` ```python from pyxll import xl_func @xl_func @xl_arg("x", "var") def my_function(x): # x was specified to use the 'var' type using @xl_arg above return str(type(x)) # return type is unspecified as so 'var' is assumed ``` When using see cached objects the var will, by default, look up the cached object from the object handle passed to the function and pass the object to the function. This can be disabled using the no_object_lookup type parameter, for example var. > **Note:** The no_object_lookup type parameter is new in PyXLL 5.6. #### Numpy Types See numpy for details about the supported Numpy types. #### Pandas Types See pandas for details about the supported Pandas types. #### Polars Types See polars for details about the supported Polars types. #### 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. ``` from pyxll import xl_func @xl_func("dict: str") # Keys are strings, values are integers def dict_test(x): return str(x) ``` From PyXLL 5.8.0, if using Python 3.8 or higher, you can also use the standard TypedDict type annotation. ``` from typing import TypedDict from pyxll import xl_func class MyTypedDict(TypedDict): a: int b: int c: int @xl_func def dict_test(x: MyTypedDict) -> str: return str(x) ``` 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 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. - ignore_missing_values If True, ignore any items where the value is missing (new in PyXLL 5.7). **dict, when used as an return type:** **dict:** - 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. #### Dataclass Types Python dataclasses are a convenient way of creating Python classes encapsulating a collection of typed data fields. They can also be used to make passing structured data objects between Python and Excel simpler. Working with dataclasses in Excel is similar to using dictionaries. From Excel, a 2d array of key, value pairs can be passed to a function expecting a dataclass and the correct dataclass will be constructed automatically. For example, the following code defines a dataclass: ```python from dataclasses import dataclass @dataclass class InventoryItem: """Class for keeping track of an item in inventory.""" name: str unit_price: float quantity_on_hand: int = 0 ``` To write an Excel function that accepts a dataclass of this type you simply need to add a type hint to the function argument: ```python from pyxll import xl_func @xl_func def cost_of_stock(item: InventoryItem) -> float: """Returns the total cost of inventory items on hand.""" return item.unit_price * item.quantity_on_hand ``` Or, if passing a signature string to @xl_func instead of using type hints: ```python from pyxll import xl_func @xl_func("InventoryItem item: float") def cost_of_stock(item): """Returns the total cost of inventory items on hand.""" return item.unit_price * item.quantity_on_hand ``` Lists of dataclasses can also be used. The first column of data is the field names, and subsequent columns are field values for each dataclass instance. ```python from pyxll import xl_func from typing import List @xl_func def total_cost_of_stock(items: List[InventoryItem]) -> float: """Returns the total cost of inventory items on hand.""" total = 0.0 for item in items: total += item.unit_price * item.quantity_on_hand return total ``` If you data is laid out with the field names as column headers, use the transpose type parameter on the dataclass arguement, for example, using @xl_arg: ```python from pyxll import xl_func, xl_arg @xl_func @xl_arg("items", transpose=True) def total_cost_of_stock(items: List[InventoryItem]) -> float: ... ``` Or using a signature string to @xl_func: ```python from pyxll import xl_func @xl_func("InvetoryItem[]: float") def total_cost_of_stock(items: List[InventoryItem]) -> float: ... ``` Dataclasses can also be used as a return type. > **Note:** Using dataclasses requires PyXLL 5.8.0 and Python 3.7 or later. #### 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 thisextra 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. ```python from pyxll import xl_func import datetime as dt @xl_func("union 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 ```python 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 ``` > **Tip:** From Python 3.10 onwards, Union[A, B] can be written as A | B. The order the union type arguments are specified in matters. 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. PyXLL will attempt to convert the Excel value to a Python value as follows: 1. If the Excel value is a cached object and one or more of the types are object or a type based on object, each object-like type conversion will be attempted in order from left to right. 2. If the Excel value exactly matches one of the types then the value will not be converted. This check is only possible for primitive types. 3. An attempt will be made to convert the value to each of the listed types in order, from left to right. The result from the first attempted conversion that succeeds will be used. > **Note:** The logic around converting cached objects was refined in PyXLL 5.6. #### Optional Types Optional types are used for arguments that can be omitted by the user calling the function. If the argument is omitted then None will be passed to Python. > **Note:** Optional types are new in PyXLL 5.6 Optional types may be specified in the @xl_func function signature using the optional type, with the actual type passed as the type parameter. They can also be specified using Python type annotations using the typing.Optional type annotation. The following is a function that will accept an optional date argument. If no argument is provided then the function will be called with None. ```python from pyxll import xl_func import datetime as dt @xl_func("optional value: str") def optional_date(value): if value is None: return "No date" return value.strftime("%Y-%m-%d") ``` The same can be written using Python type annotations as follows ```python from pyxll import xl_func import datetime as dt import typing @xl_func def optional_date(value: typing.Optional[dt.date]) -> str: if value is None: return "No date" return value.strftime("%Y-%m-%d") ``` #### Function Type Python functions exposed to Excel using @xl_func can be passed in to other Python functions from Excel using the function type. This is useful when you have one function that takes another callback function that the user can select. > **Note:** The function type is new in PyXLL 5.4. For example, the following function takes a list of values and a function and calls that function on each value and returns the result: ```python from pyxll import xl_func @xl_func("float[] values, function fn: float[]") def apply_function(values, fn): return [fn(value) for value in values] ``` This function can be called from Excel passing in another @xl_func function. ```python from pyxll import xl_func import math @xl_func("float x: float") def sigmoid(x): return 1 / (1 + math.exp(x)) ``` We can call our sigmoid function on a single value in Excel, or using the apply_function function above we can pass in the sigmoid function and call it for list of values. ```default =apply_function(A1:A20, sigmoid) ``` If you are using Python type annotations instead of passing a signature string the types typing.Callable or collections.abc.Callable may be used to specify the argument is a function. > **Warning:** Only PyXLL functions can be passed as function arguments. You cannot pass standard Excel functions, VBA functions or functions from other add-ins to Python functions using the function type. #### Error Types Also See error-handling. Excel errors can be passed to, or returned from, Python functions that use the var or exception types. Similarly, Python functions can return specific errors to Excel by returning Python Exception objects. PyXLL maps Excel errors to Python Exception types as specified in the following table: |Excel error | Python exception type| |--- | ---| |#NULL! | LookupError| |#DIV/0! | ZeroDivisionError| |#VALUE! | ValueError| |#REF! | ReferenceError| |#NAME! | NameError| |#NUM! | ArithmeticError| |#NA! | RuntimeError| New in PyXLL 5.9 To pass errors as argument or return types, as well as using the var type the explicit exception type can be used. The exception type takes a type parameter cls to further specify the exception type. Alternatively, a Python type annotation of Exception or other Exception type can be used. Using the exception type explicitly is usually used when a function can accept or return either a value or an error. The union type can be used in conjunction with the exception type for this purpose. The example below shows a function that can return a value or a ValueError exception: ```python from pyxll import xl_func @xl_func("bool x: union>") def string_or_error(x): if x: return "OK!" return ValueError() ``` Below is an equivalent function written using Python type annotations: ```python from pyxll import xl_func @xl_func def string_or_error(x: bool) -> str | ValueError: if x: return "OK!" return ValueError() ``` ### 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 object-cache. ### 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 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 x: bool") def test_custom_type_arg2(x): assert x.y == 1 return isinstance(x, CustomType) ``` ### 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)) ``` --- ## Cached Objects [docs/userguide/udfs/cached-objects.md](https://www.pyxll.com/docs/userguide/udfs/cached-objects.md) ### Introduction 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, a 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. The following example shows a case where we return a compiled regex object. Here, it is not desirable to convert the returned Python object to a primitive Excel value, and so we return it as an object: ```python from pyxll import xl_func import re # This function returns a compiled regex object, which can't be represented as an plain Excel value @xl_func def compile_pattern(pattern: str) -> object: """Compile a regex pattern and return it as a cached object.""" return re.compile(pattern) # This function takes the earlier compiled regex object, passed from Excel as a cached object handle @xl_func def count_matches(compiled: object, text: str) -> int: """Count matches of a compiled regex pattern in text.""" return len(compiled.findall(text)) ``` In Excel, we call these functions as normal and the compiled regex object is returned as a cached object handle, which can then be passed to our count_matches function. Functions can be annotated explicitly to take or return cached object handles using the object type. If no type, or var, is used, object handle arguments will be converted automatically, and returned non-primitive types will be returned as cached object handles. > **Tip:** When to use object vs var? Use object when your function returns something that has no meaningful Excel representation — a custom class instance, a large DataFrame you want to pass between functions efficiently, or any other Python object. PyXLL will store it in the object cache and return a handle to it instead. That handle can then be passed to other PyXLL functions, which will receive the original Python object. Use var when your function returns a Python primitive — a number, string, boolean, date, or other value that Excel can represent natively. PyXLL will convert it automatically. If a non-primitive type is returned, the var type will automatically cache the object and return a handle. The next 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 ``` 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. > **Note:** Objects returned from Excel to Python as cached objects are not copied. When an object handle is passed to another function, the object retrieved from the cache is the same object that was previously returned. You should be careful not to modify these objects. Instead, if you need to modify the object, you should copy or clone the object and only modify the copy. ### 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) ``` ### 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 (recalc-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. ```ini [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 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: ```python from pyxll import xl_func @xl_func(": object") 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. Objects that are to be saved must be pickle-able. This means that they must be able to be serialized and deserialized 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. ```ini [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. ```python 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. ```ini [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 workbook. 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 save-objects. > **Note:** Prior to PyXLL 5.0 the custom object handle function did not take the save kwarg. ### 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. ```python from pyxll import xl_func from random import random @xl_func("int x: object") 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. ```ini [PYXLL] clear_object_cache_on_reload = 0 ``` To have your functions recalculated automatically after reloading, and repopulate the object cache, you can do so using the recalc_on_reload option to @xl_func as desribed in the section recalc-on-reload. --- ## Cached Functions [docs/userguide/udfs/lrucache.md](https://www.pyxll.com/docs/userguide/udfs/lrucache.md) New in PyXLL 5.11 If you have one or more functions that take a long time to execute, or are called repeatedly with the same arguments, caching the function can speed things up. PyXLL includes an implementation of a Least Recently Used Cache, or LRU Cache, that will cache function results so that repeated calls with the same arguments return a cached result instead of repeatedly calling the actual Python function. The Least Recently Used cache stores up to a maximum number of results, keyed by the function arguments. Once the maximum number of results is exceeded, the least recently used result is removed from the cache to make space for the latest result. > **Note:** For other cache methods aside from LRU caching, see udf-alt-cache. The LRU cache is enabled for a function using the lru_cache keyword argument to @xl_func: ```python from pyxll import xl_func @xl_func(lru_cache=3) def cached_function(a, b): ... ``` ### Why does Excel recalculate a function? Excel tracks when each cell needs to be recalculated. To do this, it keeps a record of which cells depend on which other cells - this is referred to as the Excel Dependency Graph. When a cell is updated, Excel knows that any other cells that depend on that cell need to be recalculated. When in automatic calculation mode, when Excel determines a cell needs to be recalculated it will do so immediately. In manual calculation mode, when Excel determines a cell nees to be recalculated it will mark the cell as dirty and it will be calculated when Excel next recalculates (usually when the user presses F9). Excel doesn’t consider the cell value when deciding if a cell needs to be recalculated or not. If a cell is updated with the same value, Excel will still recalculate any dependent cells. There are other reasons Excel can decide a cell needs to be recalculated. For example, if a function used in the cell, or if one of the cell’s dependencies, is volatile. Various standard Excel functions are volatile functions, including TODAY, NOW, RAND, RANDBETWEEN, INDIRECT, OFFSET, INFO, CELL, and SUMIF. Excel can also decide that a cell needs recalculating after rows or columns are inserted or deleted. For a complete description of when Excel might determine a cell needs recalculating see the Excel documentation page Excel Recalcuation. ### Enabling LRU Caching for a function To enable LRU caching for a PyXLL worksheet function you pass the keyword argument lru_cache to the @xl_func decorator. The value of the lru_cache keyword argument can be an integer, True, or False. - If lru_cache is a positive integer then the LRU cache will cache up to a maximum number of results set by the number. - If lru_cache is True, zero, or a negative integer, the size of the cache will be infinite. - If lru_cache is False or None the function will not be cached. For example, the following function will cache up to 3 results, keyed by its arguments: ```python from pyxll import xl_func @xl_func(lru_cache=3) def cached_function(a, b): ... ``` > **Note:** PyXLL’s LRU cache is only used when calling the function from Excel. If the function is called from Python then the cache is not used. For caching functions when called from Python, see functools.lru_cache in the standard Python library for an alternative. ### LRU Cache Statistics It is possible to query the cache for some basic statistics for each cached function. The function lru_cache_info when called with a function returns a dictionary with the following keys: - maxsize - maximum number of cached reuslts, or 0 if unbounded - currsize - current number of cached results - hits - number of times a cached result has been returned - misses - number of times the function was called without finding a cached result If no results are found an empty dictionary is returned. lru_cache_info can also be called with no arguments. In that case it returns a dictionary of function names to dictionaries of the statistics listed above. ### Clearing the LRU Cache The LRU cache is always cleared when the PyXLL add-in is reloaded. It can also be cleared using the lru_cache_clear function. If lru_cache_clear is called with a function, only cached results for that function are cleared. If lru_cache_clear is called with no arguments, all cached results are cleared. ### PyXLL’s LRU Cache vs functools.lru_cache PyXLL’s LRU cache is conceptually very similar to functools.lru_cache from the Python standard library. There are however some important differences. Python’s functools.lru_cache requires all function arguments to be hashable. This is required as it needs to store the arguments efficiently and quickly determine if a function call has been made with the same arguments before. This can be problematic when using arguments such as DataFrames or other more complex Python objects that are not easily hashable. PyXLL’s LRU cache has the same requirement as it works in a similar way, but it tests the arguments before they are converted to the final Python values. Because Excel values are always simple primitives (strings, numbers, booleans, etc) they are always easily hashable. Furthermore, when a cached result already exists for a set of arguments the cached result can be returned without doing the full conversion from the primitive Excel values to the final Python arguments, which can reduce the total time taken. Python’s functools.lru_cache has no knowledge of Excel’s RTD or asynchronous functions. Wrapping one of these functions using functools.lru_cache takes a little more work. PyXLL’s LRU cache on the other hand knows about these different types of functions and handles them automatically. Once final difference is with how PyXLL’s cached objects are handled. When using PyXLL’s LRU cache, if a function returns a cached object back to Excel the object handle used doesn’t update when a cached result is found. This is intentional as it means that dependent functions can also be cached and don’t need to be recalculated unless the object handle updates. When using functools.lru_cache, if the function returns a cached object back to Excel then the object handle will update each time regardless of whether the result was cached or not. ### Why do dependent cells still get recalculated? If you have a sheet with cells dependent on a function using the LRU cache, those dependent cells will still be recalculated even if a cached result is returned. For example, suppose you have the following: ```text A1 = 1 A2 = foo(A1) A3 = bar(A2) ``` Where foo is a cached function using lru_cache and bar is not. When A1 is changed, Excel will mark A2 and A3 as being dirty. When Excel next recalculates, either automatically or when triggered manually, it will recalculate the cells in their dependency order A2 followed by A3. When recalculating A2 if foo has a cached result for the updated argument that that cached result will be called without calling the Python function foo. This doesn’t affect the fact that Excel has already marked A3 as being dirty though. Even if the result returned to cell A2 hasn’t changed, Excel will still consider A3 as dirty. Excel will continue its recalculation and still recalculate cell A3 and call the function bar. While it is not possible to change how Excel recalculates, enabling LRU caching for bar also will mean that if it has been called with the same arguments previously (and the result is still cached) the cached result can be used. ### Alternative Caching Methods New in PyXLL 5.12 You can provide your own cache to use instead of the standard LRU cache implementation using the cache kwarg to @xl_func and @xl_macro. Your cache object must be a mutable mapping. > **Tip:** The third party package cachetools provides a number of cache implementations that can be used to cache PyXLL functions. Each time the cached function is called the cache object will get queried for an existing result using it’s __getitem__ method. If a KeyError exception is raised then the function will be called and the result will be stored in the cache using __setitem__. Cached items are keyed by a hashable and comparable type, pyxll.CacheKey. Values are not stored as the plain values returned by the function, but instead as instances of pyxll.CachedValue. This is because more than just the returned Python value is stored to avoid repeating work when returning the value to Excel. If a function fails the exception is cached instead of the returned value. The exception is wrapped in a pyxll.CachedValue instance in the same way a successfully returned value is. Therefore, it is not necessary for the cache to treat failed function calls differently than those that have returned successfully. #### Example The following example shows a minimal cache class that only stores the most recent value. ```python from pyxll import xl_func, CacheKey, CachedValue class CustomCache: """Caches only the most recent result""" def __init__(self): self.__last_key = None self.__last_value = None def __getitem__(self, key: CacheKey) -> CachedValue: # Retrieve a cached value if key == self.__last_key: return self.__last_value raise KeyError(key) def __setitem__(self, key: CacheKey, value: CachedValue): # Store the value in the cache self.__last_key = key self.__last_value = value def __len__(self): # Return the number of cached objects return 1 if self.__last_key is not None else 0 def clear(self): # Clear any cached value self.__last_key = None self.__last_value = None # Pass an instance of our custom cache as the function cache object @xl_func(cache=CustomCache()) def cached_function(a, b, c): # If the Excel functions is called twice in a row with the same arguments # then the Python function will only be called once and the cached result # will be returned the second time. print(f"Cached function called with {a=}, {b=}, {c=}") return a + b + c ``` #### Optional Cache Properties When implementing a custom cache you can add the following properties to your cache class. These are use to populate the information returned by lru_cache_info. - maxsize: Return maximum number of cached reuslts, or 0 if unbounded. - currsize: Return the current number of objects in the cache. If not available, len(cache) will be used instead. --- ## Array Functions [docs/userguide/udfs/arrayfuncs.md](https://www.pyxll.com/docs/userguide/udfs/arrayfuncs.md) 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-arrays 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. ### Array Types New in PyXLL 5.7 As well as specifying the argument or return type as an array using the type[] syntax in the @xl_func signature or to @xl_arg or @xl_return decorators, you can specify if you want the Python type to be a list (same as type[]), tuple or set. Python type hints can also be used. |PyXLL Type | Python Type | Python Type Hint| |--- | --- | ---| |list | list | list or typing.List| |tuple | tuple | list or typing.Tuple| |set | set | set or typing.Set| These array types have the following type parameters: **list, tuple, and set:** - T Type used for the item type. - trim Set to true to trim the input range to exclude blank or empty cells. See array-trimming. **list and tuple:** - ndim 1 or 2, for 1d or 2d collections. For example, the following function accepts a tuple of floats: ``` from pyxll import xl_func @xl_func("tuple x: str") def tuple_func(x): # x is a tuple of strings return str(x) ``` This could also be specified using a Python type hint as follows: ``` from pyxll import xl_func @xl_func def tuple_func(x: tuple[float]): # x is a tuple of floats return str(x) ``` Two dimensional lists and tuples can also be used, for example: ``` from pyxll import xl_func # Note: This is equivalent to 'int[][]' @xl_func("list x: str") def tuple_func(x): # x is a list of lists of strings return str(x) ``` This could also be specified using a Python type hint as follows: ``` from pyxll import xl_func @xl_func def tuple_func(x: list[list[[int]]): # x is a list of lists of strings return str(x) ``` #### Trimming Array Arguments New in PyXLL 5.12 When passing an array as an argument to an Excel function sometimes you might want to pass a larger range than the actual data. For example, when creating a sheet you might allow some extra rows in case the data grows, or to allow space for the user of the sheet to add more rows. You might even select a range of entire columns. Converting a larger range than necessary takes additional time when constructing the Python collection object. Instead of constructing the entire object and then removing empty rows in your Python code, it is much more efficient (and easier!) for PyXLL to trim any trailing empty rows or columns from the Excel data before constructing the Python collection. To tell PyXLL to trim empty space use the trim type parameter to the list, tuple, or set type. For example: ```python from pyxll import xl_func @xl_func("list data, int row: float") def sum_row(data, row): """Sum the values in a row.""" row_values = data[row] return sum(row_values) ``` The function above can now accept a large range including empty rows and columns around the actual data. Before the list is constructed the data is trimmed to remove empty rows and columns from the top, bottom, left and right of the data. When specifying trim=True any empty rows from the top or bottom of the range, or empty columns from the left or right of the range, will be removed. If you only want to trim rows or columns from certain directions instead of using trim=True you can use a string to specify top, left, bottom and/or right by using the characters t, l, b, r, respectively. For example, to only trim rows from the bottom and columns from the right you would use trim='br'. ### 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 ```ini [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 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. ### 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-resize. 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 ```ini [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: |Characteristic | 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.| --- ## NumPy Array Types [docs/userguide/udfs/numpy.md](https://www.pyxll.com/docs/userguide/udfs/numpy.md) 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 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 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 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 a, numpy_array b: numpy_array") 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. Floating point numpy arrays are the fastest way to get data out of Excel into Python. If you are working on performance sensitive code using a lot of data, try to make use of numpy_array or numpy_array for the best performance. See arrayfuncs for more details about array functions. --- ## Pandas Types [docs/userguide/udfs/pandas.md](https://www.pyxll.com/docs/userguide/udfs/pandas.md) Pandas DataFrames and Series can be used as function arguments and return types for Excel worksheet functions. For polars DataFrames, see polars-types. > **Tip:** See pandas for a more complete explaination of how pandas can be used in Excel. 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", 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) ``` ### Type Hints Python type hints can be used instead of the type signature shown above. Where type parameters are required you can use typing.Annotated along with TypeParameters (see type-hints). Alternatively, you can use @xl_arg and @xl_return in conjunction with type annotations to specify the type parameters. The above functions can be written using type hints as follows: ``` from pyxll import xl_func, xl_return from pyxll import TypeParameters as TP from typing import Annotated import pandas as pd import numpy as np @xl_func(auto_resize=True) def random_dataframe( rows: int, columns: int ) -> Annotated[pd.DataFrame, TP(index=True)]: data = np.random.rand(rows, columns) column_names = [chr(ord('A') + x) for x in range(columns)] return pd.DataFrame(data, columns=column_names) ``` ### Type Parameters The following type parameters are available for the dataframe and series argument and return types: **dataframe, when used as an argument type:** dataframe **kind:** Set to pandas to specify that a pandas DataFrame is required. If not set the default DataFrame type will be used. The default DataFrame type can be set by the default_dataframe_kind option in the [PYXLL] section of the pyxll.cfg file. Defaults to pandas if not set otherwise. **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. **trim:** New in PyXLL 5.11 Trim leading and trailing empty rows and columns from the data before constructing the DataFrame. See trimming-dataframe-arguments. **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. The dictionary can be specified using standard Python dictionary syntax as part of a function signature string. However, often it is more convenient to use the @xl_arg or @xl_return decorators. These allow you to set type multiple complex parameters more easily, for example: ``` @xl_func @xl_arg("df", dtypes={"A": "date"}) def your_function(df: pd.DataFrame): .... ``` Not all column dtypes need to be specified. Any that are not specified will default to var. **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 **kind:** Set to pandas force the kind of DataFrame expected to be a pandas DataFrame. If not set, any supported kind of DataFrame can be used. **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:** 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 orientation 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:** 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. > **Tip:** For specifying multiple or complex type parameters it can be easier to use the @xl_arg and @xl_return decorators. See xl_arg_return_decorators for more details about how to use @xl_arg and @xl_return to specify type parameters. 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 pandas for more information. **Footnotes** 1. The multi_sparse parameter is new in PyXLL 5.3.0. --- ## Polars DataFrames [docs/userguide/udfs/polars.md](https://www.pyxll.com/docs/userguide/udfs/polars.md) New in PyXLL 5.6 Polars DataFrames can be used as function arguments and return types for Excel worksheet functions in a similar way to Pandas DataFrames. When used as an argument, the range specified in Excel will be converted into a Polars DataFrame as specified by the function signature. The dataframe type specifier is the same as the one used for pandas DataFrames. When passing a polars DataFrame as an argument you need to specify the kind of DataFrame you want by setting kind=polars: ``` from pyxll import xl_func @xl_func("dataframe: dataframe", auto_resize=True) def polars_dataframe_sum(df): # df is a polars.DataFrame return df.sum() ``` When returning a polars DataFrame there is no need to specify the kind as that will be infered from the type of the returned object. The following shows returning a random polars DataFrame: ``` from pyxll import xl_func import polars as pl import numpy as np @xl_func("int rows, int columns: dataframe", auto_resize=True) def random_polars_dataframe(rows, columns): data = np.random.rand(columns, rows) column_names = [chr(ord('A') + x) for x in range(columns)] return pl.DataFrame(data, columns=column_names) ``` > **Tip:** The type alias polars.dataframe can be used in place of dataframe, and pandas.dataframe can be used in place of dataframe. Both forms are equivalent. ### Type Hints Python type hints can be used instead of the type signature shown above. Where type parameters are required you can use typing.Annotated along with TypeParameters (see type-hints). Alternatively, you can use @xl_arg and @xl_return in conjunction with type annotations to specify the type parameters. The above functions can be written using type hints as follows: ``` from pyxll import xl_func import polars as pl import numpy as np @xl_func(auto_resize=True) def random_polars_dataframe(rows: int, columns: int) -> pl.DataFrame: data = np.random.rand(columns, rows) column_names = [chr(ord('A') + x) for x in range(columns)] return pl.DataFrame(data, columns=column_names) @xl_func(auto_resize=True) def polars_dataframe_sum(df: pl.DataFrame) -> pl.DataFrame: # df is a polars.DataFrame return df.sum() ``` ### Type Parameters The following type parameters are available for dataframe argument and return types: **dataframe, when used as an argument type:** dataframe **kind:** Set to polars to specify that a polars DataFrame is required. If not set the default DataFrame type will be used. The default DataFrame type can be set by the default_dataframe_kind option in the [PYXLL] section of the pyxll.cfg file. Defaults to pandas if not set otherwise. **trim:** New in PyXLL 5.11 Trim leading and trailing empty rows and columns from the data before constructing the DataFrame. See trimming-dataframe-arguments. **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. > > The dictionary can be specified using standard Python dictionary syntax as part of a function signature string. However, often it is more convenient to use the @xl_arg or @xl_return decorators. These allow you to set type multiple complex parameters more easily, for example: > > > ``` > @xl_func > @xl_arg("df", dtypes={"A": "date"}) > def your_function(df: pl.DataFrame): > .... > ``` > > Not all column dtypes need to be specified. Any that are not specified will default to var. > **strict:** New in PyXLL 5.12 Throw an error if any data value does not exactly match the given or inferred data type for that column. If set to False, values that do not match the data type are cast to that data type or, if casting is not possible, set to null instead. Defaults to True. **dataframe, when used as a return type:** dataframe **kind:** Set to polars force the kind of DataFrame expected to be a polars DataFrame. If not set, any supported kind of DataFrame can be used. > **Tip:** For specifying multiple or complex type parameters it can be easier to use the @xl_arg and @xl_return decorators. See xl_arg_return_decorators for more details about how to use @xl_arg and @xl_return to specify type parameters. When passing large DataFrames between Python functions it is not always necessary, or desirable, to return the full DataFrame to Excel. 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 dataframe can accept object handles. --- ## Asynchronous Functions [docs/userguide/udfs/asyncfuncs.md](https://www.pyxll.com/docs/userguide/udfs/asyncfuncs.md) Excel has supported asynchronous worksheet functions since Office 2010. To be able to use asynchronous worksheet 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 functions, 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 (rtd) 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. > **Note:** If Excel is interupted during an async calculation it will return control of Excel to use the user. When calculating, Excel will appear blocked but if the user clicks somewhere or presses a button on the keyboard Excel will interupt the calculation and return control to Excel. If there are any asynchronous functions that have not yet completed, after a short time Excel will resume calculating. New in PyXLL 5.9.0 Excel’s async handles (how the result is returned back to Excel) are only valid for the calculation phase they were started in. Attempting to return a result after Excel’s calculation has completed (or been interupted) results in an error. As of PyXLL 5.9.0, PyXLL will manage the async handles so that any async functions started during on calculation phase can still return their result in the next calculation phase. This means that if Excel is interupted before an async function completes then it will continue and the result will be returned to Excel when it resumes calculating. This behaviour can be disabled to give the same behaviour as earlier PyXLL versions by setting the following in your pyxll.cfg file: ```ini [PYXLL] disable_async_cache = 1 ``` ### 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() 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 config-pyxll 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. ### 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 rtd 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 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() # return immediately, the real result will be returned by the thread function return ``` The type parameter to async_handle (e.g. async_handle) 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. 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. 2. Asynchronous functions are only available in Excel 2010. Attempting to use them in an earlier version will result in an error. --- ## Function Documentation [docs/userguide/udfs/funchelp.md](https://www.pyxll.com/docs/userguide/udfs/funchelp.md) ### Writing 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. The arguments and documentation provided are displayed in Excel’s function wizard: 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. ### Linking to Additional Documentation In the Excel function wizard screen there is a “Help on this function” link. You can use this link to reference any URL or CHM (Compiled HTML File) containing your more detailed documentation. The link to use for the “Help on this function” link is set using the help_topic keyword argument to the @xl_func decorator. #### Linking to a URL (website) To set the help topic for a function to a URL pass it as the help_topic` keyword argument to the @xl_func decorator. You must use the full URL including the “http://”, “https://”, or “file://” prefix. You can include any query string or anchors that you need in the URL. For example: ``` from pyxll import xl_func @xl_func(help_topic="https://www.google.com/search?q=pyxll") def your_function(...): .... ``` When you examine this function in the Excel Function Wizard it will now have a link to the URL specified. > **Note:** Linking to URLs was added in PyXLL 5.1. In earlier versions only linking to CHM files is possible (see below). #### Using a Compiled Help File CHM, or HTML Help, files are compiled from HPP and HTML files using tools like Microsoft HTML Html (https://docs.microsoft.com/en-us/previous-versions/windows/desktop/htmlhelp/microsoft-html-help-downloads) or other third party tools. To reference a CHM file for a specific function, when registering the function with :py:deco`xl_func` set the help_topic kwarg to the absolute path of the .chm file to load, and the section of the file to open as a numeric help context id in the form “filename.chm!HelpContextID”, e.g. “C:/folder/help.chm!0”. If you only want to open the chm file, you still have to provide a help context id but it can be 0. The file path should be an absolute path and must be less than 255 characters long, so you may have to use a windows short path (this is a limitation of Excel). To add context ids to your chm file you have to add aliases to your hpp file used to make the chm file, e.g. ```text ---- MYHELP.HHP ---- [FILES] index.html myfunc1.html myfunc2.html myfunc3.html [ALIAS] IDH_topic_1 = myfunc1.html [MAP] #define IDH_topic_1 1001 ------------------------- ``` ### IntelliSense (auto-completion) Excel doesn’t natively provide IntelliSense or auto-completion for functions provided by add-ins such as PyXLL. The third party add-in Excel-DNA IntelliSense implements IntelliSense-like auto-completion for Excel add-ins, and this is the recommended way to get auto-completion using PyXLL. To install the Excel-DNA IntelliSense add-in, download it from https://github.com/Excel-DNA/IntelliSense/releases. Once you have installed the ExcelDNA-IntelliSense add-in, PyXLL will detect it automatically and start using it to give you auto-completion and in-sheet IntelliSense for your PyXLL functions. The same function documentation as shown in the Excel function wizard is used. If you need help installing the Excel-DNA IntelliSense add-in then this video will help https://www.youtube.com/watch?v=-BIOzl1igXU. > **Note:** The Excel-DNA IntelliSense add-in is not part of PyXLL, and is not supported or maintained by PyXLL. It is referenced here for information only. If you need assistance using this add-in you should contact the ExcelDNA team via their github page. --- ## Variable and Keyword Arguments [docs/userguide/udfs/varargs.md](https://www.pyxll.com/docs/userguide/udfs/varargs.md) ### Variable Arguments (*args) In Python it is possible to declare a function that takes a variable number of positional 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. ```python 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. ```python 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). ### Keyword Arguments (**kwargs) New in PyXLL 5.8 Python functions can take an arbitrary number of named arguments using the special **kwargs argument. A keyword argument is where you pass an argument to a function by name. When using **kwargs, any named argument that is not one of the function’s parameter names is added to a dictionary and passed to the function in the kwargs dictionary. Excel does not support passing arguments by name when calling Excel worksheet functions (UDFs). When registering a Python function to be called from Excel that has **kwargs the Excel function will expect a 2d array of values for the kwargs argument. PyXLL will convert that to a dictionary and pass that dictionary to the Python function as the **kwargs. The array passed from Excel to the Python function should be a list of key, value pairs. For example, the following Python function takes **kwargs and can be called from Excel passing a range of key, value pairs: ```python from pyxll import xl_func @xl_func def kwargs_example(**kwargs): return "foo={foo}; bar={bar}".format(**kwargs) ``` The function can be called from Excel, passing the **kwargs as an array of key value pairs: Arrays can be passed directly to Excel functions, without needing to refer to a range on a worksheet. The syntax for this varies depending on your language settings. In English, the format to pass an array is { "key1", value1; "key2", value2; "keyN", valueN }. , is the item separator, and ; is the row separator. In some languages the item and row separators are reversed. Specifying types for **kwargs is done in the same way as for dictionay types. See dict-types for full details of how to specify types for dictionaries. If using Python 3.12 or higher, you can also use the standard TypedDict type annotation for **kwargs. ``` from typing import TypedDict from pyxll import xl_func class MyTypedKwargs(TypedDict): foo: int bar: int @xl_func def kwargs_example(**kwargs: MyTypedKwargs) -> str: return "foo={foo}; bar={bar}".format(**kwargs) ``` > **Note:** It is not possible to combine *args and **kwargs for functions that are exposed to Excel. This is due to the constraints of how Excel functions can be called. **Footnotes** 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. --- ## Recalculating On Open [docs/userguide/udfs/recalc-on-open.md](https://www.pyxll.com/docs/userguide/udfs/recalc-on-open.md) 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 recalculated 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 metadata. ### 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 recalculated 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. ```python from pyxll import xl_func @xl_func(recalc_on_open=True) def recalc_on_open_func(): 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. ```ini [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. ```ini [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. ```python 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. ### 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. ```ini [PYXLL] disable_recalc_on_open = 1 ``` --- ## Recalculating On Reload [docs/userguide/udfs/recalc-on-reload.md](https://www.pyxll.com/docs/userguide/udfs/recalc-on-reload.md) New in PyXLL 5.10 It can be useful to have worksheet functions (UDFs) that are automatically called whenevner the PyXLL add-in is reloaded. One instance where this is useful is for functions returning objects. When reloading the PyXLL add-in any cached objects that have been returned from functions previously are cleared (unless the clear_object_cache_on_reload = 0 option is used). These functions need to be recalculated to recreate those objects, and so recalculating on reload can be helpful in that instance. PyXLL functions can be made to automatically recalculate after the PyXLL add-in is reloaded by passing recalc_on_reload=True to @xl_func. ### Example The below function uses the recalc_on_reload=True option to tell PyXLL that it should be recalculated whenever the add-in is reloaded. This function must be called at least once first (which happened either when entering the formula, or recalculating the workbook). Then, the next time the add-in is reloaded the cell containing the function will be marked as dirty and if automatic calculations are enabled it will be recalculated. ```python from pyxll import xl_func @xl_func(recalc_on_reload=True) def recalc_on_reload_func(): print("recalc_on_reload_func called!") return "OK!" ``` ### Default Behaviour The default behaviour for non-volatile worksheet functions is not to recalculate on reload unless the recalc_on_reload option is set in @xl_func. The recalc_on_reload 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_reload feature applies by default. **Real Time Data (RTD) Functions:** For RTD functions the option recalc_rtd_on_reload can be set in the PYXLL section of the pyxll.cfg config file. If set, all RTD functions will recalculate on reloading unless specifically disabled by setting recalc_on_reload=False in the @xl_func decorator. ```ini [PYXLL] ; Enable recalc on reload for all RTD functions recalc_rtd_on_reload = 1 ``` **Functions Returning Objects:** Similarly, any worksheet function that explicitly returns an object can be set to recalculate on reloading via the config setting recalc_cached_objects_on_reload. To enable recalculating all object functions on reload set recalc_cached_objects_on_reload to 1. ```ini [PYXLL] ; Eenable recalc on reload for all functions returning objects recalc_cached_objects_on_reload = 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_reload=True in @xl_func. ```python 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_reload=False to @xl_func. ### Disabling Completely If you do not want any functions to be recalculated when reloading set disable_recalc_on_reload = 1 in your pyxll.cfg file. This setting prevents any cells marked by PyXLL as needing to be recalculated when reloading the PyXLL add-in, regardles of any other settings or the recalc_on_reload option to @xl_func. ```ini [PYXLL] disable_recalc_on_reload = 1 ``` --- ## Interrupting Functions [docs/userguide/udfs/allowabort.md](https://www.pyxll.com/docs/userguide/udfs/allowabort.md) 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 config-pyxll 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. ```ini [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. --- ## Macro Functions [docs/userguide/macros/index.md](https://www.pyxll.com/docs/userguide/macros/index.md) --- ## Introduction [docs/userguide/macros/introduction.md](https://www.pyxll.com/docs/userguide/macros/introduction.md) You can write an Excel macro (sometimes called ‘Subs’ in VBA) in Python. A Python macro function can do whatever you would previously have used VBA for. Macros work in a very similar way to worksheet functions. Macro functions are almost identical to worksheet functions in how they’re written. One key difference, however, is that macro functions called by Excel can use the Excel Object Model to automate or script Excel. PyXLL macro functions written in Python are exactly the same as any other Excel macro function. They can be called from form items such as buttons, or from VBA. To register a function as a macro you use the @xl_macro decorator, which works in almost exactly the same way as the @xl_func decorator but is used for macros instead of worksheet functions. VBA macros are often used to automate tasks in Excel, but using PyXLL we can write equivalent macros entirely in Python without the need for any VBA. 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 vba for details of how the Excel Object Model can be used from Python. ### Writing an Excel Macro Function in Python If you’ve not installed the PyXLL addin yet, see installing-pyxll. To tell the PyXLL add-in to expose a Python function so that we can call it from Excel as a macro, all that is needed is to add the @xl_macro decorator to a Python function: ``` from pyxll import xl_macro @xl_macro def hello(name): return "Hello, %s" % name ``` This function takes just a single argument, name, which can be passed in when we call the function from Excel. PyXLL supports passing arguments and returning values of many different types, which is covered in detail in the function-signature section. ### Configuring PyXLL with your Python Module Once you have saved that code you need to ensure the interpreter can find it by modifying the following settings in your pyxll.cfg config file: **[PYXLL] / modules:** The list of Python modules that PyXLL will import. **[PYTHON] / pythonpath:** The list of folders that Python will look for modules in. If you saved the above code into a new file called my_module.py in a folder C:\Users\pyxll\modules you would add the Python module my_module to the modules list, and C:\Users\pyxll\modules to the pythonpath. Note that Python module file names end in .py, but the Python module names do not. ```ini [PYXLL] ; ; Make sure that PyXLL imports the module when loaded. ; ; We use the module name here, not the file name, ; and so the ".py" file extension is omitted. ; modules = my_module [PYTHON] ; ; Ensure that PyXLL can find the module. ; Multiple modules can come from a single directory. ; pythonpath = C:\Users\pyxll\modules ``` --- ## Calling Macros From Excel [docs/userguide/macros/calling-macros.md](https://www.pyxll.com/docs/userguide/macros/calling-macros.md) Macros defined with PyXLL can be called from Excel the same way as any other Excel macros. Once you have written your Python function, decorated it with the @xl_macro decorator, and configured PyXLL (as described in the previous section) you are ready to call the macro function from Excel. > **Tip:** Don’t forget you need to reload after making changes to your Python code or the PyXLL config! 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. > **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. Here’s an example Python function exposed to Excel as a macro: ```python from pyxll import xl_macro @xl_macro("str s: int") def py_strlen(s): """Return the length of a string""" return len(s) ``` For the py_strlen example above, to call that from VBA you would use the Run VBA function, e.g. ```vbnet Sub SomeVBASubroutine x = Run("py_strlen", "my string") End Sub ``` --- ## Accessing Worksheet Data [docs/userguide/macros/accessing-data.md](https://www.pyxll.com/docs/userguide/macros/accessing-data.md) ### Reading Excel Values from Python Macros will often need to read values from Excel. We can do that using the Excel Object Model in the same way as you might have done from VBA previously. For example, the following code gets a Range object and then gets the value using the Range.Value property. ```python from pyxll import xl_macro, xl_app @xl_macro def read_value_from_excel(): # Get the Excel.Application COM object xl = xl_app() # Get a Range in the current active sheet xl_range = xl.ActiveSheet.Range("A1:D10") # Access the Value of the Range value = xl_range.Value ``` What you will notice when reading values this way is that you can’t control the type of the value you get. For example, in the code above we are using a range and the Python value obtained will be a list of lists. If we were to use a single cell, the Python value would be a single value. You can use get_type_converter to access PyXLL’s type converters (including any custom type converters you may have written) and use that to convert the raw value into the type you require. However, there is a slightly easier way. Using the PyXLL class XLCell we can access the value as a specified type as follows: ```python from pyxll import xl_macro, xl_app, XLCell @xl_macro def read_value_from_excel(): # Get the Excel.Application COM object xl = xl_app() # Get a Range in the current active sheet xl_range = xl.ActiveSheet.Range("A1:D10") # Get an XLCell object from the Range object cell = XLCell.from_range(xl_range) # Get the value as a DataFrame df = cell.options(type="dataframe").value ``` ### Writing Python Values to Excel Writing values to Excel from Python is very similar to reading values, as shown above. While you can set the Range.Value property directly, using XLCell.value is often more convenient as it can do any type conversions necessary for you. For example, to write a DataFrame to a range you can do the following: ```python from pyxll import xl_macro, xl_app, XLCell @xl_macro def write_value_to_excel(): # Get the Excel.Application COM object xl = xl_app() # Get a Range in the current active sheet xl_range = xl.ActiveSheet.Range("A1") # Get an XLCell object from the Range object cell = XLCell.from_range(xl_range) # Create the DataFrame we want to write to Excel df = your_code_to_construct_the_dataframe() # Write the DataFrame to Excel, automatically resizing the range to fit the data. cell.options(auto_resize=True, type="dataframe").value = df ``` In the code above you can see that as well as converting the DataFrame to the type expected by Excel, XLCell can also automatically expand the range that’s being written to fit the entire DataFrame. For more details about the options available, see the XLCell class reference. ### Accessing Cached Objects 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 ``` --- ## Passing Python Objects [docs/userguide/macros/cached-objects.md](https://www.pyxll.com/docs/userguide/macros/cached-objects.md) New in PyXLL 5.11 While it is possible to completely replace VBA macros with Python macros, sometimes it is necessary to mix the two and call Python macros from VBA. For example, when migrating a large VBA project to Python it may not be feasible to change everything in one go. When mixing VBA and Python occasionally you may want to pass a complex Python object from Python to VBA, in order to pass it to another Python function later. Not all Python types convert easily to standard Excel types. In order to pass Python objects from Python to VBA and from VBA to Python, PyXLL mainains an ‘object cache’. Rather than return an actual Python object, PyXLL can store a returned object in this cache and return a handle to that object from the macro. This handle can then be passed to other Python macros, and PyXLL will retreive the object from the cache. For example, here are two macros - one that returns an object, and one that expects an object. To tell PyXLL that we want to pass this object via the object cache the functions use the ‘object’ type: ```python from pyxll import xl_macro class CustomObject: def __init__(self, name): self.name = name @xl_macro("string name: object") def create_object(x): return CustomObject(x) @xl_macro("object x: string") def get_object_name(x): assert isinstance(x, CustomObject) return x.name ``` There’s no other code needed, just marking the object type as ‘object’ is enough to tell PyXLL that the return value should be put in the object cache and a handle to that object returned to Excel. We can call these functions from VBA: ```vbnet Sub Test ' a is an object handle we can pass to other Python Macros a = Run("create_object", "TEST") ' get_object_name is called with the object handle b = Run("get_object_name", a) End Sub ``` Cached objects in macros work in the same way as cached objects for worksheet functions, with just one important difference: Unlike objects that are returned to the worksheet, objects returned via macros have an expiry time after which they are removed from the cache. This is explained in more detail in the following section. ### Cached Object Lifetime New in PyXLL 5.11 When using cached objects from worksheet functions, the object handle is returned to the Excel sheet and the lifetime of the cached object is managed by PyXLL. When the cell containing the object handle is cleared, the cached object is removed from the cache. For macro functions, the object handle is not written to the Excel sheet and so PyXLL has no way of knowing when the cached object is no longer required. To prevent the object cache from growing indefinitely, objects returned from macros are removed from the cache after a pre-determined amount of time. Usually, as objects are passed to VBA in order to be passed to another Python function soon after, this amount of time only needs to be a few seconds. Python objects returned from macros are called transient objects as they only remain in the cache for a finite period of time. The amount of time a transient object is given before it will be removed from the cache is called the time to live or TTL. The default TTL can be set in the pyxll.cfg config file as follows: ```ini [PYXLL] ; Default time to live for transient cached objects, in seconds object_cache_transient_ttl = 10 ``` The TTL for an object can also be set in your Python code. The TTL can be set before the object is returned, or at any time after. Changing the object’s TTL restarts the clock to when it will be removed from the cache. The function set_transient_object_ttl can be used to set the TTL for an object. If an object should never be removed from the cache, a negative TTL can be used. For example, if a returned object should be kept in cache for 5 minutes you would do the following: ```python from pyxll import xl_macro, set_transient_object_ttl class CustomObject: def __init__(self, name): self.name = name @xl_macro("string name: object") def create_long_lived_object(x): obj = CustomObject(x) # Keep the object alive for at least 5 minutes set_transient_object_ttl(obj, 300) return obj ``` --- ## Pausing Automatic Recalculation [docs/userguide/macros/pausing-calcs.md](https://www.pyxll.com/docs/userguide/macros/pausing-calcs.md) For macros that modify the workbook, having Excel update or recalculate after each individual change can result in poor performance. It is quite common to temporarily disable Excel’s screen updating and automatic calculations while performing such changes to the workbook in a macro. PyXLL provides the context manager xl_disable for this purpose. The same can be acheived using the Excel Object Model (see vba), but using xl_disable can be more convenient. The following example shows how xl_disable can be used to temporarily disable screen updating and automatic calculations while making an update to the workbook: ```python @xl_macro def macro_function(): with xl_disable(): # do some work here that updates Excel where we do not # want Excel to automatically recalculate or update. xl = xl_app() xl.Range("A1").Value = 1 # After the with block, Excel reverts to its previous calculation mode. return ``` Similar options are available when using the @xl_macro decorator to disable updating and calculations for the duration of the entire macro. --- ## Keyboard Shortcuts [docs/userguide/macros/shortcuts.md](https://www.pyxll.com/docs/userguide/macros/shortcuts.md) 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, xlcAlert @xl_macro(shortcut="Alt+F3") def macro_with_shortcut(): xlcAlert("Alt+F3 pressed") ``` 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 --- ## Real Time Data [docs/userguide/rtd.md](https://www.pyxll.com/docs/userguide/rtd.md) ### 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. 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 decorator. By registering a Python generator function, or a function that returns an RTD object, streams of values can be returned to Excel as a real time data function. RTD functions have the return type rtd. ### RTD Generators New in PyXLL 5.6 The simplest way to write an Excel RTD (Real Time Data) function using PyXLL is to write it as a Python generator. A Python generator is a special type of Python function that yields a stream of results instead of just a single result. The following is an example of a Python generator that yields a random number every 5 seconds: ``` import random import time def random_numbers(): # Loop forever while True: # Yield a random number yield random.random() # Wait 5 seconds before continuing time.sleep(5) ``` To turn this into an RTD function in Excel all that’s needed is to add the @xl_func decorator with the rtd return type: ``` from pyxll import xl_func import random import time @xl_func(": rtd") def random_numbers(): while True: yield random.random() time.sleep(5) ``` When this random_numbers function is called from Excel it will tick every 5 seconds with a new random number. To prevent the long running Python generator function from blocking the main Excel thread RTD generators are always run on a background thread. One thread is created for each generator. For more sophisticated thread management use a function that returns an RTD instance instead (see using-rtd-class). Each time the generator yields a value Excel is notified a new value is ready. Excel may not display every value as it throttles the updates that is displays (see rtd-throttle-interval). It’s important not to create a tight loop that constantly updates as doing so will prevent other threads from having time to run (including the main Excel thread) and will cause Excel to hang. > **Warning:** Unlike other PyXLL functions, RTD generators are always run on a background thread. #### Async RTD Generators New in PyXLL 5.6 Async RTD generators work in a similar way to the RTD generators described above. Instead of running in a separate thread async RTD generators are run on PyXLL asyncio event loop. Async generators are well suited to IO bound tasks such as receiving updates from a remote server. Care should be taken so that the asyncio event loop isn’t blocked by ensuring that tasks are properly asynchronous and are awaited correctly. The same example as above can be re-written as an async generator by replacing time.sleep with asyncio.sleep: ``` from pyxll import xl_func import random import asyncio @xl_func(": rtd") async def async_random_numbers(): # Loop forever while True: # Yield a random number yield random.random() # Wait 5 seconds before continuing without blocking the event loop await asyncio.sleep(5) ``` When this async_random_numbers function is called from Excel it will tick every 5 seconds with a new random number. Each time the generator yields a value Excel is notified a new value is ready. Excel may not display every value as it throttles the updates that is displays (see rtd-throttle-interval). > **Warning:** Async RTD generators share the same asyncio loop as other async functions. Blocking the asyncio event loop will cause delays in other functions, or prevent them from running entirely. See asyncio-event-loop for more details. #### Setting an Initial Value New In PyXLL 5.12 When using an RTD generator, Excel may already have finished calculating by the time your generator yields its first value. If this happens you may see an empty cell or 0 (depending on your Excel settings) until the first value is yielded from the generator. To avoid this you can set an initial value using the initial_value type parameter to the rtd type. For example: ```python @xl_func("int x: rtd **Tip:** You can use the @xl_return decorator if you don’t want to have the value in the signature string, for example: ```python @xl_func @xl_return(initial_value='Please wait...') async def your_generator(x: int) -> pyxll.RTD: while running: yield ... ``` ### Using the RTD Class For more control over the RTD behaviour of an Real Time Data function an RTD object can be returned from an RTD function instead of using a generator. The RTD class has a value property. Setting this property notifies Excel that a new value is ready. RTD functions that use the RTD class return an RTD object and update the value on that returned object each time a new value is available (for example, by scheduling an update function on a background thread). Typically this is done by writing a derived class that handles updating its own value. If multiple function calls from different cells return the same RTD object then those cells are subscribed to the same object. All the cells will update whenever the value property of the one RTD object is set. #### RTD Class Example The following example shows a class derived from RTD that periodically updates its value to the current time. 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. ``` =rtd_current_time() ``` #### RTD Class Async Example 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 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") def async_rtd_example(): return AsyncRTDExample() ``` ### 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") def rtd_object_func(x): # MyRTD sets self.value to a non-trivial Python object return MyRTD(x) ``` RTD data types can be used for RTD generators in the same way. 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 udfs/arrayfuncs). ### Restarting RTD Functions Each time your RTD function ticks, Excel re-evaluates the RTD function. PyXLL keeps track of your RTD generator or RTD instance to ensure that each time Excel calls the RTD function the correct, already running, object is returned. Without this caching behavior your Python function would be called each time the RTD function ticks, causing it to always restart and never progress past the initial value. If the arguments to an RTD function are changed, or if it is moved from one cell to another, the Python function will be called and the RTD function will restart. Because the RTD object has to be cached the usual methods of forcing a cell to recalculate such as pressing F2 to edit the cell and then entering, or force recalculating by pressing Ctrl+Alt+F9, do not work for RTD functions. There are situations though where an RTD function only returns a fixed number of values before stopping. In those cases you may want the RTD function to restart the next time Excel calculates the cell. For example, one common case is an RTD function that returns just one value after a delay: ```python @xl_func("str x: rtd") async def fetch_from_database(x): # Let the user know we're working on it yield "Please wait..." # Fetch some data data = await async_fetch_data_from_database(x) # Finally yield the data to Excel yield data ``` In this case, if the user was to attempt to recalculate the cell after the data has been returned they might expect that the data be re-fetched. But, the default behaviour is that the RTD generator is cached and so its same current state is returned again. #### Detaching New in PyXLL 5.9 The solution is to detach the RTD generator or RTD object once completed. This detaching removes the RTD generator or object from PyXLL’s RTD cache so that the next time the cell is evaluated there is no cached RTD instance. The Python function is called again, and the Excel RTD function restarts. #### Detaching RTD Generators When using an RTD generator or async generator you can tell PyXLL to automatically detach the RTD object when the generator stops. To do that, add the auto_detach=True type paramter to the rtd return type. For example: ```python @xl_func("str x: rtd") async def fetch_from_database(x): # Let the user know we're working on it yield "Please wait..." # Fetch some data data = await async_fetch_data_from_database(x) # Finally yield the data to Excel. # The generator stops after this line. yield data ``` Once the generator has stopped, if the user then recalculates the cell it will restart. > **Tip:** If you want this auto_detach behaviour to be the default for all of your RTD generators you can set the following in your pyxll.cfg file: ```ini [PYXLL] rtd_auto_detach = 1 ``` #### Detaching RTD Instances If you are using the RTD class you can detach the RTD object by calling the RTD.detach method. After calling RTD.detach, the RTD instance will no longer be cached and the next call to your RTD function will call your Python function, restarting the RTD function. For example, if in the AsyncRTDExample code from above, if we wanted our RTD function to restart after the first few ticks we would do the following: ```python 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): # Send just a few ticks for i in range(5): if self.__stopped: break # Yield to the event loop for 1s await asyncio.sleep(1) # Update value (which notifies Excel) self.value += 1 # And then detach. # The next time =async_rtd_example is called the Python # function will be called, starting a new AsyncRTDExample instance. self.detach() async def disconnect(self): self.__stopped = True @xl_func(": rtd") def async_rtd_example(): return AsyncRTDExample() ``` ### 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 or yielding values from a generator 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 ``` ### 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 recalc-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. ```ini [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. --- ## Using Type Hints [docs/userguide/typehints/index.md](https://www.pyxll.com/docs/userguide/typehints/index.md) There are many places in PyXLL where you need to specify type information to inform PyXLL how to convert between Python and Excel values. Since Python 3, Python has its own way of adding type information to functions through type hinting. For new PyXLL users, type hinting is probably the most natural way to specify types within PyXLL. For a list of the basic supported types, see standard-types. array-types, numpy-arrays, pandas-types, and polars-types are also supported, as are custom-types. > **Note:** For those interested in more details… PyXLL has its own internal type representation that it uses to map between Python and Excel types, and there are several ways to specify types in PyXLL. The PyXLL internal type system was developed long before Python’s own type hints, and so that is why there are now a few different ways of specifying types when using PyXLL. Python type hints identify a type, whereas PyXLL’s type system is subtly different in that it declares the exactly how a type will be converted between Excel and Python. For simple types there is no real difference, but for more complex types PyXLL has the concept of type parameters which modify how the type conversion happens. Since PyXLL 5.12, annotated type hints can be used that add type parameters to type hints. Using annotated type hints, all PyXLL types can be represented by Python type hints. In the documentation you will see signature strings often used. These are parsed into PyXLL’s type representation, as are type hints. Which you use is mostly down to your own preference. ### Type Hints for UDFs The most common place where type information is necessary is when exposing a Python function to Excel via the @xl_func and @xl_macro decorators. The argument types determine how the function will be registered in Excel, as well as how the Excel values will be converted into Python values. Similarly, the return type dictates how the returned Python value will be converted before being returned to Excel. To understand why type hinting necessary first consider a function without types: ```python from pyxll import xl_func @xl_func def some_function(a, b, c): return a + b + c ``` This function will do what you expect, if you pass it numbers! PyXLL will convert the Excel types to the closest Python type, and so Excel numbers will be converted to Python float types. So far so good… Now consider this next example: ```python from pyxll import xl_func @xl_func def pick_a_value(data, row_index, col_index): return data[row_index][col_index] ``` If you pass this function a range of values, a row index, and a column index - for example, =pick_a_value(A1:C3, 1, 1), you might be surprised that it fails! The range A1:C3 will be converted to a Python list of lists (list of rows, where each row is itself a list), and we can index into that in the way shown – but, the indexing will fail because the numbers passed as row_index and col_index are Excel numbers which will be converted to Python float types. We need Python int types in order to index correctly. Also, what would happen if we were to pass a single cell as our data argument? Since PyXLL doesn’t know that we always want that argument to be passed as a list, it will pass a scalar value in that case. The solution is to add some type information to control how the values are converted. Using type hints, we can do that as follows: ```python from pyxll import xl_func import typing @xl_func def pick_a_value(data: list[list], row_index: int, col_index: int) -> typing.Any: return data[row_index][col_index] ``` Note that we didn’t need to change the actual function, just the argument and return types (although the return type is not strictly necessary here). The type hints above are equivalent to using a PyXLL signature string as follows: ```python from pyxll import xl_func @xl_func("list data, int row_index, int col_index: var") def pick_a_value(data, row_index, col_index): return data[row_index][col_index] ``` Either syntax is fine, and both achieve the same thing. > **Note:** Python type hints have changed over the Python 3.x releases. The exact syntax that you need to use will depend on exactly what version of Python you’re using, and may vary from what is shown in the document. As an example, list[list]] used above requires Python >= 3.9. In earlier Python versions, typing.List[typing.List] can be used instead. If you are restricted on the version of Python you can use, and a particular type hint is not possible in your chosen Python version, you can always revert to using PyXLL signature strings as those work in all Python versions. ### Type Parameters In PyXLL, argument and return types are used for more than just to specify the Python type – they control exactly how the conversion between the Python and Excel types happen. For many types there are options to how this conversion happens, as well as the type information. For example, DataFrame types can be converted to and from ranges of values in Excel. But, the fact that an argument or return type is a DataFrame is not enough to control how that conversion happens. In the DataFrame example, we can choose whether we want the index and columns to be included, or if we want to trim blank space from the range before converting, or various other options. These other options are what are referred to as type parameters in the PyXLL documentation. Specifying type parameters using a type signature string is done as follows: ```python from pyxll import xl_func @xl_func("dataframe df, str column: float") def sum_column(df, column): return df[column].sum() ``` In the above, index=True specifies that when converting from the Excel range to the Python DataFrame the first column of the data should be interpreted as the DataFrame’s index. To do the same using only Python type hints we need to add this information to the type using the typing.Annotated class and PyXLL’s TypeParameters class. The code above can be re-written as: ```python from pyxll import xl_func from pyxll import TypeParameters as TP from typing import Annotated import pandas as pd @xl_func def sum_column( df: Annotated[pd.DataFrame, TP(index=True)], column: str ) -> float: return df[column].sum() ``` Using typing.Annotated and TypeParameters all of the options for PyXLL’s various type converters can be specified using Python type hints. ### Cached Objects Python objects can be returned from Excel functions as cached object handles, to be passed seamlessly to other Python functions. The Python objects are cached by PyXLL and retrieved when passing objects by their handle into another PyXLL function. To tell PyXLL to pass a Python object as an object handle the object type is used, but when writing Python code with type hints using the object type directly is often too broad and not helpful as a type hint. For this reason PyXLL provides the generic type alias Object (1), which can be used as Object[T] where T is the actual type. Type checkers will see T, but the PyXLL add-in will understand that an object handle is to be used. For example, the following returns a pandas.DataFrame to Excel as an object handle. Type checkers will correctly see this function as returning a DataFrame. ```python from pyxll import xl_func, Object import pandas as pd @xl_func def load_dataframe() -> Object[pd.DataFrame]: df = ... # load or build DataFrame object return df ``` See also object-cache. > **Note:** Generic type aliases require Python 3.12+. If you are using an earlier version of Python you will need to specify the return type as object using either a signature string or the @xl_return decorator. ### Real Time Data Types When defining a Real Time Data, or RTD, function, it’s necessary to set the return type to rtd. This tells the PyXLL add-in to register the function differently, as it’s an RTD function. When using type hints, if writing an RTD function that returns an RTD instance that is no problem and you can simply use RTD as your return type. The RTD class is a generic type (2) and you can specify the inner type of the RTD value as well, for example: ```python from pyxll import xl_func, RTD # This class handles the RTD updates and self.value is set to 'int' values class MyRtdType(Rtd): ... @xl_func def my_rtd_function(...) -> RTD[int]: return MyRtdType(...) ``` > **Note:** Subscripting the RTD class requires Python 3.9+ and PyXLL 5.12+. For earlier versions, use @xl_return to add the RTD return type information. When using a generator or async generator, we still need to tell the PyXLL add-in to treat the generator as an RTD function, but the return type hint now needs to be a generator type and not the plain RTD type. PyXLL provides two generic type aliases for this purpose, RTDGenerator and RTDAsyncGenerator (3). ```python from pyxll import xl_func, RTDGenerator, AsyncRTDGenerator from typing import Any import asyncio import time @xl_func def my_rtd_generator(...) -> RTDGenerator[Any]: i = 0 while True: i += 1 yield i time.sleep(5) @xl_func async def my_async_rtd_generator(...) -> AsyncRTDGenerator[Any]: i = 0 while True: i += 1 yield i await asyncio.sleep(5) ``` To type checkers, RTDGenerator[T] is an alias for typing.Generator[T, None] and AsyncRTDGenerator[T] is an alias for typing.AsyncGenerator[T, None], but the PyXLL add-in recognizes these as meaning that the function should be registered as an RTD function. PyXLL’s RTD type has its own type parameters, such as initial_value.We can set those using typing.Annotated and TypeParameters in the same way as shown earlier: ```python from pyxll import xl_func, RTDGenerator from pyxll import TypeParameters as TP from typing import Annotated import time @xl_func def my_rtd_generator(...) -> Annotated[RTDGenerator[int], TP(initial_value=0)]: i = 0 while True: i += 1 yield i time.sleep(5) ``` This is equivalent to the type signature string rtd. > **Note:** Generic type aliases require Python 3.12+. If you are using an earlier version of Python you will need to specify the return type as rtd using either a signature string or the @xl_return decorator. **Footnotes** 1. The generic type alias Object requires a minimum of Python 3.12 and PyXLL 5.12. 2. Indexing the RTD type requires a minimum of Python 3.9 and PyXLL 5.12. 3. The generic type aliases RTDGenerator and RTDAsyncGenerator require a minimum of Python 3.12 and PyXLL 5.12. --- ## Cell Formatting [docs/userguide/formatting/index.md](https://www.pyxll.com/docs/userguide/formatting/index.md) 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. > **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. > **Note:** Formatters applied to Dynamic Array functions make use of metadata to keep track of formatting applied in order to clear it if the array later contracts. --- ## Formatting Worksheet Functions [docs/userguide/formatting/udfformatters.md](https://www.pyxll.com/docs/userguide/formatting/udfformatters.md) 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: ```python 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: Formatter kwargs | | | |--- | ---| |interior_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.| |number_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 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. --- ## Pandas DataFrame Formatting [docs/userguide/formatting/pandasformatters.md](https://www.pyxll.com/docs/userguide/formatting/pandasformatters.md) Array formulas can also be formatted, and PyXLL provides the DataFrameFormatter class specifically for functions that return pandas DataFrames. ```python 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") 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 formatting 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 expression 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. ```python from pyxll import DataFrameFormatter, ConditionalFormatter, Formatter, xl_func import pandas as pd 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", 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. ### Custom Conditional Formatters 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. The following example shows how a custom ConditionalFormatter can be written that changes the background color of cells in a DataFrame based on their value. ```python from pyxll import xl_func, DataFrameFormatter, ConditionalFormatterBase, Formatter from matplotlib import colors, cm import pandas as pd class RainbowFormatter(ConditionalFormatterBase): def __init__(self, column, min=0, max=100, cmap="rainbow"): self.column = column self.min = min self.max = max self.cmap = cmap def get_formatters(self, df): # Create an empty DataFrame with the same index and columns as df. formatters = pd.DataFrame(None, index=df.index, columns=df.columns) # Normalize the column values into the range [0, 1] normalizer = colors.Normalize(self.min, self.max) values = normalizer(df[self.column]) # Get a list of (r,g,b,a) colors from a colormap. colormap = cm.get_cmap(self.cmap) color_values = colormap(values) # Create the Formatter objects, remembering Formatter.rgb takes integers from 0 to 255. # This could use any Formatter class, including your own custom formatters. formatters[self.column] = [ Formatter(interior_color=Formatter.rgb(int(r * 255), int(g * 255), int(b * 255))) for r, g, b, a in color_values ] # Return the DataFrame containing Formatter objects for the cells we want to format return formatters # Construct a DataFrameFormatter using our custom RainbowFormatter class. # Multiple formatters can be combined by adding them together. df_formatter = DataFrameFormatter( header=Formatter(interior_color=Formatter.rgb(255, 255, 0)), conditional_formatters=[ RainbowFormatter("A"), RainbowFormatter("B") ] ) # This worksheet function uses our DataFrameFormatter and RainbowFormatters @xl_func("int min, int max, int step: dataframe", formatter=df_formatter) def custom_formatter_test(min=0, max=100, step=5): df = pd.DataFrame({"A": range(min, max, step), "B": range(max, min, -step)}) return df ``` --- ## Custom Formatters [docs/userguide/formatting/customformatters.md](https://www.pyxll.com/docs/userguide/formatting/customformatters.md) 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: ```python 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] ] ``` 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. ```vbnet Sub Macro1() Range("D4:G8").Select With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With End Sub ``` See vba for more information on how to translate VBA code to Python. ### Combining Multiple Formatters Formatters can be combined so you do not have to implement every combination in a single formatter. Formatters are combined by adding them to each other. For example, to combine the above formatter with the standard DataFrameFormatter you add them together. ```python 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") def get_dataframe(): df = pd.DataFrame({ "A": [1, 2, 3], "B": [4, 5, 6], "C": [0.3, 0.6, 0.9] }) return df ``` --- ## Formatting in Macros Functions [docs/userguide/formatting/macroformatters.md](https://www.pyxll.com/docs/userguide/formatting/macroformatters.md) 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: ```python 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 cell.options(type="dataframe", auto_resize=True, formatter=formatter).value = df ``` The same method can be used from a menu function or ribbon action. --- ## Charts and Plotting [docs/userguide/plotting/index.md](https://www.pyxll.com/docs/userguide/plotting/index.md) 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. 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. > **Tip:** Some plotting libraries such as plotly and bokeh produce web-based interactive charts (1). PyXLL will display these charts using a web control, where possible (2). When the web control is used, the charts are interactive in Excel in the same way as they are when shown in a browser. 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. 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. Depending of which plotting library you use the plot itself will be either be inserted into Excel as an interactive web control (1), or as a static image. > **Note:** Depending on the version of Excel you are using and the plotting library, the chart may be exported as an HTML document (1) or an SVG image when plotting to Excel. If you are experiencing problems with HTML charts, or the web viewer control, you can disable exporting as HTML by passing allow_html=False to plot. This will cause it to export the image in a static image format instead. 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. ### Plotting from Worksheet Functions When you use plot from an Excel worksheet function using @xl_func the image inserted into the Excel workbook 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. > **Tip:** See plot-config for the config options available to customize how the plot function behaves. ### 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. ### Moving and Resizing Interactive HTML plots (1) use a special web control widget. To move or resize these plots your first need to enable Design Mode. To enable Design Mode, go to the Developer tab in the Excel ribbon and select Design Mode. Whilst in Design Mode a bitmap preview will be displayed instead of the web control. You can now move and resize this shape. There may be some lag between resizing the preview image and the preview image updating. To return to the interactive web widget, unselect Design Mode. Other plot types use a Picture shape, which can be moved and resized without entering Design Mode. New in PyXLL 5.7 After resizing a plot in Excel, when you change the selection to something else (e.g. click off the plot and into another cell) the figure will be redrawn to the new size of the image displayed in Excel. This can be disabled by passing allow_resize=False to the plot function, or by setting the following in your pyxll.cfg file ```ini [PYXLL] plot_auto_resize = 0 ``` The above only applies for plots that render as a static image and not as an interactive HTML plot. **Footnotes** 1. The web control for displaying html based charts is new in PyXLL 5.9.0. In earlier versions the chart will be displayed as a static image that is not interactive. 2. The web control for displaying html based charts requires the Microsoft WebView2 component to be installed. This is usually installed as part of the Microsoft Edge web browser. --- ## Matplotlib [docs/userguide/plotting/matplotlib.md](https://www.pyxll.com/docs/userguide/plotting/matplotlib.md) ### Plotting with 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. Plots created using matplotlib are displayed in Excel as images and are not interactive controls. > **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. ```python 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 automatically. 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. > **Tip:** Whenever you change an input argument to your plotting function, the chart will be redrawn. You can use this to create interactive dashboards where the Excel user can control the inputs to the plot and see it redraw automatically. ### Using matplotlib.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. ```python 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. ### Animated Plots Matplotlib can be used to create animated plots as well as static ones. These can also be used in Excel with PyXLL. > **Note:** Support for animated matplotlib plots is new in PyXLL 5.4.0. Animated plots using matplotlib are created using the matplotlib.animation.Animation type. The animation object can be passed to plot in the same way a Figure was used above. The animated plot will be rendered to an animated GIF and embedded in the Excel workbook. > **Warning:** If you see an error saying that the image cannot be displayed then this will be because your version of Excel is not capable of displaying animated GIFs and you will need to update to a newer version of Excel. The following code shows how to construct a simple animated plot with matplotlib and display the results in Excel. It can take a small amount of time to render the animation, depending on the number of frames and complexity of the plot. ```python from pyxll import xl_func, plot from matplotlib.animation import FuncAnimation from matplotlib import pyplot as plt import numpy as np @xl_func def plot_sine_wave(frequency=1, amplitude=1): # Create the matplotlib Figure object, axes and a line fig = plt.figure(facecolor='white') ax = plt.axes(xlim=(0, 4), ylim=(-2 * amplitude, 2 * amplitude)) line, = ax.plot([], [], lw=3) # The init function is called at the start of the animation def init(): line.set_data([], []) return line, # The animate function is called for each frame of the animation def animate(i): x = np.linspace(0, 4, 1000) y = np.sin(frequency * 2 * np.pi * (x - 0.01 * i)) * amplitude line.set_data(x, y) return line, # Construct the Animation object anim = FuncAnimation(fig, animate, init_func=init, frames=100, interval=20, blit=True) # Call pyxll.plot with the Animation object to render the animation # and display it in Excel. plot(anim) ``` For more information about animated plots in matplotlib please refer to the matplotlib user guide. --- ## Plotting with Pandas [docs/userguide/plotting/pandas.md](https://www.pyxll.com/docs/userguide/plotting/pandas.md) Pandas provides some convenient plotting capabilities based on the matplotlib package. Using pandas to plot DataFrames can be more convenient than using matplotlib directly, and because the result is a matplotlib figure it can be used with PyXLL’s plot function. Plots created using matplotlib via pandas are displayed in Excel as images and are not interactive controls. 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. ```python 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 matplotlib examples, when this function is called from Excel the plot is shown below the calling cell. The Pandas plot function optionally takes a matplotlib.Axes object. This can be used to plot to a specific Axes object instead of to the current matplotlib.pyplot figure. For example, for doing subplots or if you need more control over the matplotlib.Figure being used. ```python from pyxll import xl_func, plot import matplotlib.pyplot as plt 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] }) # Create the matplotlib Figure and Axes objects fig, ax = plt.subplots() # Plot a bar chart to the Axes we just created df.plot(kind='bar',x='name',y='age', ax=ax) # Show the matplotlib Figure created above plot(fig) ``` --- ## Plotly [docs/userguide/plotting/plotly.md](https://www.pyxll.com/docs/userguide/plotting/plotly.md) 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. Plotly supports interactive charts and these are displayed in Excel using an interactive web control, where available (1). The code below shows an Excel worksheet function that generates a plotly figure displayed it in Excel. ```python 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", 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. If the interactive web control is not available, the figure will instead be exported as a static 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. > **Tip:** Whenever you change an input argument to your plotting function, the chart will be redrawn. You can use this to create interactive dashboards where the Excel user can control the inputs to the plot and see it redraw automatically. If you have any problems with exporting plots as html or using the interactive web control, you can tell PyXLL to use a static image format instead by passing allow_html=False to plot. When exporting as an image and not html, an SVG image may be used. If you version of Excel does not support SVG images (Excel 2016 or earlier) or you are having problems with the SVG image not displaying correctly you can tell PyXLL to use the PNG format instead by passing allow_svg=False to plot. > **Warning:** If you are not using the interactive web control and the figure is being exported as an image, plotly launches a kaleido or orca subprocess to do the export. The first time you export an image from plotly it can take a few seconds. If you have anti-virus software installed it may warn you about this subprocess being launched. **Footnotes** 1. The interactive web control for displaying html based charts is new in PyXLL 5.9.0. In earlier versions the chart will be displayed as a static image that is not interactive. The web control for displaying html based charts requires the Microsoft WebView2 component to be installed. This is usually installed as part of the Microsoft Edge web browser. --- ## Seaborn [docs/userguide/plotting/seaborn.md](https://www.pyxll.com/docs/userguide/plotting/seaborn.md) Seaborn is a Python data visualization library based on matplotlib. It provides a high-level interface for drawing attractive and informative statistical graphics. As Seaborn builds on matplotlib it works perfectly with PyXLL’s plot function. All Seaborn plot functions use matplotlib.pyplot to plot 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. Plots created using Seaborn are displayed in Excel as images and are not interactive controls. ```python from pyxll import plot, xl_func import seaborn as sns @xl_func def sns_plot(): # Load a dataset to plot penguins = sns.load_dataset("penguins") # Plot a histogram, plotted to the current matplotlib.pyplot figure sns.histplot(data=penguins, x="flipper_length_mm", hue="species", multiple="stack") # Show the current matplotlib.pyplot figure using pyxll.plot plot() ``` As with the previous matplotlib examples, when this function is called from Excel the plot is shown below the calling cell. The Seaborn plotting functions also optionally take matplotlib.Axes objects. This can be used to plot to a specific Axes object instead of to the current matplotlib.pyplot figure. For example, for doing subplots or if you need more control over the matplotlib.Figure being used. ```python from pyxll import plot, xl_func import matplotlib.pyplot as plt import seaborn as sns @xl_func def sns_plot(): # Load a dataset to plot penguins = sns.load_dataset("penguins") # Create the matplotlib Figure and Axes objects fig, ax = plt.subplots() # Plot a histogram to the Axes we just created sns.histplot(data=penguins, x="flipper_length_mm", hue="species", multiple="stack", ax=ax) # Show the matplotlib Figure created above plot(fig) ``` > **Tip:** Whenever you change an input argument to your plotting function, the chart will be redrawn. You can use this to create interactive dashboards where the Excel user can control the inputs to the plot and see it redraw automatically. --- ## Bokeh [docs/userguide/plotting/bokeh.md](https://www.pyxll.com/docs/userguide/plotting/bokeh.md) 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. Bokeh supports interactive charts and these are displayed in Excel using an interactive web control, where available (1). The code below shows an Excel worksheet function that generates a bokeh figure and displays it in Excel. ```python # 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. If the interactive web control is not available, the figure will instead be exported as a static 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: ```default conda install selenium geckodriver firefox -c conda-forge ``` or ```default 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. > **Tip:** Whenever you change an input argument to your plotting function, the chart will be redrawn. You can use this to create interactive dashboards where the Excel user can control the inputs to the plot and see it redraw automatically. If you have any problems with exporting plots as html or using the interactive web control, you can tell PyXLL to use a static image format instead by passing allow_html=False to plot. When exporting as an image and not html, an SVG image may be used. If you version of Excel does not support SVG images (Excel 2016 or earlier) or you are having problems with the SVG image not displaying correctly you can tell PyXLL to use the PNG format instead by passing allow_svg=False to plot. > **Warning:** If you are not using the interactive web control and the figure is being exported as an image, bokeh launches a Selenium subprocess to do the export. The first time you export an image from bokeh it can take a few seconds. If you have anti-virus software installed it may warn you about this subprocess being launched. **Footnotes** 1. The interactive web control for displaying html based charts is new in PyXLL 5.9.0. In earlier versions the chart will be displayed as a static image that is not interactive. The web control for displaying html based charts requires the Microsoft WebView2 component to be installed. This is usually installed as part of the Microsoft Edge web browser. --- ## Altair [docs/userguide/plotting/altair.md](https://www.pyxll.com/docs/userguide/plotting/altair.md) 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. Altair supports interactive charts and these are displayed in Excel using an interactive web control, where available (1). The code below shows an Excel worksheet function that generates an altair figure and displays it in Excel. ```python # 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' ) # Enable some basic interactive features chart = chart.interactive() # 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. If the interactive web control is not available, the figure will instead be exported as a static image. This is done using altair_saver which also requires Selenium. Both of these must be installed before Altair can be used with PyXLL unless using the web control (1). - 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:** ```default conda install selenium geckodriver firefox -c conda-forge ``` or ```default 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. > **Tip:** Whenever you change an input argument to your plotting function, the chart will be redrawn. You can use this to create interactive dashboards where the Excel user can control the inputs to the plot and see it redraw automatically. If you have any problems with exporting plots as html or using the interactive web control, you can tell PyXLL to use a static image format instead by passing allow_html=False to plot. When exporting as an image and not html, an SVG image may be used. If you version of Excel does not support SVG images (Excel 2016 or earlier) or you are having problems with the SVG image not displaying correctly you can tell PyXLL to use the PNG format instead by passing allow_svg=False to plot. > **Warning:** If you are not using the interactive web control and the figure is being exported as an image, altair launches a Selenium subprocess to do the export. The first time you export an image from altair it can take a few seconds. If you have anti-virus software installed it may warn you about this subprocess being launched. **Footnotes** 1. The interactive web control for displaying html based charts is new in PyXLL 5.9.0. In earlier versions the chart will be displayed as a static image that is not interactive. The web control for displaying html based charts requires the Microsoft WebView2 component to be installed. This is usually installed as part of the Microsoft Edge web browser. --- ## Other Plotting Packages [docs/userguide/plotting/others.md](https://www.pyxll.com/docs/userguide/plotting/others.md) 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. 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. --- ## Excel Application Events [docs/userguide/events/index.md](https://www.pyxll.com/docs/userguide/events/index.md) New in PyXLL 5.12 The @xl_event decorators are used to register Python functions that will be called when specific Excel Application events occur. The events that can be handled are the same as the Excel Application events available through VBA, and the Python functions have the same arguments as their VBA counterparts. ### Registering Event Handlers Excel Application events can be handled using Python function decorated with one of the @xl_event decorators. When an Excel Application event fires, all the registered event handler functions will be called. When there are multiple event handler functions registered for the same event the order in which the registered functions will be called is not guaranteed. Event handler signatures mirror the VBA Application event signatures. Some events pass no arguments. Refer to xl-events-api and the Excel VBA Object Browser for the precise argument list. The following code registers a function to be called whenever the Excel Application AfterCalculate event is fired: ```python from pyxll import xl_event @xl_event("AfterCalculate") def on_after_calculate(): pass ``` To simplify registering Excel event handlers, helpers are provided for each of the Excel Application events so that the event name can be omitted. If you use auto-completion in your Python IDE then you will prefer to use these helpers over the event name. Elsewhere in these docs you will see the helpers used in preference to the named form, but both are equivalent. Each shortcut decorator uses the underscore naming style instead of the VBA camel case style. For example, the below is equivalent to the code above, but using the @xl_event.after_calculate helper in place of @xl_event("AfterCalculate") ```python from pyxll import xl_event @xl_event.after_calculate def on_after_calculate(): print("Excel has been calculated") ``` ### List of Events All of the Excel Application events are available through the @xl_event decorator and helper decorators. For a complete list of available events please see the API documentation xl-events-api. - @xl_event.new_workbook - @xl_event.sheet_selection_change - @xl_event.sheet_before_double_click - @xl_event.sheet_before_right_click - @xl_event.sheet_activate - @xl_event.sheet_deactivate - @xl_event.sheet_calculate - @xl_event.sheet_change - @xl_event.workbook_open - @xl_event.workbook_activate - @xl_event.workbook_deactivate - @xl_event.workbook_before_close - @xl_event.workbook_before_save - @xl_event.workbook_before_print - @xl_event.workbook_new_sheet - @xl_event.workbook_addin_install - @xl_event.workbook_addin_uninstall - @xl_event.window_resize - @xl_event.window_activate - @xl_event.window_deactivate - @xl_event.sheet_follow_hyperlink - @xl_event.sheet_pivot_table_update - @xl_event.workbook_pivot_table_close_connection - @xl_event.workbook_pivot_table_open_connection - @xl_event.workbook_sync - @xl_event.workbook_before_xml_import - @xl_event.workbook_after_xml_import - @xl_event.workbook_before_xml_export - @xl_event.workbook_after_xml_export - @xl_event.workbook_rowset_complete - @xl_event.after_calculate - @xl_event.sheet_pivot_table_after_value_change - @xl_event.sheet_pivot_table_before_allocate_changes - @xl_event.sheet_pivot_table_before_commit_changes - @xl_event.sheet_pivot_table_before_discard_changes - @xl_event.protected_view_window_open - @xl_event.protected_view_window_before_edit - @xl_event.protected_view_window_before_close - @xl_event.protected_view_window_resize - @xl_event.protected_view_window_activate - @xl_event.protected_view_window_deactivate - @xl_event.workbook_after_save - @xl_event.workbook_new_chart ### Event Arguments The Excel Application events are the same as the Application events available through VBA, and are documented in the VBA Object Browser in Excel, as well as online in Microsoft’s documentation. For event handler functions that take Excel objects as arguments, these arguments are passed as COM objects using the configured Python COM package. Supported Python COM packages are pywin32 (the default option) or comtypes. The following example shows how to write a function that handles the Application.SheetActivate event. In VBA, this event has a Sheet argument, and in Python the sheet is passed as a Sheet COM object using the default Python COM package. ```python from pyxll import xl_event @xl_event.sheet_activate def on_sheet_activated(sheet): """This handles the Excel Workbook.SheetActivate event and is passed a COM Sheet object.""" # The sheet is an Excel Sheet object as has the same properties as the corresponding VBA Sheet object. sheet_name = sheet.Name # Log a message to show the event has been handled. _log.info("EXAMPLE: Workbook.SheetActivate event for '%s' handled by %s" % (sheet_name, __file__)) ``` To specify which Python COM package should be used, use the com_package kwargs to @xl_event. The following example is the same as the above, but using the comtypes package. ```python from pyxll import xl_event @xl_event.sheet_activate(com_package="comtypes") def on_sheet_activated(sheet): """This handles the Excel Workbook.SheetActivate event and is passed a comtypes Sheet object.""" # The sheets is a ``comtypes`` COM object corresponding to the Excel Sheet object type sheet_name = sheet.Name # Log a message to show the event has been handled. _log.info("EXAMPLE: Workbook.SheetActivate event for '%s' handled by %s" % (sheet_name, __file__)) ``` The default com_package can be selected by setting com_package in the [PYXLL] section of the pyxll.cfg config file. ### Execution Context Event handlers run in Excel’s main thread in a macro-context. It is safe to use xl_app and Excel C API functions inside an event handler. Event handlers may be called while Excel is busy. To avoid slowing down Excel, keep handlers fast and avoid long running work. If you need to do heavier processing, schedule it on a background thread. Remember however that you cannot call back into Excel from a background thread and must use schedule_call if you need to call into Excel. Exceptions raised by event handlers are logged and do not prevent other handlers from running. Use logging in handlers to aid troubleshooting. ### Registration and Reloading Event handlers are registered when their modules are loaded by PyXLL. Handlers are un-registered and re-registered when the module they’re declared in is reloaded. ### Event Filters When registering a function as an event handler, by default, that function will be called whenever the Excel event is fired. Sometimes you might only want your handler to be called for some events. For example, if you have a SheetSelectionChange event you might want to only do something if the sheet belongs to a specific workbook. All @xl_event decorators take a filter kwarg. This can be set to another function that takes the same arguments as the event handler. It will be called before the event handler, and the event handler will only be called if the filter function returns True. Filters run on every event fire and will block Excel. Keep them lightweight to avoid performance problems. For example, the following filter function returns True if the worksheet belongs to a workbook with a specific name: ``` from pyxll import xl_event # The filter function has the same signature as the event handler def sheet_in_correct_workbook(sheet): # sheet is an Excel Worksheet COM object book = sheet.Parent # return True if the parent workbook name is what we're looking for return book.Name == "MyBookName.xlsx" @xl_event.sheet_activate(filter=sheet_in_correct_workbook) def on_sheet_activate(sheet): # This only gets called if the filter function returns True print(f"Sheet activated: {sheet.Name}") ``` Using a filter function as above avoids cluttering your event handler with conditional code that you might like to reuse between event handlers. A common pattern is to use a more generic factory function to return a filter function. We could re-write the above example with a filter function factory so that it can be reused for different workbook names as follows: ``` from pyxll import xl_event def sheet_in_workbook(workbook_name): """Return a filter function that takes a Sheet object.""" # This is the function our outer function will return and is # what will be used as our filter function. def sheet_in_correct_workbook(sheet): # sheet is an Excel Worksheet COM object book = sheet.Parent # return True if the parent workbook name matched our chosen 'workbook_name' return book.Name == workbook_name # Return the filter function that is using our workbook_name return sheet_in_correct_workbook # Now we can specify the workbook name when creating the filter function @xl_event.sheet_activate(filter=sheet_in_workbook("MyBookName.xlsx")) def on_sheet_activate(sheet): # This only gets called if the filter function returns True print(f"Sheet activated: {sheet.Name}") ``` --- ## Custom Task Panes [docs/userguide/ctps/index.md](https://www.pyxll.com/docs/userguide/ctps/index.md) Python UI controls can be embedded into Excel Custom Task Panes so they seamlessly fit in with the rest of the Excel user interface. > **Note:** For user interface controls that are directly on the worksheet, see ../activex/index. PyXLL has support for the following Python UI tookits. 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. --- ## PySide and PyQt [docs/userguide/ctps/qt.md](https://www.pyxll.com/docs/userguide/ctps/qt.md) 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. User interfaces developed using Qt can be hosted in Excel Custom Task Panes as docked or floating windows within Excel. If instead you want the control embedded directly on the Excel worksheet, see activex. 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: ```default > pip install pyside6 # or > pip install pyqt6 # or > conda install pyside6 # or > conda install "pyqt>=6" ``` 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. ### Creating a Qt Widget One of the main classes in Qt 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. ```python from PySide6 import QtWidgets # or from PyQt6 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. ### 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. ```python from pyxll import xl_menu, create_ctp, CTPDockPositionFloating from PySide6 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. 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. --- ## wxPython [docs/userguide/ctps/wxpython.md](https://www.pyxll.com/docs/userguide/ctps/wxpython.md) 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: ```default > 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/. ### 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. ```python 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() # 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. ```python 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. # 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. --- ## Tkinter [docs/userguide/ctps/tkinter.md](https://www.pyxll.com/docs/userguide/ctps/tkinter.md) 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. User interfaces developed using Tk can be hosted in Excel Custom Task Panes as docked or floating windows within Excel. If instead you want the control embedded directly on the Excel worksheet, see activex. 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. ### 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. ```python 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. ### 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. ```python 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. --- ## Other UI Toolkits [docs/userguide/ctps/others.md](https://www.pyxll.com/docs/userguide/ctps/others.md) PyXLL provides support for PySide and PyQt, 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 argument. 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. --- ## ActiveX Controls [docs/userguide/activex/index.md](https://www.pyxll.com/docs/userguide/activex/index.md) Similar to ctps, Python UI controls can also be embedded as a control as part of the Excel worksheet. These can be used for custom user interfaces that form part of the worksheet. For example, controls for a dashboard or for running more complex tasks that don’t naturally fit into Excel’s grid. > **Note:** For user interface controls that are hosted in a panel that can be floating or docked alongside the Excel worksheet see ../ctps/index. PyXLL has support for the following Python UI tookits. ActiveX controls can be created using a control or widget from any of the supported Python UI toolkits by calling the PyXLL function create_activex_control. ActiveX controls are objects embedded in the Excel worksheet. The initial position and size can be set when calling create_activex_control. For specific details of creating an ActiveX control with any of the supported Python UI toolkits see the links above. ### Resizing ActiveX Controls To move or resize the ActiveX control you first need to enable Design Mode. To enable Design Mode, go to the Developer tab in the Excel ribbon and select Design Mode. Whilst in Design Mode a bitmap preview will be displayed instead of the web control. You can now move and resize this shape. There may be some lag between resizing the preview image and the preview image updating. To return to the interactive web widget, unselect Design Mode. See the API reference for create_activex_control for more details. ### Other Considerations 1. The Python control hosted in an ActiveX control is not saved with the workbook. When opening a workbook that was saved with ActiveX controls added, by default you will see a placeholder instead of Python control. To recreate the Python control when the workbook is opened, one solution is to use a worksheet function that recalculates when the workbook is opened. By passing name to create_activex_control you can update an existing control with the newly created Python control. See activex-udf for more details about how to do this. 2. ActiveX controls don’t zoom in or out when zooming in or out of the Excel worksheet. The size of the control will change as you zoom, but the size of the contents will not automatically be scaled. You will need to bear this in mind when designing your user interface to ensure that it handles being resized correctly, if you want the Excel user to be able to change the zoom in Excel and still be able to use the control. --- ## PySide and PyQt [docs/userguide/activex/qt.md](https://www.pyxll.com/docs/userguide/activex/qt.md) 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. User interfaces developed using Qt can be embedded as ActiveX controls directly on the Excel worksheet. If instead you want your controls to appear in a docked or floating window, see ctps. 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 ActiveX 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: ```default > pip install pyside6 # or > pip install pyqt6 # or > conda install pyside6 # or > conda install "pyqt>=6" ``` 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. > **Tip:** Any of PySide2, PySide6, PyQt5 and PyQt6 can be used with PyXLL. ### Creating a Qt Widget One of the main classes in Qt 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 an ActiveX control. 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. ```python from PySide6 import QtWidgets # or from PyQt6 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.""" # 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. ### Creating an ActiveX control from a Qt Widget To show a QWidget in Excel as an ActiveX control we use the create_activex_control 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_activex_control. 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 ActiveX control from many different places, but usually it will be from a ribbon function, or a menu function, or a worksheet function The following code shows how we would create an ActiveX control from an Excel menu function, using the ExampleWidget control from the example above. ```python from pyxll import xl_menu, create_activex_control from PySide6 import QtWidgets # or from PyQt6 import QtWidgets @xl_menu("Qt ActiveX Control") def qt_activex_control(): # Before we can create a Qt widget the Qt App must have been initialized. # Make sure we keep a reference to this until create_activex_control 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_activex_control' function to create the ActiveX control. create_activex_control(widget) ``` When we add this code to PyXLL and reload the new menu function “Qt ActiveX Control” will be available, and when that menu function is run the ExampleWidget is created as an ActiveX control directly in the current Excel worksheet. The optional parameters to create_activex_control can be used to set the size, position, and what sheet the control is created on. To move or resize the ActiveX control you first need to enable Design Mode. To enable Design Mode, go to the Developer tab in the Excel ribbon and select Design Mode. Whilst in Design Mode a bitmap preview will be displayed instead of the web control. You can now move and resize this shape. There may be some lag between resizing the preview image and the preview image updating. To return to the interactive web widget, unselect Design Mode. See the API reference for create_activex_control for more details. --- ## wxPython [docs/userguide/activex/wxpython.md](https://www.pyxll.com/docs/userguide/activex/wxpython.md) 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. User interfaces developed using wxWindows can be embedded as ActiveX controls directly on the Excel worksheet. If instead you want your controls to appear in a docked or floating window, see ctps. Both wxWindows can be installed using pip or conda, for example: ```default > 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/. ### 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 an ActiveX control. 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. ```python 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, flag=wx.ALL | wx.EXPAND, border=10) sizer.AddSpacer(20) # Create a StaticText control and add it to the layout self.static_text = wx.StaticText(self) sizer.Add(self.static_text, flag=wx.ALL | wx.EXPAND, border=10) # 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) # 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() # 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 an ActiveX control from a wx.Frame To show a wx.Frame in Excel using PyXLL as an ActiveX control we use the create_activex_control 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_activex_control. 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 ActiveX control from many different places, but usually it will be from a ribbon function, or a menu function, or a worksheet function The following code shows how we would create a ActiveX control from an Excel menu function, using the ExampleFrame control from the example above. ```python from pyxll import xl_menu, create_activex_control import wx @xl_menu("Wx ActiveX Control") def wx_activex_control(): # Before we can create a wx.Frame the wx.App must have been initialized. # Make sure we keep a reference to this until create_activex_control 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_activex_control' function to create the ActiveX control. create_activex_control(frame, width=400, height=400) ``` When we add this code to PyXLL and reload the new menu function “Wx ActiveX Control” will be available, and when that menu function is run the ExampleFrame is opened as an ActiveX control in Excel. The optional parameters to create_activex_control can be used to set the size, position, and what sheet the control is created on. To move or resize the ActiveX control you first need to enable Design Mode. To enable Design Mode, go to the Developer tab in the Excel ribbon and select Design Mode. Whilst in Design Mode a bitmap preview will be displayed instead of the web control. You can now move and resize this shape. There may be some lag between resizing the preview image and the preview image updating. To return to the interactive web widget, unselect Design Mode. See the API reference for create_activex_control for more details. --- ## Tkinter [docs/userguide/activex/tkinter.md](https://www.pyxll.com/docs/userguide/activex/tkinter.md) 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. User interfaces developed using Qt can be embedded as ActiveX controls directly on the Excel worksheet. If instead you want your controls to appear in a docked or floating window, see ctps. 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. ### 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. ```python 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. ### Creating an ActiveX control from a tkinter.Frame To show a tkinter.Frame in Excel using PyXLL we use the create_activex_control 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 ActiveX control from many different places, but usually it will be from a ribbon function, or a menu function, or a worksheet function The following code shows how we would create an ActiveX control from an Excel menu function, using the ExampleFrame control from the example above. ```python from pyxll import xl_menu, create_activex_control import tkinter as tk @xl_menu("Tk ActiveX Control") def tk_activex_control(): # Create the top level Tk window window = tk.Toplevel() # Create our example frame from the code above and add # it to the top level window. frame = ExampleFrame(master=window) # Use PyXLL's 'create_activex_control' function to create the ActiveX control. create_activex_control(window, width=400, height=400) ``` When we add this code to PyXLL and reload the new menu function “Tk ActiveX Control” will be available, and when that menu function is run the ExampleFrame is opened as an ActiveX control in Excel. The optional parameters to create_activex_control can be used to set the size, position, and what sheet the control is created on. To move or resize the ActiveX control you first need to enable Design Mode. To enable Design Mode, go to the Developer tab in the Excel ribbon and select Design Mode. Whilst in Design Mode a bitmap preview will be displayed instead of the web control. You can now move and resize this shape. There may be some lag between resizing the preview image and the preview image updating. To return to the interactive web widget, unselect Design Mode. See the API reference for create_activex_control for more details. --- ## Other UI Toolkits [docs/userguide/activex/others.md](https://www.pyxll.com/docs/userguide/activex/others.md) PyXLL provides support for PySide and PyQt, 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 AtxBridgeBase class. The ActiveX 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 AtxBridgeBase for details of the methods you need to implement. Once you have implemented your ActiveX Bridge you pass it to create_activex_control as the bridge_cls keyword argument. Whatever object you pass as the widget to create_activex_control will be used to construct your ActiveX Bridge object. PyXLL will take care of the rest of embedding your widget into Excel. > **Warning:** Writing an ActiveX 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. --- ## Using Pandas in Excel [docs/userguide/pandas.md](https://www.pyxll.com/docs/userguide/pandas.md) Pandas DataFrames are Excel are a great match as both deal with 2d tables of data. With PyXLL, you can pass data as pandas DataFrames between Python and Excel easily. > **Note:** You can also use polars DataFrames as well as pandas. To distinguish between polars and pandas, use the polars.dataframe and pandas.dataframe types instead of just dataframe in your function signatures. If you use just dataframe PyXLL will default to using pandas. See polars-types for more information about polars types in PyXLL. ### Returning a DataFrame from a Function By far one of the most common ways to work with pandas data in Excel is with a worksheet function that returns a DataFrame. If you are not already familiar with writing Excel worksheet functions in Python using PyXLL, please see worksheet_functions first. This video demonstrates how to write worksheet functions in Python, using PyXLL. It also covers returning pandas DataFrames from functions towards the end of the video. Consider the following function that returns a panda DataFrame: ```python import pandas as pd from pyxll import xl_func @xl_func def return_dataframe(): """A function that returns a DataFrame.""" df = pd.DataFrame({ "A": [1, 2, 3], "B": [4, 5, 6] }) return df ``` The @xl_func decorator exposes the return_dataframe function to Excel as a worksheet function, but when we call this function from Excel the result might not be exactly what you expected: The DataFrame object here is being returned as an object handle. This object handle can be passed to other PyXLL functions and the Python function will be called with the actual pandas DataFrame object. This is really useful (and very fast) as it allows us to pass large, complex objects between Python functions easily. However, it is not what we want in this case! We need to tell the PyXLL add-in to convert the returned DataFrame object into a range of values so that all of the DataFrames values get returned to the Excel grid. To do that, we need to specify the return type when declaring the function. That is done either by adding a function signature to the @xl_func decorator; using the @xl_return decorator; or by using a Python type annotation. The simplest method is to add a function signature to our function as follows: ```python import pandas as pd from pyxll import xl_func @xl_func(": dataframe") def return_dataframe(): """A function that returns a DataFrame.""" df = pd.DataFrame({ "A": [1, 2, 3], "B": [4, 5, 6] }) return df ``` Note that we don’t have any arguments. The function signature is of the form argument types: return type and so in our signature above we don’t have anything before the : as there are no arguments to the function. With the return type now specified, when we call the function in Excel the PyXLL add-in will convert the pandas DataFrame object to an array of values: The dataframe return type can be parameterized using various options. These options control how the pandas DataFrame object is converted to the array that you see in Excel. For example, if you want the index to be included in the array shown in Excel, you can set the index option to True (by default, the index is only included if it is a named index): ```python import pandas as pd from pyxll import xl_func @xl_func(": dataframe") def return_dataframe(): """A function that returns a DataFrame.""" df = pd.DataFrame({ "A": [1, 2, 3], "B": [4, 5, 6] }) return df ``` For more details of how to specify return types and their type parameters, please see function-signature. You can find the documentation for all of the available parameters for the dataframe return type on the pandas-types page. ### Taking a DataFrame as a Function Argument In the above section we saw how by specifying the function return type, PyXLL can convert the returned pandas DataFrame to an array of values on the Excel grid. Similarly, PyXLL can also convert a range of values from the Excel grid passed to a function into a pandas DataFrame. Let’s look at an example function that takes a DataFrame argument, as well as a string and returns a number: ```python import pandas as pd from pyxll import xl_func @xl_func("dataframe df, str col: float") def sum_column(df, col): """Sum the values in a column.""" column_sum = df[col].sum() return column_sum ``` The function signature passed to @xl_func specifies that the argument df should be converted from a range of Excel values to a pandas DataFrame. To call this from Excel we enter it as a formula, passing in our data range including the column headers, and the column we want to sum. PyXLL converts the input range argument to a pandas DataFrame for us and calls our sum_column function with that converted DataFrame. We can also specify various type parameters to the dataframe argument type, as with the return type. For example, the index option can be used to specify the number of columns to use as the index for the DataFrame (if supplying an index). Instead of using the function signature as shown above, another option is to use the @xl_arg decorator. This works in the same way, but lets us specify the argument type parameters as keyword arguments to the @xl_arg decorator. For example, the function above could be re-written as follows: ```python import pandas as pd from pyxll import xl_func, xl_arg, xl_return @xl_func @xl_arg("df", "dataframe") @xl_arg("col", "str") @xl_return("float") def sum_column(df, col): """Sum the values in a column.""" column_sum = df[col].sum() return column_sum ``` And to specify that the first column is to be used as the DataFrame index, we would set the index type parameter like so: ```python import pandas as pd from pyxll import xl_func, xl_arg, xl_return @xl_func @xl_arg("df", "dataframe", index=1) # equivalent to "dataframe" @xl_arg("col", "str") @xl_return("float") def sum_column(df, col): """Sum the values in a column.""" column_sum = df[col].sum() return column_sum ``` This time, the DataFrame passed to our sum_column function has its index constructed from the first column in the Excel range. Setting index to greater than one results in a DataFrame with a MultiIndex. For more details of how to specify arguments types and their type parameters, please see function-signature. You can find the documentation for all of the available parameters for the dataframe argument type on the pandas-types page. ### Trimming DataFrame Arguments New in PyXLL 5.11 When passing a DataFrame as an argument to an Excel function sometimes you might want to pass a larger range than the actual data. For example, when creating a sheet you might allow some extra rows in case the data grows, or to allow space for the user of the sheet to add more rows. You might even select a range of entire columns. Converting a larger range than necessary takes additional time when constructing the DataFrame. Instead of constructing the entire DataFrame and then removing empty rows in your Python code, it is much more efficient (and easier!) for PyXLL to trim any trailing empty rows or columns from the Excel data before constructing the DataFrame. To tell PyXLL to trim empty space use the trim type parameter to the dataframe type. For example: ```python import pandas as pd from pyxll import xl_func @xl_func("dataframe df, str col: float") def sum_column(df, col): """Sum the values in a column.""" column_sum = df[col].sum() return column_sum ``` The function above can now accept a large range including empty rows and columns around the actual data. Before the DataFrame is constructed the data is trimmed to remove empty rows and columns from the top, bottom, left and right of the data. When specifying trim=True any empty rows from the top or bottom of the range, or empty columns from the left or right of the range, will be removed. If you only want to trim rows or columns from certain directions instead of using trim=True you can use a string to specify top, left, bottom and/or right by using the characters t, l, b, r, respectively. For example, to only trim rows from the bottom and columns from the right you would use trim='br'. ### Returning Multiple DataFrames Sometimes you might have a function that returns more than one DataFrames. When returning values to Excel, the result has to fit into a grid and so returning multiple DataFrames doesn’t fit as well as a single DataFrame. If it makes sense to break your function into multiple functions, with each one returning a different DataFrame, that is a good solution. But, if that’s not feasible or results in unwanted recomputation and you need to keep it all in one function, you can return multiple DataFrames from a single function. Rather than returning multiple DataFrames as multiple grids of data from a single function, instead we have to return them as a list of objects. By returning them as a list of objects this then fits into Excel’s grid. ```python import pandas as pd from pyxll import xl_func @xl_func(": object[]") def multiple_dataframes(): """A function that returns multiple DataFrames.""" df1 = pd.DataFrame({ "A": [1, 2, 3], "B": [4, 5, 6] }) df2 = pd.DataFrame({ "X": [-1, -2, -3], "Y": [-4, -5, -6] }) return [ df1, df2 ] ``` Above we’re using the object[] return type. The list of DataFrames returned by the function are returned to Excel as an array of object handles: Our function returns multiple DataFrames, but as an array of object handles. Next we need to convert those object handles into Excel arrays, which we can do with another function. This next function doesn’t do anything, but by specifying dataframe as the return type PyXLL will do the conversion for us: ```python import pandas as pd from pyxll import xl_func @xl_func("object df: dataframe") def expand_df(df): if not isinstance(df, pd.DataFrame): raise TypeError("Expected a DataFrame object") # Will be converted to an Excel array because of the return type return df ``` We can now call this new function on the objects returned previously to get the contents of the DataFrames: ### Formatting DataFrames PyXLL has a feature for applying cell formatting to the results of a worksheet function. This can be very useful for giving your spreadsheets a consistent look and feel, as well as saving time applying formatting manually. For complete details about cell formatting, please see the formatting and pandas-formatting sections of the user guide. The following video also explains how the cell formatting functionality can be used: ### Plotting DataFrames Pandas DataFrames can be plotted using the DataFrame.plot method. This is a convenient way to quickly produce plots from DataFrames. The DataFrame.plot method uses the plotting package matplotlib. The PyXLL add-in can display matplotlib figures directly in Excel, and so it is also possible to display plots of pandas DataFrames in Excel. The function plot takes a matplotlib figure and displays it in Excel. To display a pandas plot we first create a matplotlib Figure and Axes. Then we call DataFrame.plot, passing in the Axex object as we want it to plot to the matplotlib figure we created. Finally, we call plot to display the figure in Excel. ```python from pyxll import xl_func, plot import matplotlib.pyplot as plt 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] }) # Create the matplotlib Figure and Axes objects fig, ax = plt.subplots() # Plot a bar chart to the Axes we just created df.plot(kind='bar',x='name',y='age', ax=ax) # Show the matplotlib Figure created above plot(fig) return "OK!" ``` The above code creates a DataFrame. It then creates a matplotlib figure and plots the DataFrame onto that figure. Finally it displays the plot in Excel using the plot function. For more details on plotting with PyXLL, and plotting pandas DataFrames in Excel, please see pandas-plotting. ### Reading and Writing DataFrames in Macros Macro functions can be called from Excel buttons and other controls, as well as from VBA. Excel macros can automate Excel in the same way as VBA. They can also read and write values in the Excel workbooks. If you’re not already familiar with writing macro functions using PyXLL, please see writing-macros. With PyXLL, the entire Excel object model is exposed (see vba), but for dealing with pandas DataFrames (and other types) the Python values need to converted to something that Excel understands. This can be done using PyXLL’s XLCell class. In a macro function we can get the Excel Application object using xl_app. This is the same as the VBA Application object. From that, we can get the Range object for which we want to either read or write the DataFrame. Once we have the Range object, we use XLCell.from_range to get a PyXLL XLCell object. The XLCell object is similar to the Range object. It’s simpler in most ways, and can only be used for a contigous range, but it allows us to use PyXLL’s type conversion when getting or setting its value property. To read or write the cells’ value as a DataFrame we pass type="dataframe" to the XLCell.options method before accessing the value property. ```python from pyxll import xl_macro, xl_app, XLCell @xl_macro def read_value_from_excel(): # Get the Excel.Application COM object xl = xl_app() # Get a Range in the current active sheet xl_range = xl.ActiveSheet.Range("A1:D10") # Get an XLCell object from the Range object cell = XLCell.from_range(xl_range) # Get the value as a DataFrame df = cell.options(type="dataframe").value ``` The above macro reads cells A1:D10 of the currently active sheet as a pandas DataFrame. The type argument to XLCell.options is the same as the type used in the function signature in the previous sections. It can be parameterized in the same way to control exactly how the conversion is done between the Excel values and the pandas DataFrame. > **Tip:** You can pass auto_resize=True to XLCell.options when reading and writing DataFrames and the cell with automatically resize to fit the data, so you don’t need to specify the full range. ### Passing DataFrames as Objects Instead of 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", 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. > **Note:** DataFrames returned from Excel to Python as cached objects are not copied. When the object handle is passed to another function, the object retrieved from the cache is the same object that was previously returned. You should be careful not to modify DataFrames passed this way (i.e. don’t make changes inplace). Instead create a copy first and modify the copy, or use inplace=False if the pandas method you’re using supports that. ### Pandas Series pandas Series objects can be used in a similar way to DataFrames. Instead of using the dataframe type as described in the sections below, the series type is also available. Please see pandas-types for details of all of the pandas types available, including the series type. ### Advanced Usage of the Pandas Type Converters 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. You might have a function that takes the var type, which could be a DataFrame depending on other arguments. Or, you might want to return a DataFrame but want to decide in your function what type parameters to use to control the conversion. For example, you might want to leave it to use the user to decide whether to include the index or not and give that as an argument to your function. 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") df = to_dataframe(data) ``` Or the other way: ``` to_array = get_type_converter("dataframe", "var") data = to_array(df) ``` Combined with PyXLL’s var type you can pass in values from Excel as a plain array and perform the conversion in your function. Or, you can specify the return type of your function as var and convert from the DataFrame before returning the final converted value. For example: ```python import pandas as pd from pyxll import xl_func, get_type_converter ``` ```python import pandas as pd from pyxll import xl_func @xl_func("bool include_index: var") def return_dataframe(include_index=False): """A function that returns a DataFrame.""" # Create the dataframe df = pd.DataFrame({ "A": [1, 2, 3], "B": [4, 5, 6] }) # Get the function to convert from a DataFrame to the PyXLL 'var' # return type, specifying the 'index' type parameter. to_var = get_type_converter("dataframe", "var", src_kwargs={ "index": include_index }) # Convert the DataFrame and return the result converted_df = to_var(df) return converted_df ``` The above function returns a DataFrame. But, rather than using the dataframe return type to do the conversion implicitly, it uses the var return type and performs the conversion inside the function. --- ## Customizing the Ribbon [docs/userguide/ribbon.md](https://www.pyxll.com/docs/userguide/ribbon.md) ### 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). 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. ### Creating a Custom Tab - Create a new ribbon xml file. The one below contains a single tab Custom Tab and a single button. ```xml