Excel VBA access WCF service

背景:

VBA 访问WCF通过TCP能够实现但有局限性，它只适合返回值或者参数是常用类型(Int, String等)或者常用类型的数组。请参考http://damianblog.com/2009/07/05/excel-wcf/。如果要支持自定义类型，定义在WCF DataContract或者是COM对象，都会访问时提示Type没有定义。

解决方法：

创建一个COM library 去访问WCF，VBA通过COM间接访问WCF。

例子：

namespace MyDataType
{
    [Guid("9A26F178-28D6-43AC-9C1A-2CD9F9D01246")]
    public interface IMyItem
    {
        [DispId(1)]
        string ID { get;  }
        [DispId(2)]
        double Value { get; }
    }

    [Guid("B1612D7D-3DB0-4CC3-8C3C-3504CBC77BAD")]
    [ClassInterface(ClassInterfaceType.None)]
    [Serializable]
    public class MyItem : IMyItem
    {
          string _id;
          double _val;
          public MyItem(string id, double val)
          {
              _id = id;
              _val =val;
          }

          public string ID
          {
              get
              {
                  return _id;
              }
          }

          public double Value
          {
              get
              {
                  return _val;
              }

          }
    }
}

 

namespace WcfService1
{
    [ServiceContract]
    public interface IService1
    {
        [OperationContract]
        int GetData(int value);

       [OperationContract]
       MyItem GetMyItem();

    }

    [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single, IncludeExceptionDetailInFaults = true)]
    public class Service1 : IService1
    {
        public int GetData(int value)
        {
            return value * value;
        }

        public MyItem GetMyItem()
        {
            return new MyItem("ss", 0.25);
        }

    }
}

 

namespace CSharpCOM
{
    [Guid("F758C688-2BD9-433f-BAD5-2EC930FBB5B7")]
    public interface IMyInterface
    {
        [DispId(1)]
        int GetValue(int val);
        [DispId(2)]
        MyItem GetMyItem();
    }

    [Guid("78851218-0FF3-43a9-A6D5-E8680A84C788"),
    InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
    public interface MyCom_Events
    {
    }

    [Guid("3F73CB4C-80AA-483c-8623-9025B5224CFF"),
      ClassInterface(ClassInterfaceType.None),
      ComSourceInterfaces(typeof(MyCom_Events))]
    public class MyClass : IMyInterface
    {
        private IService1 _svc;
        public MyClass()
        {
            NetTcpBinding binding = new NetTcpBinding(SecurityMode.Transport);
            EndpointAddress address = new EndpointAddress("net.tcp://localhost:8733/Design_Time_Addresses/WcfService1/Service1");
            ContractDescription contract = ContractDescription.GetContract(typeof(IService1));
            ServiceEndpoint endpoint = new ServiceEndpoint(contract, binding, address);
            ChannelFactory<IService1> channelFactory = new ChannelFactory<IService1>(endpoint);
             _svc = channelFactory.CreateChannel();
        }
        public int GetValue(int val)
        {

            return _svc.GetData(val);
        }

        public MyItem GetMyItem()
        {
            return _svc.GetMyItem();
        }
    }
}

Excel VBA

Public Sub test()
    Dim svcCom As CSharpCOM.MyClass
    Set svcCom = CreateObject("CSharpCOM.MyClass")
    Dim myItem As MyDataType.myItem
    Set myItem = svcCom.GetMyItem
    MsgBox "item id is " + myItem.ID
End Sub