Search This Blog

Thursday, November 11, 2010

Creating and Configuring SharePoint for Excel Services

This article shows

  1. Creating and Configuring SharePoint for Excel Services.
  2. Running Excel Services
  3. Creating and Publishing a Spreadsheet
  4. Setting Parameters

 you'll see an icon under "Administrative Tools" called "SharePoint 3.0 Central Administration." Use this to open the SharePoint Administrator. From the Administrator you can use the "Create Site Collection" option on the "Application Management" tab to create a new SharePoint site.

Figure 1. Creating a New SharePoint Site: From the SharePoint Administrator window, select the "Blank Template" option to create a new blank SharePoint site. Table 1 shows the settings you'll need.

Give the new site a name, and use the "Blank Site" template to keep things simple. See Figure 1 for details.

Table 1. Blank SharePoint Site Settings: The table shows
the settings you'll need to create a blank SharePoint site to host
the sample Excel Services spreadsheet.

SettingUse this value
URL Name"DevX1"
URL Path"sites"
Administrator"<User name of local administrator>"
Template"Blank Site"

Figure 2. Your New SharePoint Site: Here's how the new blank SharePoint site looks in a browser.

When you've set all the values as shown in Table 1, click "OK" to create your site. If you browse to the blank SharePoint site that you just created it will look like Figure 2.

Figure 3. Creating New Items in your SharePoint Site: The Create Page screen provides several categories containing links for creating various item types.

Figure 4

Figure 4. Your Document Library in SharePoint: The new "Analytics" document library can contain a set of Excel spreadsheets.
At the top right of the screen you can see the "Site Actions" dropdown list. Select "Create" from the dropdown and you'll be taken to a screen containing several categories of options for creating various types of items (see Figure 3).

Select "Document Library" from this screen to create a new Document Library. On the Document Library screen, give the new document library a name and a Document Template. Choose any name you like (the examples in this article use "Analytics," and select "Microsoft Office Excel Worksheet" as the document template).

You'll now have a SharePoint site containing a document library of Excel spreadsheets (see Figure 4).

Running Excel Services

Before Excel Services will work with this repository, you must make sure that the services are up and running correctly. To do this, return to the Central Administration screen, and select the Operations tab. From here, select the "Services on Server" screen. You'll see a list of services at the bottom of the screen. Make sure that "Excel Calculation Services" is started. If it isn't, use the link beside it to start up the service.

Additionally, you'll have to configure a trusted file location for your Excel workbooks. You do this by returning to Central Administration, and selecting the "Create or configure this farm's shared services" link. You'll see the "SharedServices1 (Default)" entry on this screen. Drop this down, by clicking the arrow to the right of it, and select the "Open Shared Services Admin Site" option. This opens the administration screen for these shared services. On the right side of this screen you'll see the "Trusted File Locations" link which you can use to set up where you can store the Excel files securely. Select "Add Trusted File Location" and you'll see the screen shown in Figure 5

Figure 5. Adding a Trusted File Location: This screen lets you add trusted file locations to your SharePoint site.

Figure 6. Trusted File Location: From the Shared Services Administration screen, you can see a list of trusted file locations.

Use the URL address of the document library that you set up earlier (for example:http://win2k3sp:17964/sites/DevX1/Analytics) and make sure to specify the Location Type as "Windows SharePoint Services." Click "OK" and the SharePoint will create the trusted file location as shown in Figure 6.

Creating and Publishing a Spreadsheet

Now you need a spreadsheet to use for shared calculations. For this example, you'll build a very simple example spreadsheet that serves to demonstrate how the calculation services work. The spreadsheet performs a simple analytics calculation, called a "P/E Ration," which simply divides the price of a stock by its earnings. It's a useful analytic that tells you how many years you'll need to own a stock before it pays for itself in earnings. So, for example, if your stock costs $100 and earns $10 a year, it will take 10 years to pay for itself in earnings. This ratio (or analytic) is usually seen as a great indicator of the value of a stock. In a real-world scenario, the user will provide a stock ticker, and the application would retrieve the values for price and earnings from a database. The spreadsheet could then divide price by earnings using a formula. For simplicity, this example doesn't use a database—you'll simply provide the spreadsheet with the price and earnings values. The P/E will be a simple Excel formula that divides the named ranges for the Price and Earnings values.

I've added a chart to show the relative values of Price and Earnings, and you can see the spreadsheet in Figure 7.

Figure 7. Sample Spreadsheet for Excel Calculation Services: This simple spreadsheet derives the P/E ratio by dividing Price by Earnings, and displaying the result.
Figure 8. Publishing to Excel Services: Publish your spreadsheet to SharePoint using the Excel Services option on the "Publish" menu.

 When you've completed the spreadsheet, publish it to SharePoint using the Excel Services option on the "Publish" menu (see Figure 8).

When you select this, the "Save As" dialog will appear. You can use this dialog to specify the save location—this should be the location that you configured as a Trusted Location earlier. However, before you do this, you should select the "Excel Services Options" button at the bottom of the dialog. You use these options to specify the items within the sheet that you want to have available to browsers. This is important, as you may have some proprietary stuff that you do not want to publish. You can simply uncheck that from this dialog and it will not be published to SharePoint, and thus kept private.

Setting Parameters

The "Parameters" tab (see Figure 9) is incredibly useful. It contains the named ranges available on your sheet, and (if necessary) allows you to turn these into parameters that the user can use to pass data into your sheet. For example, in this sample application, if you want the user to input the Stock Ticker, you add this range as a parameter. Because this example doesn't connect to a back-end database to get the earnings and price values for the stock, add them as parameters as well.

When you serve the sheet from SharePoint into the browser, you'll see a generated UI that allows you to pass these parameters to the spreadsheet.

Figure 9. Entering Parameters in Excel Services: In this dialog, you specify the parameters that you want users to pass into the spreadsheet.
Figure 10. Publishing Your Spreadsheet to the Document Library: Specify the path to the SharePoint site to which you want to publish, and save the spreadsheet into the library.

You're now ready to publish. For the file name, specify the path to the SharePoint site that you set up earlier. The dialog will refresh and show you the document library. You can then save the document into the library (see Figure 10).

SharePoint publishes your workbook into the document library. If you browse to this document from IE, you'll see an Excel-like generated interface. Remember, your clients do not need to have Excel installed to use the application that you've just built in Excel—that's part of the magic of using SharePoint and Excel services. You can see this in Figure 11. Note the UI on the right hand side of the screen that allows users to pass in parameters.

Figure 11. Viewing the Spreadsheet: Excel Services provides a generated Excel-like UI, so clients don't have to have Excel installed to use your spreadsheet.
Figure 12. Using Firefox with Excel Services: Here's the sample spreadsheet running in Firefox.

The generated UI isn't IE-specific. You can also use Firefox to view and render the results, as shown in Figure 12.

In this article you saw the procedures to configure and use Excel Services with SharePoint 2007. This is a terrific and incredibly useful service that allows you to publish your Excel documents to a server library and let users run them, taking advantage of the Excel's calculation power and formulae. This is important on a number of fronts. First, it prevents version drift of documents—a common problem for companies that use a lot of spreadsheets. Having a centralized repository means that all users can share the same version. Additionally, because the calculations can now run on the server, many users don't even need to have Excel installed! They just need to be able to browse to the SharePoint repository and render the sheet within their browsers. The user who specifies the calculations also becomes the developer of the functionality available to the end users. So, instead of having a developer translate Excel spreadsheets and formulae into a language suitable for Web applications and building a custom UI, your formula experts can simply publish their spreadsheets to Excel and have end users consume them directly.


  1. Please let me know if you're looking for a author for your weblog. You have some really great articles and I think I would be a good asset. If you ever want to take some of the load off, I'd really like to write some
    content for your blog in exchange for a link back to mine.

    Please blast me an email if interested. Cheers!

    Feel free to surf to my homepage: cash advantage
    My web page - top article

  2. Appreciating the commitment you put into your blog and in depth
    information you provide. It's awesome to come across a blog every once in a while that isn't the same
    outdated rehashed material. Great read! I've saved your site and I'm adding your RSS feeds to my Google account.

    Here is my site; ingredients of quantrim

  3. Accordingly, a glamorous bouquet will have flowers like roses and hydrangeas in whites and
    creams. The material used should be a combination of proper fabric as well
    as the typical straw and coconut theme that is used with traditional hula girls in Hawaii.
    So, you want a beautiful wedding, but you want to make sure that you do not end up
    starting out your married life with a great deal of debt.

    Also visit my homepage ... {romantic modern wedding ideas|classic modern wedding ideas|mid century modern wedding ideas|ultra modern wedding ideas|fall modern wedding ideas|cool modern wedding ideas|creative modern wedding ideas|vintage modern wedding ideas|unique and modern wedding ideas|Ultra Modern Wedding Ideas Photograph | Paradise Garden