Chapter 1: An Introduction to Excel Services (Programming Excel Services)

This article is an excerpt from Programming Excel Services by Alvin J. Bruney, from MSPress (ISBN 9780735624078, copyright MSPress 2007, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews. (30 printed pages)

Contents

• An Introduction to Excel Services

• Excel Services Architecture Overview

• Excel Services and Office SharePoint Server 2007

• About Office Programming

• Business Intelligence Systems

• Installation and Deployment

• Excel Services Permissions

• Finding the Web Application Root

• Summary

• Additional Resources

Download the code sample that accompanies the book.

An Introduction to Excel Services

Every now and then, a product hits the market that causes a significant shift in the way software is designed and built. Excel Services in Microsoft Office SharePoint Server (MOSS) 2007 is one such product; it completely changes the way Microsoft Office system applications are written. This chapter will focus on introducing and explaining the Excel Services architecture that drives this fundamental shift in thinking so that you can gain an appreciation for the product. The focus is less on code and more on painting Excel Services in broad strokes, thereby allowing you to grasp the grand design. Later on, we intend to probe the basic building blocks that drive this new technology. We will then work our way up to some of the more advanced concepts of building and integrating sometimes disparate applications on top of the common infrastructure backbone exposed by Microsoft Office SharePoint Server 2007.

Excel Services is a new technology that allows developers to load, calculate, and display Microsoft Office Excel workbooks by using Office SharePoint Server 2007. In some respects, Excel Services behaves much like a development framework that allows developers to build applications based on Excel workbooks. The concept of providing a framework for developers is certainly not new—think the Microsoft .NET Framework. However, the way in which this technology is exposed is fundamentally different from legacy approaches. This radically different architecture enables new applications that target the Microsoft Office system and Office SharePoint Server to be efficient, robust, and scalable. Legacy Office applications can scarcely make that claim ring true.

Excel Services is built on ASP.NET 2.0 and Office SharePoint Server 2007. The architectural decision to use Excel Services on the .NET Framework and Office SharePoint Server is significant because it means that Excel Services benefits from the flexibility and performance capabilities of these highly regarded platforms. Consider that SharePoint technology is mature and provides, among other things, collaboration, content management, and business intelligence capabilities while ASP.NET provides a framework for building Web application software. Excel Services, a part of Office SharePoint Server, provides for some exciting ways to build and deploy enterprise software with internal support for key SharePoint functionality and application extensibility.

However, it's extremely important for .NET developers to approach Excel Services from a SharePoint perspective. It is a mind shift that ultimately improves the quality and effort of application development for Office SharePoint Server 2007. Office SharePoint Server is a product designed to be assembled out-of-the-box and configured appropriately. This is markedly different from an ASP.NET mindset where products must be built from the ground up using blocks of code. Following this new mindset, most of your development efforts will focus on extending Office SharePoint Server. Much of this book is designed along these lines—that is, the material forces you to think from a SharePoint perspective. An ASP.NET developer must resist the inherent urge to use the .NET Framework to build stand-alone application functionality for Office SharePoint Server because that urge is unhealthy. Rather, an ASP.NET developer should seek to build functionality that supplements what is already offered by Office SharePoint Server.

One of the fundamentals that we intend to focus on is the bridge between Office SharePoint Server and Office system applications. In that regard, it's important to point out that Excel Services targets Excel workbooks only. The depth and breadth of application products and features are not directly available to Excel Services. For instance, Excel Services contains no internal support for Microsoft Office Outlook automation. That is certainly no setback, as you will soon learn that the Excel workbook itself plays a pivotal role in exposing Office system functionality because it is able to dovetail with so many other Office system applications.

For instance, an Excel workbook can invoke search functionality, create charts, display Microsoft PivotTable dynamic views, read XML files, query data stores, provide Web service functionality, and do so much more. If Excel Services functions as a bridge between the Excel workbook and Office SharePoint Server, it must mean that traveling this bridge indirectly exposes Office system functionality to Web applications that are hosted on Office SharePoint Server. And this is the angle that will be explored going forward—that is, learning to build real-world software that is integrated with Office SharePoint Server. Chapter 7, "Advanced Concepts with Excel Services," shows some exciting integration ideas by example.

Along the way, you'll gain an appreciation for the tools and strategies employed by Excel Services to retrieve data and manipulate workbooks. Excel Services allows you to connect to data across different back ends while maintaining the connection information in a secure way—see Chapter 4, "Excel Calculation Service." You'll learn how to protect intellectual property using features specifically built in to the workbooks—see Chapter 7. You'll define the boundaries between Excel Services and its full-blown counterpart, Microsoft Office Excel 2007—see Chapter 2, "Excel Web Services." But most importantly, you will learn to think in a new way using new tools to tackle old problems.

Excel Services Architecture Overview

Figure 1-1 introduces the Excel Services architecture at a high level.

Figure 1-1. Excel Services architecture big picture overview

Office SharePoint Server 2007 contains several key components shown by the leftmost vertical section of Figure 1-1. Our focus is the Excel Services layer within the Business Intelligence component. The Excel Services placement within the hierarchy is significant because it is designed to address the Business Intelligence need at an enterprise level. This book considers the other pieces, such as collaboration and the document management items in the lower leftmost vertical section of the figure, only within the context of Excel Services.

The architecture solves a particular subset of problems associated with Business Intelligence. These problems span the spectrum of issues across the Office system platform to include browser-affined user-interface applications, lack of workbook security, version control issues, poor scalability, and terrible enterprise-level performance. Even with an abundance of new software tools, more fundamental headaches such as harvesting data from disparate back ends, analysis of dissimilar pieces of information, and poor control over out-of-sync data threaten to counter the advances of data analysis. Excel Services was designed to provide a solution for the broad spectrum of technical and business issues that conspire to derail the field of Business Intelligence.

The new architecture provides more than a face-lift for the long list of eyesores that stifle the forward movement of Business Intelligence today. Excel Services provides solutions at the design level—that is, you can now design applications that allow for both horizontal and vertical scaling. The new architecture based on the ASP.NET 2.0 platform incorporates codified guidance through industry-proven design patterns that provide a solid foundation for building tomorrow's software solutions today. As an added bonus, Excel Services allows developers to participate in the rich Office SharePoint Server end user experience. For instance, custom code can hook into the new feature of Office SharePoint Server that allows clients to connect any list or document library to Outlook. The integration with Outlook extends to offline scenarios so that clients can remain close to their data even though they aren't tethered to a modem.

In Figure 1-1, observe that the Excel Services components isolate programmatic access to the Excel model into logical layers. For instance, the front end is exposed through Excel Web Access and the middle tier is exposed through Excel Web Services. These layers can then be run on a single computer or across computers in a farm in true tier fashion. This is how the model achieves horizontal scale. For vertical scale, the Excel Services architecture supports multiple CPU configurations and memory resource additions up to the physical limit of the server. In fact, your applications built on the Excel Services platform will provide near linear performance for each processor!

If you pause for a moment to reflect on the architecture shown in Figure 1-1, you should realize that this new technology opens up all sorts of programming possibilities. For instance, the entire range of Office system products can now be addressed programmatically through the Excel Services bridge or independently through SOAP clients. And what's more interesting is the fact that all three pieces that make up the Excel Services architecture combine to expose a single point of entry into the Excel calculation engine. The calculation engine will be covered in great detail in Chapter 4.

If you're wondering how this dovetails with Office SharePoint Server, consider Figure 1-2, which shows the Excel Services architecture.

Figure 1-2. Excel Services integration overview

Observe that Windows SharePoint Services is a key player in the architecture. You'll notice that Windows SharePoint Services 3.0 is a separate service. Windows SharePoint Services 3.0 is installed independently from Office SharePoint Server as an operating system service. The separation simply secures a measure of robustness while guaranteeing scalability. Chapter 5, "Windows SharePoint Services 3.0," will explore Windows SharePoint Services in a lot more detail.

Windows SharePoint Services

From a business perspective, Windows SharePoint Services was designed to offer enterprise-level collaboration services. Windows SharePoint Services allows companies to manage and manipulate documents across geographical boundaries and through organizational hierarchical levels. The collaboration can be exposed through thick or thin clients residing locally or across the sea in a way that is user-friendly and focused on the knowledge worker. We won't just talk about these niceties; we'll build them too.

The collaboration is built on a platform that is configurable, easily deployed, and responsive to change. Windows SharePoint Services is really a next-generation point-of-convergence layer that caters to real-world demands of software. Whether it is a request from a thick client connected to a platform other than Windows or a request through a Web Part located on a dashboard running on Windows, the Windows SharePoint Services backbone can flex to service these disparate requests in an efficient manner across the enterprise. That's really what real-world software is about, and Windows SharePoint Services is built to handle it gracefully. Chapter 2 will walk you through the process of building applications with this exciting breed of software.

In the next section, we'll start a controlled dive into the bowels of the architecture. Before you can start writing code that targets the Excel Services platform, you need to understand each piece and how the pieces interface with each other. You also need to get a good grasp of what can be done and what should be revisited with a different approach to squeeze performance and functionality out of this new architecture. Then we will write code, lots of it.

Excel Web Access

The Excel Web Access (EWA) component is the most visible part of Excel Services. EWA renders HTML to the client that is cross-browser compliant. Spreadsheets, PivotTables, and charts render with the same visual fidelity in all browsers. This is fundamentally different from Web applications that are built to target the Office platform. In the past, Web clients built to target the Office platform used an eclectic mix of ActiveX objects and custom objects to render spreadsheets and PivotTables.

Although that approach solved the problem at hand, it was jaundiced and short-sighted because it failed to provide an enterprise-wide solution that allowed disparate clients to see and interact with spreadsheets. One common issue with this approach is that these products added cost and often complicated build and deployment scenarios. Another annoying side-effect is that the use of ActiveX controls imposed browser limitations on the Web clients. Consequently, applications that were based on this technology could run only in Microsoft Internet Explorer browsers because only these browsers had internal support for ActiveX controls.

The new approach is built on a policy of inclusion. Because all up-level browsers support HTML, DHMTL, and ECMAScript (JScript, JavaScript), it makes sense for EWA to construct its output in a way that is consumable by all browsers. That way, the developer is not constrained in the type of application that can be built; neither is she limited by the extent of functionality that can be incorporated into such applications. EWA applications can now target the widest possible audience. Chapter 3: Excel Web Access (Programming Excel Services) will walk you through the process of building and deploying solutions for EWA.

For the most part, the look and feel of the PivotTable or spreadsheet in EWA mirrors the fit and finish of the desktop version. However, some significant inconsistencies in the front-end functionality will take some getting used to. One such inconsistency is the way in which end-user data is input into an EWA spreadsheet. The spreadsheet cells are read-only. Data cannot be entered directly into these cells. Instead, data must be entered into controls that are tied to the target cells. Figure 1-3 shows an EWA spreadsheet.

Figure 1-3. Excel Web Access spreadsheet

To be polite, this approach is rather awkward and certainly looks and feels unnatural when compared to the desktop equivalent. More importantly, time and effort need to be invested in training the user to interact with this new input functionality. Chapter 6, "Advanced Web Parts Programming," will show you a clever way to work around that limitation.

Another point of departure is that EWA is tightly coupled to Web Parts. ASP.NET Web Parts are controls that provide pockets of functionality on Web pages. These types of functionality are built in by the developer and exposed through well-defined points in the Web Part control. Several code examples to follow in Chapter 3 and Chapter 6 will be geared at improving your level of comfort with Web Parts.

Web Parts also allow application developers to encapsulate functionality behind a user interface that can then be used to improve the user experience and drive functionality on a SharePoint site. With Excel Services, you now have the option of developing an application that allows the end user to selectively plug in pieces of functionality on the fly through the use of custom Web Parts.

When developing Excel Services applications, you need to start thinking in terms of providing end-user functionality encapsulated in Web Part controls. EWA does not allow spreadsheets, PivotTable controls, or Excel charts to lie bare on a page. It's a bit of a mental side step from ASP.NET development, but there are some benefits to be had through this approach. For one, these Web Parts can be plugged into Microsoft Office SharePoint Designer 2007 pages to provide the same functionality. Another benefit is that Web Parts allow end users to impose personalization themes at the control level so that the user interface can be customized for the end user. This functionality is especially important when designing and building accessible Web pages and applications. Chapter 3 focuses on developing customizable Web Parts. Parts of Chapter 7 will cover accessibility in greater detail.

Excel Web Services

Excel Web Services (EWS) is a bona fide Web service hosted on Office SharePoint Server 2007. EWS acts to decouple the calculation process from the front end. With this separation of responsibilities, you can now build applications that span operating system boundaries. EWS exposes the feature set of the calculation service and workbook to clients. Clients can focus on the task of displaying the results of the calculation.

EWS can service requests in a scalable manner across the enterprise Web farm in a thread-safe way. The implication here is that the Excel Services infrastructure automatically handles concurrency issues, so there is reduced risk of calculation anomalies. Chapter 4 focuses on the parts of Office SharePoint Server that allow calculations to scale across a Web farm.

You can access EWS either as a regular Web service by calling Web methods through SOAP clients as you normally would any other Web service, or you can access the functionality through a reference to a local assembly by using a technique called Direct-Linking. Direct-Linking is typically used for applications that need to run within real-time constraints such as stock trading applications while Web services are used for every other scenario. See the Project Setup Guide for a walk-through on setting a reference locally or adding a Web reference to EWS. We reserve a more detailed explanation that explores the technical merit behind these statements for Chapter 2.

Excel Calculation Service Engine

The rubber meets the road in the Excel Calculation Service (ECS) layer. As the name implies, the calculation engine is responsible for servicing calculation requests. Each calculation request is atomic—that is, once a request is received, it is processed as a single transaction. The transaction is pinned to a particular session so that calculation accuracy is guaranteed. The design accommodates distributed computation on high-performance clusters with redundancy software. The architecture has been tuned to provide a near-linear performance increase with each new server added to the farm. The performance increase occurs without penalty to the computational integrity of the calculation engine. In addition, the calculation engine achieves computational fidelity with the Excel desktop version so that you can rest assured that your calculated results, distributed or not, will be Excel accurate.

Aside from the regular duties of loading and calculating workbooks, the ECS also maintains and manages the session state for opened workbooks. Session state operates much like ASP.NET sessions with configurable time-outs and associated events. Chapter 4 will explore the technical parts of this approach in further detail.

The ECS also manages the workbook cache. Opened Excel workbooks are cached according to user sessions. For instance, multiple users sharing the same account credentials are pooled and a single instance of a workbook is used to service these requests. Calculation states, results, and external data also form part of that cached pool managed by the ECS. This behavior is specifically designed to service high concurrency across a farm with the result that performance is improved for the set of pooled users. Chapter 4 will dive deeper into the details of the ECS.

Performance and Scalability Considerations

Excel Services was designed to scale vertically and horizontally. For vertical scaling such as CPU additions, Excel Services automatically adjusts for multiple CPUs. Excel Services includes native support for 64-bit CPU architectures as well. For horizontal scaling, Web application servers can simply be added to the farm and configured using the Central Administration Web application. Each new server addition requires Internet Information Services (IIS).

Functional integrity and calculation fidelity is achieved across an expanding farm due in large part to the Excel load balancer. The load balancer contains a managed library that serves as a proxy component. The ECS proxy automatically handles communication between the front-end Web server and the application service tier on the server farm. The proxy component cannot be programmed through code.

Figure 1-4 shows the administrative console used to configure the load balancer. There are many options that are configurable. However, each option is static—that is, it does not dynamically adjust to load during execution. Administrators have to dial in the settings during use. Figure 1-4 shows the various settings available to the administrator.

Office SharePoint Server depends on a Microsoft SQL Server database for storage. You will learn how to configure the important parts of the database later on. For now, it's important to realize that no physical upper limits are built into the back-end store. Office SharePoint Server can service databases ranging in size from megabytes (MBs) on up to terabytes (TBs) and any point in between. The exception to this case is the 4-MB cap placed on the free version of Microsoft SQL Server Express Edition.

Figure 1-4. Load balancer settings

Excel Services and Office SharePoint Server 2007

Office SharePoint Server 2007 didn't magically appear on the scene in 2006—it does, in fact, have a long, rich history. It all started with a product called Site Server, released around 1997, that contained Web content management, personalization, document management, and search services. Site Server morphed into Commerce Server, which was, in turn, replaced with a new product called Digital Dashboard Starter Kit. Digital Dashboard introduced the concept of Web Parts as pluggable bits of functionality that could be added to a Web page.

Digital Dashboard churned through a few revisions and improved its Web portal offering along the way. The feature set of Digital Dashboard merged with a new product called "Tahoe" and was renamed to "SharePoint Portal Server 2001." Web Part success introduced in the Digital Dashboard continued its evolution into new ASP.NET territory, providing two development code streams for these controls.

Around this time, a product called SharePoint Team Services from Microsoft that provided Web-based team collaboration was released. SharePoint Team Services would morph into Windows SharePoint Services available with Microsoft Windows Server 2003. SharePoint Server version 2 was built on top of Windows SharePoint Services, and these technologies converged to give birth to Office SharePoint Server 2007 today.

Through the 10-year evolutionary process, best-of-breed software merged to provide a suite of services, including the following:

• Page and content customization

• Document management and storage

• Notification services

• Search and indexing

This evolutionary path allowed Office SharePoint Server to grow and mature into a tool that facilitates collaboration within an organization. This collaboration includes creation, management, and storage of information. Coupled with these exciting features, Excel Services adds a new dimension to collaboration to include Excel workbook integration services. Indeed, much of the functionality available to administrators can be built by using the development model exposed by Windows SharePoint Services. Chapter 5 will focus on these and other facets of the product.

Before you begin exploring Excel Services programmatically, you need to understand the tight coupling between Excel Services and Office SharePoint Server. Excel Services requires Office SharePoint Server 2007. However, the Excel Services component is packaged only in the Office SharePoint Server 2007 Enterprise edition. Office SharePoint Server 2007 Standard edition functionality includes only the following features: Collaboration, Enterprise Content Management, Workflow, My Sites, Profiles and Personalization, and Enterprise Search. Enterprise edition functionality includes the following: Business Data Catalog, Excel Services, Report Center, Microsoft Office InfoPath Forms Services, Key Performance Indicator (KPI), Filter Web Parts, and Windows Workflow Foundation.

You should note that although the two packages are cleanly separated, all components are installed on the target computer. The installation key provided with the product simply unlocks a specified package. The key encryption is of sufficient strength to discourage sophisticated hackers from gaining access to unauthorized packages.

If you intend to publish spreadsheets, you must install Office Enterprise 2007 or Office Professional Plus 2007. All other versions do not include the ability to publish spreadsheets. In all other versions, a published spreadsheet exists in noninteractive mode—that is, the end user cannot input or modify data inside the spreadsheet. Chapter 6 shows a clever approach that allows you to program for interactivity if you do not have the Enterprise or Professional Plus version of the Office system installed. If your programming requirements do not include interactive content, Excel Services may be of little benefit.

Another customer-centric feature is that you can try Office SharePoint Server 2007 and Excel Services before making a commitment to purchase. The trial period lasts six months, after which certain parts of the software become unusable. The trial version offers the same functionality as the actual product, so you get a real sense of whether or not the software can add value to your enterprise organization. If you decide to keep the product, you purchase it through regular purchasing channels and you receive a key that releases the lock. Software manufacturers that are confident of the capabilities of the software they produce usually offer a trial period, so there's really nothing to lose on your end.

Office SharePoint Server 2007 Licensing and Terms of Use

An important part of software evaluation deals with licensing configurations and terms of use. Although the product might fulfill every desire, it could be out of reach from a financial perspective for shops that run on a tight budget. It's also important to realize that Excel Services is bundled with Office SharePoint Server 2007. The two are indivisible; you must purchase Office SharePoint Server 2007 Enterprise edition to harvest Excel Services functionality.

Another consideration is the fact that licensing extends to the operating system. In that case, Windows Server 2003 has specific licensing regarding the number of concurrent users that can be supported for applications that run on the server.

Another issue to be aware of is that default licensing caters to installations that service intranet applications. For external-facing sites that service unauthenticated users, you must purchase an External Connector license for each Windows Server 2003 that is exposed externally.

Client Access Licenses (CAL) must be purchased for each user on the system. The CALs are for site access. Whether clients require view-only access or not, each client requires a separate CAL. For enterprise-wide consumption, you need an accurate estimate of end users who will be accessing the site. You can do the simple math to see how much the product will cost you based on these estimates.

Office SharePoint Server 2007 is tightly integrated with either Microsoft SQL Server 2000 Service Pack 3 (SP3), Microsoft SQL Server 2005, or Microsoft SQL Server Embedded Edition. Without exception, all these databases contain end user licensing agreements, some more restrictive than others. It's well worth your while to invest some time and energy in understanding the licensing terms for each of these pieces before making a commitment to Excel Services. You might find that you need to purchase extra products to be able to use Excel Services. The problem is exacerbated by the fact that Excel Services does not allow installers to mix and match packages. For instance, a client can't simply purchase the standard version along with Excel Services because interactivity is not available with that particular combination of products.

The embedded database server that is installed with the standard version is functionally equivalent to full-blown SQL Server where Office SharePoint Server is concerned. However, there are certain limitations that spoil the fun. Here's a list of limitations in no particular order:

• There is a 4-GB data limit on the database capacity.

• Business Intelligence features are not installed.

• Integration and interoperability functionality are reduced.

• Notification Services are not available.

• There is a reduced manageability feature set.

• There are no tools for scalability and performance tuning.

• The enterprise availability feature set is not available.

If these limitations are unwelcome, then consider upgrading your database.

About Office Programming

Ever since Office applications gained popularity, developers have tried to leverage the Office platform in custom-built applications. Excel spreadsheets are used heavily in the real world. And although these varied attempts to leverage the Microsoft Office platform seem fractious in nature, they all target the same underlying Office COM platform. The sobering truth is that there might never be a single point of access to Office system programmability because each new release is inextricably tied to a particular Office system product or Client Access License. For instance, Microsoft Office Web Components is tied to licensing, Visual Studio Tools for Office 2003 is tied to Microsoft Office XP, and Excel Services is firmly moored to Office SharePoint Server 2007. It's important to focus on who the key players are so that you can make educated choices about developing applications targeting the Microsoft Office system.

You might have noticed that the text takes some artistic liberties where Excel Services and Office system programmability are concerned. Strictly speaking, it isn't technically correct to claim that Excel Services targets the Office system. And it is really important to make this clear. Excel Services only allows calling code to manipulate Excel workbooks.

Because Excel workbooks are an integral part of the Office system equation and are fairly heavily used, it's safe to assume that Excel Services provides hooks into the Office system through the Excel Services gateway. As such, the literature in this book will continue to exercise this freedom of association. Also, Chapter 7 will provide examples of Excel Services application code that automates Office system components.

Alternatives to Excel Services

Part of the buzz around Excel Services is that there really is nothing like it at the enterprise level for targeting Excel workbooks. The products listed in the next few sections provide a measure of functionality that allows developers to target spreadsheets. But you should understand that these products do not provide the flex and scale of Excel Services—meaning that no single product currently available provides the same depth of functionality as Excel Services.

On the other hand, if you are looking at targeting specific feature sets, such as Excel charts or PivotTables, you'll find ample products that provide that type of functionality. Some third-party products even provide better features and functionality. For instance, charting packages are now sophisticated and specialized, and they typically provide more in terms of raw features than the Excel chart packages bundled within Excel. However, none provide the architectural backbone suitable for developing enterprise applications across the Office system. Bear this in mind while you evaluate and compare Excel Services to the current "competitive" offering.

Office Web Components

Office Web Components (OWC) was released as an add-in to Microsoft Office 2000. From that point, the Web application developer world has developed a sort of cult following to this set of components. Even today, consultants continue to recommend its use. Companies continue to implement applications based on OWC despite the fact that Microsoft has discontinued development of these controls. Chapter 6 will walk you through a few examples of integrating these products into Web Parts to overcome some inherent limitations in the Excel Services model. For more information, see this blog entry about the Office Web Components "Roadmap".

To understand why OWC has been such a success, you first have to understand the business need that fueled its creation. Web applications had long lacked the functionality of Excel. OWC provided a way to marry the desktop functionality of Excel to the Web browser experience. OWC opened a sea of possibilities for Web applications. And, to add icing to the cake, OWC could perform double duty by running in Windows applications on the desktop.

However, this flexibility came with a high price. Security was always an issue. OWC suffered some notable security compromises and exploits in part because of the ActiveX platform on which it ran. Microsoft was unable to find an elegant enterprise solution that would close the gaping security hole while justifying continued investment in this technology. It was painfully apparent that if the security issue had to be solved, OWC had to be discontinued in favor of something more secure.

EWA is not a replacement for OWC, but it does provide an elegant enterprise solution to the business need of providing end users with desktop Excel functionality hosted in a browser. As an added bonus, the thorny issue of an Internet Explorer–affined OWC component is a thing of the past; EWA renders completely in non–Internet Explorer browsers with the same visual fidelity found in Excel. The definitive guide to developing with OWC, The Microsoft Office Web Components Black Book with .NET (Lulu.com, 2005), is available on Amazon.com.

Excel COM Interop Libraries

Customers have always wanted to use Excel as a server technology. Computing a spreadsheet, calculating a range, or saving changes in a concurrent environment are persistent customer requests. That type of need existed even when Excel was in its infancy. It continues to resonate louder today as companies migrate applications to the Web to dodge the heavy burden of enterprise desktop deployment.

However, the thorn, if there ever was one, is that Excel was designed as a client-side technology. It was never meant to service server-side applications. Forcing a client-side technology to perform server-side duties through clever hacks and shady programming techniques is a recipe for disaster because it overextends the architecture. And there have been some catastrophic disasters that are best left hidden under the software carpet.

Fortunately, Excel Services now provides a calculation engine that is designed to live on the server with Web Service receptacles that reach deep into the client front end, enabling thin, thick, and smart clients to hook into the back end in a scalable fashion.

Visual Studio Tools for Office

Microsoft Visual Studio Tools for Office is both an add-on to Visual Studio and a stand-alone product. As of this writing, the offering is currently in its second revision with a third on its way. The tool suite was designed to allow .NET developers to target Office system applications with managed code. Visual Studio Tools for Office services Windows applications. It was not designed for the Web. However, there is a server component in Visual Studio Tools for Office that allows documents to be manipulated on the server. Visual Studio Tools for Office contains several limitations. The most noticeable is that it targets specific versions of Microsoft Office.

Note that Visual Studio Tools for Office was not designed from the ground up. Rather, it is built on top of the Office COM libraries and installs as a template that makes Visual Studio Tools for Office functionality available to Windows applications. Consequently, scalability and performance take a back seat to functionality. For instance, Visual Studio Tools for Office allows Windows controls to be embedded in the spreadsheet.

There are many more advantages to Visual Studio Tools for Office that are worth considering; however, the product is designed only for the desktop. Excel Services provides much of this functionality with the added benefit that performance and scalability take the front seat. And Excel Services functionality isn't limited to desktop applications. A suitable resource for Visual Studio Tools for Office development is my recent book, Professional VSTO 2005: Visual Studio 2005 Tools for Office (Wrox, 2006).

Third-Party Components

It should be obvious by now that these ad hoc technologies aimed at providing Office system functionality have created a sort of vacuum. It is difficult to choose a suitable product if you are developing a smart client application for the Internet, for instance. For that particular scenario, Visual Studio Tools for Office might not be a suitable choice because it is tied to a particular Office version. Another burden is that each Visual Studio Tools for Office installation requires a specific version of the .NET Framework.

OWC is a suitable choice. However, the end user licensing agreement dictates that these components be used in an intranet environment. To be clear, the exact rules that govern OWC terms of use are sufficiently complex to merit their own book. But the rules can be distilled into a policy that does not include Internet deployment for interactive use.

To address these and other types of needs, third-party controls have been developed to allow developers to target the Office system model. These controls are too numerous to mention; however, they all share the same concerns. The industry-proven products are not free, contain their own licensing agreements, and offer varying levels of support.

Business Intelligence Systems

In the last decade or so, Business Intelligence has evolved to mean much more than data mining. The term encompasses everything from data harvesting across disparate back ends to model-driven analysis of data. Knowledge workers typically employ a variety of tools to make sense of this mass of data. It should come as no surprise that Excel spreadsheets drive the majority of this business in the real world. Consequently, Microsoft has provided leadership in this domain by empowering Office SharePoint Server 2007 with a complete suite of Business Intelligence tools that put knowledge workers in the driver's seat.

Office SharePoint Server 2007 bridges the data gap by allowing knowledge workers to connect to disparate back ends, such as SQL Server, SAP, Siebel, spreadsheets, and Web services. The mass of data is untangled and normalized inside a new potent tool called a Report Center site. Knowledge workers can interrogate the data through Key Performance Indicators (KPI) analysis.

A Report Center site in Office SharePoint Server 2007 makes use of Excel Services to display Web Parts and KPI. Data can be harvested across platforms through built-in connection points stored in special Data Connection Libraries (DCLs). The now structured data can be viewed through PivotTable reports or displayed prominently on Office SharePoint Server dashboards in real time.

Installation and Deployment

Office SharePoint Server 2007 is a resource hog. Its default configuration is to use exactly 50 percent of memory. There's ample justification for its greedy nature because Office SharePoint Server runs several services, each responsible for a variety of jobs. In addition, SQL Server 2005 also consumes its fair share of resources. The point being made is that this platform requires an abundance of computing resources. For developers concerned with running the software on personal computers for evaluation purposes, you should be aware that there is a noticeable performance penalty being paid to run Office SharePoint Server 2007.

The performance penalty manifests itself in client-facing applications such as the Central Administration Console and in server-facing functionality such as calculation engine response times. On the other hand, servers that meet the minimum hardware and software requirements run Office SharePoint Server 2007 efficiently.

You'll need to bear that in mind when you begin to write and deploy applications on your desktop or laptop. It warrants repeating that Office SharePoint Server 2007 is designed to run on high-end servers with sufficient computing and hardware resources, as outlined in the deployment and setup guide. For an enterprise, Excel Services resource consumption can be configured either upward or downward for improved site performance.

Installation Prerequisites and System Requirements

Excel Services can be installed either as a stand-alone application or in a server farm environment. For a stand-alone setup, you need to ensure that the server has the following:

• Dual processor with processor clock speeds at 2.5 GHz or higher

• At least 1 GB of RAM

• Windows Server 2003 Service Pack 1 with NTFS

• Microsoft .NET Framework 2.0

• Windows Workflow Foundation (WF) Runtime Components

• Internet Information Services 6.0

A stand-alone configuration is helpful if you need to evaluate the software or reduce the overhead cost of administration. The stand-alone application installs and configures Windows Internal Database, which is used to power the SharePoint site.

For the server farm environment, you need to meet all the requirements just listed plus have SQL Server 2005 or SQL Server 2000 Service Pack 3.

There are two modes available for Office SharePoint Server. These modes are configured by the administrator after installation. In evaluation mode, the default model is delegation. Trusted subsystem model is the default for enterprise mode. From a security point of view, delegation mode does not guarantee stronger data protection. In fact, there are instances where delegation mode is less secure. Delegation allows user credentials from the Excel Services front end to flow to the back end. Kerberos is used with delegation to enable credentials to work in a multitier scenario.

Assuming the minimum requirements are met, simply run the SharePoint Products And Technologies Configuration Wizard to configure Office SharePoint Server. The wizard walks you through the installation and configuration of the database, Windows SharePoint Services 3.0, and the Office SharePoint Server Central Administration application.

After the wizard has completed, you need to add the Central Administration site to the list of trusted sites so that you are not prompted for the user name and password each time you invoke the Central Administration application. You can do this through Internet Explorer trusted site settings.

Next, you need to configure Office SharePoint Server services. The services can be configured through the Central Administration application. The Central Administration application also allows you to perform a number of configuration adjustments to include the following functionality:

• Configure e-mail messages

• Create SharePoint sites

• Configure logging and auditing behavior

• Configure antivirus applications

The SharePoint Central Administration application is the core piece in the administration infrastructure. It has been rewritten to provide central management and configuration for Office SharePoint Server. For instance, administrative changes made to one server in the farm are automatically propagated to the rest of the farm. The automatic synchronization and propagation is handled by two services, Windows SharePoint Services Administration service and Windows SharePoint Services Timer service. The SharePoint Shared Services option allows you to manage most aspects of Excel Services, so you should become very familiar with it.

For the server piece, installation and configuration of IIS enables the Excel Services piece on the front-end Web server to forward requests to the Excel Calculation Service on the application server. The Excel Services component cannot be used if IIS is not present or is configured incorrectly.

Excel Services Permissions

Administrative control extends to workbook resources that are being used in the Excel Services application. For instance, you can limit the use of clients to view-only mode for shared workbooks. Or you can restrict client access to open, interact, and calculate workbooks while preventing the end user from viewing the file source or formulas in the workbook. Configuring permissions for all these activities follows the same procedure and is part of the administrator's responsibility.

As an IT support person or a site administrator, you have no obligation to understand the programming details for the applications that run on Excel Services. However, you need to be able to author and enforce policy that will allow these applications to run and execute underneath an umbrella of security within an enterprise. If you are a developer in an enterprise organization, there should be a dedicated resource specialized in Office SharePoint Server administration and configuration so that you needn't be too concerned about these parts.

For small to medium shops where one developer wears several hats, it's absolutely crucial to understand administration and configuration options. To that extent, the various administrative tools and configuration options put you in the driver's seat. These tools are designed with flexibility and ease of use in mind. There's also a healthy dose of documentation to help you get up to speed in the shortest possible time. You will get a feel for configuration as we walk through code examples later.

Installation Walk-Through

The SharePoint Central Administration application is the core piece in the administration infrastructure. It has been rewritten to provide central management and configuration for Office SharePoint Server 2007. For instance, administrative changes made to one server in the farm are automatically propagated to the rest of the farm.

Let's get started with the installation. Run the setup application to invoke the installation wizard. During the installation process, you need to choose between Basic and Advanced installation options, as shown in Figure 1-5.

About Configuration Options

Figure 1-5 shows the configuration options available during setup.

Figure 1-5. Installation choices during setup

The Basic option installs as a single, stand-alone server using default settings on the target computer. You are not prompted for any configuration settings or credentials. These default configurations are also used if you repair, upgrade, or select the stand-alone option from the Advanced installation option. For this option, a special Windows database MSSEE (Microsoft SQL Server Embedded Edition) is installed.

There are no further options to consider after choosing the Basic installation option. You should consider this option if you do not have a licensed version of SQL Server, do not care for account credentials configuration, or are evaluating the software. As of this release, Office SharePoint Server supports only databases from the SQL Server family of products. Figure 1-6 shows the server type settings during installation.

Figure 1-6. Server type installation choices

The Advanced option allows you to install the components for a single server or for a server farm. There are three options present: Complete, Web Front End, and Stand-Alone. These options are self-explanatory. During installation, you will be prompted for credentials. You should note that even in the case of the Advanced options, the components can run with full functionality against the SQL Server Embedded Edition. The wizard will finalize the installation without further input. If errors are encountered during the installation process, the files are not rolled back. You need to rerun the setup application after diagnosing the problem. A log file is written that provides detailed messages of the error. The error dialog box indicates the path of the log file stored on disk. Figure 1-7 shows an example of a failed configuration.

With the installation of Office SharePoint Server complete, the installation wizard will immediately launch the SharePoint Products And Technologies Configuration Wizard. The wizard walks you through configuring Windows SharePoint Services 3.0 to complete the installation of Office SharePoint Server 2007. Windows SharePoint Services is the focus of Chapter 5.

Figure 1-7. Failed configuration dialog box

About the SharePoint Products And Technologies Configuration Wizard

The SharePoint Products And Technologies Configuration Wizard must run to start SharePoint Central Administration. The wizard performs the following functions:

• Initial configuration

• Identification of missing components

• Configuration validation and repair

• Security reset

The configuration wizard might have to start, stop, or reset the Windows SharePoint Services Administration service, the Windows SharePoint Services Timer service, IIS, and services from other applications that depend on Windows SharePoint Services and are appropriately registered. You can run the wizard from the Start menu at any time from the Microsoft Office Server menu group.

Part of the wizard install process involves database configuration. During database configuration, you have the option to configure database settings. The fields are self-explanatory. If the database name does not exist, one is created for you. If the database name exists, it must not contain tables; otherwise, an error dialog box will inform you of the problem.

If you are installing the Advanced option (shown in Figure 1-6) but do not have a valid or licensed copy of SQL Server 2000 or SQL Server 2005, you can point the SharePoint Products and Technologies Configuration Wizard to the SQL Server Embedded Edition by entering the name of the database server as \##SSEE (SQL Server Embedded Edition). Be aware though that, as pointed out earlier, a number of limitations are imposed on MSSEE. Figure 18 shows the Specify Configuration Database Settings page.

Figure 1-8. Database configuration options

About the Configuration Database

The configuration database is used to store configuration and site-mapping information for your server farm. There is exactly one configuration database per server farm whether or not the server farm consists of one computer or several computers. If you are creating a new configuration database, provide the name of the database to the configuration wizard. If an existing database is used, it must not contain any tables, stored procedures, or other objects; otherwise, a duplication error will occur. You might also notice that the various database tables contain GUIDs. The naming convention enforces the fact that you should not be tampering with those tables except through the Excel Services object model.

If you are connecting to an existing configuration database, you can click Retrieve Database Names. The configuration databases that exist on the computer running SQL Server will be listed, and you can choose the appropriate configuration database. This option is not available if you elect to create a new database server. If the configuration database is hosted on a different computer, you must provide the credentials for a domain account. A domain account is recommended so that you can scale vertically.

For configuration databases hosted on domain accounts, you need a unique domain user account that you can specify as the Windows SharePoint Services service account. This user account is used to access the configuration database. The database access account will be used for both initial database configuration and ongoing connections.

The unique user account that you create also acts as the application pool identity for the SharePoint Central Administration application pool. This is the same account under which the Windows SharePoint Services Timer service runs. The SharePoint Products And Technologies Configuration Wizard adds this account to the SQL Server Logins, the SQL Server Database Creator server role, and the SQL Server Security Administrators server role.

After database provisioning is complete, the wizard allows you to perform port configuration. Figure 1-9 shows the Configure SharePoint Central Administration Web Application page, which allows you to configure a port.

Figure 1-9. Central Administration port configuration

Database Permissions Configuration

The database access account requires the following permissions:

• Security Administrator

• Database Creator

• Database Owner

You need to manually configure these permissions in SQL Server. The configuration wizard does not perform these configurations.

The account that you specify for database access must have the following minimum permissions:

• Read/write access to the configuration database

• Server-wide permissions in SQL Server

• DBO permissions

For new configuration databases, the database access account must have the following permissions:

• Create Database

• Create Procedure

SharePoint Products and Technologies Configuration

At this point, the SharePoint Products and Technologies Configuration Wizard runs and configures a predetermined set of tasks, as shown in Figure 1-10.

Figure 1-10. Configuring SharePoint Products and Technologies page

The SharePoint Products And Technologies Configuration Wizard performs a number of functions. It examines the system for legacy SharePoint products, prompts for use of the prescan tool, and exits if it detects legacy components. It configures database access for use with Office SharePoint Server 2007. The wizard also installs and configures various features and services for use. These services and features include the Excel Calculation Service and document management library. The wizard configures security and provides the hook for the Central Administration Wizard to customize site security and configuration at run time. The wizard also creates sample data for use in Business Intelligence scenarios. As a result of the changes that need to be made on your system, the account running the SharePoint Products and Technologies Configuration Wizard must be a member of the administrator group.

When the wizard is finished, one of three possible messages is displayed. The Configuration Complete dialog box indicates that the required configuration tasks have been completed and Central Administration is available and ready for use. The Configuration Successful dialog box indicates that the required configuration tasks have been successfully completed with no errors. If it hasn't, an error dialog box appears informing you that the wizard was not successful.

When setup is finished, your computer contains a single Web application. That Web application contains a single SharePoint site collection that hosts a SharePoint site. You can use the Central Administration utility discussed shortly to create more SharePoint site collections, sites, and Web applications.

For Basic installation, Central Administration is invoked automatically after this dialog box is dismissed. For Advanced installation, a dialog box appears confirming the settings that were applied during the configuration process. If you need to rerun the SharePoint Products and Technologies Configuration Wizard, click Start, click Programs, click Administrative Tools, and then click SharePoint Products And Technologies Configuration Wizard.

If you choose to uninstall the product by using the Windows Add Or Remove Programs option, the SharePoint Products and Technologies Configuration Wizard is invoked in silent mode. If an error occurs during silent mode, the failure code is returned to the SharePoint Products and Technologies Configuration Wizard and the Configuration Failed dialog box is displayed. If you exit setup after an error is displayed, the target computer will contain the configuration up to this point—that is, no rollback will be performed.

After installation is complete, the Central Administration Web application is invoked. The Central Administration application allows you to manage configuration settings for a server farm.

The Central Administration Configuration dialog box allows you to configure the security aspects and assigned port for the Central Administration Web application. Although Kerberos is the recommended security setting, it requires special configuration. To view the Kerberos configuration document for Windows Server 2003, see Kerberos Authentication in Windows Server 2003. In any case, most enterprise installations can safely use NTLM authentication. Figure 1-11 shows the Central Administration home page.

Figure 1-11. Central Administration Web application

The Central Administration application is the main tool for configuring Office SharePoint Server 2007. The key parts of Central Administration are the Central Administration and Shared Services Administration menus on the left; the Home, Operations, and ApplicationManagement tabs toward the top; and the Site Actions menu to the far right. These menus allow an administrator to find and configure every service and feature installed on a specific computer or across a farm.

Configuration of Excel Services

At this point in the installation, Excel Services is not provisioned on the server. The next few steps involve configuration and provisioning of the Excel Services feature. The configuration options detailed here will use Central Administration exclusively, so it is important that Central Administration is running correctly. As an alternative, Office SharePoint Server provides stsadm.exe, which gives you another way to configure and provision Office SharePoint Server.

From the Central Administration home page, you note that there are administrative tasks to be performed upon first use. The main task to be performed is the provisioning of the Shared Services Provider (SSP). The best way to understand the concept of the SSP is through the "grape analogy." Consider a bunch of grapes on a stalk, each drawing nutrients from the main vine. That particular organization lends itself well to the proper distribution of nutrients from the main vine to the grapes attached to the stalk.

Within the context of the architecture of Office SharePoint Server, the grapes are actually portals containing Web sites and subsites, as numerous as the grapes on the stalk. The stalk is the SSP. It is responsible for servicing the needs of the grapes in a manner that will allow the grapes to grow without being starved for resources. Similarly, the SSP provides a set of shared services to the portals and Web sites so that they can service end-user requests in an efficient manner. The SSP provides Reporting Services, Personalization Services, Business Data Catalog Services infrastructure, Search Services, and Excel Services. Figure 1-12 shows the SharePoint architecture.

Figure 1-12. The grape analogy diagram

For each vine, several stalks might contain grapes. So too, in the architecture of Office SharePoint Server, several SSPs might exist, each servicing one or more portals. You can set up and configure multiple SSPs if you intend to manage different resources across a farm. Portals serviced by an SSP can provide only these services defined within the SSP, nothing else. A grape or portal can be serviced only by a single SSP. A bunch of grapes, or site collections on a particular stalk or SSP, consumes resources from that particular SSP. There is no way to physically limit the "nutrients" or network resources servicing the grapes or Web applications. However, you can impose restrictions using privacy policies if you need to. The concept of an SSP works particularly well if you need to govern content and resources for various Web sites within Office SharePoint Server. We will return to the grape analogy again in Chapter 5.

About Provisioning Excel Services

So far, you have created the Central Administration site that governs access to all things SharePoint. Central Administration is hosted on the first server that is added to the farm. However, administrators don't typically develop and run code, developers do. We need to create and provision a new Web application either on this server or on any other server in the farm for the express purpose of supporting the code that we will write for the rest of the chapters. This will be the team site. Office SharePoint Server supports a large number of team sites, up to the resource limit of the server.

Security best practices recommend that you avoid deploying your applications to the administrative site. You also should not be running this new Web site under administrative privileges. When and if the text breaks with these recommendations, it does so sparingly and only for illustrative purposes.

One more thing to consider is that a link to Central Administration can be found in Administrative Tools on the Programs menu on Windows Server 2003. However, there is no quick link to the new team site you will create. Because these two sites, Central Administration and Team Site, aren't necessarily hosted on the same computer, you can create your own link to the team site. Or, if you prefer, you can reach the team site through the Shared Service Provider link on the Central Administration menu. This outlines the fundamental difference between the two sites, and it is very important for you to understand the difference between the two.

Let's provide a brief overview of the process before starting so that you have a sense of direction. We first need to create a Web application for the SharePoint site. IIS will direct client requests to this Web application. The Web application will contain the portal sites. Portal sites allow team members and users to log in, use Web Parts, and perform other activities.

Portal sites use resources that are provided by the Shared Service Provider, meaning that we first need to provision an SSP for the Web application and then create the first portal site. Finally, just as the Central Administration site provides administrative functionality for Office SharePoint Server, the Web application that we are creating to hold the portal site also contains an administrative page. This page helps the administrator govern access to the portal. This administrative page is not necessarily related to the Central Administrative page or the account that runs the Central Administration.

This new portal site should not be confused with the My Sites link on each page. The My Sites tab at the top of each page is a link to a special site that provides a personal space for each individual who is authenticated. This site is not accessible by other team members. A link to the My Sites page is available so that you can quickly jump to your personal space from any page on Office SharePoint Server. The next few steps walk you through the SSP and Web application provisioning steps.

To provision Excel Services, you need to first configure one or more SSPs. A number of dependent tasks need to be performed before you can provision an SSP. From Central Administration, select Operations. From the main window, select Services On This Server. Start the Excel Calculation Service. You also need to provision the search service. Click the Start button, and fill in the appropriate items, as shown in Figure 1-13.

Figure 1-13. Search provisioning and configuration

Confirm that Excel Calculation Service is running by clicking Manage Farm Features on the Operations menu, as shown in Figure 1-14.

Figure 1-14. Farm session configuration option

The Status column, shown in Figure 1-15, confirms whether the service is running.

Figure 1-15. Service confirmation

Perform similar steps to confirm that the search service is running. SSP provisioning requires an active index. The active index is configured from the search service.

Next, click Shared Services Administration to begin provisioning the SSP. Select New on the tab at the top, and walk through the wizard. You need to create a central site and an administration site. The site creation is a straightforward process. After the SSP is provisioned, click on the link in the left navigation menu.

Examine the options in this window to configure Excel Services for the farm. You might not be familiar with the short session and long session settings. Let's consider those. For efficiency reasons, calls from the front-end Web server are handled based on user sessions. These sessions have a time limit associated with them. If a request does not involve access to the spreadsheet, it is considered a short session and the time limit is adjusted downward to save on resources.

Otherwise, the session is adjusted upward based on the assumption that the user is interacting with the spreadsheet and should have a persistent session to the back end. This is the long session. Adjust these settings appropriately.

About the Trusted File Location

While we are at this point, let's configure the trusted file location. A trusted file location is a secured repository for document storage and retrieval in Office SharePoint Server 2007. You'll store your spreadsheets here and assign security access rights to these documents. The access rights extend to include the front-end and Web Service manipulation of the document. To configure the trusted file location, select Trusted File Locations from the Excel Services Settings menu. Figure 1-16 shows the Excel Services Settings menu.

Figure 1-16. Trusted file location

Use the server name and port information in your browser address bar to define a trusted file location. A good starting point is %PROGRAMFILES%\common files\microsoft shared\Web server extensions\12\bin. Click OK to save the information. The code you will write in Chapter 2 will load spreadsheets from this location because it is trusted.

If you are unfamiliar with Office SharePoint Server or get lost in the process, it might help to examine the breadcrumb trail at the top of the figures provided in this chapter. Every page on the Central Administration site contains a trail. These trails help you to determine where you are in the page hierarchy.

Don't be intimidated by the large number of settings and the different pages that make up the Central Administration site. As you progress through the book, you will gain hands-on experience with the various settings and pages. In any case, these various pages and settings are not that complicated, requiring only a moderate amount of effort to become functional in the administration and enterprise provisioning of a site. Central Administration was designed with ease of use in mind.

Unsupported Features and Limitations

Part of any evaluation process should cover unsupported features. Discovery of unsupported features can stall the adoption process or lead to a sour attitude aimed at the software. You are well aware that sour attitudes can inadvertently poison the enthusiasm of prospective clients. Let's walk through a short list of major unsupported features.

Although it is possible to copy the managed Excel assemblies to Windows XP Professional and compile Excel Services projects successfully, the run-time creation of the Excel Web Access object will fail. Excel Services was conceived to run on a server computer such as Windows Server 2003. Consumer operating systems such as Windows XP and Windows Vista will not run Office SharePoint Server. However, if you have downloaded and installed the Excel Services templates, you will be able to create projects that compile on any operating system. You will not be able to execute these assemblies, but you will be able to push these assemblies out to the server for deployment. Chapter 3 will talk more about this process.

Excel Services is not designed to author Excel workbooks. To author workbooks, you need to use the desktop version of Excel. Excel Services simply provides access to these authored workbooks and enforces the security constraints baked into the workbooks. Bear this in mind when you evaluate Office SharePoint Server.

The Central Administration Web application does not provide configurations for the entire feature set of Office SharePoint Server. The complete configuration for the entire feature set is provided by the stsadm.exe tool. For default installations, the tool is available at %PROGRAMFILES%\common files\shared\Web server extensions\12\bin. The stsadm.exe tool provides command-line administration for Office SharePoint Server 2007. If you need to run batch files or scripts, you need to use the stsadm.exe tool. Help for this tool is provided at the command prompt by typing stsadm.exe /?. Chapter 2 touches on the points of Office SharePoint Server that are necessary for deployment and Web Parts.

Excel Services does not support external references in cells. The workaround for this is to implement user-defined functions (UDFs), which are discussed in Chapter 4 and Chapter 7. UDFs can be used to perform a wide variety of tasks, such as calling Web Services, calling external libraries, and so on .

Finding the Web Application Root

When Office SharePoint Server is installed, the IIS application is extended to include Windows SharePoint Services. The extended Web application contains a root that maps to a Uniform Resource Identifier (URI). However, the majority of SharePoint Services functionality is stored in a content database and not in the root site or in the file structure of the Web application. Chapter 5 will discuss this in more detail. For now, note that the root does contain some files to include the web.config, user controls, Web Part definition files, and any other resources specific to the Web application.

In some cases, the resulting Web page is therefore a combination of content from the file structure and from the content database with a physical location pointing to Drive:\inetpub\wwwroot\wss\virtualdirectories\GUID. There might be two or more GUID directories in the virtual directory. One GUID represents the Central Administration, and the other represents the default content application. The GUID provides no easy way to differentiate between the two from the administrator's perspective.

Follow these steps to determine the Web application root directory:

1. At a command prompt, enter inetmgr.

2. Expand the Websites node.

3. Right-click the Web application in question.

4. Note the path from the home directory, as shown in Figure 1-17.

This set of steps comes in handy for Web Part deployment, so be sure to familiarize yourself with it.

Figure 1-17. Determining the Web application root from IIS

Summary

Excel Services is a new technology built and conditioned to interact with Excel spreadsheets. One of the defining points of this new technology is that it is designed to use Office SharePoint Server 2007. The programmable parts of Excel Services are hooked into the .NET Framework so that Web Parts can be created to run on Office SharePoint Server.

The decision to use Office SharePoint Server as a platform for Excel Services, coupled with the extensibility hooks available through the .NET Framework, means that you need to be at least familiar with the basic principles of configuring and managing Office SharePoint Server. This chapter presented the key parts that you should be familiar with. In any case, configuration and management can be learned without too much effort. You also need to possess more than a passing knowledge of .NET Framework security and customization concepts to tame this brute. And what a brute it is! Applications written for Excel Services can support large numbers of concurrent connections with internal load-balancing capabilities in clustered environments across a server farm.

Lessons learned from the past strongly influenced Microsoft's decision to use Office SharePoint Server as a platform for Excel Services. Office SharePoint Server is mature, configurable, scalable, and secure. The symbiotic relationship means that Excel Services essentially derives these significant benefits without an added burden. All in all, Excel Services is an innovative stride forward built to avoid the missteps of past endeavors. Developing Office system applications that are well behaved has become progressively easier. And there's still more progress to be made. With the installation basics out of the way, It's time to write code!

Additional Resources

• Programming Excel Services: Chapter 3: Excel Web Access (Programming Excel Services)

• SharePoint Server Developer Portal

• Microsoft Office Developer Center