xlOil Python Concepts

Workbook Modules

When an Excel workbook is opened, xlOil tries to load the module <workbook_name>.py (this is configurable). When registering functions from workbook modules, xlOil defaults to making any declared functions Local Functions

The function xloil.linked_workbook when called from a workbook module retrieves the associated workbook path.

Another way to package python code for distribution is to create an XLL, see core-distributing-addins

Array Functions

By default, xlOil-Python converts Excel array arguments to numpy arrays. The conversion happens entirely in C++ and is very fast. Where possible you should write functions which support array processing (vectorising) to take advantage of this, for example the following works when it’s arguments are arrays or numbers:

@xlo.func
def cubic(x, a, b, c, d)
    return a * x ** 3 + b * x ** 2 + c * x  + d

You can take this further to evaluate polynominals of n-th degree:

@xlo.func
def poly(x, coeffs: xlo.Array(float)):
    return np.sum(coeffs * x[:,None] ** range(coeffs.T.shape[0]), axis=1)

Specifing that we expect an array argument and the data type of that array avoids the overhead of xlOil determining the type. There is a problem with this function: what happens if x is two-dimensional? To avoid this possibility we can specify:

@xlo.func
def poly(x: xlo.Array(dims=1), coeffs: xlo.Array(float)):
    return np.sum(coeffs * X[:,None] ** range(coeffs.T.shape[0]), axis=1)

Events

Events allow for a callback on user interaction. If you are familiar with VBA, you may have used Excel’s event model already. Most of the workbook events described in Excel.Appliction are available in xlOil.

See Events for more details on python events and Events for a description of the available Excel events.

Excel events do not use return values. However, some events take reference parameters. For example, WorkbookBeforeSave has a boolean cancel parameter. Setting this to True cancels the save. As references to primitive types aren’t supported in python, in xlOil you need to set this value using cancel.value=True.

Event handlers are (currently) global to the Excel instance, so you may need to filter by workbook name when handling events even if you have hooked the event in a local workbook module.

Examples

def greet(workbook, worksheet):
    xlo.Range(f"[{workbook}]{worksheet}!A1") = "Hello!"

xlo.event.WorkbookNewSheet += greet

Registering functions in other modules

xlOil automatically scans modules when they are imported or reloaded via a hook in python’s import mechanism. This ensures any xloil.func decorated functions are registered.

If you load a module outside the normal import mechanism, you can tell xlOil to look for functions to register with xloil.scan_module.

Also see Dynamic Registration, which explains how any python callable can be registered as an Excel function.

Multiple addins and event loops

xlOil_Python can be used by multiple add-ins, that is, more than one XLL loader with its own settings and python codebase can exist in the same Excel session.

  • Each add-in / XLL is loaded in a background thread equipped with an asyncio event loop. Get the loop using xloil.get_event_loop.

  • You can find the addin associated with the currently running code with xloil.source_addin .

  • All add-ins share the same python interpreter

  • All add-ins share the python object cache

  • Worksheet functions are executed in Excel’s main thread or one of its worker threads for thread safe functions

  • Async / RTD worksheet functions are executed in a dedicated xlOil Core event loop which you can access with xloil.get_async_loop()

  • You can ask xlOil to create a separate thread & event loop for an addin.

Although CPython supports subinterpreters, most C-based extensions, particularly numpy do not, so there are no plans to add subinterpreter support at this stage.