Walkthrough: Creating an Excel Services SP 2010 Web Page

Completed Solution:

Overview

  • You will need credentials for setting up the unattended service account. I am logged in as the SharePoint Farm Administrator which is also the Administrator on my Virtual Machine.
  • You will add a specific document library to the trusted file locations, so if you have not created a SharePoint site with Document Library, you will need that before continuing.
  • Create a sample Excel Workbook with a few Named Ranges.

Step-by-Step

  1. Open the SharePoint 2010 Central Administration site.
  2. Click Manage Service Applications under the Application Management section.
  3. From the ribbon click New and select Excel Services Application.
  4. In the Name box type Excel Services. For Application Pool switch to Use Existing Application Pool and select SharePoint Web Services Default from the drop-down. Your screen should now look like Figure 5-6.
  5. Click OK.
  6. Click Secure Store Service where you will create the unattended service account.
  7. If necessary follow the instructions on the screen to create a key for the application.
  8. Click New from the ribbon.
  9. Enter ExcelServices in the Application ID box, Excel Services in the Display Name box, an administrator's e-mail address in the Contact e-mail box, select Group on the Target Application Type drop-down, and set the Target Application Page URL to None as shown in the Figure 5-7.

FIGURE 5-6

  1. Click Next.
  2. Verify the fields listed are Windows User Name and Windows Password. Click Next.
  3. Add a list of application administrators in the Target Application Administrators box.
  4. In the Members box add all users and user groups that should be mapped to the unattended credentials and click OK.
  5. Check the box next to the newly created entry and click the Set icon above Credentials on the ribbon.
  6. Enter the user name and password of the account you want to map to and click OK.
  7. Return to the Manage Service Applications page (available by clicking Application Management on the left pane).
  8. Select Excel Services from the list.
  9. Select Global Settings and scroll down to the bottom and locate the External Data section.
  10. In the Application ID box type ExcelServices exactly how it was entered in the Secure Store Service screen.

  1. When your screen looks like Figure 5-8 click OK.
  1. Click Trusted File Locations on the Application Management screen.
  2. Click Add Trusted File Location in the top left of the screen.
  3. Enter the URL to your document library and check the box next to Children Trusted as shown in Figure 5-9.

  1. Scroll down and switch Allow External Data to Trusted Data Connection Libraries And Embedded under External Data.
  2. Click OK.

Congrats! You have just allowed your Excel workbooks in the document library to use the resources of your newly created Excel Services application. If your library is set up to open documents in the web browser you can now click an Excel workbook and it will open in the browser.

  1. Next open up the SharePoint Page you want to insert your Excel Web Parts on
  2. Edit page, Insert Web Part, and Under the Business Connection, add Excel Web Access to the page.
  3. Edit the web part by clicking Manage and enter the URL for the document library and the Item name
  4. Close the Manage Web Part task pane and refresh your web page
  5. Your site should have the Named Range being displayed.