Office programming in .NET has always been bedevilled by ugly APIs and inadequate tooling. The Office API dates back to the COM era and to Visual Basic for Applications, and many of its interfaces are designed specifically for the VBA language with its built-in support for named and optional parameters, loose typing, etc.
Converted to .NET, however -- raise your hand if the phrase "Office 2003 Primary Interop Assemblies" brings you out in hives -- this results in methods with some two dozen parameters, where all of them are typed as object, and all except the fifteenth are always Type.Missing.
This worked well in VB Classic with named parameters, optional parameters and evil type coercion, but was a complete pain in strict languages like C# and VB.NET.
Meanwhile, on the tooling front, all Visual Studio has offered is a Ribbon schema for the XML editor. It's not exactly the Windows Forms designer. All in all, I've not felt particularly enthusiastic about trying to build Office applications using .NET.
Last year, however, I was working on an application for a major logistics company we'll call New Zealand Toast. I can't talk about the real application, but translated into the universal language of toast, the idea was that bread producers could arrange for the logistics company to pick up pieces of bread, and deliver them in toast form to customers around the world. (If this sounds like a bad business model, or a recipe for a cold, late breakfast, bear in mind that the universal language of toast, while it works well for grilled bread products, doesn't translate so well to the competitive world of international logistics. The real application was, well, a bit more realistic.)
The application enabled Toast's customers to create consignment notes -- addressing and customs documents -- for their bread packages via a Web interface instead of having to write them out by hand.
For larger bread producers, the application allowed users to upload multiple orders in a CSV file -- assumed to be exported from Excel -- rather than entering them by hand.
This worked fine, but it wasn't a seamless experience for users. Users had to work on the file in Excel, then remember to save the file as CSV, then switch to the Web application to upload it. It seemed to me that one way around these problems was to allow users to work entirely within Excel using a provided template.
If we could put a "Toast" button on the Excel ribbon, users could work in a suitably friendly template, then click the button when they were ready. No need to save as CSV, no need to switch to the Web browser, much less scope for things to go wrong.
Fortunately the real application was built as a set of Web services, and with .NET it would be just as easy to call those from Excel as from our Web application. So I fired up Visual Studio 2008 and started throwing together a proof of concept.
The first thing I noticed in VSTO 2008 was that the Office 2007 option seemed to be disabled! The reason was that Visual Studio 2008 supports targeting different versions of the .NET Framework, and I had selected .NET 2.0. This is okay for Office 2003, but if you want to target Office 2007, you need to select .NET 3.5. Once I'd done that, I got the following options in the New Project dialog:
Creating the User Interface
The boilerplate generated for an add-in was about as minimal as you get, but things got a bit more interesting when I right-clicked the project and chose Add New Item. The New Item dialog now included a "Ribbon (Visual Designer)" item:
This allows you to create Ribbon items using (you guessed it) a visual design surface instead of editing XML files. Visual Studio starts you off with a single empty group which will go into the Add-Ins tab. You can of course change this to use a different tab, or to create a new tab just for your commands.
In fact, if the functionality you're adding logically affects more than one tab, you can put all of those tabs into the same Ribbon definition. You can also add items to the Office menu itself.
One teasing option is to mark your ribbon as "start from scratch": this hides the built-in Ribbon tabs and shows only yours, like the Blog Post template in Word. This would be very evil and wrong to do for an application-level add-in, and even for a template add-in you'd do it only if the majority of application commands didn't make sense in your template's context. (The Word Blog Post template gets away with it because things like mail merge, page layout and revision tracking aren't applicable to blog posts.)
Within each tab, you can create groups, buttons and other Ribbon elements using the toolbox and the drag-drop-edit-the-properties method familiar to all Windows Forms developers.
Here's a snippet of the Ribbon designer. I've renamed the group that Visual Studio has created for me, and I'm about to drag a command button on from the Toolbox:
Once that was done, I wanted to make the button look pleasantly Office-like. By default, Visual Studio 2008 creates a small, text-only button, but this is the primary (indeed sole) way for users to access the Toast functionality, so I thought we needed a large graphical button. (I might use small text buttons for ancillary actions like setting options, accessing address books, etc., but the main function deserved a big button.) To do this, I went to the Properties window, change the ControlSize to RibbonControlSizeLarge, and set the Image property to a suitable graphic.
When I hit the Debug button, Visual Studio launched Excel with my add-in loaded:
There's really not all much more to say. Finally building the user interface for an Office application is as easy as building a Windows Forms UI. I could have added more controls such as Office galleries, and hooked up features like Office 2007's "super tips" (multiline tooltips), but it's just variations on a theme:
VSTO 2008 also supports other Office UI idioms. Although there's no dedicated support for task panes, you can create them as Windows Forms user controls. If you're packing your application as a template or document, you can also create document action panes, which give you a prominent place to display commands or data that are specific to your document. I didn't need either of these features, though if I'd gone for the template approach I could have used the action pane to step users through the consignment creation process instead of letting them find it on the Ribbon.
Writing the Code
Of course, so far, none of my buttons actually does anything. That's easily fixed though.
To create a Click handler for a button, all I had to do was double-click it in the designer. (You can also use the Events tab in the Properties window; that's useful for creating or assigning handlers for less common events. But, as with Windows Forms, Visual Studio knows that most of the time, with buttons, we're interested in the Click event.) Visual Studio 2008 created a stub method in the code-behind file, hooked it up to the button's Click event, and displayed the stub method ready for me to edit.
And now the real work began, of writing the code to actually create the consignment notes from the contents of the workbook. In my case, this was a matter of copying data from the cells into a Web service message, and sending it to the New Zealand Toast Web services. I'm not going to say much about this because it's very specific to the application, but here are a couple of things that bit me:
- The Office API, unfortunately, is rather uneven, with modern .NET APIs suddenly giving way to crevasses of poorly documented COM interop. There's a lot of HpeHungarianPrefixedEnums.wrepWithRedundantEnumPrefixes; Excel cells are actually Ranges, except when they're Objects; Range has properties called Value and Value2; and Type.Missing is still hanging in there. You can almost feel the years of compatibility cruft. On the plus side, if you're used to the existing Automation model, or want to reuse your existing Office code in VSTO 2008, this is probably exactly what you want.
- Visual Studio doesn't give you a lot of help once it's dropped you into the code. Typically in an Office application you need to get to the Application object, and you can then drill down from there, but the generated event handler doesn't receive an Application object. The trick is that Visual Studio generates a Globals class which contains a reference to the current instance of the add-in, which in turn has a reference to the Application. So you can always get the Application object via Globals.ThisAddIn.Application. (In a template or document, this would instead be Globals.ThisWorkbook or Globals.ThisDocument.)
Other than that, it's just a matter of writing your custom logic in C# or Visual Basic, and knowing the object model of your target application.
Deploying the Application
Disclaimer: I have to admit that my proof of concept didn't go as far as deployment, so I can't comment on how well all this works.
With that caveat in mind, deploying VSTO 2008 solutions looks much easier than previous Office programming models. You can just use ClickOnce. Visual Studio creates a folder including a setup.exe file as well as your application package; all you need to do is copy that folder to your Web server and give users a link to setup.exe.
This is particularly sweet for an application like mine that targets external users. Toast couldn't have used group policy or SMS to distribute the application, but they could easily have created a page with a link to setup.exe, and a set of instructions explaining what to do when, for example, ClickOnce started asking for scary permissions. It also addresses the problem of getting users to install updates or bug fixes, as ClickOnce will check back for new versions at a configurable interval.
Don't forget that VSTO 2008 depends on .NET 3.5. Users therefore need to have .NET 3.5 installed on their machines; they also need the VSTO runtime. If you use ClickOnce, though, Visual Studio includes these prerequisites in the application manifest and ClickOnce automatically downloads them.
Admittedly I only scratched the surface of VSTO 2008 in my little proof of concept, but I did feel that it had removed a lot of the complexity and makework of building Office applications. The new Ribbon designer makes it a snap to build your user interface and to integrate it into the standard Ribbon -- or to build a custom Ribbon if that's what your application needs.
The code you'll write to drive office remains familiar from Office 2003, but hooking it up to the Office UI is now almost frictionless. And ClickOnce deployment simplifies the rollout process for organisations that don't have mighty mighty group policies to push things out, or for applications that need to be used by external users.
For someone like me who's been put off Office as a platform in the past by the high apparent cost of entry, Visual Studio 2008, with VSTO built in for the first time, and Office 2007 have been a bit of an eye-opener.
Download Visual Studio 2008 90 day trial
For detailed information and to request a free 90-day trial DVD of Visual Studio 2008 Team Suite to be sent out to you, go to the Microsoft Visual Studio webpage.
About the Author
Ivan Towlson is a software developer and architect at Mindscape. Over the years he has worked in a variety of industries from civil engineering and pharmaceuticals to telecoms and e-commerce messaging. He currently works on developer products for Mindscape.
Other related posts:
The New Zealand ALM Conference 2011 (Application Life Cycle Management)
Writing your own Html Helpers for the ASP.NET MVC Framework
Automating Visual Studio 2008