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.

Службы 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.
загрузки на листы данных из специальных источников;
вызова веб-служб из пользовательских функций.

Создание UDF с использованием управляемого кода

An easy way to create an Службы Excel managed-code UDF is to use the Microsoft Visual Studio 2005 class library template. You will need to reference the Службы 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. Сборка, созданная в предыдущей версии .NET Framework, не может ссылаться на сборку, созданную с помощью .NET Framework 2.0.

Обязательные атрибуты

To use custom functions in a class as an Службы 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. They are not considered to be Службы Excel UDF classes.

To use custom functions in a class as Службы 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 Службы 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.

Расположение библиотеки 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

Развертывание и безопасность

Тип расположения развертывания

UDF assemblies can reside in a local directory, global assembly cache, or network share. При использовании в ферме путь к локальному каталогу должен быть одинаков для всех участников фермы.

Идентификация сборок UDF

You can expose the identity of a UDF assembly by using the full path or strong name of the assembly for Службы вычислений Excel 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

Включение сборок UDF

UDF assemblies are disabled by default.

Each Службы Excel trusted location has an AllowUdfs flag.

Примечание.

[!Примечание] The AllowUdfs flag is denoted by the User-defined functions allowed option on the Службы 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.

Разрешение выполнения сборок 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.

Перезагрузка сборки UDF

To reload a UDF assembly, you can run iisreset or restart the Службы вычислений Excel application domain.

Осторожностью: Сброс IIS завершит все текущие сеансы. > Дополнительные сведения см. в разделе Практическое руководство. Включение определяемых пользователем функций.

Дополнительные сведения см. в разделе Выгрузка приложения из памяти (https://msdn.microsoft.com/library/default.asp?url=/library/csvr2002/htm/cs_mmc_administering_myhj.asp).

Разрешение разграничения доступа кода по умолчанию для сборок UDF

По умолчанию сборки UDF выполняются с уровнем "полное доверие".

Ограничение разрешения разграничения доступа кода для сборок 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: