Prepare Jmeter Reports using excel macros | Jmeter report automation using excel macros

To create an Excel macro that reads a JMeter JTL file and prepares reports in the Excel sheet, you can follow these general steps:

• Open Microsoft Excel and create a new workbook.
• Press ALT + F11 to open the Visual Basic Editor.
• In the Visual Basic Editor, insert a new module by right-clicking on the project and selecting "Insert > Module."
• In the new module, create a new sub-routine that will read the JMeter JTL file. You can use the following code to read the file:

Sub GenerateJMeterReportWithColor()

    

    ' Set the path to the JTL file

    Dim jtlFilePath As String

    jtlFilePath = "C:\Path\To\JMeter.jtl"

    

    ' Open the JTL file

    Dim jtlFile As Object

    Set jtlFile = CreateObject("Scripting.FileSystemObject").OpenTextFile(jtlFilePath, 1)

    

    ' Set up the Excel worksheet

    Dim reportSheet As Worksheet

    Set reportSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))

    reportSheet.Name = "JMeter Report"

    

    ' Write the report header

    reportSheet.Range("A1:D1").Merge

    reportSheet.Range("A1:D1").Value = "JMeter Report"

    reportSheet.Range("A2:D2").Merge

    reportSheet.Range("A2:D2").Value = "Generated on " & Now()

    reportSheet.Range("A1:D2").HorizontalAlignment = xlCenter

    reportSheet.Range("A1:D2").Font.Bold = True

    reportSheet.Range("A1:D2").Font.Size = 14

    

    ' Write the column headers

    reportSheet.Range("A4").Value = "Sample Name"

    reportSheet.Range("B4").Value = "Samples"

    reportSheet.Range("C4").Value = "Average Response Time (ms)"

    reportSheet.Range("D4").Value = "Error %"

    reportSheet.Range("A4:D4").Font.Bold = True

    

    ' Read the JTL file and populate the report

    Dim line As String

    Dim rowIndex As Integer

    rowIndex = 5

    Do Until jtlFile.AtEndOfStream

        line = jtlFile.ReadLine

        Dim values As Variant

        values = Split(line, ",")

        reportSheet.Cells(rowIndex, 1).Value = values(2)

        reportSheet.Cells(rowIndex, 2).Value = values(1)

        reportSheet.Cells(rowIndex, 3).Value = values(7)

        reportSheet.Cells(rowIndex, 4).Value = values(9)

        If values(9) = "0" Then ' If there are no errors

            reportSheet.Cells(rowIndex, 4).Interior.Color = RGB(198, 239, 206) ' Light green color

        Else

            reportSheet.Cells(rowIndex, 4).Interior.Color = RGB(255, 199, 206) ' Light red color

        End If

        rowIndex = rowIndex + 1

    Loop

    

    ' Format the report

    reportSheet.Columns("A:D").AutoFit

    reportSheet.Range("A5:D" & rowIndex - 1).Borders.LineStyle = xlContinuous

    

    ' Close the JTL file

    jtlFile.Close

    

End Sub

Thats it. Now it can read the JTL file and prepare the reports.