Working with Spreadsheets Through Excel Web Access

Excel Web Access allows users to interact with workbooks in two ways: opening a workbook so that it is completely rendered in the browser, and viewing a portion of a workbook within an Excel Web Access Web Part. All the features of Excel Web Access are available in both views, and the difference is primarily related to whether the data being displayed is important in the context of other indicators in the site or is self-contained within the workbook.

Viewing a Spreadsheet in the Browser

In some cases, the very power of Excel workbooks has become an encumbrance to users. In the past, large workbooks with complex calculations became difficult to use because they took time to load and the external link updating and formula recalculations had to be re-run by every user who opened the file. Sometimes a user only wanted to view a single chart or the results of one calculation. At other times, the user wasn't sure whether the workbook contained the information he was looking for, but he had to take the time to open it to find out.

When a workbook is published to Excel Services, you can configure the default settings of the document library so that a user can simply click the link to the file in the document library and SharePoint Server 2007 will redirect the user to a full-browser view of the workbook.

To specify that opening a file in the Web browser is the default action that occurs when a user clicks the link in a document library, modify the document library settings as follows:

1. Open the Document Library Settings page.

2. Click Advanced Settings.

3. Under Browser-Enabled Documents, click Display As Web Page and then click OK.

Alternatively, a user can select View In Web Browser from the document context menu, as shown in Figure 20-5.

Figure 20-5: Viewing in a browser

This is different from the traditional method of opening an Excel file from a Web server, where the file is downloaded to the browser and Excel is loaded in place to provide the rendering. With Excel Web Access, Excel Services handles the processing and rendering of the workbook and returns only HTML to the Web browser. That means that large spreadsheets do not have to download to your workstation for you to be able to view them. Any sheets or objects that were hidden during the publishing process will be unavailable, and a parameters pane will appear to allow input of values into parameter cells.

Note 

Although you can view and interact with workbooks through the Web browser, you cannot edit them directly. To modify a workbook, you need to edit it in Excel 2007.

Commands Available Within the Browser

You will find that you are able to navigate around the workbook, changing sheets and scrolling to view cells or charts, in much the same way as in the Excel client. This section covers the following commands; however, other commands are also available within the browser:

• Open

• Open Snapshot

• Reload Workbook

• Refresh Selected Connection

• Refresh All Connections

• Calculate Workbook

• Find

Open in Excel 2007

Use the Open In Excel 2007 command to download the workbook to your local workstation and then open the workbook directly in Excel 2007. This command requires that you have sufficient permissions to load the workbook, including the Open Items and Edit Items list permissions. Using this command is not the same as opening the spreadsheet for editing directly from the document library. Although this command gives you complete access to all areas of the workbook, the workbook is opened as a copy of the file on the server and cannot be saved directly back to the server.

Open Snapshot in Excel 2007

A "snapshot" is a read-only copy of an Excel file produced by Excel Services for users who do not have the right to open the full spreadsheet. Use the Open Snapshot In Excel 2007 command to display the data and results from formula calculations, as processed by Excel Services, but not the formulas themselves. Only sheets and graphs that were made available in the workbook when it was uploaded are rendered in the snapshot. Excel Services generates the snapshot by opening the file on the server, requerying external data sources, recalculating cell values, and outputting the values and formats to the browser.

Reload Workbook

Use the Reload Workbook command to reset any parameter values that have been applied to the workbook, and reload the workbook from its file location. This command essentially requests the Web server to retrieve the original workbook file from its storage location and re-render it in the browser with all default values.

Refresh Selected Connection/Refresh All Connections

Use the Refresh Selected Connection command to refresh the external data from a single PivotTable with an external connection. Use the Refresh All Connections command to refresh the data from all external connections.

Calculate Workbook

Use the Calculate Workbook command to execute all calculations in the workbook without refreshing external connections. This can cut down the time that it takes to recalculate results compared to a full reload of the workbook.

Find

Use the Find command to perform a server-side search for a value and highlight the cell it is in. The standard browser-based Find command does not work in Excel Web Access because the workbook content is not rendered in the source HTML of the page.

Note 

Although you can view and interact with workbooks through the Web browser, you cannot edit them directly. To modify a workbook, you need to open it in Excel 2007.

Analyzing Data in the Browser

When users view a workbook through Web Access, they receive a copy of the data rendered as HTML in their browser. Although Web Access does not support updating data in the workbook, users can navigate through the sheets and change their view of the data. Users can perform analyses on the data by, for example, sorting and filtering data in a table, without affecting any other user's view of the data.

Working with Tables and AutoFilters

Workbooks that contain Excel 2007 tables defined on ranges automatically expose those features within Excel Web Access. You can use the drop-down menus on each column in the table to sort in ascending or descending order, to use numerical comparison and text filters depending on the data type of the column, and to use the multiselect filter to quickly select items based on discrete values.

Working with PivotTables

When you include a PivotTable in the workbook, you can also interact with it in the Web browser. Although you cannot restructure the pivot table in the browser, essentially all the other features available in PivotTables within Excel 2007 are also available in the browser view. You can expand and collapse levels, sort and filter, and apply quick filters such as "Top 10" (for numbers) and "Last Quarter" (for dates).

Unsupported Features

Excel Services can recalculate formulas embedded in cells, but there are several types of content that it cannot process. To ensure the security and stability of Excel Services, most external object references such as ActiveX controls and Excel Add-ins are not supported. Additionally, embedded code, such as macros written in VBA, are not supported, so any custom functions or procedures need to be rewritten as user-defined functions and compiled into assemblies.

More Info 

For more details on referencing external function assemblies, see the "User-Defined Function Assembly" section later in this chapter.

Following is a list of the various features that are not supported by Excel Services:

• Spreadsheets with code (includes spreadsheets with VBA macros, forms controls, toolbox controls, MS 5.0 Dialogs, and XLM Sheets)

• IRM-protected spreadsheets

• ActiveX controls

• Embedded SmartTags

• PivotTables based on "multiple consolidation" ranges

• External references (links to other spreadsheets)

• Spreadsheets saved in formula view

• XML expansion packs

• XML Maps

• Data validation

• Query Tables, SharePoint Lists, Web Queries, and Text Queries

• Spreadsheets that reference add-ins

• Spreadsheets that use the RTD() function

• Spreadsheets that use spreadsheet and sheet protection

• Embedded pictures or clip art

• Cell and Sheet background pictures

• AutoShapes and WordArt

• Ink Annotations

• Organization Charts and Diagrams

• DDE Links