Excel Services Setup and Getting Started

As part of an internal prototype I was working on recently, I had to set up a machine for Excel Services – and do this repeatedly as my prototype messed up the installation in various ways. I found lots of MSDN documentation that covered all aspects of setting up the base server OS, setting up MOSS, and configuring MOSS for Excel Services in a multitude of possible configurations – but what I wanted was a nice concise checklist so that I could reliably setup an E/S box whenever I needed to. I couldn’t find one, so I’ve created my own.

This is a brief step-by-step checklist for setting up Excel Services on MOSS 2007 from scratch, and publishing workbooks. Later on, I’ll look at how to publish UDFs, and use Excel Web Access and the Excel Web Services APIs. A word of warning: I used these instructions to set up a machine specifically for testing, demoing and prototyping – and explicitly not for real production use, so some of the configuration choices I made might not be suitable for real production use.

1. Set up Windows Server 2008

1.1 Install and configure Windows Server 2008

a. Install Windows Server 2008, and all SPs and updates. This simple installation will be for a standalone server, not a farm.

b. In Server Manager | Customize this server | Enable Remote Desktop – this step is just so I could continue setting up the server remotely (kicking off various parts of the configuration while sitting in some tedious meeting, perhaps).

c. Add the Web Server role: Start | Administrative Tools | Server Manager | Roles | Add Role | Web Server (IIS)

                                                               i. Under Role Services, add Application Development, all Health & Diagnostics, all Security, and IIS 6.0 Management Compatibility (in addition to the IIS 7.0 support provided by default).

d. Add the Application Server role, which will install .NET Fx 3.0. (Alternatively, install .NET Fx 3.0 as a feature: in Server Manager, select Action | Add features | .NET Framework 3.0 Features.)

e. Turn off IE Enhanced Security Configuration: Server Manager | Security Information | Configure IE ESC – this step is just a convenience for my test machine, so that I can navigate freely without security prompts. Obviously, not recommended for production use.

f. Turn on network discovery and file sharing: Control Panel | Network & Sharing.

1.2 Install Microsoft Office SharePoint Server 2007

a. Run setup for MOSS 2007, and all SPs and updates. Configure the server as Basic.

b. At the end of setup, run the SharePoint Configuration Wizard. This configures MOSS, and does not require user input.

2. Configure MOSS to support Excel Services

2.1 Make sure Excel Services is running

a. This step should only be required for a farm setup – in a standalone setup, Excel Services should already be started, but it is included here for completeness.

b. Select Start | Administrative Tools | SharePoint 3.0 Central Administration. This runs the SharePoint Central Administration web application.

c. In Central Administration, select the Operations tab at the top.

d. On the Operations page, under Topology and Services, click on “Services on server”.

e. On the Services on Server page, ensure that Excel Calculation Services is started.

2.2 Create a new SharePoint site

a. In SharePoint Central Administration, select the Application Management tab at the top.

b. On the Application Management tab, under SharePoint Site Management, select Create site collection. This creates a new site.

c. On the Create Site Collection page, make sure the base web application is set to your server name without a port (the web app with a port is your server admin app).

d. Type in a suitable Title (eg “Contoso”).

e. Also type in a suitable URL. You’ll be given the start of the URL based on your SharePoint Server address (eg: https://MyServer). Make the new site a sub-site of the top-level “sites”, so that your full URL will be something like https://MyServer/sites/Contoso.

f. For Template Selection, you can use any of the provided templates. However, it tends to be best to start with the Document Workspace template (on the Collaboration tab), because that gives you the maximum features, page templates, web parts, etc.

g. Specify the Primary and Secondary Site Collection Administrators. You can simply set the Primary Administrator to the existing SharePoint Server administrator username. The names must be valid domain usernames – make sure to check the name, using the lookup button at the end of the field.

h. Click OK. This will cause SharePoint to create and provision the new site, and the site collection for the site. When it’s done, SharePoint will display a “Site Successfully Created” page.

i. Navigate to your new site, eg: https://MyServer/sites/Contoso. Drop down the Site Actions menu, and select Site Settings. Under Site Administration, click Site features. On the Site features page, locate Office SharePoint Server Enterprise Site features (which includes Excel Services) and make sure it is activated.

2.3 Create a Document Library to hold the workbooks

a. Navigate to your new site, eg: https://MyServer/sites/Contoso.

b. From your new site page, drop down the Actions menu, and select the Create command. Note that if you did not base your site on the Document Workspace template, the command navigation to create a document library may be slightly different.

c. From the Create page, under Libraries, select Document Library.

d. On the New page, type in a name for the library, eg “Spreadsheets”.

e. Change the default document template to Excel Spreadsheet. This is only required if you’re going to allow users to create new documents within the library – as opposed to publishing documents to the library. For my testing, I was only going to publish documents to the library, not create new ones from there, so this step was really redundant in my case.

f. Click Create. This creates the new document library.

2.4 Add a trusted file location for uploading workbooks to Excel Services

a. In Central Administration, select the Application Management tab at the top.

b. Under Office SharePoint Server Shared Services, click on “Create or configure this farm’s shared services”.

c. You can use the default SSP, probably named “SharedServices1”.

d. Click the SSP link (either in the left-hand nav-pane, or in the drop-down on the page) to go to the administration page for that SSP.

e. On the Shared Services Administration page, under Excel Services Settings, select the Trusted file locations link.

f. On the Trusted File Locations page, click the Add Trusted File Location link.

g. On the Add/Edit Trusted File Location page, type in the address – use the URL of the document library you created for this purpose, eg: https://MyServer/sites/Contoso/Spreadsheets.

h. Make sure the location type is a Windows SharePoint Services location.

i. If you want to trust sub-folders of this location, check the Children Trusted option.

j. If you want to trust workbooks with with links to external data, you can check one of these options.

k. If you want to allow User-Defined Functions, check the User-Defined Functions Allowed option at the bottom of the page.

l. Click OK.

2.5 Configure Single Sign-On for Excel Services

a. Open the Services MMC: Start menu | Administrative Tools | Services.

b. Right-click on Microsoft Single Sign-on Service, and select Properties. In the Properties dialog, on the General tab, change the Startup Type to Automatic. On the Log On tab, select the Log on as This account option, and type in the domain\username and password that you used to install MOSS 2007. Click OK.

c. Start the service by clicking Start in the Services dialog, then close the Services dialog.

d. Open the SharePoint Central Administration web app, and select the Operations tab. Under Security Configuration, click Manage settings for single sign-on. In the Manage Settings for Single Sign-On page, click Manage server settings.

e. In the Manage Server Settings for Single Sign-On page, type in the domain\username for the Single Sign-On Administrator Account – this should be the same user that the SSO service is running as (and the same as the admin user account for MOSS). Enter the same domain\username for the Enterprise Application Definition Administrator Account. Leave the other settings as their defaults, and click OK.

f. Navigate back to the Manage Settings for Single Sign-On page, and click Manage settings for enterprise application definitions. Then click New Item.

g. In the Create Enterprise Application Definition page, type in a suitable Display name and Application name. You can set these both to the same value, for example “SSO”. Type in the email address that users can contact for this application.

h. Set the Account type to Group, and the Authentication type to Windows authentication. Set the Logon Account Information Display Name fields to suitable values – the defaults of “Username” and “Password” are fine. The username field should not be masked, but the password field should be masked. Click OK.

i. Navigate back to the Manage Settings for Single Sign-On page, and click Manage Account Information for enterprise application definitions.

j. On the Manage Account Information for an Enterprise Application Definition page, make sure the SSO application definition is selected in the drop-down listbox. Then enter a suitable domain\group name as the group account name, eg: “MYDOMAIN\Domain Users”, and click Set.

k. Enter the username and password of a user on your domain that has access to the data sources that you will be using, and click OK.

l. This puts you back on the previous Manage Account Information for an Enterprise Application Definition page. Click Done.

2.6 Configure Excel Services timeouts

a. The various timeout settings on Excel Services can be tuned for optimum performance and reliability. For testing or demo purposes, you might want to disable timeouts altogether.

b. To do this, open the Shared Services Admin page for your SSP (probably SharedServices1). Under Excel Services Settings, click Trusted File Locations. From the list of trusted locations, click the one you have set up for publishing worksheets, so that you can edit the properties.

c. On the Excel Services Edit Trusted File Location page, under Session Management, set the Session Timeout to -1. The default is 300, and setting it to -1 means that sessions will not timeout.

d. Under Session Management, set the Short Session Timeout to -1 to prevent sessions timing out (default is 75 seconds) - this is the maximum allowable time between opening the session and the first user interaction. Also set the Maximum Request Duration to -1 (default is 300) - this is the maximum duration of a single request in a session.

e. Under Calculation Behavior, set the Volatile Function Cache Lifetime to 0 (zero), to force Excel to recalculate the workbook for each new session. Note this will not result in recalculations within the current session. The only way to force recalculation during a session is to change input parameters or (using the Excel Services web service) by calling the CalculateWorkbook API. Refreshing the page in the browser will not cause a recalculation. Setting the VFCL to zero simply sets a zero initial cache for each session.

2.7 Configure the Unified Logging Service

a. Out of the box, MOSS uses ULS to sent trace information to the system event logs, performance counters, and to the MOSS-specific ULS logs. For testing purposes, you probably want to maximize the log information.

b. To do this, go to SharePoint Central Admininistration, click the Operations tab, find the Logging and Reporting section, and click Diagnostic Logging.

c. In the Event Throttling section, select the Category dropdown and, for each of the eight Excel options do the following:

                                                         i. Select the item (eg, Excel Calculation Services).

                                                       ii. In the Least Critical Event to Report to the Event Log dropdown, leave the default setting (Error).

                                                      iii. In the Least Critical Event to Report to the Trace Log dropdown, select Verbose.

                                                     iv. Click OK.

                                                       v. Go back to step (i).

d. By default, the logs are written to %ProgramFiles%\Common Files\Microsoft Shared\Web Server Extensions\12\LOGS.

e. The log files are tab-delimited text files, which you can view in Notepad or Excel.

3. Create and publish a workbook to Excel Services

3.1 Create an Excel workbook (ContosoSales.xlsx)

a. On the client machine, open the regular Excel desktop application, and create a new workbook with 2 worksheets.

b. In Sheet1, set up a simple set of cell values and labels. Define named ranges for the cells with the money values. For example, select the cell containing the Sales value (cell B1 in the example below, not the label). From the Formulas tab, select Define Name. By default, Excel will suggest the label to the left (that is, “Sales”) as the name of the named range. Accept this default. Repeat for the money cell for Costs (B2).

c. On Sheet2, in any cell, enter a suitable formula for the Profit value, eg: =(Sales-Costs)/Sales. When we publish this workbook to Excel Services, we’re going to hide Sheet2.

d. Back in Sheet1, in the value cell for Profit (cell B3 in the example above), enter a formula to reference the formula in Sheet2, eg: =Sheet2!B2 (where B2 is the cell containing the profit formula in Sheet2), and format it as a percentage.

3.2 Publish the workbook to Excel Services

a. In Excel, click the Office button, and select Publish, Excel Services. This displays a modified Save As dialog.

b. In the Save As dialog, use the URL of the Spreadsheets document library you created on SharePoint Server as the path for your new workbook, eg: https://MyServer/sites/Contoso/Spreadsheets/ContosoSales.xlsx.

c. Then click the Excel Services Options button.

d. In the Excel Services Options dialog, on the Show tab, specify that you only want to publish Sheet1, not the entire workbook.

e. On the Parameters tab, click Add. In the Add Parameters dialog, select both the Sales and Costs named ranges as parameters. This will allow the browser client to feed data into the worksheet for these parameters.

f. Click Save. This will publish the workbook to Excel Services, and open a browser window with a rendering of the published spreadsheet. Note that you can’t edit the sheet directly, but you can enter values for the Costs and Sales parameters in the task pane, and apply them to update the sheet. This will cause the sheet to be recalculated on the server, and re-rendered on the client. Note also that any changes you make are not persisted to the workbook on the server.

3.3 Ensure users open the spreadsheet in the browser

a. Back on the SharePoint Server, if you navigate to the Spreadsheets document library, you should see your new workbook listed there.

b. By default, if a user navigates to this page, and clicks on a workbook, the workbook will open on the client in client Excel. To ensure that it opens in the client’s browser instead, drop down the Settings list, and select Document Library Settings.

c. Under General Settings, click Advanced settings.

d. In the Browser-enabled Documents section, select the Display as a Web page option, and click OK.

That’s it! I’ve basically collected together the necessary installation and configuration steps from a range of published sources, and distilled them down to a simple checklist. Next time I get a few moments, I’ll add to this checklist with the steps for creating and publishing UDFs, and using the Excel Web Services APIs.