Export RMsis data to an Excel document

The following example demonstrates the use of RMSis API's which is used to create a report in MS-Excel after dynamically fetching the data from RMsis using macros.

Pre-Requisites:

  • RMsis version v1.8.9.2 or higher.
  • MS-Excel with developer module to write macro.

Following steps were taken:

  1. Populate data in RMsis

                                                                            The above RMsis screen displays list of requirements present in a filter.
  2. 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.
    • Enable macros and execute listRequirementsInFilter macro present in above file.
    • The macro uses vba-json(JsonConverter.bas) library to parse JSON response, you will have to import this module if you are writing your own 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:

  1. Login into JIRA user a valid username and password.
  2. Fetch data from RMsis using API's.
  3. Insert the formatted data into excel document. 

    Excel Macro
    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 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