Professional VSTO 2005 - Visual Studio 2005 Tools For Office (2006) [eng]
.pdf
Chapter 3
The Calculate method request is handled on a separate thread so that long calculations do not affect the responsiveness of the spreadsheet. If the calculation were not handled on a separate thread, long calculations would cause the spreadsheet to stop responding to end-user requests. For a review on range functionality, see the range section. The calculation engine is probed more deeply in the Excel calculation engine section.
The code presented in Listing 3-27, adds a button to an existing Excel menu. However, if the desired effect is to add a menu bar from scratch, Listing 3-29 will do nicely.
Visual Basic
Dim NewMenuBar as Office.CommandBar = DirectCast Me.Application.CommandBars.Add(“NewMenu”,2, True,True), Office.CommandBar) If NewMenuBar IsNot Nothing Then
Dim NewButton as Office.CommandBarControl = DirectCast( NewMenuBar.Controls.Add(Office.MsoControlType.msoControlDropdown,
Temporary:= True), Office.CommandBarControl) NewButton.BeginGroup = True NewButton.Visible = True NewButton.Caption = “Tooltip text” newMenuBar.Visible= True
End If
C#
Office.CommandBar newMenuBar = (Office.CommandBar) this.Application.CommandBars.Add(“NewMenu”,2, true,true); if (newMenuBar != null)
{
Office.CommandBarControl newButton = (Office.CommandBarControl) newMenuBar.Controls.Add(Office.MsoControlType.msoControlDropdown,
missing, missing, missing, true); newButton.BeginGroup = true; newButton.Visible = true; newButton.Caption = “Tooltip text”; newMenuBar.Visible= true;
}
Listing 3-29: Menu bar creation
The result of this code is shown in Figure 3-10.
Figure 3-10
88
Advanced Excel Automation
The code is slightly different from the code presented in Listing 3-32. Instead of retrieving a reference for the ActiveMenuBar object, the code simply adds the appropriate control to the CommandBars object that is part of the application object. Note that the new commandbar object is created invisible by default. The last line of code is required if you want to see the menu bar.
You may have noticed that the code also fails to check whether or not the item already exists before adding it. This can happen if the application is run twice in succession and you choose to create permanent menus. In this case, the code may fail with an exception if the menu bar exists, because duplicates are not allowed. It’s easy to test for this condition and the implementation is left as an exercise to the reader.
VSTO and Web ser vices
Strictly speaking, VSTO contains no internal support for web services. However, web services are supported through the .NET Framework and Visual Studio .NET. The infrastructure to harness web services in Visual Studio .NET is easy to use. In fact, once the data is retrieved from a call to a web service, the code to load data into the spreadsheet is fairly straightforward and has been presented several times already.
The lack of internal support for web services means that code needs to be written to extract the data from the web service call and parse the contents into the spreadsheet. It’s not a lot of code, but it still requires some effort.
VSTO provides the QueryTable object that may be used in place of a web service call. Query tables are much more powerful and efficient than web services and expose more functionality.
The next example shows how to consume a web service in a VSTO-based application through Visual Studio .NET. You will need to create a VSTO project and name it WebData. Once you have the project created, open the property pages by pressing Ctrl+F4 key combination. From the project hierarchy tree, right-click on the reference node and select Add Web Reference. Figure 3-11 shows the property pages with the web reference option selected.
The Add Web Reference dialog should appear on screen, as shown in Figure 3-12. Type the URL to the web service. This example uses the Amazon web service http://soap.amazon.com/schemas2/ AmazonWebServices.wsdl. If a wsdl document is found at this URL, the web reference name in the right pane is enabled. You may choose to rename the web reference at this time. The example renames com.amazon.soap to Amazon. To complete the process, click Add Reference.
From this point, the namespace Amazon denotes a class that represents a web service. When the service is invoked, data is retrieved from the remote Amazon server. That data is now available to your calling code for use. Listing 3-30 provides a quick example.
89
Chapter 3
Figure 3-11
Figure 3-12
90
Advanced Excel Automation
Visual Basic
Dim search As Amazon.AmazonSearchService = New
WebServices.Amazon.AmazonSearchService()
Dim authorSearch As Amazon.AuthorRequest authorSearch = New Amazon.AuthorRequest() authorSearch.author = “Dan Brown” authorSearch.page = 1
authorSearch.tag = “webservices-20” authorSearch.devtag = “Enter valid token here” Dim results As Amazon.ProductInfo
results = search.AuthorSearchRequest(authorSearch)
If Not results Is Nothing Then ‘use results here
End If
C#
Amazon.AmazonSearchService search = new WebServices.Amazon.AmazonSearchService();
Amazon.AuthorRequest authorSearch = new Amazon.AuthorRequest(); authorSearch.author = “Dan Brown”;
authorSearch.page = 1.ToString(); authorSearch.tag = “webservices-20”; authorSearch.devtag = “Enter valid token here”;
Amazon.ProductInfo results = search.AuthorSearchRequest(authorSearch);
if(results != null)
{
//use results here
}
Listing 3-30: Web services in .NET
Stepping through the code briefly, you will notice that a search object is created. Next, an author object is created, since we intend to query author information. We set the author query to “Dan Brown”, a popular author. The next line of code indicates the number of pages of data we would like returned to us. We then set the devtag property with an Amazon token. The actual request is initiated when the ProductInfo object is created.
You should note that Amazon provides tokens to developers for testing purposes. In order to run the code, you must register for the service using the following URL http://associates.amazon.com/gp/associates/network/reports/main.html.
Please be sure to read the licensing terms of use before registering. Once your registration is complete, you should receive a token via email. For more help accessing Amazon web services, you can download and install the Amazon toolkit from the Amazon website.
You may be disappointed to discover that the approach in Listing 3-35 does not involve VSTO at all. It is purely .NET and Visual Studio working the magic. However, the data that is returned from the web service may be harvested for use in a VSTO application.
91
Chapter 3
In the real world, application functionality isn’t always built so easily. In fact, web services are relatively new and companies have only just begun exposing business services through that kind of interface.
In a number of cases, corporations expose business services through the web using a combination of querystring, Get and Post parameters. For instance, a book vendor may expose book information through a publicly available URI accepting querystring parameters.
Instead of exploring the nuts and bolts through theoretical explanation, we proceed with an example that shows how to retrieve data from a web resource and load the data into an Excel spreadsheet. The idea behind the test application is simple. We write an application to fetch book information from one of the major online book vendors.
The major book vendor will be Alibris. Alibris provides book retrieval information based on the ISBN number through its web interface. The application simply provides a list of books that are available for sale from the Alibris vendor through their web interface. As of this writing, Alibris does not provide a web service interface for book retrieval; however, this information is available by querying a public Universal Resource Identifier (URI) with the required parameters.
From Visual Studio, create your project so that it resembles the spreadsheet shown in Figure 3-13.
Figure 3-13
Name your project AlibrisData. In the Startup method, create a reference to the URL. The implementation is shown in Listing 3-31. Once the Startup event fires, the code will attempt to connect to the service. If it is successful, data will be returned and stored.
92
Advanced Excel Automation
Visual Basic
Public Shared Function GetBookInfoFromIsbn(ByVal isbn As String) As DataSet
If isbn = Nothing OrElse isbn.Trim() = String.Empty OrElse isbn.Length <> 10 Then Throw New ArgumentNullException(“Alibris”, “Parameter cannot be Nothing”)
End If
‘UsedBooksInfo ubInfo = new UsedBooksInfo(); Dim str As System.Text.StringBuilder = New
System.Text.StringBuilder(“http://direct.alibris.com/cgibin/texis/chapters/search/search.xml?”) str.Append(“qisbn=”).Append(isbn)
‘This is where we ask the search engine to get us stuff based on our requirements Dim myWebRequest As System.Net.WebRequest = System.Net.WebRequest.Create(str.ToString())
‘Set the ‘Timeout’ property in Milliseconds. myWebRequest.Timeout = 10000
‘This request will throw a WebException if it reaches the timeout limit before it is able to fetch the resource.
Dim ds As DataSet = New DataSet(“Alibris”) Try
Dim myWebResponse As System.Net.WebResponse = myWebRequest.GetResponse() Dim reader as System.IO.StreamReader
reader = New System.IO.StreamReader(myWebResponse.GetResponseStream()) Dim read As System.Xml.XmlTextReader
read = New System.Xml.XmlTextReader(reader) ds.ReadXml(read)
Catch ex As System.Net.WebException ‘inform the user that there is a problem MessageBox.Show(“Service timed out.”)
End Try Return ds
End Function
C#
public static DataSet GetBookInfoFromIsbn(string isbn)
{
if (isbn == null || isbn.Trim() == string.Empty || isbn.Length != 10) throw new ArgumentNullException(“Alibris”, “Parameter cannot be null”);
//UsedBooksInfo ubInfo = new UsedBooksInfo(); System.Text.StringBuilder str = new System.Text.StringBuilder(“http://direct.alibris.com/cgibin/texis/chapters/search/search.xml?”); str.Append(“qisbn=”).Append(isbn);
//This is where we ask the search engine to get us stuff based on our requirements System.Net.WebRequest myWebRequest = System.Net.WebRequest.Create(str.ToString());
//Set the ‘Timeout’ property in Milliseconds. myWebRequest.Timeout = 10000;
//This request will throw a WebException if it reaches the timeout limit before it is able to fetch the resource.
DataSet ds = new DataSet(“Alibris”); try
{
System.Net.WebResponse myWebResponse = myWebRequest.GetResponse();
93
Chapter 3
System.IO.StreamReader reader = new System.IO.StreamReader(myWebResponse.GetResponseStream()); System.Xml.XmlTextReader read = new System.Xml.XmlTextReader(reader); ds.ReadXml(read);
}
catch (System.Net.WebException ex)
{
//inform the user that there is a problem MessageBox.Show(“Service timed out.”);
}
return ds;
}
Listing 3-31: Web query implementation
The first line of code validates the input. An exception is thrown if the validation fails. While you may think that this approach is draconian in nature, it really is the best possible approach. The code should not proceed to create an expensive remote request with faulty arguments. And, the method should not simply return an error code either. In fact, the exception represents a violation of the assumption that an ISBN number is a valid 10-digit number. When assumptions are violated, exceptions must be thrown. To proceed otherwise is an exercise in creating ill-behaved software. As an important side note, the ISBN length should not be hard-coded to a value of 10 either. The ISBN length is scheduled to be changed to an 11-digit number in 2007, at which point, the code may no longer work as intended.
Next, the url parameter is parsed into a StringBuilder object. A StringBuilder object is used for efficiency because it avoids the temporary objects that are associated with .NET string manipulation. It’s important to note that the reason the code is wrapped in an exception-handling block is because the code sets an explicit timeout on the request. If the request takes longer than the timeout value, the WebRequest object automatically throws an exception.
Again, the assumption made by the WebRequest object is simple. A request within a reasonable time period must have a valid response, otherwise the assumption has been violated and an exception must be thrown. This is good program design enforced internally by the .NET Framework, and you should have the discipline to adopt this approach.
You should notice how the code uses an in-memory stream to read the contents into the dataset instead of writing a file to disk first. Once the data is in the dataset, the regular parsing of the dataset into the spreadsheet can occur. You must write the code to manually bind to the spreadsheet cells. If you care to, you may also use a listobject or a Range control to accept the data. Once the data is in the control, it will appear in the spreadsheet. Listing 3-32 shows the code.
Visual Basic
Dim dt As DataRow
For Each dt In ds.Tables(0).Rows
Dim rng as Excel.Range
rng = Me.Application.Range(“A1”) rng.Value = dt.ItemArray
Next
94
Advanced Excel Automation
C#
foreach (DataRow dt in ds.Tables[0].Rows)
{
rng = this.Application.get_Range(“A1”, missing); rng.Value2 = dt.ItemArray;
}
Listing 3-32: Dataset assignment to Range object
Figure 3-14 shows the application in action.
Figure 3-14
The code provides basic functionality, but it is easy to see that a lot can be added. In particular, the Alibris service accepts a wide variety of parameters that provide more flexibility to the calling application. Additionally, you can customize the user interface to add toolbars to introduce new functionality or simply format the display appropriately using the range formatting techniques discussed in Chapter 2. The application uses ISBNs because ISBN numbers are guaranteed to be unique. But you might consider adding author name and title instead of ISBN numbers.
Another more elegant enhancement would be to request the same information from a different vendor if there is an exception or timeout. This would increase the probability of getting results for the end user. You can test this method by entering an invalid ISBN number to trigger the code that queries for data from a different vendor.
Excel Ser ver Automation
VSTO has been redesigned to work on the server without requiring a running instance of Excel on the server. Server-side automation with Excel was not without pitfalls. In fact, Microsoft specifically recommends against server automation for various reasons. However, customers insisted on processing documents on the server using Microsoft Office components because there was a compelling need to do so. VSTO has been designed to provide a clean solution to this nagging problem.
95
Chapter 3
The marketing hype baked into VSTO claims that data contained inside VSTO-based applications can be manipulated without the need to start an instance of Microsoft Office on the server. It’s a bold claim to make especially when history demonstrates rather clearly that software applications that automate Microsoft Office on the server do not scale well.
So, let’s put this claim to the test. The idea is to develop an application that houses some data on the server. We then create another application that can access and modify the data. Finally, we test the claim by writing a third piece that simply monitors the server for an instance of Microsoft Office. If an instance is detected, our monitor will bark rather loudly. Listing 3-33 shows the code for the monitor.
Visual Basic
Imports System
Imports System.Runtime.InteropServices
Module Module1
Sub Main()
Dim automator As Object = Nothing
While automator Is Nothing
Try
automator = Marshal.GetActiveObject(“Word.Application”)
Console.WriteLine(“Ok, somebody lied to us! Word is running.”)
Console.Read()
Marshal.ReleaseComObject(automator)
Catch
‘Microsoft Word is not running
Console.WriteLine(“Watching...”)
End Try
End While
End Sub
End Module
C#
using System;
using System.Runtime.InteropServices;
namespace WatchDog
{
class Program
{
static void Main(string[] args)
{
object automator = null; while (automator == null)
{
try
{
automator = Marshal.GetActiveObject(“Word.Application”); Console.WriteLine(“Ok, somebody lied to us! Word is running.”); Console.Read();
Marshal.ReleaseComObject(automator);
}
catch (System.Runtime.InteropServices.COMException)
{
//Microsoft Word is not running Console.WriteLine(“Watching...”);
96
Advanced Excel Automation
}
}
}
}
}
Listing 3-33: Watchdog process code
The watchdog application is conceptually simple. A While loop drives the process. During each iteration, the code searches for an instance of Microsoft Word running on the server. If no instance exists, the application prints an appropriate message and continues monitoring. To test the watchdog, compile and run the application. While the application is running, open Microsoft Word and watch this bad boy spring into action. Remember, for a Microsoft Office automation application to scale well, it must necessarily avoid creating an instance of Microsoft Word on the server. Watchdog will monitor the server.
With the trap set, let’s see if we can dangle some live bait in the hope of attracting something big. Here is the code to create and house the VSTO based data. Create a new VSTO-based project and enter the code in Listing 3-34.
Visual Basic
Imports System
Imports System.Data
Imports System.Drawing
Imports System.Windows.Forms
Imports Microsoft.VisualStudio.Tools.Applications.Runtime
Imports Word = Microsoft.Office.Interop.Word
Imports Office = Microsoft.Office.Core
Namespace WordDocument1
Public partial Class ThisDocument <Cached> _
Public data As DataSet
Private Sub New_Startup(ByVal sender As Object, ByVal e As System.EventArgs)
data = New DataSet() data.ReadXml(“sampledata.xml”)
End Sub
Private Sub New_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs)
End Sub End Class
End Namespace
C#
using System; using System.Data;
using System.Drawing; using System.Windows.Forms;
using Microsoft.VisualStudio.Tools.Applications.Runtime; using Word = Microsoft.Office.Interop.Word;
using Office = Microsoft.Office.Core;
namespace WordDocument1
97