import os
from selenium import webdriver
from WechatPCAPI import WechatPCAPI
import time
from time import sleep
import xlwings as xw
import logging
from queue import Queue
logging.basicConfig(level=logging.INFO)
queue_recved_message = Queue()
def on_message(message):
queue_recved_message.put(message)
def main():
help(WechatPCAPI)
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=='发数据':
messageinfo=data.get('msg')
if messageinfo is not None:
wx_id=str(data.get('from_wxid'))
break
else:
continue
def driver():
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)
driver.close ()
def read_exccel():
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")
if __name__ == '__main__':
main()