我有一个excel文件，我想转换为JSON文件，excel类似于：Col A Col C Col F
1 A EE
2 B FF
4 C FF
5 D HH
6 D HH
7 A EE
8 E EE
希望JSON遵循以下格式：{
"EE": {
"A": {
"Col A key":"1",
"Col A key":"7"
},
"E": {
"Col A key":"8"
},
},
"FF": {
"B": {
"Col A key":"2"
},
"C": {
"Col A key":"4"
}
},
"HH": {
"D": {
"Col A key":"5",
"Col A key":"6"
}
}
}
有什么想法用python？我尝试了多种方法，但无法解决，这是我迄今：import openpyxl, pprint, json
print('Opening workbook...')
wb = openpyxl.load_workbook('excel_form.xlsx')
sheet = wb.get_sheet_by_name('Sheet')
excel_data = {}
print('Reading rows...')
for row in range(2, sheet.max_row + 1):
Col F = sheet['F' + str(row)].value
Col C = sheet['C' + str(row)].value
Col A = sheet['A' + str(row)].value
excel_data.setdefault(Col F, {})
excel_data[Col F].setdefault(Col C, {'Col A': Col A})
# Open a new text file and write the contents of excel_data to it.
print('Writing results...')
with open('DATA.json', 'w') as resultFile:
json.dump(Matrix, resultFile)
print('Done.')
谢谢
