xlOil Python Module Reference¶

Declaring Worksheet Functions
Excel Object Model
RTD Functions
GUI Interaction
Everything else
External libraries

`Arg`(name[, help, typeof, default, is_keywords])	Holds the description of a function argument.
`CannotConvert`	Should be thrown by a converter when it is unable to handle the provided type.
`CellError`()	Enum-type class which represents an Excel error condition of the form #N/A!, #NAME!, etc passed as a function argument.
`Event`()	Contains hooks for events driven by user interaction with Excel.
`ExcelArray`()	A view of a internal Excel array which can be manipulated without copying the underlying data.
`AllowRange`	The central part of internal API.
`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.
`Cache`	Using -> xloil.Cache in a function declaration to force the output to be placed in the python object cache rather than attempting a conversion
`SingleValue`	Use -> xloil.SingleValue in a function declaration to force the output to be a single cell value.
`RibbonControl`()	This object is passed to ribbon callback handlers to indicate which control raised the callback.
`func`([fn, name, help, args, group, local, ...])	Decorator which tells xlOil to register the function (or callable) in Excel.
`converter`([target, range, register, direction])	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")
`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.
`log`	Writes a log message to xlOil's log.
`LogWriter`()	Writes a log message to xlOil's log.
`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`(value)	Tries to the convert a given number to a dt.date or dt.datetime assuming it is an Excel date serial number.
`register_functions`(funcs[, module, append])	Registers the provided callables and associates them with the given modeule
`deregister_functions`(module, function_names)	Deregisters worksheet functions linked to specified module.
`linked_workbook`([mod])	Returns the full path of the workbook linked to the specified module or None if the module was not loaded with an associated workbook.
`source_addin`([mod])	Returns the full path of the source add-in (XLL file) assoicated with the current code.
`excel_run`(func[, num_retries, retry_delay, ...])	Schedules a callback to be run in the COM API context.
`excel_state`()	Gives information about the Excel application, in particular the handles required to interact with Excel via the Win32 API.
`excel_func`(func, *args)	Similar to VBA's Application.Run.
`excel_func_async`(func, *args)	Similar to VBA's Application.Run.
`app`([lib])	Returns a handle to the Excel.Application object.
`Range`()	Similar to the `Excel.Range <https://docs.microsoft.com/en-us/office/vba/api/excel.range(object)> object, this class allows direct access to an area on a worksheet.
`Worksheet`()
`Workbook`()
`ExcelWindow`()
`windows`	An interable collection of workbooks, windows, sheets, etc.
`workbooks`	An interable collection of workbooks, windows, sheets, etc.
`active_worksheet`()	Returns the currently active worksheet
`active_workbook`()	Returns the currently active workbook
`ExcelGUI`()	Controls an Ribbon and it's associated COM addin.
`CustomTaskPane`(pane)	Base class for custom task panes.
`TaskPaneFrame`()	References Excel's base task pane object into which the python GUI can be drawn.
`find_task_pane`([title, workbook, window])	Finds all xlOil python task panes associated with the active window, optionally filtering by the pane title.
`create_task_pane`(name[, creator, window, ...])	Returns a task pane with title <name> attached to the active window, creating it if it does not already exist.
`insert_cell_image`(writer[, size, pos, ...])	Inserts an image associated with the calling cell.
`xloil.rtd.RtdSimplePublisher`(topic)	Implements the boiler-plate involved in an RtdPublisher for the vanilla case.
`xloil.debug.exception_debug`(debugger)	Selects a debugger for exceptions in user code.
`xloil.qtgui.Qt_thread`()	All Qt GUI interactions (except signals) must take place on the thread that the QApplication object was created on.
`xloil.pandas.PDFrame`(args, *kwargs)	Converter which takes tables with horizontal records to pandas dataframes.
`xloil.pillow.ReturnImage`(args, *kwargs)	Inserts an image associated with the calling cell.

Declaring Worksheet Functions ¶

class xloil.Arg(name, help='', typeof=None, default=None, is_keywords=False)¶

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

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)¶

write_spec(this_arg)¶

xloil.Cache¶: Using -> xloil.Cache in a function declaration to force the output to be placed in the python object cache rather than attempting a conversion

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¶

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 an object of this type, which it can handle based on the error condition.

Div0 = None¶

GettingData = None¶

NA = None¶

Name = None¶

Null = None¶

Num = None¶

Ref = None¶

Value = None¶

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¶: Property which gives the dimension of the array: 1 or 2

property ncols¶: Returns the number of columns in the array

property nrows¶: Returns the number of rows in the array

to_numpy(dtype=None, dims=2)¶: Converts the array to a numpy array. If dtype is None, attempts to discover one, otherwise raises an exception if values cannot be converted to the specified dtype. dims can be 1 or 2

xloil.SingleValue¶: Use -> xloil.SingleValue in a function declaration to force the output to be a single cell value. Uses the Excel object cache for returned arrays and the Python object cache for unconvertable objects

xloil.converter(target=typing.Callable, range=False, register=False, direction='read')¶

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 not True, registers the converter as a handler for target, 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

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(module, function_names)¶: 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=False, threaded=False, volatile=False, is_async=False, register=True)¶

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

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.

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 in 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 Type. This grants the function extra priveledges, such as the ability to see un-calced cells and call the full range of Excel.Application functions. Functions which will be invoked as Excel macros, i.e. not functions called from a cell, should be declared with this attribute.
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.

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

Registers the provided callables and associates them with the given modeule

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 be 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.AllowRange(*args, **kwargs)¶

The central part of internal API.

This represents a generic version of type ‘origin’ with type arguments ‘params’. There are two kind of these aliases: user defined and special. The special ones are wrappers around builtin collections and ABCs in collections.abc. These must have ‘name’ always set. If ‘inst’ is False, then the alias can’t be instantiated, this is used by e.g. typing.List and typing.Dict.

alias of Union[bool, int, str, float, numpy.ndarray, dict, list, xloil._core.CellError, xloil._core.Range]

Excel Object Model ¶

xloil.windows = <xloil._core._Collection object>¶: An interable collection of workbooks, windows, sheets, etc.

xloil.workbooks = <xloil._core._Collection object>¶: An interable collection of workbooks, windows, sheets, etc.

xloil.app(lib=None)¶

Returns a handle to the Excel.Application object. This object is the root of Excel’s COM interface and supports a wide range of operations; it will be familiar to VBA programmers. It is well documented by Microsoft, see https://docs.microsoft.com/en-us/visualstudio/vsto/excel-object-model-overview and https://docs.microsoft.com/en-us/office/vba/api/excel.application(object).

Properties and methods of the app() object are resolved dynamically at runtime so cannot be seen by python inspection/completion tools. xlOil uses a 3rd party library to do this resolution and handle all interation with the Excel.Application object. The lib defaults to comtypes but win32com can be choosen.

Many operations using the Application object will only work in functions declared as macro type.

Examples

To get the name of the active worksheet:

@func(macro=True)
def sheetName():
    return xlo.app().ActiveWorksheet.Name

xloil.active_worksheet() → xloil._core.Worksheet¶: Returns the currently active worksheet

xloil.active_workbook() → xloil._core.Workbook¶: Returns the currently active workbook

class xloil.Range¶

Similar to the Excel.Range <https://docs.microsoft.com/en-us/office/vba/api/excel.range(object)> object, this class allows direct access to an area on a worksheet. It uses similar syntax to Excel’s object, supporting the ``cell` and range functions, however they 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

address(local=False)¶: Gets the range address in A1 format. The local parameter specifies whether the workbook and sheet name should be included. For example local=True gives “[Book1]Sheet1!F37” and local=False returns “F37”.

cell(row, col)¶: Returns a Range object which consists of the single cell specified. Note the indices are zero-based from the top left of the parent range.

clear()¶: Sets all values in the range to the Nil/Empty type

property name: str¶: A name for the object. e.g. range address, window caption, workbook name

property ncols¶: Returns the number of columns in the range

property nrows¶: Returns the number of rows in the range

range(from_row, from_col, num_rows=None, num_cols=None, to_row=None, to_col=None)¶

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(val)¶: 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.

to_com()¶: 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 ‘comtypes’ unless changed in the XLL’s ini file.

property value¶

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 assignemnt, 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.

class xloil.Workbook¶

property name: str¶: A name for the object. e.g. range address, window caption, workbook name

property path: str¶: The full path to the workbook, including the filename

to_com()¶: 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 ‘comtypes’ unless changed in the XLL’s ini file.

property windows: list¶: The workbook being displayed by this window

worksheet(name) → xloil._core.Worksheet¶: Returns the named worksheet which is part of this workbook (if it exists)

property worksheets: list¶: A list of all worksheets which are part of this workbook

class xloil.Worksheet¶

activate()¶

at_address(address) → xloil._core.Range¶: Returns the range specified by the local address, e.g. at_address('B3')

calculate()¶

property name: str¶: A name for the object. e.g. range address, window caption, workbook name

property parent¶: Returns the parent Workbook for this Worksheet

range(from_row, from_col, num_rows=None, num_cols=None, to_row=None, to_col=None) → xloil._core.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()¶: 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 ‘comtypes’ unless changed in the XLL’s ini file.

class xloil.ExcelWindow¶

property hwnd: int¶: The Win32 API window handle as an integer

property name: str¶: A name for the object. e.g. range address, window caption, workbook name

to_com()¶: 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 ‘comtypes’ unless changed in the XLL’s ini file.

property workbook: xloil._core.Workbook¶: The workbook being displayed by this window

RTD Functions ¶

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.

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

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

GUI Interaction ¶

class xloil.ExcelGUI¶: Controls an Ribbon and it’s associated COM addin. 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. This could lead to deadlocks if the call triggers event handlers on the main thread, which in turn block waiting for the thread originally calling ExcelUI.

class xloil.CustomTaskPane(pane: xloil._core.TaskPaneFrame)¶: Base class for custom task panes. Can be sub-classed to implement task panes with different GUI toolkits. Subclasses should implement at least the on_visible and on_size events.

class xloil.TaskPaneFrame¶: References Excel’s base task pane object into which the python GUI can be drawn. 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. This could lead to deadlocks if the call triggers event handlers on the main thread, which in turn block waiting for the thread originally calling TaskPaneFrame.

class xloil.RibbonControl¶: This object is passed to ribbon callback handlers to indicate which control raised the callback.

xloil.find_task_pane(title: Optional[str] = None, workbook=None, window=None)¶

Finds all xlOil python task panes associated with the active window, optionally filtering by the pane title.

This primary use of this function is to look for an existing task pane before creating a new one.

Task panes are linked to Excel’s Window objects which can have a many-to-one relationship with workbooks. If a workbook name is specified, all task panes associated with that workbook will be searched.

Returns: if title is specified, returns a (case-sensitive) match of a single xloil.CustomTaskPane object or None, otherwise returns a list of xloil.CustomTaskPane objects.

async xloil.create_task_pane(name: str, creator=None, window=None, gui: Optional[xloil._core.ExcelGUI] = None, size=None, visible=True)¶

Returns a task pane with title <name> attached to the active window, creating it if it does not already exist. This function is equivalent to ExcelGUI.create_task_pane(…)

Parameters

creator:

a subclass of QWidget or
a function which takes a TaskPaneFrame and returns a CustomTaskPane

window:

a window title or ExcelWindow object to which the task pane should be attached. If None, the active window is used.

gui: `ExcelGUI` object

GUI context used when creating a pane.

class xloil.qtgui.QtThreadTaskPane(pane, draw_widget)¶

Wraps a Qt QWidget to create a CustomTaskPane object.

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

on_size(width, height)¶: Called when the task pane is resized

on_visible(c)¶: Called when the visible state changes, passes the new state

xloil.qtgui.Qt_thread() → concurrent.futures._base.Executor¶

All Qt GUI interactions (except signals) must take place on the thread that the QApplication object was created on. This object is a concurrent.futures.Executor which executes commands on the dedicated Qt thread. All Qt interaction must take place via this thread.

Examples

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

Everything else ¶

class xloil.Event¶

Contains hooks for events driven by user interaction with Excel. The events correspond to COM/VBA events and are described in detail at Excel.Application

Notes:

The CalcCancelled and WorkbookAfterClose event are not part of the
Application object, see their individual documentation.
Where an event has reference parameter, for example the cancel bool in
WorkbookBeforeSave, you need to set the value using cancel.value=True. This is because python does not support reference parameters for primitive types.

Examples

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

xlo.event.WorkbookNewSheet += greet

AfterCalculate = <xloil._core._Event object>¶: Called after a calculation whether or not it completed or was interrupted

CalcCancelled = <xloil._core._Event object>¶: Called when the user interrupts calculation by interacting with Excel.

NewWorkbook = <xloil._core._Event object>¶

PyBye = <xloil._core._Event object>¶: Called just before xlOil finalises the 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 atexit is not.

SheetActivate = <xloil._core._Event object>¶

SheetBeforeDoubleClick = <xloil._core._Event object>¶

SheetBeforeRightClick = <xloil._core._Event object>¶

SheetCalculate = <xloil._core._Event object>¶

SheetChange = <xloil._core._Event object>¶

SheetDeactivate = <xloil._core._Event object>¶

SheetSelectionChange = <xloil._core._Event object>¶

WorkbookActivate = <xloil._core._Event object>¶

WorkbookAddinInstall = <xloil._core._Event object>¶

WorkbookAddinUninstall = <xloil._core._Event object>¶

WorkbookAfterClose = <xloil._core._Event object>¶

Excel’s event WorkbookBeforeClose, is cancellable by the user so it is not possible to know if the workbook actually closed. When xlOil calls WorkbookAfterClose, the workbook is certainly closed, but it may be some time since that closure happened.

The event is not called for each workbook when xlOil exits.

WorkbookBeforePrint = <xloil._core._Event object>¶

WorkbookBeforeSave = <xloil._core._Event object>¶

WorkbookDeactivate = <xloil._core._Event object>¶

WorkbookNewSheet = <xloil._core._Event object>¶

WorkbookOpen = <xloil._core._Event object>¶

class xloil.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.

property level¶: 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.

xloil.excel_func(func, *args)¶

Similar to VBA’s Application.Run. If the func string name is recognised as an Excel built-in function, i.e. available via VBA’s Application.WorksheetFunctions, calls it, otherwise tries to call a user-defined function with the given name. The name is case-insensitive.

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

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

async xloil.excel_func_async(func, *args)¶

Similar to VBA’s Application.Run. If the func string name is recognised as an Excel built-in function, i.e. available via VBA’s Application.WorksheetFunctions, calls it, otherwise tries to call a user-defined function with the given name. The name is case-insensitive.

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

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

Since calls to the Excel API must be done on Excel’s main thread (which also runs Excel’s GUI), this async version exists to prevent blocking and responsiveness issues.

xloil.excel_run(func, num_retries=10, retry_delay=500, wait_time=0)¶

Schedules a callback to be run in the COM API context. Much of the COM API in unavailable during the calc cycle, in particular anything which involves writing to the sheet.

Parameters

func: callable: A callable which takes no arguments and returns nothing
num_retries: int: Number of times to retry the call if Excel’s COM API is busy, e.g. a dialog box is open or it is running a calc cycle
retry_delay: int: Millisecond delay between retries
wait_time: int: Number of milliseconds to wait before first attempting to run this function

xloil.excel_state() → xloil._core._ExcelState¶: Gives information about the Excel application, in particular the handles required to interact with Excel via the Win32 API. The function returns a class with the following members:

version: Excel major version hinstance: Excel HINSTANCE hwnd: Excel main window handle (as an int) main_thread_id: Excel’s main thread ID

xloil.from_excel_date(value)¶: Tries to the convert a given number to a dt.date or dt.datetime assuming it is an Excel date serial number. Strings are parsed using the current date conversion settings. If dt.datetime is provided, it is simply returned as is. Raises ValueError if conversion is not possible.

xloil.get_async_loop()¶: 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()¶: 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(mod=None)¶: Returns the full path of the workbook linked to the specified module or None if the module was not loaded with an associated workbook. If no module is specified, the calling module is used.

xloil.source_addin(mod=None)¶: Returns the full path of the source add-in (XLL file) assoicated with the current code. That is the add-in which has caused the current code to be executed

xloil.log(msg, level=20) = <xloil._common.LogWriter object>¶

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.

xloil.debug.exception_debug(debugger)¶

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

‘vs’

uses ptvsd (Python Tools for Visual Studio) to enable Visual Studio (or VS Code) to connect via a remote session. Connection is on the default settings i.e. localhost:5678. This means your lauch.json in VS Code should be:

{
    "name": "Attach (Local)",
    "type": "python",
    "request": "attach",
    "localRoot": "${workspaceRoot}",
    "port": 5678,
    "host":"localhost"
}

A breakpoint is also set a the exception site.

None

Turns off exception debugging

External libraries ¶

xloil.insert_cell_image(writer, size=None, pos=(0, 0), origin: Optional[str] = None, compress: bool = True)¶

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.pandas.PDFrame(*args, **kwargs)¶

Converter which takes tables with horizontal records to pandas dataframes.

PDFrame(element, headings, index)

Parameters

elementtype: Pandas performance can be improved by explicitly specifying a type. In particular, creation of a homogenously typed Dataframe does not require copying the data. Not currently implemented!
headingsbool: Specifies that the first row should be interpreted as column headings
indexvarious: Is used in a call to pandas.DataFrame.set_index()

Examples

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

@xlo.func
def array2(y: xlo.PDFrame(float, headings=True)):
    pass

@xlo.func
def array3(z: xlo.PDFrame(str, index='Index')):
    pass

class xloil.pillow.ReturnImage(*args, **kwargs)¶

Inserts 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

xlOil Python Module Reference¶

Declaring Worksheet Functions ¶

Excel Object Model ¶

RTD Functions ¶

GUI Interaction ¶

Everything else ¶

External libraries ¶

Table of Contents

Previous topic

Next topic

This Page

xlOil Python Module Reference¶

Declaring Worksheet Functions¶

Excel Object Model¶

RTD Functions¶

GUI Interaction¶

Everything else¶

External libraries¶

Declaring Worksheet Functions ¶

Excel Object Model ¶

RTD Functions ¶

GUI Interaction ¶

Everything else ¶

External libraries ¶