Harness the Power

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 OfficeSystem 2005 (VSTO). In fact, I d venture a guess that some of you may havetoyed with it or even started serious development based on the tool suite. Forthose of you who haven t, Visual Studio Tools for Office System 2005 allowsdevelopers to harness the power of Microsoft Office in enterprise-levelapplications.

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

The marketing hype baked into VSTO claims that datacontained inside VSTO-based applications can be manipulated without the need tostart an instance of Microsoft Office on the server. It takes a lot of gall tomake such a claim especially when history demonstrates rather clearly thatsoftware applications that automate Microsoft Office on the server do not scalewell.

So let s put this claim to the test. The idea is todevelop an application that houses some data. We ll then create anotherapplication that can access and modify the data. Finally, we ll write a thirdpiece that simply monitors the server for an instance of Microsoft Office. Ifan instance is detected, our watchdog process will bark rather loudly exposingthe 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 isrunning.");

         Console.Read();

         Marshal.ReleaseComObject(automator);

       }

       catch(System.Runtime.InteropServices.COMException)

       {

         //Microsoft Wordis not running

         Console.WriteLine("Watching...");

       }

     }

   }

 }

}

Figure 1: Watchdogprocess code.

The watchdog application is conceptually simple. A whileloop drives the process. During each iteration, the code searches for aninstance of Microsoft Word. If no instance exists, the application prints anappropriate message and continues monitoring. To test watchdog, compile andfire the application. While the application is running, open Microsoft Word andwatch this bad boy spring into action. Remember, for a Microsoft Officeautomation application to scale well, it must necessarily avoid creating aninstance of Microsoft Word on the server.

With the trap set, let s see if we can dangle some livebait in the hope of attracting something big. The code to create and house theVSTO-based data is shown in Figure 2. Create a new VSTO-based project and enterthis 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 classThisDocument

 {

    [Cached]

   public DataSet data;

   private voidThisDocument_Startup(object sender, System.EventArgs e)

   {

     data = new DataSet();

     data.ReadXml("sampledata.xml");

   }

   private voidThisDocument_Shutdown(object sender, System.EventArgs e)

   {

   }

 }

}

Figure 2:Application data.

This application sets up the necessary plumbing thatallows data to be stored inside a VSTO-based application. The first line ofcode declares a public dataset with a cached attribute. The cached attributeinforms VSTO that the data contained in the dataset should be stored in aspecial 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 froma data store and apply some sort of business logic to the data. This isn t thereal world, so we ll skip the business logic piece. Our final piece of codesimply tries to access the data; see Figure 3.

Public void ManipulateData()

{

 DataSet newDataSet;

 // point to the doc filein the debugger directory

 String fileName ="C:WordDocument1bindebugWordDocument1.doc";

 if(ServerDocument.IsCacheEnabled(fileName))

 {

   ServerDocument servDoc= null;

   try

   {

     servDoc = newServerDocument(fileName);

     newDataSet = newSystem.Data.DataSet();

     // grab the namespaceand the class that contains the cached data

     CachedDataHostItemhostI =

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

     CachedDataItem dataI= hostI.CachedData[0];

      // load the data

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

     {

       System.IO.StringReader xmlReader =

        newSystem.IO.StringReader(dataI.Xml);

        System.IO.StringReader schemaReader =

        newSystem.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 somearbitrary information

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

            {

               row[0] = "my new value goeshere";

             }

         }

        dataI.SerializeDataInstance(newDataSet);

        servDoc.Save();

     }

  }

  finally

  {

      if (servDoc != null)

          servDoc.Close();

  }

 }

}

Figure 3: Applicationto manipulate data.

In the real world, this piece of software may representanother business object that must apply some business logic to the data. Thecode isn t that difficult to follow. First, the code tests to see if the VSTO-basedapplication contains a data cache. The data cache is a new container that isable to access and manipulate the data inside a VSTO-based application. If thedocument supports VSTO data caching, an instance of the ServerDocument class iscreated (this is a special class that is able to manipulate the actual datainside 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-basedMicrosoft word document is no longer stored inside the worddocument1.doc file; itis now stored in a special container accessible through the ServerDocumentclass using the syntax mentioned here.

Once access to the data is obtained, the code can simplyread the data into a dataset and manipulate it. Finally, the ServerDocumentclass Save method is used to write the changed data in the dataset back intothe application store.

So let s fire it up. Run the application watchdog first tobegin monitoring for an instance of Microsoft Office. Then run the code in Figure2 so that data can be loaded. Finally, fire up the code in Figure3 so that the data can be manipulated. All we need to do is sit tightand be patient. Sooner or later, Microsoft Office will appear to help in theautomation. Patience ...

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

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

The code presented demonstrates that the divorcedarchitecture actually works and is scalable although we haven t tested the scalabilityclaim. However, there are a couple of drawbacks to this silver bullet. The datamust be created using Visual Studio Tools for the Office System 2005 because itneeds to support data caching. This is quite a shortcoming because itnecessarily means that you must migrate yourapplications to VSTO solutions first if you intend to take advantage of datacaching. Also, data caching is only supported in Microsoft Word and MicrosoftExcel. There is no support for data caching in Microsoft InfoPath or MicrosoftOutlook.

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

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