Customizing the Ribbon

The Excel Ribbon interface can be customized using PyXLL. This enables you to add features to Excel in Python that are properly integrated with Excel for an intuitive user experience.

The ribbon customization is defined using an XML file, referenced in the config with the ribbon setting. This can be set to a filename relative to the config file, or as as absolute path.

The ribbon XML file uses the standard Microsoft CustomUI schema. This is the same schema you would use if you were customizing the ribbon using COM, VBA or VSTO and there are various online resources from Microsoft that document it [1].

Actions referred to in the ribbon XML file are resolved to Python functions. The full path to the function must be included (e.g. “module.function”) and the module must be on the python path so it can be imported. Often it’s useful to include the modules used by the ribbon in the modules list in the config so that when PyXLL is reloaded those modules are also reloaded, but that is not strictly necessary.

Creating a Custom Tab

  • Create a new ribbon xml file. The one below contains a single tab Custom Tab and a single button.
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
            <tab id="CustomTab" label="Custom Tab">
                <group id="ContentGroup" label="Content">
                    <button id="textButton" label="Text Button"/>
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>
  • Set ribbon in the config file to the filename of the newly created ribbon XML file.
[PYXLL]
ribbon = <full path to xml file>
  • Start Excel (or reload PyXLL if Excel is already started).
../_images/customtab.png

The tab appears in the ribbon with a single text button as specified in the XML file. Clicking on the button doesn’t do anything yet.

Action Functions

Anywhere a callback method is expected in the ribbon XML you can use the name of a Python function.

Many of the controls used in the ribbon have an onAction attribute. This should be set to the name of a Python function that will handle the action.

  • To add an action handler to the example above first modify the XML file to add the onAction attribute to the text button
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
            <tab id="CustomTab" label="Custom Tab">
                <group id="ContentGroup" label="Content">
                    <button id="textButton" label="Text Button"
                        onAction="ribbon_functions.on_text_button"/>
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>
  • Create the ribbon_functions module and add the on_text_button function [2].
from pyxll import xl_app

def on_text_button(control):
    xl = xl_app()
    xl.Selection.Value = "This text was added by the Ribbon."
  • Add the module to the pyxll config [3].
[PYXLL]
modules = ribbon_functions
  • Reload PyXLL. The custom tab looks the same but now clicking on the text button calls the Python function.
../_images/customtab.gif

Using Images

Some controls can use an image to give the ribbon whatever look you like. These controls have an image attribute and a getImage attribute.

The image attribute is set to the filename of an image you want to load. The getImage attribute is a function that will return a COM object that implements the IPicture interface.

PyXLL provides a function, load_image, that loads an image from disk and returns a COM Picture object. This can be used instead of having to do any COM programming in Python to load images.

When images are referenced by filename using the image attribute Excel will load them using a basic image handler. This basic image handler is rather limited and doesn’t handle PNG files with transparency, so it’s recommended to use load_image instead. The image handler can be set as the loadImage attribute on the customUI element.

The following shows the example above with a new button added and the loadImage handler set.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
            loadImage="pyxll.load_image">
    <ribbon>
        <tabs>
            <tab id="CustomTab" label="Custom Tab">
                <group id="ContentGroup" label="Content">
                    <button id="textButton" label="Text Button"
                        onAction="ribbon_functions.on_text_button"/>
                </group>
                <group id="Tools" label="Tools">
                    <button id="Reload"
                            size="large"
                            label="Reload PyXLL"
                            onAction="pyxll.reload"
                            image="reload.png"/>
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>
../_images/customtab_reload.png

Footnotes

[1]

Microsoft Ribbon Resources

[2]The name of the module and function is unimportant, it just has to match the onAction attribute in the XML and be on the pythonpath so it can be imported.
[3]This isn’t strictly necessary but is helpful as it means the module will be reloaded when PyXLL is reloaded.