阅读量 ： 2470
#!/usr/bin/env python3
# -*- coding: utf-8 -*-cmd /k cd "d:\g\edu\pet3" & python "$(FILE_NAME)"& ECHO. & PAUSE & EXIT
from openpyxl import load_workbook
import os,openpyxl,json,wget
from shipping_label import is_Residential,Estimate_Rate,create_domestic_label #这个文件存了key需要的请与 微信15859230935管理员联系
# 以下存产品的重量尺寸json数据，开单时自动取数
sku_size="""{
"LegoyoWhite-1":{"length":"1","width":"1","height":"1","distanceUnit": "in","weight":"2","massUnit": "OZ"}
}"""
# 模版文件名请用PO_Data 开头，系统自动搜索
for file in os.listdir("./"):
if file[0:7]=="PO_Data":
wb = load_workbook(file)
break
sheet = wb["PO Details"]
#表头序号配对，excel 表头有这几列就可以批量自动生成标签了
for i in range(sheet.max_column):
i+=1
if sheet.cell(row=1,column=i).value=="Customer Name":
customer_name=i
if sheet.cell(row=1,column=i).value=="Ship to Address 1":
Ship_to_Address_1=i
if sheet.cell(row=1,column=i).value=="Ship to Address 2":
Ship_to_Address_2=i
if sheet.cell(row=1,column=i).value=="City":
get_city=i
if sheet.cell(row=1,column=i).value=="State":
state=i
if sheet.cell(row=1,column=i).value=="Zip":
zip=i
if sheet.cell(row=1,column=i).value=="Customer Phone Number":
Customer_Phone_Number=i
if sheet.cell(row=1,column=i).value=="SKU":
SKU=i
if sheet.cell(row=1,column=i).value=="Qty":
QTY=i
if sheet.cell(row=1,column=i).value=="Update Status":
Update_Status=i
if sheet.cell(row=1,column=i).value=="Update Qty":
Update_Qty=i
if sheet.cell(row=1,column=i).value=="Carrier":
Carrier=i
if sheet.cell(row=1,column=i).value=="Tracking Number":
Tracking_Number=i
#发货地址json数据
from_address='''
"from": {
"personName": "Uyou_legoyo",
"countryCode": "US",
"phone": "9738943479",
"stateCode": "NJ",
"city": "Passaic",
"addressLine1": "90 Dayton AVE",
"addressLine2": "Gate A Building 18 i",
"zipCode": "07055"
}
'''
max_column=sheet.max_column
for i in range(sheet.max_row-1):
download_file_name=str(i+1)+'_'+str(sheet.cell(row=i+2,column=SKU).value)+".pdf"
if os.path.isfile(download_file_name):
continue
to_address='{"personName": "'+str(sheet.cell(row=i+2,column=customer_name).value)+'","countryCode":"US","phone": "'+str(sheet.cell(row=i+2,column=Customer_Phone_Number).value)+'","stateCode":"'+str(sheet.cell(row=i+2,column=state).value)+'","city":"'+str(sheet.cell(row=i+2,column=get_city).value)+'","addressLine1":"'+str(sheet.cell(row=i+2,column=Ship_to_Address_1).value)+'","addressLine2":"'+str(sheet.cell(row=i+2,column=Ship_to_Address_2).value)+'","zipCode":"'+str(sheet.cell(row=i+2,column=zip).value)+'"}'
#print(to_address)
if str(sheet.cell(row=i+2,column=SKU).value)[0:6]=='Legoyo':
parcels='"labelSize":1,"isTest": false,"parcels": [{"packageNum": 1,"length": "'
temp1=str(sheet.cell(row=i+2,column=SKU).value)
extra=',"extra": {"reference": "'+temp1[0:len(temp1)-2]+" QTY "+str(int(temp1[len(temp1)-1])*int(sheet.cell(row=i+2,column=QTY).value))+'"},'
else:
extra=',"extra": {"reference": "'+str(sheet.cell(row=i+2,column=SKU).value)+' QTY 1"},'
parcels='"labelSize":1,"isTest": false,"parcels": [{"packageNum":'+str(sheet.cell(row=i+2,column=QTY).value)+',"length": "'
#包裹尺寸
sku_size1=json.loads(sku_size)
if sku_size1[str(sheet.cell(row=i+2,column=SKU).value)]:
get_sku_size=sku_size1[str(sheet.cell(row=i+2,column=SKU).value)]
parcels=parcels+str(int(get_sku_size['length']))#长
parcels=parcels+'","width": "'+str(int(get_sku_size['width']))#宽
parcels=parcels+'","height": "'+str(int(get_sku_size['height']))+'","distanceUnit": "in","weight": "'#高
if get_sku_size['massUnit']=='LB':
parcels=parcels+str(int(get_sku_size['weight']))+'","massUnit": "lb","packageCode": "your_package","extra": {"insurance": 0,"isCod": false,"codAmount": 0,"paymentMethod": "any","dryIceWeight": 0}}],'
shipping_type=[
'"carrierCode": "usps","serviceCode": "usps_parcel_select"',
'"carrierCode": "usps","serviceCode": "usps_priority"',
'"carrierCode": "ups","serviceCode": "ups_ground"']
if is_Residential(to_address):
shipping_type.append('"carrierCode": "fedex","serviceCode": "fedex_home_delivery"')
to_address=to_address.replace('}',',')+'"isResidential": true}'
else:
shipping_type.append('"carrierCode": "fedex","serviceCode": "fedex_ground"')
to_address=to_address.replace('}',',')+'"isResidential": false}'
else:
parcels=parcels+str(int(get_sku_size['weight'])*0.0625)+'","massUnit": "lb","packageCode": "your_package","extra": {"insurance": 0,"isCod": false,"codAmount": 0,"paymentMethod": "any","dryIceWeight": 0}}],'
shipping_type=['"carrierCode": "usps","serviceCode": "usps_first"']
col=0
get_rate=10000.00
for ship_type in shipping_type:
col+=1
shipping_address='{'+from_address+',"to": '+to_address+extra+parcels+ship_type+'}'
#print (shipping_address)
estimate_rate1,estimate_rate2=Estimate_Rate(shipping_address)
if estimate_rate1==1:
get_rate1=round(estimate_rate2,2)
else:
sheet.cell(row=(i+2),column=max_column+col,value=estimate_rate2)
#print(estimate_rate2)
break
if get_rate1<get_rate:
get_rate=get_rate1
get_shipping_address=shipping_address
get_shipping_type=ship_type
if get_rate<10000:
#print(get_shipping_address)
print(get_rate)
get_status,a,b,c,d=create_domestic_label(get_shipping_address)
if get_status==1:
wget.download(c,download_file_name)
tracking_number=''
for b1 in b:
tracking_number=tracking_number+b1+','
sheet.cell(row=(i+2),column=Update_Status,value='ship')
sheet.cell(row=(i+2),column=Update_Qty,value=sheet.cell(row=(i+2),column=QTY).value)
carrier=get_shipping_type.replace(':',',')
carrier=carrier.replace('"','')
carrier1=carrier.split(',')
if carrier1[1].upper()=='FEDEX':
carrier2='FedEx'
else:
carrier2=carrier1[1].upper()
sheet.cell(row=(i+2),column=Carrier,value=carrier2)
sheet.cell(row=(i+2),column=Tracking_Number,value=tracking_number[0:len(tracking_number)-1])
else:
sheet.cell(row=(i+2),column=max_column+col,value=a)
# if os.path.isfile("new_"+file):
# print ('old file : '+"new_"+file+" will be removed!")
# os.remove("new_"+file)
wb.save(file)
print("保存excel成功,第 "+str(i+1))