import os
from selenium import webdriver
from WechatPCAPI import WechatPCAPI
import time
import datetime
import xlwings as xw
import logging
from queue import Queue
from PIL import ImageGrab
logging.basicConfig(level=logging.INFO)
queue_recved_message = Queue()
def on_message(msg):
queue_recved_message.put(msg)
def main():
wx_inst = WechatPCAPI (on_message=on_message, log=logging)
wx_inst.start_wechat (block=True)
while not wx_inst.get_myself ():
time.sleep (2)
while True:
msg=queue_recved_message.get()
if msg.get("type")=="msg::single":
data=msg.get('data')
send_or_recv=data.get('send_or_recv')
char_type=data.get("data_type")
if send_or_recv=='0+[收到]'and char_type=='1':
if msg['data']['from_wxid'] not in ['floatbottle', 'medianote'] and msg['data']['msg'] in [
'鸿运当头'] and \
msg['data']['send_or_recv'][0] == '0':
wx_inst.send_text (to_user=msg['data']['from_wxid'], msg='收到,很高兴为您服务,执行完毕需要6分钟')
else:
break
profile = webdriver.FirefoxProfile ()
profile.set_preference ('browser.download.dir', 'E:\\下载')
profile.set_preference ('browser.download.folderList', 2)
profile.set_preference ('browser.download.manager.showWhenStarting', False)
profile.set_preference ('browser.helperApps.neverAsk.saveToDisk', 'application/x-excel')
driver = webdriver.Firefox (firefox_profile=profile)
driver.get ('http://report2.hualife.cc:8071/WebReport/ReportServer?op=fs')
driver.maximize_window ()
time.sleep (3)
driver.find_element_by_xpath ("/html/body/div[2]/div[3]/div[2]/input").send_keys ("*******")
driver.find_element_by_xpath ("/html/body/div[2]/div[3]/div[3]/input").send_keys ("*******")
driver.find_element_by_id ("fs-login-btn").click ()
time.sleep (3)
driver.find_element_by_xpath ("/html/body/div[1]/div[1]/div[1]/div[1]/div/ul/li[1]/a/span[2]").click ()
driver.find_element_by_xpath (
"/html/body/div[1]/div[1]/div[1]/div[1]/div/ul/li[1]/ul/li[1]/a/span").click ()
driver.find_element_by_xpath (
"/html/body/div[1]/div[1]/div[1]/div[1]/div/ul/li/ul/li[1]/ul/li[1]/a/span").click ()
time.sleep (2)
driver.switch_to.frame (driver.find_element_by_xpath ("/html/body/div[1]/div[2]/div/div[4]/iframe[2]"))
time.sleep (4)
driver.find_element_by_xpath ("/html/body/div[1]/div[1]/div[2]/div/div[12]/div[2]/div").click ()
driver.find_element_by_xpath ("/html/body/div[3]/div[3]/div[2]").click ()
driver.find_element_by_xpath ("/html/body/div[1]/div[1]/div[2]/div/div[19]/div[2]/div").click ()
driver.find_element_by_xpath ("/html/body/div[1]/div[1]/div[2]/div/div[19]/div[2]/div").click ()
driver.find_element_by_xpath ("/html/body/div[3]/div[5]/table[1]/tbody/tr[1]/td[6]").click ()
driver.find_element_by_xpath ("/html/body/div[1]/div[1]/div[2]/div/div[16]/div/div/em/button").click ()
time.sleep (7)
driver.find_element_by_xpath ("/html/body/div[2]/table/tbody/tr/td[11]/div/div/div/em/button").click ()
time.sleep (20)
wx_inst.send_text (to_user=msg['data']['from_wxid'], msg='数据抽取完毕,正在更新KPI')
app = xw.App (visible=True, add_book=False)
wb = app.books.open (r'E:/䐁蕳/营销渠道保单清单.xlsx')
time.sleep (2)
sht = wb.sheets['sheet1']
sht.api.Rows (2).Delete ()
sht.api.Rows (1).Delete ()
sht.api.Columns (1).Delete ()
time.sleep (2)
exist_list = ["正常有效", "标体通过", "承保", "人工核保", "新增险种", "自核通过"]
rows = sht.api.UsedRange.Rows.count
cols = sht.api.UsedRange.columns.count
rng = sht[0, :cols]
for cell in rng:
if cell.value == '保单状态':
print (cell.address)
a = cell.address
a_range = sht.range ("AO2:AO" + str (rows))
cell_list = []
for cell in a_range:
cell_list.append (cell)
cell_list.reverse ()
print (" 开始调整行和列……")
for cell in cell_list:
if cell.value is not None:
find_flag = 0
for exist_value in exist_list:
if cell.value.find (exist_value) != -1:
find_flag = 1
break
else:
continue
if find_flag == 0:
cell_to_del = cell.address
sht.range (cell_to_del).api.EntireRow.Delete ()
time.sleep (2)
driver.close ()
wb1 = app.books.open (r"C:/Users/Administrator/Desktop/KPI2.xlsm")
time.sleep (8)
sht1 = wb1.sheets['更新业绩表']
sht1.range ("a1").expand ("table").clear_contents ()
wb.sheets["sheet1"].range ("a1").expand ("table").api.Copy (sht1.range ('a1').api)
wb.close ()
os.remove (r'E:/䐁蕳/营销渠道保单清单.xlsx')
app.calculate ()
time.sleep (10)
wb1.api.Application.Run ("all")
time.sleep(1)
sht2= wb1.sheets['数据简表']
wb1.macro ('sheet5.test') ()
time.sleep(1)
N1 = str (int(sht2.range ("D12").value))
N2=str (round(sht2.range ("E12").value,1))
N3 = str ('{:.1f}%'.format (sht2.range ("F12").value * 100))
N4 = str (int (sht2.range ("J12").value))
N5 = str (int (sht2.range ("K12").value))
N6 = str ('{:.1f}%'.format (sht2.range ("L12").value * 100))
N7 = str (int (sht2.range ("N12").value))
N8 = str (int (sht2.range ("O12").value))
N9 = str ('{:.1f}%'.format (sht2.range ("P12").value * 100))
curr_time = datetime.datetime.now ()
N10 = str (curr_time.month)
img1 = ImageGrab.grabclipboard ()
img1.save (r'E:/picture/img1.png')
time.sleep(1)
wx_inst.send_img (to_user=msg['data']['from_wxid'], img_abspath=r'E:/picture/img1.png')
time.sleep(1)
wx_inst.send_text (to_user=msg['data']['from_wxid'], msg=('''——[福]每日KPI[福]——\n''')+
str.lstrip('''[烟花]尊敬的各位领导,同事:\n''')+
str.lstrip('''[爱心]上表为'''+N10+'''月KPI简表,请查收。\n''')+
str.lstrip('''[太阳]中支整体'''+N10+'''月必保保费目标'''+N1+'''万,截止目前预收已达成'''+N2+'''万,达成率'''+N3+'''。\n''')+
str.lstrip('''[發]中支整体'''+N10+'''月必保实动目标'''+N4+'''人,截止目前已达成'''+N5+'''人,达成率'''+N6+'''。\n''')+
str.lstrip('''[玫瑰]主管在册'''+N7+'''人,实动'''+N8+'''人,实动率'''+N9+'''。'''))
time.sleep(5)
wb1.close ()
app.quit ()
os.remove (r'E:/picture/img1.png')
time.sleep(10)
if __name__ == '__main__':
main()