Introduction
VSTO2005 has been a great relief to .NET office developers as a result of its enhanced features. It's been a long time desire for office programmers to have something which would provide them an interface to program the office components just like any other interactive forms. Amongst the prime features that VSTO2005 has offered, the actions pane has been the best and the most wanted one. The ease of programming the task pane, the ease of its usage and the fact that to achieve the same functionality in VSTO2003 would take hundreds of lines of code has made it undoubtedly the best feature of VSTO2005.
Approach
Now let's get it going! In a nut shell I would categorize the article in to the following stages:
- Build a excel based test application which will utilize the actions pane. This would involve the following sub steps:
- Build a test user control application.
- Embed the user control in the VSTO task pane.
- Deploy the so built application. This involves the following sub steps:
- Deploy the project assembly on a web server
- Bind the manifest information to the excel template accordingly.
- Configure the Code Access Security (CAS) policies on the machine where the Excel template is deployed.
The prerequisites required to build and deploy this application are mentioned below:
Prerequisites on the Development machine are:
- Microsoft Visual Studio 2005, Visual Studio tools for Office 2005 (VSTO2005), Microsoft Excel 2003 (with Primary interop assemblies(PIA's))
- Microsoft Office 2003 (the PIA's (Primary interop assemblies) are required too but if you have the .NET framework installed first then office will automatically install the PIA's.So no need to explicitly install the PIA's).
- VSTO2005 runtime
Prerequisites on the end users machine are:
- .Net Framework 2.0
- Microsoft Office 2003 (the PIA's (Primary interop assemblies) are required too but if you have the .NET framework installed first then office will automatically install the PIA's.So no need to explicitly install the PIA's).
- VSTO2005 runtime
Let's first build a simple excel based application using VSTO2005.
Step 1: Open your Visual Studio 2005 IDE and click on New Project. Select the project type as visual basic->office->Excel Workbook Provide a name for it. Here the name is VSTO Example as shown below:
Step 2: Next you will be asked if you want to create a new document or use a existing document. Select the "Create a new document" option and use the default name (Here it is VstoExample). The screen shot is as shown below:
Step 3: Click on Ok and you will see an Excel based project created which will look as shown below in fig 3:
Step 4: Then before actually getting in to any code in the excel template, we need to create a simple user control that will be used by the actions pane of the template. So create another project of type user control. Drag and drop a button on to the user control. We need to declare an event in the user control that will be handled in the excel template code. Ideally the user control project should look as below:
Note that we have declared a simple event named "ClickButton" which is raised in the button click event of the button which we dragged and dropped on to the user control. The event is raised using the "RaiseEvent" keyword (This is again a VB.NET project). The Event declaration and the RaiseEvent statements have been encircled in the figure shown above.
Step 5: Now compile the user control project and place the user control DLL (NControl.DLL in this case) in the bin folder of the VstoExample project that you created in steps 1 & 2.
Step 6: Now let's get back to the excel project. Add a reference to the above created user control to your excel project and make sure that the user control is included in the "designer" definitions. The process is as shown in the figure below:
Note: The User control reference and the user control definition with events has been encircled in the figure above.
Step 7: Now lets write some code in the excel template that is going to show up the VSTO actions pane and add the user control we created to the task pane at run time. Double click on "thisworkbook.vb" and include the code in the workbook start up event as shown in the figure below:
Note: The Key words and Key statements have been encircled in the figure shown above.
Step 8: The next step is to implement the ClickButton event that was defined in the user control. Define an event handler in the excel template that will handle the clickbutton event of the user control. This can be done as shown in the figure below:
Step 9: The next step is to compile the project and generate the main assembly. The assembly will be named VstoExample.dll if you have followed the same names as my sample. Now we have completed all the steps that are required building the sample VSTO2005 application. Run the project and you will see the actions pane show up with the user control button "ClickMe" on it. Click on the button you will see a message "You clicked the user control button" as shown below:
Now we have successfully built and executed the sample program. The next challenge is to deploy the application on end user's machine.
Deploying the built excel application on a end users machine:
I will explain these in steps as above:
Step 1: First we need to create a virtual directory using IIS which will host all the necessary DLL's required by the sample VSTO application. Create a Virtual Directory named "TESTVSTO" in your IIS and place the necessary DLL's (in this case it is the main assembly "VstoExample.DLL" and the User Control DLL "NControl.DLL") in the directory mapped to this virtual directory. The series of steps are shown in the Snapshots below:
Step 2: Now we need to bind the manifest information to the assembly. This information basically tells the excel template on the end users machine where exactly to look for the DLL's that will be loaded on to the client side when the end user opens the excel template. Follow the steps given below in the bulleted points to create a simple windows based application to bind the manifest information to the excel template: Create a windows form based application using your Visual Studio IDE. Drag and Drop a button on to the form as shown:
Add a reference to the "Microsoft.VisualStudio.Tools.Applications.Runtime.dll" assembly as shown in the figure above. The assembly reference has been encircled in the figure above. Add the following code on the button click of the button you dragged on to the form:
Note: The key words and statements are encircled in the above figure. The code reads as below:
Line 1: objServerDocument = new ServerDocument(@"D:\VstoExample.xls");
Explanation: Here the path "D:\VstoExample.xls" is the "compiled excel template" that is taken from the "bin\debug" or the "bin\release" folder of the sample excel project that we created in the beginning of this article. You can place the excel template any where on your machine , and change the path accordingly while instantiating the server document object.
Line 2: objServerDocument.AppManifest.Dependency.AssemblyPath = @http://TestServer/TESTVSTO/VstoExample.dll;
Explanation: This line indicates that the assigned path is path that the compiled excel template should look for to load the main assembly and its dependencies. TestServer -> WebServer Name on which the virtual directory was created. TESTVSTO -> Name of the Virtual directory that we created.
Line 3: objServerDocument.Save();
Explanation: This line is to save the information bound to the excel template that will be deployed on the end users machine. Do not forget to call the close method of the serverdocument object (refer figure above) after the manifest information is bound to the excel template. Run the program and click on the BindManifest Button to bind the manifest information to the excel template that we intend to deploy on the end users machine.
Step 3: The last step in the deployment process is to configure the Code access security (CAS) information on the end user's machine.
I would like to share important information. In earlier versions of .NET (prior to .NET Framework 2.0) the "Microsoft .NET Framework 1.1 Configuration" utility was a part of the frame work installation , but in .NET 2.0 this utility has been shipped as a part of the .NET SDK 2.0 and is no more a part of the 2.0 Framework. This utility is used to configure and view CAS groups and permissions sets manually through a user interface. The size of the .NET SDK is more than 300MB. So are you are wondering that if you need to have the SDK installed on your clients machine? OOPS! Not Really! There is a easy way to achieve this. The friendly CASPOL.exe utility. This utility is shipped along with the .NET framework 2.0 as it was the case with 1.1.
We now are going to create a .BAT file that is going to configure the necessary CAS policies on the end-user's machine.
Creating a .BAT File for configuring CAS on End Users Machine:
- Open a Notepad instance on your machine
- Type in the code as shown in to figure below :
- The code reads as :
start /wait C:\WINNT\Microsoft.NET\Framework\v2.0.50727\caspol -addgroup 1 -url http://TestServer/TESTVSTO/* FullTrust -name ParentGroup
start /wait C:\WINNT\Microsoft.NET\Framework\v2.0.50727\caspol -addgroup ParentGroup -url http://TestServer/TESTVSTO/* Execution -name ChildGroup
Note that the path for caspol.exe has been given as "C:\WINNT\Microsoft.NET\Framework\v2.0.50727\caspol", this path needs to be changed based on the drive where you have installed the .NET framework 2.0 on the end users machine. For my example I have it in the standard default path.
Save the file as "VSTOCAS.BAT" file. -> Double click the "VSTOCAS.BAT" file and you will see the script asks for a confirmation to create the CAS groups as shown in the snap shots below:
Type in "Y" or "yes" The same screen will be seen twice one for the "FullTrust" (ParentGroup) Permission and another for the "Execution" (ChildGroup) Permission. Type in "Y" or "YES" for both the cases. The necessary code groups are now created. To view if they have been created properly execute the "caspol -lg" command in the visual studio command prompt and you will see the code groups that you created above listed. Refer the figure below:
We are done! Now copy the excel template to which the manifest information was bound on to the end users machine where you created the CAS groups as mentioned above. Open the excel template by double clicking the same, There you go! You will see the VSTO action pane loaded with the user control button. Click on the button to see the "You clicked the user control button!" message! The snap shot is as shown below:
Conclusion:
VSTO2005 offers a range of new features which had made life easier for Office developers. This article has showcased one such feature and the deployment of the smart client application that show cases the feature.
Points of Interest:
In the above case we deployed the assemblies on a Webserver. The assemblies can also be deployed on a shared folder on a network. Configuring CAS groups on a end users machine using a .BAT file is one of the best/simplest ways of achieving the same.