使用Python将Excel转换为JSON [英] Converting Excel into JSON using Python
本文介绍了使用Python将Excel转换为JSON的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个excel文件,我想将其转换为JSON
文件.因此,excel就是这样的:
I have an excel file and I want to convert it into a JSON
file. So the excel is something like this:
-------------------------
| 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
实施此操作吗?我尝试了各种方法,但没有成功.这是我到目前为止所做的:
Can anyone help me implement this using python
? I've tried various ways but without success. This is what I have done so far:
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.')
预先感谢
推荐答案
我更喜欢使用xlrd
将Excel行转换为JSON
格式.
I prefer using xlrd
to convert the Excel rows into a JSON
format.
import xlrd
from collections import OrderedDict
import json
打开工作簿并选择第一个工作表
Open the workbook and select the first worksheet
wb = xlrd.open_workbook("Excel-sheet location here")
sh = wb.sheet_by_index(0)
创建一个包含字典的列表
Create a list to hold dictionaries
data_list = []
遍历工作表中的每一行,然后将值提取到字典中
Iterate through each row in worksheet and fetch values into dict
for rownum in range(1, sh.nrows):
data = OrderedDict()
row_values = sh.row_values(rownum)
data['<Column Name1>'] = row_values[0]
data['<Column Name2>'] = row_values[1]
data_list.append(data)
写入文件:
Write to file:
with open("RulesJson.json", "w", encoding="utf-8") as writeJsonfile:
json.dump(data_list, writeJsonfile, indent=4,default=str)
这篇关于使用Python将Excel转换为JSON的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文