在Excel VBA中使用包含DataContract的wcf服务 [英] using a wcf service that contains a DataContract inside Excel VBA

问题描述

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

这是WCF相关的东西：

 导入System.ServiceModel 
导入System.Runtime.Serialization 
 
 <的ServiceContract（）> 
公共接口IWCF 
 
< OperationContract（）> 
 Sub PutData（ByVal as As String）
 
< OperationContract（）> 
函数GetWhats（）As TheWhats（）
 
结束接口
 
< DataContract（）> 
公共类TheWhats 
< DataMember（）>公共财产时间戳作为DateTime 
< DataMember（）>公共财产作为字符串
结束类
 
公共类WCF 
实现IWCF 
 
共享新的列表（TheWhats）
 
 Public Sub PutData（ByVal as As String）实现IWCF.PutData 
 Whats.Add（New TheWhats With {.Timestamp = Now，.TheHhat = what}）
 End Sub 
 
公共函数GetWhats（）As TheWhats（）实现IWCF.GetWhats 
返回Whats.ToArray 
结束函数
结束类
  

我的app.config：

 <？xml version = 1.0encoding =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> 
<行为> 
< serviceBehaviors> 
< behavior name => 
< serviceMetadata httpGetEnabled =false/> 
< serviceDebug includeExceptionDetailInFaults =true/> 
< / behavior> 
< / serviceBehaviors> 
< / behavior> 
< /system.serviceModel> 
< / configuration> 
  

我的vba类处理发布内容：

作为字符串= _
service：mexAddress =net.tcp：// localhost：7891 / Test / WcfService1 / Service1 / mex，pre & _
address =net.tcp：// localhost：7891 / Test / WcfService1 / Service1 /，& _
binding =NetTcpBinding_IService1，bindingNamespace =http://tempuri.org/，& _
contract =IService1，contractNamespace =http://tempuri.org/

公共ServiceObject作为对象

私人Sub Class_Initialize（）
设置ServiceObject = GetObject（pConxString）
End Sub

公共子PutData（ByVal作为字符串）
ServiceObject.PutData什么
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并注释掉服务定义中的函数，很好我不打算在Excel中使用 GetWhats（）函数。但是我猜想它需要 TheWhats 的类型定义。

从我读过的一个解决方案似乎使这个COM对象和引用该DLL。然而，这不是我的环境的可行解决方案。还有另一种方法来解决这个问题？

解决方案

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

 导入System.ServiceModel 
导入System.Runtime.Serialization 
 
< ServiceContract（）> 
公共接口IWCF_WriteOnly 
 
< OperationContract（）> 
 Sub PutData（ByVal as As String）
 
结束接口
 
< ServiceContract（）> 
公共接口IWCF_ReadOnly 
 
< OperationContract（）> 
函数GetData（）As TheWhats（）
 
结束接口
 
< DataContract（）> 
公共类TheWhats 
< DataMember（）>公共财产时间戳作为DateTime 
< DataMember（）>公共属性作为字符串
结束类
 
公共类WCF 
实现IWCF_WriteOnly 
实现IWCF_ReadOnly 
 
共享新的列表（OfWhats ）
 
 Public Sub PutData（ByVal as As String）实现IWCF_WriteOnly.PutData 
 Whats.Add（New TheWhats With {.Timestamp = Now，.TheHhat = what}）
结束Sub 
 
公共函数GetData（）作为TheWhats（）实现IWCF_ReadOnly.GetData 
返回Whats.ToArray 
结束函数
结束类
  

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

 <？xml version =1.0encoding =utf-8？> 
< configuration> 
< system.web> 
< compilation debug =true>< / compilation> 
< /system.web> 
< system.serviceModel> 
< services> 
< service behaviorConfiguration =GenericBehaviorname =DataCollectionService.WCF> 
< endpoint address =wobinding =netTcpBindingcontract =DataCollectionService.IWCF_WriteOnly/> 
< endpoint address =robinding =netTcpBindingcontract =DataCollectionService.IWCF_ReadOnly/> 
< endpoint address =mexbinding =mexTcpBindingcontract =IMetadataExchange/> 
< host> 
< baseAddresses> 
< add baseAddress =net.tcp：// localhost：9100 / DataCollectionService //> 
< / baseAddresses> 
< / host> 
< / service> 
< / services> 
<行为> 
< serviceBehaviors> 
< behavior name =GenericBehavior> 
< serviceMetadata httpGetEnabled =false/> 
< serviceDebug includeExceptionDetailInFaults =true/> 
< / behavior> 
< / serviceBehaviors> 
< / behavior> 
< /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中添加的所有项目。

You read the title and groaned. That's okay. I did too. But we do what we're asked, right? I need to build a service that can be accessed via a moniker from within Excel (2003, but I'm assuming any version of Excel should support this functionality). At the moment all I want to do is have a spreadsheet post data to a WCF service running from a Windows service on a remote machine. Because that data needs to be retrieved by something a little more sophisticated than VBA, I decided to set up a data contract. Here's my code (at the moment this is just a proof-of-concept, but it's closely related to how it needs to look when it's finished).

Here's the WCF-related stuff:

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

My app.config:

<?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>

And my vba class to handle posting stuff:

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

If I include the DataContract attribute and the function that returns the data contract object, my vba code fails in the Public Sub PutData method with the following:

"Instance of MessagePartDescription Name='GetWhatsResult' Namespace='http://tempuri.org/' cannot be used in this context: required 'Type' property was not set."

If I take out the DataContract and comment out the function in the service definition, I'm fine. I don't plan on using the GetWhats() function from within Excel. But yet I'm guessing it wants the type definition for TheWhats.

From what I've read one solution seems to be making this a COM object and referencing the DLL. However that isn't a workable solution for my environment. Is there another way to fix this?

解决方案

Okay, answered my own question. The solution (at least in my case) is to split the interface and have my service class implement both interfaces. Here's my new interface file:

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

That required a change in app.config so that two separate endpoints could operate on the same address:

<?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>

And because the write-only endpoint was isolated from the endpoint that required a data contract definition, this change in the Excel definition was necessary:

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/"""

I tested this configuration with the WCF Test Client. I had to feed it the mex endpoint manually, but when I did, it picked up both contracts. I used the PutData method to populate the service class a little, then went into Excel and populated it some more. I went back to the WCF Test Client and ran the GetData function and it returned all the items added from both the Test Client and Excel.

这篇关于在Excel VBA中使用包含DataContract的wcf服务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！