Read JSON data from a given URL and save the data as Excel file.

json_to_excel.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-


"""
Fetch JSON data and create an Excel file
Version: 1.1
Python 3.8
Date created: 14.12.2019
"""


import requests
import sys
import openpyxl
from openpyxl.styles import Font


URL = "https://xern-statistic.de/api/election"


def fetch_json_data(json_url):
    """Fetch the JSON data from a given URL.

    Args:
        json_url (str): The API url

    Returns:
        dict: JSON data (UK election 2019)
    """

    # Connect to the server
    try:
        response = requests.get(json_url)
    except OSError as e:
        print("Error: {0}".format(e))
        sys.exit(0)

    # Check if the status code is OK
    # and receive data
    if response.status_code == 200:
        print("Status 200, OK")

        return response.json()
    else:
        print("JSON data request not successfull!")
        sys.exit(0)


def write_excel(data):
    """Write data to an Excel file.

    Args:
        data (dict): The JSON data
    """

    # Call openpyxl.Workbook() to create a new blank Excel workbook
    workbook = openpyxl.Workbook()

    # Activate a sheet
    sheet = workbook.active

    # Set a title
    sheet.title = 'UK Election 2019'
    # print(sheet.title)  # -> UK Election 2019

    # Headline style
    bold16font = Font(size=16, bold=True)

    # Add headlines (1st and 2nd column)
    sheet['A1'] = 'Party'
    sheet['A1'].font = bold16font

    sheet['B1'] = 'Seats'
    sheet['B1'].font = bold16font

    # Create two lists (keys, values)
    parties = list(data.keys())
    seats = list(data.values())

    party_row = 2

    # Write data to 1st column
    for party in parties:
        sheet.cell(row=party_row, column=1).value = party
        party_row += 1

    seats_row = 2

    # Write data to 2nd column
    for seat in seats:
        sheet.cell(row=seats_row, column=2).value = seat
        seats_row += 1

    # Save the workbook (excel file)
    workbook.save('UK-Election-2019.xlsx')

json_data = fetch_json_data(URL)
# print(json_data)

write_excel(json_data)