python - Openpyxl - looking for best option to get this list, dictionaries thing sorted

Question

I have a JSON file with headers (Student, Rank, City, Interest). Here's the structure of the JSON file

[{"Student": "A", "Rank": 9, "City": “Milan”, "Interest": “Singing”}, {"Student": "B", "Rank": 5, "City": “NYC”, "Interest": “Problem Solving”}, {"Student": "C", "Rank": 9, "City": “Chicago”, "Interest": “Dancing”}, {"Student": "D", "Rank": 10, "City": “Boston”, "Interest": “Parasailing”}, {"Student": "E", "Rank": 9, "City": “Paris”, "Interest": “Apparel Designing”}, {"Student": "F", "Rank": 6, "City": “LA”, "Interest": “Wellness”}, {"Student": "G", "Rank": 25, "City": “Phoenix”, "Interest": “Acting”}]

I open this JSON file like this in my code

jsonfile = open("C:\UsersRicky\Desktop\StudData.json","rt")
storejsondata = json.load(jsonfile)

storejsondata gives me a list of dictionaries like this [{'Student': 'A', 'Rank': 9, 'City': 'Milan', 'Interest': 'Singing'}, {'Student': 'B', 'Rank': 5, 'City': 'NYC', 'Interest': 'Problem Solving'} ............]

Then I loop through this list, like this

for dictdata in storejsondata:
    print(dictdata)
print(type(dictdata))

It shows individual dictionaries from the list vertically like this {'Student': 'A', 'Rank': 9, 'City': 'Milan', 'Interest': 'Singing'} {'Student': 'B', 'Rank': 5, 'City': 'NYC', 'Interest': 'Problem Solving') ..... I want to get the keys of this, I do:

listkey=[]
for key in xdict.keys():
     print(f'keys are: {key}')
     listkey.append(key)
print(listkey)
I write the keys as header in Excel like this
wb=openpyxl.load_workbook("C:\UsersRickey\Desktop\Studrec.xlsx")
sheet=wb.active
rows = 1
cols = 1
for i in range (0,len(listkey)):
     sheet.cell(row=rows,column=cols).value=listkey[i]
     cols=cols+1

wb.save("C:\UsersRicky\Desktop\Studrec.xlsx")

I can write the headers in the Excel on row 1. This works fine.

PROBLEM:

1. How can I get the values ? If I use:

   for key, val in datadict.items(): print(key, val)

This only shows the vales of the last dictionary item i.e. G, 25, Phoenix, Acting. How to get values of all the dictionaries 2. How to write these values in Excel ? I want it like this - Row 1 Col 1, Col2, Col3, Col 4 are headers from row 2 onwards I want the values in respective columns. How to get this using Openpyxl ?

Answer 1

If openpyxl is not an absolute requirement you can really simplify this by either using the python standard library (csv and json) and writing to a csv or using pandas and writing to excel (or just about any other file format):

Standard Library:

import json
import csv

# function to read json files into a dict or list of dicts
def read_json(path):
    with open(path, 'r') as file:
        return json.load(file)

# function to write csv files from dicts or list of dicts
def write_csv(data, path):
    with open(path, 'w') as file:
        fieldnames = set().union(*data)
        writer = csv.DictWriter(file, fieldnames=fieldnames, lineterminator='
')
        writer.writeheader()
        writer.writerows(data)

# load the json data
json_data = read_json('StudData.json')

write_csv(json_data, './student_data.csv')

Pandas:

import pandas as pd

df = pd.read_json('./StudData.json')
df.to_excel('./student_data.xlsx')

Output (excel screenshot):