如何在 VBA 中设置和获取 JSESSIONID cookie? [英] How to set and get JSESSIONID cookie in VBA?

问题描述

我正在 Excel 2010 中使用 MSXML2.XMLHTTP60 为托管在 Tomcat 8.5.5 上的 Java REST Web 服务编写 VBA Web 服务客户端.

I'm writing a VBA web service client in Excel 2010 using MSXML2.XMLHTTP60 for my Java REST web services hosted on Tomcat 8.5.5.

在 VBA 中，我想从响应中获取字符串 JSESSIONID=E4E7666024C56427645D65BEB49ADC11 并将其设置在后续请求中.
(如果 Excel 崩溃，似乎这个 cookie 丢失了，用户必须再次进行身份验证.我想为用户设置最后存储的会话 ID，所以如果会话在服务器上仍然存在，他们不必在 Excel 客户端中重新进行身份验证.)

In VBA, I want to snag the string JSESSIONID=E4E7666024C56427645D65BEB49ADC11 from a response and set it in a subsequent request.
(if Excel crashes, it seems that this cookie is lost and the user has to authenticate again. I want to set the last stored session ID for the user, so if the session is still alive on the server, they don't have to re-authenticate in the Excel client.)

看到网上的一些资源，下面会拉取JSESSIONID cookie，但是最后一行总是打印为空:

I saw some online resources according to which the following will pull the JSESSIONID cookie, but the last line always prints empty:

Dim httpObj As New MSXML2.XMLHTTP60
With httpObj
    .Open "POST", URL, False
    .SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
    .SetRequestHeader "Connection", "keep-alive"
    .Send
End With
Debug.Print "Response header Cookie: " & httpObj.GetResponseHeader("Cookie")  'This should pull the JSESSIONID cookie but is empty

当我打印 httpObj.GetAllResponseHeaders 时，我没有看到任何包含 JSESSIONID 的标题.

When I print httpObj.GetAllResponseHeaders I do not see any headers that hold JSESSIONID.

在相同的资源中，以下应该设置所需的 cookie，但它没有(我在服务器上打印出传入请求的标头，并看到我的尝试没有覆盖 JSESSIONID 值).

In the same resources, the following should set the desired cookie, but it doesn't (I print out the headers of the incoming request on the server and see that my attempt did not override the JSESSIONID value).

httpObj.SetRequestHeader "Cookie", "JSESSIONID=blahblah"

我可能缺少 JSESSIONED 如何传输的机制，以及 VBA 如何以及何时提取和设置它.

I may be missing the mechanism for how JSESSIONED is transmitted, and how and when VBA pulls it and sets it.

推荐答案

虽然 omegastripes 发布了一个很好的解决方案，但我想分享我最终使用的解决方案.

While omegastripes posted a great solution, I wanted to share the solution I ended up using.

我使用的原始 MSXML2.XMLHTTP60 对象不支持 cookie.所以我改用了 WinHttp.WinHttpRequest.

The original MSXML2.XMLHTTP60 object I used does not support cookies. So instead I used WinHttp.WinHttpRequest.

这需要添加对您的代码的引用:在 VBA IDE 中，转到工具"-->引用"并确保选择了 Microsoft WinHTPP.Services version xxx.

This requires adding a reference to your code: In VBA IDE go to Tools-->References and make sure that Microsoft WinHTPP.Services version xxx is selected.

获取 cookie 并存储它的代码(假设对象 httpObj 类型为 WinHttp.WinHttpRequest):

Code that grabs the cookie and stores it (assuming an object httpObj of type WinHttp.WinHttpRequest):

' Get the JESSIONID cookie
Dim strCookie As String
Dim jsessionidCookie As String

strCookie = httpObj.GetResponseHeader("Set-Cookie")     ' --> "JSESSIONID=40DD2DFCAF24A2D64544F55194FCE04E;path=/pamsservices;HttpOnly"
jsessionidCookie = GetJsessionIdCookie(strCookie)       ' Strips to  "JSESSIONID=40DD2DFCAF24A2D64544F55194FCE04E"

'Store JSESSIONID cookie in the cache sheet

过程 GetJsessionIdCookie 在哪里:

Where the procedure GetJsessionIdCookie is:

' Takes a string of the form "JSESSIONID=40DD2DFCAF24A2D64544F55194FCE04E;path=/pamsservices;HttpOnly"
' and returns only the portion "JSESSIONID=40DD2DFCAF24A2D64544F55194FCE04E"
Public Function GetJsessionIdCookie(setCookieStr As String) As String
    'JSESSIONID=40DD2DFCAF24A2D64544F55194FCE04E;path=/pamsservices;HttpOnly

    Dim jsessionidCookie As String

    Dim words() As String
    Dim word As Variant

    words = Split(setCookieStr, ";")
    For Each word In words
        If InStr(1, word, "JSESSIONID") > 0 Then
            jsessionidCookie = word
        End If
    Next word

    GetJsessionIdCookie = jsessionidCookie
End Function

设置 cookie:

以下是创建 WinHttp.WinHttpRequest 对象并设置先前存储的 cookie 的方法:

Setting the cookie:

Here's the method that creates an WinHttp.WinHttpRequest object and sets the cookie that was previously stored:

Public Function GetHttpObj(httpMethod As String, uri As String, Optional async As Boolean = False, _
    Optional setJessionId As Boolean = True, _
    Optional contentType As String = "application/xml") As WinHttp.WinHttpRequest
    Dim cacheUtils As New CCacheUtils
    Dim httpObj As New WinHttp.WinHttpRequest
    With httpObj
        .Open httpMethod, uri, async
        .SetRequestHeader "origin", "pamsXL"
        .SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
        .SetRequestHeader "Connection", "keep-alive"
        .SetRequestHeader "Content-type", contentType
        .SetRequestHeader "cache-control", "no-cache"
    End With

    ' --- Pull stored cookie and attach to request ---
    If setJessionId Then
        httpObj.SetRequestHeader "Cookie", cacheUtils.GetCachedValue(wsJsessionidAddr)
    End If

    Set GetHttpObj = httpObj
End Function

其中 CCacheUtils 是我实现的一个类，用于存储和检索缓存值，例如 JSESSIONID cookie.

Where CCacheUtils is a class I implemented for storing and retrieving cached values such as the JSESSIONID cookie.

这篇关于如何在 VBA 中设置和获取 JSESSIONID cookie?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！