Understanding Excel Services UDFs

User-defined functions (UDFs) are custom functions that extend the calculation and data-import capabilities of Excel. Developers create custom calculation packages to provide:

Functions that are not built into Excel.
Custom implementations to built-in functions.
Custom data feeds for legacy or unsupported data sources, and application-specific data flows.

Users who create workbooks can call UDFs from a cell through formulasfor example, "=MyUdf(A1*3.42)"just like they call built-in functions.

Servicios de Excel UDFs give you the ability to use formulas in cells to call custom functions written in managed code and deployed to Microsoft SharePoint Server 2010. You can create UDFs to:

Call custom mathematical functions.
Obtener datos de orígenes de datos personalizados en hojas de cálculo.
Llamar a servicios web desde las UDF.

Creación de UDF de código administrado

An easy way to create an Servicios de Excel managed-code UDF is to use the Microsoft Visual Studio 2005 class library template. You will need to reference the Servicios de Excel UDF dynamic link library (DLL), named Microsoft.Office.Excel.Server.Udf.dll, in your managed-code UDF project.

Microsoft.Office.Excel.Server.Udf.dll has been compiled using Microsoft .NET Framework 2.0. If you use Visual Studio 2003 to create your managed-code UDF, you will not be able to reference Microsoft.Office.Excel.Server.Udf.dll. Un ensamblado creado con una versión anterior de .NET Framework no puede hacer referencia a un ensamblado creado con .NET Framework 2.0.

Atributos obligatorios

To use custom functions in a class as an Servicios de Excel UDF class, you must mark your UDF class with the Microsoft.Office.Excel.Server.Udf.UdfClass attribute. Any classes that are not marked with this attribute in the UDF assembly will be ignored by Excel Calculation Services. They are not considered to be Servicios de Excel UDF classes.

To use custom functions in a class as Servicios de Excel UDF methods, you must mark your UDF methods with the Microsoft.Office.Excel.Server.Udf.UdfMethod attribute. Any methods that are not marked with this attribute in the UDF assembly will be ignored because they are not considered to be Servicios de Excel UDF methods.

The Microsoft.Office.Excel.Server.Udf.UdfMethodattribute has an IsVolatile property. You use the IsVolatile property to specify a UDF method as volatile or nonvolatile. The IsVolatile property takes a Boolean value. The default value is false, which means that particular UDF method is nonvolatile.

Ubicación de Microsoft.Office.Excel.Server.Udf.dll

On the computer where you have installed SharePoint Server 2010, you can find a copy of Microsoft.Office.Excel.Server.Udf.dll at:

[drive:]\\Program Files\\Common Files\\Microsoft Shared\\web server extensions\\14\\ISAPI

Implementación y seguridad

Tipo de ubicación de la implementación

UDF assemblies can reside in a local directory, global assembly cache, or network share. En un escenario de granja de servidores, la ruta de acceso al directorio local debe ser idéntica en toda la granja de servidores.

Identificación de los ensamblados de UDF

You can expose the identity of a UDF assembly by using the full path or strong name of the assembly for Excel Calculation Services to call.

For example, you can use:

C:\UDFs\MySampleUdf.dll
\\MyNetworkServer\UDFs\MySampleUdf.dll
CompanyName.Hierarchichal.MyUdfNamespace.MyUdfClassName.dll, Version=1.1.0.0, Culture=en, PublicKeyToken=e8123117d7ba9ae38

Habilitar ensamblados de UDF

UDF assemblies are disabled by default.

Each Servicios de Excel trusted location has an AllowUdfs flag.

Nota:

[!NOTA] The AllowUdfs flag is denoted by the User-defined functions allowed option on the Servicios de Excel Trusted File Locations page. To learn how to navigate to the Trusted File Locations page, see Step 3: Deploying and Enabling UDFs.

The default AllowUdfs value is false. If the AllowUdfs value is set to false in a particular trusted location, the workbooks in that trusted location are not allowed to call UDFs.

In order to allow UDFs to be called from a specific trusted location, you set the AllowUdfs value to true.

If the AllowUdfs value is false when a session is started on a workbook that has UDF calls in this trusted location, the UDF calls will fail. If you change the AllowUdfs value to true after a session has started, the UDF calls will also fail. This is because changes in the AllowUdfs flag take effect on the next session, after the configuration database has been updated.

Permitir que los ensamblados de UDF se ejecuten

If administrators want to allow UDF assemblies to run, they have to register all UDF assemblies, and enable workbooks to call UDFs by setting the AllowUdfs flag to true in the trusted locations.

Volver a cargar un ensamblado de UDF

To reload a UDF assembly, you can run iisreset or restart the Excel Calculation Services application domain.

Precaución: El restablecimiento de IIS finalizará todas las sesiones actuales. > Para obtener más información, vea How to: Enable UDFs (Cómo: Habilitar UDF).

Para obtener más información, vea Descargar una aplicación de memoria (https://msdn.microsoft.com/library/default.asp?url=/library/csvr2002/htm/cs_mmc_administering_myhj.asp).

Permiso de seguridad de acceso de código predeterminado para los ensamblados de UDF

De forma predeterminada, los ensamblados de UDF se ejecutan con plena confianza.

Restricción del permiso de seguridad de acceso de código para los ensamblados de UDF

If you do not want a particular UDF assembly to run with full trust, you must explicitly restrict code access security permission for that UDF assembly. You can configure the code groups and restrict permission by using the .NET Framework 2.0 Configuration tool.

Developers can also use the RequestMinimum and RequestOptional methods in their code to ensure that their UDF assemblies don't get more permission than they require.

For more information about configuring code groups, as well as the RequestMinimum and RequestOptional methods, see the following articles on MSDN: