import openpyxl,json
wb=openpyxl.load_workbook("PTPWordData.xlsx",data_only=True)
sheetlist=wb.sheetnames
print(sheetlist)
nodeArray =[]
qid=1
#first sheet is instruction sheet for skipping that sheet loop started from 1
for i in range(1,len(sheetlist)):
    sheet=wb[sheetlist[i]]
    node={}
    node["level"]=i
    node["List"]=[]
    for r in range(2,sheet.max_row+1):
        if sheet.cell(row=r,column=1).value!=None:
            node["List"].append({
                "questionId": qid,
                "clue":sheet.cell(row=r,column=1).value
            })
            qid+=1
    nodeArray.append(node)
with open("Data.json", 'w', encoding='utf-8') as outfile:
    json.dump(nodeArray, outfile, indent=4, ensure_ascii=False)


