Displaying an Excel Workbook in the Browser

To display the Excel workbook in the browser, you use the Excel Web Access Web Part that renders the workbook in HTML. This Web Part is available to be added from the Web Parts Gallery under the Business Data grouping of Web Parts. Once added to a page, the Web Part gives you the following configuration options in addition to the default options available in all Web Parts found under the groupings of Appearance, Layout, and Advanced. Figure 12.13 shows the Excel Web Access Web Part tool pane.

Workbook Display Options

These options allow you to set up which workbook you want to connect to and what information from that workbook you want to display.

• Workbook. Select the workbook you want to display.

• Named Item. Choose a single item such as a table or a chart to display. All additional items will be available on the View drop-down list on the Web part toolbar.

• Rows. Determine how many rows should be displayed.

• Columns. Determine how many columns should be displayed.

Toolbar and Title Bar Options

With these options, you can determine how you want the title and URL generated, and what type of toolbar you want to make available for the visitors on the site.

• Autogenerate Web Part Title. The Web Part title will be generated in the format Excel Web Access-X, where X is the name of the workbook.

• Autogenerate Web Part Title URL. The URL will be pointed at the workbook and will open the workbook in the browser.

• Type of Toolbar. The four options—Full, Summary, Navigation, and None—will vary the number of commands available on the toolbar.

Toolbar Menu Commands

These four options give you the opportunity to determine which commands are available to the user on the toolbar.

• Open in Excel, Open Snapshot in Excel. If you uncheck this box, the Open command on the toolbar will be removed. Open in Excel opens the entire workbook in Excel, whereas Open Snapshot in Excel opens only a snapshot of the data with the modifications and removes all proprietary information.

• Refresh Selected Connection, Refresh all Connections. If you uncheck this box, the Refresh Selected Connection and Refresh All Connections commands will be removed from the Update drop-down list on the toolbar. These commands allow the user to refresh data connections within the Excel workbook.

• Calculate Workbook. If you uncheck this box, the Calculate Workbook command will be removed from the Update drop-down list on the toolbar. This command allows the user to calculate the workbook when modifications are made.

• Named Item Drop-Down List. If you uncheck this box, you will remove the View command on the toolbar, which allows you to switch to other named items, such as charts and pivot tables.

Navigation

These options determine which navigation options are available. Taking these options away will prevent users from viewing other portions of the workbook in this Web Part.

• Workbook Navigation. Checked by default, if you uncheck this box, you will remove both the Find command on the toolbar and the View command on the tool bar; the View command allows you to switch to other named items.

• Hyperlinks. Checked by default. If you uncheck this box, hyperlinks within the workbook will no longer be available.

Interactivity

Removing these options will determine what level of interactivity you want to allow in this view of the workbook.

• All Workbook Interactivity. All Workbook Interactivity will be available unless taken away by deselecting one of the following options.

• Parameter Modification. By deselecting this option, the user will not be able to modify the parameters of the workbook. The following option, Display Parameters Task Pane, will be grayed out.

• Display Parameters Task Pane. This option will remove the Parameters Task Pane where the user can make modifications to the parameters.

• Sorting. On tables in the workbook, this option allows the user to sort as they would in Excel.

• Filter. On tables in the workbook, this option allows the user to filter as they would in Excel.

• All PivotTable Interactivity. This option allows the user to interact with the PivotTable as they would in Excel.

• Periodically Refresh if Enabled in Workbook. This option is selected by default. If deselected, the periodic refreshes will not be automatic, and the user will need to manually refresh the data by selecting the command on the Update menu on the toolbar.

• Display Periodic Data Refresh Prompt. If the periodic refresh is enabled in the workbook, you choose whether to notify the user of refreshes: Always, Optionally, or Never. This is set to Always by default. It’s a good idea to leave this set to Always, so the user knows when the data is going to be updated.

• Close Session Before Opening a New One. This is not selected by default; however, if you do select this option, when the same workbook is opened the current session will be ended.

Exercise: Adding and Configuring an Excel Web Access Web Part to a Page

For this exercise, you’ll be using the Sales Number workbook published to the Shared Documents library in the previous exercise. 1. Navigate to the Sales site home page at http://portal/sales/. 2. Click Site Actions > Edit Page. 3. In the Left Web Part zone, click Add a Web Part. 4. Under the Business Data header, select the Excel Web Access check box. Click Add. 5. On the Web Part, click Edit > Modify Shared Web Part. 6. For the workbook, click the Browse button, navigate, and select the “Sales Numbers” workbook in the Shared Documents library on the Sales site. Click OK. 7. For Named Item, enter “Chart 1”. 8. Leave the rest of the defaults, and click OK. 9. Click Exit Edit Mode. Your page should look like Figure 12.14. Figure 12.14. Chart 1 from Sales Number rendered using the Excel Web Access Web Part. [View full size image]