PyXLL Settings¶

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 Customizing the Ribbon chapter for more details.

• developer_mode

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

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

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

• name

  The name setting, if set, changes the name of the addin as it appears in Excel.

  When using this setting the addin in Excel is indistinguishable from any other addin, and there is no reference to the fact it was written using PyXLL. If there are any menu items in the default menu, that menu will take the name of the addin instead of the default ‘PyXLL’.

• category

  The category setting changes the default category used when registering worksheet functions with xl_func.

• external_config

  This setting may be used to reference another config file (or files) located elsewhere, either as a relative or absolute path or as a URL.

  For example, if you want to have the main pyxll.cfg installed on users’ local PCs but want to control the configuration via a shared file on the network you can use this to reference that external config file.

  Multiple external config files can be used by setting this value to a list of file names (comma or newline separated) or file patterns.

  Values in external config files override what’s in the parent config file, apart from pythonpath, modules and external_config which get appended to.

  In addition to setting this in the config file, the environment variable PYXLL_EXTERNAL_CONFIG_FILE can be used. Any external configs set by this environment variable will be added to those specified in the config.

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

  [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

  [PYXLL]
  get_cached_object_id = module_name.get_custom_object_id
  

  def get_custom_object_id(obj):
      return "[Cached %s <0x%x>]" % (type(obj), id(obj))
  

  The computed id must be unique as it’s used when passing these objects to other functions, which retrieves them from the cache by the id.

• clear_object_cache_on_reload

  Clear the object cache when reloading the PyXLL add-in.

  Defaults to 1, but if using cached objects that are instances of classes that aren’t reloaded then this can be set to 0 to avoid having to recreate them when reloading.

• recalc_cached_objects_on_open

  If set, default all functions that return cached objects as 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 Recalculating On Open.

• disable_loading_objects

  If set, any cached objects saved as part of a workbook will be ignored when opening the workbook.

  Defaults to 0.

  See Saving Objects in the Workbook.

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:

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 Asynchronous Functions.

  New in PyXLL 4.2.0.

• start_event_loop

  Used to provide an alternative implementation of the asyncio event loop used by PyXLL.

  May be set to the fully qualified name of a function that takes no arguments and returns a started asyncio.AbstractEventLoop.

  If this option is set then stop_event_loop should also be set.

  See Asynchronous Functions.

  New in PyXLL 4.2.0.

• stop_event_loop

  Used to provide an alternative implementation of the asyncio event loop used by PyXLL.

  May be set to the fully qualified name of a function that stops the event loop started by the function specified by the option start_event_loop.

  If this option is set then start_event_loop should also be set.

  See Asynchronous Functions.

  New in PyXLL 4.2.0.

win32com Settings¶

• win32com_gen_path

  This sets the win32com.__gen_path__ path used for win32com’s 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 Recalculating On Open.

• rtd_volatile_default

  Make all RTD functions volatile by default. This restores the behaviour prior to PyXLL 4.5.0.

  When enabled RTD functions are volatile so they will be calculated when opening a workbook, but the wrapped Python function will only be called if the arguments to the function are actually changed.

  Usually this should be left disabled as RTD functions are now calculated when the workbook opens using the Recalculating On Open feature of PyXLL instead.

  Defaults to 0.

CTP Settings¶

• ctp_timer_interval

  Time in seconds between calls to CTPBridgeBase.on_timer.

  The CTP bridge classes are what integrate the Python UI toolkit with the Excel Windows message loop. They use on_timer to poll their own message queues. If you are finding the panel is not responsive enough you can reduce the timer interval with this setting.

  This can also be set for each CTP by passing timer_interval to :py:func`create_ctp`.

  New in PyXLL 5.1

Metadata¶

• metadata_custom_xml_namespace

  Custom metadata is saved in order to support certain features of PyXLL such as recalculating cells when a workbook opens.

  This is saved in the workbook as a CustomXMLPart using an XML namespace specific to the PyXLL add-in so as not to conflict with data saved by other add-ins. If you have specified a name for your add-in using the name setting that will be used to avoid conflict with any other PyXLL add-ins you may have loaded.

  If you prefer to specify the namespace to use instead of having PyXLL use it’s own namespace you can do so by setting this option.

  [PYXLL]
  metadata_custom_xml_namespace = urn:your_name:metadata
  

• disable_saving_metadata

  Set this option to disable writing any metadata.

  Note that this will affect all PyXLL features that require metadata such as recalculating on open, as well as formatting dynamic arrays.

  The default is 0 (not disabled).

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.

Other Settings¶

• startup_script

  Path or URL of a batch or Powershell script to run when Excel starts.

  This script will be run when Excel starts, but before Python is initialized. This is so that the script can install anything required by the add-in on demand when Excel runs.

  See Startup Script.

• disable_com_addin

  PyXLL is packaged as a single Excel addin (the pyxll.xll file), but it actually implements both a standard XLL addin and COM addin in the same file.

  Setting disable_com_addin to 1 stops the COM addin from being used.

  The COM addin is used for ribbon customizations and RTD functions and if disabled these features will not be available.

• disable_recalc_on_open

  Disable any automatic recalculations when a workbook is opened that would otherwise be caused by the Recalculating On Open feature.

  This does not stop Excel from calculating anything else, such as volatile functions or other dirty cells in the saved workbook.

  See Recalculating On Open.

• 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 Setuptools Entry Points.

• quiet

  The quiet setting is for use in enterprise settings where the end user has no knowledge that the functions they’re provided with are via a PyXLL addin.

  When set PyXLL won’t raise any message boxes when starting up, even if errors occur and the addin can’t load correctly. Instead, all errors are written to the log file.