Configuration

The config file is a plain text file that should be kept in the same folder as the PyXLL addin .xll file, and should be called the same as the addin but with the extension .cfg. In most cases this will simply be pyxll.cfg.

Paths used in the config file may be absolue or relative paths. Any relative paths should be relative to the config file.

Config values may contain environment variable substitutions. To substitute an environment variable into your value use %(envvar_name)s, e.g.

[LOG]
path = %(TEMP)s
file = %(LOG_FILE)s

Python Settings

[PYTHON]
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

The Python settings determine which Python interpreter will be used, and some Python settings.

If you don’t specify anything in the Python section then your system default Python settings will be used. Depending on how Python was installed on your system this may be fine, but sometimes you may want to specify different options from your system default; for example, when using a Python virtual environment or if the Python you want to use is not installed as your system default Python.

  • pythonpath

    The pythonpath is a list of directories that Python will search in when importing modules.

    When writing your own code to be used with PyXLL you will need to change this to include the directories where that code can be imported from.

    [PYTHON]
    pythonpath =
        c:\path\to\your\code
        c:\path\to\some\more\of\your\code
        .\relative\path\relative\to\config\file
    
  • executable

    If you want to use a different version of Python than your system default Python then setting this option will allow you to do that.

    Note that the Python version (e.g. 2.7 or 3.5) must still match whichever Python version you selected when downloading PyXLL, but this allows you to switch between different virtual environments or different Python distributions.

    PyXLL does not actually use the executable for anything, but this setting tells PyXLL where it can expect to find the other files it needs as they will be installed relative to this file (e.g. the Python dll and standard libraries).

    [PYTHON]
    executable = c:\path\to\your\python\installation\pythonw.exe
    
  • dll

    Usually setting the Python executable will be enough so that PyXLL can find the dll without further help, but if your installation is non-standard or you need to tell PyXLL to use a specific dll for any reason then this setting may be used for that.

    [PYTHON]
    dll = c:\path\to\your\python\installation\pythonXX.dll
    
  • pythonhome

    The location of the standard libraries will usually be determined from with the system default Python installation or by looking for them relative to the Python executable.

    If for any reason the standard libraries are not installed relative to the chosen Python 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.

    [PYTHON]
    pythonhome = c:\path\to\your\python\installation
    

PyXLL Settings

[PYXLL]
modules = comma or new line delimited list of python modules
ribbon = filename of a ribbon xml document
developer_mode = 1 or 0 indicating whether or not to use the developer mode
error_cache_size = maximum number of exceptions to cache for failed function calls
external_config = paths of additional config files to load
name = name of the addin visible in Excel
allow_abort = 1 or 0 to set the default value for the allow_abort kwarg
auto_resize_arrays = 1 or 0 to enable automatic resizing of all array functions
quiet = 1 or 0 to disable all start up messages
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)
  • 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 locations of these modules must be on the pythonpath, which can be set in the [PYTHON] config section.

  • ribbon

    If set, the ribbon setting should be the file name of custom ribbon user interface XML file. The file name may be an absolute path or relative to the config file.

    The XML file 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.

    See the Customizing the Ribbon chapter for more details.

  • developer_mode

    When the developer mode is active a PyXLL menu with a Reload menu item will be added to the Addins toolbar in Excel.

    If the developer mode is inactive then no menu items will be automatically created so the only ones visible will be the ones declared in the imported user modules.

    This setting defaults to off (0) if not set.

  • 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.

  • external_config

    This setting may be used to reference another config file (or files) located elsewhere.

    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.

  • 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’.

  • allow_abort

    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.

  • auto_resize_arrays

    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. If unset the default is 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.

  • 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 excluded from being reloaded.

    This setting defaults to off (0) if not set.

  • 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).

    [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 it can be added to this list to be ignored.

    Excluding a package (or sub-package) has the effect of ignoring anything within that package or sub-package. For example, if there are modules ‘a.b’ and ‘a.c’ then excluding ‘a’ will also exclude ‘a.b’ and ‘a.c’.

    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.

License Key

[LICENSE]
key = license key
file = path to shared license key file

If you have a PyXLL license key you should set it in [LICENSE] section of the config file.

The license key may be embedded in the config as a plain text string, or it can be referenced as an external file containing the license key. This can be useful for group licenses so that the license key can be managed centrally without having to update each user’s configuration when it is renewed.

  • key

    Plain text license key as provided when you purchased PyXLL.

    This does not need to be set if you are setting file.

  • file

    Path 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.

Logging

PyXLL redirects all stdout and stderr to a log file. All logging is done using the standard logging python module.

The [LOG] section of the config file determines where logging information is redirected to, and the verbosity of the information logged.

[LOG]
path = directory of where to write the log file
file = filename of the log file
format = format string
verbosity = logging level (debug, info, warning, error or critical)

PyXLL creates some configuration substitution values that are useful when setting up logging.

Substitution Variable Description
pid process id
date current date
xlversion Excel version
  • path

    Path where the log file will be written to.

    This may include substitution variables as listed above, e.g.

    [LOG]
    path = C:/Temp/pyxll-logs-%(date)s
    
  • file

    Filename of the log file.

    This may include substitution variables as listed above, e.g.

    [LOG]
    file = pyxll-log-%(pid)s-%(xlversion)s-%(date)s.log
    
  • format

    The format string is used by the logging module to format any log messages. An example format string is:

    [LOG]
    format = "%(asctime)s - %(name)s - %(levelname)s - %(message)s"
    

    For more information about log formatting, please see the logging module documentation from the Python standard library.

  • 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.

Environment Variables

For some python modules it can be helpful to set some environment variables before they are imported. Usually this would be done in the environment running the python script, but in Excel it’s more complicated as it would require either changing the global environment variables on each PC, or using a batch script to launch Excel.

For this reason, it’s possible to set environment variables in the [ENVIRONMENT] section of the config file.

[ENVIRONMENT]
NAME = VALUE

For each environement variable you would like set, add a line to the [ENVIRONMENT] section.

Shortcuts

Macros can have keyboard shortcuts assigned to them by using the shortcut keyword argument to xl_macro. Alternatively, these keyboard shortcuts can be assigned, or overridden, in the config file.

Shortcuts should be one or more modifier key names (Ctrl, Shift or Alt) and a key, separated by the ‘+’ symbol. For example, ‘Ctrl+Shift+R’. If the same key combination is already in use by Excel it may not be possible to assign a macro to that combination.

The PyXLL developer macros (reload and rebind) can also have shortcuts assigned to them.

[SHORTCUTS]
pyxll.reload = Ctrl+Shift+R
module.macro_function = Alt+F3