Understanding Excel Services UDFs

Article
03/21/2023

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.

Excel Services 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.
Get data from custom data sources into worksheets.
Appeler des services Web à partir des fonctions définies par l'utilisateur.

Création de fonctions définies par l'utilisateur en code managé

An easy way to create an Excel Services managed-code UDF is to use the Microsoft Visual Studio 2005 class library template. You will need to reference the Excel Services 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. Il est impossible pour un assembly créé avec une version antérieure de .NET Framework de référencer un assembly créé avec .NET Framework 2.0.

Attributs requis

To use custom functions in a class as an Excel Services 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 Services de calcul Excel. They are not considered to be Excel Services UDF classes.

To use custom functions in a class as Excel Services 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 Excel Services 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.

Emplacement du fichier 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

Déploiement et sécurité

Type d'emplacement de déploiement

UDF assemblies can reside in a local directory, global assembly cache, or network share. Dans un scénario de batterie, le chemin du répertoire local doit être identique dans la batterie.

Identification des assemblys UDF

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

Par exemple, vous pouvez utiliser :

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

Activation des assemblys UDF

UDF assemblies are disabled by default.

Each Excel Services trusted location has an AllowUdfs flag.

Remarque

[!REMARQUE] The AllowUdfs flag is denoted by the User-defined functions allowed option on the Excel Services 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.

Autorisation de l'exécution des assemblys UDF

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.

Rechargement d'un assembly UDF

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

Attention: La réinitialisation d’IIS met fin à toutes les sessions en cours. > Pour plus d’informations, consultez Guide pratique pour activer les fonctions définies par l’utilisateur.

Pour plus d’informations, consultez Déchargement d’une application à partir de la mémoire (https://msdn.microsoft.com/library/default.asp?url=/library/csvr2002/htm/cs_mmc_administering_myhj.asp).

Autorisation de sécurité d'accès au code par défaut pour les assemblys UDF

Par défaut, les assemblys UDF s'exécutent avec une autorisation totale.

Restriction de l'autorisation de sécurité d'accès au code pour les assemblys 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: