def data():
wb=openpyxl.load_workbook('学期上课记录表.xlsx',data_only=True)
dict_student_sign={}
for each_wsname in wb.sheetnames:
if each_wsname=='课时':
continue
ws=wb[each_wsname]
for wsrow in range(4,ws.max_row):
if not ws.cell(wsrow,2).value in [None,'\n','\r',''] and not ws.cell(wsrow,2).value in list(dict_student_sign.keys()):
dict_student_sign[ws.cell(wsrow,2).value]={}
dict_student_sign[ws.cell(wsrow,2).value]['考勤']=[]
for each_wsname in wb.sheetnames:
ws=wb[each_wsname]
if each_wsname=='课时':
for wsrow in range(2,ws.max_row+1):
dict_student_sign[ws.cell(wsrow,1).value]['课时']=ws.cell(wsrow,2).value
wscolumn=3
str_=''
while True:
if ws.cell(wsrow,wscolumn).value==None:
break
else:
str_=str_+str(ws.cell(wsrow,wscolumn).value)+'+'
wscolumn=wscolumn+1
dict_student_sign[ws.cell(wsrow,1).value]['考勤'].append('总课时：'+str_[:-1])
continue
for wsrow in range(4,ws.max_row+1):
if ws.cell(wsrow,2).value in list(dict_student_sign.keys()):
for wscolumn in range(3,33):
if ws.cell(wsrow,wscolumn).value in ['出勤','未出勤','补课']:
x=3
while True:
if wsrow-x>7:
x=x+7
else:
break
if ws.cell(wsrow-(wsrow-x),wscolumn).value in [None,'\n','\r','']:
title=''
else:
title=ws.cell(wsrow-(wsrow-x),wscolumn).value
if ws.cell(wsrow,wscolumn).value in ['未出勤']:
dict_student_sign[ws.cell(wsrow,2).value]['考勤'].append(each_wsname+str(ws.cell(1,wscolumn).value)+'日\n'+ws.cell(wsrow,wscolumn).value+title+'☆余'+str(dict_student_sign[ws.cell(wsrow,2).value]['课时']-0)+'课时')
else:
dict_student_sign[ws.cell(wsrow,2).value]['考勤'].append(each_wsname+str(ws.cell(1,wscolumn).value)+'日\n'+ws.cell(wsrow,wscolumn).value+title+'★余'+str(dict_student_sign[ws.cell(wsrow,2).value]['课时']-2)+'课时')
dict_student_sign[ws.cell(wsrow,2).value]['课时']=dict_student_sign[ws.cell(wsrow,2).value]['课时']-2
#print(dict_student_sign)
return dict_student_sign
效果：
返回了字典
三、完善功能
查询已发信息（token，cookie需要手动修改）：
[Python] 纯文本查看复制代码
def chat(token):
header={
'Host': 'mp.weixin.qq.com',
'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.45 Safari/537.36',
'Cookie': '自己添加'
}
url='https://mp.weixin.qq.com/advanced/autoreply'
param={
'action': 'smartreply',
'token': token,
'lang': 'zh_CN',
'count': 200,
'offset': 0
}
rep=requests.get(url,headers=header,params=param)
soup=BeautifulSoup(rep.text)
for each_script in soup.find_all('script',type='text/javascript'):
if '// 开启了关键词回复' in str(each_script):
text=re.sub('\n|\r| |<.*?>|//.*,//','',str(each_script))
text=re.sub(',func_ban_info:\\[//能力封禁\\]|\\.replace.*?\\)|//用数组是为了兼容appmsg_gray|\\*1','',text)
text=re.findall('list:(\\[.*\\])',text)[0]
text=re.sub(',}','}',text)
text=json.loads(text)
#print(text)
dict_content={}
for each_student in text:
list_content=[]
student_name=each_student['keyword_list'][0]['content']
ruleid=each_student['rule_id']
dict_content[student_name]={}
dict_content[student_name]['rule_id']=ruleid
if each_student['keyword_list'][0]['content']!=None:
for each_reply_list in each_student['reply_list']:
for each_content in each_reply_list['content'].split('时\n'):
if each_content!='':
list_content.append(each_content+'时')
dict_content[student_name]['content']=list_content
#print(dict_content)
return dict_content
效果：
更新自动回复（token，cookie需要手动修改）：
[Python] 纯文本查看复制代码
def function(excel_data,web_chat,token):
header={
'Referer': 'https://mp.weixin.qq.com/advanced/autoreply?action=smartreply&t=ivr/keywords&token='+str(token)+'&lang=zh_CN',
'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8',
'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.45 Safari/537.36',
'Cookie': '自己添加'
}
url='https://mp.weixin.qq.com/advanced/setreplyrule'
param={
'lang': 'zh_CN',
'cgi': 'setreplyrule',
'fun': 'save',
't': 'ajax-response',
'token': token
}
for each_data in excel_data.keys():
data={'token': token,'lang': 'zh_CN','f': 'json','ajax': 1,'replytype': 'smartreply','allreply': 1,'replycnt': 1,'keywordcnt': 1,'matchmode0': 1}
if not each_data in list(web_chat.keys()):
data['ruleid']=0
else:
data['ruleid']=web_chat[each_data]['rule_id']
if excel_data[each_data]['考勤']==web_chat[each_data]['content']:
continue
data['rulename']=each_data+'剩余课时'
data['keyword0']=each_data
num_content=0
str_content=''
for each_content in excel_data[each_data]['考勤']:
if len(str_content)<=270:
str_content=str_content+each_content+'\n'
data['content'+str(num_content)]=str_content
data['type'+str(num_content)]=1
data['fileid'+str(num_content)]='undefined'
else:
num_content=num_content+1
str_content=''
all_content=0
for content_content in data.keys():
if 'content' in content_content:
all_content=all_content+1
data['replycnt']=all_content
data=urllib.parse.urlencode(data)
print(each_data+' 更新成功')
rep=requests.post(url,headers=header,params=param,data=data)
time.sleep(2)
[复制链接]
发表于 2021-12-19 15:50
我也想给我的公众号搞一个