Excel Services Overview
Excel Services is a new server technology included in Microsoft Office SharePoint Server 2007. This shared service enables you to load, calculate, and display Excel workbooks on Office SharePoint Server 2007.
Using Excel Services, you can easily reuse and share Excel workbooks on Office SharePoint Server 2007 portals and dashboards. For example, financial analysts, business planners, or engineers can create content in Excel and share it with others by using an Office SharePoint Server 2007 portal and dashboard—without writing custom code. You can control which data is displayed, and you can maintain a single version of your Excel workbook.
There are two primary interfaces for Excel Services:
An Excel Web Access Web Part, which enables you to view and interact with a live workbook using a browser
Excel Web Services for programmatic access
In addition, you can extend Excel Calculation Services by using user-defined functions (UDFs).
For more information about Excel Calculation Services, see Excel Services Architecture.
Using Excel Services, you can view live, interactive workbooks using only a browser. This means you can save Excel workbooks and interact with them from within portal sites.
You can also interact with workbooks to explore and pivot on data, and analyze PivotTable reports and charts by using a browser. Excel Services supports workbooks that are connected to external data sources. You can embed connection strings to external data sources in the workbook or save them centrally in a data connection library file.
You can also make selected cells in worksheets editable by making them named ranges (or "parameters"). These items that you choose to make viewable, when you save to Excel Services, will appear in the Parameters pane in Excel Web Access. You can change the values of these named ranges in the parameters pane and refresh the workbook. You can also use the portal's filter Web Part to filter several Web Parts (Excel Web Access and other types of Web Parts) together.
However, you cannot use Excel Services to create workbooks. To author a workbook you must use Microsoft Office Excel 2007.
Excel Services also has a Web service. You can use Excel Web Services to load workbooks, set values in cells and ranges, refresh external data connections, calculate worksheets, and extract calculated results (including cell values, the entire calculated workbook, or a snapshot of the workbook).
For more information about snapshots, see How to: Get an Entire Workbook or a Snapshot.
Excel Services supports UDFs, which you can use to extend the capabilities of Excel Calculation Services—for example, to implement custom calculation libraries or to read data from Web services and data sources that are not natively supported by Excel Services.
Excel Services is designed to be a scalable, robust, enterprise-class server that provides feature and calculation fidelity with Excel 2007.
Scenarios and Features
Excel Services supports many different scenarios and features, some of which are described in this section.
Business Intelligence Portal and Workbook Analysis
A business intelligence portal displays scorecards and reports, and enables users to explore data using only a browser. The Report Center feature in Office SharePoint Server 2007 includes a business intelligence portal and dashboard functionalities. Figure 1 shows a report center dashboard with a library of reports, a chart, and Key Performance Indicators (KPIs) already set up.
Excel Services also gives you the ability to calculate data on the server. You can display an Excel workbook by using the Excel Web Access Web Part, connect to external data sources, and further interact with the data in the workbook.
The following figure shows a dashboard with a filter Web Part, and Excel workbooks displayed using the Excel Web Access Web Parts.
The next figure shows a workbook with formulas and specific cells designated as parameters. Designating specific cells as parameters enables users to change values in those cells in a worksheet by using the edit boxes in the right pane. Excel Services then recalculates the worksheet based on the new values.
If you need to use certain functionalities in Excel or analyze a workbook by using all Excel functionalities, you can open a workbook in Excel by clicking Open in Excel 2007. You can also open the workbook in Excel to print it and work offline.
To open a workbook using the Open in Excel 2007 command, you must have "open" rights. For more information, see the next section, "Managing Workbooks." Users who do not have this right can still open a snapshot in Excel.
You can also analyze, pivot, and interact with data using Excel Web Access as shown in the following figure.
For more information about Excel Services and business intelligence capability in Office SharePoint Server 2007, see the business intelligence documentation in Office SharePoint Server 2007 Help and on Office Online (http://office.microsoft.com/en-us/default.aspx).
The workbook management and lockdown capabilities of Excel Services enable you to:
Maintain only one copy of the workbook, created and changed by a trusted author in a central, secure place, instead of multiple copies on every user's computer. The correct version of the spreadsheet is easier to find, share, and use from within Excel and other applications.
Secure and protect workbook models and back-end data. You can give users "view only" rights to limit access to the workbook. For example, you can prevent users from opening a workbook using Excel or control what they are allowed to view in a workbook.
Create snapshots of a workbook.
Excel Services is optimized for many users and many workbooks. It can also load-balance calculation across the server farm.
For more information about managing workbooks using Excel Services, see the Office SharePoint Server 2007 TechCenter, Office SharePoint Server 2007 Help, or Office Online (http://office.microsoft.com/en-us/default.aspx).
Programmatic Access Through Custom .NET Applications
You can create custom applications—for example, ASP.NET applications—that:
Call Excel Web Services to access, parameterize, and calculate workbooks.
Have custom workflow to schedule calculation operations or send e-mail notifications.
User-Defined Functions (UDFs)
You can also use Excel Services UDFs, which give you the ability to use formulas in a cell to call custom functions that are written in managed code and deployed to Office SharePoint Server 2007.