devxlogo

Share Your Spreadsheets with Excel Services in SharePoint 2007

Share Your Spreadsheets with Excel Services in SharePoint 2007

common scenario in many companies, particularly those that create financial or other analytical applications is one where domain experts (for example financial analysts) model calculations they would like the application to perform using Excel. This model forms a proof-of-concept, as well as a sandbox in which domain experts ensure that their calculations work correctly. The model may also become the basis for a functional specification for programmers, who can take the spreadsheet and implement the formulas it contains in a high level language such as C# or Java in the main application.

In many cases this isn’t really necessary?a dedicated server application to crunch the numbers may not be the best solution?but distributing and securing a spreadsheet, as well as managing its deployment and versioning causes so much overhead that it is often worth spending time and effort to build a hosted application that performs the same calculation.

However with the advent of SharePoint 2007, which facilitates communication between its users, and with Excel Services, application developers can approach this scenario in a new and different way. Excel Services provides a server-side paradigm for Excel in which spreadsheets published to SharePoint can calculate and render on the server, letting users pull results down to their browsers. In this new scenario, analysts can publish their worksheets to SharePoint, and allow Excel Services to kick in, meaning that anybody who browses to the site containing the spreadsheet can access the full functionality of the sheet?whether or not they have Excel installed. In this scenario, the formulae that analysts enter become the code that everyone will run. You don’t need a developer to reimplement the formulae or create a new application.

Creating and Configuring SharePoint for Excel Services
This article assumes that you have SharePoint 2007 server installed. The software is currently in beta, and you can download it from MSDN. When the installation completes, 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.

Setting Use this value
Title “DevX1”
URL Name “DevX1”
URL Path “sites”
Administrator
Template “Blank Site”

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 2. Your New SharePoint Site: Here’s how the new blank SharePoint site looks in a browser.
?
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. 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.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist