Excel自定义函数
- 格式：pdf
- 大小：212.39 KB
- 文档页数：6
Excel⾃定义函数
我们知道，Excel中有很多内置的函数，⽐如求和，求平均，字符串操作函数，⾦融函数等等。在有些时候，结合业务要求，这些函数可
能不能满⾜我们的需求，⽐如我想要⼀个函数能够从WebService上获取某只股票的最新价；我想要⼀个函数能够获取当前的天⽓情况，这
些需求我们可以通过编写Excel⾃定义函数(User Define Function ,UDF )来实现，这样，在Excel中直接调⽤我们的⾃定义函数即可满⾜特定
的业务需求，⼀般地，因为这种⾃定义函数的粒度相对较⼩，所以我们可以根据业务需求编写很多基础的⾃定义函数，然后以这些⾃定义函
数为基础，编写各种复杂的分析报表。
编写UDF的⽅式有很多种，⽐如直接在VBA种编写⾃定义函数；如果您熟悉C++，可以将⾃定义函数编写到XLL中，不熟悉也可以使⽤
ExcelDNA这个开源的库使⽤.NET技术也可以将您的代码编译为XLL；如果熟悉.NET，使⽤C#编写⾃定义函数类库，然后将类库注册成
Com组件也可在Excel中调⽤。下⾯就这⼏种⽅式简要介绍，并给出其优缺点。
1. 使⽤C# 类库注册的⽅式实现Excel⾃定义函数
我⾃⼰对.NET 较熟悉，所以⾸先介绍这种在.NET中即可进⾏Excel⾃定义函数开发的模式，这种⽅法相对简单。在开始之前，还是回到
我们对YY插件的规划，我们的YY插件有天⽓，财经，地图等功能，现在我们假设需要⼀个天⽓⾃定义函数，通过该函数能够获取某个城市
某⼀天的天⽓情况，⽐如说⽓温。
⾸先我们需要创建⼀个简单的C#类库，如下图，其名为YYWeatherUDF。
然后，我们创建⼀个所有⾃定义函数的基类UDFBase.cs，在该类中，我们放⼀些基本的注册Com组件所需要的⼀些操作以及屏蔽⼀些
Object的对象的⽅法使其不要出现在Excel的UDF函数中来。有⼀点需要注意的是，在注册及取消注册为Com组件的时候，为避免Excel找不
到mscoree.dll，需要往注册表中写⼊其全部路径，下⾯的代码即为实现这⼀功能。
public abstract class UDFBase
{
///
/// 解决在某些机器的Excel提⽰找不到mscoree.dll的问题
/// 这⾥在注册表中将该dll的路径注册进去，当使⽤regasm注册该类库为com组件
/// 时会调⽤该⽅法
///
///
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(
GetSubKeyName(type, "Programmable"));
RegistryKey key = Registry.ClassesRoot.OpenSubKey(
GetSubKeyName(type, "InprocServer32"), true);
key.SetValue("", System.Environment.SystemDirectory + @"\mscoree.dll",
RegistryValueKind.String);
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(
GetSubKeyName(type, "Programmable"), false);
}
private static string GetSubKeyName(Type type, string subKeyName)
{
return string.Format("CLSID\\{{{0}}}\\{1}", type.GUID.ToString().ToUpper(), subKeyName);
}
}
///
/// 将Object类的四个公共⽅法隐藏
/// 否则将会出现在Excel的UDF函数中
///
///
[ComVisible(false)]
public override string ToString()
{
return base.ToString();
}
[ComVisible(false)]
public override bool Equals(object obj)
{
return base.Equals(obj);
}[ComVisible(false)]
public override int GetHashCode()
{
return base.GetHashCode();
}
以上这些代码是⼀个在C# 创建⼀个UDF类的基本代码，我们可以将该代码保存为基类。
现在我们要编写我们⾃⼰的实际的UDF函数了，新建⼀个名为WeatherFunc的类，继承⾃上⾯的UDFBase抽象类，并在类的属性上添加
⼀些⾃定义属性，这些属性在后⾯注册为Com组件的时候需要⽤到。ClassInterface类的构造函数中参数ClassInterfaceType.AutoDual表⽰
两边都传递，这就回是的Object类的所有公共⽅法都会显⽰出来，如果采⽤实现接⼝的⽅式，将该熟悉设置为None即可解决多余⽅法出现
在UDF列表中的问题，这⾥不细讲，继续。
[Guid("5268ABE2-9B09-439d-BE97-2EA60E103EF6")]
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public class WeatherFunc : UDFBase
{
public WeatherFunc()
{ }
}
其中Guid可以使⽤Visual Studio⾃带的⼯具⽣成，如果我们不指定，则Visual Studio在将该类注册为Com组件时，调⽤regasm⼯具的时
候会⾃动⽣成⼀个Guid，在开发过程中，⼀般我们会⼿动指定⼀个Guid，这样在调试的时候我们可以根据Guid在注册表中去查找该项，看
是否正常注册为了Com组件。
///
/// 根据城市和⽇期，返回当天的天⽓描述
///
/// 城市
/// ⽇期
///
public String YY_Weather_Condition(String city, DateTime day)
{
Weather weather = new Weather(city, Weather.TemperatureUnits.Celcius);
return weather.Condition.Text;
}
///
/// 根据城市和⽇期，返回当天的⽓温状况
///
/// 城市
/// ⽇期
///
public double YY_Weather_Temperature(String city, DateTime day)
{
Weather weather = new Weather(city, Weather.TemperatureUnits.Celcius);
return weather.Condition.Temperature;
}
///
/// 根据城市和⽇期，返回当天的风速
///
/// 城市
/// 城市
///
public double YY_Weather_WindSpeed(String city, DateTime day)
{
Weather weather = new Weather(city, Weather.TemperatureUnits.Celcius);
return weather.Wind.Speed;
}
这样我们的三个UDF函数已经写好了，然后右击⼯程项⽬，在属性设置-〉⽣成 注册为Com组件。
这⾥注册为Com组件时，Visual Studio其实是去调⽤regasm 将类库注册为Com组件，并在注册表⾥⾯写⼊⼀些信息。在发布到客户端上
部署的时候，我们可以直接导⼊该类库的相关注册表信息，或者直接调⽤regasm在客户的机器上写⼊这些信息，在后⾯讲到Excel插件安装
部署的时候，会说到这些。
然后我们直接进⾏编译。因为注册Com组件涉及对注册表进⾏操作，在XP以上系统中，需要管理员权限，如果当前Visual Studio不是以
管理员⾝份运⾏。则会出现下⾯错误：
保存退出，然后右键选择以管理员权限打开VS，然后重新编译。
完成之后，我们根据之前的GUID 5268ABE2-9B09-439d-BE97-2EA60E103EF6 到注册表中去查找，可以看到，编译并注册为Com组件
后会到注册表中写⼊⼀些信息，这些情况在安装Office等软件的时候，最后⼀步的时候，⼀般可以看到正在注册组件，然后后⾯是不停闪动
GUID，应该是在做相同的⼯作。我机器上截图如下。
现在我们打开Excel软件，在开发⼯具-〉加载项中，将我们的⾃定义函数加载进来。
现在，在公式->插⼊函数中可以看到我们编写的⾃定义函数。
