python自动化:uiautomation、pyautogui操作会计记账系统(7):自动化填写会计软件系统
#coding=utf-8
#python自动化填写会计软件系统。
import xlrd, xlwt, pyautogui, time, pyperclip,uiautomation, datetime, easygui, random
pyautogui.PAUSE = 0.2
# #代替三方鼠标定位软件
# x1,y1,x2,y2=309,22,381,41 #合同管理
x1,y1,x2,y2=810,342,827,358 #记账下来菜单
#ControlType: TextControl ClassName: AutomationId: Rect: (3,3,58,17)[55x14] Name: '' Handle: 0x0(0) Depth: 4 ValuePattern.Value: '记帐'
# pyautogui.moveTo(x1,y1,duration=2)
# pyautogui.moveTo(x2,y1,duration=2)
# # pyautogui.moveTo(x2,y2,duration=2)
# # pyautogui.moveTo(x1,y2,duration=2)
# pyautogui.moveTo(x1,y1,duration=2)
# wc1 = uiautomation.WindowControl(searchDepth=1, Name='**经营管理系统V2007 - [凭证处理]')
# # 设置为顶层
# #pyautogui.alert(text="按回车键继续程序", title="提示") # 窗口前置才能用键盘快捷键
# wc1.SetTopmost(True) # 置顶能用快捷键
#pyautogui.alert(text="程序运行时不能移动鼠标",title="提示")
# import easygui
#
# print(easygui.fileopenbox())
#
#
# # 获取当前文件路径 调试使用
# local_var_cwd = os.getcwd()
# print('当前文件夹的路径 :\n->>> < %s >' % local_var_cwd )
# [dir_name, file_name] = os.path.split(local_var_cwd)
# print('分离files & path:< %s > < %s >'%(dir_name,file_name))
#file= tkinter.filedialog.askopenfilename()#选择打开什么文件,返回文件名
#print(file)
def mous_eff(x,y):
print('mouse effect鼠标特效')
rnd=random.randint(1,6)
if rnd==1:
# 开始很慢,不断加速
pyautogui.moveTo(x, y, 1, pyautogui.easeInQuad)
if rnd ==2:
# 开始很快,不断减速
pyautogui.moveTo(x, y, 1, pyautogui.easeOutQuad)
if rnd ==3:
# 开始和结束都快,中间比较慢
pyautogui.moveTo(x, y, 1, pyautogui.easeInOutQuad)
if rnd ==4:
# 一步一徘徊前进
pyautogui.moveTo(x, y, 1, pyautogui.easeInBounce)
else:
# 徘徊幅度更大,甚至超过起点和终点
pyautogui.moveTo(x, y, 1, pyautogui.easeInElastic)
# mous_eff(100,100)
# time.sleep(2)
# mous_eff(900,1800)
# time.sleep(2)
# mous_eff(280,1720)
# time.sleep(2)
# mous_eff(380,620)
# time.sleep(2)
#file= tkinter.filedialog.askopenfilename()#选择打开什么文件,返回文件名
file=easygui.fileopenbox()
#print(file)
time.sleep(2)
def auto_fill(screenWidth, screenHeight):
#time.sleep(3)
workbook=file
data = xlrd.open_workbook(workbook) ##获取文本对象支中国电信话费
table = data.sheet_by_name('8**录入2')
nrows = table.nrows # 行数#3获取当前sheet页面的总行数,把每一行数据作为list放到 list
ncols = table.ncols # 列数
account_row=1
time.sleep(3)
pyautogui.alert(text="按回车键开始录入凭证,程序运行时不能移动鼠标", title="提示", button="ok")
#用于等待文件选择窗口消失,别挡着点击增加按钮
time.sleep(1)
#pyautogui.click(100, 80) # 会计凭证首页,点击增加按钮。系统中等字体
#print('会计凭证首页,点击增加按钮。系统小字体点击(80,60)')
#mous_eff(screenWidth/24,screenHeight/18)
pyautogui.click(screenWidth/24, screenHeight/18)
wc1 = uiautomation.WindowControl(searchDepth=1, Name='记帐凭证')
# 设置为顶层
# pyautogui.alert(text="按回车键继续程序", title="提示") # 窗口前置才能用键盘快捷键
wc1.SetTopmost(True) # 置顶能用快捷键
time.sleep(1)
pc2_5 = wc1.PaneControl(searchDepth=1, Name='', foundIndex=4)
#print('深度2pc2_5的坐标是', pc2_5.BoundingRectangle)
#print(type(pc2_5.BoundingRectangle))
pc3_1 = pc2_5.PaneControl(searchDepth=1, Name='')
#print('pc3_1的坐标是', pc3_1.BoundingRectangle)
pc4_1 = pc3_1.PaneControl(searchDepth=1, Name='平衡')
#print('pc4_1的坐标是', pc4_1.BoundingRectangle)
#print(pc4_1)
pc4_7 = pc3_1.PaneControl(searchDepth=1, Name='保存')
#print('pc4_7的坐标是', pc4_1.BoundingRectangle)
#print(pc4_7)
pc2_6 = wc1.PaneControl(searchDepth=1, Name='', foundIndex=5)
#print('深度2pc2_6的坐标是', pc2_6.BoundingRectangle)
ec3_1 = pc2_6.EditControl(searchDepth=1, Name='')
#print('附单', ec3_1)
#print('填写附单空白处需要获取焦点才能填写。')
pc3_2 = pc2_6.PaneControl(searchDepth=1, ClassName='TDateTimePicker')
#print('日期:', pc3_2.Name, pc3_2)
pc3_2.SetFocus()
#print('年份获得焦点,按下向右箭头切换到月份、日期')
pyautogui.press('right')
pyautogui.typewrite('08') # 输入月份
pyautogui.press('right') # 向右箭头移动,输入日
pyautogui.typewrite('31') # 输入日
pyautogui.press('tab')
pyautogui.press('tab')
pyautogui.press('tab')
#pyautogui.doubleClick(580, 355) #点击第一个单元格录入
for i in range(6, nrows-1):
abstract= str(table.cell(i, 11).value) # 摘要
abstract_next = str(table.cell(i+1, 11).value) # 摘要
if abstract!="":
#if abstract == "" and abstract_next == "": 因为有nrows控制行数
#连续两行空值跳出循环
if abstract !='' and abstract !='结束':
account_code = str(table.cell(i, 12).value) # 科目代码
#userName = str(table.cell(i, 5).value) # 科目名称
debit = str(table.cell(i, 14).value) # 借方金额
credit = str(table.cell(i, 15).value) # 贷方金额
print(abstract)
print(account_code)
print(debit)
print(credit)
#time.sleep(1)
print('开始录入凭证的第{}行'.format(account_row))
pyautogui.typewrite('')
# 录入摘要前输入1删除1激活单元格,这样就不需要用鼠标激活单元格,因为Ctrl+v不能直接粘贴,不能激活单元格
pyautogui.press('backspace')#删除1
pyperclip.copy(abstract)#录入摘要
print(str(abstract))
#print(type(abstract))
pyautogui.hotkey('ctrl', 'v') # 再粘贴
pyautogui.press('enter')
pyautogui.typewrite(account_code)#科目代码
print(account_code)
print('科目代码是:{}'.format(account_code))
pyautogui.press('enter')#确定内容
pyautogui.press('enter')#跳格
if debit!= '':
pyautogui.typewrite(str(debit))
print(str(debit))#借方金额
pyautogui.press('enter') # 跳格
else:
pyautogui.press('enter') # 跳格
pyautogui.typewrite(str(credit))
print(str(credit))#贷方金额
pyautogui.press('enter') # 跳格
#account_row+=1
num_receipt = table.cell(i, 16).value
if num_receipt!='':
#num_receipt = table.cell(i - 1, 14).value
#print('凭证数量不能是浮点数(不能带小数点),否则不能保存')
num_receipt=int(num_receipt)
print('附单数量是:', num_receipt)
ec3_1.SetFocus()
pyperclip.copy(num_receipt) # account_Num是账套号
print('附单数量是:', end='')
print(num_receipt)
pyautogui.hotkey('ctrl', 'v') # 再粘贴
print('按F5金额平衡')
pyautogui.press('F5') #会计凭证金额平衡快捷键
time.sleep(2)
#break
#a = pyautogui.confirm(text='完成一个凭证录入', title='结束', buttons=['OK', 'Cancel'])
#print(a) #OK
#pyautogui.click(1400,500) #点击保存,系统中等字号
print('点击保存')
pc4_7.Click()
pyautogui.confirm(text='请按回车键存盘', title='结束', buttons=['OK'])
print('抓取成功保存凭证对话框')
time.sleep(6)
pyautogui.confirm(text='请按回车键开始下一个凭证录入', title='结束', buttons=['OK'])
i+=1 #隔一行再开始录入第二个凭证
account_row += 1
elif abstract=='' and abstract_next!='':
print('#第二个凭证录入')
account_row = 1
#print('会计凭证首页,点击增加按钮。系统小字体点击(80,60)')
#mous_eff(screenWidth/24,screenHeight/18)
pyautogui.click(screenWidth / 24, screenHeight / 18)
time.sleep(1)
#print('yautogui.doubleClick(580, 355) # 系统中号字体,点击第一个单元格录入')
ec3_1.SetFocus()
pyautogui.press('tab')
pyautogui.press('tab')
elif abstract=='结束':
break
elif abstract=="" and abstract_next=='':
break
pyautogui.alert(text="程序结束",title="提示",button="ok") #将打印信息保留到cmd页面中
def func():
#print('当前屏幕分辨率宽 X 高:')
screenWidth, screenHeight = pyautogui.size()
#print(screenWidth, screenHeight) # 屏幕分辨率1920 1080,导出的按钮595,80
auto_fill(screenWidth, screenHeight)
if __name__ == "__main__":
func()
# file_full_path=r"D:\**会计系统导出数据\2020序时簿\2020 219三和村南泮一序时簿 - 副本.xls"
# #del_datas(file_full_path)
#
# excel_modifier(file_full_path)
# 打开程序