Using ServerDocument and ASP.NET

Many pieces must be put together here, but each one is fairly straightforward. Here is what we are going to do:

1. Create an ASP.NET Web site.
2. Create a simple VSTO customized expense report spreadsheet that has a cached dataset that is data bound to a list object and a cached string assigned to a named range in the Startup handler.
3. Publish the expense report template to the Web site.
4. Create an .aspx page that populates the data island (the cached dataset) before the document is served up.
5. As an added bonus, we adapt that page and turn into a custom file type handler.

In Visual Studio, select File > New > Web Site and create a new ASP.NET site. Suppose for the sake of this example that the server is http://accounting, and the Web site is http://accounting/expenses.

We come back to this Web site project later. For now, close it down and create a VSTO 2005 Excel spreadsheet project. Let's start by putting together a simple customization with one named range and one list object control bound to an untyped dataset. We will make the user's name and the expense dataset cached, so that the server can put the data in the data island when the document is served up. Figure 18-1 shows the spreadsheet with a named range and a list object. You can also see in Figure 18-1 the code behind Sheet1. The code defines a string called EmpName that is cached as well as a DataSet called Expenses that is cached. In the Startup handler for Sheet1, the code sets the Value2 property of the NamedRange called EmployeeName to the cached value EmpName. It also data binds the Expenses dataset to the ListObject called List1.

Figure 18-1. A simple expense report worksheet with two cached class members: EmpName and Expenses.

Choose the Build > Publish menu item and use the Publishing Wizard to build the spreadsheet and put it up on http://accounting/expenses. Doing so sets up the document so that it points to the customization on the Web server rather than the local machine. (Chapter 20, "Deployment," covers deployment scenarios in more detail.)

For the customization to run on the client machine, you need to have a security policy set to trust the server. Chapter 19, ".NET Code Security," covers the whys and wherefores of security policy issues in detail; for now, just trust us that you need a security policy. On the client machine, you can use the command-line CASPOL.EXE tool or the MSCORCFG.MSC management tool to create a machine-level policy that grants full trust to the customization DLL. Here we use CASPOL.EXE to add a new policy that trusts content from a directory on the accounting Web server:

> caspol -ag 1.2 -url http://accounting/expenses/* FullTrust Microsoft (R) .NET Framework CasPol 2.0 Copyright (C) Microsoft Corporation. All rights reserved. The operation you are performing will alter security policy. Are you sure you want to perform this operation? (yes/no) yes Added union code group with "-url" membership condition to the Machine level. Success

Just to make sure that works, tell CASPOL to display the security policy:

> caspol -lg Microsoft (R) .NET Framework CasPol 2.0 Copyright (C) Microsoft Corporation. All rights reserved. Security is ON Execution checking is ON Policy change prompt is ON Level = Machine Code Groups: 1. All code: Nothing 1.1. Zone - MyComputer: FullTrust 1.1.1. StrongName -: FullTrust 1.1.2. StrongName -: FullTrust 1.2. Zone - Intranet: LocalIntranet 1.2.1. All code: Same site Web 1.2.2. All code: Same directory FileIO - 'Read, PathDiscovery' 1.2.3. Url - http://accounting/expenses/*: FullTrust 1.3. Zone - Internet: Internet 1.3.1. All code: Same site Web 1.4. Zone - Untrusted: Nothing 1.5. Zone - Trusted: Internet 1.5.1. All code: Same site Web Success

We have not set up the handler on the server yet, but do a quick sanity check on the client to make sure that the document can be downloaded and the customization run on the client machine. There will not be any data in it yet; let's take care of that next.

Setting Up the Server

Use Visual Studio to open the expenses Web site created earlier, and you will see that the deployed files for this customized spreadsheet have shown up. Now all we need to do is write a server-side page that loads the blank document into memory and fills in its data island before sending it out over the wire to the client. Right-click the Web site and choose Add New Item. Add a new .aspx Web form.

We need to add a reference to Microsoft.VisualStudio.Tools.Applications. Runtime.DLL to get at the ServerDocument class. After we do that, the code is fairly straightforward right up until the point where we set the serialized state. We discuss how that works in more detail later in this chapter.

Listing 18-1. An ASPX Web Form That Edits the Data Island on the Server

<%@ Page Language="C#" AutoEventWireup="true" %> <%@ Import Namespace="System.Configuration" %> <%@ Import Namespace="System.Web.Configuration" %> <%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.Common"%> <%@ Import Namespace="System.Data.OleDb"%> <%@ Import Namespace="System.IO"%> <%@ Import Namespace= "Microsoft.VisualStudio.Tools.Applications.Runtime"%>

An Alternative Approach: Create a Custom Handler

It seems a little odd to go to an .aspx page to download a spreadsheet or document. An alternative approach to solving the problem of customizing documents on the server is to intercept requests for particular file extensions and customize the response before it goes out to the client.

This time, instead of creating a new .aspx Web form, create a new .ashx handler (see Figure 18-2).

Figure 18-2. Creating a custom handler item.

The code is essentially identical; the only difference is that because a handler is not an instance of a Web page, we do not have any of the standard page objects such as Response, Request, User, and so on. Fortunately, the context of the page request is encapsulated in a special "context" object that is passed to the handler.

Listing 18-2. Creating a Custom Handler That Edits the Data Island

<%@ WebHandler Language="C#" %> using System; using System.Data; using System.Data.Common; using System.Data.OleDb; using System.IO; using System.Web; using Microsoft.VisualStudio.Tools.Applications.Runtime; public class XLSHandler : IHttpHandler { const int Forbidden = 403; public void ProcessRequest (HttpContext context) { if (!context.User.Identity.IsAuthenticated) { context.Response.StatusCode = Forbidden; context.Response.End(); return; } DataSet dataset = new DataSet(); DataTable datatable = dataset.Tables.Add("Expenses"); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = new OleDbCommand("SELECT [Date], " + "Description, Cost FROM Expenses WHERE EmployeeName = "" + context.User.Identity.Name + """); string connectionString = ConfigurationManager. ConnectionStrings["expenses"]. ConnectionString; adapter.SelectCommand.Connection = new OleDbConnection(connectionString); adapter.Fill(datatable); FileStream file = new FileStream( @"c:INetPubWWWRootexpensesExpenseReport.XLS", FileMode.Open, FileAccess.Read); byte[] template; try { template = new byte[file.Length]; file.Read(template, 0, (int)file.Length); } finally { file.Close(); } ServerDocument sd = new ServerDocument(template, ".XLS"); try { sd.CachedData.HostItems["ExpenseReport.Sheet1"]. CachedData["EmpName"].SerializeDataInstance( context.User.Identity.Name); sd.CachedData.HostItems["ExpenseReport.Sheet1"]. CachedData["Expenses"].SerializeDataInstance(dataset); sd.Save(); // "template" still has the original bytes. Get the new bytes. template = sd.Document; } finally { sd.Close(); } context.Response.ContentType = "application/vnd.ms-excel"; context.Response.OutputStream.Write(template, 0, template.Length); } public bool IsReusable { get { return false; } } }

Finally, to turn this on, add the information about the class and assembly name for the handler to your Web.config file in the application's virtual root. If you want to debug the server-side code, you can add debugging information in the configuration file, too.

Listing 18-3. A Web Configuration File to Turn on the Handler

Now when the client hits the server, the handler will intercept the request, load the requested file into memory, contact the database, create the appropriate dataset, and serialize the dataset into the data island in the expense reportall without starting Excel.