See What’s new in PyXLL 4 for a more detailed overview of the new features in PyXLL 4.
Patch Release:
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:
deep_reload_include_site_packages
is set to 0
.recalc_on_open
when reloading PyXLL.Patch Release:
xl_cell
or range
types to worksheet functions.isatty()
) to sys.stdout
and sys.stderr
.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.
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:
Patch Release:
Patch Release:
xl_return
wasn’t working correctly with some async functions.Patch Release:
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:
XLCell.from_range
can now take an address string as well as a COM Range object.pythonhome
folder relative to the dll
setting if that is set and not executable
.Fixes:
PYTHONHOME
environment variable if ignore_environment
is set.Patch Release:
XLCell.value
with a DataFrame containing np.nan values.Patch Release:
Patch Release:
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).
Patch Release:
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:
Patch Release:
Patch Release:
Patch Release:
New Features:
Added support for Python asyncio co-routines (requires Python 3.5.1 or newer)
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:
Patch Release:
Patch Release:
Patch Release:
numpy_array<var>
) from being incorrectly type converted.Patch Release:
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:
Fixes:
Patch Release:
xl_app
now works correctly when called in a background thread with com_package=”comtypes”.Patch Release:
Patch Release:
Patch Release:
reload
and rebind
.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'>
).
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.