Using Excel Services to share pieces and parts of Excel workbooks
Excel Services in Microsoft SharePoint Server 2010 enables you to reuse and share pieces and parts of Excel workbooks. For example, you can create a single workbook, publish it to a SharePoint site, and then display charts or elements on multiple SharePoint pages by using Excel Web Access Web Parts. And, by specifying or editing permissions in SharePoint Server, you can control what users can see or do with Excel workbooks that you publish to SharePoint Server.
What do you want to do?
Publish a workbook to SharePoint Server and then display items from the workbook in multiple locations
You can create a single Excel workbook that contains a variety of elements and publish the workbook to a SharePoint site. Some elements, such as charts, PivotTables and PivotCharts, and Excel tables, have unique names assigned to them automatically by Excel when you create them. These names can be important in the publish process. There may be other elements, such as worksheets, ranges of data, and sparklines, for which you must create defined names so that the publish process recognizes them as available items. After you publish your workbook with the items you want to include, you can display those items on various SharePoint pages by connecting the workbook and a single item to an Excel Web Access web part. This enables you to reuse these items in multiple locations.
-
In your Excel workbook, identify the worksheets, charts, tables, and any other elements that you want to publish.
-
As needed, define an element you want to publish as a named item. To do this, use the following procedure:
-
Identify a chart, table, a range of cells, or a worksheet that you want to define as a named item. Then, select a range of cells that contains the information you want to include in the named item.
To define an entire worksheet as a named item, select an empty cell in the worksheet and press CTRL+A. -
On the Formulas tab, in the Defined Names group, click Define Name.
The New Name dialog box appears. -
In the Name box, type a name for the item.
-
In the Scope list, select Workbook.
It's important that you select Workbook so that you can locate the named item outside of the worksheet that contains it. For example, if you want to display a named item in an Excel Web Access Web Part and its scope is not set to Workbook, you will not see the item in the list of Named Items in the Excel Web Access Web Part. -
Use the Refers to box to confirm that the range of selected cells that is correct. Make adjustments as necessary.
For example, if you want to include a PivotChart report alongside a PivotTable report, make sure that both reports fit inside the range of cells that is listed in the Refers to box. -
Click OK to close the New Name dialog box.
-
-
Repeat Step 2 for each item that you want to define as a named item.
-
After you have finished defining named items, publish the workbook to SharePoint Server. To do this, use the following procedure:
-
On the File tab, click Save & Send, and then click Save to SharePoint.
-
In the Save to SharePoint section, click Publish Options.
-
In the Publish Options dialog box, on the Show tab, use the list to select Items in the Workbook.
-
Select the named items in the list that you want to publish, and then click OK.
Note that you can choose between named items in the workbook or specific (or all) worksheets in this dialog box, but you can't specify a combination of worksheets and items. For this reason, we recommend assigning a defined name to a worksheet if you want to publish entire worksheets in addition to other items, such as charts and tables. -
In the Save to SharePoint section, click Browse for a location, and then click Save As.
The Save As dialog box opens. -
In the Save As dialog box, specify the following options:
-
In the address box, type the URL to a SharePoint document library.
The URL resembles http://<server name>/<SharePoint site name>/< document library>. -
In the File name box, type a name for the Excel workbook.
-
-
Click Save to publish the workbook.
-
-
Use Excel Web Access web parts to display the named item(s) that you have defined on one or more SharePoint sites. For information about how to do this, see Display a workbook in an Excel Web Access Web Part.
Control what workbook users can see or do by specifying SharePoint Server permissions
Occasionally, you might want to restrict users from opening the entire workbook in Excel or from making changes to it by editing it in the browser. If you have the necessary permissions, you can specify permission settings in SharePoint Server to control what users can see or do with each workbook. For example, you can configure a workbook such that users can view it in a web browser, but not open it in Excel.
Notes:
-
Users who have at least Read permissions can typically open workbooks in Excel from a SharePoint library. For example, when you publish a workbook to SharePoint Server, by default, users can view it in a browser window.
-
If Office Web Apps are installed, then users can typically also edit the workbook in the browser window. In addition, if Microsoft Excel is installed on their computers, users can typically open the entire workbook in Excel.
Important: Before you attempt to modify user permissions in SharePoint Server, make sure to contact your SharePoint site administrator.
To specify user permissions for a workbook that has been published to SharePoint Server, take the following steps:
-
Browse to the SharePoint library that contains the Excel workbook for which you want to specify permissions.
-
Hover over the item, click the arrow that appears, and then, in the drop-down box next to the file name, select Manage Permissions.
-
By default. SharePoint lists and libraries are typically configured to inherit the permissions that are assigned from their parent sites. You can either manage those parent-site permissions, or you can choose to stop inheriting parent-site permissions and instead, specify custom permissions for the workbook.
Do one of the following:-
To manage the parent-site permissions, click Manage Parent. The list of SharePoint users and groups is displayed with a checkbox next to each name.
-
To stop inheriting parent-site permissions and specify custom permissions for the workbook, click Stop Inheriting Permissions. In the warning box that opens, click OK. The list of SharePoint users and groups is displayed with a checkbox next to each name.
-
-
Select the groups or users for whom you want to specify permissions, and then click Edit User Permissions.
-
Select (or clear) the checkboxes for the permission levels that you want to grant (or remove).
-
To enable users to view, but not download, the workbook, select View Only and clear all the other options.
-
To enable users to view and download the workbook, select Read.
-
To enable users to view, download, add, update, and delete the workbook, select Contribute.
-
To enable users to view, download, add, update, delete, and approve the workbook, select Design.
-
-
After you have selected (or cleared) the checkboxes for the users and groups that you selected in Step 4, click OK.
-
Repeat Steps 4-6 until you have finished specifying permissions for the workbook.
To learn more about how to assign user permissions in SharePoint Server, see the following resources:
Get more information
For more information about Excel Services and publishing workbooks, see the following resources:
No comments:
Post a Comment