1. 目标Excel表
【注】
1. 工作表名字将作为json中每个工作表数据的根对象名称
2. 工作表第一列将作为每行的根名字
3. 工作表第一行将作为每行对应值的key
2. 实现代码excel2json
# -*- coding:utf-8 -*-
import xlrd
import json
import codecs
file_name = "data"
# 读取 Excel
wk = xlrd.open_workbook(file_name + ".xlsx")
# 获取所有工作表名称
sheet_names = wk.sheet_names()
# 拿到所有工作表
sheets = wk.sheets()
# 存储Excel中的所有数据
excel_data = {}
def run(sheet):
sheet_data = {}
# 第一列:id
ids = sheet.col_values(0)
# 第一行:键
keys = sheet.row_values(0)
# 行长度
row_len = sheet.row_len(0)
# 遍历行
for row_num in range(1, sheet.nrows):
_id = int(ids[row_num])
sheet_data[_id] = {}
# 遍历列
for col_num in range(1, row_len):
key = keys[col_num]
value = sheet.row_values(row_num)[col_num]
# 存row_num行col_num列对应的值
sheet_data[_id][key] = value
# 存传入表的所有数据
excel_data[sheet.name] = sheet_data
# 遍历表
for i in range(len(sheets)):
run(sheets[i])
# 将excel_data数据转化成json字符串,缩进为4
data = json.dumps(excel_data, indent=4)
# 将json字符串写入dame.json文件
with codecs.open(file_name + '.json', 'w', 'utf-8') as fir:
fir.write(data)
3.生成的json文件
{
"nicks": {
"1": {
"age": 11.0,
"name": "nick1"
},
"2": {
"age": 12.0,
"name": "nick2"
},
"3": {
"age": 13.0,
"name": "nick3"
},
"4": {
"age": 14.0,
"name": "nick4"
},
"5": {
"age": 15.0,
"name": "nick5"
},
"6": {
"age": 16.0,
"name": "nick6"
},
"7": {
"age": 17.0,
"name": "nick7"
}
},
"judys": {
"1": {
"age": 11.0,
"name": "judy1"
},
"2": {
"age": 12.0,
"name": "judy2"
},
"3": {
"age": 13.0,
"name": "judy3"
},
"4": {
"age": 14.0,
"name": "judy4"
},
"5": {
"age": 15.0,
"name": "judy5"
},
"6": {
"age": 16.0,
"name": "judy6"
},
"7": {
"age": 17.0,
"name": "judy7"
}
}
}
tip:有用的话请在右侧点👍哦!