问使用VBA Excel 2010访问JIRA

EN

'Option Explicit
Dim jiraURL, Auth, restReq
Dim Url, responseCode, responseContent
Sub startFunction()
Dim loginOk As Boolean

loginOk = PrepareLogin

If (loginOk = True) Then
    getIssues 'Login ok, now fetch all issues
End If
MsgBox "All done"
End Sub
Sub getIssues()
Dim wsStartPage As Worksheet
Dim jsonResp As Object
Dim currentRow As Long
Dim issueName As String
Dim i As Long
Dim moreJiratoRead As Boolean
Dim jiraPosition As Long
    
currentRow = 1

Set wsStartPage = ActiveWorkbook.Sheets("MyIssues")
wsStartPage.Cells(currentRow, 1) = "Issue Name"
currentRow = currentRow + 1
moreJiratoRead = True
jiraPosition = 0
    
Do While (moreJiratoRead = True)

    Url = jiraURL & "rest/api/latest/search?jql=project in (someProjectNames) and status not in (dismissed, done, closed, resolved)&startAt=" & CStr(jiraPosition) & "&maxResults=1000&expand=fields&fields=key,status"
    restReq.Open "GET", Url, False
    restReq.setRequestHeader "Authorization", "Basic " & Auth
    restReq.setRequestHeader "Content-Type", "application/json"
    restReq.Send
    responseCode = restReq.Status
    responseContent = restReq.responseText
    Set jsonResp = JsonConverter.ParseJson(responseContent)
    Set issues = jsonResp("issues")

    If issues.Count = 1000 Then 'This will allow me to read all issues without limitation
        jiraPosition = jiraPosition + 1000
    Else
        moreJiratoRead = False
    End If

    
    i = 1
    Do While i <= issues.Count
        issueName = CStr(issues(i)("key"))
        wsStartPage.Cells(currentRow, 1) = issueName
        currentRow = currentRow + 1
        i = i + 1
    Loop
Loop
End Sub

Function PrepareLogin() As Boolean
Dim uid, pwd

PrepareLogin = False
uid = InputBox("Please enter your windows login id", "Windows Login")
If Len(Trim(uid)) = 0 Then
    Exit Function
Else
    pwd = InputBox("Please enter your windows password", "Windows Password")
    If Len(Trim(pwd)) = 0 Then
        Exit Function
    End If
End If

PrepareLogin = init(uid, pwd)

End Function

Function init(JIRA_USER, JIRA_PWD) As Boolean
'Login and get session ID.
Dim sOutput
Dim sCookie

init = True
jiraURL = "https://someURL/"
Set restReq = CreateObject("Msxml2.ServerXMLHTTP.6.0")
restReq.setTimeouts 6000000, 6000000, 6000000, 6000000

restReq.Open "POST", jiraURL & "/rest/auth/1/session", False
restReq.setRequestHeader "Content-Type", "application/json"
restReq.setRequestHeader "Accept", "application/json"
restReq.Send " {""username"" : """ & JIRA_USER & """, ""password"" : """ & JIRA_PWD & """}"
sOutput = restReq.responseText

If (InStr(1, LCase(sOutput), LCase("login failed")) Or InStr(1, LCase(sOutput), LCase("unauthorized"))) > 0 Then
    init = False
    MsgBox sOutput
    End
End If
sCookie = "JSESSIONID=" & Mid(sOutput, 42, 32) & "; Path=/Jira"

End Function