xlOil Python Dynamic Runtime Interaction

xlOil function registration can be controlled dynamically at runtime. Also, the Excel can be controlled from macros or the console.

Dynamic Import

Functions for registration can be specified at runtime without the need to add an xloil.func decorator.

Note

Although Excel will let you, avoid doing this from (non-async) worksheet functions since creating new functions during Excel’s calculation cycle is likely to cause instability.

The xloil.import_functions call provides an analogue of from X import Y as Z with Excel UDFs. A simple usage is:

xloil.import_functions("c:/lib/AnotherFile.py", names=["greet"], as_names=["PyGreet"])

where AnotherFile.py contains:

def greet(x:str):
    return f"Hello {x}!"

We specify the Excel name of the function explicitly, if we omitted this, the function would be registered with its python name. In Excel you can then use the formula =greet("World").

Typing annotations are respected, as are doc-strings - the import behaves as if we had decorated the function with xloil.func.

In a worksheet, xloil.import_functions is exposed as xloImport with the same arguments.

Since the import machinery can register any callable, including class constructors, you cane be a little creative. For example, the following cell formulae will create a pandas DataFrame from the range C1:F5, sum over rows and take the average of the result.

[A1] : =xloImport("pandas","DataFrame")

[A2] : =DataFrame(C1:F5)

[A3] : =xloAttr(xloAttrObj(A2,"sum",{"axis",1}), "mean")

Notice we used xloAttrObj - the output of this is always a cache reference. This stops xlOil from trying to convert the result to an Excel value. Since a DataFrame is iterable it would otherwise output DataFrame.index as an array. Also note the convenient use of array constants to specify keyword arguments.

Dynamic Registration

Functions can be registed using xloil.register_functions and deregistered with xloil.deregister_functions.

For example:

def Greet(x):
    return f"Hello {x}"

xlo.register_functions([Greet])

Any callable can be registered, for example:

class Closure:
    self._total = 0
    def __call__(self, x):
        self._total += x
        return x

xlo.register_functions(Closure())

The name and help of the function can be controlled using xloil.func and the function can be linked to a specific python module, which means it will be removed if the module is unloaded or reloaded.

xlo.register_functions(
    [xlo.func(fn=Closure(), name=f"Dynamic1", register=False)],
    module=sys.modules[__name__])

Functions are deregistered by name:

xlo.deregister_functions("Greet")