Programming Office Applications Using Visual Studio for the Microsoft Office System 2003


Visual Studio Tools for the Microsoft Office System 2003 is a set of technologies that allows .NET developers to build interactive Office-based solutions. Office applications have been programmable using VBA (Visual Basic for Automation) technology, which allows a developer to attach code to office documents. Visual Studio Tools for Office (also sometimes known as VSTO) provides .NET developers with the flexibility of managed code (in particular, VB.NET and C#) to be used as the code-behind programming language for Word- and Excel-based applications. VSTO provides full access to the Word and Excel object models (using IntelliSense for code completion). The managed code is built as DLL, which can be stored on the user 's hard disk or a network share or on a corporate intranet or secured intranet site. Because managed code is used, all the benefits of .NET Framework security apply. For instance, this greatly helps protect Office documents from unknown or untrusted code. Also, utilized by VSTO is the .NET Framework's no-touch, no-registration based deployment. Rebuild the DLL and the application is launched with the newer version.

WHAT ABOUT VBA?

VBA developers should find switching over to Visual Studio Tools for Office System relatively easy because the APIs are very similar. Also, for compatibility reasons and for flexibility, Microsoft continues to support using VBA for Office applications


You should install VSTO (Visual Studio Tools for Office) after you have installed Visual Studio .NET 2003 and Office System 2003, in that order. This is required because VSTO uses the Primary Interop Assemblies (PIAs) that have been registered into the GAC by the Office 2003 Installation program. On clients , VSTO-based applications require .NET Framework 1.1 and Office 2003. Note that Office 2003 (specifically Word and Excel products) should be either installed in the Complete selection or by specifically selecting the .NET Programmability Support option in the installation program.

VSTO allows .NET application developers to utilize and integrate Excel's calculation and charting engines and Word's word-processing functions with custom business application logic, enabling users to use the UI that they are accustomed to. VSTO provides this by adding three sets of project types for Visual Studio .NET (Excel Workbook, Word Document, and Word Template as shown in Figure 12.7).

Figure 12.7. Visual Studio .NET 2003 templates for Office.

When one of these project types (which are available for both Visual C# and Visual Basic .NET) is selected, the Visual Studio .NET environment generates the basic code required to work with Word and Excel automation objects. Next, you build the associated user interface and customized logic that you would like to add in your assembly. After the application is developed, using the integrated development capabilities, you can build and launch the associated application (Word or Excel). After you have finished the development and testing, you take the generated .NET Assembly file and store it on a corporate file server or Web server. When end users actually use these templates, these assemblies will be located by the Office 2003 application based on the .NET Framework Security Configuration policies that have been set by administrators.

For instance, in this scenario you have developed a simple Excel-based application that allows the user to select customer information and analyze it by using the charting features of Microsoft Excel (see Listing 12.4). The customer information is retrieved from a Web service ”the customer lookup Web service you developed in Chapter 9. After the data is retrieved, the Excel cells are then set to their respective values and formulas.

Listing 12.4 Simple Excel-based Application to Look Up Customer Information
 using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using Excel = Microsoft.Office.Interop.Excel; namespace ExcelProject2 {        public class LookupCustomerForm : System.Windows.Forms.Form        {         private Excel.Workbook thisWorkbook;         public Excel.Workbook ThisWorkbook         {             get             {                 return thisWorkbook;             }             set             {                 thisWorkbook = value;             }         }         private System.Windows.Forms.Label CustomerLabel;         private System.Windows.Forms.TextBox CustomerBox;         private System.Windows.Forms.Button LookupButton;         private System.ComponentModel.Container components = null;         public LookupCustomerForm()         {             InitializeComponent();         }         protected override void Dispose( bool disposing )         {             if( disposing )             {               if(components != null)               {                     components.Dispose();               }             }             base.Dispose( disposing );         }         private void InitializeComponent()         {             this.CustomerLabel = new System.Windows.Forms.Label();             this.CustomerBox = new System.Windows.Forms.TextBox();             this.LookupButton = new System.Windows.Forms.Button();             this.SuspendLayout();             //             // CustomerLabel             //             this.CustomerLabel.Location = new System.Drawing.Point(0, 8);             this.CustomerLabel.Name = "CustomerLabel";             this.CustomerLabel.Size = new System.Drawing.Size(80, 24);             this.CustomerLabel.TabIndex = 0;             this.CustomerLabel.Text = "Customer:";             //             // CustomerBox             //             this.CustomerBox.Location = new System.Drawing.Point(96, 8);             this.CustomerBox.Name = "CustomerBox";             this.CustomerBox.Size = new System.Drawing.Size(184, 20);             this.CustomerBox.TabIndex = 1;             this.CustomerBox.Text = "1";             //              // LookupButton             //             this.LookupButton.Location = new System.Drawing.Point(168, 56);             this.LookupButton.Name = "LookupButton";             this.LookupButton.Size = new System.Drawing.Size(120, 24);             this.LookupButton.TabIndex = 2;             this.LookupButton.Text = "LookUp";             this.LookupButton.Click +=                 new System.EventHandler(this.LookupButton_Click);             //             // LookupCustomerForm             //             this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);             this.ClientSize = new System.Drawing.Size(292, 101);             this.Controls.Add(this.LookupButton);             this.Controls.Add(this.CustomerBox);             this.Controls.Add(this.CustomerLabel);             this.Name = "LookupCustomerForm";             this.Text = "LookupCustomerForm";             this.ResumeLayout(false);         }         private void LookupButton_Click(object sender, System.EventArgs e)         {             localhost.BusinessService proxy = new localhost.BusinessService();             localhost.Customer customer                   = proxy.LookUpCustomer(CustomerBox.Text);             Excel.Worksheet thisWorkSheet =               (Excel.Worksheet)thisWorkbook.Worksheets[1];             thisWorkSheet.Cells[1,1] = "Customer ID";             thisWorkSheet.Cells[1,2] = customer.id;             thisWorkSheet.Cells[2,1] = "Name";             thisWorkSheet.Cells[2,2] = customer.Name;             localhost.Order[] orders = customer.Order;             int i = 5;             thisWorkSheet.Cells[3,1] = "Orders";             thisWorkSheet.Cells[3,2] = "Item";             thisWorkSheet.Cells[3,3] = "Quantity";             thisWorkSheet.Cells[3,4] = "Price";             foreach (localhost.Order order in orders)             {               thisWorkSheet.Cells[i,1] = order.Item;               thisWorkSheet.Cells[i,2] = 1;               thisWorkSheet.Cells[i,3] = order.Price;               thisWorkSheet.Cells[i,4] = 1*order.Price;               i++;             }             thisWorkSheet.Cells[i,4] = "=+SUM(D5:D"+(i-1)+")";         }     } } 

To use this form as an Excel application, it is required to associate creation of the form by an appropriate event captured by the OfficeCodeBehind class (Listing 12.5). Because in this scenario, the actual customer lookup is performed by the Customer Lookup Form object, a reference to the Excel Workbook object is passed to the form for the appropriate callback.

Listing 12.5 CodeBehind Class for Associating the Event Handler
 using System; using System.Windows.Forms; using Excel = Microsoft.Office.Interop.Excel; // Office integration attribute. Identifies the // startup class for the workbook. Do not modify. [assembly:System.ComponentModel.DescriptionAttribute( "OfficeStartupClass, Version=1.0, Class=ExcelProject2.OfficeCodeBehind")] namespace ExcelProject2 {     public class OfficeCodeBehind     {         internal Excel.Application ThisApplication         {             get { return thisApplication;}         }         internal Excel.Workbook ThisWorkbook         {             get { return thisWorkbook;}         }         private Excel.Application thisApplication = null;         private Excel.Workbook thisWorkbook = null;         private Excel.WorkbookEvents_OpenEventHandler openEvent;         private Excel.WorkbookEvents_BeforeCloseEventHandler beforeCloseEvent;         public OfficeCodeBehind()         {         }         public void _Startup(object application, object workbook)         {             this.thisApplication = application as Excel.Application;             this.thisWorkbook = workbook as Excel.Workbook;             openEvent=                new Excel.WorkbookEvents_OpenEventHandler (ThisWorkbook_Open);             thisWorkbook.Open += openEvent;                          beforeCloseEvent =                     new Excel.WorkbookEvents_BeforeCloseEventHandler(                         ThisWorkbook_BeforeClose);             thisWorkbook.BeforeClose += beforeCloseEvent;         }         public void _Shutdown()         {             thisApplication = null;             thisWorkbook = null;         }         object FindControl(string name )         {             return FindControl(name, (Excel.Worksheet)                 ThisWorkbook.ActiveSheet);         }         object FindControl(string name, Excel.Worksheet sheet )         {             Excel.OLEObject theObject;             try             {                 theObject = (Excel.OLEObject) sheet.OLEObjects(name);                 return theObject.Object;             }             catch             {                 // Returns null if the control is not found.             }             return null;         }  protected void ThisWorkbook_Open()   {   LookupCustomerForm lcf = new LookupCustomerForm();   lcf.ThisWorkbook = thisWorkbook;   lcf.Show();   }  protected void ThisWorkbook_BeforeClose(ref bool Cancel)         {         }     } } 
Figure 12.8. Programming Office 2003 using .NET.

This is really what is involved in development of a Visual Studio Tools for Office “based .NET solution. A similar capability is available for Word 2003 as well, where the Word application and Document objects are used.



Microsoft.Net Kick Start
Microsoft .NET Kick Start
ISBN: 0672325748
EAN: 2147483647
Year: 2003
Pages: 195
Authors: Hitesh Seth

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net