See What’s new in PyXLL 4 for a more detailed overview of the new features in PyXLL 4.
Patch Release:
Fix a problem when returning tuples or namedtuples from worksheet functions using the ‘object’ return type.
New Features:
Python 3.9 build available
Python 3.9 is now supported, both 32 bit and 64 bit versions.
Improvements:
RTD objects can be re-used
RTD objects can be re-used between Excel functions, allowing a single RTD object to be returned from multiple calls to the same, or other, functions.
This simplifies cases where an RTD object is a singleton, or global instance, that may be returned multiple times in the same workbook.
skip_primitives option added to object type
Excel worksheet functions returning objects using the object type can now optionally
skip primitive values from being returned as cached objects, and instead return the
value directly.
@xl_func("var x: object<skip_primitives=True>")
def func(x):
# If the result is a primitive type (int, float, bool, etc) then
# it will be returned as that value. Otherwise it will be returned
# to Excel as an object handle.
return result
PyXLL wheel files now on PyPI
The stubs PyXLL packages can be installed from PyPI and are available for non-Windows platforms. This is to make it easier to run unit tests of PyXLL code using continuous integration.
Fixes:
Fix problem where modules in site-packages would still be reloaded even when deep_reload_include_site_packages
is set to 0.
Don’t call functions registered with recalc_on_open when reloading PyXLL.
Fix potential issue when using recalc_on_open and cell formatting.
Patch Release:
Fixed an edge case where in some circumstances the the sheet id was incorrect when passing xl_cell or range types to worksheet functions.
Add missing methods (e.g. isatty()) to sys.stdout and sys.stderr.
Allow worksheet functions with the same Python function names, as long as the Excel function names are different.
New Features:
New formatting options
Apply formatting to cells from worksheet functions and macros.
See Cell Formatting for details.
Options to recalculate cells when opening a workbook
Functions can be marked as needing to be recalculated when the workbook is opened by passing
recalc_on_open=True to xl_func.
RTD functions now use this so they are called automatically when an Excel workbook is opened and start ticking immediately. This replaces the previous behaviour where RTD functions were volatile by default.
If opening a workbook containing RTD functions saved with a previous version of PyXLL, you will need to recalculate the workbook and re-save it in order for those RTD functions to calculation on opening.
Functions returning objects can default to being recalculated when opening via the config
option recalc_cached_objects_on_open. This can be used to populate the object cache whenever a
workbook is opened without having to calculate the whole workbook.
See Recalculating On Open for details.
Improvements:
RTD functions are no longer volatile by default
Previously RTD functions were marked as being volatile so that Excel would recalculate them when opening a workbook in order for them to start ticking immediately.
This is no longer necessary because of the new recalc on open feature, but may be
re-enabled by setting rtd_volatile_default=1 in the PYXLL section of the pyxll.cfg file.
Simplified config for Python venv environments
PyXLL now reads any pyvenv.cfg file automatically so configuring PyXLL to use a venv
now only requires setting executable in the pyxll.cfg file.
Added more sys.flags options to the config
The following sys.flags can be set from pyxll.cfg (listed below with their default values). Previously these could only be set using environment variables, which would have no effect if ‘ignore_environment’ was also set.
ignore_environment = 1
inspect = 1
optimize = 0
debug = 0
verbose = 0
dont_write_bytecode = 0
no_user_site = 0
no_site = 0
The ‘inspect’ flag is set by default as it prevents unhandled SystemExit exceptions from terminating the Excel process.
Warnings added for common config errors
Warnings are now written to the log when certain common config errors are identified.
Patch Release:
Fix a regression introduced in 4.4.0. The license and external config files need to be opened for shared access to avoid issues with multiple users trying to access them at the same time.
Patch Release:
Fix a problem where Pandas datetimes were losing sub-second accuracy when being expanded to an Excel range.
Patch Release:
Fixed a problem that meant xl_return wasn’t working correctly with some async functions.
Patch Release:
Work around a change in the latest monthly release of Excel that stopped RTD functions from updating.
Update XLCell.value so that when setting cached objects the object is added to the cache before
any dependent formulas are calculated.
New Features:
New setting startup_script added.
If set, the script is run before Python is initialized and can be used to create or update the Python environment.
See docs/4.x/userguide/config/startup and Using a startup script to install and update Python code.
Improved win32com integration
New settings to control where win32com’s wrapper classes get written to, and optionally recreate them when starting Excel.
See win32com Settings.
Pass Excel Range objects to functions directly
A new range argument type has been added.
The range argument type can be used instead of xl_cell and will pass the cell reference
as a Excel COM Range object instead of an XLCell.
Also added method a new XLCell.to_range method getting a Range object from an XLCell
instance.
Improvements:
The object cache has been updated to handle inserting and deleting rows and columns correctly.
Continue to use auto resize feature for CSE array formulas even if Excel has Dynamic Arrays.
XLCell.from_range can now take an address string as well as a COM Range object.
Look for the pythonhome folder relative to the dll setting if that is set and not executable.
Fixes:
Don’t use the PYTHONHOME environment variable if ignore_environment is set.
Patch Release:
Fix a problem setting cell values using XLCell.value with a DataFrame containing np.nan values.
Importing anvil.server with deep reloading enabled was causing a problem which is now fixed.
Patch Release:
Ensure RTD server is shut down cleanly when Excel closes.
Fix an issue where Excel wasn’t recognizing the PyXLL COM add-in as being signed.
If a non-RTD object is returned from an RTD function, return it to Excel as if ‘var’ was specified as the return type.
Returning an object from a macro function now string converts the object, rather than failing because cached objects can’t be returned from macros.
Fix a problem with using forward slashes in the Python executable path in Windows 7.
Log a warning if ‘auto_resize’ is set on an async function.
Replace invalid chars from name in pyxll.cfg when constructing names for the PyXLL internal macros.
Work around an issue with Symantec Endpoint Protection that kills the Excel process under certain conditions.
Patch Release:
Bug fix for some users that were experiencing issues when running Excel behind a firewall or proxy server.
Patch Release:
Performance improvement of the deep reload import tracker.
The change in 4.3.0 to track dependencies of imports made inside functions, not just module level imports, caused a performance regression for packages that make extensive use of importing modules inside functions (e.g. pandas).
New Features:
Python 3.8 support has been added.
Auto-reloading of Python modules.
PyXLL can now detect when imported Python modules have been updated and reload automatically.
This must be enabled in the config file as follows:
[PYXLL]
auto_reload = 1
See docs/4.x/userguide/reloading for details.
XLCell can now be used to set values as well as get them. This allows for cached objects to
be written to worksheets from macros as well as from worksheet functions.
Better error handling capabilities
PyXLL 4.0 introduced custom error handling for worksheet functions. This has been extended to handle errors from all Excel function and callbacks to provide a consistent way of handling errors from macro, menu and ribbon functions.
See docs/4.x/userguide/errors for details.
Renewal license keys are now fetched automatically.
If you have a license key issued from the PyXLL store (not a legacy Enthought license), when the license key has expired PyXLL will now attempt to download the renewal license key automatically.
This means that when you renew your license, you will not have to manually update the license key in the config file, as long as you have an internet connection and the PyXLL license server is not blocked by a firewall.
Improvements:
Previously, deep reloading only considered modules that were imported while PyXLL was opening.
Now, even if modules are imported dynamically later (e.g. when calling and Excel function), they will now be tracked and reloaded correctly when reloading PyXLL.
Modules and packages from site-packages are now excluded from being reloaded when the deep reload feature is enabled. This can be disabled by setting the following in the pyxll.cfg:
[PYXLL]
deep_reload_include_site_packages = 1
Improved error messages when starting PyXLL if Python can’t be found.
The “About…” item in the Add-Ins tab is now still shown even if PyXLL couldn’t be fully opened (if Python couldn’t be loaded, for example).
Added a link to the log file from the “About” dialog.
Provide more information on the “Invalid License Key” dialog.
Fixes:
Python 3.7.4 introduced a new “base_executable” setting that’s used to determine what executable should be used by the the multiprocessing module.
This is now set automatically to be the same as the configured Python executable for Python 3.7 and 3.8.
Alternatively, it can be set in the config file by setting base_executable in the [PYTHON] section.
Registering Excel functions or macros with the same names as the built-in developer tools macros (e.g. “pyxll_reload”) no longer interferes with the behaviour of the developer tool menus and ribbon buttons.
Fixed an problem in the async example that prevented it from working with Python 3.5.
Patch Release:
Update async examples with recent changes to IEX API.
Fix an issue calling ribbon functions that recalculate or load workbooks that use the object cache.
Add-ins created using the separate PyXLL-Bake tool weren’t recognizing baked functions correctly.
Patch Release:
Fix an issue introduced in 4.2.2 where volatile cells could be continuously recalculated when also using cached objects.
Patch Release:
Fixes an intermittent crash bug found in some versions of Excel that occurred when opening Excel with certain workbooks that used the object cache.
Patch Release:
Fix a problem when registering functions with very long signatures.
New Features:
Added support for Python asyncio co-routines (requires Python 3.5.1 or newer)
Asynchronous UDFs can be written as async functions without needing an async_handle argument.
RTD methods connect and disconnect may be async.
A new pyxll.get_event_loop method gets the event loop used to run async UDFs and RTD methods. By default, this runs on a single background thread, but a custom implementation maybe specified (configured in the pyxll.cfg config file).
See Asynchronous Functions and RTD Class Async Example for more details.
Added transpose kwarg to xl_func and xl_macro.
When set, transpose any array result returned.
This can be useful for returning 1d arrays as rows instead of columns.
Improvements:
Array resizing is suppressed for functions called as a nested function. For example, if a function ‘inner’ is set to auto-resize, if called as =SUM(inner(A1)) it will not automatically resize the range it was called from.
If the outer function is set to automatically resize, it will resize as before regardless of any nested functions.
The XLAsyncHandle class used for async functions has two new methods,
XLAsyncHandle.set_value and XLAsyncHandle.set_error.
These can be used instead of xlAsyncReturn and make it possible to
return errors consistent with non-async UDFs from async functions.
The text encoding of the log file can now be set in the config, and defaults to ‘utf-8’.
Automatically add ‘Scripts’ to PATH if using Canopy and not already set.
Bug Fixes:
Fix a crash bug to do with auto-resizing that would occur under some situations when first loading a sheet or when reloading PyXLL.
The pyxll.cfg config file would not be found if it was read-only. Now read-only config files are read correctly.
When returning arrays as var, if elements in the array were themselves lists or tuples the array would fail to be returned to Excel correctly. Now those inner lists/tuples are returned as cached objects within the outer array.
Patch Release:
When using type annotations instead of a signature, string and collection types are now recognized correctly.
The internal pre-compiled (.pyc) ‘pyxll’ module wasn’t being loaded when using Python 3.7, instead falling back to the .py source version which is slower to load.
The Anaconda “Library\bin” path is now always prepended to the PATH if it’s found. This is a modification to the change introduced in 4.0.2, which only prepended it if it was missing. This change ensures that it is now always the first item on the PATH, and may be disabled by setting “fix_anaconda_path=0” in the PYXLL section of the config.
Return errors consistent with non-async UDFs from async functions.
Patch Release:
Fix ordering issue with un-sorted DataFrame indexes.
Fix index and column naming when using both an index and columns for a DataFrame argument. Previously both the index and column would be given the same name, now the name is assumed to belong only to the index.
Patch Release:
DataFrame dtype conversion wasn’t working correctly for types ‘float’ and ‘float_nan’.
Fixed NumPy arrays with mixed types (using numpy_array<var>) from being incorrectly type converted.
Added ‘index_dtypes’ parameter to DataFrame and Series type conversion.
Warn if ribbon.xml file has multiple elements with the same id.
Improve error reporting when a Python module needed by the ribbon can’t be imported.
Patch Release:
Disabling of auto-resize when dynamic array support is detected introduced in 4.1.0 was incorrectly identifying dynamic array support in the latest Office 365 non-insider track.
Fixed minor memory leak when using the object cache.
Name of internal COM add-in wasn’t using the ‘name’ config option when set.
New Features:
Substitution values in the config can now have defaults, .e.g verbosity = %(LOG_LEVEL:info)s.
The auto_resize feature is now disabled automatically if the version of Excel being used supports dynamic arrays.
This means no user code or config changes are required to be able to support add-ins being used in newer versions of Excel that are still also being used in older versions.
The automatic disabling of the auto_resize behaviour can be turned off by the folling config setting
[PYXLL]
allow_auto_resizing_with_dynamic_arrays = 1
See docs/4.x/userguide/udfs/arrayfuncs for more details about array functions.
Improvements:
1d lists returned when using the var type are now implicitly returned as a column of data.
Skip attempting to cast numpy arrays when using dtype var.
If no config file can be found when loading the PyXLL add-in an error message will be shown.
Internal changes to support loading ribbon and images from an add-in created with the pyxll-bake tool.
Fixes:
Fix potential conflict when both PyXLL and Jinx (the Excel Java Add-On, https://exceljava.com) add-ins are installed together.
RTD functions can now safely be called multiple times in a single formula in the same cell.
Fixed an error that would occur when trying to auto-resize an array function on a sheet with an “’” in the sheet name.
Pandas Series and Index objects are returned as cached objects when using the var return type. Previously, depending on the content, they could be expanded into 2d arrays.
Patch Release:
Fix issue on Windows 7 when returning numpy arrays.
Get the correct SID when registering COM add-in and RTD server when multiple accounts with the same name exist.
xl_app now works correctly when called in a background thread with com_package=”comtypes”.
Patch Release:
Remove internal code for detecting Anaconda PATH issues from public pyxll.py stubs module.
Fix issue that meant that PyXLL didn’t always detect when the COM add-in had been disabled when starting.
Patch Release:
Don’t convert empty DataFrames to list of lists (appears in Excel as 0) when passed as var.
Detect and fix PATH issues when using Anaconda 3.7 (disable by setting “fix_anaconda_path=0” in PYXLL section of the config).
Add work-around for issue where Excel doesn’t resolve HKCU/Software/Classes alias correctly.
Reduce debug noise when importing the pyxll module.
Improve logging when there’s a problem registering the COM addin or RTD server.
Patch Release:
Fixed issue with Python functions reload and rebind.
Downgraded a couple of errors in the log file to debug warnings.
New Features:
Pandas support (replaces pyxll_utils.pandastype).
Internal object cache (replaces pyxll_utils.objectcache).
Supports passing Python objects between Excel functions, including returning Python objects from RTD functions.
1d array types added using the type suffix []. For 2d arrays (previously the only array type) use [][].
This can be disabled if you are upgrading from an earlier version by setting
always_use_2d_arrays=1 in the PYXLL section of your config file.
Functions with a variable number of arguments (*args) are now supported.
Customizable error handling. Worksheet functions can now return human readable errors instead of #N/A, #NUM etc.
New dict type for passing dicts between Excel and Python.
Parameterized custom types.
Custom type handlers can now take keyword arguments, passed from the type arguments in the function signature,
eg. my_custom_type<ndim=2> would call the type conversion function for my_custom_type
with the additional kywarg ndim=2.
Decorators xl_arg and xl_return added as an alternative to providing type information
via the function signature.
Now types can be parameterized the function signature can be more complex. The decorators can use used in addition to a signature, or without a signature, to provide type information.
Python 3.7 support added.
Improvements:
NumPy floating point array arguments and return types are now significantly faster.
Tests show as much as a 15x speed up for passing floating point numpy arrays (e.g. numpy_array<float>)
between Python and Excel.
Reduced overhead of disabling calculations in the function wizard.
In previous versions checking whether a function was being called from the function wizard or not was an expensive operation and was performed every time a function with this option was called.
This has been greatly improved for sheets with multiple functions by reducing the number of times this check is called when recalculating a sheet or workbook.
Added ‘casting’ option to numpy array types (e.g. numpy_array<float, casting='nan'>).
‘unsafe’ (default) Always convert to chosen dtype. Will fail if any input can’t be converted.
‘nan’ If an input can’t be converted, replace it with NaN.
‘no’ Never do any type conversion.
Added ‘ndim’ option to numpy array types (e.g. numpy_array<float, ndim=1>).
Defaults to ndim=2. Use for passing 1d arrays between Excel and Python.
RTD functions can now be used as array formulas.
rtd and async_handle types can now specify the return type, e.g. rtd<float>.
New method RTD.set_error to return errors to Excel from an RTD function.
