excel转json

1、需求：原始数据格式如图1所示，需要转成图2所示的json格式

2、脚本

思路：

• 把最后6列拼接为一列值

• 在新的文件中，每次拿出id相同的数据，转json

• 每一次的结果用一个列表累加保存

# coding=utf-8
import pandas as pd
import json
import xlrd
import openpyxl
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
import openpyxl.styles as sty
from openpyxl import Workbook ,load_workbook

def to_json2(df,orient='split'):
    df_json = df.to_json(orient = orient, force_ascii = False)
    return json.loads(df_json)
def to_fronrend(data):
    return {"data": data}

##将file文件中的后6列拼接成一列new_value，保存为new_file
file = '../data/data.xlsx'
new_file = './data.xlsx'
df = pd.read_excel(file)
# 将df的第3列开始转换为字符形，方便后续拼接
for i in range(3,len(df.columns)):
    df.iloc[:,i]=df.iloc[:,i].map(lambda x:str(x))
# 将df的后面六列进行拼接  
str1 = df.iloc[:,3].str.cat(df.iloc[:,4],sep='-')
str2 = df.iloc[:,5].str.cat(df.iloc[:,6],sep='-')
str3 = df.iloc[:,7].str.cat(df.iloc[:,8],sep='-')
want_str=str1+','+str2+','+str3
want_str=pd.DataFrame(want_str,columns=['new_value'])
new_data = pd.concat([df.iloc[:,0:3],want_str],axis=1)
result=[]
for i in range(1,16):
    print("———————第",i,"次计算：—————————————")
    ##每一次new_data取值为id相同的数据，保存成一个excel
    data_to_save=new_data[new_data['id']==i]
    data_to_save.to_excel(new_file)
    data_to_save = data_to_save.reset_index(drop=True)
    #print(pd.read_excel(new_file,index_col=0))
    
    ##开始处理成json
    
    value,label=data_to_save['id'][0],data_to_save['一级分类'][0]
    #print(value,label)
    wb=load_workbook(new_file)
    
    for ws in wb:
        c = ws.max_column
        r = ws.max_row
        list_key=[]
        jsonLine=[]
        datalines=[]
        dict_v={}
   
        for col in range(2,c+1):
            list_key.append(ws.cell(row=1,column=col).value)
    
        for col in range(2,c+1):
            dataline_value,dataline_label,dataline_children=[],[],[]
            for row in range(2,r+1):
                data_children={}               
                data_children["value"]=ws.cell(row=row,column=5).value
                data_children["label"]=ws.cell(row=row,column=4).value
                dataline_children.append(data_children)
            dict_c={}
            dict_c["value"]=value
            dict_c["label"]=label
            dict_c["children"]=dataline_children
    result.append(dict_c)

#将list保存为json文件
filename= './二级联动.json'
with open(filename,'w') as file_obj:
    json.dump(result,file_obj,ensure_ascii=False)