Visual Studio Tools for Office: Using C# with Excel, Word, Outlook, and InfoPath [Electronic resources] نسخه متنی

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

Chapter 20, "Deployment," covers deployment scenarios in more detail.)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"%>
<script runat=server>
const int Forbidden = 403;
protected void Page_Load(object sender, EventArgs e)
{
// If the user is not authenticated, then we do not want
// to give the user any expense report at all.
if (!User.Identity.IsAuthenticated)
{
Response.StatusCode = Forbidden;
Response.End();
return;
}
// If we do have a username, fetch the user's personal data from the
// database (or Web service or other data source.)
DataSet dataset = new DataSet();
DataTable datatable = dataset.Tables.Add("Expenses");
OleDbDataAdapter adapter = 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.
string connectionString = 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.
FileStream file = new FileStream(
@"c:\INetPub\WWWRoot\expenses\ExpenseReport.XLS",
FileMode.Open, FileAccess.Read);
byte[] template;
try {
template = new byte[file.Length];
file.Read(template, 0, (int)file.Length);
}
finally {
file.Close();
}
// 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.
ServerDocument sd = 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();
}
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "application/vnd.ms-excel";
Response.OutputStream.Write(template, 0, template.Length);
Response.Flush();
Response.Close();
}
</script>

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).

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:\INetPub\WWWRoot\expenses\ExpenseReport.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

<configuration>
<system.web>
<httpHandlers>
<add verb="GET" path="ExpenseReport.xls"
type="XLSHandler, XLSHandler"/>
</httpHandlers>
<compilation debug="true"/>
</system.web>
</configuration>

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.