Willem Hoek  »  Automate your Jira Excel reporting with Python

Nov 14, 2020

Salvador Dalí, The Persistence of Memory, 1931

Jira is a tool used for project and issue management by more than a million users from 100,000+ companies. Jira comes with great reporting functionality and you can create custom Excel reports by manually exporting Jira items. However, if you do the same task regularly e.g. a Daily Status Report – you may want to fully automate the process.

This post will show you how you can programmatically create Jira Reports in Excel using open-source packages and libraries. The program will perform the following steps:

  1. Pull Jira issues using the Jira API
  2. Parse the extracted data (in JSON format) and save it to a database (SQLite)
  3. Produce an Excel report (XLSX format) from the database

Manually extract data from jira

Before we automate anything, let’s first have a look at how to export Jira items manually. The automated method will be based on the same query. In Jira, go to “Filters” and select the items you want to view and export. You could press “Export” to “Export Excel CSV (all fields)”. This is the action that we want to automate in step 1.

Press “Switch to JQL” to get the filter details in Jira Query Language (JQL) format. Also note the server URL. This information will be required later on.

Automate the process using Python

Get a Jira API token

We will use the Jira API to extract the issues. You need to first create an API token for your user account. Select “Account Settings” then “Security”. Look for the option to get an API token.

Python environment

The program is written in Python (programming language). I highly suggest you use the Anaconda Python distribution [1] as it already contains most of the libraries used such as Pandas (for data manipulation and analysis) and XlsxWriter (for creating Excel files). If you don’t use Anaconda – you may need to first install those packages using pip install.

If you are using Anaconda – the only additional package to install is Python Jira [2]. This is done with the following command in the Anaconda prompt.

conda install jira

A bare bones working program is listed below to show the key concepts. The only change required is to add your own user settings and JQL to extract the issues.

from jira.client import JIRA
import pandas as pd
import sqlite3
import xlsxwriter

# Settings
email = 'name@domain.com'                    # Jira username
api_token = "wlBnubFKBPYCQ8NQFb377407"       # Jira API token
server = 'https://kk2000.atlassian.net/'     # Jira server URL
jql = "project = KK"                         # JQL 

# Get issues from Jira
jira = JIRA(options={'server': server}, basic_auth= (email, api_token))
jira_issues = jira.search_issues(jql,maxResults=0)

# JSON to SQLite
issues = pd.DataFrame()
for issue in jira_issues:
    d = {
        'id': issue.id ,
        'key': issue.key,
        'assignee': str(issue.fields.assignee),
        'creator' : str(issue.fields.creator),
        'reporter': str(issue.fields.reporter),
        'created' : str(issue.fields.created),   
        'labels': str(issue.fields.labels),
        'components': str(issue.fields.components),
        'description': str(issue.fields.description),
        'summary': str(issue.fields.summary),
        'fixVersions': str(issue.fields.fixVersions),
        'subtask': str(issue.fields.issuetype.subtask),
        'issuetype': str(issue.fields.issuetype.name),
        'priority': str(issue.fields.priority.name),
        'project': str(issue.fields.project),
        'resolution': str(issue.fields.resolution),
        'resolution_date': str(issue.fields.resolutiondate),
        'status': str(issue.fields.status.name),
        'status_description': str(issue.fields.status.description),
        'updated': str(issue.fields.updated),
        'versions': str(issue.fields.versions),
        'watchcount': str(issue.fields.watches.watchCount),
    issues = issues.append(d, ignore_index=True)    
con = sqlite3.connect("jira-issues.db")
issues.to_sql("issues", con, if_exists="replace")

# Get data from SQLite
con = sqlite3.connect("jira-issues.db")
sql = "select issuetype, count(*) count from issues group by issuetype"
df = pd.read_sql_query(sql, con)

# Create Excel file
row = 1
col = 1
workbook = xlsxwriter.Workbook('jira-excel.xlsx')
header = workbook.add_format({'bold': True, 'align': 'center', 'bg_color': '#D8E4BC'})
center = workbook.add_format({'align': 'center'})
worksheet = workbook.add_worksheet('Summary')
worksheet.write(row, col, 'Issue Type', header)
worksheet.write(row, col + 1, 'Count', header)
row += 1
for index, dat in df.iterrows():
    worksheet.write(row + index, col, dat['issuetype'])
    worksheet.write(row + index, col + 1, int(dat['count']), center)

print('All done!')

Total execution time is less than a second. But it all depends on how many issues you are exporting.

The sample program creates a very simple Excel file - see below. However, the Python XlsxWriter library [3] can create almost any Excel file that you can create manually. Including complex text/date formating, multiple sheets, filters, column/row sizing, conditional formating and charts to name a few features.

Why use a database to store the data?

In the example above, the Jira data is first written to a SQL database. The Excel report pulls the data from the database. This is not technically required but provides multiple advantages such as:

References and further reading

[1] Python Anaconda: Your data science toolkit
https://www.anaconda.com/products/individual#Downloads, Retrieved: 2020-11-14

[2] Python Jira: Python library to work with Jira APIs
https://jira.readthedocs.io/, Retrieved: 2020-11-14

[3] XlsxWriter: Creating Excel XLSX files using Python
https://xlsxwriter.readthedocs.io/, Retrieved: 2020-11-14

[4] SQLite: Fast, self-contained, high-reliability, full-featured, SQL database
https://www.sqlite.org/, Retrieved: 2020-11-14

[5] DB Browser for SQLite
https://sqlitebrowser.org/, Retrieved: 2020-11-14