校验并往Excel模板填数据

import pandas as pd
import os
import openpyxl
from openpyxl import load_workbook
# import xlrd
# import xlwt
# from xlutils.copy import copy

jsonObj = ${input}
typeOfJsonObj = type(jsonObj)
print("typeOfJsonObj:",typeOfJsonObj)
print("jsonObj:",jsonObj)

customerPoNo = jsonObj["customerPoNo"]
print("customerPoNo:",customerPoNo)
shipmentNo = jsonObj["shipmentNo"]
print("shipmentNo:",shipmentNo)

if  'sourceLocation' in jsonObj:
    sourceLocation = jsonObj["sourceLocation"]
    print("sourceLocation:",sourceLocation)
else :
    print("键 sourceLocation 不存在")
    sourceLocation = "-"
    
if  'estimatedArrivalDate' in jsonObj:
    estimatedArrivalDate = jsonObj["estimatedArrivalDate"]
    print("estimatedArrivalDate:",estimatedArrivalDate)
else :
    estimatedArrivalDate = "-"

if  'totalItemPackageCount' in jsonObj:
    totalItemPackageCount = jsonObj["totalItemPackageCount"]
    print("totalItemPackageCount:",totalItemPackageCount)
else :
    totalItemPackageCount = "-"
    
if  'submitTenderDate' in jsonObj:
    submitTenderDate = jsonObj["submitTenderDate"]
    print("submitTenderDate:",submitTenderDate)
else :
    submitTenderDate = "-"
    
if  'totalGrossWeight' in jsonObj:
    totalGrossWeight = jsonObj["totalGrossWeight"]
    print("totalGrossWeight:",totalGrossWeight)
else :
    totalGrossWeight = "-"
    
if  'detailDeliveryAddress' in jsonObj:
    detailDeliveryAddress = jsonObj["detailDeliveryAddress"]
    print("detailDeliveryAddress:",detailDeliveryAddress)
else :
    detailDeliveryAddress = "-"
   
    
if  'lineInformation' in jsonObj:
    lineInformation = jsonObj["lineInformation"]
    typeOfJsonlineInformation = type(lineInformation)
    print("typeOfJsonlineInformation:",typeOfJsonlineInformation)
    print("lineInformation:",lineInformation)
else :
    lineInformation = {'quantity': '-', 'itemCode': '-', 'engDescription': '-', 'barcode': '-'}

file = POtemplate
print("file:",file)
filePath = file.replace("\\", "\\\\")
file = eval(repr(filePath).replace('\\\\', '\\'))
print("file:",file)

wb = openpyxl.load_workbook(file)

# 获取workbook中所有的表格
sheets = wb.sheetnames
sheetsOfType = type(sheets)
print("sheetsOfType:",sheetsOfType)

sheet = wb['DN_1']
sheet.cell(2, 2).value = customerPoNo
sheet.cell(6, 2).value = shipmentNo
sheet.cell(7, 2).value = detailDeliveryAddress
sheet.cell(9, 2).value = totalItemPackageCount
sheet.cell(10, 2).value = totalGrossWeight
sheet.cell(11, 2).value = submitTenderDate
sheet.cell(12, 2).value = estimatedArrivalDate
sheet.cell(13, 2).value = sourceLocation

index = 0
for ele in lineInformation:
    typeOfEle = type(ele)
    print("typeOfEle:",typeOfEle)
    
    if  'quantity' in ele:
        quantity = ele["quantity"]
        print("quantity:",quantity)
    else :
        quantity = "-"
    sheet.cell(18+index, 4).value = quantity
    
    if  'itemCode' in ele:
        itemCode = ele["itemCode"]
        print("itemCode:",itemCode)
    else :
        itemCode = "-"
    sheet.cell(18+index, 3).value = itemCode
    
    if  'engDescription' in ele:
        engDescription = ele["engDescription"]
        print("engDescription:",engDescription)
    else :
        engDescription = "-"
    sheet.cell(18+index, 2).value = engDescription
    
    
    if  'barcode' in ele:
        barcode = ele["barcode"]
        print("barcode:",barcode)
    else :
        barcode = "-"
    sheet.cell(18+index, 5).value = barcode
    
    index = index + 1
    
wb.save(file)