Publishing Office Excel 2007 Workbooks

Office Excel 2007 integrates with SharePoint Server to provide a new publishing feature that sets Excel Services options that define how the workbook will function in Excel Calculation Services. Uploading an Office Excel 2007 workbook directly to a trusted location document library makes it accessible, but you can only set the Excel Services options through the Publish command.

Publishing a Workbook to Excel Calculation Services

1. Open and edit the file in Office Excel 2007.

2. From the File menu, point to Publish and select Excel Services, as shown in Figure 11-4.

3. For the Save As Type, select either Excel Workbook (.xlsx) or Excel Binary Workbook (.xlsb). A binary workbook can be useful in instances where there are compatibility or performance issues because the data are stored in binary format instead of XML.

4. In the File Name box, type the full URL path to the document library along with the file name of the document: for example, http://spserver/records/excellibrary/portfolioanalysis.xlsx.

5. Click the Excel Options button in the lower right corner of the dialog box.

6. On the Show tab of the Excel Services Options dialog box, shown in Figure 11-5, click the drop-down list and select either Sheets or Items in Workbook and check which objects should be visible to users. See the next section for more details.

7. On the Parameters tab, click Add. The Add Parameters dialog box, shown in Figure 11-6, then appears.

8. Check the box next to the parameter that users can use to input values. See the next section for more details.

   Important 

   Only cells that are given Defined Names in Office Excel 2007 appear in the Add Parameters dialog box.

9. Click OK.

10. Click Save to save your workbook to the document library.

Figure 11-4: Publishing a workbook to Excel Calculation Services.

Figure 11-5: The Show tab in the Excel Services Options dialog box.

Figure 11-6: The Add Parameters dialog box.

Configuring Excel Calculation Services Publishing Options

You are given several choices when publishing from Office Excel 2007.

Restricting Visibility

By default, when you publish a workbook to Excel Services, the entire workbook is processed and rendered to users who view it in a Web browser. A powerful feature of Office Excel 2007 is the ability to restrict which sheets or objects within the workbook can be seen by users. Restricting access to parts of the workbook in the Web browser affects all users, including the workbook author. On the Show tab of the Excel Services Options dialog box, shown in Figure 11-5, you can select the following options:

• Entire Workbook This option does not restrict workbook visibility.

• Sheets This option allows only specific sheets to be seen. Use this option to hide sheets with supporting data or calculations that you do not want to expose while displaying only the sheet(s) showing the results.

• Items In The Workbook This option allows you to select specific named ranges, charts, tables, pivot tables, and pivot charts in the workbook to display. Only objects with Defined Names appear in the list.

Defining Parameters

When you publish a workbook to Excel Calculation Services, users who view the workbook in the browser are not able to edit the data in the cells. To allow users to input data for calculations in the workbook, you need to expose input parameters. A parameter is a named cell in the workbook that is exposed through a data entry screen in Excel Web Access. Users enter values in the input screen to update the calculations in the workbook. Users can update parameter values even if the cell is on a sheet that is hidden from view. The parameter values input are temporary and apply only to a specific user's session.

Opening Excel Workbooks in the Browser

When a document library is registered as a Trusted File Location with Excel Calculation Services, users can either open the workbook directly in Excel or they can view the workbook content directly in the browser. Opening a workbook in the browser provides a read-only view that still allows users to scroll, navigate between tabs, and sort and filter data. While viewing a workbook in the browser, data and results on a sheet are displayed but users cannot see the formulas or link to references that produced the results. If you have enabled parameter options, then users can enter values for those parameters and see the calculated results.

Viewing a Workbook in the Browser

1. Browse to the document library that contains the workbook.

2. From the document context menu select View In Web Browser.

Configure the Default Click Action to Open in a Browser

1. From the document library toolbar, click Settings and then click Document Library Settings.

2. Under General Settings, click Advanced settings.

3. In the browser-enabled Documents section, select Display As A Web Page.

The Excel Web Access Web Part

Excel Web Access is the component of SharePoint Server that renders Office Excel 2007 workbook content in HTML format in a Web browser. One of the innovations of Excel Web Access is that all of the conversion is done on the server so that no Active X controls or other binary objects are delivered to the browser. Excel Web Access allows users to open an entire workbook in their Web browser and, through the Excel Web Access Web Part, to display selected objects from workbooks on a dashboard page.

Adding an Excel Web Access Web Part to a Dashboard

1. Open the dashboard and select Edit Page from the Site Actions menu.

2. In the zone where you want the chart or sheet to appear, click Add A Web Part.

3. Scroll down the list to the Excel Web Access Web Part, select the check box next to it, and then click Add.

4. On the dashboard, click on Click Here To Open The Tool Pane link.

5. Next to the Workbook text box, shown in Figure 11-7, click the ellipsis button (…) and browse to the workbook that you want to display.

6. In the Named Item text box, type the name of the range of cells or chart objects to display in the Web Part.

7. Click OK.

Figure 11-7: Excel Web Access Web Part properties.