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 a bonus, we adapt that page and turn it 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, 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 Build > Publish, 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 that 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. For now, take a look at the code in Listing 18.1.

Listing 18.1. An ASPX Web Form That Edits the Data Island on the Server

<%@ Page Language="VB" 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"%> <script runat=server>   Const Forbidden As Integer = 403   Protected Sub Page_Load(ByVal sender As Object, _       ByVal e As EventArgs)     ' If the user is not authenticated, then we do not want     ' to give the user any expense report at all.     If Not User.Identity.IsAuthenticated Then       Response.StatusCode = Forbidden       Response.End()       Return     End If     ' If we do have a username, fetch the user's personal data     ' from the database (or Web service or other data source).     Dim dataset As DataSet = New DataSet()     Dim datatable As DataTable = dataset.Tables.Add("Expenses")     Dim adapter As OleDbDataAdapter = New OleDbDataAdapter()     ' Authenticated usernames are hard to malform. If there is a     ' chance that a string could be provided by a hostile caller,     ' do not use string concatenation without vetting the string     ' carefully. Better still, avoid SQL injection attacks     ' entirely by using stored procedures.     adapter.SelectCommand = New OleDbCommand( _       "SELECT [Date], Description, Cost " & _       "FROM Expenses WHERE EmployeeName = """ & _       "User.Identity.Name""")     ' It's a good idea to store connection strings in the     ' web.config file both for security  they can be     ' encrypted in web.config  and for convenience      ' you can update the config file when the database server     ' changes.     Dim connectionString As String = _       ConfigurationManager.ConnectionStrings( _       "expenses").ConnectionString     adapter.SelectCommand.Connection = _       New OleDbConnection(connectionString)     adapter.Fill(datatable)     ' We do not want to modify the file on disk; instead,     ' we'll read it into memory and add the user's     ' information to the in-memory document before we serve it.     Dim file As FileStream = New FileStream( _       "c:\INetPub\WWWRoot\expenses\ExpenseReport.XLS", _       FileMode.Open, FileAccess.Read)     Dim template As Byte()     Try       template = New Byte(file.Length) {}       file.Read(template, 0, CType(file.Length, Integer))     Finally       file.Close()     End Try     ' Finally, we'll create a ServerDocument object to     ' manipulate the in-memory copy. Because it only has     ' a raw array of bytes to work with, it needs to be     ' told whether it is looking at an .XLS, .XLT, .DOC,     ' or .DOT.     Dim sd As ServerDocument = New ServerDocument( _       template, ".XLS")     Try       sd.CachedData.HostItems("ExpenseReport.Sheet1"). _         CachedData("EmpName").SerializeDataInstance( _         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()     End Try     Response.ClearContent()     Response.ClearHeaders()     Response.ContentType = "application/vnd.ms-excel"     Response.OutputStream.Write(template, 0, template.Length)     Response.Flush()     Response.Close()   End Sub </script>