Office 2003 and What s New for Developers

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.

 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