QueryStorm apps can define their own context menu commands. The commands can be defined on cells, tables, shapes and other excel objects.
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.
Creating a new context menu command
To scaffold a new context menu command click the "Add->Context command class" item:
This will scaffold a new class that derives from
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
KnownContextMenuLocationsfor a list of known locations. If not supplied,
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.
captionparameter is mandatory, the rest are optional.
For each argument, there is a corresponding property. All of the properties except
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).
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.
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Since that the command is instantiated by the IOC container, the
IExcelAccessor will be automatically injected into the constructor.
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.
There are two ways to specify an image for a command:
- using the
- using the
The above resources are not official Microsoft resources. In case they are not available, try searching online for "excel faceId gallery".
Users can also specify their own custom images. To use a custom image, add it to the project as content:
Once the file is imported into the
content folder of the project, the file can be referenced in the
pictureResourceName and the
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:
It might possible for QueryStorm to generate the mask ad hoc so specifying the mask image might not be needed in the future.