xlOil Python GUI Customisation¶
Status Bar¶
Possibly the simplest Excel GUI interaction: writing messages to Excel’s status bar:
from xloil import StatusBar
with StatusBar(1000) as status:
status.msg('Doing slow thing')
...
status.msg('Done slow thing')
The StatusBar object clears the status bar after the specified number of milliseconds once the with context ends
Ribbon¶
xlOil allows dynamic creation of Excel Ribbon components. See Custom UI: The Fluent Ribbon for background.
gui = xlo.create_gui(r'''<customUI xmlns=...>....</<customUI>''',
mapper={
'onClick1': run_click,
'onClick2': run_click,
'onUpdate': update_text,
})
The mapper
dictionary (or function) links callbacks named in the ribbon XML to python functions.
Each handler should have a signature like the following:
def ribbon_callback1(ctrl: RibbonControl)
...
def ribbon_callback2(ctrl: RibbonControl, arg1, arg2)
...
def ribbon_callback3(ctrl: RibbonControl, *args)
...
async def ribbon_callback4(ctrl: RibbonControl, *args)
...
The RibbonControl
describes the control which raised the callback. The number of additional
arguments is callback dependent. Some callbacks may be expected to return a value.
See the Resources in Custom UI: The Fluent Ribbon for a description of the appropriate callback signature.
Callbacks declared async will be executed in the addin’s event loop. Other callbacks are executed in Excel’s main thread. Async callbacks cannot return values.
The getImage callbacks must return a PIL Image. Instead of using a getImage per control, a single loadImage attribute can be added:
<customUI loadImage="MyImageLoader" xmlns=...>
...
<button id="AButton" image="icon.jpg" />
The MyImageLoader function will be called with the argument icon.jpg and be expected to return a PIL Image.
Instead of a dictionary, the mapper object can be a function which takes any string and returns a callback handler.
The gui
object returned above is actually a handle to a COM addin created to support
the ribbon customisation. If the object goes out of scope and is deleted by python or if you call
ribbon.disconnect()
, the add-in is unloaded along with the ribbon customisation.
See xlOil Python GUI Examples for an example of ribbon customisation.
Custom Task Panes¶
Custom task panes are user interface panels that are typically docked to one side of a window in Excel application.
Custom task panes are created using the ExcelGUI object. There is no need to create a ribbon as well, but task panes are normally opened using a ribbon button.
Currently only Qt is supported using PyQt5
or PySide2
. Additional support may be added.
from PyQt5.QtWidgets import QWidget # could use PySide2 instead
class MyTaskPane(QWidget):
def __init__(self): # Must have no args
... # some code to draw the widget
def send_signal(int):
... # some code to emit a Qt signal
excelui = xlo.create_gui(...)
pane = excelui.create_task_pane('MyPane', creator=MyTaskPane)
pane.widget.send_signal(3)
The create_task_pane
call first looks for a pane with the specified name which is already
attached to the active window, returning a reference to it if found. Otherwise the creator
is used. If creator
inherits from QWidget, it is constructed and attached to a new
custom task pane
With Qt, all GUI interactions (other than signals) must take place in the same thread, or
Qt will abort. To achieve this xlOil creates a special Qt-only thread, constructs MyTaskPane
on that thread, then starts the Qt event loop to run the GUI.
It is also possible to pass a function as the creator
argument. The function should take an
xloil.TaskPaneFrame
and return a xloil.CustomTaskPane
.
To talk to your widget, it’s best to set up a system of Qt signals. (the syntax differs slightly in PyQt5). It’s also possible to run GUI commands on xlOil’s Qt thread in the following way:
from xloil.qtgui import Qt_thread
future = Qt_thread.submit(func, args) # Qt_thread is a concurrent.futures.Executor
future.result() # Blocks, no need to do this if result is discarded
The pane
object is automatically stored to a registry so there is no need to hold a reference.
Task panes are attached by default to the active window and it is possible to have multiple
windows per open workbook. xlOil will free the panes when the parent workbook closes.
To look for a task pane without having a xloil.ExcelGUI
object:
pane = xloil.find_task_pane("MyPane")