Harness the Power

Visual Studio Tools for Office System 2005

asp:Feature

LANGUAGES: ALL

ASP.NET VERSIONS: 2.0

 

Harness the Power

Visual Studio Tools for Office System 2005

 

By Alvin Bruney

 

Perhaps you ve heard of Visual Studio Tools for Office System 2005 (VSTO). In fact, I d venture a guess that some of you may have toyed with it or even started serious development based on the tool suite. For those of you who haven t, Visual Studio Tools for Office System 2005 allows developers to harness the power of Microsoft Office in enterprise-level applications.

 

Currently, the VSTO tool suite supports Microsoft Excel, Word, InfoPath, and Outlook. These applications may be built using either C# or Visual Basic.NET (Visual Basic). While most of the development targets Windows forms and thick-client development, one important piece focuses on server-side development.

 

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 takes a lot of gall to make such a claim 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. We ll then create another application that can access and modify the data. Finally, we ll write a third piece that simply monitors the server for an instance of Microsoft Office. If an instance is detected, our watchdog process will bark rather loudly exposing the fraud. The code for the watchdog process is shown in Figure 1.

 

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...");

       }

     }

   }

 }

}

Figure 1: 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. If no instance exists, the application prints an appropriate message and continues monitoring. To test watchdog, compile and fire 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.

 

With the trap set, let s see if we can dangle some live bait in the hope of attracting something big. The code to create and house the VSTO-based data is shown in Figure 2. Create a new VSTO-based project and enter this code.

 

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

{

 public partial class ThisDocument

 {

    [Cached]

   public DataSet data;

   private void ThisDocument_Startup(object sender, System.EventArgs e)

   {

     data = new DataSet();

     data.ReadXml("sampledata.xml");

   }

   private void ThisDocument_Shutdown(object sender, System.EventArgs e)

   {

   }

 }

}

Figure 2: Application data.

 

This application sets up the necessary plumbing that allows data to be stored inside a VSTO-based application. The first line of code declares a public dataset with a cached attribute. The cached attribute informs VSTO that the data contained in the dataset should be stored in a special way so that it is accessible outside the application.

 

Next, the code loads some arbitrary data, sampledata.xml, into the dataset. In the real world, this application would typically read from a data store and apply some sort of business logic to the data. This isn t the real world, so we ll skip the business logic piece. Our final piece of code simply tries to access the data; see Figure 3.

 

Public void ManipulateData()

{

 DataSet newDataSet;

 // point to the doc file in the debugger directory

 String fileName = "C:\WordDocument1\bin\debug\WordDocument1.doc";

 if (ServerDocument.IsCacheEnabled(fileName))

 {

   ServerDocument servDoc = null;

   try

   {

     servDoc = new ServerDocument(fileName);

     newDataSet = new System.Data.DataSet();

     // grab the namespace and the class that contains the cached data

     CachedDataHostItem hostI =

      servDoc.CachedData.HostItems["WordDocument1.ThisDocument"];

     CachedDataItem dataI = hostI.CachedData[0];

      // load the data

     if (null != dataI.Xml && null != dataI.Schema)

     {

       System.IO.StringReader xmlReader =

        new System.IO.StringReader(dataI.Xml);

        System.IO.StringReader schemaReader =

        new System.IO.StringReader(dataI.Schema);

       newDataSet.ReadXmlSchema(schemaReader);

       newDataSet.ReadXml(xmlReader);

       if(newDataSet != null && newDataSet.Tables !=

          null && newDataSet.Tables.Count > 0)

         {

             // Modify the data by adding some arbitrary information

            foreach (DataRow row in newDataSet.Tables[0].Rows)

            {

               row[0] = "my new value goes here";

             }

         }

        dataI.SerializeDataInstance(newDataSet);

        servDoc.Save();

     }

  }

  finally

  {

      if (servDoc != null)

          servDoc.Close();

  }

 }

}

Figure 3: Application to manipulate data.

 

In the real world, this piece of software may represent another business object that must apply some business logic to the data. The code isn t that difficult to follow. First, the code tests to see if the VSTO-based application contains a data cache. The data cache is a new container that is able to access and manipulate the data inside a VSTO-based application. If the document supports VSTO data caching, an instance of the ServerDocument class is created (this is a special class that is able to manipulate the actual data inside the application).

 

Notice how the code uses a special naming syntax, WordDocument1.ThisDocument , to access the data. This is because the data that is displayed in a VSTO-based Microsoft word document is no longer stored inside the worddocument1.doc file; it is now stored in a special container accessible through the ServerDocument class using the syntax mentioned here.

 

Once access to the data is obtained, the code can simply read the data into a dataset and manipulate it. Finally, the ServerDocument class Save method is used to write the changed data in the dataset back into the application store.

 

So let s fire it up. Run the application watchdog first to begin monitoring for an instance of Microsoft Office. Then run the code in Figure 2 so that data can be loaded. Finally, fire up the code in Figure 3 so that the data can be manipulated. All we need to do is sit tight and be patient. Sooner or later, Microsoft Office will appear to help in the automation. Patience ...

 

If you have waited for a few days for an instance of Microsoft Office to show, there s no point in waiting anymore. The hype is true! Microsoft Office is not required to manipulate data contained in VSTO-based applications on the server.

 

The cure for this ailment is the new design that separates data from the code that manipulates it. This divorced architecture allows calling code to service data contained inside VSTO-based applications without the need to start an instance of either Microsoft Office or Microsoft Excel. Because an instance of Office is avoided during the automation, then the scalability problems that accompany Office automation disappear entirely.

 

The code presented demonstrates that the divorced architecture actually works and is scalable although we haven t tested the scalability claim. However, there are a couple of drawbacks to this silver bullet. The data must be created using Visual Studio Tools for the Office System 2005 because it needs to support data caching. This is quite a shortcoming because it necessarily means that you must migrate your applications to VSTO solutions first if you intend to take advantage of data caching. Also, data caching is only supported in Microsoft Word and Microsoft Excel. There is no support for data caching in Microsoft InfoPath or Microsoft Outlook.

 

Another drawback is that the VSTO tools suite is not free; it does cost a fair amount of cash. When compared to the regular Office development based on COM, which is essentially free, the cost can seem like an unnecessary investment. Still, if you have a requirement for a highly scalable piece of Office automation software, VSTO is a good alternative.

 

Alvin Bruney is an ASP.NET MVP. He has been involved in .NET technology since the beta version. Look for his new book, Professional Visual Studio Tools for Office 2005 from WROX/Wiley publishing. You can get his previous book, The Microsoft Office Web Components Black Book with .NET, at http://www.lulu.com/owc.

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish