PyXLL enables you to integrate sophisticated user interfaces directly into Excel.
Python UI controls can be embedded into ExcelCustom Task Panesso they seamlessly fit in with the rest of the Excel user interface.
PyXLL has support for the following Python UI tookits.
3.7.1 PySide2 and PyQt5
PySide2andPyQt5are both Python packages wrapping the popularQt5UI toolkit. They are quite similar but have different licenses and so which one you choose will be down to your own preference. Both work equally well with PyXLL.
This document is not a guide to use PySide2 or PyQt5. It is only intended to instruct you on how to use PySide2 and PyQt5 with the Custom Task Pane feature of PyXLL. You should refer to the relevant package documentation for details of how to use each package.
Both PySide2 and PyQt5 can be installed using pip or conda, for example: > pip install pyside2
# or
> pip install pyqt5
# or
> conda install pyside2
# or
> conda install "pyqt>=5"
Typically you will only want to install one or the other, and you should install it using piporconda and not both. You can find more information about PySide2 and PyQt5 on the websites,https://pypi.org/project/PySide2/and https://pypi.org/project/PyQt5respectively.
Creating a Qt Widget
One of the main classes in Qt5 is theQWidgetclass. To create your own user interface it is thisQWidgetclass that you will use, and it’s what PyXLL will embed into Excel as a Custom Task Pane.
The following code demonstrates how to create simple Qt widget. If you run this code as a Python script then you will see the widget being shown.
from PySide2 import QtWidgets
# or from PyQt5 import QtWidgets
import sys
class ExampleWidget(QtWidgets.QWidget): def __init__(self):
super().__init__()
self.initUI() def initUI(self):
"""Initialize the layout and child controls for this widget."""
# Give the widget a title
self.setWindowTitle("Example Qt Widget")
# Create a "Layout" object to help layout the child controls. # A QVBoxLayout lays out controls vertically.
vbox = QtWidgets.QVBoxLayout(self)
# Create a QLineEdit control and add it to the layout
self.line_edit = QtWidgets.QLineEdit(self) vbox.addWidget(self.line_edit)
# Create a QLabel control and add it to the layout
self.label = QtWidgets.QLabel(self) vbox.addWidget(self.label)
# Connect the 'textChanged' event to our 'onChanged' method
self.line_edit.textChanged.connect(self.onChanged)
# Set the layout for this widget
self.setLayout(vbox) def onChanged(self, text):
"""Called when the QLineEdit's text is changed"""
# Set the text from the QLineEdit control onto the label control
self.label.setText(text)
self.label.adjustSize() if __name__ == "__main__":
# Create the Qt Application
app = QtWidgets.QApplication(sys.argv)
# Create our example widget and show it
widget = ExampleWidget() widget.show()
# Run the Qt app
sys.exit(app.exec_())
When you run this code you will see our example widget being display, and as you enter text into the line edit control the label below will be updated.
Creating a Custom Task Pane from a Qt Widget
To show a QWidget in Excel using PyXLL we use thecreate_ctpfunction.
As above, before we can create the widget we have to make sure the QApplication has been initialized. Unlike the above script, our function may be called many times and so we don’t want to create a new application each time and so we check to see if one already exists.
The QApplication object must still exist when we callcreate_ctp. If it has gone out of scope and been released then it will cause problems later so always make sure to keep a reference to it.
We can create the Custom Task Pane from many different places, but usually it will be from aribbon functionor amenu function.
The following code shows how we would create a custom task pane from an Excel menu function, using the ExampleWidgetcontrol from the example above.
from pyxll import xl_menu, create_ctp, CTPDockPositionFloating from PySide2 import QtWidgets
# or from PyQt5 import QtWidgets
@xl_menu("Example Qt CTP")
def example_qt_ctp():
# Before we can create a Qt widget the Qt App must have been initialized. # Make sure we keep a reference to this until create_ctp is called.
app = QtWidgets.QApplication.instance() if app is None:
app = QtWidgets.QApplication([])
# Create our example Qt widget from the code above
widget = ExampleWidget()
# Use PyXLL's 'create_ctp' function to create the custom task pane. # The width, height and position arguments are optional, but for this # example we'll create the CTP as a floating widget rather than the # default of having it docked to the right.
create_ctp(widget, width=400, height=400,
position=CTPDockPositionFloating)
When we add this code to PyXLL and reload the new menu function “Example Qt CTP” will be available, and when that menu function is run the ExampleWidget is opened as a Custom Task Pane in Excel.
Unlike a modal dialog, a Custom Task Pane does not block Excel from functioning. It can be moved and resized, and even docked into the current Excel window in exactly the same way as the native Excel tools.
See the API reference forcreate_ctpfor more details.
3.7.2 wxPython
wxPythonis a Python packages that wraps the UI toolkitwxWindows.
This document is not a guide to use wxPython or wxWindows. It is only intended to instruct you on how to use wxPython with the Custom Task Pane feature of PyXLL. You should refer to the relevant package documentation for details of how to use wxPython and wxWindows.
Both wxWindows can be installed using pip or conda, for example: > pip install wxpython
# or
> conda install wxpython
You should install it using piporconda and not both.
Creating a wx Frame
Two of the main classes we’ll use in wxPython are thewx.Frameandwx.Panelclasses.
Awx.Frameis the main window type, and it’s this that you’ll create to contain your user interface that will be embedded into Excel as a Custom Task Panel. Frames typically host a singlewx.Panelwhich is where all the controls that make up your user interface will be placed.
The following code demonstrates how to create simple wx.Frame and corresponding wx.Panel. If you run this code as a Python script then you will see the frame being shown.
import wx
class ExamplePanel(wx.Panel): def __init__(self, parent):
super().__init__(parent=parent)
# Create a sizer that will lay everything out in the panel. # A BoxSizer can arrange controls horizontally or vertically.
sizer = wx.BoxSizer(orient=wx.VERTICAL)
# Create a TextCtrl control and add it to the layout
self.text_ctrl = wx.TextCtrl(self) sizer.Add(self.text_ctrl)
# Create a StaticText control and add it to the layout
self.static_text = wx.StaticText(self) sizer.Add(self.static_text)
# Connect the 'EVT_TEXT' event to our 'onText' method
self.text_ctrl.Bind(wx.EVT_TEXT, self.onText)
# Set the sizer for this panel and layout the controls
self.SetSizer(sizer)
self.Layout()
def onText(self, event):
"""Called when the TextCtrl's text is changed"""
# Set the text from the event onto the static_text control
text = event.GetString()
self.static_text.SetLabel(text) class ExampleFrame(wx.Frame):
def __init__(self):
super().__init__(parent=None)
# Give this frame a title
self.SetTitle("Wx Example")
# Create the panel that contains the controls for this frame
self.panel = ExamplePanel(parent=self)
if __name__ == "__main__":
# Create the wx Application object
app = wx.App()
# Construct our example Frame and show it
frame = ExampleFrame() frame.Show()
(continued from previous page)
# Run the application's main event loop
app.MainLoop()
When you run this code you will see our example frame being display, and as you enter text into the text control the static text below will be updated.
Next we’ll see how we can use this frame in Excel.
Creating a Custom Task Pane from a wx.Frame
To show a wx.Frame in Excel using PyXLL we use thecreate_ctpfunction.
As above, before we can create the frame we have to make sure the wx.App application object has been initialized. Unlike the above script, our function may be called many times and so we don’t want to create a new application each time and so we check to see if one already exists.
The wx.App object must still exist when we callcreate_ctp. If it has gone out of scope and been released then it will cause problems later so always make sure to keep a reference to it.
We can create the Custom Task Pane from many different places, but usually it will be from aribbon functionor amenu function.
The following code shows how we would create a custom task pane from an Excel menu function, using the ExampleFramecontrol from the example above.
from pyxll import xl_menu, create_ctp, CTPDockPositionFloating import wx
@xl_menu("Example wx CTP")
def example_wx_ctp():
# Before we can create a wx.Frame the wx.App must have been initialized. # Make sure we keep a reference to this until create_ctp is called.
app = wx.App.Get() if app is None:
app = wx.App()
# Create our example frame from the code above
frame = ExampleFrame()
# Use PyXLL's 'create_ctp' function to create the custom task pane.
(continued from previous page)
# The width, height and position arguments are optional, but for this # example we'll create the CTP as a floating window rather than the # default of having it docked to the right.
create_ctp(frame, width=400, height=400,
position=CTPDockPositionFloating)
When we add this code to PyXLL and reload the new menu function “Example wx CTP” will be available, and when that menu function is run the ExampleFrame is opened as a Custom Task Pane in Excel.
Unlike a modal dialog, a Custom Task Pane does not block Excel from functioning. It can be moved and resized, and even docked into the current Excel window in exactly the same way as the native Excel tools.
See the API reference forcreate_ctpfor more details.
3.7.3 Tkinter
tkinteris a Python packages that wraps theTk GUI toolkit.
tkinteris included with Python and so is available to use without needing to install any additional packages. This document is not a guide to use tkinter. It is only intended to instruct you on how to use Tkinter with the Custom Task Pane feature of PyXLL. You should refer to the tkinter documentation for details of how to use tkinter.
You can find more information about tkinter in the Python docs websitehttps://docs.python.org/3/library/tkinter. html.
Creating a tk Frame
One of the main classes in tkinter is theFrameclass. To create your own user interface it is thisFrameclass that you will use, and it’s what PyXLL will embed into Excel as a Custom Task Pane.
The following code demonstrates how to create simpletkinter.Frame. If you run this code as a Python script then you will see the frame being shown.
import tkinter as tk
class ExampleFrame(tk.Frame): def __init__(self, master):
super().__init__(master)
self.initUI() def initUI(self):
# allow the widget to take the full space of the root window
self.pack(fill=tk.BOTH, expand=True)
# Create a tk.Entry control and place it using the 'grid' method
self.entry_value = tk.StringVar()
self.entry = tk.Entry(self, textvar=self.entry_value)
self.entry.grid(column=0, row=0, padx=10, pady=10, sticky="ew")
# Create a tk.Label control and place it using the 'grid' method
self.label_value = tk.StringVar()
self.label = tk.Label(self, textvar=self.label_value)
self.label.grid(column=0, row=1, padx=10, pady=10, sticky="w")
# Bind write events on the 'entry_value' to our 'onWrite' method
self.entry_value.trace("w", self.onWrite)
# Allow the first column in the grid to stretch horizontally
self.columnconfigure(0, weight=1) def onWrite(self, *args):
"""Called when the tk.Entry's text is changed"""
# Update the label's value to be the same as the entry value
self.label_value.set(self.entry_value.get()) if __name__ == "__main__":
# Create the root Tk object
root = tk.Tk()
# Give the root window a title
root.title("Tk Example")
# Construct our frame object
ExampleFrame(master=root)
# Run the tk main loop
root.mainloop()
When you run this code you will see our example frame being display, and as you enter text into the text entry control the static text label below will be updated.
Creating a Custom Task Pane from a tkinter.Frame
To show atkinter.Framein Excel using PyXLL we use thecreate_ctpfunction.
As above, before we can create the frame we have to create a root object to add it to. Unlike the above script, our function may be called many times and so we don’t want to use thetk.Tkroot object. Instead we use a tk.Toplevelobject.
We can create the Custom Task Pane from many different places, but usually it will be from aribbon functionor amenu function.
The following code shows how we would create a custom task pane from an Excel menu function, using the ExampleFramecontrol from the example above.
from pyxll import xl_menu, create_ctp, CTPDockPositionFloating import tkinter as tk
@xl_menu("Example Tk CTP")
def example_tk_ctp():
# Create the top level Tk window and give it a title
window = tk.Toplevel() window.title("Tk Example")
# Create our example frame from the code above and add # it to the top level window.
frame = ExampleFrame(master=window)
# Use PyXLL's 'create_ctp' function to create the custom task pane. # The width, height and position arguments are optional, but for this # example we'll create the CTP as a floating window rather than the # default of having it docked to the right.
create_ctp(window, width=400, height=400,
position=CTPDockPositionFloating)
When we add this code to PyXLL and reload the new menu function “Example Tk CTP” will be available, and when that menu function is run the ExampleFrame is opened as a Custom Task Pane in Excel.
Unlike a modal dialog, a Custom Task Pane does not block Excel from functioning. It can be moved and resized, and even docked into the current Excel window in exactly the same way as the native Excel tools.
3.7.4 Other UI Toolkits
PyXLL provides support forPySide2 and PyQt5,wxPython, andTkinter.
If you want to use another Python UI toolkit that’s not already supported then you still may be able to. To do so you need to provide you own implementation of PyXLL’sCTPBridgeBaseclass.
TheCTP Bridgeis what PyXLL uses to manage getting certain properties of the Python UI toolkit’s window or frame objects in a consistent way and passing events from Excel to Python.
See the API reference forCTPBridgeBasefor details of the methods you need to implement.
Once you have implemented your CTP Bridge you pass it tocreate_ctpas thebridge_clskeyword argu- ment. Whatever object you pass as the widget tocreate_ctpwill be used to construct your CTP Bridge object. PyXLL will take care of the rest of embedding your widget into Excel.
Warning: Writing a CTP Bridge requires detailed knowledge of the UI toolkit you are working with. This is an expert topic and PyXLL can only offer support limited to the functionality of PyXLL and not third party packages.
Custom Task Panes (CTPs) are created using a control or widget from any of the supported Python UI toolkits by calling the PyXLL functioncreate_ctp. All CTPs can be docked into the main Excel window and the initial position and size can be set when callingcreate_ctp.
For specific details of creating a custom task pane with any of the supported Python UI toolkits see the links above. Examples are provided in theexamples/custom_task_panesfolder in the PyXLL download.
Fig. 2: A Python user interface in Excel