Excel2007的自定义工作表函数（第一部分）

译者：taller   来源：http://blogs.msdn.com/excel
发表于：2006年7月7日

Excel 2007 investments in UDFs #1
EXCEL 2007的自定义工作表函数（第一部分）

For the next few posts, we have a “guest post” from Danny Khen, a program manager on the Excel Services team.  Danny is going to talk about UDFs in Excel and Excel Services.  Enjoy.
在接下来的几个帖子中，Excel Services团队的项目经理Danny Khen将讲述Excel和Excel Services中的自定义工作表函数，希望大家喜欢。

UDFs are user-defined worksheet functions – custom functions that you create to supplement Excel’s set of intrinsic worksheet functions. UDFs are used to create calculation libraries, or to import data into Excel sheets in custom ways.
UDF就是自定义工作表函数的缩写，就是用户创建的用于满足特定需求的函数，它是Excel内置工作表函数的一个补充，UDF可以用于生成一个计算库或者按照用户定义的方式将数据导入Excel工作表。

In Excel 2007, we’ve made a number of key investments around UDFs. They revolve around two main areas: allowing UDFs to take advantage of important Excel improvements, and extending UDF-based Excel solutions to the server side with Excel Services.
在Excel 2007中，我们围绕UDF花费了很多精力，主要有两个方面：Excel的重大改进可以应用于UDF和利用Excel Services将基于UDF的Excel解决方案扩展到服务器端。

Updated XLLs
更新XLLs

A while back Dave mentioned that we updated XLLs (Excel’s addins based on the C-API) to give developers access to new Excel functionality. As that post explained, one of the common things that developers do in XLLs is to implement UDFs. We wanted to make sure that XLL authors can make use of some great new features of Excel 2007 itself in the UDFs they create. To recap, XLLs have support in Excel 2007 for:
正如Dave前面提到的一样，我们更新XLLs（基于C-API的Excel加载宏）以便于开发者使用Excel的新功能，对于开发者来说，在XLLs中实现UDF成为一件很普通的事情，我们希望确保XLL作者可以在UDF中使用Excel2007那些伟大的新功能，简单的说，Excel 2007 XLLs支持：

The bigger grid
更大的数据表

More function arguments
更多的函数参数

Multi-threaded calculation
多线程计算

There are many more detailed about those improvements in that other post.
在另外的帖子中会更加详细的解释这个改进。

Server-side UDFs
服务器端的UDF

Dave has also posted a number of entries about Excel Services – the new feature in the Office SharePoint Server 2007 that enables calculation, display, and exploration of Excel workbooks on the server. These posts cover many aspects of Excel Services.
Dave已经发表了好几个关于Excel Services的帖子，这些帖子涵盖了很多Excel Services的概念，简单的说就是： Office SharePoint Server 2007 提供的新功能，可以在服务器上实现计算、显示和访问Excel工作簿。

Much like Excel’s ability to be extended by writing UDFs in Excel addins, Excel Services also has an extensibility mechanism for writing UDFs. I’d like to use the rest of this post to explain and demonstrate Excel Services UDFs. In a follow-up couple of posts, I will show how you can create Excel solutions that use UDFs and can run both on a client machine using Excel 2007 and in a server environment using Excel Services.
就像可以通过在Excel加载宏中使用UDF扩展Excel功能一样，Excel Services同样可以使用UDF的扩展机制，我希望利用下面的帖子来解释和演示Excel Services UDF， 在接下来的几个帖子中，我将向大家展示如何利用UDF实现Excel解决方案，此方案不仅可以用于Excel 2007的客户端，而且可以用于具备Excel Services环境的服务器。

They’re managed
托管代码

Server-side UDFs are implemented as methods .NET 2.0 assemblies. That is to say, Excel Services directly supports only managed code UDFs. Existing native function libraries and Excel UDFs can be used with Excel Services by “wrapping” them with the new style of server managed UDFs; I will show how in the follow-up posts.
服务器端的UDF应用了.NET 2.0组件，也就是说Excel Services可以而且仅仅支持托管代码UDF，已经在用的函数库和Excel UDF可以在服务器端托管UDF中共存，在下面的帖子中将进行介绍。

But why did we actually “go managed”? Excellent question. Managed code for enterprise-level solutions is becoming more and more popular, because of the many advantages that .NET code has to offer. Robustness and security are among the important advantages. Some of you may already be engaged in developing managed UDFs or other types of managed solutions. With Excel Services, we focused specifically on server stability, and we felt that using .NET as the basis for our extensibility would be the right thing to do in this respect.
为什么要使用托管代码呢？这是一个非常好的问题，托管代码在企业级解决方案中越来越广泛的应用，原因在于.NET代码有很多优势，健壮和安全是其中最重要的优势，你们中某些人可以已经投入托管UDF或者提供托管方案的开发之中，对于Excel Service我特别关注服务器的稳定性，因此我们觉得以.NET为基础进行扩展是完全正确的。

They’re part of a V1 feature
他们将成为V1功能的一部分

In Office SharePoint Server 2007 we introduce the first implementation of Excel Services and of its extensibility mechanism. To start with, we simply had to prioritize the support for various features in Excel Services (even regardless of UDFs), and not everything made it for this first version. Moreover, we know that going forward we will be doing a lot of thinking around managed interfaces and extensibility for Excel – both on the client side and on the server. We want to make sure that customers’ initial investment in managed UDFs is secured, and that at the same time we are not constrained in any way when we design the best possible infrastructure in the future. For those reasons, there are certain restrictions with the first version of server UDFs:
在Office SharePoint Server 2007中我们第一次引入了Excel Services和扩展机制，开始时，我们不得不对Excel Services的各种功能（即使忽略UDF）进行逐个处理，因此并非所有的功能都会在第一个版本中提供，此外，我们深知无论是客户端还是服务器端，都有非常多的关于托管接口和Excel扩展的事情等着我们去做，我们希望尽量保护用户托管UDF的投资，同时也希望我们在将来可能设计出的更好的架构，不会过多的影响现在的客户，鉴于上面的原因，在第一版服务器UDF中会有某些限制。

No Excel OM: the entire interface with the Excel sheet is done thru the UDF call signature; arguments are passed into the function from the Excel formula, and return values are passed back into the formula.
Excel工作表的接口全部通过UDF调用标识，参数由Excel公式传递到函数，返回值传回公式。

More restrictive than Excel addins with
与Excel加载宏相比有更多的限制：

Type conversion and supported data types.
类型转换和所支持的数据类型

Error handling: all exceptions thrown by the UDF code are returned into the Excel sheet as #VALUE errors.
错误处理：所有的UDF代码的异常反应在Excel工作表中都是#VALUE错误。

Simple load / runtime behavior: for example, all UDFs currently run together with Excel Services code in the same .NET application domain.
简单的装载/运行：例如，现在所以的在Excel Services代码中的UDF存在于同一个.NET应用域中。

We believe that lots of useful solutions can be created under these restrictions; in fact we see many existing Excel UDF-based solutions that could comply.
我们详细基于这些限制可以产生很多有用的解决方案，事实上我们可以看到很多现存的基于UDF的Excel解决方案可以借鉴。

They need to be thread-safe
安全线程

Excel Services is a server feature, and its calculation engine runs as a multi-threaded backend service. We have to require (and assume) that all UDFs are thread safe.
Excel Services是服务器功能，它的计算引擎提供多线程后台服务，我们不得不要求所有的UDF都是安全的线程

Security
安全

A couple of things to mention about the way a server administrator can control the security of Excel Services UDFs.
服务器管理员可以从如下几个方面控制Excel Services UDF的安全性。

First, Excel Services will not load and run just any old method in any old .NET assembly. An admin needs to register the assembly on a server list of trusted UDF assemblies.
首先，Excel Services将不会装载和运行任何存在于以前.NET组件中的旧的方法，管理员需要在服务器上注册信任的UDF组件。

On top of that, since these are .NET assemblies, an admin can make use of .NET’s CAS (Code Access Security) infrastructure to restrict UDF access to resources. For example, if a UDF package is simply a collection of math calculation functions, an admin can turn off its ability to access web services, external data, native code etc – and make the server environment safer for everyone.
其次，因为使用了.NET组件，所以管理员可以利用.NET’s CAS (代码访问安全) 限制UDF访问资源，例如：对于一个简单的数学计算函数的UDF包，管理员可以禁止它使用网络服务，外部数据，原始代码等功能，使得服务器环境对每个人都更安全。

So what does this thing look like?
UDF的真面目

Very simple, actually. All you need to know about (assuming you speak some .NET dialect) is two new attributes. Both of them are defined in the Microsoft.Office.Excel.Server.Udf namespace, and you need to reference an assembly that is shipped with Excel Services (Microsoft.Office.Excel.Server.Udf.dll) to get them.
实际上很简单，你所要知道的只有两个新的属性，它们都要在Microsoft.Office.Excel.Server.Udf名称空间中定义，你可以参考Excel Services (Microsoft.Office.Excel.Server.Udf.dll)附带的组件。

The two attributes are UdfClass, which you use to mark a class where server UDFs are defined, and UdfMethod, with which you mark each individual public method to be considered a server UDF. The UdfMethod also has a boolean property – IsVolatile – used to declare the UDF as volatile, if you want the server to call it each time it recalculates the workbook, regardless of any change in its dependencies. The default is false, which means that the method is non-volatile; it gets called only when a value changes somewhere down the dependency chain of the formula that calls the UDF.
这两个属性是UdfClass（用于标识服务器UDF定义的类）和UdfMethod（用于标识每个共用的方法都是一个服务器UDF）,UdfMethod也有一个布尔类型属性－IsVolatile－用于声明UDF具有易失性，如果你希望服务器每次调用时，不管是否有改变工作簿都会重新计算，就要设置此参数为True，该参数的默认值为False，也就是说该方法是非易失性的，在这种情况下，只有与公式相关的改变才会调用UDF.

That’s really it. A typical UDF class will look something like this:
一个典型的UDF类就像下面所示：

//Code
using Microsoft.Office.Excel.Server.Udf;

namespace YourNamespace
{
    [UdfClass]
    public class YourClass
    {
        [UdfMethod]
        public <return-type> NonVolatileMethod(<arguments>)
        {
  …
        }
        [UdfMethod(IsVolatile=true)]
        public <return-type> VolatileMethod(<arguments>)
        {
  …
        }
    }
}

Show us the goods!

Well, alright… Attached to this blog post is a fully developed code sample. It shows a UDF package by the name of WishExcelHad (because it implements a couple of functions that we wish Excel had…). The package has two method to manipulate text strings with words. The first method, WehWordM, takes the string, a required word position, and a delimiter string, and returns the word in that position after parsing the text with the given delimiter. The other method, WehWordcountM, takes a string and a delimiter, and returns the number of words in that string, when it is parsed with the given delimiter.
博客帖子中的代码是一个完整的开发代码示例，其名称为WishExcelHad的UDF包，这个UDF保有两种使用文本字符串的方法，第一种方法，利用WehWordM，取得字符串、单词位置和分隔符，根据指定的分隔符返回分析的文本；另一种方法，利用WehWordcountM，取得字符串和分隔符，根据指定的分隔分析并返回该字符串中的单词个数。

You can use the attached Excel workbook as an example that calls these two methods in formulas. You’ll need to save it to Excel Services as an XLSX file.
你可以用附带的Excel工作簿为例子在公式中调用这两个方法，并以XLSX文件保存到Excel Services中。

Why the weird “M” as the suffix for those method names? It designates “Managed” – these UDFs are implemented purely with managed code, and are intended to run with Excel Services. Stay tuned to the blog and look for the following posts, where I will show versions of the same UDFs, that can run across Excel 2007 and Excel Services.
为什么我们用“M”作为这些方法的名字的前缀呢？它代表“托管”－这些UDF完全有托管代码实现，将应用于Excel Service，在接下来的帖子中，我将向大家展示UDF的不同版本可以分别运行在Excel 2007和Excel Services中。

Published Wednesday, May 03, 2006 6:14 AM by David Gainer
Filed Under: Excel Server, UDFs, Programmability

注：本文翻译自http://blogs.msdn.com/excel ，原文作者为David Gainer（a Microsoft employee），Excel Home 授权转载。严禁任何人以任何形式转载，违者必究。