Adding Excel Web Parts to Pages Programmatically in SharePoint Server 2007

Summary: Learn how to programmatically add an instance of the Excel Web Access Web Part to a Microsoft Office SharePoint Server 2007 page.

Applies to: 2007 Microsoft Office System, Microsoft Office SharePoint Server 2007, Excel Services

Joel Krist, Akona Systems

October 2007

Excel Services is part of Microsoft Office SharePoint Server 2007. Excel Services is built on ASP.NET and Windows SharePoint Services 3.0 technologies. There are three core Excel Services components: Excel Web Access Excel Web Services Excel Calculation Services Excel Web Access is an Excel Services Web Part in Office SharePoint Server 2007 that renders live Microsoft Office Excel 2007 workbooks on a Web page, and enables the user to interact with those workbooks. Excel Web Access is the visible Excel Services component for the user. You can use Excel Web Access like any other Web Part in Office SharePoint Server 2007, and it does not require anything to be installed on the user's client computer except a compatible Web browser. This Office Visual How To demonstrates how to programmatically add an instance of the Excel Web Access Web Part to an Office SharePoint Server 2007 page.

Watch the Video Length: 09:14 | Size: 6.98 MB | Type: WMV file

Code It | Read It | Explore It

Code It

Download the Code Sample

In the following sections, you use Microsoft Visual Studio 2005 to create an application for Windows that presents a simple user interface that accepts information from the user and then adds the Excel Web Access Web Part to an Office SharePoint Server page. The code in this article uses the Office SharePoint Server object model. The steps in the following sections require that you run Visual Studio on a computer with Office SharePoint Server 2007 installed. The four major steps in this demonstration are:

1. Creating a Windows application project in Visual Studio 2005.

2. Adding references to the required assemblies to the Visual Studio project.

3. Designing the user interface of the sample application.

4. Implementing the functionality that adds the Excel Web Access Web Part to an Office SharePoint Server page.

First, you create a Windows application project in Visual Studio 2005.

1. Start Visual Studio 2005.

2. On the File menu, point to New, and then click Project.

3. In the New Project dialog box, in the Project Types pane, expand Visual C# or Visual Basic, and then select Windows.

4. In the Templates pane, select Windows Application.

5. Type AddEWATool for the name of the project.

6. Specify a location for the project, and then click OK.

   Visual Studio generates a Windows application project containing a single source file named Form1.cs or Form1.vb, depending on the language you selected in Step 3.

The sample code in this article uses the ExcelWebRenderer class from the Microsoft.Office.Excel.WebUI namespace and other classes from the Microsoft.SharePoint namespace. You need to add references to the necessary assemblies to the project to enable these objects to be used.

The Microsoft.Office.Excel.WebUI assembly, which provides the implementation of the ExcelWebRenderer class, is located in the global assembly cache (GAC). However, it is not displayed on the .NET tab of the Visual Studio Add Reference dialog box. To add a reference to the Microsoft.Office.Excel.WebUI assembly, you can browse to it from the Visual Studio Add Reference dialog box. The article How to: Locate and Copy Microsoft.Office.Excel.WebUI.dll provides information about how to locate and copy the Microsoft.Office.Excel.WebUI.dll file. However, for the purposes of this how-to article, you need only to determine the fully qualified path to the assembly file. Determine the fully qualified path to the assembly file, and then use the following procedure to add a reference to it.

1. In Solution Explorer, right-click the AddEWATool project, and then click Add Reference.

2. In the Add Reference dialog box, on the Browse tab, for File name, type the fully qualified path to the Microsoft.Office.Excel.WebUI.dll assembly file.

3. Click OK to add the reference.

   Figure 1. Adding a reference to Microsoft.Office.Excel.WebUI.dll

   

You also need to add a reference to the Microsoft.SharePoint assembly.

1. In Solution Explorer, right-click the AddEWATool project, and then click Add Reference.

2. In the Add Reference dialog box, on the .NET tab, locate and select the Windows SharePoint Services component (Microsoft.SharePoint.dll).

3. Click OK to add the reference.

Next, add a reference to the System.Web assembly to the project.

1. In Solution Explorer, right-click the AddEWATool project, and then click Add Reference.

2. In the Add Reference dialog box, on the .NET tab, locate and select the System.Web component (System.Web.dll).

3. Click OK to add the reference.

The AddEWATool sample application presents a simple user interface: three text boxes, a progress bar, and a button.

Figure 2. User interface

To design the user interface in Visual Studio, open the Form1.cs or Form1.vb file in the Visual Studio designer, and then add the following controls to the form.

After adding references to the required assemblies and designing the user interface, you add the code that provides the ability to add the Excel Web Access Web Part to an Office SharePoint Server page.

1. In the Visual Studio designer, double-click button1 to open the Form1.cs file or the Form1.vb file in code view.

   Visual Studio displays the source code for the form with the button1_Click button click event handler visible.

2. Add the following Imports or using statements to the top of the Form1.cs or Form1.vb source file. If you are using the Form1.cs file, add the statements after the using statements that Visual Studio generated when you created the project.

   Imports Microsoft.SharePoint
   Imports Microsoft.SharePoint.WebPartPages
   Imports Microsoft.SharePoint.Administration
   Imports Microsoft.Office.Excel.WebUI
   

   The Imports and using statements enable you to use the classes and types defined in the referenced namespaces without needing to use fully qualified namespace paths.

3. Add the following variable declarations to the top of the Form1 class definition.

   Private appName As String = "Add EWA Tool"
   Private specifyPageURLMsg As String = "Please specify the URL of " + _
       "the page to be modified, for example, " + _
       "https://server/site/default.aspx"
   Private specifyWorkbookURIMsg As String = "Please specify the " + _
       "location of a workbook from a trusted location."
   Private specifyZoneIDMsg As String = "Please specify the ID of " + _
       "the zone to add the Web Part to."
   Private siteProblemMsg As String = "There was a problem with the " + _
       "URL of the page. Please check that the site and page exist."
   Private addProblemMsg As String = "There was a problem adding " + _
       "the Web Part."
   Private pageCheckInMsg As String = "Added Excel Web Access Web Part."
   Private successMsg As String = "Web Part successfully added."
   

   Note

   To determine the path to a workbook in Office SharePoint Server 2007, right-click the workbook file, and then click Copy Shortcut. Or, right-click the workbook file, and then click Properties to display the address (URL) of the workbook. The target workbook must reside in a trusted location; if it does not, the Excel Web Access Web Part cannot open it. For more information about how to trust a location by using the SharePoint Central Administration site, see How to: Trust a Location.

4. Add the following code to the body of the button1_Click event handler.

   Dim pageURL As String = textBox1.Text
   Dim bookURI As String = textBox2.Text
   Dim zoneID As String = textBox3.Text
   
   ' Initialize the progress bar.
   progressBar1.Minimum = 0
   progressBar1.Maximum = 4
   progressBar1.Step = 1
   progressBar1.Value = 0
   
   If (AddWebPart(pageURL, bookURI, zoneID)) Then
       MessageBox.Show(successMsg, appName, _
           MessageBoxButtons.OK, MessageBoxIcon.Information)
   End If
   
   progressBar1.Value = 1
   

5. Add the following code to the definition of the Form1 class to define the AddWebPart method.

   Public Function AddWebPart(ByVal pageURL As String, _
       ByVal workbookURI As String, ByVal zoneID As String) As Boolean
   
       Dim targetSite As SPSite = Nothing
       Dim targetWeb As SPWeb = Nothing
       Dim webpartManager As SPLimitedWebPartManager = Nothing
       Dim targetPage As SPFile = Nothing
       Dim ewaWebPart As ExcelWebRenderer = Nothing
       Dim pageCheckedOut As Boolean = False
   
       Try
           ' Validate arguments.
           If String.IsNullOrEmpty(pageURL) Then
               Throw New Exception(specifyPageURLMsg)
           End If
   
           If String.IsNullOrEmpty(workbookURI) Then
               Throw New Exception(specifyWorkbookURIMsg)
           End If
   
           If String.IsNullOrEmpty(zoneID) Then
               Throw New Exception(specifyZoneIDMsg)
           End If
   
           ' Step 1 completed.
           progressBar1.PerformStep()
   
           ' Open the site.
           Try
               targetSite = New SPSite(pageURL)
               targetWeb = targetSite.OpenWeb()
           Catch exc As Exception
               Throw New Exception(siteProblemMsg + "\n" + exc.Message)
           End Try
   
           ' Step 2 completed.
           progressBar1.PerformStep()
   
           ' Create an instance of the Excel Web Access Web Part 
           ' and set its WorkbookUri property.
           ewaWebPart = New ExcelWebRenderer()
           ewaWebPart.WorkbookUri = workbookURI
   
           ' Step 3 completed.
           progressBar1.PerformStep()
   
           ' Add the Excel Web Access Web Part to the specified
           ' zone in the specified page.
           Try
               ' Check out the page so it can be modified.
               targetPage = targetWeb.GetFile(pageURL)
               targetPage.CheckOut()
               pageCheckedOut = True
   
               webpartManager = targetPage.GetLimitedWebPartManager( _
                 System.Web.UI.WebControls.WebParts.PersonalizationScope.Shared)
               webpartManager.AddWebPart(ewaWebPart, zoneID, 0)
           Catch exc As Exception
               Throw New Exception(addProblemMsg + "\n" + exc.Message)
           Finally
               ' Check in and publish the page.
               If pageCheckedOut Then
                   targetPage.CheckIn(pageCheckInMsg, _
                       SPCheckinType.MajorCheckIn)
               End If
           End Try
   
           ' Step 4 completed.
           progressBar1.PerformStep()
   
       Catch ex As Exception
           MessageBox.Show(ex.Message, appName, _
               MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
   
           Return False
       Finally
           If Not targetWeb Is Nothing Then
               targetWeb.Close()
           End If
   
           If Not targetSite Is Nothing Then
               targetSite.Close()
           End If
       End Try
   
       Return True
   End Function
   

6. Build the AddEWATool project.

Read It

This article demonstrates how to programmatically add an instance of the Excel Web Access Web Part to an Office SharePoint Server 2007 page. The steps are:

1. Creating a Windows application project in Visual Studio.

2. Adding references to the required assemblies to the Visual Studio project.

3. Designing the user interface of the sample application.

4. Implementing the functionality that adds the Excel Web Access Web Part to a page.

Explore It

• Excel Services Architecture

• How to: Programmatically Add an Excel Web Access Web Part to a Page

• How to: Trust a Location

• How to: Trust Workbook Locations Using Script

• SharePoint Server 2007 Developer Center

• Microsoft Office Developer Center