Export Issues to Excel with Images?

[Deleted user]

Hey,

maybe someone can help me to answer it. I want to Export Issues from Jira in an Excel Document with Images. I tried a few plugins but it seems on Cloud none of them can include the images in the excel file. 

So is there maybe an option to do that or is it not possible?

kind regards

[Kmmaughs]

A colleague of mine did this while taking a different approach. He started with an excel file and literally wrote a VBA script to call the JIRA API and pull the information into the excel. It's definitely more work but more of a free approach if you don't want to buy a plug-in. 

Here's the VBA script:

Private p&, token, dic
Global strUsr As String
Global strPwd As String

Const strUrl = "http://YOURJIRAENV/rest/api/2/search?jql=filter=10000&expand=projects.issuetypes.fields,projects.issuetypes.attachment&fields=customfield_10001,customfield_10002,customfield_10003,customfield_10004,customfield_10005,created,customfield_10006,customfield_10007,assignee,summary,description,attachment&startAt=0&maxResults=1000"

Public Sub getData(strUsr, strPwd)

strJSONData = httpGET(CStr(strUrl), CStr(strUsr), CStr(strPwd))
Set oData = JsonConverter.ParseJson(CStr(strJSONData))

Rows("3:3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Cells(3, 1).Select
DoEvents
For Each Sheet In Worksheets
If Sheet.Name <> "FIRSTTABNAME" Then 
Application.DisplayAlerts = False
Sheet.Delete
Application.DisplayAlerts = True
End If
DoEvents
Next Sheet

iRow = 3
On Error Resume Next
For Each issue In oData("issues")
DoEvents
Cells(iRow, 1).Select
ActiveSheet.Hyperlinks.Add Anchor:=Cells(iRow, 1), Address:="http://YOURJIRAENV/browse/" & issue("key"), TextToDisplay:=issue("key")

Set oFields = issue("fields")
Cells(iRow, 2).Value = oFields("summary")
Cells(iRow, 3).Value = oFields("assignee")
Cells(iRow, 4).Value = Split(oFields("created"), "T")(0)
Cells(iRow, 5).Value = oFields("customfield_10001")
Cells(iRow, 6).Value = oFields("customfield_10002")
Cells(iRow, 7).Value = oFields("customfield_10003")
Cells(iRow, 8).Value = oFields("customfield_10004")
Cells(iRow, 9).Value = oFields("customfield_10005")
Cells(iRow, 10).Value = oFields("customfield_10006")
Cells(iRow, 11).Value = oFields("customfield_10007")


Set oAttachments = oFields("attachment")
iCol = 0
For Each attachment In oAttachments
DoEvents
strImageName = attachment("filename")
strImagePath = attachment("content")
If (Len(strImageName) > 30) Then
strImageLink = Left(strImageName, 30)
Else
strImageLink = strImageName
End If

Sheets.Add(After:=Sheets("FIRSTTABNAME")).Name = strImageLink
Cells(2, 2).Select
ActiveSheet.Pictures.Insert(strImagePath).Select
Selection.Cut
ActiveSheet.PasteSpecial Format:="Picture (GIF)", Link:=False, DisplayAsIcon:=False

Sheets("FIRSTTABNAME").Hyperlinks.Add Anchor:=Sheets("FIRSTTABNAME").Cells(iRow, 12 + iCol), Address:="", SubAddress:="'" & strImageLink & "'!A1", TextToDisplay:=strImageName
Sheets(strImageLink).Hyperlinks.Add Anchor:=Sheets(strImageLink).Range("A1"), Address:="", SubAddress:="'FIRSTTABNAME'!A" & iRow, TextToDisplay:="Return to FIRSTTABNAME"

Sheets("FIRSTTABNAME").Activate
Cells(iRow, 12 + iCol).Select
iCol = iCol + 1
DoEvents
Next attachment
iRow = iRow + 1
Next issue
On Error GoTo 0
Cells.EntireColumn.AutoFit
Cells(3, 1).Select
strSaveAsFilePath = ActiveWorkbook.path
strSaveAsFileName = "\FILENAMEHERE - " & Format(Now(), "YYMMDD_HHmm") & ".xlsm"
ActiveWorkbook.SaveAs Filename:=strSaveAsFilePath & strSaveAsFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
If (MsgBox("Your file has been exported to:" & vbCrLf & vbCrLf & strSaveAsFilePath & strSaveAsFileName & vbCrLf & vbCrLf & "Would you like to go to the folder?", vbQuestion + vbYesNo, "Task Complete") = vbYes) Then
Shell "C:\WINDOWS\explorer.exe """ & strSaveAsFilePath & "", vbNormalFocus
End If
End Sub


Public Function httpGET(fn As String, _
Optional authUser As String = vbNullString, _
Optional authPass As String = vbNullString) As String
pHtml = fn
Dim oHttp As Object
Set oHttp = CreateObject("Microsoft.XMLHTTP")
Call oHttp.Open("GET", pHtml, False)
If (authUser <> vbNullString) Then
' need to do basic authentication
' acknowledgement to http://pastie.org/1192157
oHttp.SetRequestHeader "Content-Type", "application/json"
oHttp.SetRequestHeader "Accept", "application/json"
oHttp.SetRequestHeader "Authorization", "Basic " + _
EncodeBase64(authUser + ":" + authPass)
End If
Call oHttp.Send("")
httpGET = oHttp.ResponseText
Set oHttp = Nothing
End Function


Function EncodeBase64(text As String) As String


Dim arrData() As Byte
arrData = StrConv(text, vbFromUnicode)

Dim objXML As MSXML2.DOMDocument
Dim objNode As MSXML2.IXMLDOMElement

Set objXML = New MSXML2.DOMDocument
Set objNode = objXML.createElement("b64")

objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
EncodeBase64 = Application.Clean(objNode.text)

Set objNode = Nothing
Set objXML = Nothing
End Function


Sub Button1_Click()

If (MsgBox("Enter Valid Credentials to Continue", vbCritical + vbYesNo, "WARNING") = vbYes) Then
If (strUsr = "" Or strPwd = "") Then
frmMain.Show
End If

Call getData(strUsr, strPwd)
End If
End Sub

 Some things to note about the code above:

• FIRSTTABNAME is literally the name you give the first tab in the excel sheet.
• YOURJIRAENV is the URL of your JIRA environment.
• FILENAMEHERE is the name of the excel sheet that gets generated/output by the script. It is written to store in the same folder as the where the sheet gets run from.
• The example above shows how to pull some standard fields from JIRA and custom defined fields. The "filter=10000" in the URL references a saved filter that is the basis for the export. 
• The output in the file begins at row 3. Row 2 has the column headers, so you pretty much define those yourself.
• In the final file, the Images are stored in the excel file in a separate tab; the hyperlink from the first tab goes to the image tab in the excel. 
• (There was some code left in here that relates to storing and linking assignee information in MS applications, but you may not need that).

I hope this helps someone.

[dorin gez]

Hi,

Looks amazing ! 

will it work for DC?

tnx,

Dorin

[Josh loe]

Dominic,

There is no way to export issues into an excel spreadsheet that include the attachments at this time. 

You can download all attachments and manually input them in the spreadsheet.  To do this take a system backup and unzip the file.  The attachments will be in there usually, I have seen some issues where the attachment however is not set to the correct extension.  You'd have to set it to a .pdf or .jpg or whatever to get that to work.

To sum it up, it's not possible to do easily but with a little work if you absolutely need that information it can be done.

-Josh Loe