Writing a Filter Consumer Web Part for Windows SharePoint Services 3.0 using Excel Services

Applies to:  Windows SharePoint Services 3.0

Joel Krist, iSoftStone

May 2007

As in previous releases, Windows SharePoint Services 3.0 uses the ASP.NET Web Part connection framework to connect Web Parts to each other programmatically. Filter Web Parts use this connectivity support to provide filter criteria to other Web Parts. Developers can now allow users to provide search or filter criteria. Web Part connections are based on the concepts of "providers" and "consumers." A provider Web Part supplies information to one or more consumer Web Parts through a programmatic interface.

This Office Visual How To illustrates the steps to create a filter consumer Web Part for Windows SharePoint Services by presenting code that implements a Web Part that displays news headlines. The headlines displayed depend on the regions selected in the Geographic Region filter provider Web Part. The News Headlines Web Part uses Microsoft Excel Services to retrieve the list of all available news headlines from an Excel workbook.

For information on creating the Geographic Region filter provider Web Part, see Writing a Filter Provider Web Part for Windows SharePoint Services 3.0.

Watch the Video

Length: 10:44 | Size: 12.6 MB | Type: WMV file

Code It | Read It | Explore It

Code It

Download the Code Sample

This section walks through five major steps to create the News Headlines filter consumer Web Part:

  1. Create a Web Control Library project in Microsoft Visual Studio 2005.

  2. Add references to the required assemblies.

  3. Set the Web Part assembly version number.

  4. Sign the Web Part assembly with a strong name.

  5. Add the code that implements the functionality of the News Headlines filter consumer Web Part.

Create a Web Control Library Project in Visual Studio 2005

An easy way to create a filter consumer Web Part assembly is to use the Visual Studio 2005 Web Control Library template.

To create a Web Control Library project in Visual Studio 2005

  1. Start Visual Studio.

  2. On the File menu, point to New, and then click Project. The New Project dialog box appears.

  3. In the Project Types pane, select Visual C# or Visual Basic and then select the Windows category.

  4. In the Templates pane, select Web Control Library.

  5. Specify NewsHeadlinesWebPart for the name of the project.

  6. Specify a location for the project and click OK. Visual Studio generates a Web Control Library project containing a single source file named WebCustomControl1.cs or WebCustomControl1.vb, depending on the language selected in step 3.

  7. Rename the WebCustomControl1.cs or WebCustomControl1.vb source file to NewsHeadlinesWebPart.cs or NewsHeadlinesWebPart.vb, depending on the language being used, by right-clicking the file name in Solution Explorer and clicking Rename.

Add References to the Required Assemblies

The class that implements the News Headlines Web Part is derived from the Microsoft.SharePoint.WebPartPages.WebPart class and uses Excel Web Services. References to the Windows SharePoint Services and Excel Web Services assemblies must be added to the project to allow these assemblies to be used.

The following steps show linking directly to the Excel Web Services library. This is done because the News Headlines Web Part executes within the Windows SharePoint Services environment, rather than calling the Web service methods by using SOAP over HTTP. Linking directly in this scenario provides better performance and security and enables the News Headline Web Part to work in all SharePoint topologies. When building a separate Web application (a Web application not running within Windows SharePoint Services), the true Web service interface is used. For more information about when to link directly to the Excel Web Services DLL and when to call Excel Web Services by using SOAP over HTTP, see Loop-Back SOAP Calls and Direct Linking in the Microsoft Office SharePoint Server 2007 SDK.

If Visual Studio is running on a computer that has Office SharePoint Server 2007 installed, follow this procedure.

To add references to the required assemblies

  1. On the Project menu, click Add Reference. The Add Reference dialog box appears.

  2. Click the .NET tab. Locate and select the Excel Web Service component (Microsoft.Office.Excel.Server.WebServics.dll).

  3. Scroll down and locate the Windows SharePoint Services component (Microsoft.SharePoint.dll). Press and hold CTRL while selecting the Windows SharePoint Services component so that both it and the Excel Web Service component are selected.

  4. Click OK to add the references.

If Visual Studio is running on a computer that does not have Office SharePoint Server 2007 installed, the Windows SharePoint Services and Excel Web Services assemblies are not available. In this case you can copy the assemblies from a computer that has Office SharePoint Server 2007 installed to a local project folder on the development computer. The assembly files needed to create the News Headlines filter consumer Web Part are Microsoft.SharePoint.dll and Microsoft.Office.Excel.Server.WebServices.dll. By default these assemblies are located in the following folder on a computer that has Office SharePoint Server 2007 installed:

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI

After you have created local copies of the assemblies, you can add references to them by browsing for the local files.

To add references to local copies of the assemblies

  1. On the Project menu, click Add Reference. The Add Reference dialog box appears.

  2. Click the Browse tab and navigate to the local folder containing the copies of the Windows SharePoint Services and Excel Web Service assemblies.

  3. Select the Microsoft.SharePoint.dll and Microsoft.Office.Excel.Server.WebServices.dll files.

  4. Click OK to add the references.

To work with Excel Web Services you must add a reference to the System.Web.Services assembly.

To add a reference to System.Web.Services

  1. On the Project menu, click Add Reference. The Add Reference dialog box appears.

  2. Click the .NET tab. Locate and select the System.Web.Services component.

  3. Click OK to add the reference.

Set the Version Number of the Web Part Assembly

By default, the AssemblyVersion property of the Web Control Library project is set to increment each time the Web Part is recompiled. A SharePoint Web Part page identifies a Web Part with the version number that is specified in the web.config file. With the AssemblyVersion property set to autoincrement, if the Web Part is recompiled and redeployed after importing it into a Web Part page, the Web Part framework compares the version number that is specified in the web.config file and the version number of the deployed Web Part. If the version numbers do not match, an error occurs. To prevent the version number of the Web Part from being incremented each time a recompilation is done, set the version number of the Web Part assembly.

To set the version number of the Web Part assembly

  1. Click the Project menu, and then click NewsHeadlinesWebPart Properties.

  2. On the project Properties page, click the Application tab.

  3. Click Assembly Information.

  4. In the Assembly Information dialog box, specify 1.0.0.0 for the assembly version.

  5. Click OK to save the changes.

  6. Close the project Properties page.

Sign the Web Part Assembly with a Strong Name

To allow the Web Part assembly to call Excel Services Web service methods, it must be signed with a strong name. A strong name consists of the assembly's identity—its simple text name, version number, and culture information (if provided)—plus a public key and a digital signature.

To assign a strong name to the Web Part assembly in Visual Studio

  1. Click the Project menu and then click NewsHeadlinesWebPart Properties.

  2. On the project Properties page, click the Signing tab.

  3. Select the Sign the assembly check box.

  4. In the Choose a strong name key file list, select <New...>.

  5. In the Create Strong Name Key dialog box, enter keypair as the key file name, and clear the Protect my key file with a password check box.

  6. Close the project Properties page.

Implement the News Headlines Filter Consumer Web Part

The next step is to create the class that provides the implementation of the News Headlines filter consumer Web Part. Add the following Imports or using statements to the top of the source file for the Web Part, replacing the Imports or using statements that were generated by Visual Studio when the project was created.

Imports wsswebparts = Microsoft.SharePoint.WebPartPages
Imports aspnetwebparts = System.Web.UI.WebControls.WebParts
Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Text
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.HtmlControls
Imports System.Web.UI.WebControls
Imports System.Collections.ObjectModel
Imports Microsoft.Office.Excel.Server.WebServices
using wsswebparts = Microsoft.SharePoint.WebPartPages;
using aspnetwebparts = System.Web.UI.WebControls.WebParts;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Collections.ObjectModel;
using Microsoft.Office.Excel.Server.WebServices;

The Imports and using statements make it possible to use the classes and types defined in the referenced namespaces without having to use fully qualified namespace paths.

Next, add code to the NewsHeadlinesWebPart class so that it does the following:

  1. Creates a user interface with a DataGrid control for displaying news headlines.

  2. Exposes a consumer connection point to receive the IFilterValues interface from the Geographic Region filter provider Web Part.

  3. Uses the IFilterValues interface in the OnPreRender method and displays news headlines based on the current region filter value. The unfiltered list of all available news headlines is loaded from an Excel workbook by using Excel Services. The Excel workbook is assumed to be in a trusted file location and to have the following properties:

    • The workbook contains a worksheet named "Sheet1".

    • The "Sheet1" worksheet contains a range named "Headlines".

    • The "Headlines" range is two columns wide with the first column containing a news headline and the second column containing a region.

Replace the entire existing NewsHeadlinesWebPart class definition with the following code.

Public Class NewsHeadlinesWebPart
    Inherits wsswebparts.WebPart

    Public Class Headline
        Private m_title As String
        Private m_region As String

        Public Sub New(ByVal Title As String, ByVal Region As String)
            Me.m_title = Title
            Me.m_region = Region
        End Sub

        Public Property Title() As String
            Get
                Return Me.m_title
            End Get

            Set(ByVal value As String)
                Me.m_title = value
            End Set
        End Property

        Public Property Region() As String
            Get
                Return Me.m_region
            End Get

            Set(ByVal value As String)
                Me.m_region = value
            End Set
        End Property

    End Class

    Dim filterProviders As List(Of wsswebparts.IFilterValues) = _
        New List(Of wsswebparts.IFilterValues)()
    Dim unfilteredHeadlines As List(Of Headline)
    Dim headlinesDataGrid As DataGrid
    Dim lblError As Label

    Protected Overrides Sub CreateChildControls()
        headlinesDataGrid = New DataGrid()
        lblError = New Label()

        headlinesDataGrid.ID = "list1"
        Controls.Add(headlinesDataGrid)

        MyBase.CreateChildControls()
    End Sub

    Private Sub GetHeadlinesUsingWebService()
        Dim status As Status() = Nothing
        Dim sessionId As String = Nothing

        ' Get the list of headlines from the Excel workbook
        ' by using Excel Web Services.

        ' Initialize Excel Web Services.
        Dim es As ExcelService = New ExcelService()

        ' Open the workbook. This actionloads the workbook from the
        ' specified URL and returns a sessionId that can be used to 
        ' perform further operations on the workbook. Replace the
        ' <TrustedLocation> placeholder with a full Windows SharePoint
        ' Services location, network file share, or Web folder address
        ' of the trusted location of the Excel workbook containing
        ' the news headlines. Replace the <Workbook>
        ' placeholder with the name of the Excel workbook containing
        ' the news headlines.
        Try
            sessionId = _
                es.OpenWorkbook("<TrustedLocation>/<Workbook>.xlsx", _
                string.Empty, string.Empty, Status)
        Catch
            sessionId = Nothing
        End Try

        ' Ensure that the workbook has been successfully opened
        ' on the server. If not, show an error message to the user.
        If sessionId Is Nothing Then
            ShowError("Error opening workbook. Check the URL in " & _
                "OpenWorkbook, and be sure that the workbook is " & _
                "in a trusted location.")
            Exit Sub
        End If

        ' Retrieve the headlines/regions currently defined in the
        ' workbook from Excel Services and add them to the collection of
        ' headlines. Or, if there is a problem getting the headlines,
        ' show an error to the user.

        ' The code shown below assumes the following:
        '
        ' - The opened workbook contains a worksheet named "Sheet1".
        ' - The "Sheet1" worksheet contains a range named "Headlines".
        ' - The "Headlines" range is two columns wide with the first
        '   column containing a news headline and the second column
        '   containing a region.

        Dim AllHeadlines As Object() = es.GetRangeA1(sessionId, _
            "Sheet1", "Headlines", True, Status)

        If Not AllHeadlines Is Nothing Then
            unfilteredHeadlines = New List(Of Headline)()

            For Each HeadlineRow As Object() In AllHeadlines
                unfilteredHeadlines.Add( _
                    New Headline(Convert.ToString(HeadlineRow(0)), _
                    Convert.ToString(HeadlineRow(1))))
            Next HeadlineRow
        Else
            ShowError("Error getting headlines from workbook.")
        End If
    End Sub

    Private Sub ShowError(ByVal message As String)
        ' Show an error message to the user, and remove all other
        ' controls from the Web Part.
        lblError.Text = message
        Me.Controls.Clear()
        Me.Controls.Add(lblError)
    End Sub

    ' Use the ConnectionConsumer attribute to specify a callback method
    ' that the Web Part framework can use to provide filter provider 
    ' instances.
    <aspnetwebparts.ConnectionConsumer("News Headlines", _
        "IFilterValues", AllowsMultipleConnections:=True)> _
    Public Sub SetConnectionInterface( _
        ByVal filterProvider As wsswebparts.IFilterValues)
        If Not filterProvider Is Nothing Then
            ' Add the filter provider to the list of providers.
            Me.filterProviders.Add(filterProvider)

            ' Tell the provider the parameter we are looking for.
            Dim l As List(Of wsswebparts.ConsumerParameter) = _
                New List(Of wsswebparts.ConsumerParameter)()
            l.Add(New wsswebparts.ConsumerParameter("Region", _
wsswebparts.ConsumerParameterCapabilities.SupportsMultipleValues Or _
wsswebparts.ConsumerParameterCapabilities.SupportsAllValue))

            filterProvider.SetConsumerParameters( _
    New ReadOnlyCollection(Of wsswebparts.ConsumerParameter)(l))
        End If
    End Sub

    Protected Overrides Sub OnPreRender(ByVal e As EventArgs)
        Me.EnsureChildControls()

        ' Call Excel Web Services to get the list of all
        ' news headlines.
        GetHeadlinesUsingWebService()

        ' The filtering logic performs a union of all of the
        ' filters (a logical OR). If we didn't get any filter providers 
        ' or if any of the filters send the "All" value (that is,
        ' provider.ParameterValues == null), we don't need to 
        ' filter and we can return all of the headlines.

        Dim filteredHeadlines As List(Of Headline) = Nothing
        Dim shouldFilter As Boolean = True

        If Me.filterProviders.Count = 0 Then
            shouldFilter = False
        ElseIf Me.filterProviders.Count > 0 Then
            For Each filterProvider As wsswebparts.IFilterValues In _
            Me.filterProviders
                If filterProvider.ParameterValues Is Nothing Then
                    ' Some filter sent "All"--don't bother with the
                    ' rest of the filtering.
                    shouldFilter = False
                    Exit For
                End If
            Next filterProvider
        End If

        If Not shouldFilter Then
            ' The "filtered" headlines are unfiltered.
            filteredHeadlines = Me.unfilteredHeadlines
        Else
            ' Just fill in the headlines that match the filters.

            filteredHeadlines = New List(Of Headline)()

            ' Create a lookup from region to a list of headlines that 
            ' correspond to that region.
            Dim regionHeadlineMap As Dictionary( _
            Of String, List(Of Headline)) = _
                New Dictionary(Of String, List(Of Headline))()

            For Each headline As Headline In Me.unfilteredHeadlines
                Dim headlinesForRegion As List(Of Headline) = Nothing

                If Not regionHeadlineMap.TryGetValue( _
                headline.Region, headlinesForRegion) Then
                    headlinesForRegion = New List(Of Headline)()
                    regionHeadlineMap.Add(headline.Region, _
                    headlinesForRegion)
                End If

                headlinesForRegion.Add(headline)
            Next headline

            For Each filterProvider As wsswebparts.IFilterValues In _
            Me.filterProviders

                Dim values As ReadOnlyCollection(Of String) = _
                filterProvider.ParameterValues

                If Not values Is Nothing Then
                    For Each v As String In values
                        If v Is Nothing Then
                            ' This indicates the "Empty" value, which
                            ' doesn't apply to headlines, because
                            ' they all have regions.
                        Else
                            Dim matchedHeadlines _
                                As List(Of Headline) = Nothing

                            If regionHeadlineMap.TryGetValue(v, _
                            matchedHeadlines) Then
                                For Each matchedHeadline As Headline _
                                In  matchedHeadlines
                                    If Not _
                       filteredHeadlines.Contains(matchedHeadline) Then
                                filteredHeadlines.Add(matchedHeadline)
                                    End If
                                Next matchedHeadline
                            End If
                        End If
                    Next v
                End If
            Next filterProvider

        End If

        ' Display the filtered headlines.
        headlinesDataGrid.DataSource = filteredHeadlines
        headlinesDataGrid.DataBind()

        MyBase.OnPreRender(e)
    End Sub
End Class
public class NewsHeadlinesWebPart : wsswebparts.WebPart
{
    public class Headline
    {
        private string title;
        private string region;

        public Headline(string Title, string Region)
        {
            this.title = Title;
            this.region = Region;
        }

        public string Title
        {
            get
            {
                return this.title;
            }
            set
            {
                this.title = value;
            }
        }

        public string Region
        {
            get
            {
                return this.region;
            }
            set
            {
                this.Region = value;
            }
        }
    }

    List<wsswebparts.IFilterValues> filterProviders =
        new List<wsswebparts.IFilterValues>();
    List<Headline> unfilteredHeadlines;

    DataGrid headlinesDataGrid;
    Label lblError;

    protected override void CreateChildControls()
    {
        headlinesDataGrid = new DataGrid();
        lblError = new Label();
        unfilteredHeadlines = new List<Headline>();

        headlinesDataGrid.ID = "list1";
        Controls.Add(headlinesDataGrid);

        base.CreateChildControls();
    }

    private void GetHeadlinesUsingWebService()
    {
        Status[] status = null;
        string sessionId = null;

        // Get the list of headlines from the Excel workbook by calling
        // Excel Web Services.
            
        // Initialize Excel Web Services.
        ExcelService es = new ExcelService();

        // Open the workbook. This actionloads the workbook from the
        // specified URL and returns a sessionId that can be used to 
        // perform further operations on the workbook. Replace the
        // <TrustedLocation> placeholder with a full Windows SharePoint
        // Services location, network file share, or Web folder address
        // of the trusted location of the Excel workbook containing
        // the news headlines. Replace the <Workbook>
        // placeholder with the name of the Excel workbook containing
        // the news headlines.
        try
        {
            sessionId = 
                es.OpenWorkbook("<TrustedLocation>/<Workbook>.xlsx",
                string.Empty, string.Empty, out status);
        }
        catch
        {
            sessionId = null;
        }

        // Ensure that the workbook has been successfully opened on the 
        // server. If not, show an error message to the user.
        if (sessionId == null)
        {
            ShowError("Error opening workbook. Check the URL in " +
                "OpenWorkbook, and be sure that the workbook is in " +
                "a trusted location");
            return;
        }

        // Retrieve the headlines/regions currently defined in the
        // workbook from Excel Services and add them to the collection of 
        // headlines. Or, if there is a problem getting the headlines, 
        // show an error to the user.

        // The code shown below assumes the following:
        //
        // - The opened workbook contains a worksheet named "Sheet1".
        // - The "Sheet1" worksheet contains a range named "Headlines".
        // - The "Headlines" range is two columns wide with the first
        //   column containing a news headline and the second column
        //   containing a region.

        object[] AllHeadlines = es.GetRangeA1(sessionId, "Sheet1",
            "Headlines", true, out status);

        if (AllHeadlines != null)
        {
            unfilteredHeadlines = new List<Headline>();

            foreach (object[] HeadlineRow in AllHeadlines)
            {
                unfilteredHeadlines.Add(
                    new Headline(Convert.ToString(HeadlineRow[0]), 
                    Convert.ToString(HeadlineRow[1])));
            }
        }
        else
        {
            ShowError("Error getting headlines from workbook.");
            return;
        }
    }

    private void ShowError(string message)
    {
        // Show an error message to the user, and remove all other
        // controls from the Web Part.
        lblError.Text = message;
        this.Controls.Clear();
        this.Controls.Add(lblError);
    }

    // Use the ConnectionConsumer attribute to specify a callback
    // method that the Web Part framework can use to provide filter 
    // provider instances.
    [aspnetwebparts.ConnectionConsumer("News Headlines", 
     "IFilterValues", AllowsMultipleConnections = true)]
    public void SetConnectionInterface(
        wsswebparts.IFilterValues filterProvider)
    {
        if (filterProvider != null)
        {
            // Add the filter provider to the list of providers.
            this.filterProviders.Add(filterProvider);

            // Tell the provider the parameter we are looking for.
            List<wsswebparts.ConsumerParameter> l =
                new List<wsswebparts.ConsumerParameter>();
            l.Add(new wsswebparts.ConsumerParameter("Region",
wsswebparts.ConsumerParameterCapabilities.SupportsMultipleValues |
wsswebparts.ConsumerParameterCapabilities.SupportsAllValue));

            filterProvider.SetConsumerParameters(
            new ReadOnlyCollection<wsswebparts.ConsumerParameter>(l));
        }
    }

    protected override void OnPreRender(EventArgs e)
    {
        this.EnsureChildControls();

        // Call Excel Web Services to get the list of all
        // news headlines.
        GetHeadlinesUsingWebService();

        // The filtering logic performs a union of all of the
        // filters (a logical OR). If we didn't get any filter 
        // providers or if any of the filters send the "All" value
        // (that is, provider.ParameterValues == null), we don't 
        // need to filter and we can return all of the 
        // headlines.

        List<Headline> filteredHeadlines = null;

        bool shouldFilter = true;
        if (this.filterProviders.Count == 0)
        {
            shouldFilter = false;
        }
        else if (this.filterProviders.Count > 0)
        {
            foreach (wsswebparts.IFilterValues filterProvider in
            this.filterProviders)
            {
                if (filterProvider.ParameterValues == null)
                {
                    // Some filter sent "All"--don't bother with the
                    // rest of the filtering.
                    shouldFilter = false;
                    break;
                }
            }
        }

        if (!shouldFilter)
        {
            // The "filtered" headlines are unfiltered.
            filteredHeadlines = this.unfilteredHeadlines;
        }
        else
        {
            // Just fill in the headlines that match the filters.

            filteredHeadlines = new List<Headline>();

            // Create a lookup from region to a list of headlines that 
            // correspond to that region.
            Dictionary<string, List<Headline>> regionHeadlineMap =
            new Dictionary<string, List<Headline>>();
            foreach (Headline headline in this.unfilteredHeadlines)
            {
                List<Headline> headlinesForRegion = null;
                if (!regionHeadlineMap.TryGetValue(headline.Region,
                    out headlinesForRegion))
                {
                    headlinesForRegion = new List<Headline>();
                    regionHeadlineMap.Add(headline.Region,
                        headlinesForRegion);
                }

                headlinesForRegion.Add(headline);
            }

            foreach (wsswebparts.IFilterValues filterProvider in
            this.filterProviders)
            {

                ReadOnlyCollection<String> values = 
                    filterProvider.ParameterValues;
                if (values != null)
                {
                    foreach (string v in values)
                    {
                        if (v == null)
                        {
                            // This indicates the "Empty" value, which
                            // doesn't apply to headlines, because 
                            // they all have regions.
                        }
                        else
                        {
                            List<Headline> matchedHeadlines;
                            if (regionHeadlineMap.TryGetValue(v,
                                out matchedHeadlines))
                            {
                                foreach (Headline matchedHeadline in
                                matchedHeadlines)
                                {
                                    if
(!filteredHeadlines.Contains(matchedHeadline))
                                    {
filteredHeadlines.Add(matchedHeadline);
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        // Display the filtered headlines.
        headlinesDataGrid.DataSource = filteredHeadlines;
        headlinesDataGrid.DataBind();

        base.OnPreRender(e);
    }
}

Read It

Introduced in Windows SharePoint Services 3.0, filter Web Parts use the Web Part connection support to provide filter criteria from one Web Part (the "provider" Web Part) to another Web Part (the "consumer" Web Part). This makes it possible for developers to build more sophisticated applications incorporating the 2007 Microsoft Office system by enabling users to provide search and filter criteria.

To learn how to package and deploy the News Headlines filter consumer Web Part created in this Visual How To, and how to improve the appearance and configurability of a Web Part, see Deploying and Optimizing a SharePoint Web Part That Calls Excel Web Services.

Explore It