如何给Excel添加用户自定义的公式

如何给Excel添加用户自定义的公式

Excel允许我们自定义公式。过去的方法是通过一种叫XLL的特殊DLL来实现。但是XLL不能用托管代码来写。现在，用托管代码如何实现自定义公式呢？很简单，Excel支持一种叫自动化插件的技术，可以使用C#和VB.NET开发。

1． 打开Visual Studio 2005创建一个新的C#类库项目，取名叫AutomationAddIn。

2． 然后在Class1.cs中添加如下的代码

using System;
using System.Runtime.InteropServices;
using Microsoft.Win32;

namespace AutomationAddin
{
  [ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
  public class MyFunctions
  {
    public MyFunctions()
    {

    }

    public double MultiplyNTimes(double Number1, double Number2, double TimesToMultiply)
    {
      double result = Number1;
      for (double i = 0; i < TimesToMultiply; i++)
      {
        result = result * Number2;
      }

      return result;
    }

    [ComRegisterFunctionAttribute]
    public static void RegisterFunction(Type type)
    {
      Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
    }

    [ComUnregisterFunctionAttribute]
    public static void UnregisterFunction(Type type)
    {
      Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type),false);
    }

private static string GetSubKeyName(Type type)
    {
      string s = @"CLSID/{" + type.GUID.ToString().ToUpper() + @"}/Programmable";
      return s;
    }
  }
}

3． 点击菜单Project-〉Properties，选中Build页，勾上Register for COM interop.

4． 编译整个类库项目

5． 启动Excel，调出Add Ins对话框(03，07有不同，见文后PS)

6． 点击Automation按钮，将出现Automation Servers对话框，选中我们刚刚建的AutomationAddIn.MyFunctions (此处为刚刚类库项目的命名空间名+类名)

7． 点击OK按钮(这时会出现一个对话框，选择否)，将回到Add Ins对话框

8． 点OK按钮。

9． 测试我们的函数。在一个sheet中A1输入3，A2输入4，A3输入5。选中A4，点击公式栏中的fx按钮，跳出公式选择对话框。在分类下拉框中选择AutomationAddIn.MyFunctions, 然后选中公式MultiplyNTimes。点击OK

10． 跳出函数参数对话框，分别输入A1，A2，A3，点击OK

11． 回到工作表格，如下 3072 = 3*4*4*4*4*4（5次）

12． 由此可见,自定义的公式生效了，事实上我们还可以在类库中添加进一些更加有用的功能。随便列出几个如下：

public string GetStars(double Number)
{
  string s = "";
  for (double i = 0; i < Number; i++)
  {
    s = s + "*";
  }
  return s;
}

public double AddNumbers(double Number1, [Optional] object Number2, [Optional] object Number3)
{
  double result = 0;
  result += Convert.ToDouble(Number1);

  if (!(Number2 is System.Reflection.Missing))
  {
    Excel.Range r2 = Number2 as Excel.Range;
    double d2 = Convert.ToDouble(r2.Value2);
    result += d2;
  }

  if (!(Number3 is System.Reflection.Missing))
  {
    Excel.Range r3 = Number3 as Excel.Range;
    double d3 = Convert.ToDouble(r3.Value2);
    result += d3;
  }

  return result;
}

public double CalculateArea(object Range)
{
  Excel.Range r = Range as Excel.Range;
  return Convert.ToDouble(r.Width) + Convert.ToDouble(r.Height);
}

public double NumberOfCells(object Range)
{
  Excel.Range r = Range as Excel.Range;
  return r.Cells.Count;
}

public string ToUpperCase(string input)
{
  return input.ToUpper();
}

 

PS：调出AddIns对话框

·         2003：Menu Tools->Add Ins

·         2007：Office Button->Excel Options->Add ins->Excel Add Ins