Python代码实现Excel转JSON

import pandas as pd
import json

data = pd.read_excel(r'/Users/wanglingyi/Desktop/Excel转json/2020本科专业目录.xlsx',sheet_name='汇总')

json_list = []
for name in data['门类'].unique():                           #选门类 
    name_dict={ 
   }                    #建立该门类的字典，存储该门类的名字和专业类及其信息 
    name_dict['name'] = name                                 #存储门类名字 
    name_options = []                                        #准备存储信息 
    name_data = data[data['门类']==name]                     #数据降级 
    #print(name_data) #逐层检查数据是否正确

    for kind in name_data['专业类'].unique():                 #选专业类 
        kind_dict={ 
   }                                         #建立专业类字典，放专业类名字和信息 
        kind_dict['name'] = kind                             #存储专业类名字 
        options = []                                       	 #准备存储信息
        kind_data = name_data[name_data['专业类']==kind]      #数据降级
        #print(kind_data) #逐层检查数据是否正确
        
				#定专业，专业代码唯一，所以不再限定
        for code in kind_data['专业代码']:                            
            #根据上面推出要找代码一样的数据
            code_data = kind_data[kind_data['专业代码']== code ]       
            info = { 
   }                                      #取数据
            info['code'] = code                                      
            for name in code_data['专业名称']:
                info['name'] = name
            for grant in code_data['学位授予门类']:
                info['grant'] = grant
            for years in code_data['修业年限']:
                info['years'] = years
            #print(info) #逐层检查数据是否正确
            options.append(info)
            #print(options) #逐层检查数据是否正确

        kind_dict['options'] = options                   #填充数据
        name_options.append(kind_dict)

    name_dict['options'] = name_options
    json_list.append(name_dict) 
    #print(json_list) #展示结果（先预览看一下）

#确定结果格式正确后以文件形式输出
json_dict = { 
   }
json_dict['data'] = json_list
data_dict = json.dumps(json_dict, ensure_ascii=False)
with open('data.json','w') as f_w:
        f_w.write(data_dict)

{ 
   
  "data": [
    { 
   
      "name": "哲学",
      "options": [
        { 
   
          "name": "哲学类",
          "options": [
            { 
   
              "code": "010101",
              "name": "哲学",
              "grant": "哲学",
              "years": "四年"
            },
            { 
   
              "code": "010102",
              "name": "逻辑学",
              "grant": "哲学",
              "years": "四年"
            },
            { 
   
              "code": "010103K",
              "name": "宗教学",
              "grant": "哲学",
              "years": "四年"
            },
            { 
   
              "code": "010104T",
              "name": "伦理学",
              "grant": "哲学",
              "years": "四年"
            }
          ]
        }
      ]
    }
   ]
} .........