xlOil Python Module Reference

Declaring Worksheet Functions 

`AllowRange`
`Arg`(name[, help, typeof, default, kind])	Holds the description of a function argument.
`Array`([dtype, dims, trim, fast, cache_return])	This object can be used in annotations or @xlo.arg decorators to tell xlOil to attempt to convert an argument to a numpy array.
`Cache`	alias of `_TypeConverter`
`CannotConvert`	Should be thrown by a converter when it is unable to handle the provided type.
`CellError`(value)	Enum-type class which represents an Excel error condition of the
`ExcelArray`()	A view of a internal Excel array which can be manipulated without copying the underlying data.
`FastArray`	Tells Excel to pass a 2-d array of float, which appears in python as a 2-d numpy.array of float.
`SingleValue`	alias of `_TypeConverter`
`func`([fn, name, help, args, group, local, ...])	Decorator which tells xlOil to register the function (or callable) in Excel.
`converter`([target, range, register, ...])	Decorator which declares a function or a class to be a type converter which serialises from/to a set of simple types understood by Excel and general python types.
`returner`([target, register])	A proxy for converter(..., direction="write")
`import_functions`(source[, names, as_names, ...])	Loads functions from the specified source and registers them in Excel.
`register_functions`(funcs[, module, append])	Registers the provided callables and associates them with the given module
`deregister_functions`(funcs[, module])	Deregisters worksheet functions linked to specified module.
`scan_module`(module[, addin])	Parses a specified module to look for functions with with the xloil.func decorator and register them.

class xloil.Arg(name, help='', typeof=None, default=<class 'xloil.func_inspect.Arg._EMPTY'>, kind=0)

Holds the description of a function argument. Can be used with the xloil.func decorator to specify the argument description.

Examples

@xloil.func(args = { 
    'a': xloil.Arg("A", "The First Arg", default=3),
    'b': xloil.Arg("B", "Next Arg",      typeof=double),
})
def MyFunc(a, b, c):
    ...

KEYWORD_ARGS = 1

POSITIONAL = 0

VARIABLE_ARGS = 2

classmethod from_signature(name, param): Constructs an Arg from a name and an inspect.param

classmethod full_argspec(func): Returns a list of Arg for a given function which describe the function’s arguments

property has_default: Since ‘None’ is a fairly likely default value, this property indicates whether there was a user-specified default

static override_arglist(arglist, replacements)

class xloil.Array(dtype=<class 'object'>, dims=2, trim=True, fast=False, cache_return=False)

This object can be used in annotations or @xlo.arg decorators to tell xlOil to attempt to convert an argument to a numpy array.

You don’t use this type directly, Array is a static instance of this type, so use the syntax as show in the examples below.

If you don’t specify this annotation, xlOil may still pass an array to your function if the user passes a range argument, e.g. A1:B2. In this case you will get a 2-dim Array(object). If you know the data type you want, it is more perfomant to specify it by annotation with Array.

Parameters:

*(dtype, dims, trim)*

Element types are converted to numpy dtypes, which means the only supported types are: int, float, bool, str, datetime, object. (Numpy has a richer variety of dtypes than this but Excel does not.)

For the float data type, xlOil will convert #N/As to numpy.nan but other values will causes errors.

dimsint

Arrays can be either 1 or 2 dimensional, 2 is the default. Note the Excel has the following behaviour for writing arrays into an array formula range specified with Ctrl-Alt-Enter: “If you use a horizontal array for the second argument, it is duplicated down to fill the entire rectangle. If you use a vertical array, it is duplicated right to fill the entire rectangle. If you use a rectangular array, and it is too small for the rectangular range you want to put it in, that range is padded with #N/As.”

trimbool

By default xlOil trims arrays to the last row & column which contain a nonempty string or non-#N/A value. This is generally desirable, but can be disabled with this paramter. Has no effect when used for return values.

fast: bool

Specifies a xloil.FastArray. This can only be used with 2-dim float arrays. See the doc string for the class for more details.

cache_return: bool

If used in a return value annotation, returns a cache reference to the result. This avoids copying the array data back to Excel and can improve performance where the array is passed to another xlOil function.

Examples

@xlo.func
def array1(x: xlo.Array(int)):
    pass

@xlo.func
def array2(y: xlo.Array(float, dims=1)):
    pass

@xlo.func
def array3(z: xlo.Array(str, trim=False)):
    pass

xloil.Cache: alias of _TypeConverter

exception xloil.CannotConvert: Should be thrown by a converter when it is unable to handle the provided type. In a return converter it may not indicate a fatal condition, as xlOil will fallback to another converter.

class xloil.CellError(value: int)

Enum-type class which represents an Excel error condition of the form #N/A!, #NAME!, etc passed as a function argument. If a function argument does not specify a type (e.g. int, str) it may be passed a CellError, which it can handle based on the error condition.

Members:

NULL

DIV

VALUE

REF

NAME

NUM

NA

GETTING_DATA

DIV: CellError = None

GETTING_DATA: CellError = None

NA: CellError = None

NAME: CellError = None

NULL: CellError = None

NUM: CellError = None

REF: CellError = None

VALUE: CellError = None

property name: str

Type:: str

property value: int

Type:: int

class xloil.ExcelArray

A view of a internal Excel array which can be manipulated without copying the underlying data. It’s not a general purpose array class but rather used to create efficiencies in type converters.

It can be accessed and sliced using the usual syntax (the slice step must be 1):

x[1, 1] # The value at 1,1 as int, str, float, etc.

x[1, :] # The second row as another ExcelArray

x[:-1, :-1] # A sub-array omitting the last row and column

property dims: int

Property which gives the dimension of the array: 1 or 2

Type:: int

property ncols: int

Returns the number of columns in the array

Type:: int

property nrows: int

Returns the number of rows in the array

Type:: int

property shape: tuple

Returns a tuple (nrows, ncols) like numpy’s array.shape

Type:: tuple

slice(from_row: int, from_col: int, to_row: int, to_col: int) → ExcelArray: Slices the array

to_numpy(dtype: int | None = None, dims: int | None = 2) → object: Converts the array to a numpy array. If dtype is None, xlOil attempts to determine the correct numpy dtype. It raises an exception if values cannot be converted to a specified dtype. The array dimension dims can be 1 or 2 (default is 2).

class xloil.FastArray

Tells Excel to pass a 2-d array of float, which appears in python as a 2-d numpy.array of float. No other types are allowed. This significantly reduces the overhead of passing large array arguments but is less flexible: defaults are not supported and if any value in the input array is not a number, Excel will return #VALUE! before even calling xlOil.This means cache auto-expansion and array auto-trimming are not possible.

When used as a return type, the function must return a 2-d numpy.array of float and cannot return error conditions: errors raised will be written to the log, but the function will return NaN.

** Cannot be used in local functions **

xloil.SingleValue: alias of _TypeConverter

xloil.converter(target=<class 'object'>, range=False, register=False, direction='read', check_cache=True)

Decorator which declares a function or a class to be a type converter which serialises from/to a set of simple types understood by Excel and general python types.

A type converter class is expected to implement at least one of read(self, val) and write(self, val). It may take parameters in its constructor and hold state.

A function is interpreted as a type reader or writer depending on the direction parameter.

Readers

A reader converts function arguments to python types. It should receieve a value of:

int, bool, float, str, xloil.ExcelArray, CellError, xloil.Range (optional)

and return a python object or raise an exception (ideally xloil.CannotConvert).

If range is True, xlOil may pass a Range or ExcelArray object depending on how the function was invoked. The converter should handle both cases consistently.

Writers

A return type converter should take a python object and return a simple type which xlOil knows how to return to Excel. It should raise CannotConvert if it cannot handle the given object.

Parameters:

target:: The type which the converter handles
register:: If True, registers the converter as a handler for target type, replacing any exsting handlers. For a reader, this means if target is used as an argument annotation, this converter will be used. For a writer, it enables target as an return type annotation and it allows xlOil to try to call this converter for Excel functions with no return annotation.
range:: For readers, setting this flag allows xloil.Range arguments to be passed
direction:: When decorating a function, the direction “read” or “write” determines the converter behaviour
check_cache:: For readers, setting this to False turns off xlOil’s automatic cache expansion for string inputs. The converter must manually expand cache strings if desired.

Examples

@converter(double)
def arg_sum(x):
    if isinstance(x, ExcelArray):
        return np.sum(x.to_numpy())
    elif isinstance(x, str):
        raise Error('Unsupported')
    return x

@func
def pyTest(x: arg_sum):
    return x

xloil.deregister_functions(funcs: object, module: object = None) → None: Deregisters worksheet functions linked to specified module. Generally, there is no need to call this directly.

xloil.func(fn=None, name=None, help='', args=None, group='', local=None, rtd=None, macro=True, command=False, threaded=False, volatile=False, is_async=False, register=True, errors=None)

Decorator which tells xlOil to register the function (or callable) in Excel. If arguments are annotated using ‘typing’ annotations, xlOil will attempt to convert values received from Excel to the specfied type, raising an exception if this is not possible. The currently available types are

int
float
str: Note this disables cache lookup
bool
numpy arrays: see Array
CellError: Excel has various error types such as #NUM!, #N/A!, etc.
None: if the argument points to an empty cell
cached objects
datetime.date
datetime.datetime
dict / kwargs: this converter expects a two column array of key/value pairs
arg list / *args

If no annotations are specified, xlOil will pass a type from the first eight above types based on the value provided from Excel.

If a parameter default is given in the function signature, that parameter becomes optional in the declared Excel function.

If keyword args (**kwargs) are specified, xlOil expects a two-column array of (string, value) to be passed. For variable args (*args) xlOil adds a large number of trailing optional arguments. The variable argument list is ended by the first missing argument. If both kwargs and args are specified, their order is reversed in the Excel function declaration.

Parameters:

fn: function or Callable:

Automatically passed when func is used as a decorator

name: str

Overrides the funtion name registered with Excel otherwise the function’s declared name is used.

help: str

Overrides the help shown in the function wizard otherwise the function’s doc-string is used. The wizard cannot display strings longer than 255 chars. Longer help string can be retrieved with xloHelp

args: dict

A dictionary with key names matching function arguments and values specifying information for that argument. The information can be a string, which is interpreted as the help to display in the function wizard or it can be an xloil.Arg object which can contain defaults, help and type information.

group: str

Specifes a category of functions in Excel’s function wizard under which this function should be placed.

local: bool

Functions in a workbook-linked module, e.g. Book1.py, default to workbook-level scope (i.e. not usable outside that workbook) itself. You can override this behaviour with this parameter. It has no effect outside workbook-linked modules.

macro: bool

If True, registers the function as Macro Sheet Type. This grants the function extra priveledges, such as the ability to see un-calced cells and call Excel.Application functions. This is not the same as a ‘VBA Macro’ which is a command. Threaded functions cannot be declared as macro type.

command: bool

If True, registers this as a Command. Commands are run outside the calculation cycle on Excel’s main thread, have full permissions on the Excel object model and do not return a value. They correspond to VBA’s ‘Macros’ or ‘Subroutines’. Unless declared local, XLL commands are hidden and not displayed in dialog boxes for running macros, although their names can be entered anywhere a valid command name is required. Commands cannot currently be run async since their primary use is writing to the workbook which requires running on Excel’s main thread.

rtd: bool

Determines whether a function declared as async uses native or RTD async. Only RTD functions are calculated in the background in Excel, native async functions will be stopped if calculation is interrupted. Default is True.

threaded: bool

Declares the function as safe for multi-threaded calculation. The function must be careful when accessing global objects. Since python (at least CPython) is single-threaded there is no direct performance benefit from enabling this. However, if you make frequent calls to C-based libraries like numpy or pandas you make be able to realise speed gains.

volatile: bool

Tells Excel to recalculate this function on every calc cycle: the same behaviour as the NOW() and INDIRECT() built-ins. Due to the performance hit this brings, it is rare that you will need to use this attribute.

is_async: bool

If true, manually creates an async function. This means your function must take a thread context as its first argument and start its own async task similar to xloil.async_wrapper. Generally this parameter should not be used and async functions declared using the normal async def syntax.

errors: str [‘propagate’, ‘accept’, ‘’]

When errors is set to ‘propagate’, if any function argument is an error code (#N/A!, #NUM!, etc.), that error code is given as the function’s return value, otherwise all argument values are handled by the function. Error propagation is consistent with how Excel built-in functions handle errors, it improves performance in the presence of error codes and it allow’s Excel’s error tracing of function.

If errors is set to an empty string (the default), the ini file setting ‘ErrorPropagation’ determines the behaviour. If errors is ‘accept’, error values are passed to the function regardless of the ini file setting.

xloil.import_functions(source: str, names=None, as_names=None, addin: Addin = None, workbook_name: str = None) → None

Loads functions from the specified source and registers them in Excel. The functions do not have to be decorated, but are imported as if they were decorated with xloil.func. So if the functions have typing annotations, they are respected where possible.

This function provides an analogue of from X import Y as Z but with Excel UDFS.

Note: registering a large number of Excel UDFs will impair the function name-lookup performance (which is by linear search through the name table).

Parameters:

source: str

A module name or full path name to the target py file

names: [Iterable[str] | dict | str]

If not provided, the specified module is imported and any xloil.func decorated functions are registered, i.e. call xloil.scan_module.

If a str or an iterable of str, xlOil registers only the specified names regardless of whether they are decorated.

If it is a dict, it is interpreted as a map of source names to registered function names, i.e.``names = keys(), as_names=values()``.

If it is the string ‘*’, xlOil will try to register all callables in the specified module, including async functions and class constructors.

as_names: [Iterable[str]]

If provided, specifies the Excel function names to register in the same order as names. Should have the same length as names. If this is omitted, functions are registered under their python names.

addin:

Optional xlOil.Addin which the registered functions are associated with. If ommitted the currently executing addin is used, or the Core addin if this cannot be determined.

workbook_name: [str]

Optional workbook associated with the registered functions.

xloil.register_functions(funcs, module=None, append=True)

Registers the provided callables and associates them with the given module

Parameters:

funcs: iterable: An iterable of _WorksheetFunc (a callable decorated with func), callables or _FuncSpec. A callable is registered by using func with the default settings. Passing one of the other two allows control over the registration such as changing the function or argument names.
module: python module: A python module which contains the source of the functions (it does not have to equal the module calling this function). If this module is edited it is automatically reloaded and the functions re-registered. Passing None disables this behaviour.
append: bool: Whether to append to or overwrite any existing functions associated with the module

xloil.returner(target=None, register=False): A proxy for converter(…, direction=”write”)

xloil.scan_module(module, addin=None): Parses a specified module to look for functions with with the xloil.func decorator and register them. Rather than call this manually, it is easer to import xloil.importer which registers a hook on the import function.

xloil.AllowRange(*args, **kwargs): alias of Union[bool, int, str, float, ndarray, dict, list, CellError, Range]

Excel Object Model 

`workbooks`	A collection of all the Workbook objects that are currently open in the Excel application.
`worksheets`
`app`()	Returns the parent Excel Application object when xlOil is loaded as an addin.
`active_worksheet`()	Returns the currently active worksheet.
`active_workbook`()	Returns the currently active workbook.
`Application`([com, hwnd, workbook])	Manages a handle to the Excel.Application object.
`Caller`(args, *kwargs)	Captures the caller information for a worksheet function.
`Range`(address)	Represents a cell, a row, a column or a selection of cells containing a contiguous blocks of cells.
`Workbook`()	A handle to an open Excel workbook.
`Worksheet`()	Allows access to ranges and properties of a worksheet.
`ExcelWindow`()	A document window which displays a view of a workbook.
`ExcelWindows`()	A collection of all the document window objects in Excel.
`Workbooks`()	A collection of all the Workbook objects that are currently open in the Excel application.
`Worksheets`()	A collection of all the Worksheet objects in the specified or active workbook.

xloil.workbooks = <xloil.Workbooks object>

A collection of all the Workbook objects that are currently open in the Excel application.

See Excel.Workbooks

xloil.worksheets = <xloil._core._ActiveWorksheets object>

xloil.app() → Application: Returns the parent Excel Application object when xlOil is loaded as an addin. Will throw if xlOil has been imported to run automation.

xloil.active_worksheet() → Worksheet: Returns the currently active worksheet. Will raise an exception if xlOil has not been loaded as an addin.

xloil.active_workbook() → Workbook: Returns the currently active workbook. Will raise an exception if xlOil has not been loaded as an addin.

class xloil.Application(com: object = None, hwnd: object = None, workbook: object = None)

Manages a handle to the Excel.Application object. This object is the root of Excel’s COM interface and supports a wide range of operations.

In addition to the methods known to python, properties and methods of the Application object can be resolved dynamically at runtime. The available methods will be familiar to VBA programmers and are well documented by Microsoft, see Object Model Overview

Note COM methods and properties are in UpperCamelCase, whereas python ones are lower_case.

Examples

To get the name of the active worksheet:

return xlo.app().ActiveWorksheet.Name

See Excel.Application

__enter__() → object

__exit__(arg0: object, arg1: object, arg2: object) → None

calculate(full: bool = False, rebuild: bool = False) → None

Calculates all open workbooks

Parameters:

full:: Forces a full calculation of the data in all open workbooks
rebuild:: For all open workbooks, forces a full calculation of the data and rebuilds the dependencies. (Implies full)

property enable_events: bool

Pauses or resumes Excel’s event handling. It can be useful when writing to a sheet to pause events both for performance and to prevent side effects.

Type:: bool

open(filepath: str, update_links: bool = True, read_only: bool = False, delimiter: object = None) → Workbook

Opens a workbook given its full filepath.

Parameters:

filepath:: path and filename of the target workbook
update_links:: if True, attempts to update links to external workbooks
read_only:: if True, opens the workbook in read-only mode

quit(silent: bool = True) → None: Terminates the application. If silent is True, unsaved data in workbooks is discarded, otherwise a prompt is displayed.

range(address: str) → object: Create a range object from an external address such as “[Book]Sheet!A1”

to_com(lib: str = '') → object: Returns a managed COM object which can be used to invoke Excel’s full object model. For details of the available calls see the Microsoft documentation on the Excel Object Model. The lib used to provide COM support can be ‘comtypes’ or ‘win32com’. If omitted, the default is ‘win32com’, unless specified in the XLL’s ini file.

property visible: bool

Determines whether the Excel window is visble on the desktop

Type:: bool

property windows: ExcelWindows

A collection of all Windows open in this Application

Type:: ExcelWindows

property workbook_paths: None

A set of the full path names of all workbooks open in this Application. Does not use COM interface.

Type:: None

property workbooks: Workbooks

A collection of all Workbooks open in this Application

Type:: Workbooks

class xloil.Caller(*args, **kwargs)

Captures the caller information for a worksheet function. On construction the class queries Excel via the xlfCaller function.

address(a1style=False): Gives the sheet address either in A1 form: ‘Sheet!A1’ or RC form: ‘Sheet!R1C1’

property sheet: Gives the sheet name of the caller or None if not called from a sheet.

property workbook: Gives the workbook name of the caller or None if not called from a sheet. If the workbook has been saved, the name will contain a file extension.

class xloil.Range(address: str)

Represents a cell, a row, a column or a selection of cells containing a contiguous blocks of cells. (Non contiguous ranges are not currently supported). This class allows direct access to an area on a worksheet. It uses similar syntax to Excel’s Range object, supporting the cell and range functions, however indices are zero-based as per python’s standard.

A Range can be accessed and sliced using the usual syntax (the slice step must be 1):

x[1, 1] # The *value* at (1, 1) as a python type: int, str, float, etc.

x[1, :] # The second row as another Range object

x[:-1, :-1] # A sub-range omitting the last row and column

See Excel.Range

__getitem__(arg0: object) → object: Given a 2-tuple, slices the range to return a sub Range or a single element.Uses normal python slicing conventions i.e[left included, right excluded), negative numbers are offset from the end.If the tuple specifies a single cell, returns the value in that cell, otherwise returns a Range object.

address(local: bool = False) → str: Returns the address of the range in A1 format, e.g. [Book]SheetNm!A1:Z5. The sheet name may be surrounded by single quote characters if it contains a space. If local is set to true, everything prior to the ‘!’ is omitted.

property bounds: Tuple[int, int, int, int]

Returns a zero-based tuple (top-left-row, top-left-col, bottom-right-row, bottom-right-col) which defines the Range area (currently only rectangular ranges are supported).

Type:: Tuple[int, int, int, int]

cell(row: int, col: int) → Range: Returns a Range object which consists of a single cell. The indices are zero-based from the top left of the parent range.

clear() → None: Clears all values and formatting. Any cell in the range will then have Empty type.

property formula: object

Get / sets the formula for the range. If the cell contains a constant, this property returns the value. If the cell is empty, this property returns an empty string. If the cell contains a formula, the property returns the formula that would be displayed in the formula bar as a string. If the range is larger than one cell, the property returns an array of the values which would be obtained calling formula on each cell.

When setting, if the range is larger than one cell and a single value is passed that value is filled into each cell. Alternatively, you can set the formula to an array of the same dimensions.

Type:: object

property ncols: int

Returns the number of columns in the range

Type:: int

property nrows: int

Returns the number of rows in the range

Type:: int

property parent: Worksheet

Returns the parent Worksheet for this Range

Type:: Worksheet

range(from_row: int, from_col: int, to_row: object = None, to_col: object = None, num_rows: object = None, num_cols: object = None) → Range

Creates a subrange using offsets from the top left corner of the parent range. Like Excel’s Range function, we allow negative offsets to select ranges outside the parent.

Parameters:

from_row: int: Starting row offset from the top left of the parent range. Zero-based. Can be negative
from_col: int: Starting row offset from the top left of the parent range. Zero-based. Can be negative
to_row: int: End row offset from the top left of the parent range. This row will be included in the range. The offset is zero-based and can be negative to select ranges outside the parent range. Do not specify both to_row and num_rows.
to_col: int: End column offset from the top left of the parent range. This column will be included in the range. The offset is zero-based and can be negative to select ranges outside the parent range. Do not specify both to_col and num_cols.
num_rows: int: Number of rows in output range. Must be positive. If neither num_rows or to_rows are specified, the range ends at the last row of the parent range.
num_cols: int: Number of columns in output range. Must be positive. If neither num_cols or to_cols are specified, the range ends at the last column of the parent range.

set(arg0: object) → None: Sets the data in the range to the provided value. If a single value is passed all cells will be set to the value. If a 2d-array is provided, the array will be pasted at the top-left of the range with the remainging cells being set to #N/A. If a 1d array is provided it will be pasted at the top left and repeated down or right depending on orientation.

set_formula(formula: object, how: str = '') → None

The how parameter determines how this function differs from setting the formula property:

dynamic (or omitted): identical to setting the formula property

array: if the target range is larger than one cell and a single string is passed, set this as an array formula for the range

implicit: uses old-style implicit intersection - see “Formula vs Formula2” on MSDN

property shape: Tuple[int, int]

Returns a tuple (num columns, num rows)

Type:: Tuple[int, int]

to_com(lib: str = '') → object: Returns a managed COM object which can be used to invoke Excel’s full object model. For details of the available calls see the Microsoft documentation on the Excel Object Model. The lib used to provide COM support can be ‘comtypes’ or ‘win32com’. If omitted, the default is ‘win32com’, unless specified in the XLL’s ini file.

trim() → Range: Returns a sub-range by trimming to the last non-empty (i.e. not Nil, #N/A or “”) row and column. The top-left remains the same so the function always returns at least a single cell, even if it’s empty.

property value: object

Property which gets or sets the value for a range. A fetched value is converted to the most appropriate Python type using the normal generic converter.

If you use a horizontal array for the assignment, it is duplicated down to fill the entire rectangle. If you use a vertical array, it is duplicated right to fill the entire rectangle. If you use a rectangular array, and it is too small for the rectangular range you want to put it in, that range is padded with #N/As.

Type:: object

class xloil.Workbook

A handle to an open Excel workbook. See Excel.Workbook

__getitem__(arg0: object) → object: If the index is a worksheet name, returns the Worksheet object, otherwise treats the string as a workbook address and returns a Range.

add(name: object = None, before: object = None, after: object = None) → Worksheet

Add a worksheet, returning a Worksheet object.

Parameters:

name: str: Names the worksheet, otherwise it will have an Excel-assigned name
before: Worksheet: Places the new worksheet immediately before this Worksheet object
after: Worksheet: Places the new worksheet immediately before this Worksheet object. Specifying both before and after raises an exception.

property app: Application

Returns the parent xloil.Application object associated with this object.

Type:: Application

close(save: bool = True) → None: Closes the workbook. If there are changes to the workbook and the workbook doesn’t appear in any other open windows, the save argument specifies whether changes should be saved. If set to True, changes are saved to the workbook, if False they are discared.

property name: str

Type:: str

property path: str

The full path to the workbook, including the filename

Type:: str

range(address: str) → object: Create a Range object from an address such as “Sheet!A1” or a named range

save(filepath: str = '') → None: Saves the Workbook, either to the specified filepath or if this is unspecified, to its original source file (an error is raised if the workbook has never been saved).

to_com(lib: str = '') → object: Returns a managed COM object which can be used to invoke Excel’s full object model. For details of the available calls see the Microsoft documentation on the Excel Object Model. The lib used to provide COM support can be ‘comtypes’ or ‘win32com’. If omitted, the default is ‘win32com’, unless specified in the XLL’s ini file.

property windows: ExcelWindows

A collection object of all windows which are displaying this workbook

Type:: ExcelWindows

worksheet(name: str) → Worksheet: Returns the named worksheet which is part of this workbook (if it exists) otherwise raises an exception.

property worksheets: Worksheets

A collection object of all worksheets which are part of this workbook

Type:: Worksheets

class xloil.Worksheet

Allows access to ranges and properties of a worksheet. It uses similar syntax to Excel’s Worksheet object, supporting the cell and range functions, however indices are zero-based as per python’s standard.

See Excel.Worksheet

__getitem__(arg0: object) → object

If the argument is a string, returns the range specified by the local address, equivalent to at.

If the argument is a 2-tuple, slices the sheet to return an xloil.Range. Uses normal python slicing conventions, i.e [left included, right excluded), negative numbers are offset from the end.

activate() → None: Makes this worksheet the active sheet

property app: Application

Returns the parent xloil.Application object associated with this object.

Type:: Application

at(address: str) → _ExcelRange: Returns the range specified by the local address, e.g. .at('B3:D6')

calculate() → None: Calculates this worksheet

cell(row: int, col: int) → _ExcelRange: Returns a Range object which consists of a single cell. The indices are zero-based from the top left of the parent range.

property name: str

Type:: str

property parent: Workbook

Returns the parent Workbook for this Worksheet

Type:: Workbook

range(from_row: int, from_col: int, to_row: object = None, to_col: object = None, num_rows: object = None, num_cols: object = None) → Range

Specifies a range in this worksheet.

Parameters:

from_row: int: Starting row offset from the top left of the parent range. Zero-based.
from_col: int: Starting row offset from the top left of the parent range. Zero-based.
to_row: int: End row offset from the top left of the parent range. This row will be included in the range. The offset is zero-based. Do not specify both to_row and num_rows.
to_col: int: End column offset from the top left of the parent range. This column will be included in the range. The offset is zero-based. Do not specify both to_col and num_cols.
num_rows: int: Number of rows in output range. Must be positive. If neither num_rows or to_rows are specified, the range ends at the end of the sheet.
num_cols: int: Number of columns in output range. Must be positive. If neither num_cols or to_cols are specified, the range ends at the end of the sheet.

to_com(lib: str = '') → object: Returns a managed COM object which can be used to invoke Excel’s full object model. For details of the available calls see the Microsoft documentation on the Excel Object Model. The lib used to provide COM support can be ‘comtypes’ or ‘win32com’. If omitted, the default is ‘win32com’, unless specified in the XLL’s ini file.

property used_range: _ExcelRange

Returns a Range object that represents the used range on the worksheet

Type:: _ExcelRange

class xloil.ExcelWindow

A document window which displays a view of a workbook. See Excel.Window

property app: Application

Returns the parent xloil.Application object associated with this object.

Type:: Application

property hwnd: int

The Win32 API window handle as an integer

Type:: int

property name: str

Type:: str

to_com(lib: str = '') → object: Returns a managed COM object which can be used to invoke Excel’s full object model. For details of the available calls see the Microsoft documentation on the Excel Object Model. The lib used to provide COM support can be ‘comtypes’ or ‘win32com’. If omitted, the default is ‘win32com’, unless specified in the XLL’s ini file.

property workbook: Workbook

The workbook being displayed by this window

Type:: Workbook

class xloil.ExcelWindows

A collection of all the document window objects in Excel. A document window shows a view of a Workbook.

See Excel.Windows

property active: object

Gives the active (as displayed in the GUI) object in the collection or None if no object has been activated.

Type:: object

get(name: str, default: object = None) → object: Tries to get the named object, returning the default if not found

class xloil.Workbooks

A collection of all the Workbook objects that are currently open in the Excel application.

See Excel.Workbooks

property active: object

Gives the active (as displayed in the GUI) object in the collection or None if no object has been activated.

Type:: object

add() → Workbook: Creates and returns a new workbook with an Excel-assigned name

get(name: str, default: object = None) → object: Tries to get the named object, returning the default if not found

class xloil.Worksheets

A collection of all the Worksheet objects in the specified or active workbook.

See Excel.Worksheets

property active: object

Gives the active (as displayed in the GUI) object in the collection or None if no object has been activated.

Type:: object

add(name: object = None, before: object = None, after: object = None) → Worksheet

Add a worksheet, returning a Worksheet object.

Parameters:

name: str: Names the worksheet, otherwise it will have an Excel-assigned name
before: Worksheet: Places the new worksheet immediately before this Worksheet object
after: Worksheet: Places the new worksheet immediately before this Worksheet object. Specifying both before and after raises an exception.

get(name: str, default: object = None) → object: Tries to get the named object, returning the default if not found

class xloil.PauseExcel: A context manager which pauses Excel by disabling events, turning off screen updating and switching to manual calculation mode. Which of these changees are applied can be controlled by parameters to the constructor - the default is to apply all of them.

RTD Functions 

`RtdPublisher`()	RTD servers use a publisher/subscriber model with the 'topic' as the key The publisher class is linked to a single topic string.
`RtdServer`()	An RtdServer allows asynchronous interaction with Excel by posting update notifications which cause Excel to recalcate certain cells.
`xloil.rtd.subscribe`(server, topic, coro)	Subscribes to topic on the given RtdServer, starting the publishing task coro if no publisher for topic is currently running.
`xloil.rtd.RtdSimplePublisher`(topic)	Implements the boiler-plate involved in an RtdPublisher for the vanilla case.

class xloil.RtdPublisher

RTD servers use a publisher/subscriber model with the ‘topic’ as the key The publisher class is linked to a single topic string.

Typically the publisher will do nothing on construction, but when it detects a subscriber using the connect() method, it creates a background publishing task When disconnect() indicates there are no subscribers, it cancels this task with a call to stop()

If the task is slow to return or spin up, it could be started the constructor and kept it running permanently, regardless of subscribers.

The publisher should call RtdServer.publish() to push values to subscribers.

connect(num_subscribers: int) → None: Called by the RtdServer when a sheet function subscribes to this topic. Typically a topic will start up its publisher on the first subscriber, i.e. when num_subscribers == 1

disconnect(num_subscribers: int) → bool

Called by the RtdServer when a sheet function disconnects from this topic. This happens when the function arguments are changed the function deleted. Typically a topic will shutdown its publisher when num_subscribers == 0.

Whilst the topic remains live, it may still receive new connection requests, so generally avoid finalising in this method.

done() → bool: Returns True if the topic can safely be deleted without leaking resources.

stop() → None: Called by the RtdServer to indicate that a topic should shutdown and dependent threads or tasks and finalise resource usage

property topic: str

Returns the name of the topic

Type:: str

class xloil.RtdServer

An RtdServer allows asynchronous interaction with Excel by posting update notifications which cause Excel to recalcate certain cells. The python RtdServer object manages an RTD COM server with each new RtdServer creating an underlying COM server. The RtdServer works on a publisher/subscriber model with topics identified by a string.

A topic publisher is registered using start(). Subsequent calls to subscribe() will connect this topic and tell Excel that the current calling cell should be recalculated when a new value is published.

RTD sits outside of Excel’s normal calc cycle: publishers can publish new values at any time, triggering a re-calc of any cells containing subscribers. Note the re-calc will only happen ‘live’ if Excel’s caclulation mode is set to automatic

drop(arg0: str) → None: Drops the producer for a topic by calling RtdPublisher.stop(), then waits for it to complete and publishes #N/A to all subscribers.

peek(topic: str, converter: IPyFromExcel = None) → object

Looks up a value for a specified topic, but does not subscribe. If there is no active publisher for the topic, it returns None. If there is no published value, it will return CellError.NA.

This function does not use any Excel API and is safe to call at any time on any thread.

property progid: str

Type:: str

publish(topic: str, value: object, converter: IPyToExcel = None) → bool

Publishes a new value for the specified topic and updates all subscribers. This function can be called even if no RtdPublisher has been started.

This function does not use any Excel API and is safe to call at any time on any thread.

An Exception object can be passed at the value, this will trigger the debugging hook if it is set. The exception string and it’s traceback will be published.

start(topic: RtdPublisher) → None: Registers an RtdPublisher with this manager. The RtdPublisher receives notification when the number of subscribers changes

start_task(topic: str, func: object, converter: IPyToExcel = None) → None: Launch a publishing task for a topic given a func and a return converter. The function should take a single xloil.RtdReturn argument.

subscribe(topic: str, converter: IPyFromExcel = None) → object

Subscribes to the specified topic. If no publisher for the topic currently exists, it returns None, but the subscription is held open and will connect to a publisher created later. If there is no published value, it will return CellError.NA.

This calls Excel’s RTD function, which means the calling cell will be recalculated every time a new value is published.

Calling this function outside of a worksheet function called by Excel may produce undesired results and possibly crash Excel.

class xloil.rtd.RtdSimplePublisher(topic)

Implements the boiler-plate involved in an RtdPublisher for the vanilla case. Just need to implement an exception-safe async run_task which collects the data and publishes it to the RtdServer.

connect(num_subscribers): Called by the RtdServer when a sheet function subscribes to this topic. Typically a topic will start up its publisher on the first subscriber, i.e. when num_subscribers == 1

disconnect(num_subscribers)

Called by the RtdServer when a sheet function disconnects from this topic. This happens when the function arguments are changed the function deleted. Typically a topic will shutdown its publisher when num_subscribers == 0.

Whilst the topic remains live, it may still receive new connection requests, so generally avoid finalising in this method.

done(): Returns True if the topic can safely be deleted without leaking resources.

stop(): Called by the RtdServer to indicate that a topic should shutdown and dependent threads or tasks and finalise resource usage

topic()

Returns the name of the topic

Type:: str

xloil.rtd.subscribe(server: RtdServer, topic: str, coro)

Subscribes to topic on the given RtdServer, starting the publishing task coro if no publisher for topic is currently running.

coro must be a coroutine and the string topic must be derived from the function’s arguments in a way which uniquely identifies the data to be published

GUI Interaction 

`StatusBar`([timeout])	Displays status bar messages and clears the status bar (after an optional delay) on context exit.
`ExcelGUI`([name, ribbon, funcmap, connect])	An ExcelGUI wraps a COM addin which allows Ribbon customisation and creation of custom task panes.
`TaskPaneFrame`()	Manages Excel's underlying custom task pane object into which a python GUI can be drawn.
`RibbonControl`()	This object is passed to ribbon callback handlers to indicate which control raised the callback.
`xloil.gui.CustomTaskPane`()	Base class for custom task panes.
`xloil.gui.find_task_pane`([title, workbook, ...])	Finds xlOil python task panes attached to the specified window, with the given pane title.
`xloil.gui.qtpy.Qt_thread`([fn, discard])	All Qt GUI interactions (except signals) must take place on the thread on which the QApplication object was created.
`xloil.gui.qtpy.QtThreadTaskPane`(args, *kwargs)	Wraps a Qt QWidget to create a CustomTaskPane object.
`xloil.gui.tkinter.Tk_thread`([fn, discard])	All Tk GUI interactions must take place on the thread on which the root object was created.
`xloil.gui.tkinter.TkThreadTaskPane`(*args, ...)	Wraps a Tk window to create a CustomTaskPane object.

class xloil.StatusBar(timeout: int = 0)

Displays status bar messages and clears the status bar (after an optional delay) on context exit.

Examples

with StatusBar(1000) as status:
  status.msg('Doing slow thing')
  ...
  status.msg('Done slow thing')

msg(msg: str, timeout: int = 0) → None: Posts a status bar message, and if timeout is non-zero, clears if after the specified number of milliseconds

class xloil.ExcelGUI(name: object = None, ribbon: object = None, funcmap: object = None, connect: bool = True)

An ExcelGUI wraps a COM addin which allows Ribbon customisation and creation of custom task panes. The methods of this object are safe to call from any thread; however, since COM calls must be made on Excel’s main thread, the methods schedule those calls and return an awaitable future to the result. This could lead to deadlocks if the future’s result is requested synchronously and, for example, one of Excel’s event handlers is triggered. The object’s properties do not return futures and are thread-safe.

activate(id: str) → _Future: Activatives the ribbon tab with the specified id. Returns False if there is no Ribbon or the Ribbon is collapsed.

attach_pane(arg0: object, arg1: object, arg2: object, arg3: object, arg4: object) → object

Given task pane contents (which can be specified in several forms) this function creates a new task pane displaying those contents.

Returns the instance of CustomTaskPane. If one was passed as the ‘pane’ argument, that is returned, if a QWidget was passed, a QtThreadTaskPane is created.

Parameters:

pane: CustomTaskPane (or QWidget type): Can be an instance of CustomTaskPane, a type deriving from QWidget or an instance of a QWidget. If a QWidget instance is passed, it must have been created on the Qt thread.
name:: The task pane name. Will be displayed above the task pane. If not provided, the ‘name’ attribute of the task pane is used.
window:: A window title or xloil.ExcelWindow object to which the task pane should be attached. If None, the active window is used.
size:: If provided, a tuple (width, height) used to set the initial pane size
visible:: Determines the initial pane visibility. Defaults to True.

attach_pane_async(pane: object, name: object = None, window: object = None, size: object = None, visible: object = True) → object: Behaves as per attach_pane, but returns an asyncio coroutine. The pane argument may be an awaitable to a CustomTaskPane.

connect() → _Future

Connects the underlying COM addin to Excel, No other methods may be called on a ExcelGUI object until it has been connected.

This method is safe to call on an already-connected addin.

property connected: bool

True if the a connection to Excel has been made

Type:: bool

create_task_pane(name: object, creator: object, window: object = None, size: object = None, visible: object = True) → object: Deprecated: use attach_pane. Note that create_task_pane tries to find_task_pane before creation whereas attach_pane does not.

disconnect() → _Future: Unloads the underlying COM add-in and any ribbon customisation. Avoid using connect/disconnect to modify the Ribbon as it is not perfomant. Rather hide/show controls with invalidate and the vibility callback.

invalidate(id: str = '') → _Future

Invalidates the specified control: this clears the cache of responses to callbacks associated with the control. For example, this can be used to hide a control by forcing its getVisible callback to be invoked, rather than using the cached value.

If no control ID is specified, all controls are invalidated.

property name: str

The name displayed in Excel’s COM Addins window

Type:: str

class xloil.TaskPaneFrame

Manages Excel’s underlying custom task pane object into which a python GUI can be drawn. It is unlikely that this object will need to be manipulated directly. Rather use xloil.gui.CustomTaskPane which holds the python-side frame contents.

The methods of this object are safe to call from any thread. COM must be used on Excel’s main thread, so the methods all wrap their calls to ensure to this happens.

attach(handler: object, hwnd: int) → _Future: Associates a xloil.gui.CustomTaskPane with this frame. Returns a future with no result.

com_control(lib: str = '') → object: Gets the base COM control of the task pane. The lib used to provide COM support can be ‘comtypes’ or ‘win32com’ (default is win32com). This method is only useful if a custom progid was specified during the task pane creation.

property position: str

Gets/sets the dock position, one of: bottom, floating, left, right, top

Type:: str

property size: Tuple[int, int]

Gets/sets the task pane size as a tuple (width, height)

Type:: Tuple[int, int]

property title: str

Type:: str

property visible: bool

Determines the visibility of the task pane

Type:: bool

property window: ExcelWindow

Gives the window of the document window to which the frame is attached, can be used to uniquely identify the pane

Type:: ExcelWindow

class xloil.RibbonControl

This object is passed to ribbon callback handlers to indicate which control raised the callback.

property id: str

A string that represents the Id attribute for the control or custom menu item

Type:: str

property tag: str

A string that represents the Tag attribute for the control or custom menu item.

Type:: str

class xloil.gui.CustomTaskPane

Base class for custom task panes. xlOil provides two toolkit-specfic implementations: xloil.gui.pyqt5.QtThreadTaskPane (pyside is also supported) and xloil.gui.tkinter.TkThreadTaskPane.

Can be sub-classed to implement task panes with different GUI toolkits.

Subclasses can implement functions to recieve events:

on_visible(self, value):
Called when the visible state changes, value contains the new state. It is not necessary to override this to control pane visibility - the window will be shown/hidden automatically

on_docked(self):
Called when the pane is docked to a new location or undocked

on_destroy(): Called before the pane is destroyed to release any resources

property pane: TaskPaneFrame: Returns the TaskPaneFrame: a reference to the window holding the python GUI

property position: str: Returns the docking position: one of bottom, floating, left, right, top

property size: Tuple[int, int]: Returns a tuple (width, height)

property visible: bool: Returns True if the pane is currently shown

xloil.gui.find_task_pane(title: str = None, workbook=None, window=None) → CustomTaskPane

Finds xlOil python task panes attached to the specified window, with the given pane title. The primary use of this function is to look for an existing task pane before creating a new one.

Parameters:

title:: if title is specified, returns a (case-sensitive) match of a single xloil.CustomTaskPane object or None if not found. Otherwise returns a list of xloil.CustomTaskPane objects.
window: str or `xloil.ExcelWindow`: The window title to be searched
workbook: str or `xloil.Workbook`:: Task panes are linked to Excel’s Window objects which can have a many-to-one relationship with workbooks. If a workbook is specified, all task panes associated with that workbook will be searched.

You must import this module before any other mention of Qt or Pyside: this allows xlOil to create a thread to manage the Qt GUI and the Qt app object. All interaction with the Qt must be done on that thread or crashes will ensue. Use Qt_thread.submit(…) or the @Qt_thread to ensure functions are run on the thread.

class xloil.gui.qtpy.QtExecutor

property app: A reference to the singleton QApplication object

map(fn, *iterables, timeout=None, chunksize=1)

Returns an iterator equivalent to map(fn, iter).

Args:

fn: A callable that will take as many arguments as there are: passed iterables.
timeout: The maximum number of seconds to wait. If None, then there: is no limit on the wait time.
chunksize: The size of the chunks the iterable will be broken into: before being passed to a child process. This argument is only used by ProcessPoolExecutor; it is ignored by ThreadPoolExecutor.

Returns:

An iterator equivalent to: map(func, *iterables) but the calls may be evaluated out-of-order.

Raises:

TimeoutError: If the entire result iterator could not be generated: before the given timeout.

Exception: If fn(*args) raises for any values.

shutdown(wait=True, cancel_futures=False)

Clean-up the resources associated with the Executor.

It is safe to call this method several times. Otherwise, no other methods can be called after this one.

Args:

wait: If True then shutdown will not return until all running: futures have finished executing and the resources used by the executor have been reclaimed.
cancel_futures: If True then shutdown will cancel all pending: futures. Futures that are completed or running will not be cancelled.

submit(fn, *args, **kwargs): Schedules the callable, fn, to be executed as fn(*args, **kwargs) and returns a Future object representing the execution of the callable.

async submit_async(fn, *args, **kwargs): Behaves as submit but wraps the result in an asyncio.Future so it can be awaited.

class xloil.gui.qtpy.QtThreadTaskPane(*args, **kwargs)

Wraps a Qt QWidget to create a CustomTaskPane object. The optional widget argument must be a type deriving from QWidget or an instance of such a type (a lambda which returns a QWidget is also acceptable).

on_destroy(): Called before the pane is destroyed to release any resources

property pane: TaskPaneFrame: Returns the TaskPaneFrame: a reference to the window holding the python GUI

property position: str: Returns the docking position: one of bottom, floating, left, right, top

property size: Tuple[int, int]: Returns a tuple (width, height)

property visible: bool: Returns True if the pane is currently shown

property widget: This returns the QWidget which is root of the the pane’s contents. If the class was constructed from a QWidget, this is that widget.

xloil.gui.qtpy.Qt_thread(fn=None, discard=False) → QtExecutor

All Qt GUI interactions (except signals) must take place on the thread on which the QApplication object was created. This object returns a concurrent.futures.Executor which creates the QApplication object and can run commands on a dedicated Qt thread. It can also be used a decorator.

All QT interaction must take place via this thread.

Examples

future = Qt_thread().submit(my_func, my_args)
future.result() # blocks

@Qt_thread(discard=True)
def myfunc():
    # This is run on the Qt thread and returns a *future* to the result.
    # By specifying `discard=True` we tell xlOil that we're not going to
    # keep track of that future and so it should log any exceptions.
    ... 

You must import this module before any other mention of tkinter: this allows xlOil to create a thread to manage the Tk GUI and the Tk root object. All interaction with the Tk must be done on that thread or crashes will ensue. Use Tk_thread.submit(…) or the @Tk_thread to ensure functions are run on the thread.

class xloil.gui.tkinter.TkExecutor

map(fn, *iterables, timeout=None, chunksize=1)

Returns an iterator equivalent to map(fn, iter).

Args:

fn: A callable that will take as many arguments as there are: passed iterables.
timeout: The maximum number of seconds to wait. If None, then there: is no limit on the wait time.
chunksize: The size of the chunks the iterable will be broken into: before being passed to a child process. This argument is only used by ProcessPoolExecutor; it is ignored by ThreadPoolExecutor.

Returns:

An iterator equivalent to: map(func, *iterables) but the calls may be evaluated out-of-order.

Raises:

TimeoutError: If the entire result iterator could not be generated: before the given timeout.

Exception: If fn(*args) raises for any values.

shutdown(wait=True, cancel_futures=False)

Clean-up the resources associated with the Executor.

It is safe to call this method several times. Otherwise, no other methods can be called after this one.

Args:

wait: If True then shutdown will not return until all running: futures have finished executing and the resources used by the executor have been reclaimed.
cancel_futures: If True then shutdown will cancel all pending: futures. Futures that are completed or running will not be cancelled.

submit(fn, *args, **kwargs) → Future: Schedules the callable, fn, to be executed as fn(*args, **kwargs) and returns a Future object representing the execution of the callable.

async submit_async(fn, *args, **kwargs): Behaves as submit but wraps the result in an asyncio.Future so it can be awaited.

class xloil.gui.tkinter.TkThreadTaskPane(*args, **kwargs)

Wraps a Tk window to create a CustomTaskPane object.

on_destroy(): Called before the pane is destroyed to release any resources

property pane: TaskPaneFrame: Returns the TaskPaneFrame: a reference to the window holding the python GUI

property position: str: Returns the docking position: one of bottom, floating, left, right, top

property size: Tuple[int, int]: Returns a tuple (width, height)

property top_level: Toplevel: This returns a tkinter.Toplevel window into which the pane’s contents should be placed.

property visible: bool: Returns True if the pane is currently shown

xloil.gui.tkinter.Tk_thread(fn=None, discard=False) → TkExecutor

All Tk GUI interactions must take place on the thread on which the root object was created. This function returns a concurrent.futures.Executor which creates the root object and can run commands on the dedicated Tk thread. It can also be used a decorator.

All Tk interaction must take place via this thread.

Examples

future = Tk_thread().submit(my_func, my_args)
future.result() # blocks

@Tk_thread(discard=True)
def myfunc():
    # This is run on the Tk thread and returns a *future* to the result.
    # By specifying `discard=True` we tell xlOil that we're not going to
    # keep track of that future and so it should log any exceptions.
    ... 

Events 

A module containing event objects which can be hooked to receive events driven by Excel’s UI. The events correspond to COM/VBA events and are described in detail in the Excel Application API. The naming convention (including case) of the VBA events has been preserved for ease of search.

See Introduction and Excel.Application

Using the Event Class 

Events are hooked using +=, e.g.
event.NewWorkbook += lambda wb: print(wb_name)
Events are unhooked using -= and passing a reference to the handler function
event.NewWorkbook += foo
event.NewWorkbook -= foo
You should not return anything from an event handler

Each event has a handlers property listing all currently hooked handlers
Where an event has reference parameter, for example the cancel bool in WorkbookBeforePrint, you need to set the value using cancel.value=True. This is because python does not support reference parameters for primitive types.
def no_printing(wbName, cancel):
  cancel.value = True
xlo.event.WorkbookBeforePrint += no_printing
Workbook and worksheet names are passed a string, Ranges as passed as a xloil.Range

Python-only Events 

These events are specific to python and not noted in the Core documentation:

PyBye:
Fired just before xlOil finalises its embedded python interpreter. All python and xlOil functionality is still available. This event is useful to stop threads as it is called before threading module teardown, whereas python’s atexit is called afterwards. Has no parameters.

UserException:
Fired when an exception is raised in a user-supplied python callback, for example a GUI callback or an RTD publisher. Has no parameters.

file_change:
This is a special parameterised event, see the separate documentation for this function.

Examples 

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

xlo.event.WorkbookNewSheet += greet
...
xlo.event.WorkbookNewSheet -= greet

print(xlo.event.WorkbookNewSheet.handlers) # Should be empty

def click_handler(sheet_name, target, cancel):
    xlo.worksheets[sheet_name]['A5'].value = target.address()

xlo.event.SheetBeforeDoubleClick += click_handler

class xloil.event.Event

add(handler: object) → Event: Registers an event handler callback with this event, equivalent to event += handler

clear() → None: Removes all handlers from this event

property handlers: tuple

The tuple of handlers registered for this event. Read-only.

Type:: tuple

remove(handler: object) → Event: Deregisters an event handler callback with this event, equivalent to event -= handler

xloil.event.pause() → None: Pauses Excel’s event handling. Equivalent to VBA’s Application.EnableEvents = False or xlo.app().enable_events = False

xloil.event.allow() → None: Resumes Excel’s event handling after a pause. Equivalent to VBA’s Application.EnableEvents = True or xlo.app().enable_events = True

xloil.event.AfterCalculate: Event = None

xloil.event.WorkbookOpen: Event = None

xloil.event.NewWorkbook: Event = None

xloil.event.SheetSelectionChange: Event = None

xloil.event.SheetBeforeDoubleClick: Event = None

xloil.event.SheetBeforeRightClick: Event = None

xloil.event.SheetActivate: Event = None

xloil.event.SheetDeactivate: Event = None

xloil.event.SheetCalculate: Event = None

xloil.event.SheetChange: Event = None

xloil.event.WorkbookAfterClose: Event = None

xloil.event.WorkbookRename: Event = None

xloil.event.WorkbookActivate: Event = None

xloil.event.WorkbookDeactivate: Event = None

xloil.event.WorkbookBeforeClose: Event = None

xloil.event.WorkbookBeforeSave: Event = None

xloil.event.WorkbookAfterSave: Event = None

xloil.event.WorkbookBeforePrint: Event = None

xloil.event.WorkbookNewSheet: Event = None

xloil.event.WorkbookAddinInstall: Event = None

xloil.event.WorkbookAddinUninstall: Event = None

xloil.event.XllAdd: Event = None

xloil.event.XllRemove: Event = None

xloil.event.ComAddinsUpdate: Event = None

xloil.event.PyBye: Event = None

xloil.event.UserException: Event = None

Everything else 

`in_wizard`()	Returns true if the function is being invoked from the function wizard : costly functions should exit in this case to maintain UI responsiveness.
`get_async_loop`()	Returns the asyncio event loop associated with the async background worker thread.
`get_event_loop`()	Returns the background asyncio event loop used to load the current add-in.
`from_excel_date`(arg0)	Identical to xloil.to_datetime.
`date_formats`
`linked_workbook`()	Returns the full path of the workbook linked to the calling module or None if the module was not loaded with an associated workbook.
`source_addin`()	Returns the full path of the source add-in (XLL file) associated with the current code.
`excel_state`()	Gives information about the Excel application, in particular the handles required to interact with Excel via the Win32 API.
`run`(func, *args)	Calls VBA's Application.Run taking the function name and up to 30 arguments.
`run_async`(func, *args)	Calls VBA's Application.Run taking the function name and up to 30 arguments.
`call`(func, *args)	Calls a built-in worksheet function or command or a user-defined function with the given name.
`call_async`(func, *args)	Calls a built-in worksheet function or command or a user-defined function with the given name.
`excel_callback`(func[, wait, retry, api])	Schedules a callback to be run in the main thread.
`cache`
`Addin`()
`source_addin`()	Returns the full path of the source add-in (XLL file) associated with the current code.
`xloil_addins`
`core_addin`()
`xloil._core._AddinsDict`()	A dictionary of all addins using the xlOil_Python plugin keyed by the addin pathname.
`xloil._core._DateFormatList`(**kwds)	Registers date time formats to try when parsing strings to dates.
`xloil._core._LogWriter`()	Writes a log message to xlOil's log.
`xloil.logging.log`	An instance of xloil._LogWriter which writes a log message to xlOil's log.
`xloil.debug.use_debugger`(debugger, **kwargs)	Selects a debugger for exceptions in user code.

class xloil.ObjectCache

Provides a way to manipulate xlOil’s Python object cache

Examples

@xlo.func
def myfunc(x):
    return xlo.cache(MyObject(x)) # <-equivalent to cache.add(...)

@xlo.func
def myfunc2(array: xlo.Array(str), i):
    return xlo.cache[array[i]]   # <-equivalent to cache.get(...)

add(obj: object, tag: str = '', key: str = '') → object

Adds an object to the cache and returns a reference string.

xlOil automatically adds objects returned from worksheet functions to the cache if they cannot be converted by any registered converter. So this function is useful to:

force a convertible object, such as an iterable, into the cache

return a list of cached objects

create cached objects from outside of worksheet fnctions e.g. in commands / subroutines

xlOil uses the caller infomation provided by Excel to construct the cache string and manage the cache object lifecycle. When invoked from a worksheet function, this caller info contains the cell reference. xlOil deletes cache objects linked to the cell reference from previous calculation cycles.

When invoked from a source other than a worksheet function (there are several possibilies, see the help for xlfCaller), xlOil again generates a reference string based on the caller info. However, this may not be unique. In addition, objects with the same caller string will replace those created during a previous calculation cycle. For example, creating cache objects from a button clicked repeatedly will behave differently if Excel recalculates in between the clicks. To override this behaviour, the exact cache key can be specified. For example, use Python’s id function or the cell address being written to if a command is writing a cache string to the sheet. When key is specified the user is responsible for managing the lifecycle of their cache objects.

Parameters:

obj:: The object to cache. Required.
tag: str: An optional string to append to the cache ref to make it more ‘friendly’. When returning python objects from functions, xlOil uses the object’s type name as a tag
key: str: If specified, use the exact cache key (after prepending by cache uniquifier). The user is responsible for ensuring uniqueness of the cache key.

contains(ref: str) → bool: Returns True if the given reference string links to a valid object

get(ref: str, default: object = None) → object: Fetches an object from the cache given a reference string. Returns default if not found

keys() → list: Returns all cache keys as a list of strings

remove(ref: str) → bool: xlOil manages the lifecycle for most cache objects, so this function should only be called when add was invoked with a specified key - in this case the user owns the lifecycle management.

xloil.cache = ObjectCache

xloil.call(func: object, *args) → object

Calls a built-in worksheet function or command or a user-defined function with the given name. The name is case-insensitive; built-in functions take priority in a name clash.

The type and order of arguments expected depends on the function being called.

func can be built-in function number (as an int) which slightly reduces the lookup overhead

This function must be called from a non-local worksheet function on the main thread.

call can also invoke old-style macro sheet commands

xloil.call_async(func: object, *args) → _ExcelObjFuture

Calls a built-in worksheet function or command or a user-defined function with the given name. See xloil.call.

Calls to the Excel API must be done on Excel’s main thread: this async function can be called from any thread but will require the main thread to be available to return a result.

Returns an awaitable, i.e. a future which holds the result.

xloil.core_addin() → Addin

xloil.excel_callback(func: object, wait: int = 0, retry: int = 500, api: str = '') → _PyObjectFuture

Schedules a callback to be run in the main thread. Much of the COM API in unavailable during the calc cycle, in particular anything which involves writing to the sheet. Returns a future which can be awaited.

Parameters:

func: callable
A callable which takes no arguments and returns nothing
retryint
Millisecond delay between retries if Excel’s COM API is busy, e.g. a dialog box
is open or it is running a calc cycle.If zero, does no retry
waitint
Number of milliseconds to wait before first attempting to run this function
apistr
Specify ‘xll’ or ‘com’ or both to indicate which APIs the call requires.
The default is ‘com’: ‘xll’ would only be required in rare cases.

xloil.excel_state() → ExcelState: Gives information about the Excel application, in particular the handles required to interact with Excel via the Win32 API. Only available when xlOil is loaded as an addin.

xloil.from_excel_date(arg0: object) → object: Identical to xloil.to_datetime.

xloil.get_async_loop() → object: Returns the asyncio event loop associated with the async background worker thread. All async / RTD worksheet functions are executed on this event loop.

xloil.get_event_loop(): Returns the background asyncio event loop used to load the current add-in. Unless specified in the settings, all add-ins are loaded in the same thread and event loop.

xloil.in_wizard() → bool: Returns true if the function is being invoked from the function wizard : costly functions should exit in this case to maintain UI responsiveness. Checking for the wizard is itself not cheap, so use this sparingly.

xloil.linked_workbook() → str: Returns the full path of the workbook linked to the calling module or None if the module was not loaded with an associated workbook.

xloil.run(func: object, *args) → object

Calls VBA’s Application.Run taking the function name and up to 30 arguments. This can call any user-defined function or macro but not built-in functions.

The type and order of arguments expected depends on the function being called.

Must be called on Excel’s main thread, for example in worksheet function or command.

xloil.run_async(func: object, *args) → _ExcelObjFuture

Calls VBA’s Application.Run taking the function name and up to 30 arguments. This can call any user-defined function or macro but not built-in functions.

Calls to the Excel API must be done on Excel’s main thread: this async function can be called from any thread but will require the main thread to be available to return a result.

Returns an awaitable, i.e. a future which holds the result.

xloil.source_addin() → Addin: Returns the full path of the source add-in (XLL file) associated with the current code. That is the add-in which has caused the current code to be executed

xloil.source_addin() → Addin: Returns the full path of the source add-in (XLL file) associated with the current code. That is the add-in which has caused the current code to be executed

xloil.date_formats = _DateFormatList

class xloil.ExcelState

Gives information about the Excel application. Cannot be constructed: call xloil.excel_state to get an instance.

property hinstance: int

Excel Win32 HINSTANCE pointer as an int

Type:: int

property hwnd: int

Excel Win32 main window handle as an int

Type:: int

property main_thread_id: int

Excel main thread ID

Type:: int

property version: int

Excel major version

Type:: int

class xloil.Addin

property async_slice: int

Sets/gets the time slice in milliseconds for which the asyncio event loop is allowed to run before being interrupted. The event loop holds the GIL while it is running, so making this interval too long could impact the performance of other python functions.

Type:: int

property async_throttle: int

Sets/gets the interval in milliseconds between switches to the asyncio event loop embedded in this addin. The event loop holds the GIL while it is running, so making this interval too short could impact the performance of other python functions.

Type:: int

property event_loop: object

The asyncio event loop used for background tasks by this addin

Type:: object

functions() → List[_FuncSpec]: Returns a list of all functions declared by this addin.

property pathname: str

Type:: str

property settings: object

Gives access to the settings in the addin’s ini file as nested dictionaries. These are the settings on load and do not allow for modifications made in the ribbon toolbar.

Type:: object

property settings_file: str

The full pathname of the settings ini file used by this addin

Type:: str

xloil.xloil_addins = _AddinsDict

class xloil._core._AddinsDict: A dictionary of all addins using the xlOil_Python plugin keyed by the addin pathname.

class xloil._core._DateFormatList(**kwds)

Registers date time formats to try when parsing strings to dates. See std::get_time for format syntax.

append(x: str) → None: Add an item to the end of the list

clear() → None: Clear the contents

count(x: str) → int: Return the number of times x appears in the list

extend(**kwds): Helper for @overload to raise when called.

insert(i: int, x: str) → None: Insert an item at a given position.

pop(**kwds): Helper for @overload to raise when called.

remove(x: str) → None: Remove the first item from the list whose value is x. It is an error if there is no such item.

class xloil._core._LogWriter

Writes a log message to xlOil’s log. The level parameter can be a level constant from the logging module or one of the strings error, warn, info, debug or trace.

Only messages with a level higher than the xlOil log level which is initially set to the value in the xlOil settings will be output to the log file. Trace output can only be seen with a debug build of xlOil.

debug(msg: object, *args) → None: Writes a log message at the ‘debug’ level

error(msg: object, *args) → None: Writes a log message at the ‘error’ level

flush() → None: Forces a log file ‘flush’, i.e write pending log messages to the log file. For performance reasons the file is not by default flushed for every message.

property flush_on: str

Returns or sets the log level which will trigger a ‘flush’, i.e a writing pending log messages to the log file.

Type:: str

info(msg: object, *args) → None: Writes a log message at the ‘info’ level

property level: str

Returns or sets the current log level. The returned value will always be an integer corresponding to levels in the logging module. The level can be set to an integer or one of the strings error, warn, info, debug or trace.

Type:: str

property level_int: int

Returns the log level as an integer corresponding to levels in the logging module. Useful if you want to condition some output based on being above a certain log level.

Type:: int

property levels: List[str]

A list of the available log levels

Type:: List[str]

property path: str

The full pathname of the log file

Type:: str

trace(msg: object, *args) → None: Writes a log message at the ‘trace’ level

warn(msg: object, *args) → None: Writes a log message at the ‘warn’ level

xloil.logging.log(msg: object, *args, **kwargs) → None = <xloil._LogWriter object>

An instance of xloil._LogWriter which writes a log message to xlOil’s log. The level parameter can be a integer constant from the logging module or one of the strings error, warn, info, debug or trace.

Only messages with a level higher than the xlOil log level which is (initially) set in the xlOil settings file will be output to the log file. Trace output can only be seen with a debug build of xlOil.

xloil.logging.log_except(msg, level='error'): Logs ‘{msg}: {stack trace}’ with a default level of ‘error’

xloil.debug.use_debugger(debugger, **kwargs)

Selects a debugger for exceptions in user code. Only effects exceptions which are handled by xlOil. Choices are:

pdb

opens a console window with pdb active at the exception point

vscode or debugpy

listens

None

Turns off exception debugging (does not turn off debugpy)

External libraries 

`insert_cell_image`(writer[, size, pos, ...])	Inserts an image associated with the calling cell.
`xloil.matplotlib.ReturnFigure`(args, *kwargs)	Inserts a plot as an image associated with the calling cell.

xloil.insert_cell_image(writer: object, size: object = None, pos: object = None, origin: object = None, compress: bool = True) → str

Inserts an image associated with the calling cell. A second call to this function removes any image previously inserted from the same calling cell.

Parameters:

writer:

a one-arg function which writes the image to a provided filename. The file format must be one that Excel can open.

size:

A tuple (width, height) in points.
“cell” to fit to the caller size
“img” or None to keep the original image size

pos:

A tuple (X, Y) in points. The origin is determined by the origin argument

origin:

“top” or None: the top left of the calling range
“sheet”: the top left of the sheet
“bottom”: the bottom right of the calling range

compress:

if True, compresses the resulting image before storing in the sheet

class xloil.matplotlib.ReturnFigure(*args, **kwargs)

Inserts a plot as an image associated with the calling cell. A second call removes any image previously inserted by the same calling cell.

Parameters:

size:

A tuple (width, height) in points.
“cell” to fit to the caller size
“img” or None to keep the original image size

pos:

A tuple (X, Y) in points. The origin is determined by the origin argument

origin:

“top” or None: the top left of the calling range
“sheet”: the top left of the sheet
“bottom”: the bottom right of the calling range