Get-SPExcelBlockedFileType
Example
[image]

Returns a file type or list of file types that are prevented from loading.

Get-SPExcelBlockedFileType [-Identity <SPExcelBlockedFileTypePipeBind>] -ExcelServiceApplication <SPExcelServiceApplicationPipeBind> [-AssignmentCollection <SPAssignmentCollection>]

Parameters

Parameter Required Type Description

Identity

Optional

Microsoft.Office.Excel.Server.Cmdlet.SPExcelBlockedFileTypePipeBind

Specifies the BlockedFileType object to return. If the Identity parameter is not specified, this cmdlet returns the list of file extensions that are currently prevented from loading on Excel Services.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of a file type (for example, FileType1); one of the following file types: XLSX, XLSB, XLSM; or an instance of a valid SPExcelBlockedFileType object.

ExcelServiceApplication

Yes

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the Excel Services application that contains the SPBlockedExcelFileType list object.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Detailed Description

Use the Get-SPExcelBlockedFileType cmdlet to read a file type or a list of file types that cannot be loaded by using Excel Services. Excel Services will not load any file types that correspond to a file type in this list.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1----------------
Copy Code
Get-SPExcelServiceApplication -Identity "MyExcelService" | Get-SPExcelBlockedExcelFileType | format-table

This example returns a list of file extensions that currently cannot be loaded by using Excel Services, and then formats the result in a table.
Click here to submit feedback on this topic.


Get-SPExcelDataConnectionLibrary
Example
[image]

Returns a trusted data connection library or a list of trusted data connection libraries.

Get-SPExcelDataConnectionLibrary [-Identity <SPExcelDCLPipeBind>] -ExcelServiceApplication <SPExcelServiceApplicationPipeBind> [-AssignmentCollection <SPAssignmentCollection>]

Parameters

Parameter Required Type Description

Identity

Optional

Microsoft.Office.Excel.Server.Cmdlet.SPExcelDCLPipeBind

Specifies the DataConnectionLibrary object to return. If the identity parameter is not specified, this cmdlet returns all the data connection libraries with connection files that can be loaded by Excel Services.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of a data connection library (for example, DataConnectionLib1); a valid URL, in the form http://server_name; or an instance of a valid SPExcelDCL object.

ExcelServiceApplication

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the Excel Service application that contains the SPExcelDataConnectionLibrary list object.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Detailed Description

Use the Get-SPExcelDataConnectionLibrary cmdlet to get a library of data connection files that Excel Services and Excel client use to connect to an external data source. Excel Services loads data connection files only if they are stored in a data connection library that is in the list of trusted data connection libraries.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1--------------
Copy Code
Get-SPExcelserviceapplication -identity "MyExcelService" | Get-SPExcelDataConnectionLibrary

This example displays all the Data Connection Libraries (DCLs) that connection files can be loaded by an Excel Services application.

--------------EXAMPLE 2--------------
Copy Code
Get-SPExcelserviceapplication -identity "MyExcelService" | Get-SPExcelDataConnectionLibrary | where {$_.Description.Contains("Sales Cube")}

This example displays all the DCLs that have descriptions containing the words "Sales Cube" from the specified Excel Services application.


Get-SPExcelDataProvider
Example
[image]

Returns a safe data provider or a list of safe data providers.

Get-SPExcelDataProvider -ExcelServiceApplication <SPExcelServiceApplicationPipeBind> [-AssignmentCollection <SPAssignmentCollection>]

Parameters

Parameter Required Type Description

ExcelServiceApplication

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the Excel Service application that contains the SPExcelDataProvider list object.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Detailed Description

Use the Get-SPExcelDataProvider cmdlet to read a safe data provider or a list of safe data providers for an Excel Services application. Excel Services refresh external data connections only if the data provider for the connection is on the list of safe data providers. Excel Services includes a set of common data providers on this list, so using cmdlets to modify safe data providers is typically an operation only in custom data connection scenarios.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

To display the data provider with the providerID of SQLOLEDB from the list of data providers that is on the MyExcelService application, use the following syntax:
Copy Code
Get-SPExcelserviceapplication -identity "MyExcelService" | Get-SPExceldataprovider | {% where $_.providerID -eq "SQLOLEDB"}

To select a specific provider from the list of safe providers, you must pipe to a cmdlet, that is, a where clause, that can filter the list of providers.

--------------EXAMPLE--------------
Copy Code
Get-SPExcelDataProvider -ExcelServiceApplication "Excel Services" | format-table

This example displays a list of safe data providers for the specific Excel Service application, and then formats the results of the data providers in a table.


Get-SPExcelFileLocation
Example
[image]

Returns a trusted file location or a list of trusted file locations.

Get-SPExcelFileLocation [-Identity <SPExcelFileLocationPipeBind>] -ExcelServiceApplication <SPExcelServiceApplicationPipeBind> [-AssignmentCollection <SPAssignmentCollection>]

Parameters

Parameter Required Type Description

Identity

Optional

Microsoft.Office.Excel.Server.Cmdlet.SPExcelFileLocationPipeBind

Specifies the SPExcelFileLocation object to return. If the Identity parameter is not specified, the cmdlet returns all the file locations in the specified Excel Services application.

A valid string identifying the file location, such as "http://myPortal/myTeam"; or a valid string identifying the path, such as "C:\folder_name"; a valid GUID, such as "12345678-90ab-cdef-1234-567890bcdefgh"; or an instance of a valid SPExcelFileLocation object.

ExcelServiceApplication

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the Excel Services Application that contains the SPExcelFileLocation list object.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Detailed Description

Use the Get-SPExcelFileLocation cmdlet to get locations from which Excel Services loads workbooks. Excel Services loads only workbooks that are stored in a trusted file location. Properties of trusted file locations control how workbooks can be used when loaded by Excel Services. Excel Services always enforces the properties that are defined by the trusted file location from which a workbook was loaded. The properties of the trusted file location to use are determined by comparing the file path for the workbook with the address of the trusted file location.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1--------------
Copy Code
Get-SPExcelServiceApplication -identity "MyExcelService" | Get-SPExcelFileLocation

This example displays a list of trusted file locations for the MyExcelService application.

--------------EXAMPLE 2--------------
Copy Code
Get-SPExcelServiceApplication -identity "MyExcelService" | Get-SPExcelFileLocation | format-list *

This example displays a list of trusted file locations for the MyExcelService application, and then formats the list of properties into a table.

--------------EXAMPLE 3--------------
Copy Code
Get-SPExcelServiceApplication -identity "MyExcelService" | Get-SPExcelFileLocation | where {$_.ExternalDataAllowed -ne "None"}

This example displays a list of trusted file locations that allow external data connectivity for the MyExcelService application.


Get-SPExcelServiceApplication
Example
[image]

Returns a SPExcelServiceApplication object.

Get-SPExcelServiceApplication [-Identity <SPExcelServiceApplicationPipeBind>] [-AssignmentCollection <SPAssignmentCollection>]

Parameters

Parameter Required Type Description

Identity

Optional

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the ExcelServiceApplication object to get.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Detailed Description

Use the Get-SPExcelServiceApplication cmdlet to retrieve a SPExcelServiceApplication object. If the object returned by this cmdlet is changed, the change affects all instances of the specified Excel Services application in the farm.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1--------------
Copy Code
Get-SPExcelserviceapplication -"MyExcelServices"

This example displays the Excel Service Application named "My Excel Services".

--------------EXAMPLE 2--------------
Copy Code
Get-SPExcelServiceApplication

This example displays every Excel Services application that is running in the farm.

--------------EXAMPLE 3--------------
Copy Code
Get-SPExcelServiceApplication -Identitiy "My Excel Service" | format-list *

This example displays every Excel Services application named "MyExcelService", and then displays all of its settings in a list format.


Get-SPExcelUserDefinedFunction
Example
[image]

Returns a user defined function or a collection of user defined functions.

Get-SPExcelUserDefinedFunction [-Identity <SPExcelUDFPipeBind>] -ExcelServiceApplication <SPExcelServiceApplicationPipeBind> [-AssignmentCollection <SPAssignmentCollection>]

Parameters

Parameter Required Type Description

Identity

Optional

Microsoft.Office.Excel.Server.Cmdlet.SPExcelUDFPipeBind

Specifies the UserDefinedFunction object to get. If the Identity parameter is not specified, the cmdlet returns all of the functions in the user defined functions list.

The type must be a valid name of the code assembly (for example, SampleCompany.SampleApplication.SampleUdf); or an instance of a valid SPExcelUDF object.

ExcelServiceApplication

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the Excel Service Application that contains the SPExcelUserDefinedFunction list object.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Detailed Description

Use the Get-SPExcelUserDefinedFunction cmdlet to read a user defined function and its properties, or to return the collection of a user defined functions for an Excel Service application. Excel Services use only user-defined functions that have an entry in the list of user-defined functions. User-defined functions are managed code assemblies that can be called from an Excel workbook by using standard Excel formula syntax. The assemblies can perform custom logic or other actions, such as refresh data. PowerShell cmdlets operate on the entries in the user-defined function list, but do not operate on the actual assemblies.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1--------------
Copy Code
Get-SPExcelServiceApplication -identity "MyExcelService" | Get-SPExcelUserDefinedFunction-identity SampleCompany.SampleApplication.SampleUdf

This example displays the user-defined function named "SampleCompany.SampleApplication.SampleUdf" from the list of user-defined functions that is on the MyExcelService application.

--------------EXAMPLE 2--------------
Copy Code
Get-SPExcelServiceApplication | Get-SPExcelUserDefinedFunction | format-list

This example displays every user defined function for every Excel Service application in the farm, and then displays the results in a list.


New-SPExcelBlockedFileType
Example
[image]

Adds a file type to the list of file types that are prevented from loading by an Excel Services application.

New-SPExcelBlockedFileType -ExcelServiceApplication <SPExcelServiceApplicationPipeBind> -FileType <XLSX | XLSB | XLSM> [-AssignmentCollection <SPAssignmentCollection>] [-Confirm [<SwitchParameter>]] [-WhatIf [<SwitchParameter>]]

Parameters

Parameter Required Type Description

ExcelServiceApplication

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the Excel Service Application that contains the SPBlockedExcelFileType list object.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

FileType

Required

Microsoft.Office.Excel.Server.FileType

Specifies the file types that are blocked from loading.

The type must be one of the following file extensions: XLSX, XLSB, or XLSM.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Confirm

Optional

System.Management.Automation.SwitchParameter

Prompts you for confirmation before executing the command. For more information, type the following command: get-help about_commonparameters

WhatIf

Optional

System.Management.Automation.SwitchParameter

Displays a message that describes the effect of the command instead of executing the command. For more information, type the following command: get-help about_commonparameters

Detailed Description

Use the New-SPExcelBlockedExcelFileType cmdlet to add an entry to the list of file types that are prevented from loading by Excel Services. Excel Services will not load any file types that correspond to a file type that is on this list. The specified file type is prevented from loading by all instances of Excel Services for the specified Excel Services application.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE--------------
Copy Code
Get-SPExcelServiceApplication -identity "MyExcelService" | New-SPExcelBlockedExcelFileType -FileType XLSM

This example displays a list of file extensions that is currently prevented from loading by Excel Services.


New-SPExcelDataConnectionLibrary
Example
[image]

Adds a new data connection library (DCL) to an Excel Services application.

New-SPExcelDataConnectionLibrary -Address <String> -ExcelServiceApplication <SPExcelServiceApplicationPipeBind> [-AssignmentCollection <SPAssignmentCollection>] [-Confirm [<SwitchParameter>]] [-Description <String>] [-WhatIf [<SwitchParameter>]]

Parameters

Parameter Required Type Description

Address

Required

System.String

Specifies the address of the Windows SharePoint Data Connection Library. The address must be unique in the list of trusted data connection libraries.

The type must be a valid URL, in the form http://server_name.

ExcelServiceApplication

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the Excel Service Application that contains the SPExcelDataConnectionLibrary list object.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Confirm

Optional

System.Management.Automation.SwitchParameter

Prompts you for confirmation before executing the command. For more information, type the following command: get-help about_commonparameters

Description

Optional

System.String

Specifies a friendly description for the data connection library.

The type must be a valid string; for example, this is a custom OLEDB provider.

WhatIf

Optional

System.Management.Automation.SwitchParameter

Displays a message that describes the effect of the command instead of executing the command. For more information, type the following command: get-help about_commonparameters

Detailed Description

Use the New-SPExcelDataConnectionLibrary cmdlet to add a Data Connection Library (DCL) to the list of trusted data connection libraries for an Excel Service application. Excel Services loads only data connection files that are stored in a DCL that is on the list of trusted DCLs. DCLs are a type of list in Windows SharePoint that contains data connection files. These files contain all the resources that Excel Services and the Excel client need to connect to an external data source. DCLs enable you to reuse and share data connections. Data connections can be loaded by using information from the workbook file; however, using a DCL to load data connections provides the ability for data connections to be managed separately from workbooks.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE----------------
Copy Code
Get-SPExcelServiceApplication -identity "MyExcelService" | New-SPExcelDataConnectionLibrary -address "http://portal/site/salesDCL" -description "This is the DCL where all the connections to the sales data are stored."

This example adds a new DCL, and then sets its description to the list of trusted DCLs that is on the MyExcelService application.

Note: Workbooks can use connection files stored in that library to refresh data on the Excel Services application.


New-SPExcelDataProvider
Example
[image]

Adds a new safe data provider to an Excel Services application.

New-SPExcelDataProvider -ExcelServiceApplication <SPExcelServiceApplicationPipeBind> -ProviderId <String> -ProviderType <Oledb | Odbc | OdbcDsn> [-AssignmentCollection <SPAssignmentCollection>] [-Confirm [<SwitchParameter>]] [-Description <String>] [-WhatIf [<SwitchParameter>]]

Parameters

Parameter Required Type Description

ExcelServiceApplication

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the Excel Service Application that contains the SPExcelDataProvider list object.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

ProviderId

Required

System.String

Specifies the name of the provider. This ID should be present in the connection strings generated by this provider and the ID must be unique for the provider type.

The type must be a valid name of a provider; for example CustomOLEDB.

ProviderType

Required

Microsoft.Office.Excel.Server.DataProviderType

Specifies the supported type of data provider to add to safe provider list.

The type must be one of the following: OleDb, Odbc, or OdbcDsn.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Confirm

Optional

System.Management.Automation.SwitchParameter

Prompts you for confirmation before executing the command. For more information, type the following command: get-help about_commonparameters

Description

Optional

System.String

Specifies a friendly description for the new data provider.

The type must be a valid string; for example, this is a custom OLEDB provider.

WhatIf

Optional

System.Management.Automation.SwitchParameter

Displays a message that describes the effect of the command instead of executing the command. For more information, type the following command: get-help about_commonparameters

Detailed Description

Use the New-SPExcelDataProvider cmdlet to add a data provider to the list of safe data providers in an Excel Services application. Excel Services will refresh external data connections only if the data provider for a connection is on the list of safe data providers. Excel Services includes a set of common data providers on this list, so using cmdlets to modify safe data providers is an operation that typically occurs only in custom data connection scenarios.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1--------------
Copy Code
Get-SPExcelServiceApplication -identity "MyExcelService" | New-SPExcelDataProvider -providerID "CustomOLEDB" -ProviderType OleDb -description "This is a custom OLEDB provider"

This example adds a new custom OLEDB data provider to the list of safe data providers for the MyExcelService application.

--------------EXAMPLE 2--------------
Copy Code
Get-SPExcelServiceApplication | New-SPExcelDataProvider -providerID "CustomOLEDB" -ProviderType OleDb -description "This is a custom OLEDB provider"

This example adds a new custom OLEDB data provider to the list of safe data providers for all Excel Service applications in the farm.


New-SPExcelFileLocation
Example
[image]

Adds a new trusted location to an Excel Services application.

New-SPExcelFileLocation -Address <String> -ExcelServiceApplication <SPExcelServiceApplicationPipeBind> [-AbortOnRefreshOnOpenFail <SwitchParameter>] [-AssignmentCollection <SPAssignmentCollection>] [-AutomaticVolatileFunctionCacheLifetime <Int32>] [-ChartAndImageSizeMax <Int32>] [-ConcurrentDataRequestsPerSessionMax <Int32>] [-Confirm [<SwitchParameter>]] [-DefaultWorkbookCalcMode <File | Manual | Auto | AutoDataTables>] [-Description <String>] [-DisplayGranularExtDataErrors <SwitchParameter>] [-ExternalDataAllowed <None | Dcl | DclAndEmbedded>] [-IncludeChildren <SwitchParameter>] [-LocationType <SharePoint | Unc | Http>] [-ManualExtDataCacheLifetime <Int32>] [-NewWorkbookSessionTimeout <Int32>] [-PeriodicExtDataCacheLifetime <Int32>] [-RequestDurationMax <Int32>] [-SessionTimeout <Int32>] [-ShortSessionTimeout <Int32>] [-UdfsAllowed <SwitchParameter>] [-WarnOnDataRefresh <SwitchParameter>] [-WhatIf [<SwitchParameter>]] [-WorkbookSizeMax <Int32>]

Parameters

Parameter Required Type Description

Address

Required

System.String

Specifies a SharePoint location, Web folder, or file directory that Excel Services can load workbooks from.

The type must be a valid URL, in the form http://myPortal/myTeam; or a valid path in form, \\server_name\folder_name

ExcelServiceApplication

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the Excel Service Application that contains the SPExcelFileLocation list object.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

AbortOnRefreshOnOpenFail

Optional

System.Management.Automation.SwitchParameter

Specifies that the load of an Excel file automatically fails if an automatic data refresh fails when the file is opened. The load is failed only if the following conditions are true:

  • The user has Windows SharePoint View Only permissions to file.

  • The workbook file has data connections that refresh automatically when the Excel file is opened.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

AutomaticVolatileFunctionCacheLifetime

Optional

System.Int32

Specifies the maximum time, in seconds, that a computed value for a volatile function is cached for automatic recalculations. Valid values include:

  • -1, calculate once when the workbook loads

  • 0, always calculate

  • 1 to 2073600, cache 1 second to 24 days. The default integer value is 300.

The type must be the integers -1 or 0, or an integer value in the range of 1 to 2073600.

ChartAndImageSizeMax

Optional

System.Int32

Specifies the maximum size, in MB, of a chart or image that can be opened. The default value is 1.

The type must be a valid integer greater than 0.

ConcurrentDataRequestsPerSessionMax

Optional

System.Int32

Specifies the maximum number of concurrent external data requests allowed in each session. If a session must issue more than this number of requests, additional requests must be queued. The scope of this setting is the logical server. The default value is 5.

The type must be a positive integer.

Confirm

Optional

System.Management.Automation.SwitchParameter

Prompts you for confirmation before executing the command. For more information, type the following command: get-help about_commonparameters

DefaultWorkbookCalcMode

Optional

Microsoft.Office.Excel.Server.DefaultWorkbookCalcMode

Specifies the calculation mode of workbooks. Settings other than xlFile override the workbook settings. The default setting is xlFile.

The type must be one of the following: xlFile, xlManual, xlAuto, or xlAutoDataTables.

Description

Optional

System.String

Specifies a friendly description for the new file location.

The type must be a valid string; for example, this is the file location of the monthly sales reports.

DisplayGranularExtDataErrors

Optional

System.Management.Automation.SwitchParameter

Displays granular error messages for external data failures for files in this location.

ExternalDataAllowed

Optional

Microsoft.Office.Excel.Server.AllowExternalData

Specifies the type of external data access allowed for workbooks. None specifies that data refresh is disabled. Dcl specifies that data is refreshed only when it uses a connection file in a trusted data connection library (DCL). DclandEmbedded specifies that a DCL can be used but is not required. The scope of this setting is the trusted location. The default value is None.

Any of the following settings:

  • None

  • Dcl

  • DclAndEmbedded

IncludeChildren

Optional

System.Management.Automation.SwitchParameter

Indicates that sub-URLs are trusted. For example, if the trusted file location is http://portal, and IncludeChildren is true, then http://portal/subSite is also trusted, as is any sub URL under the URL specified in Address.

LocationType

Optional

Microsoft.Office.Excel.Server.LocationType

Specifies the type of location. Location type indicates how Excel Services retrieves files from that location specified in Address. The default setting is the SharePoint type. The default value is SharePoint.

The type must be one of the following: SharePoint, UNC, or HTTP(S).

ManualExtDataCacheLifetime

Optional

System.Int32

Specifies the time, in seconds, that Excel Services will wait before re-issuing a manual, or user-initiated, external data request. During this time period, the results from the previous external data request may be returned from an external data cache. The default value is 300.

The type must be -1 or any positive integer.

NewWorkbookSessionTimeout

Optional

System.Int32

Specifies the time, in seconds, that a session for a new, unsaved, workbook will remain active on Excel Services with no user activity. Valid values include the following:

  • -1, session never expires

  • 0, session expires at the end of a single request

  • 1 to 2073600, remain active 1 second to 24 days. The default value 1800.

The type must be -1, 0, or an integer in the range of 1 to 2073600.

PeriodicExtDataCacheLifetime

Optional

System.Int32

Specifies the time, in seconds, that Excel Services will wait before re-issuing an on-open or periodic (that is, automatic) external data request. During this period, the results from the previous external data request may be returned from an external data cache. A value of -1 specifies to never refresh after the first query. The default value is 300.

The type must be -1, 0, or any positive integer.

RequestDurationMax

Optional

System.Int32

Specifies the maximum duration, in seconds, for a single request on a session. After this time elapses, the request times out. The default value is 300.

The type must be -1 or an integer in the range of 1 to 2073600.

SessionTimeout

Optional

System.Int32

Specifies the time, in seconds, that a session will remain active on Excel Services with no user activity. Valid values include the following:

  • -1, session never expires

  • 0, session expires at the end of a single request

  • 1 to 2073600, remain active 1 second to 24 days. The default value is 300.

The type must be -1, 0, or an integer in the range of 1 to 2073600.

ShortSessionTimeout

Optional

System.Int32

Specifies the time, in seconds, a user has to make the initial interaction with a spreadsheet. After this time has elapsed, if the user is not active, then the session is closed. The value must always be less than SessionTimeout. If the ShortSessionTimeout value is greater than or equal to SessionTimeout, an error is returned. The default value is 75.

The type must be -1 or any positive integer.

UdfsAllowed

Optional

System.Management.Automation.SwitchParameter

Specifies that user-defined functions can be called by workbooks that are loaded from the trusted file location that is specified in Address.

The default value is false.

WarnOnDataRefresh

Optional

System.Management.Automation.SwitchParameter

Specifies that a warning is displayed to the user on the first refresh of data for the workbook. The default value is true.

WhatIf

Optional

System.Management.Automation.SwitchParameter

Displays a message that describes the effect of the command instead of executing the command. For more information, type the following command: get-help about_commonparameters

WorkbookSizeMax

Optional

System.Int32

Specifies the maximum size, in MB, of a workbook that can be loaded. The default value is 10.

The type must be an integer value in the range of 1 to 2000.

Detailed Description

Use the New-SPExcelFileLocation cmdlet to add a new location to the trusted file locations list in an Excel Services application. Excel Services will load only workbooks that are stored in a trusted file location. Properties of trusted file locations control how workbooks can be used when loaded on Excel Services. Excel Services always enforces the properties defined by the trusted file location that a workbook was loaded from. The properties the trusted file location uses are determined by comparing the file path for the workbook with the Address parameter of the trusted file location.

The longest match takes prevalence. For example, if http://portal/site/ and http://portal/site/subsite are trusted locations, and you load a workbook from the subsite, the application will use the properties from the subsite trusted location because it has the longest matching address.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1--------------
Copy Code
Get-SPExcelServiceApplication -identity "MyExcelService" | New-SPExcelFileLocation -address "http://myPortal/myTeam" -includechildren -locationType SharePoint -description "This is my team's site on myPortal." -workbooksizemax 50 -externaldataallowed DclAndEmbedded -WarnOnDataRefresh:$false

This example adds a new trusted file location to the list of trusted file locations that is on the MyExcelService application.

Note: This specific example enables workbooks to load from any child or subfolder that starts with http://myPortal/myTeam. All workbooks loaded from this location must be smaller than 50 megabytes (MB) in size. The workbooks can refresh external data by using connections that are embedded in the workbook file or stored in a Data Connection Library (DCL). A warning message that the workbook is attempting to refresh external data will not be displayed.

--------------EXAMPLE 2--------------
Copy Code
Get-SPExcelServiceApplication -identity "MyExcelService" | New-SPExcelFileLocation -address http://myPortal/myTeam/managers -locationType SharePoint -description "This is the managers subsite for myTeam."

This example adds a new trusted file location to the list of trusted file locations that is on the MyExcelService application with a given address and description.


New-SPExcelServiceApplication
Example
[image]

Provisions a new instance of an Excel Services application.

New-SPExcelServiceApplication -Name <String> -SPIisWebServiceApplicationPool <SPIisWebServiceApplicationPoolPipeBind> [-AccessModel <Delegation | TrustedSubsystem | Claims>] [-AssignmentCollection <SPAssignmentCollection>] [-CachingOfUnusedFilesEnable <SwitchParameter>] [-Confirm [<SwitchParameter>]] [-CrossDomainAccessAllowed <SwitchParameter>] [-Default <SwitchParameter>] [-EncryptedUserConnectionRequired <None | Connection>] [-ExternalDataConnectionLifetime <Int32>] [-FileAccessMethod <UseImpersonation | UseFileAccessAccount>] [-LoadBalancingScheme <WorkbookUrl | RoundRobin | Local>] [-MemoryCacheThreshold <Int32>] [-Partition <SwitchParameter>] [-PrivateBytesMax <Int32>] [-SessionsPerUserMax <Int32>] [-SiteCollectionAnonymousSessionsMax <Int32>] [-TerminateProcessOnAccessViolation <SwitchParameter>] [-ThrottleAccessViolationsPerSiteCollection <Int32>] [-UnattendedAccountApplicationId <String>] [-UnusedObjectAgeMax <Int32>] [-WhatIf [<SwitchParameter>]] [-WorkbookCache <String>] [-WorkbookCacheSizeMax <Int32>]

Parameters

Parameter Required Type Description

Name

Required

System.String

Specifies a friendly name for the new Excel Services application. The name that you use must be a unique name of an Excel Services application that is in this farm. The name can be a maximum of 128 characters and cannot include the invalid characters comma (","), equal ("="), and colon (":").The default value is Service_numofapplications+1.

The type must be a valid string; for example, MyExcelServiceApp1.

SPIisWebServiceApplicationPool

Required

Microsoft.SharePoint.PowerShell.SPIisWebServiceApplicationPoolPipeBind

Specifies the existing IIS application pool to run the Web service in for the new Excel Service application.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid URL of a search application, in the form http://server_name; or an instance of a valid SPIisWebServiceApplicationPool object.

AccessModel

Optional

Microsoft.Office.Excel.Server.AccessModel

Specifies the type of authentication model that the Excel Services front end components use to communicate with the application server. TrustedSubSystem (TSS) specifies that a trusted account is used for the communication. Delegation specifies that the identity of the user is delegated to the back end. The default value is TSS.

Note:
In most farm configurations Delegation will require Kerberos. If the AccessModel settings is changed at runtime, all existing sessions and any backed up sessions are lost.

Note:
With both authentication models, files that are stored in Windows SharePoint are always retrieved as the user making the request so per user permissions checking is performed.

The type must be one of the following: Delegation, TSS.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

CachingOfUnusedFilesEnable

Optional

System.Management.Automation.SwitchParameter

Specifies that files that are no longer used by Excel Services can remain in the cache for later use. The default is true when the parameter is not specified on the command.

Confirm

Optional

System.Management.Automation.SwitchParameter

Prompts you for confirmation before executing the command. For more information, type the following command: get-help about_commonparameters

CrossDomainAccessAllowed

Optional

System.Management.Automation.SwitchParameter

Specifies that trusted workbooks and data connection files can be requested and rendered by Web parts or pages that reside in other HTTP domains.

Default

Optional

System.Management.Automation.SwitchParameter

Specifies that the proxy for this service application is added to the default proxy group of the farm.

EncryptedUserConnectionRequired

Optional

Microsoft.Office.Excel.Server.EncryptedConnectionMode

Requires that encryption is used between the end-user and the Excel Server. The scope of this setting is the logical server. The default values is None.

The type must be one of the following: None or Connection.

ExternalDataConnectionLifetime

Optional

System.Int32

Specifies the maximum number of seconds that an external data connection can remain open in the connection pool. Valid values include -1 to never recycle, or 1 to 2073600 to remain active 1 second to 24 days. The default value is 1800.

FileAccessMethod

Optional

Microsoft.Office.Excel.Server.FileAccessMethod

Specifies the method that Excel Services uses to retrieve files. UseFileAccessAccount specifies that all workbooks are always retrieved as Excel Services. UseImpersonation specifies that Excel Services will retrieve the workbook files as the user who is making the request. The default value is UseImpersonation.

The type must be one of the following: UseImpersonation or UseFileAccessAccount.

LoadBalancingScheme

Optional

Microsoft.Office.Excel.Server.LoadBalancingScheme

Specifies the load-balancing schema that is used by the Excel Services application for sending requests to different back-end Excel Services computers. The default value is Workbook URL.

The type must be one of the following: Round Robin, LocalWorkbook, or Workbook URL.

MemoryCacheThreshold

Optional

System.Int32

Specifies the percentage of the maximum private bytes that can be allocated to inactive objects. When the memory cache threshold is exceeded, cached objects that are not currently in use are released. Setting this value to 0 disables caching of inactive objects. The default value is 90.

The type must be an integer in the range of 0 to 95.

Partition

Optional

System.Management.Automation.SwitchParameter

Restricts this service to behave only on a partitioned set of site collections. This setting cannot be changed after the application is provisioned.

PrivateBytesMax

Optional

System.Int32

Specifies the maximum private bytes, in MB, used by Excel Services. When set to -1, the maximum private bytes defaults to 50% of physical memory on the computer. Valid values include -1 for no limit, and any positive integer. The default value is -1.

The type must be -1 or any positive integer.

SessionsPerUserMax

Optional

System.Int32

Specifies the maximum number of sessions allowed for a user. If a user reaches this maximum the oldest session will be deleted when a new session is started. Valid values include -1 for no limit, and any positive integer. The default value 25.

The type must be -1 or any positive integer.

SiteCollectionAnonymousSessionsMax

Optional

System.Int32

Specifies the maximum number of anonymous sessions allowed per site collection. A value of 0 specifies that no anonymous sessions are allowed. Valid values include -1 for no limit, 0 to disable the setting, or any positive integer. The default value is 25.

The type must be -1, 0, or any positive integer.

TerminateProcessOnAccessViolation

Optional

System.Management.Automation.SwitchParameter

Terminates the Excel Calculation Services when an access violation occurs in the process.

ThrottleAccessViolationsPerSiteCollection

Optional

System.Int32

Specifies that if a workbook causes an access violation error on Excel Services, then all files originating from that workbook's site collection will be blocked from loading for the specified period (in seconds). Existing sessions for files on that site collection are unaffected. Valid values include 0 to disable the setting, and the range of 1 to 2073600 seconds (24 days). The default value is 0.

The type must be an integer value in the range of 0 to 2073600.

UnattendedAccountApplicationId

Optional

System.String

Specifies that the Application ID that is used to lookup the Unattended Service Account credentials from the Secure Store Service that is specified by UnattendedAccountSecureServiceAppName. The Secure Store Service is contacted when this parameter is set and specific error cases occur. Valid values are alphanumeric strings with a maximum of 256 characters. The default value is an empty string.

The type must be a valid string of alphanumeric characters; for example, UnattendedServiceAccountID.

Note:
The Unattended Account is a single account that all workbooks can use to refresh data. An Unattended Account is required when workbook connections specify None for authentication, or when any non-Windows credentials are used to refresh data.

UnusedObjectAgeMax

Optional

System.Int32

Specifies the maximum amount of time, in minutes, that objects not currently used in a session are kept in the memory cache. Valid values include -1 for no maximum setting, and a positive integer in the range of 1 to 34560 seconds (24 days). The default value is -1.

The type must be -1, or an integer in the range of 1 to 34560.

WhatIf

Optional

System.Management.Automation.SwitchParameter

Displays a message that describes the effect of the command instead of executing the command. For more information, type the following command: get-help about_commonparameters

WorkbookCache

Optional

System.String

Specifies the local file system location of the cache that is used for storing workbooks used by Excel Services.

The type must be a valid path in either of the following forms:

  • C:\folder_name

  • \\server_name\folder_name

WorkbookCacheSizeMax

Optional

System.Int32

Specifies the maximum allowable size, in MB, of an individual session. The default value is 0.

The type must be an integer in the range of 0 to 4095.

Detailed Description

Use the New-SPExcelServiceApplication cmdlet to provision a new Excel Service with new settings. After you create a new Excel Service, you can use the Set-SPExcelServiceApplication cmdlet to modify its global settings.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1--------------
Copy Code
New-SPExcelServiceApplication -Name "MyExcelService" -SPIisWebServiceApplicationPool "SharePoint Services"

This example provisions a new instance of Excel Services with the display name "MyExcelService".

--------------EXAMPLE 2--------------
Copy Code
New-SPExcelServiceApplication -Name "My Excel Service" -SPIisWebServiceApplicationPool "SharePoint Services" -SessionsPerUserMax 5

This example provisions a new instance of Excel Services with the display name "My Excel Service" and allows only 5 sessions per user per back end application server computer that is running Excel Services.


New-SPExcelUserDefinedFunction
Example
[image]

Adds a new user defined function to an Excel Services application.

New-SPExcelUserDefinedFunction -Assembly <String> -ExcelServiceApplication <SPExcelServiceApplicationPipeBind> [-AssemblyLocation <GAC | LocalFile>] [-AssignmentCollection <SPAssignmentCollection>] [-Confirm [<SwitchParameter>]] [-Description <String>] [-Enable <SwitchParameter>] [-WhatIf [<SwitchParameter>]]

Parameters

Parameter Required Type Description

Assembly

Required

System.String

Specifies the strongly-typed name of the assembly. The name must be unique in the list of user-defined functions. The name can be a maximum of 4096 alphanumeric characters.

The type must be a string of alphanumeric characters; for example:

  • SampleCompany.SampleApplication.SampleUdf

  • C:\UDFs\SampleUdf.dll

  • \\MyNetworkServer\UDFs\SampleUdf.dll

ExcelServiceApplication

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the Excel Services application that contains the list of user-defined functions list.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

AssemblyLocation

Optional

Microsoft.Office.Excel.Server.AssemblyLocation

Specifies the location where the assembly is stored. The default value is GAC.

The type must be one of the following: GAC or LocalFile.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Confirm

Optional

System.Management.Automation.SwitchParameter

Prompts you for confirmation before executing the command. For more information, type the following command: get-help about_commonparameters

Description

Optional

System.String

Specifies a friendly description for the user defined function. The name can be a maximum of 4096 alphanumeric characters. The default value is an empty string.

The type must be a valid string of alphanumeric characters; for example, this is the user defined function for consolidation reports.

Enable

Optional

System.Management.Automation.SwitchParameter

Activates the user-defined function for use with Excel Services. The default value is true.

WhatIf

Optional

System.Management.Automation.SwitchParameter

Displays a message that describes the effect of the command instead of executing the command. For more information, type the following command: get-help about_commonparameters

Detailed Description

Use the New-SPExcelUserDefinedFunction cmdlet to add a user-defined function to an Excel Services application. Excel Services uses only user-defined functions that have an entry in the list of user-defined functions. User-defined functions are managed code assemblies that can be called from an Excel workbook by using standard Excel formula syntax. The assemblies can perform custom logic or other actions, such as refresh data. PowerShell cmdlets operate on the entries that are on the list of user-defined functions, but they do not operate on the actual assemblies.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1--------------
Copy Code
New-SPExceluserdefinedfunction -ExcelServiceApplication "MyExcelService" -assembly "SampleCompany.SampleApplication.SampleUdf" -assemblylocation GAC

This example adds the SampleComany.SampleApplication.SampleUdf file to the MyExcelService application as a new user-defined function.

Note: The user-defined function assembly is deployed to the Global Access Cache (GAC).

--------------EXAMPLE 2--------------
Copy Code
Get-SPExcelServiceApplication | New-SPExceluserdefinedfunction -assembly "SampleCompany.SampleApplication.SampleUdf" -assemblylocation GAC

This example adds the SampleComany.SampleApplication.SampleUdf to every Excel Service application in the farm.

Note: The user-defined function assembly is deployed to the Global Access Cache (GAC).


Remove-SPExcelBlockedFileType
Example
[image]

Removes an entry from the list of file types that are prevented from loading on an Excel Services application.

Remove-SPExcelBlockedFileType [-Identity <SPExcelBlockedFileTypePipeBind>] -ExcelServiceApplication <SPExcelServiceApplicationPipeBind> [-AssignmentCollection <SPAssignmentCollection>] [-Confirm [<SwitchParameter>]] [-WhatIf [<SwitchParameter>]]

Parameters

Parameter Required Type Description

Identity

Optional

Microsoft.Office.Excel.Server.Cmdlet.SPExcelBlockedFileTypePipeBind

Specifies the file type to remove from the list of blocked file types.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of a file type (for example, FileType1); one of the following file types: XLSX, XLSB, XLSM; or an instance of a valid SPExcelBlockedFileType object.

ExcelServiceApplication

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the Excel Service Application that contains the SPBlockedExcelFileType list object.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Confirm

Optional

System.Management.Automation.SwitchParameter

Prompts you for confirmation before executing the command. For more information, type the following command: get-help about_commonparameters

WhatIf

Optional

System.Management.Automation.SwitchParameter

Displays a message that describes the effect of the command instead of executing the command. For more information, type the following command: get-help about_commonparameters

Detailed Description

Use the Remove-SPExcelBlockedExcelFileType cmdlet to remove an entry from the list of file types that are prevented from loading on an Excel Services application. Excel Services will not load any file types that correspond to a file type that is on this list.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE----------------
Copy Code
Get-SPExcelServiceApplication -identity "MyExcelService" | Remove-SPExcelBlockedExcelFileType -filetype XLSX

This example removes the .xlsx file extensions from the list of file types that are prevented from loading by Excel Services.

After the .xlsx file extension is removed from the list, these extensions will be allowed to load in Excel Services.


Remove-SPExcelDataConnectionLibrary
Example
[image]

Removes a data connection library from an Excel Services application.

Remove-SPExcelDataConnectionLibrary [-Identity <SPExcelDCLPipeBind>] -ExcelServiceApplication <SPExcelServiceApplicationPipeBind> [-AssignmentCollection <SPAssignmentCollection>] [-Confirm [<SwitchParameter>]] [-WhatIf [<SwitchParameter>]]

Parameters

Parameter Required Type Description

Identity

Optional

Microsoft.Office.Excel.Server.Cmdlet.SPExcelDCLPipeBind

Specifies the data connection library to remove.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of a data connection library (for example, DataConnectionLib1); a valid URL, in the form http://server_name; or an instance of a valid SPExcelDCL object.

ExcelServiceApplication

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the Excel Service application that contains the SPExcelDataConnectionLibrary list object.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Confirm

Optional

System.Management.Automation.SwitchParameter

Prompts you for confirmation before executing the command. For more information, type the following command: get-help about_commonparameters

WhatIf

Optional

System.Management.Automation.SwitchParameter

Displays a message that describes the effect of the command instead of executing the command. For more information, type the following command: get-help about_commonparameters

Detailed Description

Use the Remove-SPExcelDataConnectionLibrary cmdlet to remove a library from the Excel Services trusted data connection libraries list. Excel Services loads data connection files only if they are stored in a data connection library that is on the trusted data connection libraries list.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1--------------
Copy Code
Get-SPExcelserviceapplication -identity "MyExcelService" | Remove- SPExcelDataConnectionLibrary -Identity "http://portal/site/salesDCL"

This example removes the http://portal/site/salesDCL library from the list of trusted Data Connection Library (DCLs), which is on the MyExcelService application.

Note: Connection files will no longer be loaded from this library. Workbooks that depend on this connection file may not refresh data.

--------------EXAMPLE 2--------------
Copy Code
Get-SPExcelServiceApplication | Get-SPExcelDataConnectionLibrary | Remove-SPExcelDataConnectionLibrary

This example removes all Data Connection Libraries (DCLs) from all Excel Services applications running in the farm.

Note: Connection files will no longer be loaded from this library. Workbooks that depend on this connection file may not refresh data.


Remove-SPExcelDataProvider
Example
[image]

Removes a data provider from an Excel Service application.

Remove-SPExcelDataProvider -Identity <SPExcelDataProviderPipeBind> [-AssignmentCollection <SPAssignmentCollection>] [-Confirm [<SwitchParameter>]] [-WhatIf [<SwitchParameter>]]

Parameters

Parameter Required Type Description

Identity

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelDataProviderPipeBind

Specifies the SPExcelDataProvider object to remove.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh, or an instance of a valid SPExcelDataProvider object.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Confirm

Optional

System.Management.Automation.SwitchParameter

Prompts you for confirmation before executing the command. For more information, type the following command: get-help about_commonparameters

WhatIf

Optional

System.Management.Automation.SwitchParameter

Displays a message that describes the effect of the command instead of executing the command. For more information, type the following command: get-help about_commonparameters

Detailed Description

Use the Remove-SPExcelDataProvider cmdlet to remove a data provider that is stored on the list of safe data providers. Excel Services will refresh external data connections only if the data provider for that connection is on the list of safe data providers. Excel Services includes a set of common data providers on this list, so using cmdlets to modify safe data providers is typically an operation only in custom data connection scenarios.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1--------------
Copy Code
Get-SPExcelServiceApplication -identity "MyExcelService" | Get-SPExcelDataProvider | {% where $_.providerID -eq "SQLOLEDB" } | Remove-SPExcelDataProvider

This example removes the default SQL OLEDB data provider from the list of safe data providers that is on the MyExcelService application.

Note: Excel Services will no longer refresh any data connections that use this data provider.

--------------EXAMPLE 2--------------
Copy Code
$provider = Get-SPExcelServiceApplication -identity "MyExcelService" | Get-SPExcelDataProvider | {where $_.providerID -eq "MyCustomProvider"}
Copy Code
Remove-SPExcelDataProvider $provider

This example removes a custom data provider from the list of safe data providers that is on the MyExcelService application.

Note: Excel Services will no longer refresh any data connections that use this data provider.


Remove-SPExcelFileLocation
Example
[image]

Removes a trusted file location from an Excel Service application.

Remove-SPExcelFileLocation [-Identity <SPExcelFileLocationPipeBind>] -ExcelServiceApplication <SPExcelServiceApplicationPipeBind> [-AssignmentCollection <SPAssignmentCollection>] [-Confirm [<SwitchParameter>]] [-WhatIf [<SwitchParameter>]]

Parameters

Parameter Required Type Description

Identity

Optional

Microsoft.Office.Excel.Server.Cmdlet.SPExcelFileLocationPipeBind

Specifies the FileLocation object to remove.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid string identifying the file location, in the form http://myPortal/myTeam; a valid string identifying the path, in the form C:\folder_name; or an instance of a valid SPExcelFileLocation object.

ExcelServiceApplication

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the Excel Services Application that contains the SPExcelFileLocation list object.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Confirm

Optional

System.Management.Automation.SwitchParameter

Prompts you for confirmation before executing the command. For more information, type the following command: get-help about_commonparameters

WhatIf

Optional

System.Management.Automation.SwitchParameter

Displays a message that describes the effect of the command instead of executing the command. For more information, type the following command: get-help about_commonparameters

Detailed Description

Use the Remove-SPExcelFileLocation cmdlet to remove a location from the list of trusted file locations. Excel Services will load only workbooks that are stored in a trusted file location. Properties of trusted file locations control how workbooks can be used when loaded on Excel Services. Excel Services always enforces the properties defined by the trusted file location from which a workbook was loaded. The properties used by the trusted file location are determined by comparing the file path for the workbook with the Address parameter of the trusted file location.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1--------------
Copy Code
Remove-SPExcelFileLocation -ExcelServiceApplication "My Excel Service" -Identity http://portal/badTeamSite

This example removes the http://portal/badTeamSite from the list of trusted file locations for the My Excel Service application.

--------------EXAMPLE 2--------------
Copy Code
Get-SPExcelServiceApplication | Get-SPExcelFileLocation | where {$_.Address -ne "http://"} | Remove-SPExcelFileLocation

This example removes all non default trusted file locations from every Excel Service application in the farm.


Remove-SPExcelUserDefinedFunction
Example
[image]

Removes a user defined function from an Excel Service application.

Remove-SPExcelUserDefinedFunction [-Identity <SPExcelUDFPipeBind>] -ExcelServiceApplication <SPExcelServiceApplicationPipeBind> [-AssignmentCollection <SPAssignmentCollection>] [-Confirm [<SwitchParameter>]] [-WhatIf [<SwitchParameter>]]

Parameters

Parameter Required Type Description

Identity

Optional

Microsoft.Office.Excel.Server.Cmdlet.SPExcelUDFPipeBind

Specifies the SPExcelUserDefinedFunction object that you want to remove.

The type must be a valid name of the code assembly (for example, SampleCompany.SampleApplication.SampleUdf); or an instance of a valid SPExcelUDF object.

ExcelServiceApplication

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the Excel Services application that contains the list of user-defined functions.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Confirm

Optional

System.Management.Automation.SwitchParameter

Prompts you for confirmation before executing the command. For more information, type the following command: get-help about_commonparameters

WhatIf

Optional

System.Management.Automation.SwitchParameter

Displays a message that describes the effect of the command instead of executing the command. For more information, type the following command: get-help about_commonparameters

Detailed Description

Use the Remove-SPExcelUserDefinedFunction cmdlet to remove a user-defined function from the list of user-defined functions. User-defined functions are managed code assemblies that can be called from an Excel workbook by using standard Excel formula syntax. The assemblies can perform custom logic or other actions, such as refresh data. The PowerShell cmdlets operate on the entries in the user-defined function list, but do not operate on the actual assemblies.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1--------------
Copy Code
Remove-SPExcelUserDefinedFunction -ExcelServiceApplication "My Excel Service" -identity "SampleCompany.SampleApplication.SampleUdf"

This example removes the SampleCompany.SampleApplication.SampleUdf user-defined function from the list of user-defined functions that is in the MyExcelService application.

--------------EXAMPLE 2--------------
Copy Code
Get-SPExcelServiceApplication | Remove-SPExcelUserDefinedFunction -identity "SampleCompany.SampleApplication.SampleUdf"

This example removes the SampleCompany.SampleApplication.SampleUdf user-defined function from the list of user-defined functions for every Excel Service application in the farm.


Set-SPExcelDataConnectionLibrary
Example
[image]

Sets properties of a data connection library for an Excel Service application.

Set-SPExcelDataConnectionLibrary -Identity <SPExcelDCLPipeBind> -ExcelServiceApplication <SPExcelServiceApplicationPipeBind> [-Address <String>] [-AssignmentCollection <SPAssignmentCollection>] [-Confirm [<SwitchParameter>]] [-Description <String>] [-WhatIf [<SwitchParameter>]]

Parameters

Parameter Required Type Description

Identity

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelDCLPipeBind

Specifies the DataConnectionLibrary object to update.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of a data connection library (for example, DataConnectionLib1); a valid URL, in the form http://server_name; or an instance of a valid SPExcelDCL object.

ExcelServiceApplication

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the Excel Services application that contains SPExcelDataConnectionLibrary list object.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

Address

Optional

System.String

Specifies the address of the Windows SharePoint Data Connection Library. The address must be unique in the list of trusted data connection libraries.

The type must be a valid URL, in the form http://server_name.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Confirm

Optional

System.Management.Automation.SwitchParameter

Prompts you for confirmation before executing the command. For more information, type the following command: get-help about_commonparameters

Description

Optional

System.String

Specifies a friendly description for the data connection library.

The type must a valid a string; for example, this is the DCL where all the connections to the sales data are stored.

WhatIf

Optional

System.Management.Automation.SwitchParameter

Displays a message that describes the effect of the command instead of executing the command. For more information, type the following command: get-help about_commonparameters

Detailed Description

Use the Set-SPExcelDataConnectionLibrary cmdlet to set the properties of a library from which Excel Services loads data connection files. Excel Services loads data connection files only if they are stored in a data connection library (DCL) that is on the list of trusted DCLs. DCLs are a type of list in Windows SharePoint that contain data connection files. These files contain everything that Excel Services and the Excel client need to connect to an external data source. DCLs enable you to reuse and share data connections. Data connections can be loaded by using information from the workbook file; however, using a DCL to load data connections provides an additional layer for data connections so that they can be managed separately from workbooks.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1--------------
Copy Code
Get-SPExcelServiceApplication -identity "MyExcelService" | Get-SPExcelDataConnectionLibrary | {% where $_.address -eq "http://portal/site/salesDCL" } | Set-SPExcelDataConnectionLibrary -description "This is the DCL where all the connections to the sales data are stored."

This example sets a display description for the entry "http://portal/site/salesDCL" in the list of trusted Data Connection Libraries (DCLs) that is on the MyExcelService application.

--------------EXAMPLE 2--------------
Copy Code
Get-SPExcelServiceApplication -identity "MyExcelService" | Get-SPExcelDataConnectionLibrary | {% where $_.address -eq "http://portal/test/site/salesDCL" } | Set-SPExcelDataConnectionLibrary -address "http://portal/site/salesDCL"

This example updates the address of the Data Connection Library (DCL) for the entry "http://portal/test/site/salesDCL" to "http://portal/site/salesDCL" in the list of trusted DCLs, which is on the MyExcelService application.


Set-SPExcelDataProvider
Example
[image]

Sets properties of a safe data provider for an Excel Service application.

Set-SPExcelDataProvider -Identity <SPExcelDataProviderPipeBind> [-AssignmentCollection <SPAssignmentCollection>] [-Confirm [<SwitchParameter>]] [-Description <String>] [-ProviderId <String>] [-ProviderType <Oledb | Odbc | OdbcDsn>] [-WhatIf [<SwitchParameter>]]

Parameters

Parameter Required Type Description

Identity

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelDataProviderPipeBind

Specifies the SPExcelDataProvider object to update.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh, or an instance of a valid SPExcelDataProvider object.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Confirm

Optional

System.Management.Automation.SwitchParameter

Prompts you for confirmation before executing the command. For more information, type the following command: get-help about_commonparameters

Description

Optional

System.String

Specifies a display description for the data provider.

The type must a valid a string; for example, this is a custom OLEDB provider.

ProviderId

Optional

System.String

Specifies the name of the data provider. This ID should be present in the connection strings generated by this data provider, and the ID must be unique for the data provider type.

The type must a valid name of a provider; for example, CustomOLEDB.

ProviderType

Optional

Microsoft.Office.Excel.Server.DataProviderType

Specifies the supported type of the data provider to set.

The type must be one of the following: OleDb, Odbc, or OdbcDsn.

WhatIf

Optional

System.Management.Automation.SwitchParameter

Displays a message that describes the effect of the command instead of executing the command. For more information, type the following command: get-help about_commonparameters

Detailed Description

Use the Set-SPExcelDataProvider cmdlet to set the properties of a data provider that is stored in the list of safe data providers. Excel Services will refresh external data connections only if the data provider for that connection is in the list of safe data providers. Excel Services include a set of common data providers in this list, so using cmdlets to modify safe data providers is typically an operation of custom data connection scenarios.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1--------------
Copy Code
Get-SPExcelServiceApplication -identity "MyExcelService" | Get-SPExcelDataProvider | {where $_.providerID -eq "SQLOLEDB" } | Set-SPExcelDataProvider -description "This is the Microsoft SQL OLEDB driver (MDAC)"

This example retrieves the SQLOLEB data provider from the list of safe data providers that is on the MyExcelService application, and then adds a display description.

--------------EXAMPLE 2--------------
Copy Code
$provider = Get-SPExcelServiceApplication -identity "MyExcelService" | Get-spexceldataprovider | {where $_.providerID -eq "SQLOLEDB" }
Copy Code
Set-SPExcelDataProvider $provider -Description "The SQLOLEDB provider description has changed."
Copy Code
$provider | format-table

This example retrieves the SQLOLEB data provider from the list of safe data providers that is on the MyExcelService application, as an object, adds a new display description, and then displays the provider output to the command line window, formatted as a table.


Set-SPExcelFileLocation
Example
[image]

Sets properties of a trusted file location for an Excel Services application.

Set-SPExcelFileLocation -Identity <SPExcelFileLocationPipeBind> -ExcelServiceApplication <SPExcelServiceApplicationPipeBind> [-AbortOnRefreshOnOpenFail <SwitchParameter>] [-Address <String>] [-AssignmentCollection <SPAssignmentCollection>] [-AutomaticVolatileFunctionCacheLifetime <Int32>] [-ChartAndImageSizeMax <Int32>] [-ConcurrentDataRequestsPerSessionMax <Int32>] [-Confirm [<SwitchParameter>]] [-DefaultWorkbookCalcMode <File | Manual | Auto | AutoDataTables>] [-Description <String>] [-DisplayGranularExtDataErrors <SwitchParameter>] [-ExternalDataAllowed <None | Dcl | DclAndEmbedded>] [-IncludeChildren <SwitchParameter>] [-LocationType <SharePoint | Unc | Http>] [-ManualExtDataCacheLifetime <Int32>] [-NewWorkbookSessionTimeout <Int32>] [-PeriodicExtDataCacheLifetime <Int32>] [-RequestDurationMax <Int32>] [-SessionTimeout <Int32>] [-ShortSessionTimeout <Int32>] [-UdfsAllowed <SwitchParameter>] [-WarnOnDataRefresh <SwitchParameter>] [-WhatIf [<SwitchParameter>]] [-WorkbookSizeMax <Int32>]

Parameters

Parameter Required Type Description

Identity

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelFileLocationPipeBind

Specifies the FileLocation object to update. The name must be unique in the list of file locations.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid string identifying the file location, in the form http://myPortal/myTeam; a valid string identifying the path, in the form C:\folder_name; or an instance of a valid SPExcelFileLocation object.

ExcelServiceApplication

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the Excel Service Application that contains the SPExcelFileLocation list object.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

AbortOnRefreshOnOpenFail

Optional

System.Management.Automation.SwitchParameter

Specifies that the load of an Excel file automatically fails if an automatic data refresh fails when the file is opened. The load is failed only if the following conditions are true:

  • User has Windows SharePoint View Only permissions to the Excel file.

  • Workbook file has data connections that refresh automatically when the Excel file is opened.

Address

Optional

System.String

Specifies a SharePoint location, Web folder, or file directory that Excel Services can load workbooks from.

The type must be a valid URL, in the form http://myPortal/myTeam; or a valid path in form, \\server_name\folder_name

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

AutomaticVolatileFunctionCacheLifetime

Optional

System.Int32

Specifies the maximum time, in seconds, that a computed value for a volatile function is cached for automatic recalculations. Valid values include:

  • -1, calculate once when the workbook loads

  • 0, always calculate

  • 1 to 2073600, cache 1 second to 24 days

The type must be the integers -1 or 0, or an integer value in the range of 1 to 2073600.

ChartAndImageSizeMax

Optional

System.Int32

Specifies the maximum size, in MB, of a chart or image that can be opened. The default value is 1.

The type must be a valid integer greater than 0.

ConcurrentDataRequestsPerSessionMax

Optional

System.Int32

Specifies the maximum number of concurrent external data requests allowed in each session. If a session must issue more than this number of requests, additional requests must be queued. The scope of this setting is the logical server. The default value is 5.

The type must be a positive integer.

Confirm

Optional

System.Management.Automation.SwitchParameter

Prompts you for confirmation before executing the command. For more information, type the following command: get-help about_commonparameters

DefaultWorkbookCalcMode

Optional

Microsoft.Office.Excel.Server.DefaultWorkbookCalcMode

Specifies the calculation mode of workbooks. All of the settings except the xlFile setting override the workbook settings. If this parameter is changed, it will be applied to all new sessions on Excel Services when the ConfigDB properties cache is refreshed. The default setting is xlFile.

The type must be one of the following: xlFile, xlManual, xlAuto, or xlAutoDataTables.

Description

Optional

System.String

Specifies a friendly description for the new file location.

The type must be a valid string; for example, this is the file location of the monthly sales reports.

DisplayGranularExtDataErrors

Optional

System.Management.Automation.SwitchParameter

Displays granular error messages for external data failures for files in this location.

ExternalDataAllowed

Optional

Microsoft.Office.Excel.Server.AllowExternalData

Specifies the type of external data access allowed for workbooks. None specifies that data refresh is disabled. Dcl specifies that data is refreshed only when it uses a connection file in a trusted data connection library (DCL). DclandEmbedded specifies that a DCL can be used but is not required. The scope of this setting is the trusted location.

The default value is None.

The type must be one of the following: None, Dcl, or DclAndEmbedded.

IncludeChildren

Optional

System.Management.Automation.SwitchParameter

Indicates that sub-URLs are trusted. For example, if the trusted file location is http://portal, and IncludeChildren is true, then http://portal/subSite is also trusted, as is any sub URL under the URL specified in Address.

LocationType

Optional

Microsoft.Office.Excel.Server.LocationType

The type of location. LocationType indicates how Excel Services retrieves files from that location specified in Address. The default value is SharePoint.

The type must be one of the following: SharePoint, UNC, or HTTP(S).

ManualExtDataCacheLifetime

Optional

System.Int32

Specifies the time, in seconds, that Excel Services waits before re-issuing a manual, or user-initiated, external data request. During this period, the results from the previous external data request may be returned from an external data cache. The default value is 300.

The type must be -1 or any positive integer.

NewWorkbookSessionTimeout

Optional

System.Int32

Specifies the time, in seconds, that a session for a new, unsaved, workbook will remain active on Excel Services with no user activity. The default value 1800. Valid values include the following:

  • -1, session never expires

  • 0, session expires at the end of a single request

  • 1 to 2073600, remain active 1 second to 24 days.

The type must be -1, 0, or an integer in the range of 1 to 2073600.

PeriodicExtDataCacheLifetime

Optional

System.Int32

Specifies the time, in seconds, that Excel Services waits before re-issuing an on-open or periodic (that is, automatic) external data request. During this period, the results from the previous external data request may be returned from an external data cache. A value -1 specifies to never refresh after the first query. The default value is 300.

The type must be -1, 0, or any positive integer.

RequestDurationMax

Optional

System.Int32

Specifies the maximum duration, in seconds, for a single request on a session. After this time elapses the request is timed out. The default value is 300.

The type must be -1 or an integer in the range of 1 to 2073600.

SessionTimeout

Optional

System.Int32

Specifies the time, in seconds, that a session will remain active on Excel Services with no user activity. The default is 300 seconds. The default value is 300. Valid values include:

  • -1, session never expires

  • 0, session expires at the end of a single request

  • 1 to 2073600, remain active 1 second to 24 days

The type must be -1, 0, or an integer in the range of 1 to 2073600.

ShortSessionTimeout

Optional

System.Int32

Specifies the time, in seconds, that a user has to make the initial interaction with a spreadsheet. After this time has elapsed, if the user is not active, then the session is closed. The value must always be less than SessionTimeout. If the ShortSessionTimeout value is greater than or equal to SessionTimeout, an error is returned. The default value is 75.

The type must be -1 or any positive integer.

UdfsAllowed

Optional

System.Management.Automation.SwitchParameter

Specifies that user-defined functions can be called by workbooks loaded from the trusted file location that is specified in Address.

WarnOnDataRefresh

Optional

System.Management.Automation.SwitchParameter

Specifies that a warning is displayed to the user on the first refresh of data for the workbook. The default value is true.

WhatIf

Optional

System.Management.Automation.SwitchParameter

Displays a message that describes the effect of the command instead of executing the command. For more information, type the following command: get-help about_commonparameters

WorkbookSizeMax

Optional

System.Int32

Specifies the maximum size, in MB, of a workbook that can be loaded. The default value is 10.

The type must be an integer value in the range of 1 to 2000.

Detailed Description

Use the Set-SPExcelFileLocation cmdlet to set properties of a location that Excel Services loads workbooks from. Excel Services will load only workbooks that are stored in a trusted file location. Properties of trusted file locations control how workbooks can be used when loaded on Excel Services. Excel Services always enforces the properties that are defined by the trusted file location from which a workbook was loaded. The properties that are used by the trusted file location are determined by comparing the file path for the workbook with the Address parameter of the trusted file location.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1--------------
Copy Code
Get-SPExcelFileLocation -ExcelServiceApplication "My Excel Service" | where { $_.externaldataallowed -eq DclAndEmbedded} | Set-SPExcelFileLocation -exernaldataallowed Dcl

This example gets every trusted file location for the MyExcelService application that allows data connections to load from workbook file and specifies that the locations must use a Data Connection Libarary (DCL) to load data connections.

--------------EXAMPLE 2--------------
Copy Code
Get-SPExcelServiceApplication | Get-SPExcelFileLocation | where {$_.Address -eq "http://"} | Set-SPExcelFileLocation -Description "This is the default trusted file location for Excel Services. It allows any file from anywhere in SharePoint to load."

This example changes the description of the default file location for every Excel Service application in the farm.


Set-SPExcelServiceApplication
Example
[image]

Sets global properties for an Excel Services application.

Set-SPExcelServiceApplication [-Identity <SPExcelServiceApplicationPipeBind>] [-AccessModel <Delegation | TrustedSubsystem | Claims>] [-AssignmentCollection <SPAssignmentCollection>] [-CachingOfUnusedFilesEnable <SwitchParameter>] [-Confirm [<SwitchParameter>]] [-CrossDomainAccessAllowed <SwitchParameter>] [-EncryptedUserConnectionRequired <None | Connection>] [-ExternalDataConnectionLifetime <Int32>] [-FileAccessMethod <UseImpersonation | UseFileAccessAccount>] [-LoadBalancingScheme <WorkbookUrl | RoundRobin | Local>] [-MemoryCacheThreshold <Int32>] [-PrivateBytesMax <Int32>] [-SessionsPerUserMax <Int32>] [-SiteCollectionAnonymousSessionsMax <Int32>] [-SPIisWebServiceApplicationPool <SPIisWebServiceApplicationPoolPipeBind>] [-TerminateProcessOnAccessViolation <SwitchParameter>] [-ThrottleAccessViolationsPerSiteCollection <Int32>] [-UnattendedAccountApplicationId <String>] [-UnusedObjectAgeMax <Int32>] [-WhatIf [<SwitchParameter>]] [-WorkbookCache <String>] [-WorkbookCacheSizeMax <Int32>]

Parameters

Parameter Required Type Description

Identity

Optional

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the SPExcelServiceApplication object to update.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

AccessModel

Optional

Microsoft.Office.Excel.Server.AccessModel

Specifies the type of authentication model that the Excel Services front end components use to communicate with the application server. TrustedSubSystem (TSS) specifies that a trusted account is used for the communication. Delegation specifies that the identity of the user is delegated to the back end. The default value is TSS.

Note:
In most farm configurations Delegation will require Kerberos. If the AccessModel settings is changed at runtime, all existing sessions and any backed up sessions are lost.

Note:
With both authentication models, files that are stored in Windows SharePoint are always retrieved as the user making the request so per user permissions checking is performed.

The type must be one of the following: Delegation, TSS.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

CachingOfUnusedFilesEnable

Optional

System.Management.Automation.SwitchParameter

Specifies that files that are no longer used by Excel Services can remain in the cache for later use. The default is true when the parameter is not specified on the command.

Confirm

Optional

System.Management.Automation.SwitchParameter

Prompts you for confirmation before executing the command. For more information, type the following command: get-help about_commonparameters

CrossDomainAccessAllowed

Optional

System.Management.Automation.SwitchParameter

Specifies that trusted workbooks and data connection files can be requested and rendered by Web parts or pages that reside in other HTTP domains.

EncryptedUserConnectionRequired

Optional

Microsoft.Office.Excel.Server.EncryptedConnectionMode

Requires that encryption is used between the end-user and the Excel Server. The scope of this setting is the logical server. The default values is None.

The type must be one of the following: None or Connection.

ExternalDataConnectionLifetime

Optional

System.Int32

Specifies the maximum number of seconds that an external data connection can remain open in the connection pool. Valid values include -1 to never recycle, or 1 to 2073600 to remain active 1 second to 24 days. The default value is 1800.

FileAccessMethod

Optional

Microsoft.Office.Excel.Server.FileAccessMethod

Specifies the method that Excel Services uses to retrieve files. UseFileAccessAccount specifies that all workbooks are always retrieved as Excel Services. UseImpersonation specifies that Excel Services will retrieve the workbook files as the user who is making the request. The default value is UseImpersonation.

The type must be one of the following: UseImpersonation or UseFileAccessAccount.

LoadBalancingScheme

Optional

Microsoft.Office.Excel.Server.LoadBalancingScheme

Specifies the load-balancing schema that is used by the Excel Services application for sending requests to different back-end Excel Services computers. The default value is Workbook URL.

The type must be one of the following: Round Robin, LocalWorkbook, or Workbook URL.

MemoryCacheThreshold

Optional

System.Int32

Specifies the percentage of the maximum private bytes that can be allocated to inactive objects. When the memory cache threshold is exceeded, cached objects that are not currently in use are released. Setting this value to 0 disables caching of inactive objects. The default value is 90.

The type must be an integer in the range of 0 to 95.

PrivateBytesMax

Optional

System.Int32

Specifies the maximum private bytes, in MB, used by Excel Services. When set to -1, the maximum private bytes defaults to 50% of physical memory on the computer. Valid values include -1 for no limit, and any positive integer. The default value is -1.

The type must be -1 or any positive integer.

SessionsPerUserMax

Optional

System.Int32

Specifies the maximum number of sessions allowed for a user. If a user reaches this maximum the oldest session will be deleted when a new session is started. Valid values include -1 for no limit, and any positive integer. The default value 25.

The type must be -1 or any positive integer.

SiteCollectionAnonymousSessionsMax

Optional

System.Int32

Specifies the maximum number of anonymous sessions allowed per site collection. A value of 0 specifies that no anonymous sessions are allowed. Valid values include -1 for no limit, 0 to disable the setting, or any positive integer. The default value is 25.

The type must be -1, 0, or any positive integer.

SPIisWebServiceApplicationPool

Optional

Microsoft.SharePoint.PowerShell.SPIisWebServiceApplicationPoolPipeBind

Specifies the IIS application pool name for the Excel Services application.

TerminateProcessOnAccessViolation

Optional

System.Management.Automation.SwitchParameter

Terminates the Excel Calculation Services when an access violation occurs in the process.

ThrottleAccessViolationsPerSiteCollection

Optional

System.Int32

Specifies that if a workbook causes an access violation error on Excel Services, then all files originating from that workbook's site collection will be blocked from loading for the specified period (in seconds). Existing sessions for files on that site collection are unaffected. Valid values include 0 to disable the setting, and the range of 1 to 2073600 seconds (24 days). The default value is 0.

The type must be an integer value in the range of 0 to 2073600.

UnattendedAccountApplicationId

Optional

System.String

Specifies that the Application ID that is used to lookup the Unattended Service Account credentials from the Secure Storage Service that is specified by UnattendedAccountSecureServiceAppName. The Secure Storage Service is contacted when this parameter is set and specific error cases occur. Valid values are alphanumeric strings with a maximum of 256 characters. The default value is an empty string.

The type must be a valid string of alphanumeric characters; for example, UnattendedServiceAccountID.

Note:
The Unattended Account is a single account that all workbooks can use to refresh data. An Unattended Account is required when workbook connections specify None for authentication, or when any non-Windows credentials are used to refresh data.

UnusedObjectAgeMax

Optional

System.Int32

Specifies the maximum amount of time, in minutes, that objects not currently used in a session are kept in the memory cache. Valid values include -1 for no maximum setting, and a positive integer in the range of 1 to 34560 seconds (24 days). The default value is -1.

The type must be -1, or an integer in the range of 1 to 34560.

WhatIf

Optional

System.Management.Automation.SwitchParameter

Displays a message that describes the effect of the command instead of executing the command. For more information, type the following command: get-help about_commonparameters

WorkbookCache

Optional

System.String

Specifies the local file system location of the cache that is used for storing workbooks used by Excel Services.

The type must be a valid path in either of the following forms:

  • C:\folder_name

  • \\server_name\folder_name

WorkbookCacheSizeMax

Optional

System.Int32

Specifies the maximum allowable size, in MB, of an individual session. The default value is 0.

The type must be an integer in the range of 0 to 4095.

Detailed Description

Use the Set-ExcelService cmdlet to change global runtime properties for Excel Services. Changes to properties made using this cmdlet affect all machines in the farm that are running an instance of the specified Excel Service application.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1--------------
Copy Code
Set-SPExcelServiceApplication ExcelServiceTestApplication -sessionsperusermax 10

This example sets the Excel Service application named ExcelServiceTestApplication to enable each user to open no more than 10 sessions at time on a back end application server running Excel Services.

--------------EXAMPLE 2--------------
Copy Code
Get-SPExcelServiceApplication | %where{ $_.privatebytesmax -ne -1 } | Set-SPExcelService -privatebytesmax -1

This example displays every Excel Service application running in the farm that is using a user-specified value for the maximum number of private bytes of memory on the computer, and then sets that value to -1, which indicates Excel Services will consume 50% of the available memory resources.


Set-SPExcelUserDefinedFunction
Example
[image]

Sets properties of a user defined function in an Excel Services application.

Set-SPExcelUserDefinedFunction -Identity <SPExcelUDFPipeBind> -ExcelServiceApplication <SPExcelServiceApplicationPipeBind> [-Assembly <String>] [-AssemblyLocation <GAC | LocalFile>] [-AssignmentCollection <SPAssignmentCollection>] [-Confirm [<SwitchParameter>]] [-Description <String>] [-Enable <SwitchParameter>] [-WhatIf [<SwitchParameter>]]

Parameters

Parameter Required Type Description

Identity

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelUDFPipeBind

Specifies the SPExcelUserDefinedFunction object to update.

The type must be a valid name of the code assembly; for example, SampleCompany.SampleApplication.SampleUdf; or an instance of a valid SPExcelUDF object.

ExcelServiceApplication

Required

Microsoft.Office.Excel.Server.Cmdlet.SPExcelServiceApplicationPipeBind

Specifies the Excel Service Application that contains the SPExcelUserDefinedFunction list object.

The type must be a valid GUID, in the form 12345678-90ab-cdef-1234-567890bcdefgh; a valid name of an Excel Services application in the farm (for example, MyExcelService1); or an instance of a valid SPExcelServiceApplication object.

Assembly

Optional

System.String

Specifies the strongly-typed name of the assembly. The name must be unique in the list of user-defined functions, and cannot be more than 4096 alphanumeric characters long.

The type must be a string of alphanumeric characters; for example, SampleCompany.SampleApplication.SampleUdf, C:\UDFs\SampleUdf.dll, or \\MyNetworkServer\UDFs\SampleUdf.dll.

AssemblyLocation

Optional

Microsoft.Office.Excel.Server.AssemblyLocation

Specifies the location where the assembly is stored.

The type must be one of the following: GAC or LocalFile.

AssignmentCollection

Optional

Microsoft.SharePoint.PowerShell.SPAssignmentCollection

Manages objects for the purpose of proper disposal. Use of objects, such as SPWeb or SPSite, can use large amounts of memory and use of these objects in Windows PowerShell scripts requires proper memory management. Using the SPAssignment object, you can assign objects to a variable and dispose of the objects after they are needed to free up memory. When SPWeb, SPSite, or SPSiteAdministration objects are used, the objects are automatically disposed of if an assignment collection or the Global parameter is not used.

Note:
When the Global parameter is used, all objects are contained in the global store. If objects are not immediately used, or disposed of by using the Stop-SPAssignment command, an out-of-memory scenario can occur.

Confirm

Optional

System.Management.Automation.SwitchParameter

Prompts you for confirmation before executing the command. For more information, type the following command: get-help about_commonparameters

Description

Optional

System.String

Specifies a display description for the user-defined function. The name cannot be more than 4096 alphanumeric characters long.

The type must be a string of alphanumeric characters; for example, this is the user defined function for consolidation reports.

Enable

Optional

System.Management.Automation.SwitchParameter

Activates the user defined function for use with Excel Services.

WhatIf

Optional

System.Management.Automation.SwitchParameter

Displays a message that describes the effect of the command instead of executing the command. For more information, type the following command: get-help about_commonparameters

Detailed Description

Use the Set-SPExcelUserDefinedFunction cmdlet is used to set properties of a user defined function assembly. Excel Services uses only user defined functions that have an entry in the user defined functions list. User defined functions are managed code assemblies that can be called from an Excel workbook using standard Excel formula syntax. The assemblies can perform custom logic or other actions, such as refresh data. PowerShell cmdlets operate on the entries in the user defined function list and do not operate on the actual assemblies.

Input Types

Return Types

Errors

Error | Description

Exceptions

Exceptions | Description

Example

--------------EXAMPLE 1--------------
Copy Code
Set-SPExcelUserDefinedFunction -ExcelServiceApplication "My Excel Service" -Identity  SampleCompany.SampleApplication.SampleUdf -Description "This is the new description for the sample UDF"

This example changes the description of the user-defined function (UDF) for the UDF SampleCompany.SampleApplication.SampleUdf file for the Excel Service application named "My Excel Service".

--------------EXAMPLE 2--------------
Copy Code
Get-SPExcelServiceApplication "My Excel Service" | Set-SPExcelUserDefinedFunction -Identity  SampleCompany.SampleApplication.SampleUdf -Enable: $false

This example disables the sample UDF SampleCompany.SampleApplication.SampleUdf file from the Excel Service named "My Excel Service".