Excel如何调用JWS(Java Webservice)

   最近需要用到Excel调用JAVA Webservice，一开始用VBA调用.Net写的Webservice，例如：

   

    Dim objSoapClient As New SoapClient
    
    objSoapClient.ClientProperty("ServerHTTPRequest") = True
   
    objSoapClient.mssoapinit ("http://svr-015:60/MMS/CalMetrics.asmx?WSDL")
    s = objSoapClient.HelloWorld()
    
    出现错误：WSDLReader:XML   Paser   failed   at   linenumber1,lineposition   5,reason   is:Invalid   xml   declaration。

 

    解决方法:放弃用SoapClient，改用"MSXML2.XMLHTTP"，具体参考如下代码：

---

 

    'get方式调用JWS
    Set objhttp = CreateObject("MSXML2.XMLHTTP")
    Set xmlDOC = CreateObject("MSXML.DOMDocument")

    strWebserviceURL = "http://se.hundsun.com/axis/services/JiraService?method=getProject"
    
    '设置参数及值   
    objhttp.Open "get", strWebserviceURL, False
   
    '设置Content-type，很重要
    objhttp.setRequestHeader "Content-Type", "text/xml;charset=utf-8"
    objhttp.send (strWebserviceURL)
    bOK = xmlDOC.Load(objhttp.responseXML)
   
    '判断状态
    If objhttp.Status = 200 Then
'        xmlstr = xmlDOC.XML
        ConvDatasetToRecordsetProject xmlDOC    '解析XML
    End If

   

---

Public Function ConvDatasetToRecordsetProject(ByVal voNL As DOMDocument)
    Err.Clear
    On Error GoTo ErrHandle
   
    Application.Cursor = xlWait
   
    Dim iXMLValueNode As IXMLDOMNode
    Dim iXMLNodeList As IXMLDOMNodeList

    Set iXMLNodeList = voNL.selectNodes("soapenv:Envelope/soapenv:Body/multiRef")

    Dim str As String
    For Each iXMLValueNode In iXMLNodeList
            str = iXMLValueNode.childNodes.Item(1).Text + "/" + iXMLValueNode.childNodes.Item(0).Text
            proList.AddItem str
    Next
   
    Application.Cursor = xlDefault

   
ExtSub:
 
    Exit Function
   
ErrHandle:
    Application.Cursor = xlDefault
    MsgBox Err.Description, vbCritical + vbOKOnly
    GoTo ExtSub

End Function

---