使用包含 Excel VBA 内的 DataContract 的 wcf 服务

你读了标题并呻吟了。没关系。我也这么做了。但我们按照要求去做，对吧？我需要构建一个可以通过 Excel 中的名字进行访问的服务（2003 年，但我假设任何版本的 Excel 都应该支持此功能）。目前，我想做的就是将电子表格将数据发布到从远程计算机上的 Windows 服务运行的 WCF 服务。因为需要通过比 VBA 更复杂的东西来检索数据，所以我决定建立一个数据契约。这是我的代码（目前这只是一个概念验证，但它与完成后的外观密切相关）。

这是WCF相关的内容：

Imports System.ServiceModel
Imports System.Runtime.Serialization

<ServiceContract()>
Public Interface IWCF

    <OperationContract()>
    Sub PutData(ByVal what As String)

    <OperationContract()>
    Function GetWhats() As TheWhats()

End Interface

<DataContract()>
Public Class TheWhats
    <DataMember()> Public Property Timestamp As DateTime
    <DataMember()> Public Property TheWhat As String
End Class

Public Class WCF
    Implements IWCF

    Shared Whats As New List(Of TheWhats)

    Public Sub PutData(ByVal what As String) Implements IWCF.PutData
        Whats.Add(New TheWhats With {.Timestamp = Now, .TheWhat = what})
    End Sub

    Public Function GetWhats() As TheWhats() Implements IWCF.GetWhats
        Return Whats.ToArray
    End Function
End Class

我的应用程序配置：

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.web>
    <compilation debug="true"></compilation>
  </system.web>
  <system.serviceModel>
    <services>
      <service name="DataCollectionService.WCF">
        <endpoint address=""
                  binding="netTcpBinding"
                  contract="DataCollectionService.IWCF" />
        <endpoint address="mex"
                  binding="mexTcpBinding"
                  contract="IMetadataExchange" />
        <host>
          <baseAddresses>
            <add baseAddress="net.tcp://localhost:9100/DataCollectionService/ "/>
          </baseAddresses>
        </host>
      </service>
    </services>
    <behaviors>
      <serviceBehaviors>
        <behavior name="">
          <serviceMetadata httpGetEnabled="false"/>
          <serviceDebug includeExceptionDetailInFaults="true"/>
        </behavior>
      </serviceBehaviors>
    </behaviors>
  </system.serviceModel>
</configuration>

我的 vba 类来处理发布内容：

Private Const pConxString As String = _
    "service:mexAddress=""net.tcp://localhost:7891/Test/WcfService1/Service1/mex"", " & _
    "address=""net.tcp://localhost:7891/Test/WcfService1/Service1/"", " & _
    "binding=""NetTcpBinding_IService1"", bindingNamespace = ""http://tempuri.org/"", " & _
    "contract=""IService1"", contractNamespace=""http://tempuri.org/"""

Public ServiceObject As Object

Private Sub Class_Initialize()
    Set ServiceObject = GetObject(pConxString)
End Sub

Public Sub PutData(ByVal what As String)
    ServiceObject.PutData what
End Sub

Private Sub Class_Terminate()
    Set ServiceObject = Nothing
End Sub

如果我包括DataContract属性和返回数据协定对象的函数，我的 vba 代码失败Public Sub PutData方法如下：

“MessagePartDescription Name='GetWhatsResult' Namespace='http://tempuri.org/' 的实例不能在此上下文中使用：未设置所需的 'Type' 属性。”

如果我取出 DataContract 并注释掉服务定义中的函数，就可以了。我不打算使用GetWhats()Excel 中的函数。但我猜它需要类型定义TheWhats.

根据我读到的内容，一种解决方案似乎是将其设为 COM 对象并引用 DLL。然而，这对于我的环境来说不是一个可行的解决方案。还有其他方法可以解决这个问题吗？

好吧，回答了我自己的问题。解决方案（至少在我的情况下）是拆分接口并让我的服务类实现这两个接口。这是我的新界面文件：

Imports System.ServiceModel
Imports System.Runtime.Serialization

<ServiceContract()>
Public Interface IWCF_WriteOnly

    <OperationContract()>
    Sub PutData(ByVal what As String)

End Interface

<ServiceContract()>
Public Interface IWCF_ReadOnly

    <OperationContract()>
    Function GetData() As TheWhats()

End Interface

<DataContract()>
Public Class TheWhats
    <DataMember()> Public Property Timestamp As DateTime
    <DataMember()> Public Property TheWhat As String
End Class

Public Class WCF
    Implements IWCF_WriteOnly
    Implements IWCF_ReadOnly

    Shared Whats As New List(Of TheWhats)

    Public Sub PutData(ByVal what As String) Implements IWCF_WriteOnly.PutData
        Whats.Add(New TheWhats With {.Timestamp = Now, .TheWhat = what})
    End Sub

    Public Function GetData() As TheWhats() Implements IWCF_ReadOnly.GetData
        Return Whats.ToArray
    End Function
End Class

这需要更改 app.config 以便两个单独的端点可以在同一地址上运行：

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.web>
    <compilation debug="true"></compilation>
  </system.web>
  <system.serviceModel>
    <services>
      <service behaviorConfiguration="GenericBehavior" name="DataCollectionService.WCF">
        <endpoint address="wo" binding="netTcpBinding" contract="DataCollectionService.IWCF_WriteOnly" />
        <endpoint address="ro" binding="netTcpBinding" contract="DataCollectionService.IWCF_ReadOnly" />
        <endpoint address="mex" binding="mexTcpBinding" contract="IMetadataExchange" />
        <host>
          <baseAddresses>
            <add baseAddress="net.tcp://localhost:9100/DataCollectionService/" />
          </baseAddresses>
        </host>
      </service>
    </services>
    <behaviors>
      <serviceBehaviors>
        <behavior name="GenericBehavior">
          <serviceMetadata httpGetEnabled="false"/>
          <serviceDebug includeExceptionDetailInFaults="true"/>
        </behavior>
      </serviceBehaviors>
    </behaviors>
  </system.serviceModel>
</configuration>

由于只写端点与需要数据协定定义的端点隔离，因此 Excel 定义中的此更改是必要的：

Private Const pConxString As String = _
    "service:mexAddress=""net.tcp://localhost:9100/DataCollectionService/Mex"", " & _
    "address=""net.tcp://localhost:9100/DataCollectionService/wo"", " & _
    "binding=""NetTcpBinding_IWCF_WriteOnly"", bindingNamespace = ""http://tempuri.org/"", " & _
    "contract=""IWCF_WriteOnly"", contractNamespace=""http://tempuri.org/"""

我使用 WCF 测试客户端测试了此配置。我必须手动向它提供 mex 端点，但是当我这样做时，它会获取两个合同。我用的是PutData方法填充一点服务类，然后进入 Excel 并填充更多内容。我回到 WCF 测试客户端并运行GetData函数，它返回从测试客户端和 Excel 添加的所有项目。