Create a connectable Spreadsheet Web Part

Microsoft Office Excel 2003

Create a connectable Spreadsheet Web Part

This procedure describes how to create two Spreadsheet Web Parts that implement Web Part connection interfaces. To make the example clearer, it assumes you are creating these two Web Parts by using the Suppliers and Products tables from the Northwind sample database on Microsoft SQL Server:

A Web Part that displays data from the Suppliers table that can send the selected row to another Web Part.
A Web Part that displays data from the Products table that can be filtered by the value it receives from another Web Part.

Because a one-to-many relationship exists between the Suppliers and Products tables on the SupplierID column, these two Web Parts can be connected to provide the SupplierID value from the Suppliers Web Part to filter the rows displayed in the Products Web Part. However, you can create similar Web Parts by using any data source that has a one-to-many relationship based on a single column.

Note You can make the process of creating a Spreadsheet Web Part simpler by using the Spreadsheet Web Part Add-in for Microsoft Office Excel 2003. This add-in is available for download from the Microsoft Download Center Web Site.

Show Create a row provider Spreadsheet Web Part

Create a Spreadsheet Web Part as described in the Create a data-bound Spreadsheet Web Part topic, selecting a table such as the Suppliers table in the Northwind sample database making sure to include the primary key SupplierID column.

Edit the solution specification file of the Web Part you created to include an InterfaceConnections element that implements an IRowProvider interface on the mapped table:

<?xml version="1.0"?>
<SolutionSpecification 
   xmlns="http://schemas.microsoft.com/WebPart/v2/Spreadsheet/SolutionSpecification">
   <WebPartSettings>
      <XMLSSFileLocation>URLToXMLSpreadsheetFile
         </XMLSSFileLocation>
      <LockedDown>True</LockedDown>
   </WebPartSettings>
   <InterfaceConnections>
      <RowProvider Name="RowProvider1_WPQ_" 
         MaxConnections="-1" 
         MenuLabel="Provide selected row" 
         Description="Sends the selected row to another Web Part.">
         <SendRow>
            <MapEntryID>Insert Map/Entry@ID</MapEntryID>
         </SendRow>
      </RowProvider>
   </InterfaceConnections>
</SolutionSpecification>

Where Insert Map/Entry@ID is the value of the ID attribute value from the <Entry> tag contained within the <Map> tag for the mapped table in the XML Spreadsheet file.

Show Create a filter consumer Spreadsheet Web Part

Create a blank worksheet in Excel.
On the Data menu, point to Import External Data, and then click Import Data.
In the Select Data Source dialog box, click New Source.
Click Data retrieval services, and then click Next.
Create a Data Retrieval Service Connections file (.uxdc) that connects to a Microsoft SQL Server table with a foreign key relationship to the first Spreadsheet Web Part, such as the Products table in the Northwind sample database. Include the foreign key column in the query, such as the SupplierID column.
Import the data as an XML list range into the current worksheet, starting at cell A1.
Hide the column containing the SupplierID column.
On the File menu, clickSave As. Navigate to a document library on the Microsoft Windows SharePoint Services server where you will be using the Spreadsheet Web Part, set Save as type to XML Spreadsheet (*.xml), type a file name, and then click OK.

Create a solution specification file that points to the XML Spreadsheet file (.xml) you created. Include an InterfaceConnections element that implements an IFilterConsumer interface that maps the ID from the first Spreadsheet Web Part to the foreign key field in the mapped table.

<?xml version="1.0"?>
<SolutionSpecification 
   xmlns="http://schemas.microsoft.com/WebPart/v2/Spreadsheet/SolutionSpecification">
   <WebPartSettings>
      <XMLSSFileLocation>URLToXMLSpreadsheetFile
         </XMLSSFileLocation>
      <LockedDown>True</LockedDown>
   </WebPartSettings>
   <InterfaceConnections>
      <FilterConsumer Name="RowConsumer1_WPQ_" 
         MaxConnections="-1" 
         MenuLabel="Get Filter From" 
         Description="Filters products by the selected SupplierID value.">
         <LocalFilter>
            <MapEntryID>Insert Map/Entry@ID</MapEntryID>
         </LocalFilter>
      </FilterConsumer>
   </InterfaceConnections>
</SolutionSpecification>

Where Insert Map/Entry@ID is the value of the ID attribute value from the <Entry> tag contained within the <Map> tag for the mapped table in the XML Spreadsheet file.

Save the solution specification file with an .xml extension directly to a document library on your Windows SharePoint Services server, or save the file locally and upload it to the document library.
Create a Web Part definition file (.dwp) that references the Spreadsheet Web Part assembly and points to the solution specification file you created. For details, see the "To create the Web Part definition file for your Spreadsheet Web Part and import it into a Web Part page" section in the Create a data-bound Spreadsheet Web Part topic.

Show Import and connect the Web Parts

Import the two Web Part definition files (.dwp) into a Web Part page.
Connect the two Spreadsheet Web Parts selecting the primary key field in the first Web Part (such as the SupplierID column in the Suppliers table) to filter the foreign key field in the second Web Part (such as the SupplierID column in the Products table).
Verify that clicking on different rows in the first Web Part filters the rows shown in the second Web Part.

previous page start next page