Skip to content

Instantly share code, notes, and snippets.

@niftycode

niftycode/json_to_excel.py

Last active Dec 29, 2019
Embed
What would you like to do?
Read JSON data from a given URL and save the data as Excel file.
#!/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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.