Context menus
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 ContextCommand
.
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 commandallowedLocations
: Locations where this command will be available (e.g. cell, table). UseKnownContextMenuLocations
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:
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.
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
andmaskResourceName
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:
Once the file is imported into the content
folder of the project, the file can be referenced in the pictureResourceName
and the maskResourceName
parameters:
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.