Using VSTO Excel Smart Tags With Pattern Matching (Regular Expressions)

We are currently developing an Excel Add-in to be used for hostel renting companies.

The existing system we were shown overuses one particular worksheet to contain 10 different columns of rental details, per bed, per day. That results in a very wide worksheet indeed with 1000s of columns. In order to reduce this to a simple bed per date grid of renting/availability the repeated information is extracted to additional worksheets and there will now be one date (column) per bed (row).

Removing such a large amount of needed information from the main worksheet leaves a usability problems: how to easily display and access the associated detail information in a convenient manner.

Excel supports a couple of useful features we can make use of. The first is cell comments. The second is Smart Tags.

Cell Comments

Any cell in a worksheet supports comments. Comments are simple mult-line text entries, shown when you hover over the cell and are ideal for displaying an extra layer of detail. If a comment is present a small red triangle is shown in the top right corner of the cell.

We will use the cell comments to display a copy of all the detail information for that guest booking. Although this adds a lot of duplicate data to the worksheet, it is well worth the overhead to have all the booking details available on every occupied date/bed.

While working on this part of the problem, we found that accessing the comment text string is not well document, but the easiest solution is to call cell.Comment.Text(Type.Missing, Type.Missing, Type.Missing)

Smart Tags

Smart tags are a very cool way of adding context menus that are sensitive to cell content. If a match is found a small purple triangle is shown in the bottom right corner of the cell. The context menus can then provide somewhere to place navigation to related information (e.g. using the cell value as a lookup key).

You have 2 basic choices with Smart Tags. They can have:

  • A list of keywords to match exact words in cells, or (more usefully)
  • A list of regular expressions to match against cell content

We decide to use a contrived system of placing a Guest Id in the bed/date cells of the booking sheet. The Guest Id is of the form yyyymmnnnn. It makes perfect sense to use regular expressions (like @”\d{10}” to match on any number with exactly 10 digits.

A similar contrived system for the beds to rent uses a code of the form roomname-bedletter e.g. G5-A or Flat1-D. In this instance a regular expression of @”\w[\w\d]{0,5}-\w” was used.

Assuming an include like using VSTO = Microsoft.Office.Tools.Excel at the top of the main ribbon .cs file, within the load event of our VSTO ribbon we added code like this:

VSTO.SmartTag guestTag = new VSTO.SmartTag(“http://hitechmagic.com#guest”, “Guest Id”);
guestTag.Expressions.Add(
new System.Text.RegularExpressions.Regex(@”\d{10}”));
Microsoft.Office.Tools.Excel.
Action guestDetailsAction = new Microsoft.Office.Tools.Excel.Action(“Guest details…”);
guestDetailsAction.Click +=
new VSTO.ActionClickEventHandler(guestDetailsAction_Click);
Microsoft.Office.Tools.Excel.Action bookingDetailsAction = new Microsoft.Office.Tools.Excel.Action(“Booking details…”);
bookingDetailsAction.Click +=
new VSTO.ActionClickEventHandler(bookingDetailsAction_Click);
guestTag.Actions = new Microsoft.Office.Tools.Excel.Action[] { guestDetailsAction, bookingDetailsAction };
Globals.ThisAddIn.VstoSmartTags.Add(guestTag); 

 

 

 
Basically this creates a new Smart Tag, adds a regular expression to the list of expressions, sets an array of 2 menu actions as the current actions (each with response events connected), then adds the Smart Tag to the global list of Smart Tags.

 

Results

When we run the add-in with matching values in the cells, we immediately saw little purple triangles on any cells with Guest Id numbers. When we hover over the cell, a little info icon appears beside or below the cell. Clicking the info icon produces a popup (context) menu and clicking the “Guest Details” or “Booking details” options triggers the event handlers you hooked up as expected.

All-in-all our use of Smart Tags went very smoothly and added considerable value for a few hours investigation.

Cheers, Dave

WordPress Themes