See What’s new in PyXLL 5 for an overview of the new features in PyXLL 5.
Tip
Looking to upgrade to the latest version of PyXLL, or switch to an older one?
Update the PyXLL package to the latest, or the version you need, using pip:
pip install --upgrade pyxll
# Or, for a specific version: pip install --upgrade "pyxll==x.x.x"
Use the pyxll update command to update the installed Excel add-in:
pyxll update
# Or, for a specific version: pyxll update --version x.x.x
See How to Upgrade PyXLL for more detailed instructions, including how to upgrade manually.
Patch Release:
Fixed a regression in 5.10.0 when loading certain versions of PySide and PyQt.
Long environment variables more than 260 characters can be used in the config file.
Fixed a warning about a style being applied while already formatting when a formatter is applied to a function that is recalculated on open.
Bokeh plots no longer warn about resources not being set, and default to using inline resources.
Fixed an error message about ActiveX controls not being able to render a preview when first opening some workbooks.
The included altair example function now enables basic interactivity.
All altair chart types can be exported, not just altair.Chart.
Environment variables in the config file that are commented out no longer get written to the log file.
Important:
The code signing certificate that is used to sign the PyXLL add-in has changed
If you do not allow loading of untrusted add-ins you will need to add the new PyXLL certificate as a Trusted Publisher, even if you have already trusted the previous certificate.
Excel may prompt you to do this the first time the add-in is loaded, or if you are in an enterprise environment you may need to ask your IT department to add the certificate for you.
This FAQ article explains more about how to add PyXLL as a Trusted Publisher.
New Features:
Added a new option to xl_func to recalculate when reloading. This can be used for functions
that return objects so they automatically recalculate after reloading the add-in.
This can also be enabled in the config file by setting recalc_cached_objects_on_reload = 1 to recalculate
all functions that have returned objects on reloading, and recalc_rtd_on_reload = 1 to recalculate all
RTD functions on reloading.
New mechanism for proving default kwargs to the xl_func, xl_macro, and xl_menu
decorators.
This can be useful for things like customizing how function names appear in Excel, or defaulting the function categories.
Extra logging attributes ‘address’, ‘sheet’, and ‘cell’.
These new attributes can be used in the log format string to log the calling cell, if called from an Excel worksheet function.
For example, the log format string %(levelname)s:%(address): %(message)s will include the calling cell
address for each log entry logged within a worksheet function invocation.
These new attributes will be blank (empty string) when logging outside of a worksheet function.
See Logging.
Different log formats can be specified for different log levels.
In addition to the existing format setting in the [LOG] section of the pyxll.cfg file, the following
settings can now also be used:
debug_format
info_format
warning_format
error_format
critical_format
It is not necessary to set all of these. If one is not set then the format from the next lowest log level will be used.
See Logging.
Improvements:
Various improvements and fixes to the behaviour of the WebView2 plot control introduced in 5.9.
Loading certain OpenSSL DLLs when using Anaconda Python distributions can be problematic. A patch to Python’s DLL loading has been added that in many cases will work around this issue.
This can be disabled by setting disable_load_library_hook = 1 in the [PYXLL] section of the config.
Cell formatting imporovements:
Clearing the style from a range when applying cell formatting is now significantly faster for large ranges in most cases.
Formatting now checks for an infinite loop caused by a formatter triggering a recalculation, which then re-triggers the formatting. The standard formatters shouldn’t do this, but it is possible to write a custom formatter that does.
The pyxll install and pyxll activate commands have a new switch --install-first, which will cause
the PyXLL add-in to be installed before any other existing add-ins.
Errors in the log format string would result in nothing being logged. Now any formatting errors are written to the log file, along with the log messages without any formatting applied.
Minor user interface improvements for licensing information and About dialogs.
Fixes:
Constructing a DataFrame with no column headers or index from a single cell is now possible.
Fixed an issue that was causing some widgets to not be closed correctly when a Custom Task Pane was closed.
Clearing cached objects would fail if an Excel event handler failed to register due to a bug in some versions of Excel. A fallback has been implemented to work around this and ensure objects are always cleared.
Fix a error message when returing arrays from macros with the auto_resize_arrays = 1 option set.
contextvars set by xlfCaller could leak between worksheet function calls when triggering
further recalculations from those worksheet functions, and that has now been resolved.
Fix an issue that was causing occasional crashes when clicking on plots for some users.
Fixed a regression in get_type_converter that could cause it to get slower over time.
RTD array functions using the old-style Ctrl+Shift+Enter method were not working since 5.9.0, and this is now fixed.
PyQt5 no longer always includes a qt.conf file, which was resulting in a warning being incorrectly logged.
Patch Release:
Extra keyword arguments passed to plot should be passed to the underlying plotting method, but that was missed
when adding html plotting for plotly and altair. This is now fixed and extra kwargs are now correctly forwarded to
plotly.io.write_html and altair’s Chart.to_json().
Calling plot with a name longer than 32 characters would silently fail when using the new html plots, leaving
a detached ActiveX control that could not be deleted. Now an error will be raised if the name is too long
Fix a regression since 5.8.4 in the Custom Task Panes feature that would result in the CTP closing when the window style changed (which can happen when creating a child modal dialog).
Optional arguments to UDFs of type float[] or float[][] now correctly coerce string arguments into floats to
behave consistently with non-optional arguments of the same type.
pandas DataFrames containing datetime.date objects no longer get converted to datetime.datetime objects when
using get_type_converter to convert from a DataFrame to a list of lists.
The pyxll install and pyxll activate commands could alter the loading order of other Excel add-ins
in some cases. These command now always preserve the loading order of previously installed add-ins.
Patch Release:
Change the timeserver used to sign the PyXLL add-in. For some users the add-in was showing as untrusted despite being signed, which was found to be because of the timeserver used when signing.
New Features:
Embedded WebView2 control added for supporting plotting libraries that output html.
Used by plotly, bokeh and altair plots by default.
Can be disabled by passing allow_html to plot, or by setting plot_allow_html = 0
in the [PYXLL] section of the pyxll.cfg file.
Requires Windows 10 and Edge or WebView2 to be installed.
ActiveX controls hosting Qt, Wx, and Tk Python widgets.
New function create_activex_control will wrap a Qt, Wx` or Tk Python widget
in an ActiveX control and add it to the Excel sheet.
Similar to create_ctp but can be used for embedding custom controls directly
in the worksheet instead of as a separate panel.
Async functions now continue after Excel’s calculation is interrupted.
Previously when calling an async function as an Excel worksheet function, if Excel was interrupted before the function returned an exception would be raised and the function would have to start again.
With this change, async results returned after Excel has been interrupted will be returned to Excel when it next calculates, so long as no inputs to the function have changed.
RTD functions can now be ‘detached’.
Usually, an RTD function will only restart if one of the inputs changes, or if the formula is re-entered. Recalculating Excel, even force-recalculating the sheet doesn’t restart an RTD function.
After an RTD function has been detached, the RTD function will be restarted the next time it is calculated, even if none of the inputs have changed. For example, when force-recalculating Excel by pressing Ctrl+Alt+F9.
RTD generator functions can be automatically detached after the generator ends by setting the
auto_detach type parameter, e.g. rtd<auto_detach=True>.
Class based RTD functions can detach using the new ‘detach’ method on the RTD class.
New context manager xl_disable for temporarily disabling automatic calculations and screen
updates inside macro functions.
Added Python 3.13 support.
Improvements:
Initialising an RTD function is now faster.
none_value option added to xl_func that works in the same way as the existing nan_value
and inf_value. Can be useful for polars DataFrames containing null values.
The dataframe type now converts from polars DataFrames when used as a return type automatically without having to explicitly speciify kind=polars.
Options added to xl_macro to disable automatic calculations and screen updating while the
macro is being called.
Add missing stubs for classes IRibbonUI and IRibbonControl to the Python wheel.
These are not used at runtime as the implementations are in the PyXLL add-in itself, but can be useful
for type checking.
Exception types can be used in type annotations for arguments that explicitly take Exception types.
The | shortcut for typing.Union is now recognized correctly when used as a type annotation.
Fixes:
Updated PySide6 support for Anaconda (the location of the qt.conf file had changed in the Anaconda distribution).
Patch Release:
Fix an issue with converting pandas DataFrames where both the index and columns are MultiIndex objects.
Fix another issue converting pandas DataFrames with non-unique column names.
Patch Release:
Fix a regression in 5.7.0 that stops animated matplotlib plots from animating correctly.
Fix an issue when setting XLCell.value with auto_resize=False to an array
larger than the target range size.
Patch Release:
Add a workaround for PySide6 bug PYSIDE-2780.
Disable allow_abort feature for Python 3.11. This is due to issues with Python’s adaptive interpreter introduced in Python 3.11 that cause Excel to freeze when using this option. This only affects Python 3.11 and these problems do not affect 3.12 or earlier versions.
Fix an issue loading ribbon files from package resources when using Python 3.7 and 3.8.
Patch Release:
Fix a regression introduced in 5.7.0 that could cause Excel to lock up when Python code
triggers a CommandBars.OnUpdate event under certain conditions.
For example, creating an Excel InputBox control from Python.
New Features:
Easily write and update Excel tables.
Python values, arrays and DataFrames can be written to Excel in Python macros using the XLCell class.
This has been extended so that it is also possible to write and update Excel tables using the same XLCell.value
interface.
See Working with Tables for more details.
dataclass types can be used for argument and return types to Excel functions.
Convert between tabular data and dataclasses using PyXLL’s dataclass type conversion.
See Dataclass Types for details.
Added support for **kwargs in worsheet functions (UDFs).
See Keyword Arguments (**kwargs) for details.
Improvements:
Performance improvements:
Converting from pandas DataFrames to Excel arrays is now faster.
Cell formatting where multiple different formats are used (e.g. DataFrame formatting) is now much faster.
xlfCaller can now be used from async and rtd generator functions.
The auto_resize option to XLCell.options now works when getting XLCell.value, as well
as when setting it.
If the cell references a table then the entire table’s contents are returned when auto-resizing.
If the cell is not part of a table but is part of a block of non-blank cells, the values for the entire block are returned.
The TypedDict annotation can be used for dict types and kwargs.
XLCell has two new methods, XLCell.offset and XLCell.resize.
Defaults for the disable_function_wizard_calc and disable_replace_calc arguments to xl_func can now be
set in the pyxll.cfg file, e.g.
[PYXLL]
disable_function_wizard_calc = 1
disable_replace_calc = 1
Added new pandas.index type for pandas Index objects in addition to the existing pandas.dataframe and pandas.series types.
Formatters can now be applied differently if the function is nested in a formula. Previously, if the function bar had a formatter
it would not be applied if called as =foo(bar()), only if the function was not nested (i.e. =bar()).
Now, formatters can specify they should be applied even if the function is nested by overriding the method
Formatter.apply_if_nested, and may also optionally override the methods Formatter.apply_nested
and Formatter.clear_nested.
Fixes:
datetime64 types are treated the same as datetimes when returned using the var type.
Fixed regression with xl_license_notifier
Setting a numpy array of size 1 no longer results in a warning from numpy about treating arrays as scalars.
Fixed a DeprecationWarning for using pkg_resource to load ribbon resources.
The xml returned by get_ribbon_xml contained a reference to an internal function not exposed to Python, causing an error if
the xml was passed to set_ribbon_xml
Escape sequences used in the ribbon xml (e.g. &) are now handled correctly.
Fixed some missing type information in the pyxll stubs Python package.
Patch Release:
Dictionary arguments using the dict type can now accept dictionary object handles as well as arrays.
Fixes a couple of issues with DataFrameFormatter:
Using Python type hints instead of the @xl_func signature string with this formatter no longer results in an error.
Formatting is now applied correctly when using async functions.
When using deep reloading and a virtual environment, the base environment’s site-packages were not always correctly excluded for some Python environments.
A minor change to how asynchronous functions check if Excel is in formula editing mode that was causing problems for certain Excel versions.
A memory leak was identified and resolved that could impact some users using DataFrameFormatter with large DataFrames.
COM errors raised internally in the PyXLL add-in (not through pywin32 or other Python modules) are logged with more meaningful error messages where possible.
New Features:
Python 3.12 build available
Python 3.12 is now fully supported, both 32 bit and 64 bit versions.
Resizing charts in Excel now redraws the Python figure.
Plots displayed in Excel using the plot function will now automatically get redrawn after the shape
in Excel is resized when the selection changes (i.e. when clicking off the shape to something else).
This can be disabled for individual plots by passing allow_resize=False to plot, or disabled
by default globally by setting the following in the config
[PYXLL]
plot_allow_resize = 0
New argument and return types tuple, set and list in addition to the existing type[] array types.
Can be used in the PyXLL signature string or as Python type hints.
The item type can be specified as a type parameter, and 2d tuples and lists can be specified using
the ndim type parmeter, eg tuple<float, ndim=2>.
Improvements:
Better support for type hints, including:
Inconsistent type information provided via xl_arg and a Python type hint now produces a warning instead of an error, and various instances where this inconsistent check was failing incorrectly have been resolved.
Annotated types can be used to provide PyXLL with type information.
Type hints using types.GenericAlias now work as expected (e.g. list[int] can be used a type hint).
The object cache now has improved support for sheets being renamed.
importlib.metadata is now used in preference to pkg_resources, where available.
Warnings filters default to the same as Python if not set in the config.
ignore_missing_values type parameter added to dict type.
Fixes:
Packages in the user site-packages folder are now excluded from being reloaded by default, in the same way as other site-packages packages.
Patch Release:
Fix an issue where functions marked to recalc_on_open would not work when using Save As… instead of Save.
Fix a cache lookup error when using the argument type dict<str, object>.
The undocumented type argument key_error for the object type can be set to False to supress cache errors and pass
the key through to the Python function if the lookup fails. This has been available since version 5, but was not working when
set to False instead of an exception class.
Added an option to disable type consistency checks. These checks were not present in earlier versions and so for code bases that already have inconsistent type annotations and signature strings this option can be used to simplify upgrading and is set as follows:
[PYXLL]
disable_inconsistent_type_check = 1
Patch Release:
Fix a build issue with the 32 bit Python 3.11 version that was preventing the add-in from loading for some users.
Improve speed when printing to sys.stdout and sys.stderr. The change in 5.6.0 to redirect these to PyXLL’s log handler
instead of the logging root logger introduced a performance regression that has been resolved.
Patch Release:
Add a work-around for a bug in PyQt6 that can cause Excel to hang when closing a Qt window in some circumstances.
Patch Release:
Fix regresion to @xl_arg_doc decorator.
Fix import error when starting up affecting some 32 bit Python versions.
Patch Release:
Update polars type conversion to work with latest polars release (0.17).
Fix problem raising a TypeError when an invalid type parameter is encountered.
New Features:
RTD functions can now be written as generators and async generators
Python generators and async generators can now be used as a simlper way to write RTD functions.
See RTD Generators for more details.
Added support for polars.DataFrame as argument and return type
polars DataFrames can be used as arguments and return values in a similar way to pandas DataFrames.
See Polars DataFrames.
New ‘optional’ type
Arguments to worksheet functions and macros can be specified as optional in the function signature or by using type annoations.
See Optional Types.
Configuration options for Python warnings
The Python warnings package can now be configured directly in the [LOG] section of the pyxll.cfg
file.
See Warnings.
Improvements:
The numpy.datetime64 value NaT is now handled the same way as NaN.
The CustomUI elements qat, contextualTabs, commands and backstage are now all supported in the PyXLL
ribbon XML files.
Custom (non-PyXLL) settings in the pyxll.cfg config file can now span multiple indented lines.
New config substition values have been added, including xll and config paths. This allows custom paths not known to PyXLL to be specified relative to either the xll location or the config file location.
The error message shown when a startup script fails can now be customized.
xl_app will now log a warning if called from a background thread. This can be customized using the warnings
configuration.
Closing PyXLL’s asyncio event loop now raises an exception. This will make it easier to track down incorrectly called
functions that try to close the asyncio event loop, preventing subsequent tasks from running.
A warning is generated if xl_arg is used for an argument that doesn’t exist.
An exception is raised if a type signature, xl_arg, xl_return, or get_type_converter is used
with invalid type parameters.
sys.stdout and sys.stderr are now redirected directly to PyXLL’s own log handler instead of going via the root logging logger.
This means replacing the root log handler no longer affects the redirection of sys.stdout and sys.stderr, avoiding an
infinite recursion error if the root logger’s handler is replaced with a logging.StreamHandler.
Usually packages should not do any logging modifiations or configuration as that should be done at the application level, but this avoids problems with certain packages that do this.
The union type type has been overhauled and now works correctly in more situations.
Specifically, when object is used as one of the possible types previously that would match all types and prevent any other
conversion from happening.
A new type parameter no_object_lookup has been added to the var type to suppress converting cached object handles to objects.
var<no_object_lookup=True> can be used where you do not want cached objects to be transparently looked up when doing
argument conversion from Excel to Python.
If rolling the log file fails for any reason don’t immediately retry. The interval between retries can be configured using
the new setting roll_backoff_interval in the LOG section of the config.
Fixes:
Fixed a problem where user site packages couldn’t be used with Python 3.11 even when no_user_site = 0
was set in the config.
XLCell.from_range now accepts Excel Range objects from the COM packages comtypes and xlwings as
well as pywin32.
Errors raised by xl_func functions called from VBA are now converted correctly, in the same way as if
called as a worksheet function.
Passing cached objects to RTD functions now works for objects where the equality operator is overriden (pandas DataFrames, for example).
The function get_ribbon_xml was missing from the pyxll stubs Python wheel and has been added.
Array resizing for versions of Excel without dynamic arrays now works correctly when using locales with the list separator set to something other than ‘,’.
Don’t block multiple threads from importing at the same time as this was causing a problem with the pymongo package.
This was introduced to work around Python issue #33895, but is no longer needed since Python 3.8. For Python versions
prior to 3.8 the fix has been amended so that it allows multiple threads to import modules at the same time.
Patch Release:
Fix an issue in when using a startup script with the pyxll-restart-excel command where
the restart message box wouldn’t be usable if a progress bar was also active.
Security Update:
Update to protect against a potential security vulnerability.
Patch Release:
Fix regression when setting dates and datetimes using XLCell.value.
New Features:
Python 3.11 build available
Python 3.11 is now fully supported, both 32 bit and 64 bit versions.
Experimental ARM support
PyXLL can now be using in Excel running on Windows for ARM. This include M1 based Macs running Windows in a VM, such as Parallels.
A setting needs to be enabled in your pyxll.cfg file to enable a work around to an issue
in the current ARM version of Excel:
[PYXLL]
enable_arm64ec_fix = 1
See Can PyXLL run on ARM PCs or M1 Macs? for details.
New options added to control how nan values are returned to Excel.
See the new NaN config settings for details.
Function argument can now be labelled differently in Excel than in Python.
The xl_arg decorator can now be used to give function arguments
a different name in Excel to the Python argument name.
Improvements:
Performance Improvements:
Returning large pandas DataFrames to Excel now takes less time than in previous versions.
The minimal fixed overhead for calling Python functions as worksheet functions has been very slightly reduced even further.
Custom Task Pane Improvements:
create_ctp now returns a CustomTaskPane object to allow various properties of the custom task
pane to be later queried or modified.
A new kwarg position_restrict has been added to create_ctp. This can be used to specify where the
custom task pane’s position should be restricted to. For example, to prevent it from being un-docked.
Config resources loaded from a URL can now use NTLM authentication for intranet sites.
Certain config options, like startup_script, external_config and license_file can use URLs as well as file paths. Now these can be loaded from an intranet server requiring authentication using NTLM (or auto-logon).
When using multiple ribbon XML files it is now possible for each file to have it’s own onLoad callback.
The XML files get merged into one single XML document before being loaded by Excel, but now PyXLL will keep track of the onLoad callbacks and call each of them when the ribbon is loaded.
Async functions can be used as ribbon functions.
New optional_external_config option added. This works in the same way as the existing external_config
option but if the file doesn’t exist a warning is logged instead of an error.
The default error handler for UDFs produces more succinct error messages for nested errors (for example, when
an ObjectCacheKeyError exception causes subsequent ObjectCacheKeyError exceptions).
Long error strings are truncated to 255 characters (the full error will still be available in the log file).
Removed dependency on cURL and Internet Explorer.
PyXLL has the capability to make web requests, for example when reading a file referenced in the config using a URL instead of a path. It also uses this to check a license server when starting up, which is how it can auto-update the license information on renewal.
Previously this was done using the open source library cURL, falling back to a headless, invisible, Internet Explorer control if cURL failed (often due to proxy settings).
This has now been entirely replaced to use a different Windows API that allows for more control over various proxy settings (see Web Control Settings), as well as solving some problems with the headless Internet Explorer control.
Fixes:
Using *args now works correctly for async functions.
Errors converting arguments for async functions are now returned to Excel immediately.
var[] now works correctly as a base type when defining a custom type using xl_arg_type
(as well as other 1d array types).
The log format option now defaults to something sensible if omitted from the config.
The RTD constructor no longer throws an exception if called outside of Excel to
allow for RTD classes to be used in tests.
Setting index=0 and index_dtype together when using the pandas DataFrame argument type
now correctly doesn’t include the first column as the DataFrame index.
Fix an issue with PyXLL not loading correctly if the environment variable PYXLL_DEBUG_LOG is set to an invalid path.
XLCell.from_range now works correctly when specifying a range from a workbook other than
the current active workbook.
Patch Release:
Fix an issue importing nested Python packages bundled into the xll file.
Accessing the Context property on IRibbonControl objects passed to ribbon controls would cause Excel to
crash if no workbook was open. This has been fixed and now the Context property will be None when there
is no open workbook.
Patch Release:
Fix a problem that could crash Excel if an error handler returned a string that was longer than 65535 characters.
Patch Release:
Fix an issue that could cause a crash when passing #REF! as an argument to a worksheet function.
Patch Release:
Fix an issue with the latest version of PyQt6 and custom task panes not working.
Don’t reload the PyXLL add-in when Excel is started using “/R pyxll.xll” and PyXLL is already loaded via the add-in manager.
Handle pandas.NA in the same way as np.nan when returning pandas.DataFrames objects to Excel.
Convert NaN values returned as part of a pandas object to #NUM! instead of #N/A! to be consistent
with how NaN is handled when returned as a number.
Patch Release:
Fix a problem for some Anaconda users where the system path was not being correctly set, resulting in DLL module load failures.
New Features:
Added support for displaying animated matplotlib plots in Excel.
New argument type function for passing PyXLL worksheet functions as arguments to other PyXLL functions.
plot has a new reset kwarg that can be used to reset the shape in Excel to the size
and position specified. If False (default) the behavior is unchanged and the size and position
arguments are only set the initial size and position.
Improvements:
plot when used with bokeh now uses SVG images where possible, by default.
plot has a new kwarg alt_text that can be used to set the alternative text (description)
on the picture object in Excel.
XLCell methods now raise an exception if called from outside of Excel (i.e. when using the PyXLL
stub package for unit testing etc where the XLCell functionality is not available).
Remove static dependency on gdiplus.dll. This is usually installed as part of Windows, but if it is missing
it would prevent PyXLL from loading. This is required for loading png and other image formats in the ribbon,
but now if it is missing PyXLL will still load and a warning will be logged.
Fixes:
Fix a problem loading the Python DLL when using the Anaconda version of Python 3.10.
Work around a rare edge case linked to ASLR (Address Space Layout Randomization) during PyXLL initialization that would cause Excel to crash when starting.
xl_app would sometimes fail if called from an Excel COM event handler triggered by PyXLL’s
‘recalc on open’ feature.
plot would cause Excel to show an out of memory error if called while Excel was processing user
interface events (e.g. when clicking a checkbox). Updating the picture in Excel now happens asynchronously.
The kwargs disable_function_wizard_calc and disable_replace_calc to xl_func did not work
correctly for all display languages. These now work independently of the chosen display language.
When installing using the pyxll install command the config file would be written out with UNIX style LF
line endings which some old versions of Notepad don’t recognise correctly. This has been updated to
use Windows CRLF line endings.
Using XLCell.value to set a cell value to NaN or Inf now correctly sets the cell value
as the #NUM! error in Excel.
Passing an error (eg #N/A! or #DIV/0!) to an RTD function would cause the RTD function to be
re-started repeatedly.
Patch Release:
Fix a regression in 5.3.0 that caused some functions not to be exported to Excel if imported indirectly from another module appearing in the pyxll.cfg file ahead of the module that actually included those functions.
Notes:
The code signing certificate used to build PyXLL has changed between 5.3.0 and 5.3.1 (the previous one has expired).
New Features:
Python 3.10 build available
Python 3.10 is now supported, both 32 bit and 64 bit versions.
Improvements:
New option to not return sparse results for MultiIndexes of DataFrames and Series.
The dataframe type now has a new parameter multi_sparse to control how MultiIndex indexes and
columns are returned to Excel. It can be set to True (default), False, 'index' or 'columns'.
If the index (or columns) of a returned DataFrame are a MultiIndex the default behaviour is to display sparse results that don’t repeat each label repeatedly as this is generally looks better. This can be disabled using this new option if it is not wanted.
Pass cells or values to worksheet functions.
Argument types like union<xl_cell, var> and union<range, var> can now be used for arguments
that can accept a cell reference or a value. This can be used when an argument may be a cell
reference or a value entered directly in the formula.
New --non-interactive option for the PyXLL Command Line Tool.
A new switch --non-interactive or -ni can be passed to any of the pyxll commands using the
PyXLL Command Line Tool and will prevent the tool from prompting the user for input.
This is intended to be used with pyxll activate to make it easier to script activating
pre-prepared PyXLL environments for automated deployment.
Using this switch with another command that requires user input will result in an error.
Fixes:
Callbacks specified in the config file, such as error_handler, now work correctly when used with the
separate PyXLL-Bake tool (not part of the generally available PyXLL product).
Patch Release:
Fix a problem with finding the Excel COM Application when using certain Excel versions that was introduced in 5.2.0.
Fix an issue with array resizing that can occur when Excel returns a #SPILL error.
Patch Release:
Fix an issue affecting a few users where Excel would crash when reloading PyXLL.
Patch Release:
Fix a new issue introduced in 5.2.0 when logging to network drives.
Patch Release:
Fix edge case where lists of dates weren’t being automatically converted correctly when the function return
type was specified as var instead of var[] or date[].
Patch Release:
Fix regression that was preventing an external config file configured via the environment variable
PYXLL_EXTERNAL_CONFIG_FILE from being read.
New Features:
Log Rolling:
PyXLL can automatically roll its log file to avoid long running Excel processes generating huge log files.
This is controlled by some new options in the [LOG] section of the pyxll.cfg file which can
be found in the Logging section of the config documentation.
Disable calculations during schedule_call callback:
schedule_call is most often used in order to schedule a function that will call Excel via
the Excel Object Model (or Excel API). When making updates to Excel it is often better to temporarily
disable automatic calculation and screen updating and restore those at the end of the function.
The schedule_call function has a couple of new options (disable_calculation and
disable_screen_updating) that can be used as a more convenient way of doing that for the
duration of the scheduled callback.
Added a new option category to the PYXLL section of the config file. If set, this will be used
as the default category for xl_func instead of ‘PyXLL’.
Improvements:
Tk integration improvements. The way the Tk message loop was polled to update the UI previously could result in jerky scrolling or unresponsive controls.
This has been improved to ensure messages are not missed and the Tk message loop is now automatically polled more frequently when the window is active to improve the responsiveness.
The initial position of floating Custom Task Panes can now be specified when calling
create_ctp.
Removed a dependency on win32com.shell when the option win32com_delete_gen_path is used.
Improved performance of resizing and formatting multiple arrays. The old style (CSE) Array resizing and formatting is now batched to avoid disabling and re-enabling Excel’s auto-calc mode repeatedly. This does not affect dynamic arrays.
xl_app would occasionally cause Excel to crash if called outside of a macro or menu function
due to a bug in the internal Excel C API function xlGetHwnd used. A work-around has been created for this
to avoid using that API function in situations where it may fail.
Fixed an issue where specifying a numpy_array with dtype=object would cause unexpected results.
The ribbon xml files are now consistently loaded as utf-8 encoded files, regardless of Python version or Windows locale settings.
Patch Release:
Command line installer improvements:
Unblock the pyxll.xll file if it has been blocked by Edge or Internet Explorer.
Try to install the PyXLL certificate as part of the install rather than relying on Excel to do it.
Fix some text to make some errors clearer.
Catch an error if path auto-completion doesn’t work (e.g. when using Cygwin).
Allow quoted paths to be used during installation.
The idQ attribute can now be used in ribbon xml elements for fully qualified ids. To use these
you must also specify the namespace using a xmlns attribute on the customUI element.
New Features:
Added support for PySide6 and PyQt6 custom task panes.
Added union types for argument and return types.
Add support for numpy datetime64 types.
Improvements:
Help topic passed to xl_func can now be a URL as well as a local .chm file.
The pyxll package now includes type hints for versions of Python that support them, and uses
the exceltypes type library if installed.
plot now closes the active figure after plotting with matplotlib.pyplot. This
prevents settings from previous plot leaking to the next plot.
Interval between calls to CTPBridgeBase.on_timer can now be set using a new on_timer kwarg
to create_ctp or via a new ctp_timer_interval config setting.
Handle the case where the log file is on a network drive that becomes unavailable. Logging will stop, but if the log file later becomes available again it will resume.
Various improvements to the PyXLL command line installer.
Further reduce the likelihood of win32com wrapper modules becoming corrupted from multiple Excel sessions running at the same time.
Fixes:
DataFrameFormatter now works correctly on DataFrames with a MultiIndex index.
Fixed a problem registering *args functions with both volatile and thread_safe set.
XLCell.from_range would fail if called with a cell from a sheet with non-ASCII characters in the name.
Fixed a problem reported with cell formatting in some non-English language versions of Excel.
Added a work-around for an edge case where docstrings including a hyphen on the first line get rejected by Excel as function help text.
Patch Release:
Fix Python object leak when using “rtd<object>” return type. This does not affect the normal “rtd” return type when used to return objects, only the explict “rtd<object>” form.
Patch Release:
Fix another unicode problem with the “pyxll install” command.
Show a better error message if trying to run “pyxll install” on a non-Windows computer.
Patch Release:
Fix a problem with the “pyxll install” command when the user’s temp folder includes unicode characters.
Fix an issue with some docstrings not being parsed correctly when creating the function help text.
Patch Release:
Fix a reference leak when using the ‘auto_resize’ option to xl_func.
Improve prompts in the “pyxll update” command to make the instructions clearer.
Add a __main__.py file to the PyXLL wheel so the CLI tool can be run using “python -m pyxll”.
Patch Release:
Ribbon images were sometimes not loaded when the ribbon path contained both ‘/’ and ‘' path separators.
Fixed a problem merging ribbon XML documents containing menu controls.
Show a more helpful error message if the log file can’t be written to when starting.
Patch Release:
Add a work-around for a bug in current Anaconda distribution of PyQt5 where some enums are missing.
Patch Release:
Fix a regression in 5.0.2 for docstrings that only include parameter documentation with no function documentation.
Some module dependencies were not being tracked correctly when imported outside of when PyXLL loads. For example, when importing from a Jupyter notebook.
Patch Release:
There is a limit of 255 chars for the parameter names string passed to Excel used in the function wizard and PyXLL automatically truncates parameter names in order to fit in this limit. When this truncating occurs any parameter documentation in the function docstring for the truncated parameter was lost, and this patch release fixes that.
Patch Release:
Fix a problem where pyxll activate was not working with older PyXLL releases.
Fix a regression since 4.6.1 with cell formatting where the current active sheet and selection could be changed when applying the formatting.
New Features:
Custom Task Panes
Task Panes are Excel windows that can be floating or docked as part of the Excel user interface.
PyXLL 5 adds the capability to write custom task panes in Python using any of the following Python UI toolkits:
PySide2
PyQt5
wxWindows
tkinter
See Custom Task Panes.
Plotting Integrations
PyXLL now has integration with the following Python plotting and charting packages:
matplotlib
plotly
bokeh
altair
See Charts and Plotting.
Serialized Cached Objects
Cached objects can be serialized and saved as part of the Excel workbook. When a workbook containing saved objects is opened they are deserialized and loaded into PyXLL’s object cache.
To specify that an object should be saved use the save parameter to the object return type.
Command line tool
New command line tool for installing and uninstalling the PyXLL add-in.
After installing the PyXLL wheel the pyxll command line tool can be used to install,
configure, check and uninstall the PyXLL Excel add-in.
> pip install "pyxll >= 5.0.0"
> pyxll install
Use pyxll --help for a complete list of options.
Entry Points
Python packages can now be loaded by PyXLL via setuptools’ entry-points.
This allows package developers to distribute functionality to other PyXLL users more easily as no additional PyXLL configuration is required when installing a package with PyXLL entry points.
Conditional DataFrame Formatting
DataFrameFormatter can now apply conditional formatting.
Improvements:
RTD Function Formatting
Cell formatting can now also be applied to the results of RTD functions.
Composite Ribbon Toolbars
The ribbon toolbar can now be composed of multiple xml files instead of a single file.
The ribbon setting can now be a list of files, which PyXLL will merge into a single ribbon.
This can be used by package authors who want to add a ribbon to their package via an entry point without needing changes to be made to the main PyXLL configuration or ribbon xml file.
Ribbon images can be loaded from resources
Ribbon images in the ribbon.xml file can be resources using pkg_resources as well as filenames.
Use the format module:resource to use a package resource as an image in the ribbon.xml file.
Auto-Rebinding
When using the xl_func, xl_macro or xl_menu decorators outside
of the usual module imports as PyXLL is starting, PyXLL will automatically reflect these functions
in Excel without needing to call rebind.
This simplifies working with adhoc worksheet functions from an interactive Python prompt in Excel, such as a Jupyter notebook.
Functions in non-reloaded modules remain after reloading
When reloading, if a module is not reloaded (for example because it is excluded by the deep reloading rules) any PyXLL functions registered in that module are retained. Previously only functions in the reloaded modules would be available after reloading.
atexit handlers are called when Excel closes
Any exit handlers registered with atexit.register are now called when Excel is closed.
Environment variables for PyXLL license key
The following environment variables may be used instead of setting the license key in the pyxll.cfg config file:
PYXLL_LICENSE_KEY
PYXLL_LICENSE_FILE
Changes:
New software license agreement
The PyXLL software license agreement has been updated.
See PyXLL Terms and Conditions or the software license agreement PDF file included in the PyXLL download for details.
Deep reloading by default
Deep reloading is now enabled by default. See Reloading and Rebinding for details about how PyXLL reloads modules.
To disable deep reloading set the following in your PyXLL config file.
[PYXLL]
deep_reload = 0
Default recalc_on_open to False for RTD functions
All function types now behave consistently and will only recalculate when first opening a workbook
if the recalc_on_open option to xl_func is set to True or otherwise enabled.
See Recalculating On Open for details.
async_call replaced with schedule_call
async_call has been replaced with a new more fully featured schedule_call
function. This new function allows the callback to be called after a delay and has some options
for retrying in case of failure.
Fixes:
Some instability when opening the ribbon toolbar when first starting Excel has been fixed.
Functions scheduled with schedule_call or async_call are no longer called while
the user is inputting a function. This is because doing so can cause Excel to lose the cell currently
being edited.
