Ribbons

QueryStorm apps can define their own ribbons. This goes for workbook apps as well as extension apps. Adding a ribbon can make it easier for users to interact with your app (e.g. invoke actions, display status messages).

Here's an example of a ribbon defined by a workbook app:

Defining a ribbon

To define a ribbon, add a "Ribbon class" from the project's context menu:

The scaffolded code provides a sample implementation of the IRibbon interface.

To implement the IRibbon interface, the scaffolded class defines two members:

• a GetRibbonUI() method that describes the ribbon as an XML string
• a ControlUpdateRequired event that notifies Excel when it should update a particular ribbon control

The format of the XML returned by the GetRibbonUI method is described in the official Microsoft documentation here.

If the ribbon class is correctly defined and the XML is structured correctly, all that is required for the ribbon to show up in Excel is to build the project.

Handling ribbon events

When a user clicks on a ribbon button, the ribbon class can handle the event by defining a method and specifying the name of that method as the value of the onAction property:

The event handler method should accept a single string argument which will contain the id of the button that was clicked.

Binding ribbon controls

Control properties (e.g. label, visible, editable, image) can be set in the XML string returned by the GetRibbonUI method. In this case, the values are fixed and don't change over time.

Control properties can also be dynamic and change over time. To achieve this, they should be bound to properties of the ribbon class.

For example, a button's Enabled property can be bound to a property on the ribbon class like so:

Control properties can also bind to methods. The method should accept a single string parameter (the id of the control) and return the appropriate type of result (e.g. a boolean for getVisible, a string for getLabel).

<button getEnabled='ShouldButtonBeEnabled'/>

public bool ShouldButtonBeEnabled(string controlName)
{
    ...
}

Updating a property

If the ribbon class implements INotifyPropertyChanged the controls in the ribbon will listen to the PropertyChanged event and update bound controls accordingly whenever the event is fired.

Updating a control

To update all properties of a particular control, you must call the InvalidateControl method on the ribbon class, passing in the control id. This will fire the ControlUpdateRequired event causing the control to be updated.

Binding limitations

Bindings are currently only supported for the most commonly used properties (getLabel, getEnabled, getVisible, getImage, and getPressed). This limitation is temporary and we will be adding support for other properties in the future, as requested by users. This limitation only applies to binding properties (i.e. properties that start with "get").

Obtaining dependencies

In order to do useful work, the ribbon usually needs to access other objects in the app. To do so, it can request them using constructor injection.

For example, if the ribbon needs access to the App and the IWorkbookAccessor, it should define a constructor that accepts these two dependencies as parameters:

public Ribbon1(App app, IWorkbookAccessor workbookAccessor)
{
    ...
}

It can then store those dependencies inside fields and use them as needed.

Communicating with components

In workbook apps, code that interacts with the workbook tends to be located inside components. Instead of implementing workbook-related behavior in the ribbon, it would be better if the ribbon just forwarded calls to the components that already define the behavior.

Ribbons can communicate with components in two ways:

1. by referencing them directly
2. by broadcasting events via the app.HandleEvent(EventData) method

Referencing components directly

Components can be resolved directly fom the container just like any other dependency:

class Ribbon1
{
    MyComponent1 component1;

    Ribbon1(MyComponent1 component1)
    {
        this.component1 = component1;
    }

    ...

    public void Handle_MyRibbonButton1_Click()
    {
        // call the appropriate method on the target component
        component1.SomeMethod();
    }
}

The component instance that is injected will be the same instance created by the App (components are registered as singletons inside the container).

The HandleEvent method

The app instance defines a HandleEvent method that allows sending an event into the app. This event will then be handled by any (and all) components that define a matching [EventHandler(name)] attribute.

This can be useful if we want to simplify our ribbon, so that we use the same method to handle all buttons, and that method just broadcasts an event into the app, using the name of the button as the event name.

Here's what that would look like:

public class Ribbon1
{
    App app;
    public Ribbon1(App app)
    {
        this.app = app;
    }

    public string GetRibbonUI()
    {
        // notice that we're using the same "onAction" for both buttons
        return 
@"<customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui'> 
   <ribbon> 
     <tabs> 
       <tab id='tab1' label='MyTab'> 
         <group id='group1' label='Group1'> 
           <button id='btn1' label='button1' onAction='HandleClick' />
           <button id='btn2' label='button2' onAction='HandleClick' />
         </group > 
       </tab> 
     </tabs> 
   </ribbon> 
 </customUI>";
    }

    public void HandleClick(string controlId)
    {
        // Send the event into the app so components can react to it.
        // Use the controlId as the event name.
        app.HandleEvent(new EventData(controlId, null));
    }
}

In the above example, the HandleClick method handles both ribbon buttons. It calls the app.HandleEvent method to broadcast an event into the app, passing in the id of the button as the name of the event. Components can then handle the event by defining methods that are decorated with the [EventHandler(...)] attribute:

class Component1
{
    [EventHandler("myButton1")]
    public void HandleRibbonButton1Clicked()
    {
        ...
    }    

    [EventHandler("myButton2")]
    public void HandleRibbonButton2Clicked()
    {
        ...
    }    
}

The approach outlined above leaves us with a simple ribbon class that does nothing except send events into the app, while code related to the behavior of the workbook is left where it belongs - inside components.

Workbook-scoped ribbons

If a workbook app defines a ribbon, the ribbon will be visible only in the owning workbook. It will not be visible in other workbooks.

Images in ribbons

Ribbon controls can define an image in two ways:

1. by using the imageMso property to use a built-in image
2. by using the image property to reference a custom image

The imageMso property

The easiest way to use an image on a ribbon button is to use the built-in images in Excel via the imageMso property.

For example, a button with imageMso='ObjectSendToBack', e.g.:

 <button id='myButton1' imageMso='ObjectSendToBack' label='Hello from app' size='large'>

...would show up like this:

For a list of the images available in the mso gallery, search for "office image mso gallery" online, or visit one of the following resources:

• Mso gallery (pdf)
• Mso gallery (web)

Using custom images

Users can also use custom images in the ribbon. To do so, first add the image to the project (as content):

Once the file is in the content folder, you can reference it in the image property by name:

Dynamic images

Most of the time buttons will have a static image and the above approach will work fine. When needed, though, control images can also be dynamic.

For dynamic images, use the getImage or getImageMso properties. The getImage property should bind to a property that returns a System.Drawing.Image object. To retrieve an image from the content folder, use the ResourcesHelper.ReadAsImage(imgName) method.

public class Ribbon1 : IRibbon, INotifyPropertyChanged
{
    public string GetRibbonUI()
    {
        return
@"<customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui'> 
<ribbon> 
 <tabs> 
   <tab id='Tab1' label='My Tab'> 
     <group id='group1' label='Group 1'> 
       <button id='btn1' label='Hello' size='large' onAction='btn1_Click' getImage='Btn1Image'/> 
     </group > 
   </tab> 
 </tabs> 
</ribbon> 
</customUI>";
    }

    int i = 0;
    public Image Btn1Image
    {
        get
        {
            string img = (i % 2 == 0)
                ? "pencil.png"
                : "book.png";

            // read the image from the "content" folder
            return ResourcesHelper.ReadAsImage(img);
        }
    }

    public void btn1_Click(string controlId)
    {
        i++;
        // signal that Btn1Image has changed
        OnPropertyChanged(nameof(Btn1Image));
    }

    private void OnPropertyChanged(string propertyName)
        => PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));

    public event EventHandler<RibbonControlChangeRequiredArgs> ControlUpdateRequired;
    public event PropertyChangedEventHandler PropertyChanged;
}

Merging ribbon tabs

Apps do not need to use their own ribbon tabs. Instead, app can put buttons into built-in Excel ribbon tabs, or multiple apps can put their ribbon buttons into the same tab.

Adding controls to existing tabs

To put controls into an built-in ribbon tab, the idQ property of the ribbon tab should match one of the built in tabs. For example, to put buttons into the Data tab, use idQ='mso:TabData'. You must also make sure to define the mso namespace in the customUI tag and set it to http://schemas.microsoft.com/office/2006/01/customui.

For example, the Windy.Porter extension uses this approach to add two buttons into the Data tab:

<customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui'
        xmlns:mso='http://schemas.microsoft.com/office/2006/01/customui'> 
   <ribbon>
     <tabs>
       <tab idQ='mso:TabData'> 
         <group id='group1' label='Porter'>
           <button id='btnExport' label='Export data' size='large' onAction='btnExport_Click' imageMso='ExportSharePointList'/> 
           <button id='btnImport' label='Import data' size='large' onAction='btnImport_Click' imageMso='ImportSharePointList'/> 
         </group>
       </tab>
     </tabs>
   </ribbon>
 </customUI>

The result can be seen here:

.

When reusing an existing tab, it is advisable to not specify a label for the tab, otherwise it will override the existing label of the tab.

Sharing tabs among apps

Apps can use this same approach to share ribbon tabs to save screen space.

When two or more apps need to share a ribbon, they should all use the name XML namespace and idQ for the tab name. For example, the snippet below defines a new xml namespace called my that points to an arbitrary made up url and uses it it in the idQ property of the tab:

<customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui'
        xmlns:my='http://my.made.up.company.name'> 
   <ribbon>
     <tabs>
       <tab idQ='my:TabData'> 
         <group id='group1' label='some group'>
           <button id='btnSomeButton' label='Some button'/> 
         </group>
       </tab>
     </tabs>
   </ribbon>
 </customUI>

When multiple apps use the same idQ, their ribbon tabs will be merged.