xlOil Python GUI Examples
This is code for the xlOil python test spreadsheet. You can find the associated sheet at Example sheets.
# If PIL is not first, gives a "specified module cannot be found" error - ?
try:
from PIL import Image
except ImportError:
pass
import xloil as xlo
import datetime as dt
import asyncio
import inspect
#---------------------------------
# GUI: Creating Custom Task Panes
#---------------------------------
#
# We demonstrate how task panes can be created using Qt and Tk. We wrap the
# Qt pane creation in a try/except in case qtpy is not installed.
#
try:
# You must import `xloil.gui.qtpy` before `qtpy`, this allows xlOil to create
# a thread to manage the Qt GUI. *All* interaction with the Qt GUI except emitting
# signals must be done on the GUI thread or Qt _will abort_. Use `Qt_thread.submit(...)`
# to send jobs to Qt's thread.
import xloil.gui.qtpy
from qtpy.QtWidgets import QLabel, QWidget, QHBoxLayout, QPushButton, QProgressBar
from qtpy.QtCore import Signal, Qt
class OurQtPane(QWidget):
_progress = Signal(int)
def set_progress(self, x: int):
# Use a signal to send the progress: this is thread safe
self._progress.emit(x)
def __init__(self):
super().__init__() # Must call this or Qt will crash
progress_bar = QProgressBar(self)
progress_bar.setGeometry(200, 80, 250, 20)
self._progress.connect(progress_bar.setValue, Qt.QueuedConnection)
label = QLabel("Hello from Qt")
layout = QHBoxLayout()
layout.addWidget(label)
layout.addWidget(progress_bar)
self.setLayout(layout)
except ImportError:
class OurQtPane:
...
# Like Qt, xlOil's tkinter module must be imported before using the toolkit
# to allow xlOil to create the *Tk_thread* and the tkinter root object. All
# interactions with tkinter must take place via *Tk_thread*.
from xloil.gui.tkinter import TkThreadTaskPane, Tk_thread
# Unlike Qt, it's not (I think) common to derive the from a tkinter object.
# Instead, we derive from `TkThreadTaskPane`, which derives from `CustomTaskPane`
class OurTkPane(TkThreadTaskPane):
def __init__(self):
super().__init__() # Important!
import tkinter as tk
top_level = self.top_level
btn = tk.Button(top_level, text="This is a Button", fg='blue')
btn.place(x=20, y=50)
from tkinter import ttk
self._progress_bar = ttk.Progressbar(top_level, length=200, mode='determinate')
self._progress_bar.place(x=20, y=100)
@Tk_thread
def set_progress(self, x: int):
self._progress_bar['value'] = x
# Define this method to capture the docking position change event
def on_docked(self):
xlo.log(f"Tk frame docking position: {self.position:}", level='info')
# Create a wxPython task pane, but wrap in a try..except in case wx is not installed
try:
from xloil.gui.wx import wx_thread
import wx
class OurWxPane(wx.Frame):
def __init__(self):
# ensure the parent's __init__ is called
super().__init__(None, title='Hello')
# create a panel in the frame
pnl = wx.Panel(self)
# put some text with a larger bold font on it
st = wx.StaticText(pnl, label="Hello World!")
font = st.GetFont()
font.PointSize += 10
font = font.Bold()
st.SetFont(font)
self._gauge = wx.Gauge(pnl)
# and create a sizer to manage the layout of child widgets
sizer = wx.BoxSizer(wx.VERTICAL)
sizer.Add(st, wx.SizerFlags().Border(wx.TOP|wx.LEFT, 25))
sizer.Add(self._gauge, wx.SizerFlags().Border(wx.TOP|wx.LEFT, 25))
pnl.SetSizer(sizer)
@wx_thread
def set_progress(self, x: int):
self._gauge.SetValue(x)
except ImportError:
class OurWxPane:
...
_PENDING_PANES = dict()
_PANE_NAMES = {
'Tk': "MyTkPane",
'Qt': "MyQtPane",
'wx': "MyWxPane"
}
# We define a function to create a task pane using Tk or Qt. We first check
# that the pane has not already been created, then construct a instance of the
# pane class, then attach it to the ExcelGUI object created later.
async def make_task_pane(toolkit):
global _excelgui
pane_name = _PANE_NAMES[toolkit]
key = (pane_name, xlo.app().windows.active.name)
pane = xlo.gui.find_task_pane(pane_name)
if pane is not None:
xlo.log(f"Found pane: {key}")
pane.visible = True
return
# Since we are using async, the open pane button may have been
# clicked more than once before the pane got a chance to create
pane_future = _PENDING_PANES.get(key, None)
if pane_future is not None:
return
# attach_pane can accept a CustomTaskPane instance, or a QWidget
# instance or an awaitable to one of those things. You can also
# pass a QWidget type which xlOil wrap in a QtThreadTaskPane and
# create in the correct thread.
#
# (We could just pass `OurTkPane` rather than using Tk_thread, this
# is just to demonstrate passing an awaitable)
if toolkit == 'Tk':
future = _excelgui.attach_pane_async(
name=pane_name,
pane=Tk_thread().submit_async(OurTkPane))
elif toolkit == 'Qt':
future = _excelgui.attach_pane_async(
name=pane_name,
pane=OurQtPane)
elif toolkit == 'wx':
future = _excelgui.attach_pane_async(
name=pane_name,
pane=OurWxPane)
else:
raise Exception()
_PENDING_PANES[key] = future
pane = await future
del _PENDING_PANES[key]
pane.visible = True
return pane
#----------------------
# GUI: Creating Ribbons
#----------------------
#
# xlOil is able to create a ribbon entry for a workbook which is automatically
# removed when the workbook is closed and the associated workbook module is
# unloaded. To create the ribbon XML use an editor such as Office RibbonX Editor:
# https://github.com/fernandreu/office-ribbonx-editor
#
# GUI 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.
#
def _get_icon_path():
import os
# Gets the path to an icon file to demonstrate PIL image handling
return os.path.join(os.path.dirname(xloil.linked_workbook()), 'icon.bmp')
def button_image(ctrl):
# Ribbon callback to determine the button's icon (see ribbon xml)
im = Image.open(_get_icon_path())
return im
# Maps button ids in the ribbon xml below to GUI toolkit names
_BUTTON_MAP = {
"buttonTk": "Tk",
"buttonQt": "Qt",
"buttonWx": "wx"
}
def get_button_label(ctrl, *args):
# Ribbon callback to determine button label text
return f"Open {_BUTTON_MAP[ctrl.id]}"
async def press_open_pane_button(ctrl):
toolkit = _BUTTON_MAP[ctrl.id]
xlo.log(f"Open {toolkit} Pressed")
await make_task_pane(toolkit)
#
# The combo box in the ribbon xml has the value 33, 66 or 99. We send
# this as the progress % to the progress bar in our task panes (if they
# have been created)
#
def combo_change(ctrl, value):
qt_pane = xlo.gui.find_task_pane(_PANE_NAMES['Qt'])
if qt_pane:
qt_pane.widget.set_progress(int(value))
tk_pane = xlo.gui.find_task_pane(_PANE_NAMES['Tk'])
if tk_pane:
tk_pane.set_progress(int(value))
wx_pane = xlo.gui.find_task_pane(_PANE_NAMES['wx'])
if wx_pane:
wx_pane.frame.set_progress(int(value))
return "NotSupposedToReturnHere" # check this doesn't cause an error
async def press_open_console_button_tk(ctrl):
def sesame(root):
from xloil.gui.tkinter import TkConsole
import tkinter
import code
top_level = tkinter.Toplevel(root)
console = TkConsole(top_level, code.interact,
fg='white', bg='black', font='Consolas', insertbackground='red')
console.pack(expand=True, fill=tkinter.BOTH)
console.bind("<<CommandDone>>", lambda e: top_level.destroy())
top_level.deiconify()
from xloil.gui.tkinter import Tk_thread
await Tk_thread().submit_async(sesame, Tk_thread().root)
async def press_open_console_button_qt(ctrl):
def sesame():
from xloil.gui.qt_console import create_qtconsole_inprocess
console = create_qtconsole_inprocess()
console.show()
return console
from xloil.gui.qtpy import Qt_thread
await Qt_thread().submit_async(sesame)
#
# We construct the ExcelGUI (actually a handle to a COM addin) using XML to describe
# the ribbon and a map from callbacks referred to in the XML to actual python functions
#
_excelgui = xlo.ExcelGUI(ribbon=r'''
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="customTab" label="xloPyTest" insertAfterMso="TabHome">
<group idMso="GroupClipboard" />
<group idMso="GroupFont" />
<group id="customGroup" label="MyButtons">
<button id="buttonTk" getLabel="getButtonLabel" getImage="buttonImg" size="large" onAction="pressOpenPane" />
<button id="buttonQt" getLabel="getButtonLabel" getImage="buttonImg" size="large" onAction="pressOpenPane" />
<button id="buttonWx" getLabel="getButtonLabel" getImage="buttonImg" size="large" onAction="pressOpenPane" />
<button id="tkConsole" label="Tk Console" size="large" onAction="pressOpenConsoleTk" />
<button id="qtConsole" label="Qt Console" size="large" onAction="pressOpenConsoleQt" />
<comboBox id="comboBox" label="Combo Box" onChange="comboChange">
<item id="item1" label="33" />
<item id="item2" label="66" />
<item id="item3" label="99" />
</comboBox>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
''',
funcmap={
'pressOpenPane': press_open_pane_button,
'pressOpenConsoleTk': press_open_console_button_tk,
'pressOpenConsoleQt': press_open_console_button_qt,
'comboChange': combo_change,
'getButtonLabel': get_button_label,
'buttonImg': button_image
})
#-----------------------------------------
# Images: returning images from functions
#-----------------------------------------
# In case PIL is not installed, protect this section
try:
# This import defines a return converter which allows us to return a PIL image
import xloil.pillow
import os
from PIL import Image
# The image return converter is registered, so we just need to return the PIL
# image from an xlo.func. Returning an image requires macro=True permissions
@xlo.func(macro=True)
def pyTestPic():
im = Image.open(_get_icon_path())
return im
# Normally we use a return converter as an annotation like `-> ReturnImage` but
# if we want to dynamically pass arguments to the converter we can call it
# directly as below
@xlo.func(macro=True)
def pyTestPicSized(width:float, height:float, fitCell: bool=False):
from PIL import Image
import os
im = Image.open(_get_icon_path())
if fitCell:
return xlo.pillow.ReturnImage(size="cell")(im)
else:
return xlo.pillow.ReturnImage((width, height))(im)
except ImportError:
pass
#-----------------------------------------
# Plots: returning matplotlib figures from functions
#-----------------------------------------
# In case matplotlib is not installed, protect this section
try:
# This import defines a return converter for a matplotlib figure
# It also imports matplotlib like this:
#
# import matplotlib
# matplotlib.use('Agg')
# from matplotlib import pyplot
#
# The order is important: the matplotlib backend must be switched
# from the Qt default before pyplot is imported
#
import xloil.matplotlib
from matplotlib import pyplot
@xlo.func(macro=True, local=False)
def pyTestPlot(x, y, width:float=4, height:float=4, **kwargs):
fig = pyplot.figure()
fig.set_size_inches(width, height)
ax = fig.add_subplot(111)
ax.plot(x, y, **kwargs)
return fig
except ImportError:
pass