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:
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().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 longfloat[] or float[][] now correctly coerce string arguments into floats to
behave consistently with non-optional arguments of the same type.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.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:
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:
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.xl_macro to disable automatic calculations and screen updating while the
macro is being called.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.Fixes:
Patch Release:
Patch Release:
XLCell.value with auto_resize=False to an array
larger than the target range size.Patch Release:
Patch Release:
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:
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.xl_license_notifierDeprecationWarning for using pkg_resource to load ribbon resources.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_xmlpyxll stubs Python package.Patch Release:
dict type can now accept dictionary object handles as well as arrays.DataFrameFormatter:@xl_func signature string with this formatter no longer results in an error.DataFrameFormatter with large DataFrames.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:
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.types.GenericAlias now work as expected (e.g. list[int] can be used a type hint).importlib.metadata is now used in preference to pkg_resources, where available.ignore_missing_values type parameter added to dict type.Fixes:
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:
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:
Patch Release:
@xl_arg_doc decorator.Patch Release:
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:
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.xl_func functions called from VBA are now converted correctly, in the same way as if
called as a worksheet function.get_ribbon_xml was missing from the pyxll stubs Python wheel and has been added.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:
pyxll-restart-excel command where
the restart message box wouldn’t be usable if a progress bar was also active.Security Update:
Patch Release:
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:
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.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:
*args now works correctly for async functions.var[] now works correctly as a base type when defining a custom type using xl_arg_type
(as well as other 1d array types).RTD constructor no longer throws an exception if called outside of Excel to
allow for RTD classes to be used in tests.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.XLCell.from_range now works correctly when specifying a range from a workbook other than
the current active workbook.Patch Release:
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:
Patch Release:
Patch Release:
pandas.NA in the same way as np.nan when returning pandas.DataFrames objects to Excel.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:
New Features:
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).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:
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.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.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.XLCell.value to set a cell value to NaN or Inf now correctly sets the cell value
as the #NUM! error in Excel.#N/A! or #DIV/0!) to an RTD function would cause the RTD function to be
re-started repeatedly.Patch Release:
Notes:
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:
error_handler, now work correctly when used with the
separate PyXLL-Bake tool (not part of the generally available PyXLL product).Patch Release:
#SPILL error.Patch Release:
Patch Release:
Patch Release:
var instead of var[] or date[].Patch Release:
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:
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:
datetime64 types.Improvements:
xl_func can now be a URL as well as a local .chm file.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.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.Fixes:
XLCell.from_range would fail if called with a cell from a sheet with non-ASCII characters in the name.Patch Release:
Patch Release:
Patch Release:
Patch Release:
xl_func.Patch Release:
Patch Release:
Patch Release:
Patch Release:
Patch Release:
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:
See Custom Task Panes.
Plotting Integrations
PyXLL now has integration with the following Python plotting and charting packages:
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:
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:
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.