...
- Populate data in RMsis
The above RMsis screen displays list of requirements present in a filter. - Create macro in MS-Excel to fetch the data from RMsis and populate it into the Excel document.
- See attached document ListFilterDetails.xlsm for reference.
- See listRequirementsInFilter macro present in above file.
- The macro uses vba-json(JsonConverter.bas) library to parse JSON response, import this module in VB before executing the macro.
The Excel document created in MS Excel which lists the requirements present in filter.
The content is populated dynamically using excel macro after fetching it from RMsis.
The code of the excel macro is mentioned below, it contains following steps:
- Login into JIRA user a valid username and password.
- Fetch data from RMsis using API's.
Insert the formatted data into excel document.
Code Block language vb title Excel Macro collapse true Public Sub listRequirementsInFilter() 'Add header column names Cells(1, 1).value = "ID" Cells(1, 2).value = "Summary" Cells(1, 3).value = "Status" Cells(1, 4).value = "Priority" Cells(1, 5).value = "Criticality" Cells(1, 5).value = "Release" Dim requirementIds As Collection, requirementId As Integer Dim requirementKey, requirementSummary As String Dim requirementStatus, requirementPriority, requirementCriticality As Integer Dim RequirementStatuses, Priorities, Criticalities As Dictionary rmsisApiString = "{getPlannedRequirementStatuses{id,name}}" ResponseText = GetRequestfromRMsis(rmsisApiString) Set StatusJSON = ParseJson(ResponseText) Set RequirementStatuses = New Dictionary RequirementStatuses.Add -1, "" Dim key As Integer, value As String For Each Item In StatusJSON("data")("getPlannedRequirementStatuses") key = Item("id") value = Item("name") RequirementStatuses.Add key, value Next rmsisApiString = "{getPriorities{id,name}}" ResponseText = GetRequestfromRMsis(rmsisApiString) Set StatusJSON = ParseJson(ResponseText) Set Priorities = New Dictionary Priorities.Add -1, "" For Each Item In StatusJSON("data")("getPriorities") key = Item("id") value = Item("name") Priorities.Add key, value Next rmsisApiString = "{getCriticalities{id,name}}" ResponseText = GetRequestfromRMsis(rmsisApiString) Set StatusJSON = ParseJson(ResponseText) Set Criticalities = New Dictionary Criticalities.Add -1, "" For Each Item In StatusJSON("data")("getCriticalities") key = Item("id") value = Item("name") Criticalities.Add key, value Next rmsisApiString = "{getPlannedRequirementsByFilter(projectId:1,filterId:1)}" ResponseText = GetRequestfromRMsis(rmsisApiString) Set JSON = ParseJson(ResponseText) Set requirementIds = JSON("data")("getPlannedRequirementsByFilter") For i = 1 To requirementIds.Count requirementId = requirementIds(i) rmsisApiString = "{getRequirementById(id:reqId){key,summary,requirementStatus{id},priority{id},criticality{id}}}" rmsisApiString = Replace(rmsisApiString, "reqId", requirementId) ResponseText = GetRequestfromRMsis(rmsisApiString) Set JSON = ParseJson(ResponseText) requirementKey = JSON("data")("getRequirementById")("key") requirementSummary = JSON("data")("getRequirementById")("summary") If IsNull(JSON("data")("getRequirementById")("requirementStatus")) Then requirementStatus = -1 Else requirementStatus = JSON("data")("getRequirementById")("requirementStatus")("id") End If If IsNull(JSON("data")("getRequirementById")("priority")) Then requirementPriority = -1 Else requirementPriority = JSON("data")("getRequirementById")("priority")("id") End If If IsNull(JSON("data")("getRequirementById")("criticality")) Then requirementCriticality = -1 Else requirementCriticality = JSON("data")("getRequirementById")("criticality")("id") End If Cells(i + 1, 1).value = requirementKey Cells(i + 1, 2).value = requirementSummary Cells(i + 1, 3).value = RequirementStatuses.Item(requirementStatus) Cells(i + 1, 4).value = Priorities.Item(requirementPriority) Cells(i + 1, 5).value = Criticalities.Item(requirementCriticality) Next i Call FormatHeaderRow End Sub Function GetRequestfromRMsis(rmsisApiString) As String 'Function to fetch details using RMsis graph APIs Function GetRequestfromRMsis(rmsisApiString) As String Dim stringURL As String Dim auth auth = "(YWRtaW46YWRtaW4=)" Set restRequest = CreateObject("Msxml2.ServerXMLHTTP.6.0") stringURL = "http://10.0.1.15:8080/rest/service/latest/rmsis/graphql?query=" restRequest.Open "GET", stringURL & rmsisApiString, False restRequest.SetRequestHeader "Content-Type", "application/json" restRequest.SetRequestHeader "Authorization", "Basic " & auth restRequest.Send If restRequest.status = "401" Then MsgBox "Not authorized" Else 'MsgBox restRequest.ResponseText GetRequestfromRMsis = restRequest.ResponseText End If End Function Private Sub FormatHeaderRow() ' FormatHeaderRow Macro Rows("1:1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Font.Bold = True With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.Font .ThemeColor = xlThemeColorDark1 .TintAndShade = 0 End With End Sub
...