import requests
import json
import time
import openpyxl
def fanyi_baidu(czwz,servnumber):
url =‘http://134.35.10.10/baimingdan_auto/baimingdan_auto_deal.ashx’
headers={‘user-agent’:‘Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.81 Safari/537.36’}
data = {‘baimingdan’:czwz,‘servnumber’:servnumber}
data_json = json.dumps(data)
response =requests.post(url=url,data=data_json,headers=headers)
drc_obj = response.text
return drc_obj
#时间计算
def get_current_time(input_date =‘0’):
#如果时间传入为空
if input_date ==‘0’:
ct = time.time() #-246060
local_time = time.localtime(ct)
data_head = time.strftime("%Y%m%d%H%M%S", local_time)
data_secs = abs(ct - round(ct)) * 1000
time_stamp = “%s%03d” % (data_head, data_secs)
else:
time_stamp = input_date +‘120000001’
return time_stamp
#获取表格数据 #.replace(" “,”").replace("[","").replace("]","").replace("’","").split(",")
def excel_type():
excel_nr ={}
wb = openpyxl.load_workbook(’./市场部白名单数据.xlsx’)
ws = wb.active
minr = ws.min_row
minc = ws.min_column
maxr = ws.max_row
maxc = ws.max_column
# 数据库内容
#print(minr, minc, maxr, maxc)
rngs = ws.iter_rows(min_row=minr, min_col=minc,
max_row=maxr, max_col=maxc)
row_cs = 0
for row in rngs:
if row_cs >= 1:
value = [c.value for c in row]
oid = value[0]
sanji = value[2]
#fq_time = time.strftime("%Y%m%d%H%M%S", time.localtime(value[4].timestamp()))
sjhm = str(value[5])[0:11]
sqqk = value[7]
drqk = value[8]
hsjg = value[9]
row_nr = [sanji,sjhm,sqqk,drqk,hsjg,row_cs]
#print(row_nr)
excel_nr[oid]=row_nr
row_cs += 1
try:
excel_nr.pop(None)
except:
pass
return excel_nr
#excel 数据写入
def excel_seek(hang,neirong):
wb = openpyxl.load_workbook('./市场部白名单数据.xlsx')
ws = wb.active
#写入单元格内容,因为表格行数从第二行开始,数据加1
row_id1 = "J" + str(int(hang) + 1)
ws[row_id1] = neirong
#写入查询处理时间
in_time = get_current_time()
row_id2 = "K" + str(int(hang) + 1)
ws[row_id2] = in_time
# 执行完后保存
wb.save('./市场部白名单数据.xlsx')
# 结束反馈
return '数据处理完成'
if name == ‘main’:
# 循环执行
while True:
excel_neirong = excel_type()
#循环表内数据,每次查询一个,返回后记录表中
for neirong in excel_neirong:
if excel_neirong[neirong][4]==’’ or excel_neirong[neirong][4] is None :
#print(neirong,excel_neirong[neirong])
fyjg_fk = fanyi_baidu(excel_neirong[neirong][0],excel_neirong[neirong][1])
if fyjg_fk[0:1] =='0':
fyjg_fk='不符合'
elif fyjg_fk[0:1] =='1':
fyjg_fk = '符合'
else:
fyjg_fk = '未知'
try:#如果表格被打开没有写入报错,就跳出
excel_st = excel_seek(excel_neirong[neirong][5],fyjg_fk)
print('查询',excel_neirong[neirong][1],'表格处理状态:',excel_st)
except:
pass
print('查询',excel_neirong[neirong][1],'网页反馈结果:',fyjg_fk)
time.sleep(0.5)
# 执行后延时60秒
time.sleep(3)