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 databaseyou'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 locationthis 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.