Office 2003 and What s New for Developers | Programming Microsoft Infopath: A Developers Guide

As we begin to develop applications using Visual Studio and SOA, we need to learn about the variety of components that Office 2003 provides. Many of these are covered in later chapters as part of complete solution examples. At this time, though, it is important to cover the basics. In later chapters, we will use these solutions that use InfoPath as the front end for data collection and aggregation.

Smart Documents

Office 2003 introduces a new technology that allows Office documents to become more than a static repository of user data. Smart Documents take automation to a new level and can automatically enter data into appropriate Word or Excel fields, access external information, and even combine documents. One of the most important features of these types of documents is the contextual help that is available to guide users through the preparation of complicated documents. The underlying technology of Smart Documents is an XML structure that is programmed to what steps a user needs to complete and that provides help along the way. As a user moves through a Smart Document, the insertion point determines what is displayed in the task pane. Developers can provide everything from context-sensitive help to external data calculations for a specific section of documents. Smart Documents are the perfect method to pull and aggregate data into a Word or Excel document.

Developing a Smart Document

Take the following steps to develop a Smart Document:

Attach an XML schema to Word 2003 or Excel 2003 and annotate the portions of the document that will have Smart Document actions or help topics associated with the XML.
Save the document as a template so that others can create an instance of the template from the New Document task pane.
Using Visual Studio, implement the ISSmartDocument interface or an XML schema that conforms to the Smart Document XML schema. This is needed to display the contents in the Document Actions task pane and to handle the specific defined actions.
Develop an XML-based solutions manifest that references the files used within the Smart Document. You should then save the manifest in a location referred to by the Smart Document custom document properties.
Place the solution s file in the locations referred to in the solutions manifest.

Users who want to instantiate and use a Smart Document should open the Word or Excel template and start interacting with the document. The use of templates within Smart Documents allows for a no-touch deployment. Smart Documents also offer enhanced security restrictions that allow them to become a trusted solution.

Smart Document Security Restrictions

The enhanced security restrictions offered by Smart Documents are:

Management by security policy.
Solution manifests must come from trusted sites. The solution manifest themselves must be code signed or otherwise trusted.
Code that runs as part of the Smart Document solution is subject to the user s Office security settings.
Users are prompted whether to initiate an install of a Smart Document solution.

Smart Tags Version 2

Smart Tags were first introduced as part of Office XP and have been substantially enhanced within Office 2003. This includes the addition of Smart Tags to PowerPoint 2003 and Access 2003 as well as additional enhancements for Word 2003, Excel 2003, and Outlook 2003 when you re reading HTML e-mail and writing e-mail with Word as your default e-mail editor. Also, there are a variety of enhancements to the Smart Tag recognizer and the Microsoft Office Smart Tag List (MOSTL) that provide support for regular expressions and context-free grammar based recognition, as well as advanced support for property settings on items in a list of terms.

Smart Tag functionality has also been improved to include the capability to execute actions immediately on recognition without requiring any user intervention. For example, a Smart Tag could recognize a product name and automatically start an action that opens the browser or links to a related page. Also added was the ability to modify the current document, which allows developers to automatically format a recognized term . For example, a product ID could automatically be turned into a product name. Additionally, this allows developers to add required content like a product description or to update a reference to a product catalog. Smart Tags provide a great way to connect to a variety of different data sources that exist within an organization and serve up contextually valid or recognized terms. For example, within an Excel spreadsheet you can connect a list of general ledger accounts to specific types of assets.

Windows Sharepoint Services and Sharepoint Portal Server

Windows Sharepoint Services (WSS) is a collaboration platform that is a core component of Windows Server 2003. WSS is an ASP.NET-based page and platform container for componentized user interfaces called Web Parts . WSS provides an out-of-the-box solution for team-based collaboration that includes a portal interface and document management system.

The portal interface is built on ASP.NET and offers personalization, state management, and load balancing. WSS offers self-service capabilities that allow users to create, maintain, and customize their own portal pages. Document management allows you to manage and maintain the revision history of not only traditional documents types like Word, Excel, and PowerPoint, but also InfoPath forms through a special forms library function, as shown in Figure 1.11. This provides the ideal repository and versioning mechanism for the distribution and location of enterprise forms.

Figure 1.11: The Create Page allows document and form libraries.

WSS provides sites for team-based collaboration and increased producitivty through the creation of a team portal site. Sharepoint Portal Server (SPS) 2003 connects site, people, and information together for the enterprise. Built on top of WSS and the .NET Framework, SPS inherited all the features of WSS and provides the core features of portal sites for people and documents within an enterprise.

Sites created within SPS are specific to the SPS framework, but they utilize the base WSS technologies of Web parts and document libraries. The direct integration between the two helps to lower the amount of code associated with the development, training, and support of an enterprise portal site.

SPS extends the capabilities of WSS by providing a site registry and search mechanism. The site registry is a centralized repository of Web site and portal pages. It provides an easy-to-use Web site locator. The search within SPS is an inteligent crawling service that can index and search Web sites, public folders, file shares, documents, and XML files.

Visual Studio Tools for Office

The new Visual Studio Tools for Office (VSTO) enables developers to build managed application solutions on Word 2003 and Excel 2003. Using Visual Studio 2003 to write managed code that executes behind documents and spreadsheets allows Word and Excel to take advantage of the .NET Framework features of no-touch deployment, Web Service Integration, and security. When a user opens a Word 2003 or Excel 2003 file associated with a custom solution, the application will query the server and download the new DLL(s) to the user s machine. Developers won t need to touch every desktop, and users won t have to download files.

Note

No-touch deployment provides a mechanism to hook Internet Explorer 5.01 and above to listen for .NET assemblies that are requested by an application. During the request, the executable is downloaded to an area on the local hard drive called the assembly download cache. The application is then launched by a process named IEExec into a constrained security environment.

CD-ROM The sample solution provided on the CD-ROM (\Code\Chapter 1\ReportingService\Setup.exe) demonstrates an easy sample method for publishing analytical data from a Web Service into a Word or Excel document where it can be further analyzed . As an example, let s create a reporting Web Service that pulls sales data from the local Northwinds database. The code in Listing 1.1 is provided on the CD-ROM.

Listing 1.1: Creating a Web Service-based dataset that returns SQL Server data.

 <WebMethod()> Public Function GetUpdatedTotals() As DataSet Dim sqlConn As SqlConnection Dim sqlCmd As SqlCommand Dim strConstring As String Dim intUserID As Integer strConstring = ConfigurationSettings.AppSettings("constring") sqlConn = New SqlConnection(strConstring) sqlConn.Open() sqlCmd = New SqlCommand   With sqlCmd     .Connection = sqlConn     .CommandTimeout = 30     .CommandType = CommandType.Text     Dim sqlInfo As String     sqlInfo =  "SELECT Employees.Country, Employees.LastName,                  Orders.ShippedDate, Orders.OrderID, " & """" & "Order                 Subtotals" & """" & ".Subtotal AS SaleAmount "     sqlInfo =  sqlInfo & "FROM Employees INNER JOIN (Orders INNER JOIN" &                  """" & "Order Subtotals" & """" & " ON Orders.OrderID = "                  & """" & "Order Subtotals" & """" & ".OrderID) "     sqlInfo = sqlInfo & "ON Employees.EmployeeID = Orders.EmployeeID"     CommandText = SqlInfo   End With   Dim DataDA As SqlDataAdapter = New SqlDataAdapter   DataDA.SelectCommand = sqlCmd   Dim DataDS As DataSet = New DataSet   DataDA.Fill(DataDS, "SalesData")   Return DataDS   sqlConn.Close() End Function

Once the Web Service is published, you are ready to create an Excel application that consumes this service. Once installed, VSTO provides a new type of project in Visual Studio.NET 2003, as shown in Figure 1.12.

Figure 1.12: The VSTO project as it appears in Visual Studio.NET 2003.

Using Visual Studio.NET, you create a new Office 2003 project. This creates an Excel-based project that provides managed code behind pages. For our code, we want to create an application that when opened would instantiate and call the reporting Web Service and then create a Pivot Table that the user could analyze and drill into the received data. Do this by entering the code in Listing 1.2 into the ThisWorkbook Open handle. This code is activated on the open of the spreadsheet and will publish the data within an Excel Pivot Table.

Listing 1.2: Generating the Pivot Table from the dataset returned by a Web Service.

 Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open Dim ReportingInfo As New ReportingServices.ReportingService Dim ds As New DataSet Dim XMLFile As String XMLFile = ("C:\employee.xml") ' call the web service and write the XML file to local disk ds = ReportingInfo.GetUpdatedTotals() ds.WriteXml(XMLFile) ' load the XML file into Excel ThisApplication.Workbooks.OpenXML("C:\employee.xml") ' set the pivot table up         ThisApplication.ActiveWorkbook.PivotCaches.Add(SourceType:=    Excel.XlPivotTableSourceType.xlDatabase,  SourceData:=    "employee!R2C1:R832C7").CreatePivotTable(TableDestination:="",  TableName:= "PivotTable1",   DefaultVersion:= Microsoft.Office.Interop.Excel._ XlPivotTableVersionList.xlPivotTableVersion10)              ThisApplication.ActiveSheet.PivotTableWizard( TableDestination:=ThisApplication.ActiveSheet.Cells(3, 1)) ThisWorkbook.ActiveSheet.Cells(3, 1).Select() With ThisWorkbook.ActiveSheet.PivotTables("PivotTable1")._     PivotFields("/SalesData/Country")     .Orientation = Microsoft.Office.Interop.Excel._     XlPivotFieldOrientation.xlPageField     .Position = 1 End With ' clean up the XML file Kill(xmlfile)     End Sub

At this point, we are ready to distribute the application to end users, who can open the spreadsheet and further analyze their sales numbers without having to be concerned with actually gathering or publishing the data.