he previous article in this series discussed how to use the Visual Studio Tools for Office (VSTO) platform to build a custom alerting application that used Outlook as the alerting engine. The ubiquity and power of the Office platform make this possible; as a developer you can exploit the capabilities already available to users via their Office installations, using Office as an application runtime platform.
Beyond that, you can also drastically improve users’ experiences by building applications that mesh neatly with their daily workflow. For example consider an application that a financial analyst might use to research companies?looking at their stock price history, earnings per share, and other financial analytics. Typically, they’ll use Excel to manipulate the data. Therefore, building an application that works alongside Excel will usually benefit them more than building a separate application.
That’s exactly what you’ll do in this article. Using VSTO, you’ll look into what it takes to build a simple custom research pane that fits alongside a worksheet. This custom research pane renders detailed information about the company that a financial analyst is currently investigating in Excel.
Getting Started
 |
? |
| Figure 1. The Research Actions Pane: The figure shows a research actions pane running in Excel. |
Before you get going you’ll need Visual Studio 2005, Visual Studio Tools for Office, and of course, a copy of Microsoft Excel 2003. The code supplied in this article should work with the Office 2007 betas?but I didn’t test with them, so if you go that route, I’d love to hear what the results are!
With Office 2003, Microsoft introduced the concept of the Actions Pane, which by default appears on a right-hand sidebar and provides a space for UI, allowing users to take some specific action. You can see an example Actions Pane UI in Figure 1, which offers users easy access to research about a topic via a friendly UI.
The Actions Pane concept is particularly useful for applications such as Excel, where you may want to present a “typical” Windows UI to the user, containing lists, buttons, tabs and the like. This type of UI is quite unattractive if placed directly into a spreadsheet, and doesn’t fit the spreadsheet paradigm. Consider what the Encarta research pane shown in Figure 1 would look like if the information were instead input directly to the spreadsheet. Not only would it be visually unappealing, but it could also detract from the user’s work flow. Separating it into an Action Pane provides a more attractive and far less disruptive UI.
 |
? |
| Figure 2. Excel with Custom Research Action Pane: The figure shows how separating the UI into a separate Action Pane can work even for displaying relatively large amounts of text. |
Consider the case of a research action pane for a stock analyst, which might return large chunks of textual information. It is much more palatable to have this information rendered in a list or a Web browser interface within the research action pane. For example, an analyst might be researching a number of companies using a spreadsheet. As she flicks through each company, she’d like a call-out showing the details of each company. This is the ideal scenario for a research pane like the Encarta one shown in Figure 1. The scenario might look something like Figure 2.
In this case, you can see that the user has a portfolio application running in Excel that keeps track of the number of shares purchased, the purchase price, the current price and the associated Delta (earnings or losses).
Beyond the numbers in the spreadsheet, the analyst can retrieve additional company data from a different source?data that may help her decide whether to hold onto a particular stock or to increase or decrease her holdings. This information is a “deep dive” into company information made available in an Action Pane that renders the information for her.
Building the Action Pane
To build this Action Pane, first create a new Excel Workbook project in Visual Studio 2005 with the Visual Studio Tools for Office add-in installed by clicking File -> New. Select “Office” as the project type, and “Excel Workbook” as the template from the New Project dialog. When asked, create a new document.
After doing this you’ll get a Visual Studio 2005 solution containing a new project that contains an .xls (Excel Workbook) file and three worksheets, each implemented as a .cs file. This is your workbook; you’ll add the research pane to it.
To build an Action Pane, you don’t need to do anything special?just build a user control. So, add a new user control to your project and call it StockResearch. Add a new WebBrowser control to this user control. The WebBrowser control will act as the rendering engine to display the results from calls made to a Web service that supplies the data.
Next, you’ll need to create a Web reference to the service that hosts the data. Do this by adding a Web reference to the following WSDL:
http://ws.invesbot.com/stockquotes.asmx?WSDLThe preceding URL points to the WSDL file for a public Web service (indexed on Webmethods.net) that exposes a Web method that, given a company stock ticker symbol, returns the company description as well as some company fundamentals.
Next, you’ll need to implement a public method on the StockResearch control that calls this service, gets the returned data, formats it into HTML and loads this HTML into the WebBrowser control. You can see the full routine in Listing 1, but there’s a lot going on so it’s worth walking through the code little by little.
First you’ll need to create an XML Document to store the XML returned by the Stock Quote Service.
XmlDocument xmlDoc = new XmlDocument();Next, you’ll create an instance of the StockQuotes Web services proxy and call it with the ticker string that is passed in to this function as a parameter:
com.invesbot.ws.StockQuotes x = new com.invesbot.ws.StockQuotes(); String strQuote = x.GetQuote(strTicker).InnerXml;Some of the XML that is returned is HTML encoded. As we will render this on a page using XSLT we should decode it. All we need to decode are the ‘<‘ and ‘>‘ symbols.
strQuote = strQuote.Replace(">", ">"); strQuote = strQuote.Replace("<", "<");The
int nStart = strQuote.IndexOf(""); int nEnd = strQuote.IndexOf(" The XML returned from the service is a node, not a document, so you must give it a root node so it will validate.
strQuote = "" + strQuote; strQuote = strQuote + " ";Next, you need to load the string into an XmlDocument object to parse it.
xmlDoc.LoadXml(strQuote);Now, onto the XSLT that performs the transformation. Listing 2 contains a sample of the XML generated thus far, and Listing 3 shows the conv.xslt file that converts the XML to HTML. To achieve this you need to declare an XslCompiledTransform object and load the XSLT into it:
XslCompiledTransform xslt = new XslCompiledTransform(); xslt.Load("c:\conv.xslt");Now, create a memory stream and open an XmlTextWriter on the stream. The xslt.Transform method uses the XmlTextWriter to write the transformed XML to this stream. When you're done, set the stream's position to its beginning.
MemoryStream mem = new MemoryStream(); XmlTextWriter myWriter = new XmlTextWriter(mem, Encoding.ASCII); xslt.Transform(xmlDoc, myWriter); mem.Position = 0;You can now point the WebBrowser object's DocumentStream property at this stream, which causes the browser to load and render the HTML.
WebBrowser1.DocumentStream = mem;
The next thing to do is to make sure that this gets called with a default value when the control is first loaded. You can do that in the StockResearch class constructor like this:
public StockResearch() { InitializeComponent(); GetUpdatedData("YHOO"); }Adding the Action Pane to the Spreadsheet
Now that you have a working action pane, you need to add it to your spreadsheet, and trigger it to activate when you use your spreadsheet.
Within Sheet1.cs, you need to declare the action pane by making it a member variable of the class and activating it on startup as shown below:
public partial class Sheet1 { StockResearch sr = new StockResearch(); private void Sheet1_Startup(object sender, System.EventArgs e) { Globals.ThisWorkbook.ActionsPane.Controls.Add(sr); }Now, you can trigger an update to the action pane whenever the user changes an item on the sheet by calling the update method from the Sheet1_Change event. It's very simple, you just pull the value of the Target argument passed to that event-handler, which is the selected range in Excel. You then pass this Range to the StockResearch research pane's GetUpdatedData function which gets the data and updates the Excel pane. You will then see the results from Figure 2.
private void Sheet1_Change( Microsoft.Office.Interop.Excel.Range Target) { string strTicker = Target.Text.ToString(); sr.GetUpdatedData(strTicker); } }As with Outlook, you can use Excel as a terrific vehicle for delivering your applications. In a situation where you have to deliver research data to your users, they may already be using Excel, and as such by using it, not only will you gain the benefits of a desktop runtime environment that you can deliver your app to, but you'll also be able to enhance their user experience by keeping your application and data inline with theirs. This article presented an example scenario where financial users might be using spreadsheets to manage their portfolios. Using VSTO, you can deliver your data enhancements directly into their spreadsheets by capitalizing on the Action Pane concept. And the best part is that doing so is both straightforward and uses your existing .NET coding skills?developing user controls. In other words, you don't even need to learn a new API to build Action Panes within Office apps.
