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 specialjust 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:
The 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 '>
strQuote = strQuote.Replace(">", ">");
strQuote = strQuote.Replace("<", "<");
node in the returned XML contains lots of hard-to-decode inline HTML, and you don't want to use that directly in the document, as it breaks XML validation (HTML isn't well-formed XML). Here's how to remove it from the string representing the XML.
int nStart = strQuote.IndexOf("<Change>");
int nEnd = strQuote.IndexOf("</Change");
strQuote = strQuote.Remove(nStart, nEnd - nStart + 9);
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 = "<StockQuote>" + strQuote;
strQuote = strQuote + "</StockQuote>";
Next, you need to load the string into an XmlDocument object to parse it.
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();
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);
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: