通过pywinauto和win32gui和pyautogui自动化实现批量导入

清空文件，两行以下的文件

import os
 
# 要操作的文件路径
file_path = 'example.txt'
 
# 删除文件
if os.path.exists(file_path):
    os.remove(file_path)
 
# 重新创建文件
with open(file_path, 'w') as file:
    file.write('')  # 如果需要写入内容，可以在这里写入
1
2
3
4
5
6
7
8
9
10
11
12

拆分文件选择写入

from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl import styles
from openpyxl.styles import *
import pandas as pd
import string
import re
import os
from openpyxl.comments import Comment

def segmentation_col(df, col_name):
    # 按列的值分组
    list_type = df[col_name].unique()
    df_list = []
    for item in list_type:
        df_list.append(df[df[col_name] == item])
    return df_list


def create_excel(file_path):
    # 没有就创建
    if os.path.exists(file_path):
        print("文件已存在")
        print(file_path)
    else:
        # 创建一个新的 Excel 文件
        wb = Workbook()
        wb.save(file_path)


if __name__ == '__main__':
    file_path1 = './src/冬装客户库存清理.xlsx'
    file_path2 = './src/分组数据.xlsx'
    # delete_empty_rows_and_columns(file_path1)
    create_excel(file_path2)
    # 加载工作簿
    wb = load_workbook(file_path1)
    # 获取sheet页,修改第一个sheet页面为
    name1 = wb.sheetnames[0]
    ws1 = wb[name1]
    ws1.title = "销售明细"
    wb.save(file_path1)
    # 销售明细
    df0 = pd.read_excel(file_path1, sheet_name='销售明细', dtype={"颜色代码": str, '尺码代码':str})
    df0 = df0.dropna()
    df_list = segmentation_col(df0, "仓库名称")

    print(len(df_list))
    for i in range(0, len(df_list)):

        item = df_list[i]
        item = item.reset_index(drop=True)
        store_code = item["仓库代码"][0]
        result_df = item.copy()
        result_df.pop("仓库代码")
        result_df.pop("商品名称")
        result_df.pop("仓库名称")
        result_df["店员代码"] = ""
        result_df["VIP代码"] = ""
        result_df["备注"] = ""
        result_df["标准价"] = ""
        result_df["折扣"] = ""
        result_df["单价"] = ""
        print(store_code)
        result_df = result_df[["商品代码", "颜色代码", "尺码代码", "店员代码", "VIP代码", "备注", "数量", "标准价", "折扣", "单价"]]
        result_df.to_excel(file_path2, index=False)
        # 插入一行
        wb = load_workbook(file_path2)
        # 获取sheet页,修改第一个sheet页面为
        name1 = wb.sheetnames[0]
        ws1 = wb[name1]
        ws1.title = "销售明细"
        wb.save(file_path2)
        # 获取指定的文件
        wb = load_workbook(file_path2)
        # 获取指定的sheet
        ws = wb["销售明细"]
        ws.insert_rows(1)
        ws["A1"] = "ltGen"
        # 为单元格A1添加批注
        comment = Comment('TableName:ExcelMx;BRow:2;ERow:2;BCol:1;ECol:11;', 'Author')
        ws['A1'].comment = comment
        ws["B1"] = "商品分色分码明细"
        wb.save(file_path2)
        quit()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

将文件复制到两行之后

总体思路

通过win32获取窗口句柄

通过pywinauto 连接窗口

并找到工具栏控件点击

通过pyautogui对图片点击，选中图

通过pywinauto键盘输入

通过for循环实现多次导入

import win32gui
import win32con
import win32api
import pyautogui
from pynput import keyboard, mouse
from loguru import logger
from threading import Thread
import time
import re
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl import styles
from openpyxl.styles import *
import pandas as pd
import string
import re
import os
import datetime
import time
from pywinauto import Application
from pywinauto import findwindows
from pywinauto import keyboard
import pyautogui
from pywinauto import mouse
pyautogui.PAUSE = 1


def find_all_window():
    hd = win32gui.GetDesktopWindow()
    # 获取所有子窗口
    hwnd_child_list = []
    win32gui.EnumChildWindows(hd, lambda hwnd, param: param.append(hwnd), hwnd_child_list)
    str_list = []
    for hwnd in hwnd_child_list:
        print("句柄：", hwnd, "标题：", win32gui.GetWindowText(hwnd))
        str_list.append("句柄：" + str(hwnd) + "标题：" + win32gui.GetWindowText(hwnd))
        # f.write("句柄：" + str(hwnd) + " 标题：" + win32gui.GetWindowText(hwnd) + '\n'
    # 将句柄生成文件

    list_as_string = '\n'.join(str_list)
    # 打开文件进行写入
    with open('output.txt', 'w', encoding='utf-8') as file:
        file.write(list_as_string)


# 将查询的窗口前置，handle是句柄
def preposition_windows(handle):
    hwnd = win32gui.FindWindow(0, win32gui.GetWindowText(handle))  # 寻找窗口
    if not hwnd:
        print("找不到该窗口")
    else:
        win32gui.SetForegroundWindow(hwnd)  # 前置窗口


def find_control(handle,class_name):
    result = []

    # class_name是窗口类名,name是窗口名,可以只写一个,另一个为None,也可以两个都写
    # handle = wg.FindWindow(class_name, name)
    def callback(handle, res):
        if win32gui.GetClassName() == class_name:
            result.append(str(handle) + ": " + win32gui.GetWindowText(handle))
        return True

    win32gui.EnumChildWindows(handle, callback, None)
    return result
    # result就是控件名


if __name__ == '__main__':
    # 查询所有窗口，当名字查不到句柄时，使用
    # find_all_window()
    # 连接app，通过窗口名称
    handle = win32gui.FindWindow(None, "frm_ImportExcel")
    # 使用uia模式，访问更多的标签
    app = Application(backend="uia").connect(handle=handle)
    # 验证是否找到窗口
    win32gui.ShowWindow(handle, win32con.SW_SHOW)
    win32gui.SetForegroundWindow(handle)  # 前置窗口
    dlg = app["frm_ImportExcel"]
    # 查找工具栏
    tool_bar = dlg.child_window(class_name='TToolBar')
    
    for i in range(0, 1):
        # 选取第一个编辑框
        time.sleep(1.5)
        tool_bar.child_window(title="新增", control_type="Button").click_input()
        time.sleep(0.5)
        channel_code = '000'
        store_code = '10300'
        note = '库存清理'

        file_path = "11212导出格式.xlsx"
        image_path = './src/aim1.png'
        # 定位图片
        location = pyautogui.locateOnScreen(image_path)
        # 如果找到图片，则计算它的中心并移动鼠标
        if location is not None:
            x, y = pyautogui.center(location)
            pyautogui.moveTo(x, y)
            pyautogui.click()
            pyautogui.scroll(600)  # 向上滚动600个单位；
        else:
            print('图片未找到')
        image_path = './src/aim2.png'
        # 定位图片
        location = pyautogui.locateOnScreen(image_path)
        # 如果找到图片，则计算它的中心并移动鼠标
        if location is not None:
            x, y = pyautogui.center(location)
            pyautogui.moveTo(x, y)
            pyautogui.click()
        else:
            print('图片未找到')
        time.sleep(2)
        keyboard.send_keys('{DOWN}')
        time.sleep(0.2)
        keyboard.send_keys(channel_code)
        time.sleep(0.2)
        keyboard.send_keys('{ENTER}')
        time.sleep(0.2)
        keyboard.send_keys('{DOWN}')
        time.sleep(0.2)
        keyboard.send_keys(store_code)
        time.sleep(0.2)
        keyboard.send_keys('{ENTER}')
        time.sleep(0.2)
        keyboard.send_keys('{DOWN}')
        time.sleep(0.2)
        keyboard.send_keys('{DOWN}')
        time.sleep(0.2)
        keyboard.send_keys('{DOWN}')
        time.sleep(0.2)
        keyboard.send_keys('{DOWN}')
        time.sleep(0.2)
        keyboard.send_keys('{DOWN}')
        time.sleep(0.2)
        keyboard.send_keys('{DOWN}')
        time.sleep(0.2)
        keyboard.send_keys('{DOWN}')
        time.sleep(0.2)
        keyboard.send_keys(note)
        keyboard.send_keys('{ENTER}')
        time.sleep(0.2)
        # 点击导出
        tool_bar.child_window(title="导入", control_type="Button").click_input()
        time.sleep(0.5)
        keyboard.send_keys("1")
        time.sleep(0.2)
        keyboard.send_keys("1212导出格式.xlsx")
        time.sleep(0.2)
        keyboard.send_keys('{ENTER}')
        time.sleep(1)
        # 确认导入
        keyboard.send_keys('{ENTER}')
        time.sleep(1)
        # 点击保存
        tool_bar.child_window(title="保存", control_type="Button").click_input()
        time.sleep(0.5)
        # 点击转入
        tool_bar.child_window(title="转入", control_type="Button").click_input()
        time.sleep(0.5)
        #确认转入
        keyboard.send_keys('{ENTER}')
        time.sleep(1)
    #
    #
    #time.sleep(1)
    #tool_bar.child_window(title="导入", control_type="Button").click_input()
    #time.sleep(2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170