# 读取文件
filename = r'data/01 爬取微信公众号历史文章/urls 二律背反的一灯如豆-out.xlsx'
df = pd.read_excel(filename,dtype=object)
df = df.reindex(columns=['日期', '标题', '原创', '地址','完成情况','储存地址'])
#df = df.head(5)
dfsel = (df['标题'] !='随文') & (df['完成情况'] != 1)
save_folder = r"I:\code\python\data\01 爬取微信公众号历史文章\01 二律背反的一灯如豆" + "\\"
# 设置保存格式为 mhtml，减少要操作文件保存下拉框的情况
options = webdriver.ChromeOptions()
options.add_argument('--save-page-as-mhtml')

# 启动浏览器
driver = webdriver.Chrome(options=options)
wait = WebDriverWait(driver,10)

df.loc[dfsel,"完成情况"],df.loc[dfsel,"储存地址"] = zip(*df[dfsel].apply(download_mhtml_with_not_check, axis=1,args=(driver,wait)))

# 在timeout秒内，返回中心值,间隔时长time_setp
# 封装一个pyautogui限时查找函数
#
def finde_gui_element(png,timeout = 5,time_setp=0.2):
 i = 1
 if timeout <=0 : timeout = 5
 if time_setp <=0 : time_setp = 0.2
 while True:
  if i > timeout/time_setp: return None
  center = pyautogui.locateCenterOnScreen(png,grayscale=False,confidence=0.9)
  if center == None:
   time.sleep(0.2)
  else:
   return center
  i = i + 1

def download_mhtml_with_not_check(x,driver,wait):
 name = ''
 try:
  url = str(x['地址'])
  driver.get(url)
  # 获取浏览器标题，用于检测是否是置顶页
  wait.until(EC.presence_of_element_located((By.XPATH,'//h2[@id="activity-name"]')))
  title = driver.find_element_by_xpath('//h2[@id="activity-name"]').text
  print('no:',x.name,'url:',url,'title:',title)
  wait.until(EC.presence_of_element_located((By.XPATH,'//div[@id="page-content"]')))
  
  #进入下载
  pyautogui.hotkey('ctrl', 's')
  # 等待一下对话框弹出
  time.sleep(1)
  bt = finde_gui_element(r'data\png\save.png') #查找保存按键
  if bt == None:
   return (0,'')
  else:
   # 根据标题组合成具体路径
   name = save_folder + ' ' + title + '.mhtml'
   #print(name)
   pyperclip.copy(name)
   pyautogui.hotkey('ctrl', 'v')
   time.sleep(0.1)
   pyautogui.hotkey('Enter')
   # 检查是否弹出另存为
   bt = finde_gui_element(r'data\png\confirmsaveas.png',timeout=0.5)
   if bt != None:
    # 说明出现重复明明，点击覆盖
    pyautogui.hotkey('Tab')
    pyautogui.hotkey('Enter')
    return (1,name)
   bt = finde_gui_element(r'data\png\cancle.png',timeout=0.5)
   if bt != None:
    #还爱，说明出现了一些异常
    pyautogui.hotkey('esc')
    pyautogui.hotkey('esc')
    pyautogui.leftClick(bt)
    return (-1,name)
  # 加多一个esc防止出现窗口还在
  pyautogui.hotkey('esc')
 except Exception as e:
  print(str(e))
  return (-2,name)
 return (1,name)

Option Explicit

Sub add_hype()
 Dim ws As Worksheet, arr As Variant, i As Long
 Set ws = ThisWorkbook.Worksheets(1)
 arr = ws.UsedRange.Value
 ws.Cells.Hyperlinks.Delete
 For i = 2 To UBound(arr)
  If CStr(arr(i, 2)) = "随文" Then
  
  Else
   If CStr(arr(i, 5)) = "1" Then
    ws.Hyperlinks.Add Anchor:=ws.Cells(i, 6), Address:=CStr(arr(i, 6))
   End If
  End If
 Next i
 
End Sub