Export (0) Print
Expand All
Expand Minimize

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

Windows SharePoint Services 3

Visual How To

Applies to:  Windows SharePoint Services 3.0

Joel Krist, iSoftStone

May 2007

Overview

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.

See It

Writing a Filter Consumer WebPart

Watch the Video

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

Code It | Read It | Explore 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.

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 : 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);
    }
}

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.

Show:
© 2014 Microsoft