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.

Office Visual How To

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

Joel Krist, Akona Systems

October 2007

Overview

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.

See It Adding Excel Web Parts to Pages Programmatically

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.

Creating a Windows Application Project in Visual Studio

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

To create a Windows application project in Visual Studio

  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.

Adding References to the Required Assemblies

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.

To add a reference to the Microsoft.Office.Excel.WebUI assembly

  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

    Add reference to Microsoft.Office.Excel.WebUI.dll

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

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.

To add a reference to the System.Web 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 System.Web component (System.Web.dll).

  3. Click OK to add the reference.

Designing the Sample Application User Interface

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

Figure 2. User interface

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.

Table 1. Form1 controls

Control Type Name Purpose

TextBox

textBox1

Specifies page URL.

TextBox

textBox2

Specifies workbook URI.

TextBox

textBox3

Specifies Web Part zone.

ProgressBar

progressBar1

Shows progress status.

Button

button1

Adds Web Part to page.

Implementing the Add Web Part Functionality

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.

To implement the functionality that adds the Excel Web Access Web Part to a SharePoint 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
    
    using Microsoft.SharePoint;
    using Microsoft.SharePoint.WebPartPages;
    using Microsoft.SharePoint.Administration;
    using 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."
    
    private string appName = "Add EWA Tool";
    private string specifyPageURLMsg = "Please specify the URL of " + 
        "the page to be modified, for example, " +
        "https://server/site/default.aspx";
    private string specifyWorkbookURIMsg = "Please specify the " +
        "location of a workbook from a trusted location.";
    private string specifyZoneIDMsg = "Please specify the ID of " +
        "the zone to add the Web Part to.";
    private string siteProblemMsg = "There was a problem with the " +
        "URL of the page. Please check that the site and page exist.";
    private string addProblemMsg = "There was a problem adding the " +
        "Web Part.";
    private string pageCheckInMsg = "Added Excel Web Access Web Part.";
    private string successMsg = "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
    
    string pageURL = textBox1.Text;
    string bookURI = textBox2.Text;
    string zoneID = textBox3.Text;
    
    // Initialize the progress bar.
    progressBar1.Minimum = 0;
    progressBar1.Maximum = 4;
    progressBar1.Step = 1;
    progressBar1.Value = 0;
    
    if (AddWebPart(pageURL, bookURI, zoneID))
        MessageBox.Show(successMsg, appName,
            MessageBoxButtons.OK, MessageBoxIcon.Information);
    
    progressBar1.Value = 0;
    
  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
    
    public bool AddWebPart(string pageURL, string workbookURI,
        string zoneID)
    {
        SPSite targetSite = null;
        SPWeb targetWeb = null;
        SPLimitedWebPartManager webpartManager = null;
        SPFile targetPage = null;
        ExcelWebRenderer ewaWebPart = null;
        bool pageCheckedOut = false;
    
        try
        {
            // Validate arguments.
            if (String.IsNullOrEmpty(pageURL))
                throw new Exception(specifyPageURLMsg);
    
            if (String.IsNullOrEmpty(workbookURI))
                throw new Exception(specifyWorkbookURIMsg);
    
            if (String.IsNullOrEmpty(zoneID))
                throw new Exception(specifyZoneIDMsg);
    
            // Step 1 completed.
            progressBar1.PerformStep();
    
            // Open the site.
            try
            {
                targetSite = new SPSite(pageURL);
                targetWeb = targetSite.OpenWeb();
            }
            catch (Exception exc)
            {
                throw new Exception(siteProblemMsg + "\n" + exc.Message);
            }
    
            // 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;
    
                // Add the Web Part.
                webpartManager = targetPage.GetLimitedWebPartManager(
                  System.Web.UI.WebControls.WebParts.PersonalizationScope.Shared);
                webpartManager.AddWebPart(ewaWebPart, zoneID, 0);
            }
            catch (Exception exc)
            {
                throw new Exception(addProblemMsg + "\n" + exc.Message);
            }
            finally
            {
                // Check in and publish the page.
                if (pageCheckedOut)
                    targetPage.CheckIn(pageCheckInMsg, 
                        SPCheckinType.MajorCheckIn);
            }
    
            // Step 4 completed.
            progressBar1.PerformStep();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, appName,
                MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
    
            return false;
        }
        finally
        {
            if (null != targetWeb)
                targetWeb.Close();
    
            if (null != targetSite)
                targetSite.Close();
        }
    
        return true;
    }
    
  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