eta_python/rpa_smm_refresh.py

import hug
from bottle import route, run
import pyautogui
import random
import time
import os
import pythoncom
import win32gui, win32con, win32process
import pyperclip
import psutil
import urllib.parse
import autoit
import datetime
# import subprocess

hug.API(__name__).http.output_format = hug.output_format.json

exePath = r"C:\Program Files (x86)\Kingsoft\WPS Office\11.8.2.12085\office6\et"
# exePath = exePath = r"C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE"
# exePath = exePath = r"C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE"

@hug.get('/smm_chemical/server')
def smm_server():
    return 1

# 顶部有色按钮的x,y位置
smm_button_x = 642
smm_button_y = 53

# 中间更新数据下拉框按钮的x,y位置
smm_refresh_select_button_x = 212
smm_refresh_select_button_y = 119

# 中间更新数据按钮的x,y位置
smm_refresh_button_x = 232
smm_refresh_button_y = 178

# 超时时间，默认15s
timeout_time = 15


# 随机点击的按钮范围
pointMinX = 200
pointMaxX = 650
pointMinY = 300
pointMaxY = 550

# 异常的监控弹框，需要关闭进程的
err_windows_name_list = ["WPS Office","另存为"]

@hug.get('/smm_chemical/refresh')
def smm_refresh(file_path):
    screen_width, screen_height = pyautogui.size()
    print("当前时间：{}；当前屏幕分辨率：{} x {}".format(datetime.datetime.now(), screen_width, screen_height))

    pyautogui.FAILSAFE = False

    file_path = file_path.replace('"', '')

    # 显示桌面
    pyautogui.hotkey('win', 'd')

    try:
        file_path = urllib.parse.unquote(file_path)
        fileFullPath = file_path

        # print("full_path start")
        fileFullPath = fileFullPath.replace("\\\\", "\\")

        print("文件路径:", fileFullPath)

        if not os.path.exists(fileFullPath):
            print("文件不存在")
            return

        if os.path.exists(fileFullPath) and ".xlsx" in fileFullPath \
                and fileFullPath.find("~$") < 0 \
                and fileFullPath.find("template") < 0:

            # 获取文件名和扩展名
            file_name, file_ext = os.path.splitext(os.path.basename(fileFullPath))
            file_name_ext = file_name + file_ext
            # print(file_name_ext)

            # 停止2s打开，避免打开过快导致excel崩溃
            time.sleep(2)

            try:

                # 启动程序
                code = exePath + " " + fileFullPath
                # print(code)
                autoit.run(code)
                autoit.win_wait_active(file_name_ext, timeout=20)  # 等待窗口激活
                autoit.win_move(file_name_ext, 0, 0, 1024, 768)
                # autoit.win_move(file_name_ext, 0, 0, 1920, 1080)

                # print("监听成功了")

            except Exception as e:
                print("监听", fileFullPath, "失败：", e)

                ## 失败的话，截图记录下原因
                screenshot = pyautogui.screenshot()
                imgPath = datetime.datetime.now().strftime("%Y%m%d%H%M%S")+"_screenshot_img.png"
                screenshot.save(imgPath)

                hwnd = win32gui.GetForegroundWindow()
                found_title = win32gui.GetWindowText(hwnd)
                print("当前窗口名称：", found_title)
                if found_title == "":
                    print("窗口未打开")
                    return

                # 遍历异常名称列表去校验，如果匹配上了，那么就要退出excel并打开
                for err_windows_name in err_windows_name_list:
                    if found_title.startswith(err_windows_name):
                        # 获取与窗口句柄关联的进程ID
                        process_id = win32process.GetWindowThreadProcessId(hwnd)[1]
                        # 根据进程ID获取进程对象并尝试优雅地终止进程
                        target_process = psutil.Process(process_id)
                        target_process.terminate()
                        print("可能是EXCEL崩溃导致，优雅退出EXCEL")
                        return
                    
                # 如果是office的，那么就是要将file_name_ext变更为 file_name;因为office没有后缀
                if found_title.startswith(file_name_ext) == False:
                    print("当前窗口与excel不一致，当前窗口：", found_title, "；excel：", file_name_ext)
                    return
                autoit.win_move_by_handle(hwnd, 0, 0, 1024, 768)
                # autoit.win_move_by_handle(hwnd, 0, 0, 1920, 1080)

            # 等待自动刷新结束
            #time.sleep(15)

            # 保存
            #pyautogui.hotkey('ctrl', 's')
            #time.sleep(1)

            # 关闭当前excel
            #pyautogui.hotkey('ctrl', 'w')

            #return True

            # 屏幕最大化
            #hwnd = win32gui.GetForegroundWindow()
            #win32gui.ShowWindow(hwnd, win32con.SW_MAXIMIZE)
            #time.sleep(1)

            # 随机2-3次点击 单元格区域
            r = random.randint(2, 3)
            for i in range(r):
                pointX = random.randint(pointMinX, pointMaxX)
                pointY = random.randint(pointMinY, pointMaxY)
                print("随机点击", i + 1, "下；", "点击X：", pointX, "；Y：", pointY)
                pyautogui.moveTo(pointX, pointY, 0.5)
                pyautogui.click()
                time.sleep(1)

            # 随机停留几秒
            r = random.randint(1, 2)
            time.sleep(r)

            # 点击 SMM DATA PRO 按钮
            # point_x = 541
            # point_y = 63
            pyautogui.moveTo(smm_button_x, smm_button_y, 0.5)
            pyautogui.click()
            time.sleep(1)

            # 点击 刷新下拉框 按钮
            # point_x = 210
            # point_y = 142
            pyautogui.moveTo(smm_refresh_select_button_x, smm_refresh_select_button_y, 0.5)
            pyautogui.click()
            time.sleep(1)

            # 点击 全部工作表 按钮
            print("开始刷新了")
            # point_x = 240
            # point_y = 233
            pyautogui.moveTo(smm_refresh_button_x, smm_refresh_button_y, 0.5)
            pyautogui.click()
            time.sleep(0.5)

            pointX = random.randint(pointMinX, pointMaxX)
            pointY = random.randint(pointMinY, pointMaxY)
            # 点击完刷新按钮后,移开鼠标
            pyautogui.moveTo(pointX, pointY, 0.5)

            # 等待刷新结束
            time.sleep(timeout_time)

            # 保存
            pyautogui.hotkey('ctrl', 's')
            time.sleep(1)

            # 关闭当前excel
            pyautogui.hotkey('ctrl', 'w')

            return True
        else:
            print("ext err:" + fileFullPath)

        return True
    except Exception as e:
        print("Exception:")
        print(str(e))
        return False


if __name__ == "__main__":
    app = __hug__.http.server()
    run(app=app, reloader=True, port=7007)