Export Issues to Excel with Images?

Question

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

dorin gez · Answer

Hi,

Looks amazing !

will it work for DC?

tnx,

Dorin

Kmmaughs · Answer

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.

Josh loe_gb18e3 · Answer

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