In the previous installment we saw how to create a COM server for use by Microsoft Excel. We will now look at how we can use this technology to create what Excel calls a ”Real-time Data”, or RTD server. An RTD server is called at regularly scheduled intervals to keep data in an Excel worksheet up-to-date. The default interval is one minute. Although one minute is hardly ”real-time”, the interval should never be less than this, or Excel will spend so much time updating it won’t be useful for much else.
An RTD server can provide any sort of data to Excel, whether from a database, the results of some calculation, or from an internet service. We will choose the latter as our example here. This means we shall have to take a digression (the agile folks would say ”spike”) to review web services before we can get down to the business of creating an RTD server.
As people find more and more uses for the internet, web service technologies have grown as well. We will choose as our example what might be called a ”typical” web service using SOAP, the simple object access protocol. In the Visual Studio, the tools for utilizing a web service have changed with Microsoft’s introduction of the Windows Communication Foundation, or WCF. WCF services can be local, on a company LAN, or on the web. The classic web service is now only a subset of services that can be integrated into a C# program using the Visual Studio.
We will chose as our example a web service that provides a recent stock quote when provided with a ticker symbol. This web service is not for commercial use; it is only an example to illustrate how to use web services in a .NET program. The potential difficulty with web services is that the XML used to communicate data over the web would be unpleasant to construct and parse in a normal request and response transaction with the service. Fortunately, the Visual Studio will generate this code for us automatically. This is possible due to WSDL, the web services description language. A web service provides a formal XML description of what it provides; the Visual Studio uses this information to create the additional C# code we need. This code is wrapped as a proxy, which is to say we get what we need by invoking methods of the proxy object in our local code , and the proxy object object runs to the web service to do the actual work.
We start by creating a Visual Studio solution with two projects: a dll library project that does the work, and a console application project for testing purposes. We’ll call the library project WebServiceTest. The key to our library is adding a service reference.
In the Add Service Dialog window, we enter the url of the web service and hit ”Go”. The dialog will then access the website and report the protocols supported by the service and the methods supported by each protocol. We will use the following url for non-commercial testing purposes only.
To obtain this information , the Visual Studio retrieves information from the wsdl file provided by the website. Sometimes, the wsdl information provided by the website does not match what is expected by the Visual Studio. In such cases, we can often achieve success by falling back to the classic web service interface of the Visual Studio. We shall illustrate that here.
To create a classic web reference, we click on the ”Advanced” button, which will take us to the service references settings dialog, which has an ”Add Web Reference…” button. The classic add web reference dialog behaves much the same as the add service reference dialog. We enter the url and click the tiny arrow to the right. We then enter the name we would like to use for our proxy class.
When we click ”Add Reference”, Visual Studio will read the information in the site’s wsdl file to create the proxy class. In addition, Visual Studio will automatically add references to the required libraries System.EnterpriseServices and System.Web.Services.
As is often the case, the Visual Studio hides its work by default. If we would like to see what VS has done, we can go to the Solution Properties window and click the ”Show All Files” button at the top. We can then, if we like, examine the code in the automatically generated C# file ”Reference.cs”.
The hard part is done. The only thing that remains is to write some code to access the web service, and of course, to test it. This is easy, we create a method in a public class that invokes the StockQuote method of the proxy class. Just looking at this line of code, there would be no way to tell that the information is actually being retrieved from a web service.
public string GetQuote(string Symbol)
// ”Classic” web service code
WebStockQuote.StockQuote wsqs = new WebStockQuote.StockQuote();
Since we are just illustrating general principals, error handling code has been omitted for clarity.
To test this code, we need only add some code to the console test application to instantiate the public class and invoke the method, in this case called ”GetQuote”. The example code provides the symbol for Intel Corporation as a default if the test does not provide a command line argument.
Note that there is a great deal of information here, but it is wrapped in XML. A practical application would have to parse this XML to extract the pertinent data.
A completed version of the Visual Studio 2012 project can be downloaded here.
Creating a .NET library to access a web service is easy. Easier, in fact, than parsing the resulting data once we retrieve it. In the next installment we will see how to use what we have learned about COM in the previous section to integrate this web service library into Microsoft Excel.