Inside SharePointExtending Office Applications with Web Services

Pav Cherny

Contents

SharePoint Web Services and the Object Model
Outlook 2007 and the StsSync Protocol
Excel 2007 and Excel Web Access
Access 2007 and the Access Web Datasheet Control
Word 2007 and the MetaWeblog API
PowerPoint 2007 and MOSS 2007
InfoPath 2007 and InfoPath Forms Services
Conclusion

The 21 standard Web services in Microsoft Windows SharePoint Services (WSS) 3.0 and 19 standard Web services in Microsoft Office SharePoint Server (MOSS) 2007 enable you to create remarkably flexible solutions for retrieving data from local and remote resources, extending standard Microsoft Office suite features and integrating business applications. Of course, you may have even more than 40 Web services. If you install additional server solutions that integrate with SharePoint, such as Microsoft Office Project Server 2007, you might find that the count on your SharePoint server easily exceeds 60 or more Web services. And that doesn't even count any of the custom SharePoint Web services you might want to implement. Web services are fundamental building blocks of distributed business applications, and in the era of Software-plus-Services, Web services are even more significant because they provide the means for organizations to integrate on-premise solutions with hosted environments.

In this column, I discuss the advantages of Web services in a SharePoint environment, specifically their use in and around standard Office applications. Although Office applications typically rely on Microsoft Office FrontPage and WSS Remote Procedure Calls (RPCs), and Web Distributed Authoring and Versioning (WebDAV) for file-based access to SharePoint lists and libraries, they also use Web services. The basic Web services included in WSS 3.0 are sufficient for most Office applications, such as Microsoft Word 2007, Outlook 2007, and Access 2007, while others, such as PowerPoint 2007 and InfoPath 2007, can use additional Web services that are available only with MOSS 2007 or with separate products. I mention product dependencies where necessary. If you want to follow my explanations in a test environment, you can find worksheets with step-by-step instructions in the companion material for this column, available in the Code Download section at technet.microsoft.com/magazine/bb978519.

Exhaustive developer discussions and business data integration topics are beyond the scope of this article. I assume you are familiar with Web services and their use in business applications. For a general overview regarding the built-in Web services of WSS 3.0 and MOSS 2007, I recommend the article "Web Services Access" in the SharePoint Server 2007 SDK as a starting point (see msdn.microsoft.com/library/bb862916.aspx) .

SharePoint Web Services and the Object Model

A classic question in SharePoint solutions development is whether to use Web services or the object model. The object model is easier to use, exposes more interfaces, and has less overhead than Web services, yet the object model is only available on the SharePoint server due to COM dependencies. Web services are more accessible and are therefore the right choice for client applications and server-side components, such as Business Data Web parts, that need to aggregate data from remote sources. As data moves from local, on-premise systems into remote, hosted environments, SharePoint solutions must increasingly embrace a service-oriented architecture (SOA) to be able to deal with distributed SharePoint environments, as illustrated the diagram in Figure 1.

Figure 1 Web Services in a distributed SharePoint environment (Click the image for a larger view)

By eliminating the tight coupling between front-end and back-end subsystems, Web services provide extensive flexibility. As Figure 1 suggests, front-ends can interface with any underlying platform as long as the intermediary Web service exposes the expected interface. I demonstrated this in my September 2008 column (technet.microsoft.com/magazine/cc742803) by replacing the built-in WSS 3.0 E-mail Integration Web Service (Sharepoint­EmailWS.asmx) with a custom version that exposes the same interface.

Of course, the same principle also applies to client apps, such as Office applications that consume SharePoint Web services. Stephen Toub demonstrated this very intuitively in his MSDN article "Custom Calendar Providers for Outlook 2003" by building a custom Web service that mirrors the interface of the SharePoint Lists service (Lists.asmx) to return custom data instead of SharePoint list items to Outlook (see msdn.microsoft.com/aa168130). He showed how an Outlook calendar can display such diverse data as entries from the event log, items in an RSS feed, postings from a newsgroup, conversations in Windows Live Messenger, and system restore points on a computer, and later expanded the solution to support Contacts lists as well.

In 2006, Patrick Creehan updated the implementation so that it now works with Outlook 2007. (See Patrick's blog entry "Custom Calen­dar Providers for Outlook 2007" available at blogs.msdn.com/pcreehan/archive/2006/11/21/custom-calendar-providers-for-outlook-2007.aspx.)

Outlook 2007 and the StsSync Protocol

Now that you know that custom list providers can take advantage of the Outlook integration with SharePoint, let's take a closer look at the Outlook/SharePoint interaction. To access the contents of a SharePoint list or document library, Outlook communicates with SharePoint through the Lists Web service and displays the lists and their items side-by-side with standard Outlook folders. You can also create new or update existing items directly in Outlook, in which case Outlook uses the Lists service to update the SharePoint repositories. The interaction and the solution architecture are relatively straightforward, as illustrated in Figure 2.

Figure 2 Outlook StsSync architecture (Click the image for a larger view)

In order to link to the desired lists, Outlook supports a special URL format based on the StsSync protocol identifier that is registered with Outlook as the protocol handler. You can find the corresponding registration on your workstation in the following Registry key: HKEY_CLASSES_ROOT\StsSync\Shell\Open\Command. Outlook receives the StsSync URL as a command-line parameter, breaks it up into its individual parts, and then uses the parameters to link to the specified SharePoint list.

StsSync URLs are long and cryptic because they contain several query string parameters in typical URL character-encoded format, such as:

stssync://sts/?ver=1.0&type=calendar&cmd=add-­folder&base-url=http%3A%2F%2Fsharepoint%2FHR%2FAdministration&list-url=%2FLists%2FCalendar%2FAllItems%2Easpx&guid=%7B4DF13090%2DDE1C%2D43EA%2D8B44%2D904869FEABC4%7D&site-name=HR%20Site&list-name=Admin%20Events

Fortunately you do not need to specify these URLs directly if you link Outlook to the desired list from within the SharePoint user interface. Just open the SharePoint list in Internet Explorer and then on the list's Actions menu, click Connect to Outlook. This command calls a JavaScript function named ExportHailStorm (located on the server in \%PROGRAMFILES%\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\LAYOUTS\1033\Init.js), which constructs the StsSync URL based on list parameters and then passes the resulting URL to Internet Explorer, which in turn passes this URL to the registered protocol handler, which is Outlook. For an example of how to work with SharePoint lists in Outlook, see the companion worksheet "Working with Outlook in WSS."

One of the most important StsSync parameters is base-url, which points to the SharePoint site, such as sharepoint/HR/Administration. Outlook auto­matically appends a reference to the Lists Web service to this URL (for example, sharepoint/HR/Administration/_vti_bin/Lists.asmx). Then the road is clear to communicate with SharePoint. The individual parts of the StsSync URL are explained in the StsSync Structure Specification at msdn.microsoft.com/cc313101.

Excel 2007 and Excel Web Access

In Figure 2, you might have noticed the Export to Spreadsheet option underneath Connect to Outlook on the SharePoint Actions menu, which exports the contents of a list into an Excel spreadsheet. Excel 2007 does not use the StsSync protocol. Instead, Export to Spreadsheet calls a function named ExportList (located on the SharePoint server in \%PROGRAMFILES%\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\LAYOUTS\1033\Core.js), which initiates an interesting sequence of steps to export the list items. This feature does not use Web services.

The ExportList call results in an HTTP request directed at an ISAPI extension (Owssvr.dll) to generate and return a Web query (.iqy) file. This .iqy file contains a Web query statement that references Owssvr.dll to retrieve the actual data from the SharePoint list in the form of an XML document. You might consider Owssvr.dll a prehistoric version of a Web service, but its interface does not adhere to industry standards for Web services, so it is valid to say that Excel doesn't use Web services out of the box.

An Excel solution that does use Web services is Excel Web Access, which is included in MOSS 2007 and is illustrated in Figure 3. In fact, Excel Web Access uses Excel Services (ExcelService.asmx), which in turn relies on Excel Calculation Services (ECS), a calculation engine that provides the same calculation functions as the Excel 2007 desktop application. Excel Web Access is designed to work as a data view and parameterized analysis tool. By assigning specific cells as parameters, enabling only specific parts of the worksheet to be viewable online, and setting SharePoint access permissions on the workbook itself, administrators can gain a very granular degree of control over what users can access in an Excel workbook. For security reasons, Excel Web Access and Excel Services do not support some of the features of Excel 2007, such as inline Visual Basic code, form controls, and external references. However, solution developers can create managed code that can be accessed from within a workbook on SharePoint. For more information about Excel Services, including step-by-step how-to guides, see the white­ paper "Excel Services Step-by-Step Guides" at technet.microsoft.com/cc263430.

Figure 3 Excel and Excel Web Access architecture (Click the image for a larger view)

Access 2007 and the Access Web Datasheet Control

Similar to exporting a SharePoint list to a spreadsheet, you can export a list to a database by using the Open with Access option on the Actions menu. Open with Access also relies on the Owssvr.dll ISAPI extension to obtain data from SharePoint. If you click on Open with Access, you call the JavaScript function ExportToDatabase (located on the SharePoint server in %\PROGRAMFILES%\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\LAYOUTS\1033\Core.js), which instantiates a SharePoint.ExportDatabase ActiveX control. This control communicates with Owssvr.dll and creates the Access database with tables, views, reports, and other objects linked to the SharePoint list objects or copied into the database from SharePoint according to your export choices. See the companion worksheet "Working with Access in WSS" to get started with SharePoint lists in Access 2007.

Whereas the SharePoint.ExportDatabase ActiveX control communicates with Owssvr.dll and does not use Web services, Access 2007 actually uses SharePoint Web services via a DLL called Stslist.dll, as illustrated in Figure 4. For example, you can display the External Data ribbon in Access 2007, click on SharePoint List, and then link to the desired list as the data source of a linked table. In this scenario, Access calls the Lists Web service (Lists.asmx) on the server. You can then use SQL statements to search or update the data in Access. Access also calls Lists.asmx every time you modify a record in a linked table to ensure that the SharePoint data remains current.

Figure 4 Access and Access Web Datasheet architecture (Click the image for a larger view)

You can also export individual Access tables as lists to SharePoint and even move an entire Access database to a SharePoint site, which creates a SharePoint list for each table and moves all the data to SharePoint in one bulk operation. Although SharePoint cannot enforce referential integrity in its lists (users moving databases with referential entries to SharePoint will receive a warning message to that effect), it does allow references between lists as though they were tables. When exporting tables from Access, Access communicates with the Webs (Webs.asmx) and Views (Views.asmx) Web services in addition to Lists.asmx to create a list for each table as well as a site column for each table column, and list views based on these site columns.

Following a successful export, you might want to view a SharePoint list in a Datasheet view. Just click on the Edit in Datasheet option on the list's Actions menu. Edit in Datasheet redirects the browser to a Web page that references the Access Web Datasheet control through the following CLSID: 65BCBEE4-7728-41A0-97BE-14E1CAE36AAE. Internet Explorer then loads this ActiveX control on the local workstation into the Web page. If you search through the registry on a workstation running the 2007 Office system, you can find the corresponding registration entry for the ActiveX control (ProgID ListNet.ListNet) in the HKEY_CLASSES_ROOT hive. Check out the InprocServer32 registry key and you can see that the Access Web Datasheet control is implemented in Stslist.dll. The Access Web Datasheet control uses the same Web services as does Access 2007.

Additional Resources

SharePoint Products and Technologies Web Site

microsoft.com/sharepoint

Windows SharePoint Services TechCenter

technet.microsoft.com/windowsserver/sharepoint

Windows SharePoint Services Developer Center

msdn.microsoft.com/sharepoint

Microsoft SharePoint Products and Technologies Team Blog

blogs.msdn.com/sharepoint

Word 2007 and the MetaWeblog API

Word 2007 uses a unique Web service implemented in a file called Metaweblog.aspx to take advantage of the MetaWeblog API on SharePoint servers. This API exposes just three methods (newPost, editPost, and getPost), but it still communicates over HTTP using XML structures. WSS 3.0 supports blogging out of the box with a Blog site template and Web management interface. Based on the MetaWebl­og API, you can use Word 2007 as a blog program, which provides a viable alternative to the browser interface.

Note that the MetaWeblog service does not use an .asmx file name extension like other SharePoint Web services. The MetaWeblog API was developed more than five years ago by Dave Winer and popular blogging tools expect to access this API through an .aspx page (you can read the specification at xmlrpc.com/metaWeblog­Api). By using .aspx instead of the usual .asmx naming convention, SharePoint remains compatible with these blogging tools.

PowerPoint 2007 and MOSS 2007

In a WSS 3.0 environment, PowerPoint 2007 does not use Web services, but communicates with SharePoint primarily through FrontPage/WSS RPCs and WebDAV when accessing presentation files in a document library. Yet if you have MOSS 2007, PowerPoint can take advantage of SharePoint slide libraries and the Slide Library Web service (SlideLibrary.asmx).

Slide libraries are a special type of document library. You can use them in conjunction with the Publish Slides feature of PowerPoint, which can break up a presentation deck into its individual slides and save these as separate files in a folder or library. When publishing slides in a slide library, Power­Point communicates with Webs.asmx and Slidelibrary.asmx to verify URLs and check for slide collisions. If a slide with the same name already exists in the slide library, PowerPoint prompts you to replace the existing slide or skip the current one.

Once slides are uploaded to a slide library, they can be reused in other presentation decks while the original slides remain in the slide library on the SharePoint server. SharePoint adds a timestamp to each slide and PowerPoint can check this timestamp as well as other metadata, such as the name of the PowerPoint presentation from which the slide originated, to detect updates through the Slide Library Web service. If updates exist, you can choose to replace the local slide in the current presentation or append the changed slide to your presentation. For an introduction, read the article "Use Slide Libraries to Share and Reuse PowerPoint 2007 Slide Content" at office.microsoft.com/en-us/powerpoint/HA012261671033.aspx. See also the companion worksheet named "Working with Slide Libraries."

Occasionally, you might notice an error message in PowerPoint 2007 when publishing slides, stating that "n of n slides that were being published to <library> failed. Try publishing again." This can be the result of mis­sing access permissions or WebDAV communication problems. As a workaround, you can publish the slides to a folder on the local file system and then copy the files manually into the slide library using Windows Explorer and the UNC path to the slide library, such as \\sharepoint\SlideLib instead of sharepoint/SlideLib. Windows Explorer uses the same WebDAV redirector as PowerPoint 2007, but it doesn't contact SlideLibrary.asmx to detect slide collisions. Instead it uses WebDAV to detect if a file already exists. Figure 5 shows the slide library architecture for PowerPoint 2007.

Figure 5 PowerPoint and SlideLibrary architecture (Click the image for a larger view)

InfoPath 2007 and InfoPath Forms Services

This column wouldn't be complete without mentioning InfoPath 2007 and InfoPath Forms Services. In fact, InfoPath 2007 is the biggest consumer of Web services among the Office applications discussed in this column. Of course, you can make extensive use of Web services as data sources in InfoPath forms, but even if you leave forms development aside and focus on publishing simple form templates that do not use external data sources, InfoPath 2007 communicates with SharePoint through Web services in addition to FrontPage/WSS RPCs and WebDAV, as illustrated in Figure 6. For an example that shows how to publish form templates in a SharePoint forms library, see the worksheet "Working with InfoPath Forms Services," available in the Code Download.

Figure 6 InfoPath and Forms Services architecture (Click the image for a larger view)

Important Web services that InfoPath 2007 relies on are Webs.asmx and Lists.asmx. Among other things, InfoPath retrieves information about the definition of site columns and content types from the Webs service, and uses this information to populate the site column list boxes in the Select a Field or Group dialog box so you can associate form columns with existing SharePoint site columns when publishing a form template. InfoPath creates any new columns without an association in the SharePoint site by using the Lists service. InfoPath also interacts with the Lists service to create the forms library and a content type definition for the form template. For the actual template upload, InfoPath uses FrontPage/WSS RPCs and WebDAV, simply placing the template with the file name template.xsn into the new library's Forms subfolder.

For browser-compatible form templates, the Publishing Wizard in InfoPath might offer the option to enable a form to be filled out using a browser, depending on whether you have MOSS 2007 or Microsoft Office Forms Server 2007 on the SharePoint server. InfoPath detects whether Forms Services is installed and enabled for the current SharePoint site by using the FormServerDetector.aspx Web page (such as sharepoint/_layouts/FormServerDetector.aspx?IsFormServerEn­abled=check), which returns <server IsFormServerEnabled = 'true' /> when Forms Services are present and enabled. If you then enable your form to be filled out using a browser, InfoPath 2007 calls the Forms Services Web service (FormsServices.asmx) during the publishing process to browser-enable the form template accordingly. For more information about browser-enabled InfoPath forms, see the Microsoft Office Forms Server 2007 General Reference available at msdn.microsoft.com/aa701145.

Conclusion

Web services are an important part of the SharePoint platform. They enable business solutions that can access local and remote resources, integrate distributed business applications with each other, and provide valuable features to Office applications. The most important Web services for Office apps are Webs.asmx and Lists.asmx, included in WSS 3.0, for working with SharePoint sites, lists, and libraries. Another Web service used occasionally is Views.asmx, which provides methods for working with list views. Most Office applications do not require a MOSS 2007 deployment, although some applications, such as PowerPoint 2007 and InfoPath 2007, can benefit from advanced features, such as Slide Libraries and Forms Services that are only available with MOSS 2007 or as separate products.

In the era of Software-plus-Services, Web services play an important role because they decouple hosted back-end environments from on-premise front-end applications. SharePoint is a key technology that drives online collaboration forward; Office applications and Office add-ins continue to run on local workstations, and Web services in combination with other Web technologies provide seamless access to features and data across corporate firewalls in distributed environments. It is no coincidence that SharePoint servers host 20, 40, 60, or more Web services. Web services are the right choice for business solutions that emphasize ease of use, simplicity of access, and flexibility while the object model is the right choice to build these Web services.

Pav Cherny is an IT expert and author specializing in Microsoft technologies for collaboration and unified communication. His publications include white papers, product manuals, and books with a focus on IT operations and system administration. Pav is President of Biblioso Corporation, a company that specializes in managed documentation and localization services.