Dictionary method for xlsx file and write to json format

See more:

JSON

dictionary

Python3.3

I have xlsx file with information about sports grounds (cell 5 - area, where sport ground is situated, cell 6 - district in the area, 7 - address in the district). I need json dictionary, where areas (AdmArea) will be keys of big dictionary, values will be small dictionary with districts as keys, addresses (in list format) will be values for small dictionary. Of course each area has more than one district, each district in the meantime has more than one address. Necessary for addresses to be in the order of appearance in the excel file.

This is file xlsx: https://stepik.org/media/attachments/lesson/268674/data-25290-2019-09-30.xlsx
My code prints out for each admArea not corresponding districts. I need in case for the similar area correspond to it all the districts, which are included in this area. It prints out randomly!

What I have tried:

import json

from collections import defaultdict

import openpyxl

wb = openpyxl.load_workbook('data-25290-2019-09-30.xlsx')

fout = open('outa12.json', 'w', encoding='utf-8')

d1 = {}


res = defaultdict(list)

sheet = wb['Sheet0']

for i in range(2, sheet.max_row+1):

    admArea = sheet.cell(row=i,column=5).value

    distr = sheet.cell(row=i,column=6).value

    addr = sheet.cell(row=i,column=7).value

    res[distr].append(addr)

    d1[admArea]=res

json.dump(d1, fout, ensure_ascii=False)

fout.close()