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 code pieces 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, in the end, 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 the ability to login as a member of the SharePoint Farm Administrators group. It is assumed that you are 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.
    • Previously, the 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 as a user who is 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

In order to run the script, some 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, respectively.

Line 8 – The SharePoint service should have a descriptive name. In this case, it will be called ExcelServices.

Line 9 – IIS will create a separate IIS Application Pool for the new SharePoint service. This service should also have 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 13Get-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 15Get-SPExcelServiceApplication retrieves any Excel Service Applications and pipes them into the Remove-SPServiceApplication cmdlet, which deletes them.

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

The Script – Add SharePoint Managed Account

In order 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 earlier 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 – Request the credentials (password) for the current user be typed in manually and create a Credential object.

Line 26New-SPManagedAccount creates a new SharePoint Managed Account for the current user.

The Script – Provision Excel Services

Create a new instance of Excel Services with a new IIS Application Pool , using the constants defined in the beginning of the script.

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 32New-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 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 just created by using Get-SPServiceInstance on the server with a piped | where clause for a service name that contains Excel. Then run Start-SPServiceInstance on the object you just 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 any that contain the string ‘Excel’
  • Start-SPServiceInstance – Starts those SharePoint services found in the previous step, that contain the string ‘Excel’

The Full Script

Here 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 of how to do so).

 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;

There is your complete PowerShell script to 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: