Chapter 3: Excel Web Access (Programming Excel Services)

This article is an excerpt from Programming Excel Services by Alvin J. Bruney, from MSPress (ISBN 9780735624078, copyright MSPress 2007, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews. (39 printed pages)

The Microsoft Office Excel Web Access component is one of the primary interfaces for Excel Services and consists of Web Parts. EWA forms part of the Excel Web front end and allows Microsoft .NET Framework applications to hook into Windows SharePoint Services. This chapter will focus on building Web Parts from Microsoft Visual Studio 2005 and installing them on Microsoft Office SharePoint Server (MOSS) 2007 so that they can be used in SharePoint applications. You'll get a feel for configuration and integration as well. The material will also examine some of the new templates that ease the burden of Web Part creation and deployment. You can find more information about Web Parts in the article Introducing the ASP.NET 2.0 Web Parts Framework.

Contents

• An Introduction to Excel Web Access

• Programming with Excel Services Web Parts

• Modifying Web Parts from Office SharePoint Server

• Debugging Web Parts in Visual Studio

• Displaying Workbooks in Web Parts

• Timesheet Application

• Summary

• Additional Resources

Download the code sample that accompanies the book.

An Introduction to Excel Web Access

Chapter 1: An Introduction to Excel Services (Programming Excel Services) introduced Excel Web Access (EWA). EWA services the need to write applications that render in Web browsers. EWA outputs HTML and ECMAScript (JScript, JavaScript) that can be consumed by any Web browser. However, a short list of inconsistencies crop up on different browsers. These inconsistencies are mainly driven by the extent of DHTML support. In any case, common up-level Web browsers should have no trouble displaying the output from Excel Services.

EWA is built on the concept of Web Parts. Web Parts are Web control containers that expose functionality to the client page. EWA Web Parts are built on ASP.NET Web Parts, meaning that the base class for the Web Parts control is the System.Web.UI.WebControls.WebParts.WebPart class. This approach is a departure from previous versions of SharePoint, and it highlights the tighter coupling between ASP.NET and Microsoft Office SharePoint Server 2007.

The Web Parts that you build as an ASP.NET developer from Visual Studio 2005 will be deployed to a server running Office SharePoint Server. Administrators and end users will then be able to add your Web Part to their pages. When the Web Part is invoked, the code that you wrote will be executed in the SharePoint Web page. The Web Part that is being executed exists within the executing context of Office SharePoint Server. The architecture implies that a Web Part has implicit access to Office SharePoint Server page-level resources, such as session and view-state intrinsics.

A SharePoint Web page is simply an ASP.NET Web page that is dynamically compiled at request time and might or might not include content stored in an Office SharePoint Server content database. We will explain exactly what this means later in the chapter. For now, note that after the page has been created, the Web Part code is invoked. The Web Part also contains Microsoft .NET Framework code because it was built using the Microsoft .NET Framework platform. Therefore, a Web Part that is being run on a SharePoint Web page allows .NET Framework code to interact with a Web page on the SharePoint site.

Earlier, we mentioned briefly that dynamically compiled pages can source data from a content database. There are certain advantages to storing a page in a content database as opposed to disk. Some of these benefits include transactional updates of documents and document metadata, consistent backups of documents and document metadata, a programmable storage layer, and deadlock detection and resolution. If you think about it, you should realize that this architecture is aimed at satisfying the need to scale to large numbers of users. You should also be pleased to learn that the architecture handles low to moderate loads comfortably as well.

Programming with Excel Services Web Parts

Office SharePoint Server comes stocked with a number of Web Parts, as shown in Figure 3-1. Figure 3-1 shows a sampling of Web Parts that can be added to a Web page in Office SharePoint Server.

If these Web Parts don't suit your need, you can build your own custom Web Parts. Custom Web Parts can perform any function that the developer empowers them to fulfill. For instance, custom Web Parts can draw charts, exchange data, provide search functionality, or read files. Later chapters will show examples of each of these functions. In addition, several vendor-supplied Web Parts can be downloaded or purchased for use. The Microsoft Office Web site also offers Web Parts that can perform analysis services and other tasks.

Before blindly setting out to write Web Parts, you first need to figure out the range of functionality that is built in. If these Web Parts don't suit your fancy and you can't customize their behavior, you will need to build your own. The rest of the chapter is dedicated to expanding your knowledge in that direction.

Let's get started with a HelloWorld Web Part. The Web Part simply writes some text—specifically, "Hello world"—to the client. We then deploy the assembly to a server running Office SharePoint Server. After the assembly has been deployed, you add the Web Part to a page on the SharePoint site so that the piece of text can be displayed. You perform this sequence of steps every time a Web Part is authored or updated. By default, you can't simply build the Web Part and refresh the Web page like you do in ASP.NET.

Figure 3-1. Office SharePoint Server default Web Parts

In the next few sections, we break up the sequence of steps so that you can learn each piece and gain appreciation for how it all fits together. Separating each step into its own topic also allows you to refer back to individual sections if you run into trouble later on.

Manual Creation of Web Parts

As an ASP.NET developer, you might be disappointed to learn that Visual Studio 2005 does not ship with a development environment that allows developers to create Web Parts that can be deployed to Office SharePoint Server. It also does not have integrated development environment (IDE) design-time support for Web Part creation. The implication here is that you cannot drag and drop controls on a design surface to create Office SharePoint Server Web Parts. You have to construct the Web Part by hand.

The exercise to build Web Parts in this fashion is similar to classic ASP programming. The manual process involves building Web Parts from Web Control Libraries. There are workarounds to getting the designer up and working. Parts of this chapter and Chapter 6, "Advanced Web Parts Programming," will outline those approaches. However, for now, let's focus on the basics.

Open Visual Studio if it is not already opened, and create a new project based on a Class Library template, as shown in Figure 3-2. Name the project HelloWorldWebPart.

Figure 3-2. Web Part based on a Class Library template

In the HelloWorldWebPart code file, enter the code shown in Listing 3-1.

Listing 3-1. The HelloWorld Web Part

using System; 
using System.Web.UI; 
using System.Web.UI.WebControls.WebParts; 
 
namespace HelloWorldWebPart 
{ 
    public class HelloWorldWebPart : WebPart 
    { 
        protected override void Render(HtmlTextWriter writer) 
        { 
            writer.Write("Hello world"); 
        } 
    } 
}

Imports System 
Imports System.Web.UI 
Imports System.Web.UI.WebControls.WebParts 
 
Namespace HelloWorldWebPart 
    Public Class HelloWorldWebPart 
        Inherits WebPart 
        Protected Overrides Sub Render(ByVal writer As HtmlTextWriter) 
            writer.Write("Hello world") 
        End Sub 
    End Class 
End Namespace

The code compiles with a reference to System.Web. There's not a whole lot going on in there to be excited about. The class inherits from the Web Part class and overrides the Render method. Although you can inherit from a SharePoint Web Part class, you should prefer the ASP.NET class instead because the SharePoint Web Part is present for backward compatibility. We talk more about specific functionality that is unique to Web Parts based on the SharePoint Web class in Chapter 7, "Advanced Concepts with Excel Services." The Render method makes a System.Web.UI.HtmlTtextWriter object available. Its responsibility is to serve as a container for moving data to the client for display.

Signing Web Part Assemblies

You then need to sign the assembly. Signing an assembly, or strong-naming it, allows the .NET Framework to execute the assembly with full trust. The reason for this requirement is evident. Code running on the SharePoint site has access to the object model—discussed in Chapter 5, "Windows SharePoint Services 3.0"—which can perform privileged functions such as document library creation, data management, and a wide spectrum of other administrative functions. The strong name requirement acts as a first-level gate that confirms the intent of code being deployed on the site. For more information about code signing, see How to: Sign an Assembly with a Strong Name.

Also, you need to understand that these types of assemblies are designed to be delivered over the Internet through the browser to be installed and executed on the local computer. Signing these types of assemblies provides an explicit guarantee that the code has not been tampered with since it was signed by the author. Code signing does not make any other implicit or explicit guarantees.

Open the property pages for the project, as shown in Figure 3-3. Figure 3-3 shows the Signing tab in Visual Studio 2005.

After you have signed the assembly, retrieve the details of the public token. See the next section, "Retrieving Signing Details," if you do not know how to do this. You can also choose to sign the assembly from the Visual Studio 2005 Command Prompt window. Here is an example:

Sn.exe -k myTempKey.snk

Figure 3-3. Signing your assembly

Retrieving Signing Details

Open a Visual Studio 2005 Command Prompt window, and type sn.exe –t [full path]\ HelloWorldSharePoint.dll. Copy the public key token that is returned from the output window. Listing 3-2 provides a complete example.

Listing 3-2. Output after running sn.exe –t

C:\Program Files\Visual Studio 8\VC>sn -T "C:\ 
Documents and Settings\Alvin\My Documents\Visual Studio 2005\ 
Projects\HelloWorldWebPart\HelloWorldWebPart\bin\Debug\ 
helloworldwebpart.dll" 
 
 (R) .NET Framework Strong Name Utility  Version 2.0.50727.42 
Copyright (c)  Corporation.  All rights reserved. 
 
Public key token is 4847df5260465341

The .snk file must be located in the same directory with the .dll file; otherwise, an error will occur.

web.config Modification

Open the web.config file for the Web site where you intend to deploy the Web Part. Add the code shown in Listing 3-3 to the web.config file.

Listing 3-3. web.config file modification

<SafeControl Assembly="Enter your Assembly Name here", 
 Version=1.0.0.0, Culture=neutral, 
 PublicKeyToken="Enter your public key token here" 
 Namespace="Enter your namespace here" 
 TypeName="Enter your type name here" Safe="True" />

The namespace and type name can be obtained by examining the code file for the project. They correspond to the namespace and the name of the class. To retrieve the version and culture, navigate to the bin\debug folder and examine the properties of the assembly. Alternatively, you can click the Assembly Information button of the Application tab on the property pages to view the Assembly Information dialog box, which is shown in Figure 3-4.

Figure 3-4. Assembly Information dialog box

Listing 3-4 provides a complete example of the web.config file entry.

Listing 3-4. Actual modification in web.config

<SafeControl Assembly="HelloWorldWebPart", 
 Version=1.0.0.0, Culture=neutral, 
 PublicKeyToken=4847df5260465341 
 Namespace="HelloWorldWebPart" 
 TypeName=" HelloWorldWebPart" Safe="True" />

That's it. The next section demonstrates how to create Web Parts using the Visual Studio Extensions add-in.

Creating Web Parts Projects via Visual Studio Templates

Visual Studio contains an add-in that enables developers to build Web Parts that can be deployed on SharePoint sites. The add-in takes care of much of the internal plumbing required to move a Web Parts project from Visual Studio to the SharePoint repository. If you have not done so by now, download and install the Visual Studio 2005 extensions for Windows SharePoint Services 3.0 from MSDN.

The Visual Studio Extensions contain Visual Studio project templates, Visual Studio Item templates, and a SharePoint solution generator. We examine only the Web Part template here. Working examples of the other templates are provided in Chapter 7. Here is a summary of these items:

Visual Studio 2005 Project Templates

Templates that allow developers to build Web Parts. Four templates are available: Web Part, Team Site Definition, Blank Site Definition, and List Definition.

Visual Studio 2005 Item Templates

Items that can be added to an existing project. Four Item templates are available: Custom Field, List Definition, Content Type, and Module.

SharePoint Solution Generator

A stand-alone program that builds a site definition project from an existing SharePoint site.

Assuming you have downloaded and installed the Visual Studio Extensions, select New, then click Project from the Visual Studio File menu. Select SharePoint from the Project Types pane on the left side of the page. From the Templates pane on the right side of the page, select the Web Part template. Name the project HelloWorldWebPart. Click OK to proceed. Figure 3-5 shows a project being created using the new add-in.

The HelloWorldSharePoint project should resemble Figure 3-5. Figure 3-6 shows the Solution Explorer view for a project created using the new Visual Studio templates for Office SharePoint Server.

Notice that the default class automatically inherits from System.Web.UI.WebControls.WebParts.WebPart. In addition, there is a stub Render function, also shown in Figure 3-6, that is commented out. Uncomment the last line, and change the text from "Output HTML" to "Hello world". Then build the application.

Figure 3-5. Visual Studio Extensions

Figure 3-6. HelloWorldSharePoint project

Now take a closer look at the Solution Explorer window. Click on the Properties tab. Notice that the Properties folder contains a temporary.snk file. This is the key file that is used to sign the assembly. That information was part of the Signing tab you saw in Figure 3-3. You learned about signing in the "Signing Web Part Assemblies" section earlier. The extension also adds a reference to the Microsoft.SharePoint managed assembly and adds three references to classes inside the SharePoint assembly. You can see those references at the top of the code file.

Before you can deploy the application, you need to configure a deployment path to the server running Office SharePoint Server. Figure 3-7 shows the Properties option that leads to the property pages.

Figure 3-7. HelloWorldSharePoint property objects

From the property pages, select the Debug tab in the left column. Figure 3-8 shows the Debug tab in Visual Studio.

Figure 3-8. HelloWorldSharePoint debug options

In the text box next to the Start Browser With URL option, add the path to your server—for instance http://servername:port—as shown in Figure 3-8. This is the portal site where you intend to deploy the Web Part. If you do not modify this path and the default path does not point to your portal site, the F5 command will fail with an error message. Another tab that you should focus on is the SharePoint Solution tab shown in Figure 3-9.

Figure 3-9. HelloWorldSharePoint project SharePoint Solution options

The details you enter into the fields on the SharePoint Solution tab provide information to the end user about your Web Part. The descriptions you enter here show up when your Web Part appears in the Web Part gallery.

Deploying Web Parts

Web Parts can be deployed either manually or through an automatic installation process. Let's consider the manual process first. All installation processes use the stsadm.exe tool to install the required files to the server running Office SharePoint Server. Assuming your solution is named HelloWorldWebPart, follow these steps to deploy your application to Office SharePoint Server.

Manual Installation Walk-Through

Open a Command Prompt window to your solution directory, and set the PATH variable to the stsadm executable file. You need to set up some variables that ease the manual process, as shown by the code in Listing 3-5.

Listing 3-5. Variable declaration for SPAdminTool

set SPAdminTool=%CommonProgramFiles%\Shared\web server extensions\12\BIN\stsadm.exe 
set Task=install 
set PackageFile=%~dp0HelloWorldWebPart.wsp 
set PackageName= HelloWorldWebPart.wsp 
set TargetUrl=http://<servername:port> 

The SPAdminTool variable allows you to invoke stsadm from the current directory; otherwise, you need to use a fully qualified file path to stsadm. The other variables are self-explanatory. Next, add the solution package to Office SharePoint Server. To do this, open the Command Prompt window and type in the following:

"%SPAdminTool%" -o addsolution -filename "%PackageFile%"

Deploy the solution to Office SharePoint Server by typing in the following:

"%SPAdminTool%" -o deploysolution -name 
"%PackageName%" -local -allowGacDeployment -url %TargetUrl%

Finally, activate the SharePoint Web Part so that it shows up in the Web Part gallery by typing the following line at the command prompt:

"%SPAdminTool%" -o activatefeature -id 
f13d7cc3-fd1b-43cd-80a2-cf107522cbe2 -url %TargetUrl%

That's it for the manual process. The steps performed here are equivalent to the steps we performed in the manual deployment step, so you should have a firm understanding of the process. The manual process comes in handy when Web Parts start to misbehave and interfere with the execution of a page on the portal site. For instance, a malfunctioning Web Part can cause the page to fail to load. In some cases, you will need to fix the page by unloading the culprit Web Part using the manual process.

Automatic Installation Walk-Through

In the usual case, the developer copies the required files to a network file share on the server running Office SharePoint Server and the administrator installs the Web Part to that server. If the developer has administrative privileges on that server, the developer can use the Visual Studio Extensions add-in to deploy the Web Part. The act of deploying a Web Part makes the Web Part available to a SharePoint site—that is, it will show up in the Web Part gallery. An end user needs to add the Web Part to a SharePoint page for it to run. If this is your first walk on the SharePoint side, you need to remember that SharePoint development in the .NET Framework is a three-part dance of development, deployment, and user addition.

To use the Visual Studio Extensions add-in, simply press F5. F5 deploys the assembly to the server running Office SharePoint Server. It does not execute the assembly as would be the case for normal ASP.NET development. The action invokes a setup.bat file in the \bin\debug directory that copies the relevant files onto the server running Office SharePoint Server and installs the solution file (WSP) containing the code for the Web Part in the root of the Web site.

In some cases, the installation might fail. You can manually force an installation by navigating to the \bin\debug directory of your project from a command prompt. Run setup /uninstall to uninstall the application followed by setup /install to re-install the application. You should use this option sparingly. If further issues remain, it might help to open the setup.bat file in a text editor to figure out the exact line causing the error. The content of the setup.bat file is similar to the content you created for the manual installation process.

Deployment Through CAB Files

To confirm that your Web Part is correctly installed, follow these steps. From the portal home page, select Edit Page from the Site Actions link. Click Add Web Part. Figure 3-10 shows a SharePoint Web page in edit mode.

Figure 3-10 Web Part confirmation process

Click Advanced Web Part Gallery And Options. You can find this option by clicking the Add a Web Part button in Office SharePoint Server. Click Server Gallery. Your Web Part will show up in the list. If you do not see your Web Part, uninstall the Web Part and re-install it.

Uninstalling Web Parts from Office SharePoint Server

If your Web Part does not show up, you need to redo the installation steps. It's always advisable to first remove all files through the uninstall process before installing. If you have created a .cab file, you can use a single command to remove the package from Office SharePoint Server:

stsadm -o deletewppack -name HelloWorldWebPart.cab -url <http://servername:port>

If you do not have a .cab file, you can use the following set of commands to invoke the uninstall procedure. Define a set of variables if they have not already been defined.

set SPAdminTool=%CommonProgramFiles%\Shared\web server extensions\12\BIN\stsadm.exe 
set Task=install 
set PackageFile=%~dp0HelloWorldWebPart.wsp 
set PackageName= HelloWorldWebPart.wsp 
set TargetUrl=http://<servername:port>

To use the Deactivate feature for HelloWorldWebPart, type the following text into a command line:

"%SPAdminTool%" -o deactivatefeature -id 
f13d7cc3-fd1b-43cd-80a2-cf107522cbe2 -url %TargetUrl%

To use the Uninstall feature for HelloWorldWebPart, type the following text into a command line:

"%SPAdminTool%" -o uninstallfeature -id f13d7cc3-fd1b-43cd-80a2-cf107522cbe2 -force

To use the Retract solution for HelloWorldWebPart, type the following text into a command line:

"%SPAdminTool%" -o retractsolution -name "%PackageName%" -local -url %TargetUrl%

To use the Delete solution for HelloWorldWebPart from SharePoint Server, type the following text into a command line:

"%SPAdminTool%" -o deletesolution -name "%PackageName%"

In case you are wondering, Office SharePoint Server differentiates between installations and activations of Web Parts. You need to perform both operations to remove the solution from the server running Office SharePoint Server.

In some cases, malformed Web Parts can cause the Web site to malfunction. For instance, the Web site might refuse to start. One good approach to solving these types of issues is to uninstall custom Web Parts via the link in the error page until the site starts working correctly again. As a note to advanced users, in addition to using batch file automation, you can build an executable using the object model that will perform these steps. (See Chapter 5.)

Modifying Web Parts from Office SharePoint Server

As a developer, you need to know that Web Parts you develop for Office SharePoint Server can be customized by the end user or administrator. This is the default behavior. The next section will guide you through developing Web Parts that allow you to hook into the SharePoint Web Part customization theme. For now, let's briefly walk through the steps an end user would use to customize a Web Part.

Click Modify Shared Web Part on the edit menu for your Web Part, as shown in Figure 3-11. Figure 3-11 shows a Web Part's modification property being invoked.

Figure 3-11. SharePoint Web Part modification

Three default categories—Appearance, Layout, and Advanced—occupy the Web Part menu. Each category presents different attributes that can be tweaked to adjust the appearance and layout of the control on the Web page. Figure 3-12 shows the tool pane in Visual Studio 2005.

Figure 3-12. Tool pane editor

As a developer, you need to learn how to hook into these customizations to enable a feature-rich Web Part control. An example will be provided later in the chapter.

Debugging Web Parts in Visual Studio

Malformed Web Parts or other Web Part run-time anomalies that are not caught can cause the Web page to fail to load. It's comforting to know that the powerful Visual Studio debugger is available to developers in much the same way as it is for .NET Framework applications. Web Parts debugging is dependent on having a working ASP.NET debugger, so you need to make sure that you can at least set and hit a breakpoint in a regular ASP.NET application.

Consider Figure 3-13. This page is produced because an ill-behaved Web Part could not be loaded.

In most cases, SharePoint exception handling code will catch the error and display the error page. You can use the link on the error page to find the Web Part and close it. (See Figure 3-14.) Alternatively, you can uninstall the Web Part using the techniques described previously.

Figure 3-13. Error page for an unhandled exception

Figure 3-14. Web Part page maintenance

To debug a Web Part in Visual Studio 2005 using the new Visual Studio Extensions for Office SharePoint Server, you need to attach the debugger to an executing process. The process of attaching the debugger is straightforward.

Let's consider a concrete example for an errant Web Part called DynamicDgWebPart. When the Web Part is added to the page, it causes the page to crash. To debug the Web Part, open Visual Studio and select Attach To Process from the Debug menu, as shown in Figure 3-15.

Figure 3-15. Snapshot of the Visual Studio debugger

In the Available Process list, find the w3wp.exe worker process and double-click it to attach the debugger to the ASP.NET worker process, as shown in Figure 3-16. Then simply refresh the error page in Office SharePoint Server. The debugger will break on the breakpoint if that breakpoint occurs before the actual error that caused the page failure.

Figure 3-16. Attach To Process dialog box in Visual Studio 2005

In some cases, the issue that is causing the page to fail to load occurs long before user code is called. In those cases, the debugger is of little assistance. You need to view as much error information as possible from the stack trace that is dumped to the error page—also known as the yellow screen of death.

The SharePoint page configuration does not dump a full stack trace by default. You need to turn on that functionality. To do so, navigate to the web.config file for the error page being displayed. Review Chapter 1 to find out how to determine the Web application for a particular site. Make the modification to the web.config file that is shown in Listing 3-6.

Listing 3-6. web.config safe control modification

<SharePoint> 
    <SafeMode MaxControls="200" CallStack="true"  
DirectFileDependencies="10" TotalFileDependencies="50" 
AllowPageLevelTrace="true"> 
      <PageParserPaths> 
      </PageParserPaths> 
    </SafeMode>

Reset Internet Information Services (IIS), and refresh the Web page. A stack trace will appear if the exception occurs again. Remember to undo the changes you made to the code after debugging is complete. Stack traces in production aren't only an eyesore, they present serious security issues as well.

Responding to Events in Web Parts

Web Parts are key building blocks for SharePoint software development. The next example walks you through the process of building a Web Part with an embedded control that is able to respond to end user events. The example is based on a DataGrid control that loads data from the Microsoft SQL Server AdventureWorks database and allows the end user to sort columns. You can find the AdventureWorks database on the CodePlex site.

Use the Class Library template in Visual Studio to create a library named WebPartGrid. You need to import the namespaces shown in Listing 3-7.

Listing 3-7. Web Part namespace declaration

System 
System.Web 
System.Web.UI 
System.Web.UI.WebControls 
System.Web.UI.WebControls.WebParts 
System.Data 
System.Data.SqlClient 
Microsoft.SharePoint 
Microsoft.SharePoint.WebControls 
Microsoft.SharePoint.WebPartPages

Next, let's create a DataGrid object. Inside the class, add a CreateControls method. These namespaces are located in the System.Web, System.Drawing, and Windows SharePoint Services libraries. Listing 3-8 illustrates the process.

Listing 3-8. Web Part CreateChildControls method

protected override void CreateChildControls() 
{ 
    itemsAvailable = new DataGrid(); 
 
    itemsAvailable.HeaderStyle.BackColor = System.Drawing.Color.SlateGray; 
    itemsAvailable.HeaderStyle.Font.Bold = true; 
    itemsAvailable.AllowSorting = true; 
    itemsAvailable.AllowPaging = true; 
    itemsAvailable.HeaderStyle.HorizontalAlign = 
        HorizontalAlign.Center; 
    itemsAvailable.AlternatingItemStyle.BackColor = 
        System.Drawing.Color.Gainsboro; 
    itemsAvailable.SortCommand +=  
      new DataGridSortCommandEventHandler(itemsAvailable_SortCommand); 
    itemsAvailable.PageIndexChanged +=  
  new DataGridPageChangedEventHandler(itemsAvailable_PageIndexChanged); 
    itemsAvailable.DataSource = GetData(); 
    itemsAvailable.DataBind(); 
 
    Controls.Add(itemsAvailable); 
}

Protected Overrides Sub CreateChildControls() 
    itemsAvailable = New DataGrid() 

    itemsAvailable.HeaderStyle.BackColor = _
        System.Drawing.Color.SlateGray 
    itemsAvailable.HeaderStyle.Font.Bold = True 
    itemsAvailable.AllowSorting = True 
    itemsAvailable.AllowPaging = True 
    itemsAvailable.HeaderStyle.HorizontalAlign = HorizontalAlign.Center 
    itemsAvailable.AlternatingItemStyle.BackColor = _ 
        System.Drawing.Color.Gainsboro 

    AddHandler itemsAvailable.SortCommand, AddressOf _

        itemsAvailable_SortCommand 
    AddHandler itemsAvailable.PageIndexChanged, AddressOf 
      itemsAvailable_PageIndexChanged 
    itemsAvailable.DataSource = GetData() 
    itemsAvailable.DataBind() 
 
    Controls.Add(itemsAvailable) 
End Sub

The CreateChildControls method is called by the ASP.NET page framework to notify server controls that use composition-based implementation to create any child controls they contain in preparation for rendering. CreateChildControls is called immediately before a control is rendered, and it is responsible for populating the child controls that might be part of the page. The code places the DataGrid in this method because it is a child control of the form and needs to be initialized before the Web Part is rendered. The DataGrid is then added to the controls collection so that it can participate in the render process later.

Examine the snippet of code shown in Listing 3-9.

Listing 3-9. Web Part Render method

protected override void Render(HtmlTextWriter output) 
{ 
    this.EnsureChildControls(); 
    this.itemsAvailable.RenderControl(output); 
}

Protected Overrides Sub Render(ByVal output As HtmlTextWriter) 
    Me.EnsureChildControls() 
    Me.itemsAvailable.RenderControl(output) 
End Sub

Code that makes use of the CreateChildControls method requires a call to the EnsureChildControls method. The RenderControl method of the DataGrid is used to format the output for the client. At this point, the DataGrid renders on the client if it contains data.

The code to load data into the DataGrid follows the standard pattern, which is presented in Listing 3-10 for your benefit.

Listing 3-10. Code to load data from SQL Server

protected DataSet GetData() 
{ 
    string sqlString = "SELECT AddressID, AddressLine1, AddressLine2, 
City, StateProvinceID, PostalCode FROM Person.Address"; 
    string connectionString = @"Data Source=<servername>\SQLSVR2005; 
    Initial Catalog='C:\PROGRAM FILES\MICROSOFT SQL 
        SERVER\MSSQL.1\MSSQL\DATA\ADVENTUREWORKS_DATA.MDF';
        Integrated Security=True"; 

    using (System.Data.SqlClient.SqlConnection mySQLConnect =  
        new SqlConnection(connectionString)) 
    { 
        mySQLConnect.Open(); 
        SqlCommand myCmd = new SqlCommand(sqlString, mySQLConnect); 
        System.Data.SqlClient.SqlDataAdapter myAdapter = new 
            SqlDataAdapter(myCmd); 
        DataSet ds = new DataSet(); 

        myAdapter.Fill(ds, "ItemsTable"); 

        return ds; 
    }
}

Protected Function GetData() As DataSet 
    Dim sqlString As String = "SELECT AddressID, AddressLine1, _
AddressLine2, City, StateProvinceID, PostalCode FROM Person.Address" 
    Dim connectionString As String = "Data _ Source=<servername>\SQLSVR2005; Initial Catalog= _
'C:\PROGRAM FILES\MICROSOFT SQL SERVER\ _
MSSQL.1\MSSQL\DATA\ADVENTUREWORKS_DATA.MDF';Integrated Security=True" 

    Using mySQLConnect As System.Data.SqlClient.SqlConnection = 
        New SqlConnection(connectionString) 
        mySQLConnect.Open() 
        Dim myCmd As SqlCommand = New SqlCommand(sqlString, _
            mySQLConnect) 
        Dim myAdapter As System.Data.SqlClient.SqlDataAdapter = 
            New SqlDataAdapter(myCmd) 
        Dim ds As DataSet = New DataSet() 

        myAdapter.Fill(ds, "ItemsTable") 

        Return ds 
    End Using 
End Function

The code queries the AdventureWorks database for data specified by the query. For illustrative purposes, the query and connection string sit inside the GetData method. In production code, this would certainly not be the case. A data set is returned if the query is successful.

Build and deploy the Web Part using the approach outlined earlier in the "Deploying Web Parts" section. From your site collection, find the Web Part and add it to the page. Your application's output should resemble Figure 3-17.

Figure 3-17. GridViewWebPart with data

The effort wasn't entirely painful, but there isn't much real-world functionality baked in. Let's add the sorting functionality. Although sorting is implemented by default in the GridView, the DataGrid lacks that feature. Listing 3-11 shows the code needed, starting with the PreRender method of the page.

Listing 3-11. Page sorting through the PreRender method

protected override void OnPreRender(EventArgs e) 
{ 
    DataSet ds = GetData(); 
    DataView dv = ds.Tables[0].DefaultView; 

    if (ViewState["SortExpression"] != null && _ 
ViewState["SortDirection"] != null) 
    { 
        dv.Sort = ViewState["SortExpression"] + " " + ViewState["SortDirection"]; 
    } 
    itemsAvailable.DataSource = dv; 
    itemsAvailable.DataBind(); 

    base.OnPreRender(e); 
}

Protected Overrides Sub OnPreRender(ByVal e As EventArgs) 
    Dim ds As DataSet = GetData() 
    Dim dv As DataView = ds.Tables(0).DefaultView 

    If Not ViewState("SortExpression") Is Nothing AndAlso Not 
        ViewState("SortDirection") Is Nothing Then 
        dv.Sort = ViewState("SortExpression") & " " & _
            ViewState("SortDirection") 
    End If 
    itemsAvailable.DataSource = dv 
    itemsAvailable.DataBind() 

    MyBase.OnPreRender(e) 
End Sub

The sort functionality requires that the control remember the last sort direction and sort expression. However, because the DataGrid itself offers no place to store these items, we use ViewState. The idea is to retrieve the stored values of the last sort performed and invert the functionality on the appropriate column. Finally, we bubble the event to the parent control by calling MyBase.OnPreRender. This type of code is not specific to Office SharePoint Server; rather, it relates to ASP.NET and you should be quite familiar with it.

Finally, we add the sort command handler shown in Listing 3-12.

Listing 3-12. Sort command handler

void itemsAvailable_SortCommand(object source, DataGridSortCommandEventArgs e) 
{ 
    string SortExpression = (string)ViewState["SortExpression"]; 
    string SortDirection = (string)ViewState["SortDirection"]; 

    if (SortExpression != e.SortExpression) 
    { 
        SortExpression = e.SortExpression; 
        SortDirection = "asc"; 
    } 
    else 
    { 
        if (SortDirection == "asc") 
            SortDirection = "desc"; 
        else 
            SortDirection = "asc"; 
    } 

    ViewState["SortExpression"] = SortExpression; 
    ViewState["SortDirection"] = SortDirection; 
}

Private Sub itemsAvailable_SortCommand(ByVal source As Object,  
ByVal e As DataGridSortCommandEventArgs) 
    Dim SortExpression As String = CStr(ViewState("SortExpression")) 
    Dim SortDirection As String = CStr(ViewState("SortDirection")) 

    If SortExpression <> e.SortExpression Then 
        SortExpression = e.SortExpression 
        SortDirection = "asc" 
    Else 
        If SortDirection = "asc" Then 
            SortDirection = "desc" 
        Else 
            SortDirection = "asc" 
        End If 
    End If 

    ViewState("SortExpression") = SortExpression 
    ViewState("SortDirection") = SortDirection 
End Sub

Essentially, when the sort event fires, we need to remember the column and the type of sort that was performed so that future sorts can occur in the correct order.

The last bit of code, shown in Listing 3-13, wires the event to its handler.

Listing 3-13. Wiring the Sort event

itemsAvailable.SortCommand +=  
new DataGridSortCommandEventHandler(itemsAvailable_SortCommand);

AddHandler itemsAvailable.SortCommand, AddressOf _
    itemsAvailable_SortCommand

Displaying Workbooks in Web Parts

One of the most important functions of Excel Services is that it allows a Web Part to display a workbook. The next example will examine the scenario where a workbook is loaded in a Web Part and displayed from Office SharePoint Server.

To load a workbook in a SharePoint Web Part, open the portal and select Edit Page from the Site Actions menu. Click on Add New Web Part. Select the Excel Web Part. Click on Click Here To Open The Tool Pane to open the tool pane. You can also open the tool pane for any Web Part by clicking the Edit button in the Web Part zone and selecting Modify Shared Web Part. Enter the path to a valid workbook in the Excel Web Access tool pane. Figure 3-18 shows Excel Web Access with a loaded spreadsheet.

Figure 3-18. Excel Web Access spreadsheet

Unfortunately, the code to programmatically display a Web Part in Office SharePoint Server is a bit more involved. We revisit the issue in Chapter 5 because it involves hooking into the Windows SharePoint Services model, as well as adding certain security configurations to get the Excel Web Part to display correctly.

In any case, any control displayed in a Web Part provides certain default configuration settings that can be applied by the end user. The configuration settings reside in the tool pane container. By default, the tool pane consists of five categories, each containing various properties that can be applied to the Web Part.

As you grow more confident in building Web Parts, you might begin to wonder about influencing the behavior of the properties in the tool pane. In fact, business requirements might even dictate that you customize the tool pane. In any case, we provide the full details of the code in Chapter 5. For now, we need to realize that building Web Parts with the options presented so far is arduous, time-consuming, and error prone. The next section outlines a way forward that requires less effort.

Web Parts and User Controls

To be absolutely blunt, developing cool Web Parts with arcane classic ASP approaches as the code showed earlier is a bit uncivilized. More significantly, the process is error prone because you cannot immediately see the results of your labor unless you deploy your Web Part to Office SharePoint Server and load it into a page. The lack of a tangible design surface on which to model and shape your programming ideas eats aggressively into your development time. There must be a better way!

Fortunately, it's rather easy to incorporate a designer into your Web Part project by using ASP.NET user controls. You might recall that user controls are server controls that contain encapsulated logic. User controls have .ascx extensions and run embedded in ASP.NET pages much like Web Parts. Because they have a lot in common with Web Parts (for example, they implement the IWebPart interface), it is possible to use the User Control designer to develop a user control that can be embedded in a SharePoint page as a Web Part. That's a fairly cheap way to gain designer support for creating SharePoint Web Parts in Visual Studio.

Create a project based on a user control template. You need to choose New, then Web Site from the Visual Studio toolbar menu. Name the Web site UserWebPart. In the solution manager, add a new item of type User Control.

By default, the UserWebPart control opens in source view. Select Design to invoke the Visual Studio designer, and drag and drop a GridView component on the designer. Configure the DataSource property of the GridView object to point to a table in SQL Server like you normally would when developing ASP.NET applications. If you prefer, you can edit the .ascx file as well. In our example, GridView is connected to the AdventureWorks database pointed at the Address table. At this time, you can choose to format GridView as appropriate. For grins, we have added paging and sorting and enabled the client-side callback mechanism as well as edit functionality. We have also spruced up the view by adding formatting via Auto Format. This is all done from the designer by pointing and clicking. We haven't written any code.

The user control should resemble Figure 3-19.

Figure 3-19. Design view of GridView

To confirm that we have a working user control, embed the control in a Web Form and run the application. You can get a feel for presentation and functionality. This is no different from regular ASP.NET development, which is why the text glosses over the details.

All that is needed now is to create the actual Web Part as we have done in the past. Instead of building controls by hand, we only need to create a single user control in the body of the Render method and load the contents of the .ascx file. If the load routine is successful, it will render a fully functional grid—complete with editing, paging, and sorting functionality—into the Web Part. Note that we can load as many user controls as we need; there is no set limit. From the point of view of the Web Part, it acts as a host to the user control.

Listing 3-14 shows the complete code for the Web Part. Notice that it has shrunk considerably because the business logic has been encapsulated in the user control. And this is a best practice!

Listing 3-14. Web Part user control code

using System; 
using System.Web.UI; 
using System.Runtime.InteropServices; 

namespace UserWebPart 
{ 
    [Guid("9367ff12-4f6a-4a1a-a930-75ce802c4c82")] 
    public class UserWebPart : _
System.Web.UI.WebControls.WebParts.WebPart 
    { 
        private UserControl usercontrol; 
        protected override void CreateChildControls() 
        { 
            base.CreateChildControls(); 
            Controls.Clear(); 

            usercontrol = (UserControl) _
Page.LoadControl(@"/usercontrols/ 
              webusercontrol.ascx"); 

            Controls.Add(usercontrol); 
        } 
        protected override void Render(HtmlTextWriter writer) 
        { 
            EnsureChildControls(); 
            usercontrol.RenderControl(writer); 
        } 
    } 
}  

Imports System 
Imports System.Web.UI 
Imports System.Runtime.InteropServices 

Namespace UserWebPart 
    <Guid("9367ff12-4f6a-4a1a-a930-75ce802c4c82")> _ 
    Public Class UserWebPart 
        Inherits System.Web.UI.WebControls.WebParts.WebPart 
        Private usercontrol As UserControl 
        Protected Overrides Sub CreateChildControls() 
            MyBase.CreateChildControls() 
            Controls.Clear() 
 
            usercontrol = _
        CType(Page.LoadControl("/usercontrols/webusercontrol.ascx"), 
              UserControl) 

            Controls.Add(usercontrol) 
        End Sub 
        Protected Overrides Sub Render(ByVal writer As HtmlTextWriter) 
            EnsureChildControls() 
            usercontrol.RenderControl(writer) 
        End Sub 
    End Class 
End Namespace

There are three parts to this code. One part, CreateChildControls, loads the user controls. Another part, the Render method, renders the control. The third part is an ugly GUID above the class. We will examine its reason for being in Chapter 7; it does have a purpose. Notice the LoadControl call inside CreateChildControls. It is responsible for loading the user control from a file on disk located in a directory called usercontrols. There's nothing special about this directory, and the name is arbitrarily chosen. We simply need to create the directory and copy over the .ascx file that was built in the earlier part of the exercise. Once the code executes, the runtime will find the user control and load it into the Web Part. You do not need to configure any special permissions for this folder.

Take it for a spin. You will find that the user control is fully functional and that it is able to sort, page, and perform the functions that were programmed in earlier. Notice that the GridView object embedded in the Web Part automatically sorts and pages without the postback flash à la AJAX. There's a strong hint in there somewhere that more is possible along that avenue, but we defer a more detailed discussion to Chapter 6. So, finally, you can add a few lines of code to a Web Part and incorporate the world of ASP.NET via user controls into Office SharePoint Server.

And in case you missed the signpost in the middle of the road, you should realize that you can swap, adjust, or modify the user controls live while the Web site is running. The new control will simply be loaded on the next pass. There's no price too high for that type of functionality.

Guidelines for Developing User Controls for SharePoint Server

A few new things are worth mentioning. Every time you modify the user control, you need to manually copy the files to the usercontrol directory. It really isn't a high price to pay, and there is a certain giddy feeling of knowing that you can, through user controls, unleash the ASP.NET productivity monster onto Office SharePoint Server 2007. The more creative among you can easily modify the setup.bat file to copy the files to the directory or provide a build event using MSBuild to do the dirty work.

If you actually ran the application code presented earlier, you would soon find that it throws the following ugly exception:

This control does not allow connection strings with the following keywords: 'Integrated Security', 'Trusted_Connection'.

It is complaining rather loudly about the Windows Authentication portion of the connection string. Recall that the earlier example actually had the connection string embedded in the code. The sleight of hand trick allowed us to focus on the example of building and populating a GridView without polluting it with security exceptions.

As it turns out, controls or assemblies loaded from outside the global assembly cache (GAC) are not trusted by default. This rule applies directly to us because we are loading a user control from an untrusted folder. One solution is to simply place the assembly in the GAC using the approach outlined in Chapter 2, "Excel Web Services." That strategy is without merit in this case because .ascx files are not assemblies, contain a user interface piece and, therefore, cannot easily be stored in the GAC.

Another approach is to raise the trust level of the Web Part from the web.config file. Consider the following approach:

<trust level="Full" originUrl="" />

That approach is a punishable offense because it increases the attack surface for malicious code. Although it will work, the potential for damage borders on unacceptable.

The best approach is to make a change to the configuration file of the Web site. Find the tagMapping attribute in the web.config file for the Web site, and replace it with the line of text shown in Listing 3-15.

Listing 3-15. Code to work around the security exception

<tagMapping> 
    <clear/> 
</tagMapping>

Timesheet Application

Consider a request by a client to build a timesheet application. Let's use the example of a small consulting firm that has five consultants who work in the field on a daily basis. These consultants update their timesheet at project completion with billable hours. The accountants in the accounting department keep track of the billable hours by using a spreadsheet. The accountants are accustomed to running reports and macros to extract relevant metrics at the end of the month.

The consultants note that the single spreadsheet version shared among team members often leads to accidents and versioning issues. These accidents and out-of-sync spreadsheets cost the company money through inaccurate reporting.

There are many approaches to writing an application that will service the company's needs. We already have all the fundamentals to put this application together. Let's walk through a few scenarios to see whether we can agree on the best design for updating the system. First, we can write either a Web application or a smart client. The smart client has particular appeal because it is connection-aware. This works out particularly well for field workers who don't always have access to the Internet. If these aren't major issues, an ASP.NET client will do nicely because it hides the burden of installation. The ASP.NET client can connect to a spreadsheet hosted on Office SharePoint Server 2007 that stores the data. We can use Excel Web Services for the connection logic. Let's proceed with an ASP.NET application.

To build the application, you create a new Web Part project, as shown in Figure 3-20. Name the project Accounter. Figure 3-20 shows a Web Part made up of text boxes, labels, and a button.

Figure 3-20. Interface for the Accounter application

Aside from the coding aspects, the spreadsheet needs to be provisioned for each consultant. Provisioning the spreadsheet involves providing the consultant with a pass-code number and a sheet with the consultant's name in the workbook. The sheet for the junior consultant Candia is shown in Figure 3-20.

In the prepared spreadsheet, a hidden column in Microsoft Office Excel is used to store the numeric password. Each column in the spreadsheet represents a client and the billable hours attached to the client. As the consultants perform audits in the field, data is entered into the next adjacent column, as shown in Figure 3-21. Excel supports one million rows and 16,000 columns, so there's a fair chance you'll run out of work before exceeding the column limit. The accountants are free to embed macros, formulas, or whatever is necessary to facilitate data extraction. The spreadsheet prepared by the accountant resembles Figure 3-21.

Figure 3-21. Excel spreadsheet representation of completed Accounter application

The accountant in charge of the application simply needs to add one sheet per consultant, named appropriately. In addition, the accountant needs to create named ranges in column A. The application that you write will fill columns B, C, D, and so on for each worksheet in the workbook. The accountant saves the spreadsheet in a trusted file location so that EWS can service the spreadsheet. At any point in time, the spreadsheet can be updated, swapped, or calculated for reporting metrics without affecting the user because workbooks are opened in user sessions.

When you are finished building the user interface, let's walk through the approach. We provide the relevant code snippets along with the explanation. The driving event for the application code is the Submit button. Submit calls the SaveClientData method. The main structure of the SaveClientData method call looks like the code shown in Listing 3-16.

Listing 3-16. Accounter application logic

// Save the user input into the spreadsheet session. 
LoadInputDataIntoSpreadsheet(es, programCode, programCode, sessionId, 
    sheetName); 

// Write the values in the user session to the workbook in 
// the trusted location. 
SaveWorkBook(es, targetWorkbookPath); 

// Clear the form. 
ClearForm();

' Save the user input into the spreadsheet session. 
LoadInputDataIntoSpreadsheet(es, programCode, programCode, sessionId, _
    sheetName) 
' Write the values in the user session to the workbook in the 
' trusted location. 
SaveWorkBook(es, targetWorkbookPath) 
' Clear the form. 
ClearForm()

Let's begin with the simple method calls. ClearForm is a simple routine to clear the form after it is submitted. (See Listing 3-17.)

Listing 3-17. Routine to notify client that data saves are pending

private void ShowUnsavedClientData(DataItem di) 
{ 
    ConsultantPasscodeField.Text = string.Empty; 
    ConsultantNameField.Text    = di.EmployeeName; 
    ConsultantNumberField.Text  = di.EmployeeNumber.ToString(); 
    ClientHoursField.Text       = di.ClientHours.ToString(); 
    ClientNameField.Text        = di.ClientName; 
    ClientNumberField.Text      = di.ClientNumber.ToString(); 
    DateField.Text              = di.CurrentDate.ToString(); 
    statusBar.Items[0].Text     = "Click submit to save this data, 
        Cancel to clear."; 
    statusBar.Items[0].BackColor= Color.Yellow; 
}

Private Sub ShowUnsavedClientData(ByVal di As DataItem) 
    ConsultantPasscodeField.Text = string.Empty 
    ConsultantNameField.Text = di.EmployeeName 
    ConsultantNumberField.Text = di.EmployeeNumber.ToString() 
    ClientHoursField.Text = di.ClientHours.ToString() 
    ClientNameField.Text = di.ClientName 
    ClientNumberField.Text = di.ClientNumber.ToString() 
    DateField.Text = di.CurrentDate.ToString() 
    statusBar.Items(0).Text = "Click submit to save this data, " & _
        "Cancel to clear." 
    statusBar.Items(0).BackColor= Color.Yellow 
End Sub

There isn't much to explain here. This routine will be invoked by a call to ClearForm when we submit the form successfully. Another simple method is LoadInputDataIntoSpreadsheet, shown in Listing 3-18.

Listing 3-18. Code to load input data into the spreadsheet

public void LoadInputDataIntoSpreadsheet(ExcelService es,  
string sheetname, string programCode, string sessionId, string sheetName) 
{ 
    // Valid client, so begin processing. 
    es.SetCellA1(sessionId, sheetName, programCode + 5.ToString(), 
      ClientNumberField.Text.Trim()); 
    es.SetCellA1(sessionId, sheetName, programCode + 6.ToString(), 
      ClientNameField.Text.Trim()); 

    es.SetCellA1(sessionId, sheetName, programCode + 7.ToString(), 
      ClientHoursField.Text.Trim()); 
    es.SetCellA1(sessionId, sheetName, programCode + 9.ToString(), 
      DateField.Text.Trim()); 
}

Public Sub LoadInputDataIntoSpreadsheet(ByVal es As ExcelService, ByVal  
sheetname As String,  
ByVal programCode As String, ByVal sessionId As String,  
ByVal sheetName_Renamed As String) 
    ' Valid client, so begin processing. 
    es.SetCellA1(sessionId, sheetName, programCode & 5.ToString(), _
        ClientNumberField.Text.Trim()) 
    es.SetCellA1(sessionId, sheetName, programCode & 6.ToString(), _
        ClientNameField.Text.Trim()) 

    es.SetCellA1(sessionId, sheetName, programCode & 7.ToString(), _
        ClientHoursField.Text.Trim()) 
    es.SetCellA1(sessionId, sheetName, programCode & 9.ToString(), _
        DateField.Text.Trim()) 
End Sub

This method is responsible for moving the end user input values into the spreadsheet. The programCode variable holds the next empty column—for instance, B, C, D, E, and so on. The logic concatenates a numeric position to the address to form a valid range. As an example, the first line of code sets the cell reference to B5. On the next iteration, the reference is set to C5, and so on.

The SaveWorkbook method call is responsible for writing the in-memory values of the workbook to the actual workbook in the trusted location, as we can see in Listing 3-19.

Listing 3-19. Code to save a workbook session to disk

// This routine persists the workbook to the SharePoint document library. 
void SaveWorkBook(ExcelService es, string targetWorkbookPath) 
{ 
    Status[] status; 
    // Open the workbook, then call GetWorkbook and close the session. 
    string sessionId = es.OpenWorkbook(targetWorkbookPath, _lang, 
        _lang, out status); 

    // Get a full snapshot of the workbook. 
    byte[] workbook = es.GetWorkbook(sessionId, 
        WorkbookType.FullWorkbook, 
      out status); 

    // Close workbook. This also closes the session. 
    es.CloseWorkbook(sessionId); 

    // Write the resulting Excel file to stdout, as a binary stream. 
    FileStream fs = new FileStream(@"E:\Accounter\Accounter.xlsx", FileMode.Create); 
    // Strictly speaking a binary writer isn't required here. 
    BinaryWriter binaryWriter = new BinaryWriter(fs); 
    binaryWriter.Write(workbook); 
    binaryWriter.Close(); 
} 

' This routine persists the workbook to the SharePoint document library. 
Private Sub SaveWorkBook(ByVal es As ExcelService, ByVal targetWorkbookPath As String) 
    Dim status As Status() 
    ' Open the workbook, then call GetWorkbook  
    ' and close the session. 
    Dim sessionId As String = es.OpenWorkbook(targetWorkbookPath, _
        _lang, _lang, 
      status) 

    ' Get a full snapshot of the workbook. 
    Dim workbook As Byte() = es.GetWorkbook(sessionId, _
        WorkbookType.FullWorkbook, 
      status) 

    ' Close workbook. This also closes the session. 
    es.CloseWorkbook(sessionId) 

    ' Write the resulting Excel file to stdout, as a binary stream. 
    Dim fs As FileStream = New _
    FileStream("E:\Accounter\Accounter.xlsx", 
      FileMode.Create) 
    ' Strictly speaking a binary writer isn't required here. 
    Dim binaryWriter As BinaryWriter = New BinaryWriter(fs) 
    binaryWriter.Write(workbook) 
    binaryWriter.Close() 
End Sub

We gloss over this piece of code because Chapter 4, "Excel Calculation Service," offers a more detailed analysis. For now, we note that the code grabs the in-memory snapshot of the workbook and uses the .NET Framework API to write the file to disk.

Before this code can run without issues, some plumbing needs to be set up. Let's discuss this process in detail. The plumbing is the heart of the application. It opens the worksheet using the end user's name as the worksheet value. See the code in Listing 3-20.

Listing 3-20. Code showing the logic behind the save data functionality

public void SaveClientData() 
{ 
    using (ExcelWebService.ExcelService es = new ExcelService()) 
    { 
        string sheetName = ConsultantNameField.Text; 
        Status[] outStatus; 
        // Open the workbook. 
        string sessionId = OpenWorkbook(es, sheetName); 

        // Workbook cannot be opened, so inform the user of the issue. 
        // Most likely, an accountant has locked the range for 
        // end-of-month reporting. 
        if (String.IsNullOrEmpty(sessionId)) 
        { 
            statusBar.Items[0].Text = "Unable to save data. Please wait 
                a while before trying again"; 
            statusBar.Items[0].BackColor = Color.Red; 
            throw new ApplicationException("The workbook cannot be 
                opened, please try again later"); 
        } 

        // Use the consultant's name to retrieve the provisioned data. 
        object[] rangeResult = es.GetRangeA1(sessionId, sheetName, 
            "a1:a11", 
          false, out outStatus); 

        string employeePassCode = GetPassCode(rangeResult); 
        string programCode = GetProgramCode(rangeResult); 
        if(programCode.trim() != string.Empty) 
        {  
            // Authenticate the user.  
        } 
        else 
        { 
            statusBar.Text = "You are not authorized to access this application."; 
            statusBar.BackColor = Color.Red;
        } 
    } 
}

Public Sub SaveClientData() 
    Using es As ExcelWebService.ExcelService = New ExcelService() 
        Dim sheetName As String = ConsultantNameField.Text 
        Dim outStatus As Status() 
        ' Open the workbook. 
        Dim sessionId As String = OpenWorkbook(es, sheetName) 

        ' Workbook cannot be opened, so inform the user of the issue. 
        ' Most likely, an accountant has locked the range for 
        ' end-of-month reporting. 
        If String.IsNullOrEmpty(sessionId) Then 
            statusBar.Items(0).Text = "Unable to save data. " & _
                "Please wait a while before trying again" 
            statusBar.Items(0).BackColor = Color.Red 
            Throw New ApplicationException("The workbook cannot" & _
            "be opened, please try again later") 
        End If 

        ' Use the consultant's name to retrieve the provisioned data. 
        Dim rangeResult As Object() = es.GetRangeA1(sessionId, _
            sheetName, "a1:a11", False, outStatus) 

        Dim employeePassCode As String = GetPassCode(rangeResult) 
        Dim programCode As String = GetProgramCode(rangeResult) 
        If programCode.trim() <> String.Empty Then 
            ' Authenticate the user.   
        Else 
            statusBar.Text = "You are not authorized to " & _
                "access this application." 
            statusBar.BackColor = Color.Red 
        End If 
    End Using 
End Sub

The method first creates a Web service proxy object within a using block. The using block guarantees resource deallocation to avoid memory leaks. Then the consultant's name is used to find and open the workbook through the OpenWorkbook method. A valid session ID is returned if the call is successful. The session identifier is a long encrypted string similar to the following:

"64.dcb16eaf-ec49-4f54-a2f4-… -03-00-02T02:00:00:0000#-0060"

Because the programmer has intimate knowledge of the back-end spreadsheet, she can set a range such as A1:A11. This corresponds to the values entered by the accountant when the spreadsheet was provisioned for a particular consultant. We'll use that range to extract a special pass-code value and a program code value. Obviously, we need to hide rows 1 and 2 because these contain sensitive data. The visibility does not affect program logic. And we can use Excel workbook security to prevent users from turning the visibility back on.

The pass-code value entered by the accountant in the workbook simply provides another level of authentication. The code can check the consultant's name and pass code that were entered in the front-end application against the values in the spreadsheet for a match, taking action as appropriate.

The programCode variable is initially empty when the spreadsheet is first provisioned. Recall that the programCode variable represents the next column that is available for writing. The programCode feature prevents code from overwriting ranges that have already been written to. When the code has updated the spreadsheet with values from the Windows client on the first time through, the value is updated from an initially empty value to C. Column A contains data that was provisioned by the accountant, and column B contains data that was just written when the value of programCode was empty. This indicates to the calling code that column C is available for storing values the next time a write is required. On the next pass through**, programCode** will be updated from C to D, and so on. Columns will be filled in sequential fashion.

The method to extract the programCode and employeePassCode are logically structured the same way. A loop iterates the results of the GetRangeA1 method, and an appropriate index is used into the object array. Listing 3-21 shows the code for the GetProgramCode method.

Listing 3-21. Code to extract the program logic from the EWS jagged array object

// Get the passcode from the input jagged array. 
string GetProgramCode(object[] input) 
{ 
    string retVal = string.Empty; 
    if (input != null && input.Length > 0) 
    { 
        object[] range = (object[])input[0]; 
        if (range != null && range.Length > 0) 
            retVal = range[0].ToString(); 
    } 

    return retVal; 
}

' Get the passcode from the input jagged array. 
Private Function GetProgramCode(ByVal input As Object()) As String 
    Dim retVal As String = String.Empty 
    If Not input Is Nothing AndAlso input.Length > 0 Then 
        Dim range As Object() = CType(input(0), Object()) 
        If Not range Is Nothing AndAlso range.Length > 0 Then 
            retVal = range(0).ToString() 
        End If 
    End If 

    Return retVal 
End Function

Another nifty feature that we have added focuses on saving results if exceptions occur. If updates through EWS fail, the application intelligently serializes the data to disk so that application data is not lost. At application startup, the code then checks to see whether there is any serialized data that needs to be saved. Listing 3-22 shows the startup code.

Listing 3-22. Code that runs at startup to deserialize data and prompt user

private void Page_Load(object sender, EventArgs e) 
{ 
    DataItem di = DeSerialize(); 
    if (di != null && di.ContainsData) 
    { 
        statusBar.Text = "You have unsaved data from " + 
          di.CurrentDate.ToShortDateString(); 
        ShowUnsavedClientData(di); 
    } 
}

Private Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) 
    Dim di As DataItem = DeSerialize() 
    If Not di Is Nothing AndAlso di.ContainsData Then 
        statusBar.Text = "You have unsaved data from " & _
          di.CurrentDate.ToShortDateString() 
        ShowUnsavedClientData(di) 
    End If 
End Sub

The serialization and deserialization routines are stock implementations that grab data from the serialized object and store it in the associated text box field. The code is provided in Listing 3-23, but the details of the process are glossed over because it relates entirely to .NET Framework programming.

Listing 3-23. Serialization and deserialization routines

public void Serialize(DataItem di) 
{ 
    if (!String.IsNullOrEmpty(ConsultantNameField.Text)) 
    { 
        using (FileStream fs = new FileStream(ConsultantNameField.Text.Trim(), 
          FileMode.Create)) 
        { 
            BinaryFormatter bf = new BinaryFormatter(); 
            bf.Serialize(fs, di); 
        } 
    } 
} 
public DataItem DeSerialize() 
{ 
    if (!String.IsNullOrEmpty(ConsultantNameField.Text)) 
    { 
        try 
        { 
            using (FileStream fs = new 
            FileStream(ConsultantNameField.Text.Trim(), FileMode.Open)) 
            { 
                BinaryFormatter bf = new BinaryFormatter(); 

                return (DataItem)bf.Deserialize(fs); 
            } 
        } 
        catch (FileNotFoundException) 
        {// Perform some logging here. 
        } 
    } 
    return null; 
} 

Public Sub Serialize(ByVal di As DataItem) 
    If (Not String.IsNullOrEmpty(ConsultantNameField.Text)) Then 
        Using fs As FileStream = New _
          FileStream(ConsultantNameField.Text.Trim(), FileMode.Create) 
            Dim bf As BinaryFormatter = New BinaryFormatter() 
            bf.Serialize(fs, di) 
        End Using 
    End If 
End Sub 
Public Function DeSerialize() As DataItem 
    If (Not String.IsNullOrEmpty(ConsultantNameField.Text)) Then 
        Try 
            Using fs As FileStream = New _
    FileStream(ConsultantNameField.Text.Trim(), FileMode.Open) 
                Dim bf As BinaryFormatter = New BinaryFormatter() 

                Return CType(bf.Deserialize(fs), DataItem) 
            End Using 
        Catch e1 As FileNotFoundException 
            'Perform some logging here. 
        End Try 
    End If 
    Return Nothing 
End Function

The Serialize method uses the binary formatter to serialize the object. The DeSerialize method performs the reverse. The remainder of the code is concerned with niceties that allow application messages to be displayed neatly in the status bar object at the bottom of the form. Figure 3-22 shows the application at run time.

Figure 3-22. Accounter application at run time

Guidelines for Developing Windows Clients That Target Excel Services

As usual, there are a couple of nuances that you need to be aware of, so let's discuss those. An accountant using Office SharePoint Server 2007 could simply create a new spreadsheet, provision it for the consultants, and use the Publish feature in Office Excel 2007 to publish the spreadsheet on the server. The accountant could then provide access to the consultants through a URI. Your services weren't actually needed at all!

This leads us to a much larger question: What is the value of a .NET Framework developer in Office SharePoint Server applications? To answer that question, you need to learn to think differently. Recall that we focused on extensibility earlier on. A spreadsheet published to Office SharePoint Server can accommodate only spreadsheet functionality. However, a .NET Framework application hooked into Office SharePoint Server through EWS can integrate Windows functionality into spreadsheets, thereby extending the spreadsheet capabilities considerably.

Another important point worth mentioning is that budget-conscious shops might purchase Office SharePoint Server 2007 but elect to stay with their current version of Office, such as Microsoft Office XP or Microsoft Office 2003. Only Office Enterprise or Professional Plus versions offer the ability to publish spreadsheets.

One important advantage of this application is that it is connection aware. A published spreadsheet will lose user data if the connection is dropped. The published spreadsheet is also limited to sites that guarantee an Internet connection. This can be an issue for consultants working in the field or traveling on the road. In that regard, the customized application extends the usability of the spreadsheet application. The guiding rule here is that if the application requires integration or extra functionality not built into an Excel spreadsheet, it is a good candidate for integration with a .NET Framework client. Otherwise, it isn't and you should be content to allow an accountant to proceed without your help.

Speaking of exceptions, the observant among you might have noticed that the Accounter application contained a vicious bug in the way the programCode variable is updated. The next column is a combination of a letter and a number. However, after 26 letters, Excel uses a double-letter naming convention. Because our application does not take this into account, the code will eventually fail. The bug fix as well as the implementation of the programCode logic is left as an exercise to the reader.

Summary

This chapter presented the foundation of Web Parts. You learned to build and deploy Web Parts. There are several methods for building Web Parts. The manual approach involves a lot of legwork to create appropriate types and to sign the assembly. The deployment process is equally arduous. However, the manual process has a certain knack for imparting life-long lessons that pay dividends when automated processes go haywire.

To ease the task of writing and deploying Web Parts, Microsoft has released new Office SharePoint Server template add-ins. These templates help simplify the programming effort and bring the added advantage of the powerful Visual Studio debugger to Office SharePoint Server. Although there are many tools that can help you write Web Parts—such as the new Microsoft Office SharePoint Designer 2007,—Visual Studio is the recommended tool for developers.

The vigilant among you might have noticed that this chapter glossed over a lot of the internal details of the SharePoint infrastructure. This oversight will be rectified when Chapter 5 nosedives into the bowels of Windows SharePoint Services. For now, you have a good practical overview of developing working solutions in Office SharePoint Server. Chapter 6 will build on this platform by diving deep into advanced functionality. The reasons why things work the way they do will be explained in Chapter 5.

Finally, this chapter signed for and delivered the goods that ASP.NET programmers have been patiently waiting for. Using user controls, we have a relatively painless way to build fully functional Web Parts that approach the productivity of the Visual Studio 2005 design environment. We can combine this approach with the event code that we learned earlier and take advantage of the design-time capabilities of server controls. You can extend this technique to work with any server control.

This final step completes the puzzle and prepares us to vault beyond the ordinary into advanced programming techniques that push against the boundaries of application functionality. You'll get to experience this from a front row seat in Chapter 6 and Chapter 7.

Additional Resources

• Programming Excel Services: Chapter 1: An Introduction to Excel Services (Programming Excel Services)

• SharePoint Server Developer Portal

• Microsoft Office Developer Center