These decorators, functions and classes are used to expose Python functions to Excel as worksheet functions (UDFs).
Please see Worksheet Functions for complete details on writing Excel worksheet functions in Python.
See also Type Conversion and Real Time Data.
xl_func is decorator used to expose python functions to Excel. Functions exposed in this way can be called from formulas in an Excel worksheet and appear in the Excel function wizard.
signature (string) –
string specifying the argument types and, optionally, their names and the return type. If the return type isn’t specified the var type is assumed. eg:
"int x, string y: double" for a function that takes two arguments, x and y and returns a double.
"float x" or "float x: var" for a function that takes a float x and returns a variant type.
If no signature is provided the argument and return types will be inferred from any type annotations,
and if there are no type annotations then the types will be assumed to be var.
See Standard Types for the built-in types that can be used in the signature.
category (string) – String that sets the category in the Excel function wizard the exposed function will appear under.
help_topic (string) – Path of the help file (.chm) or URL that will be available from the function wizard in Excel.
thread_safe (boolean) – Indicates whether the function is thread-safe or not. If True the function may be called from multiple threads in Excel 2007 or later
macro (boolean) – If True the function will be registered as a macro sheet equivalent function.
Macro sheet equivalent functions are less restricted in what they can do, and in
particular they can call Excel macro sheet functions such as xlfCaller.
allow_abort (boolean) –
If True the function may be cancelled by the user pressing Esc. A KeyboardInterrupt exception is raised when Esc is pressed. If not specified the behavior is determined by the allow_abort setting in the config (see PyXLL Settings).
Enabling this option has performance implications. See Interrupting Functions for more details.
volatile (boolean) – if True the function will be registered as a volatile function, which means it will be called every time Excel recalculates regardless of whether any of the parameters to the function have changed or not
disable_function_wizard_calc (boolean) –
Don’t call from the Excel function wizard. This is useful for functions that take a long time to complete that would otherwise make the function wizard unresponsive
The default value for this argument can be set in the pyxll.cfg file.
disable_replace_calc (boolean) –
Set to True to stop the function being called from Excel’s find and replace dialog.
The default value for this argument can be set in the pyxll.cfg file.
arg_descriptions – dict of parameter names to help strings.
name (string) – The Excel function name. If None, the Python function name is used.
auto_resize (boolean) – When returining an array, PyXLL can automatically resize the range used by the formula to match the size of the result.
hidden (boolean) –
If True the UDF is hidden and will not appear in the Excel Function Wizard.
@Since PyXLL 3.5.0
transpose (boolean) –
If true, if an array is returned it will be transposed before being returned to Excel. This can be used for returning 1d lists as rows.
@Since PyXLL 4.2.0
recalc_on_open (boolean) –
If true, when saved and re-opened the cell calling this function will be recalculated. The default is True for functions returning cached objects and RTD functions, and False otherwise.
@Since PyXLL 4.5.0
recalc_on_reload (boolean) –
If true, when the add-in is reloaded the cell calling this function will be recalculated.
@Since PyXLL 5.10.0
formatter (pyxll.Formatter) –
Formatter object to use to format the result of the function.
For brevity a dict may be used, in which case a Formatter will be
constructed from that dict.
See Cell Formatting.
@Since PyXLL 4.5.0
nan_value –
Value to use in the case that the return value is NaN.
Defaults to the global setting nan_value set in the config file, or #NUM! if not set.
Set to an Exception instance (e.g. RuntimeError()) to return an Excel error.
@Since PyXLL 5.5.0
posinf_value –
Value to use in the case that the return value is +Inf.
Defaults to the global setting posinf_value set in the config file, or Excel’s own numeric representation of +Inf if not set.
Set to an Exception instance (e.g. RuntimeError()) to return an Excel error.
@Since PyXLL 5.5.0
neginf_value –
Value to use in the case that the return value is -Inf.
Defaults to the global setting neginf_value set in the config file, or Excel’s own numeric representation of -Inf if not set.
Set to an Exception instance (e.g. RuntimeError()) to return an Excel error.
@Since PyXLL 5.5.0
none_value –
Value to use in the case that the return value is None.
Defaults to the global setting none_value set in the config file. If not set, the Excel NULL value is used
which gets displayed as 0 by default.
Set to an empty string to return an empty looking cell instead of 0.
@Since PyXLL 5.9.0
Example usage:
from pyxll import xl_func
@xl_func
def hello(name):
"""return a familiar greeting"""
return "Hello, %s" % name
# Python 3 using type annotations
@xl_func
def hello2(name: str) -> str:
"""return a familiar greeting"""
return "Hello, %s" % name
# Or a signature may be provided as string
@xl_func("int n: int", category="Math", thread_safe=True)
def fibonacci(n):
"""naive iterative implementation of fibonacci"""
a, b = 0, 1
for i in xrange(n):
a, b = b, a + b
return a
See Worksheet Functions for more details about using the xl_func decorator, and Array Functions for more details about array functions.
XLAsyncHandle instances are passed to Asynchronous Functions as the async_handle argument.
They are passed to xlAsyncReturn to return the result from an asynchronous function.
Set the value on the handle and return it to Excel.
Equivalent to xlAsyncReturn.
@Since PyXLL 4.2.0
Example usage:
from pyxll import xl_func
import threading
import sys
@xl_func("async_handle h, int x")
def async_func(h, x):
def thread_func(h, x):
try:
result = do_calculation(x)
h.set_value(result)
except:
result.set_error(*sys.exc_info())
thread = threading.Thread(target=thread_func, args=(h, x))
thread.start()
New in PyXLL 4.2
For Python 3.5.1 and later, asynchronous UDFs can be simplified by simply using the async keyword on the function declaration and dropping the async_handle argument.
Async functions written in this way run in an asyncio event loop on a background thread.
