python 数据json处理 写入excel

# -- coding: utf-8 --**
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#pip install pandas
#pip install openpyxl
#
#
#
#http://192:8090/nationExchangeLogController/uploadlogDetailList.do?order=asc&limit=130000&offset=0&startTime=2021-01-01&endTime=2021-04-30&agId=6718&level=20&levelType=1&serviceType=1&reportBillType=3&uploadStaus=0&headNo=&failReason=&_=1669099702149
#
#
#

import json
import time
import pandas
import datetime




with open('xinguan2-24.txt', 'rb') as f:
#with open('xcv.txt', 'rb') as f:
    try:
        data = json.load(f)
        #print(data)
        #print(type(data))
        #print(len(data))
        #for i in int(range(data)):
        #    print(data[i])
        '''
        for i in data:
            #print(i)
            print(i['auditTime'])
            time.sleep(6)
            '''
    except Exception as err:
        print(err)
#print(data)

dataOutput = {
"addtype":[],
"approvalNumber":[],

"baspSpecCode":[],

"batchId":[],

"batchNo":[],


"batchUse":[],

"cndc":[],

"createDate":[],

"creator":[],

"creatorId":[],

"creatorLoginAccount":[],

"editDate":[],

"editDateStr":[],

"editor":[],


"editorId":[],


"madeDate":[],

"madeDateStr":[],


"prodSpecInfo":[],
#vaccineProperties,vaccName,vaccDosage,vaccPackage,vaccOthx1,vaccOthx2,vaccOthx3
"vaccineProperties":[],

"vaccName":[],

"vaccDosage":[],

"vaccPackage":[],

"vaccOthx1":[],

"vaccOthx2":[],

"vaccOthx3":[],


"productInfo":[],


"psfid":[],

"specifications":[],

"valiDateStr":[],


"validateDate":[]
}

indexNum = 1




for i in data:
    #print(i['auditTime'])
    if "addtype" in i:
        dataOutput["addtype"].append(i["addtype"])
    else:
        dataOutput["addtype"].append("null")

    if "approvalNumber" in i:
        dataOutput["approvalNumber"].append(i["approvalNumber"])
    else:
        dataOutput["approvalNumber"].append("null")
    

    if "baspSpecCode" in i:
        dataOutput["baspSpecCode"].append(i["baspSpecCode"])
    else:
        dataOutput["baspSpecCode"].append("null")
        

    if "batchId" in i:
        dataOutput["batchId"].append(i["batchId"])
    else:
        dataOutput["batchId"].append("null")

    if "batchNo" in i:
        dataOutput["batchNo"].append(i["batchNo"])
    else:
        dataOutput["batchNo"].append("null")

        

    if "batchUse" in i:
        dataOutput["batchUse"].append(i["batchUse"])
    else:
        dataOutput["batchUse"].append(i["headId"])





    if "cndc" in i:
        dataOutput["cndc"].append(i["cndc"])
    else:
        dataOutput["cndc"].append("null")

        

    if "createDate" in i:
        dataOutput["createDate"].append(i["createDate"])
    else:
        dataOutput["createDate"].append("null")

        
        
    if "creator" in i:
        dataOutput["creator"].append(i["creator"])
        #print("key ok")
        #print(i.keys())
    else:
        #print("no msgReason key")
        dataOutput["creator"].append("null")
        #print(i.keys())
        #dataOutput["msgReason"].append(i["null"])
        #dataOutput.add("msgReason")
        #dataOutput["msgReason"] = "null"
        #dataOutput.update(msgReason = "null")
        #i.update(msgReason= "null")
        #print(i.keys())
        #time.sleep(6)


        
    if "creatorId" in i:
        dataOutput["creatorId"].append(i["creatorId"])
    else:
        dataOutput["creatorId"].append("null")

        

    if "creatorLoginAccount" in i:
        dataOutput["creatorLoginAccount"].append(i["creatorLoginAccount"])
    else:
        dataOutput["creatorLoginAccount"].append("null")

        

    if "editDate" in i:
        dataOutput["editDate"].append(i["editDate"])
    else:
        dataOutput["editDate"].append("null")

        

    if "editDateStr" in i:
        dataOutput["editDateStr"].append(i["editDateStr"])
    else:
        dataOutput["editDateStr"].append("null")

        

    if "editor" in i:
        dataOutput["editor"].append(i["editor"])
    else:
        dataOutput["editor"].append("null")

    if "editorId" in i:
        dataOutput["editorId"].append(i["editorId"])
    else:
        dataOutput["editorId"].append("null")

        

    if "madeDate" in i:
        dataOutput["madeDate"].append(i["madeDate"])
    else:
        dataOutput["madeDate"].append("null")

        

    if "madeDateStr" in i:
        dataOutput["madeDateStr"].append(i["madeDateStr"])
    else:
        dataOutput["madeDateStr"].append("null")

        
    #vaccineProperties,vaccName,vaccDosage,vaccPackage,vaccOthx1,vaccOthx2,vaccOthx3
    if "prodSpecInfo" in i:
        
        dataOutput["prodSpecInfo"].append(i["prodSpecInfo"])

        #add
        dataOutput["vaccineProperties"].append(i["prodSpecInfo"].split('/')[0])
        dataOutput["vaccName"].append(i["prodSpecInfo"].split('/')[1])
        dataOutput["vaccDosage"].append(i["prodSpecInfo"].split('/')[2])
        dataOutput["vaccPackage"].append(i["prodSpecInfo"].split('/')[3])
        dataOutput["vaccOthx1"].append(i["prodSpecInfo"].split('/')[4])
        dataOutput["vaccOthx2"].append(i["prodSpecInfo"].split('/')[5])
        dataOutput["vaccOthx3"].append(i["prodSpecInfo"].split('/')[6])
        
    else:
        dataOutput["vaccineProperties"].append("null")
        dataOutput["vaccName"].append("null")
        dataOutput["vaccDosage"].append("null")
        dataOutput["vaccPackage"].append("null")
        dataOutput["vaccOthx1"].append("null")
        dataOutput["vaccOthx2"].append("null")
        dataOutput["vaccOthx3"].append("null")


        
        #dataOutput["prodSpecInfo"].append("null")
        
        #print(len(i["prodSpecInfo"].split("/")))
        #for i in range(len(i["prodSpecInfo"].split("/"))):
        #    print(i)
        #print(i["prodSpecInfo"].split('/')[0])
        #print(i["prodSpecInfo"].split('/')[1])
        #print(i["prodSpecInfo"].split('/')[2])
        #print(i["prodSpecInfo"].split('/')[6])
        
        

    #debug
    #time.sleep(3)

        

    if "productInfo" in i:
        dataOutput["productInfo"].append(i["productInfo"])
    else:
        dataOutput["productInfo"].append("null")

        

    if "psfid" in i:
        dataOutput["psfid"].append(i["psfid"])
    else:
        dataOutput["psfid"].append("null")
        

    if "specifications" in i:
        dataOutput["specifications"].append(i["specifications"])
    else:
        dataOutput["specifications"].append("null")

        
    if "valiDateStr" in i:
        dataOutput["valiDateStr"].append(i["valiDateStr"])
    else:
        dataOutput["valiDateStr"].append("null")

        
    if "validateDate" in i:
        dataOutput["validateDate"].append(i["validateDate"])
    else:
        dataOutput["validateDate"].append("null")



    
    
    print("正在处理" + str(indexNum) + "行")
    indexNum += 1

print(dataOutput.keys())
#print(len(dataOutput["vaccNum"]))
for i in dataOutput.keys():
    #print(i)
    #print(len(dataOutput(str(i))))

    print(len(dataOutput[i]))

#print(dataOutput)
print("正在写入Excel")


currentTime = datetime.datetime.now()
dataForm = pandas.DataFrame(dataOutput)
dataForm.to_excel("批号-" + currentTime.strftime("%Y%m%d") + '.xlsx')
print("写入完成")