Context menus

QueryStorm apps can define their own context menu commands. The commands can be defined on cells, tables, shapes and other excel objects.

Example context command class

Excel natively allows configuring context menus using the Application.CommandBars property. While it's possible for user code to use this property directly, this API is not very user-friendly and involves several subtle gotchas (e.g. control references get garbage collected, each workbook works with its own copy of the control).

To make context menus easier to define, QueryStorm offers a much simpler API as well as tooling described below.

To scaffold a new context menu command click the "Add->Context command class" item:

Add context command class

This will scaffold a new class that derives from ContextCommand.

Add context command class

The scaffolded command will work as-is, simply by compiling the project.

On startup, the App object will scan the project for public non-abstract classes that inherit from ContextCommand. It will then instantiate each one using the IOC container after which it will hook them up with Excel.

Defining the command

The base class provides a constructor with parameters that configure the command:

caption: The title of the command
allowedLocations: Locations where this command will be available (e.g. cell, table). Use KnownContextMenuLocations for a list of known locations. If not supplied, "Cell" is assumed.
category: If specified, the menu item will be nested in a parent drop-down menu with the specified caption. If not specified (null or empty string), the item will be displayed directly inside the context menu root.
order: The order in which the item is to be displayed. Items inside the same group are ordered using this property in ascending order.
faceId: The id of the built-in image for the menu item.
pictureResourceName: The name of the png or bmp file to use as the image for the menu item.
maskResourceName: The name of the png or bmp file to use as the mask for the menu item. Used for transparency handling in the picture.

Only the caption parameter is mandatory, the rest are optional.

For each argument, there is a corresponding property. All of the properties except Order and Category have setters and the UI will reflect any changes to them (e.g. a control can get disabled in certain situations, or can have its text or image dynamically changed).

The Execute method is called when the menu item is clicked by the user. It is the place where the core functionality of the command is implemented.

Obtaining dependencies

In order to perform useful work, the command will likely require access to other objects in the app.

For example, if we want to define a command that will let us change the text casing of selected cells, we will need to have access to the Excel Application object so we can get the selected range. In order to do that, we'll need the IExcelAccessor service which we can request by adding a constructor argument for it:

public class LowercaseSelectionCommand : ContextCommand
{
    private readonly IExcelAccessor excelAccessor;

    public LowercaseSelectionCommand (IExcelAccessor excelAccessor) 
        : base(
            caption: "Lowercase selected cells", 
            allowedLocations: new [] {KnownContextMenuLocations.Cell, KnownContextMenuLocations.Table}, 
            category: "Text", 
            order: 1, 
            faceId: 91)
    {
        this.excelAccessor = excelAccessor;
    }

    public override void Execute()
    {
        excelAccessor.Application.GetSelectedRange().UpdateValues(v => v?.ToString().ToLower());
    }
}

Since that the command is instantiated by the IOC container, the IExcelAccessor will be automatically injected into the constructor.

Workbook-scoped commands

If a context menu command is defined in a workbook app, it will automatically be limited to the workbook that defines it. If another workbook is open at the same time, it will not be able to see any commands defined in the first workbook.

For extension apps there is no such limitation. Commands defined in extension apps are available in all workbooks.

Command images

There are two ways to specify an image for a command:

using the FaceId argument
using the pictureResourceName and maskResourceName arguments

FaceId

The faceId argument allows referencing one of Excel's built-in icons for context menus. The list of available icons can be found here or here.

The above resources are not official Microsoft resources. In case they are not available, try searching online for "excel faceId gallery".

Custom images

Users can also specify their own custom images. To use a custom image, add it to the project as content:

Add file as content

Once the file is imported into the content folder of the project, the file can be referenced in the pictureResourceName and the maskResourceName parameters:

Context command with image

In the example above, the bulb.png image has a transparent background.

Unfortunately, transparency is not automatically supported in context menus so the bulb's background is gray instead of white.

To achieve transparency, a mask image must be defined. The mask is a copy of the original image such that the transparent area is white, while the rest of the image is black:

By applying the mask, we get the following result: