Provision Excel Services in Sharepoint Server with Powershell

       9 minute read

Before reading…

  • Target Audience: This article targets IT Pros, DevOps and System Administrators working in a SharePoint Server environment.
  • Scenario: While providing a full PowerShell script to provision (setup) Excel Services, it also explains each section of the process, line by line, showing how the pieces of code fit together.
  • Sources: I wrote 100% of the content, including the PowerShell code, without an editor providing input. The information was derived from my experience as a SDET (software development engineer in test) with Excel Services at Microsoft.

…and here’s the sample.

SharePoint administrators are able to setup, deploy and configure Excel Services running on SharePoint Server 2010 using PowerShell. In this article, you’ll see how to provision Excel Services using PowerShell cmdlets (pronounced “command-lets”). Each step will be explained and you’ll have a complete script to setup Excel Services in SharePoint.

The purpose of this PowerShell script will be to:

  1. Remove any existing occurrences of Excel Services.
  2. Remove the existing Internet Information Server (IIS) Application Pool used by Excel Services.
  3. Provision (setup) Excel Services with the domain user account running the script.
  4. Start Excel Services.

Contents

  1. Prerequisites
  2. Getting Ready
  3. The Script – Set Constants
  4. The Script – Initial Clean Up
  5. The Script – Add SharePoint Managed Account
  6. The Script – Provision Excel Services
  7. The Script – Run Excel Services
  8. The Full Script
  9. Summary
  10. Resources

Prerequisites

Before we proceed, you must have a SharePoint Server 2010 farm and be a member of the SharePoint Farm Administrators group. You should be familiar with the following technologies, at least to a small extent.

  • Excel Services – Allows you to view and edit Excel workbooks in your browser that are located in SharePoint document libraries.
  • SharePoint Server 2010 – The server platform required by Excel Services.
    • The previous version of SharePoint (2007) that ran Excel Services was called Microsoft Office SharePoint Server (MOSS).
  • Internet Information Systems (IIS) – Web server and platform that runs on Windows Server 2008.
  • PowerShell – A scripting language and platform used by Windows operating systems.

Getting Ready

  1. Log in to your SharePoint server. You must be a member of the Farm Administrators group. If you have a multiple-server farm configured, any server in the farm will work.

  2. Open PowerShell with the SharePoint snap-in.

    a. Start –> All Programs –> Microsoft SharePoint 2010 Products –> SharePoint 2010 Management Shell

    b. Alternatively, you can open a regular PowerShell prompt and add the Microsoft.SharePoint.PowerShell snap-in with the following command:

    Add-PSSnapin Microsoft.SharePoint.PowerShell

The Script – Set Constants

To run the script required variables need to be set.

6 ##### Set constants
7 $ServiceAccount = $env:UserDomain + "\" + $env:UserName;
8 $ServiceName="ExcelServices";
9 $IISAppPool="ExcelServicesAppPool";

Line 7 – In this sample, the script is assumed to be running in a Windows Active Directory environment, which requires user authentication. The credentials of the user logged into the server, running the PowerShell cmdlets, will be used to authenticate when Excel Services is running. The script reads the environment variables for the user’s domain and name, $env:UserDomain and $env:UserName.

Line 8 – Give the SharePoint service a descriptive name, such as ExcelServices.

Line 9 – IIS will create a separate IIS Application Pool for the new SharePoint service. Give this service a descriptive name, such as ExcelServicesAppPool, instead of the name SharePoint provides by default.

The Script – Initial Clean Up

Before provisioning a new service, it’s best to verify that the environment is clean. This allows you to proceed knowing there aren’t any current instances of Excel Services, which might create conflicts with your script.

11 ##### Clean up
12 write-host " ---- Removing all instances of Excel Services Service Application Proxies" -Fore Red;
13 Get-SPServiceApplicationProxy | where { $_.TypeName.Contains('Excel Services') } -ErrorAction SilentlyContinue | Remove-SPServiceApplicationProxy -confirm:$false;
14 write-host " ---- Removing all instances of Excel Services Service Applications" -Fore Red;
15 Get-SPExcelServiceApplication | Remove-SPServiceApplication -confirm:$false -ErrorAction SilentlyContinue;
16 write-host " ---- Removing IIS Application pool from SP, if it exists" -Fore Red;
17 Remove-SPServiceApplicationPool -Identity $IISAppPool -ErrorAction SilentlyContinue;

Line 13 – Get-SPServiceApplicationProxy retrieves any SharePoint Service Application Proxies with Excel Services in the name. These are piped | into the Remove-SPServiceApplicationProxy cmdlet, which deletes them.

  • Service Application Proxies are the virtual link between Web Applications, such as Excel Services, and the SharePoint service. They understand the load-balancing scheme for a service, communicate to service machine instance(s) and enable inter-farm services.

Line 15 – Get-SPExcelServiceApplication retrieves any Excel Service Applications and pipes them into the Remove-SPServiceApplication cmdlet, which deletes them.

Line 17 – Remove-SPServiceApplicationPool deletes the SharePoint Service Application Pool and IIS Application Pool named ExcelServicesAppPool. That value is contained in $IISAppPool, which is defined on line 9.

The Script – Add SharePoint Managed Account

For an Active Directory user account to manage SharePoint services and web applications, it must be registered with SharePoint as a Managed Account. In this example, you’ll be using the account you’re logged in as to run Excel Services.

Note: You can use a different Active Directory account to run Excel Services, but how to do so is not covered in this guide.

19 ##### Add user account as a SPManagedAccount, if it isn't already, so we can use it to run the application pool
20 $ManagedAccount = Get-SPManagedAccount | where { $_.UserName.Contains($ServiceAccount) };
21 write-host "Service account (currently logged in as) = $ServiceAccount";
22 if (($ManagedAccount -eq $null) -or ($ManagedAccount.UserName.ToLower() -ne $ServiceAccount.ToLower()))
23 {
24    write-host "We will use this account to run the Excel Services application!";
25    $credAccount = $host.ui.PromptForCredential("Managed Account", "Enter Account Credentials:", "", "NetBiosUserName" );
26    New-SPManagedAccount -Credential $credAccount;
27 }

Line 20 – Determine if the current logged in user, as defined in line 7, is registered in SharePoint as a Managed Account. This is done by searching for the user name with Get-SPManagedAccount and creating a SPManagedAccount object variable called $ManagedAccount.

Line 22 – If the Managed Account is not found (meaning that $ManagedAccount is null) OR running under a different user account (meaning that it’s different than $ServiceAccount), then add the current user as a SharePoint Managed Account (in lines 25 and 26). Otherwise, the current user is already registered as a Managed Account with SharePoint and does not need to be added again.

Line 25 – Prompt the current user to enter their password in order to create a Credential Object.

Line 26 – New-SPManagedAccount creates a new SharePoint Managed Account with the current user’s credentials.

The Script – Provision Excel Services

Using the constant variables defined in the beginning of the script, create a new instance of Excel Services with a new IIS Application Pool.

29 ##### Provision Excel Services using a new IIS application pool
30 write-host "Creating new Excel Services Application: $ExcelServices"
31 write-host "Using a new IIS application pool: $IISAppPool"
32 New-SPServiceApplicationPool -Name $IISAppPool -Account $ServiceAccount | New-SPExcelServiceApplication -Name $ServiceName -Default | Out-Null;

Line 32 – New-SPServiceApplicationPool creates a new SharePoint Service Application Pool in IIS and New-SPExcelServiceApplication creates the Excel Service in SharePoint.

The Script – Run Excel Services

Now that Excel Service exists on the SharePoint server, it still needs to be started so it can process the requests to view and edit Excel workbooks.

34 ##### Start Excel Services:
35 write-host "Starting Excel Services service instance."
36 Get-SPServiceInstance -Server $env:computername | where {$_.TypeName.Contains('Excel')} | Start-SPServiceInstance;

Line 36 – Find the SharePoint service that was created by using Get-SPServiceInstance with a piped | where clause for a service name that contains Excel. Then run Start-SPServiceInstance on the object found to start the service running.

Note: In PowerShell, the pipe | carries the results from the first cmdlet to the next one, and so on. Breaking down the cmdlets from line 36, they translate as follows:

  • Get-SPServiceInstance -Server $env:computername – Retrieves all SharePoint services running on the local server
  • where {$_.TypeName.Contains(‘Excel’)} – Reduces the list of services to those that contain the string ‘Excel’
  • Start-SPServiceInstance – Starts the SharePoint services containing the string ‘Excel’ in the name (found in the previous step)

The Full Script

The following is the full script. To run this, copy/paste the contents below into a text editor and save it as a .ps1 file (example: ProvisionExcelServices.ps1).

Note: The script must be run from the SharePoint 2010 Management Shell or a PowerShell prompt with the Microsoft.SharePoint.PowerShell add-in loaded (see Getting Ready for details).

 1 #~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 2 # Provisions excel services with a new IIS application pool and starts the service instance.
 3 # NOTE: This will delete any existing instances of Excel before provisioning.
 4 #~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 5 write-host "----- Starting Provision Excel Services -----" -Fore Green;
 6 ##### Set constants
 7 $ServiceAccount = $env:UserDomain + "\" + $env:UserName;
 8 $ServiceName="ExcelServices";
 9 $IISAppPool="ExcelServicesAppPool";
10  
11 ##### Clean up
12 write-host " ---- Removing all instances of Excel Services Service Application Proxies" -Fore Red;
13 Get-SPServiceApplicationProxy | where { $_.TypeName.Contains('Excel Services') } -ErrorAction SilentlyContinue | Remove-SPServiceApplicationProxy -confirm:$false;
14 write-host " ---- Removing all instances of Excel Services Service Applications" -Fore Red;
15 Get-SPExcelServiceApplication | Remove-SPServiceApplication -confirm:$false -ErrorAction SilentlyContinue;
16 write-host " ---- Removing IIS Application pool from SP, if it exists" -Fore Red;
17 Remove-SPServiceApplicationPool -Identity $IISAppPool -ErrorAction SilentlyContinue;
18  
19 ##### Add user account as a SPManagedAccount, if it isn't already, so we can use it to run the application pool
20 $ManagedAccount = Get-SPManagedAccount | where { $_.UserName.Contains($ServiceAccount) };
21 write-host "Service account (currently logged in as) = $ServiceAccount";
22 if (($ManagedAccount -eq $null) -or ($ManagedAccount.UserName.ToLower() -ne $ServiceAccount.ToLower()))
23 {
24     write-host "We will use this account to run the Excel Services application!";
25     $credAccount = $host.ui.PromptForCredential("Managed Account", "Enter Account Credentials:", "", "NetBiosUserName" );
26     New-SPManagedAccount -Credential $credAccount;
27 }
28      
29 ##### Provision Excel Services using a new IIS application pool
30 write-host "Creating new Excel Services Application: $ExcelServices"
31 write-host "Using a new IIS application pool: $IISAppPool"
32 New-SPServiceApplicationPool -Name $IISAppPool -Account $ServiceAccount | New-SPExcelServiceApplication -Name $ServiceName -Default | Out-Null;
33  
34 ##### Start Excel Services:
35 write-host "Starting Excel Services service instance."
36 Get-SPServiceInstance -Server $env:computername | where {$_.TypeName.Contains('Excel')} | Start-SPServiceInstance;

This PowerShell script will enable Excel Services on a SharePoint Server 2010 farm. Please note the requirements listed earlier in Getting Ready.

Summary

Through the course of this document, you’ve learned how to:

  • Remove a SharePoint service application via PowerShell.
  • Create a new Excel Services SharePoint service application via PowerShell.
  • Start a SharePoint service application via PowerShell.
  • Register an Active Directory user as a SharePoint Managed Account.
  • Understand piping and using the pipe character | in PowerShell.
  • Automate the provisioning of Excel Services via PowerShell, increasing efficiency and repeatability.

Resources

Updated: