Excel Service Configuration & OData access

Verify Application Pool.
Get-SPServiceApplicationPool | Select Name
Get the existing Application Pool.
$SvcAppPool = Get-SPServiceApplicationPool "SvcApp_SPServiceApplicationPool_01"
If you are creating a new application pool:
Determine which service account you will use to run the application pool that will be assigned to the.  If you need to, create and register a service account to run the Subscription Settings Service Application.
Create the Service Application Pool and register it to the Service Account.
$SvcAppPool = New-SPServiceApplicationPool -Name "SvcApp_SPServiceApplicationPool_01"$AppPool = New-SPServiceApplicationPool -Account (Get-SPManagedAccount Domain\SvcAccount)
Verify Application Pool.
Get-SPServiceApplicationPool | Select Name
Get the existing Application Pool.
$SvcAppPool = Get-SPServiceApplicationPool "SvcApp_SPServiceApplicationPool_01"
Open the SharePoint 2013 Management Shell as Administrator.
Create the Service Application and assign it to the Application Pool.
$SvcApp = New-SPExcelServiceApplication –ApplicationPool $SvcAppPool -Name "SvcApp_New-SPExcelServiceApplication_01"
Verify Service Application.
Get-SPServiceApplication | Select Name
Verify Service Application Proxy
Get-SPServiceApplicationProxy | Select Name

Fixing – The workbook cannot be opened

When opening an Excel workbook (.xlsx) in the browser the error "The workbook cannot be opened" is thrown:

This is caused by the account running Excel Services not having proper permissions to the Content Database.  When you upload an Excel workbook to a SharePoint library, the workbook is stored as blobs in the Content Database.  When you choose to open this in Excel Services, the account running Excel Services needs to retrieve and reassemble the workbook.  If the account running Excel Services lacks proper permissions, "The workbook cannot be opened".

We have a KB on how to resolve this issue:

The Excel Services Application for SharePoint 2010 does not load or display workbooks


In this blog, I will step you through this KB; where to collect data and explain what the two below SharePoint Management Shell Commands do:

$w = Get-SPWebApplication –Identity <URL of the Web application>
$w.GrantAccessToProcessIdentity("<insert service account>")


$w = Get-SPWebApplication –Identity http://win12sp13:7864

You need to find the <URL of the Web application>.  To do this:

1. Browse to the report library where the failing workbook is located and make note of the URL.

2. Central Administration > Application Management > Manage web applications compare the URL to the list of Web Apps.  Find the correct Web App URL and add it to “URL of the web application”.

You now have the first command:

$w = Get-SPWebApplication –Identity http://tschauer

3. Central Administration > Security > Configure Service Accounts > from the first dropdown, select the application pool running "Excel Services Application" and add that account where is says “<insert service account>”.

You now have the second command:


So now you can run these commands by following the below steps:

1. Click Start, click All Programs.
2. Click Microsoft SharePoint 2010/2013 Products.
3. Click SharePoint 2010/2013 Management Shell.
4. At the Windows PowerShell command prompt (PS C:\>), type the following command, and then press ENTER:

$w = Get-SPWebApplication –Identity http://tschauer

When you run these commands you are:

1. Adding the account running Excel Services as a User to the Content Database (Security > Users).
2. Giving the account running Excel Services the SPDataAccess Membership (Account > Membership)
3. Giving the account running Excel Services Full Control to the Web Application (Via "Policy for Web Application")

Consuming OData feeds from Excel Services 2013



Service Host – http://win12sp13:7864/_vti_bin/ExcelRest.aspx

Library name – ExcelStore

ExcelSheet (which contains a table data, creating a data is VERY IMPORTANT) – UnitRefs.xlsx

oData – Call indicate that its a OData call.




You need to fully construct the Url for oData service call, just call up to the point of:

http://win12sp13:7864/_vti_bin/ExcelRest.aspx/ExcelStore/UnitRefs.xlsx/OData, then within the connection import wizard select the excel table to select.