Excel2007的自定義工作表函數（第一部分）

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)附帶的組件。

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 NonVolatileMethod() ??????? { ? … ??????? } ??????? [UdfMethod(IsVolatile=true)] ??????? public VolatileMethod() ??????? { ? … ??????? } ??? } }

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

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。