Jira Tempo offers a servlet to export Jira Tempo Worklog to Excel. And Tempo describes a solution to import these data to excel. But this solution is a mess. After one year of frustration i developed an alternative one.
Jira Tempo Worklog Excel Export via Servlet
As described in https://tempoplugin.jira.com/wiki/spaces/TEMPO0716/pages/125731087/Tempo+Servlet+Manual and http://blog.tempo.io/2010/tutorial-how-to-get-your-jira-tempo-data-into-excel-pivot-report/ i set up an more or less working solution.
To interpret XML in Excel was hell. But at the end it works. In my case the required URL is long. So long that it get lost every time with closing excel worksheet. Every time at startup first action was to modify data connection and set correct URL again (copy and paste from worksheet).
Good point was, that no other components needed.
Jira Tempo Worklog Excel Export via Excel Sheet
Now solution hat been changed to excel way. The download button in Tempo for Jira offers an excel export. The trick is to trigger this function on a regular basis. On our small linux based fileserver i developed a simple script. Via curl should the download work. Unfortunately the authentication via basic auth does not work because ngnix proxy as frontend webserver.
But there is a second way to basic auth is written in https://developer.atlassian.com/jiradev/jira-apis/jira-rest-apis/jira-rest-api-tutorials/jira-rest-api-example-basic-authentication – second part.
You can encode your login credentials, e.g. Fred/Fred, within some online tools like https://www.base64encode.org/ (Use Encode, not Decode!). The result may e.g. RnJlZC9GcmVk
Now the magic working curl call is
curl -b „cookiesfile“ -L -D- -X GET -H „Authorization: Basic RnJlZC9GcmVk“ -H „Content-Type: application/json“ „https://jira-host.tld/secure/TempoSearchBoard!excel.jspa?v=1&search_filter=12602&periodType=FLEX&periodView=DATES&from=2017-01-01&to=2017-12-31“ -o „az-nachweise.xls“
You can modify dates and filename as you need. The result file is stored in current directory.
For me this solution works within a cron job and export data every 30 minutes during business hours.
In Excel I prefer using PowerPivot. It has an simple way to import related (and only related) data and a stable way to update this data on opening the worksheet.
Works like a charm.