Making an Excel workbook available through Excel Services begins with uploading an Excel 2007 workbook to a document library on a SharePoint Server 2007 site. You can upload an existing Excel workbook or create a new one directly within the document library. Any of the standard techniques for interacting with a document library will work to store an Excel file on the server, such as uploading it through the Web site or through the Explorer View. However, to take advantage of specific features of Excel Services, such as controlling which worksheets are visible and which cells can receive input, you must use the Publish feature within Excel 2007. None of the other methods of making an Excel 2007 workbook available through Excel Services (for example, storing it in a file share or non-SharePoint Web site) will provide support for restricting the visibility of sheets and defining input parameters.
To publish a workbook to Excel Services, follow these steps:
Open and edit the file in Excel 2007.
From the File menu, point to Publish and select Excel Services
For the Save As Type, select either Excel Workbook (.xlsx) or Excel Binary Workbook (.xlsb).
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://contoso.msft/sitedirectory/sales/forecasts/Q12007.xlsx.
Click Excel Services Options as shown in Figure 20-2.
If you want to hide any parts of the workbook, complete the following actions: On the Show tab, click the drop-down list and select either Sheets or Items In The Workbook. Then clear the check boxes for items that should remain hidden from viewers.
If you want to allow users to input new values into the spreadsheet at run time, complete the following actions: On the Parameters tab, click Add, and then choose the parameter cells.
Figure 20-2: Publish Excel Workbook-Save As Dialog
To use parameters, you must define named cells that can be updated with new values.
Publishing an Excel 2007 workbook stores it in a SharePoint Server 2007 document library, which allows you to take advantage of the document management features built into document libraries, such as version control, workflows, and life-cycle management, including information management policies and auditing. Users can publish workbooks to any document library for which they have Add permissions, but they will not be able to view the workbook through Web Access until you enable it as a Trusted File Location.
Some organizations use the Workbook Sharing feature to allow multiple users to edit a spreadsheet simultaneously. If this feature is enabled in the file, Excel Services will not load or process it.
One of the controls that can be placed on a workbook when it is uploaded to the server is to limit which parts of the workbook can be viewed by users in the Web browser. Although Excel Services loads and processes the entire spreadsheet when a user requests to view it, the author can specify which parts of the workbook are visible and which parts are hidden. Excel 2007 provides three options for controlling the visible area of the workbook:
Entire Workbook The default option is to display all items.
Sheets You can select specific sheets to display, but you cannot limit which items on each sheet are available.
Items In The Workbook You can select from a list of named ranges, charts, tables, pivot tables, and pivot charts. In this case, you need to assign names to these objects before saving the workbook to the server.
None of these options affect the ability to view and edit items in a workbook when it is opened in Excel 2007, only when it is viewed through the Web browser. If the workbook contains many sheets of supporting data and calculations or charts that show different presentations of the results, you can choose to hide the parts of the workbook that are not relevant to the viewer. Any time you want to edit the workbook, you can open it directly in Excel 2007 and have full access to all the sheets and objects.
Excel Web Access renders workbook data in a read-only view, which allows users to navigate between the spreadsheet tabs but not edit any of the cells. You can enable selective user input to the workbook by naming specific cells and then identifying those cells as Parameters during the publishing process. To assign a name to a cell, right-click the cell in the workbook and select Name A Range. The dialog box shown in Figure 20-3 appears. Enter a meaningful name and a description that explains the purpose of the parameter.
Figure 20-3: The New Name dialog box for defining parameters
Each parameter is a single value that can be changed by the user and will be updated in the workbook when the user applies the change. The changed value is preserved only for the individual session that the user is participating in, and all parameter changes are discarded when the user's session ends. Likewise, the parameters entered by one user are not visible to other users and do not affect the calculations in other users' sessions. You add and remove cells as parameters on the Parameters tab of the Excel Services Options dialog box, as shown in Figure 20-4.
Figure 20-4: The Parameters tab
It is a good idea to use clear and easily understood names for the cells that will be used as parameters because the cell name appears as the parameter name in the Web browser.
There are certain requirements for a cell to be used as a parameter:
The parameter can refer only to a single cell.
The cell must be a named "cell."
The cell cannot contain a formula.
The cell cannot be in a pivot table, table, or chart.
A powerful aspect of the parameter feature is that a user can update a parameter even if the cell falls in a part of the workbook that is not marked as visible. Although the user might not be able to see the cell that is affected, the results of calculations linked to that cell value might be visible. This allows you to hide the underlying data set that produces results while still allowing users to update key values.