Tuesday, 22 May 2012

Excel Service in SharePoint 2010

How to add and use Excel Service and Excel web access webparts in SharePoint 2010?

Excel Service is a very useful feature in SharePoint 2010 you can do the below things and many more by using this

  1. You can use to display Excel worksheet as a webpage for the users
  2. You can restrict Access to specific worksheets in an Excel workbook and display only the once needed to the users

  1. You can even show a specific named item in a worksheet, so lets say you want to show only one table you created in the worksheet or a set of cells you can do that even !!!

Doesn't this sound exciting ? Lets crack on…...

         Lets first see how to Display a workbook in an Excel Web Access Web Part
You can display an Excel workbook on a dashboard or other site page by connecting the workbook to an Excel Web Access Web Part. You can customize the appearance and functionality of Excel Web Access Web Parts, and you can connect them to other Web Parts for data analysis tasks such as filtering and getting external data.

To display a workbook in an Excel Web Access Web Part, you must add the Web Part to a page and then connect the workbook to the Web Part by entering the workbook URL or UNC in the Excel Web Access tool pane. As a final step, you can customize the Web Part by selecting properties in the Excel Web Access tool pane for the Web Part. By configuring properties, you can control the size and layout of the Web Part, and control the ways that users can interact with the workbook.

 How to Add an Excel Web Access Web Part to a page
This procedure describes the specific steps that you have to take to add an Excel Web Access Web Part to an existing Web Part page. To add a Web Part to a page, you must belong to the default Members SharePoint group for the site.

If no appropriate page exists, you might have to create a site page.

To add an Excel Web Access Web Part to a Web Part page, take these steps.

  1. In the Quick Launch pane, click All Site Content. Then, on the All Site Content page, click Create.
  2. On the Create page in the Pages and Sites list, click Web Part Page.

  1. The following illustration shows an example of a newly created Web Part page. The page contains a Header zone and one Body zone. Each zone contains the Add a Web Part box. In the zone where you want to add the Excel Web Access Web Part, click Add a Web Part to display the Web Part Picker.

  1. In the Categories pane of the Web Part Picker, click Business Data. In the Web Parts pane, click Excel Web Access. Then, click Add.

Connect a workbook to a Web Part

  1. In the Excel Web Access
    Select a Workbook
    pane, click the link
    Click here to open the tool pane
     The page changes to Edit mode, and displays the Excel Web Access tool pane.

  1. In the Workbook Display section in the
    text box, enter the URL or UNC of the workbook. If you do not know the URL, you can click the browse button, and then select the workbook that you want to use in the
    Select an Asset
    -- Web Page Dialog
  2. In the
    Named Item box, type the name of a named item in the workbook (such as a defined name, the name of a chart, table, PivotTable, or PivotChart) that you want displayed in the web part.
  1. In the Toolbar and Title Bar section and in the
    Navigation and Interactivity section, select one or more properties that you want to enable. If you want to disable a property, clear the associated check box.
To save changes and exit Web Part Edit mode, you can click OK at the bottom of the Web Part tool pane. Or, to stop editing and view the workbook in Display mode, click Stop Editing.
This displays the Web Part that you configured to use its associated workbook on the page.

Now lets see how to Show Specific Work Sheets in an Excel workbook or even just a set of cells, lets do that in easy steps !!

  1. Open the Excel workbook you want to create views
  1. Click on the Excel office button >  Server  > Excel Services Options

  1. In the Show tab you can set, what all worksheets you can show on the workbook , here you can set it to the  appropriate sheets you want.

           4.  Now lets see how we can show specific cells/ranges,
Go to the Excel workbook select the range of cells/cell you want to display > Right click  > "Name a Range" > set the name > I set it to "PublicView"(no space)  and Click OK

             5.   That's it, now you have created a specific named item, now go back to the SharePoint site, edit the Excel WebAccess webpart and this time on the > Named Item  > type in "PublicView" > OK

           6.  The Webpart will start showing the specific table range on the page as below…

Hope this helped you…. Happy SharePointing !

No comments:

SharePoint Template Sites

  SharePoint look book Get inspired Discover the modern experiences you can build with SharePoint in Microsoft 365 https://lookbook.microsof...