以下更新至2023年4月8日，这是日常工作中利用python帮同事们写的一些小工具，帮他们处理大量重复性工作，提高工作效率，解放生产力！
里面涉及的账号密码、邮箱、token、key等敏感信息均已改成随机码，不用尝试哦
auto_mail.py
# 帮助丁方硕发送外汇周报邮件的程序，执行后会将一段文本和指定目录下的某个文件作为附件发送邮件出去 # 核心知识点：发送带附件的邮件，调用win10系统通知 import os import smtplib from email.header import Header from email.mime.application import MIMEApplication from email.mime.text import MIMEText from email.mime.multipart import MIMEMultipart from plyer import notification to_list = ['[email protected]', '[email protected]'] # 收件人列表 cc_list = ['[email protected]', '[email protected]'] # 抄送人列表 to_list_test = ['[email protected]'] # 测试用的收件人列表 cc_list_test = ['[email protected]'] # 测试用的抄送人人列表 # 登录邮箱，需要知道邮箱登录地址及对应的端口号25 smtp = smtplib.SMTP() smtp.connect("smtp.exmail.qq.com", "25") # 邮箱登录地址，及端口号 smtp.login("[email protected]", "Dfs260713") # 邮箱账号，及密码 # 以上代码也可以写成如下带SSL加密的形式： # smtp = smtplib.SMTP_SSL(host="smtp.exmail.qq.com", port=465) # smtp.login("[email protected]", "Dfs260713") # 准备邮件内容 msg = MIMEMultipart() msg['From'] = '[email protected]' # 设置发件人 msg['To'] = ';'.join(to_list) # 设置收件人，使用join函数将列表用;号隔开 msg['Cc'] = ';'.join(cc_list) # 设置抄送人，使用join函数将列表用;号隔开 msg['Subject'] = '资金部上周外汇周报' # 设置邮件标题 msg.attach(MIMEText('郭总、刘总:<br><p>上周外汇周报已出，请过目。</p>', 'html', 'utf-8')) #设置邮件文本 try: filepath = 'C:\\Users\\Lenovo\\Desktop\\美元周报邮件发送\\' filepath_test = 'D:\\newfolder\\' attach_file = MIMEApplication(open(filepath + (os.listdir(filepath))[0], 'rb').read()) #访问filepath目录下的第一个文件，以只读形式打开，此处rb如果省略，当文件名或文件内容存在中文时，会报编码错误 attach_file['Content-type'] = 'application/octet-stream' attach_file.add_header('Content-Disposition', 'attachment', filename=Header((os.listdir(filepath))[0], 'utf-8').encode()) # 设置header信息，设置附件文件名称，其中文件名要用Header(filename,'utf-8').encode()来进行解码，否附件文件名会显示乱码 msg.attach(attach_file) # 添加附件 smtp.sendmail('[email protected]', to_list + cc_list, msg.as_string()) # 发送邮件 # 调用windows自带的系统通知，需要在库引用里面增加from plyer import notification # 调用该系统通知时，如果用pyinstaller打包成exe文件，打包命令末尾应加上一句--hidden-import plyer.platforms.win.notification，打包命令示例如下： # pyinstaller -F -w script.py --hidden-import plyer.platforms.win.notification # 如果打包时不加这句，那么打包后的exe文件运行时就会报“NotImplementedError: No usable implementation found!”错误 notification.notify( title='邮件发送成功', # 设置通知标题 message='外汇周报邮件发送成功', # 设置通知内容 app_icon=None, # 设置通知图标 timeout=15, # 设置通知持续时间 ) except Exception as e: notification.notify( title='邮件发送失败', message='请联系李堤检查程序：\n' + str(e), app_icon=None, timeout=15, ) smtp.quit() # 退出邮箱，这一步很重要不能省略
encrypt_tool.py
这是一个EXCEL文件批量设置密码的工具，会将指定目录下的EXCEL文件全部设置统一的密码。
import os
import tkinter
import tkinter.filedialog
from tkinter import Tk, LabelFrame, Label, Entry, Button, END, messagebox
import win32com.client
import win32com
import pandas as pd
import xlrd
from openpyxl import load_workbook
root = Tk()
root.title("python小工具")
root.wm_geometry('500x280+500+250')
root.resizable(False, False)
l_frame_1 = LabelFrame(root, text="表格批量加密", padx=10, pady=10)
l_frame_1.pack(pady=20)
label_1 = Label(l_frame_1, text="请选择待加密表格文件所在路径：")
text_box = Entry(l_frame_1, bd=2)
label_1.grid(row=0, column=0, padx=5, pady=10)
text_box.grid(row=0, column=1, padx=5, pady=10)
def btn_view_click():
text_box.delete(0, END)
text_box.insert(0, tkinter.filedialog.askdirectory())
btn_view = Button(l_frame_1, text='浏览...', command=btn_view_click)
btn_view.grid(row=0, column=2, padx=5, pady=10)
label_open_pwd = Label(l_frame_1, text="设置文档打开密码：")
label_edit_pwd = Label(l_frame_1, text='设置文档编辑密码：')
entry_open_pwd = Entry(l_frame_1, bd=2)
entry_edit_pwd = Entry(l_frame_1, bd=2)
label_open_pwd.grid(row=1, column=0, padx=10, pady=10)
label_edit_pwd.grid(row=2, column=0, padx=10, pady=10)
entry_open_pwd.grid(row=1, column=1, padx=10, pady=10)
entry_edit_pwd.grid(row=2, column=1, padx=0, pady=10)
def btn_encrypt_click():
file_path = text_box.get()
open_pwd = entry_open_pwd.get()
edit_pwd = entry_edit_pwd.get()
success_list = []
failed_list = []
if file_path != "":
for dir_path, dir_names, file_names in os.walk(file_path):
for filename in file_names:
if (not filename.startswith("~$")) and (filename.endswith(".xls") or filename.endswith(".xlsx")):
full_path = os.path.join(dir_path, filename).replace("/", "\\")
try:
#ts_wb = load_workbook(full_path, data_only=True)
df = pd.read_excel(full_path,sheet_name=0,index_col=0,header=None)
try:
#ts_wb.close()
del df
xcl = win32com.client.Dispatch("Excel.Application")
xcl.DisplayAlerts = False # 不显示警告，保存文件时直接覆盖保存，不弹出提示
xcl.Visible = False # 后台运行
wb = xcl.Workbooks.Open(full_path)
wb.SaveAs(full_path, None, open_pwd, edit_pwd)
xcl.Quit()
if len(success_list) < 100 :
success_list.append(full_path)
except Exception as e:
if len(failed_list) < 100:
failed_list.append(full_path + ";错误原因：" + e)
except Exception as e:
if len(failed_list) < 100:
failed_list.append(full_path + ";错误原因：文档本身已有密码")
s = "\n"
log_file = open('表格加密日志.txt', 'w')
log_file.write("已成功加密的清单如下：\n")
log_file.write(s.join(success_list))
log_file.write("\n")
log_file.write("加密失败的清单如下：\n")
if failed_list:
log_file.write(s.join(failed_list))
else:
log_file.write("不存在加密失败的文件")
messagebox.showinfo('消息', "已处理完成，请查看日志")
else:
messagebox.showerror('错误', "请先选择文件路径")
btn_encrypt = Button(l_frame_1, text='确定加密', command=btn_encrypt_click)
btn_encrypt.grid(row=3, column=1, padx=10, pady=10)
root.mainloop()
form_app4.py
# 这个程序是用于周钊铌每周拆分应收逾期表格文件，并发送邮件给各部门
# 营销中心各二级部门的数据不拆分成单独文件
# v2022.9.16
# 核心知识点包括：
# python 图形界面程序tkinter的使用
# python messagebox的使用
# python 进度条ProgressBar的使用
# openpyxl 对Excel表格的操作
# python 正则表达式的使用
# python发 送带附件的邮件
# openpyxl 知道表格列号，获取列标字母的方法get_column_letter(int)
# openpyxl 获取表格有数据的区域的最大行号的方法
# openpyxl 遍历表格指定区域
# openpyxl 合并单元格
# openpyxl 新建、保存表格
# openpyxl 设置单元格数值格式
# openpyxl 遍历表格所有有数据的区域，并调整字体、字号、边框、列宽
# python 将小数格式调整为保留两位小数的百分数形式
import datetime
import smtplib
import re
import tkinter.filedialog
from email.header import Header
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from tkinter import *
from tkinter import messagebox, ttk
from openpyxl import load_workbook, Workbook
from openpyxl.styles import Alignment, PatternFill, Font, Border, Side
from openpyxl.utils import get_column_letter
all_dept_arr = ["营销中心-华南区", "营销中心-西部区", "营销中心-华东区", "营销中心-华北区", "营销中心-华中区", "营销中心-行业客户二部", "营销中心-行业客户一部", "营销中心-渠道部",
"营销中心-呼叫运营部", "营销中心-华东区行业客户部", "营销中心-打印机事业部", "营销中心-医疗事业部", "华东区大客户部", "战略大客户部", "创新开发事业部", "金融及大客户部",
"金融大客户部",
"海外发展部", "金融事业二部", "海外事业部"]
sales_dept_arr = ["营销中心-华南区", "营销中心-西部区", "营销中心-华东区", "营销中心-华北区", "营销中心-华中区", "营销中心-行业客户二部", "营销中心-行业客户一部", "营销中心-渠道部",
"营销中心-呼叫运营部", "营销中心-华东区行业客户部", "营销中心-打印机事业部", "营销中心-医疗事业部"]
to_list = {
"营销中心-华南区": ["[email protected]"],
"营销中心-西部区": ["[email protected]"],
"营销中心-华东区": ["[email protected]"],
"营销中心-华北区": ["[email protected]"],
"营销中心-华中区": ["[email protected]"],
"营销中心-行业客户二部": ["[email protected]"],
"营销中心-渠道部": ["[email protected]"],
"营销中心-呼叫运营部": ["[email protected]"],
"营销中心-华东区行业客户部": ["[email protected]"],
"营销中心-行业客户一部": ["[email protected]"],
"营销中心-打印机事业部": ["[email protected]"],
"营销中心-医疗事业部": ["[email protected]"],
"华东区大客户部": ["[email protected]"],
"战略大客户部": ["[email protected]"],
"创新开发事业部": ["[email protected]"],
"金融及大客户部": ["[email protected]"],
"金融大客户部": ["[email protected]"],
"海外发展部": ["[email protected]"],
"金融事业二部": ["[email protected]"],
"海外事业部": ["[email protected]"],
"营销中心": ["[email protected]"]
}
to_list_test = {
"营销中心-华南区": ["[email protected]"],
"营销中心-西部区": ["[email protected]"],
"营销中心-华东区": ["[email protected]"],
"营销中心-华北区": ["[email protected]"],
"营销中心-华中区": ["[email protected]"],
"营销中心-行业客户二部": ["[email protected]"],
"营销中心-渠道部": ["[email protected]"],
"营销中心-呼叫运营部": ["[email protected]"],
"营销中心-华东区行业客户部": ["[email protected]"],
"营销中心-行业客户一部": ["[email protected]"],
"营销中心-打印机事业部": ["[email protected]"],
"营销中心-医疗事业部": ["[email protected]"],
"华东区大客户部": ["[email protected]"],
"战略大客户部": ["[email protected]"],
"创新开发事业部": ["[email protected]"],
"金融及大客户部": ["[email protected]"],
"金融大客户部": ["[email protected]"],
"海外发展部": ["[email protected]"],
"金融事业二部": ["[email protected]"],
"海外事业部": ["[email protected]"],
"营销中心": ["[email protected]"]
}
name_list = {
"营销中心-华南区": "总",
"营销中心-西部区": "总",
"营销中心-华东区": "总",
"营销中心-华北区": "总",
"营销中心-华中区": "总",
"营销中心-行业客户二部": "总",
"营销中心-渠道部": "总",
"营销中心-呼叫运营部": "总",
"营销中心-华东区行业客户部": "总",
"营销中心-行业客户一部": "总",
"营销中心-打印机事业部": "总",
"营销中心-医疗事业部": "总",
"华东区大客户部": "总",
"战略大客户部": "总",
"创新开发事业部": "总",
"金融及大客户部": "总",
"金融大客户部": "总",
"海外发展部": "总",
"金融事业二部": "总",
"海外事业部": "总",
"营销中心": "总"
}
cc_list = {
"营销中心-华南区": ["[email protected]", "[email protected]", "[email protected]", "[email protected]",
"[email protected]", "[email protected]"],
"营销中心-西部区": ["[email protected]", "[email protected]", "[email protected]", "[email protected]",
"[email protected]", "[email protected]"],
}
cc_list_test = {
"营销中心-华南区": ["[email protected]"],
"营销中心-西部区": ["[email protected]"],
"营销中心-华东区": ["[email protected]"],
"营销中心-华北区": ["[email protected]"],
"营销中心-华中区": ["[email protected]"],
"营销中心-行业客户二部": ["[email protected]"],
"营销中心-渠道部": ["[email protected]"],
"营销中心-呼叫运营部": ["[email protected]"],
"营销中心-华东区行业客户部": ["[email protected]"],
"营销中心-行业客户一部": ["[email protected]"],
"营销中心-打印机事业部": ["[email protected]"],
"营销中心-医疗事业部": ["[email protected]"],
"华东区大客户部": ["[email protected]"],
"战略大客户部": ["[email protected]"],
"创新开发事业部": ["[email protected]"],
"金融及大客户部": ["[email protected]"],
"金融大客户部": ["[email protected]"],
"海外发展部": ["[email protected]"],
"金融事业二部": ["[email protected]"],
"海外事业部": ["[email protected]"],
"营销中心": ["[email protected]"]
}
dept_arr = []
current_sales_dept = []
dept_arr_mail = []
addr_dict = dict()
ar_balance = dict()
overdue_amount = dict()
overdue_percent = dict()
overdue_date = ""
# 程序主窗口
root = Tk()
root.title("Python小助手@lidi v1.0") # 主窗口的标题
root.wm_geometry('500x450+500+250') # 主窗口的大小及位置，大小为500*450，距离屏幕左边500px，距离屏幕顶部250px
root.resizable(False, False) # 这只主窗口不可以拉动改变大小
l_frame_1 = LabelFrame(root, text="拆分文件", padx=10,
pady=10) # 设置一个frame控件，父容器为root，padx设置距离父容器左边的距离，单位为像素，pady设置距离父容器顶部的距离
l_frame_1.pack(pady=20) # 使用pack才算将该控件绑定到窗口上，距离顶部20像素
label_1 = Label(l_frame_1, text="请选择待拆分文件：") # 设置一个文本标签控件，父容器为l_frame_1，就是前面的frame控件
text_box = Entry(l_frame_1, bd=2) # 设置一个文本框控件，父容器为l_frame_1，就是前面的frame控件
label_1.grid(row=0, column=0, padx=5, pady=10) # 使用表格布局，设置文本控件在frame控件的第0行第0列，padx和pady分别设置左、上边距
text_box.grid(row=0, column=1, padx=5, pady=10) # 原理同上
# 定义“浏览”按钮的功能为点击后打开一个文件选择框filedialog
def btn_view_click():
text_box.delete(0, END)
text_box.insert(0, tkinter.filedialog.askopenfilename())
btn_view = Button(l_frame_1, text='浏览...', command=btn_view_click) # 设置一个“浏览”按钮，父容器为l_frame_1，绑定函数btn_view_click()
btn_view.grid(row=0, column=2, padx=5, pady=10) # 设置“浏览”按钮的位置
pgrs_bar = ttk.Progressbar(root) # 设置一个进度条
l_frame_2 = LabelFrame(root, text="发送邮件", padx=30, pady=10)
label_name = Label(l_frame_2, text="发件人邮箱")
label_pwd = Label(l_frame_2, text='发件人密码')
label_addr_list = Label(l_frame_2, text='收件人地址簿')
entry_name = Entry(l_frame_2, bd=2, state='readonly') # state设置文本框为只读，此处因为后续将邮箱账号写死在程序里了，不用手输了，所以这里设为只读了
entry_pwd = Entry(l_frame_2, bd=2, show='*', state='readonly') # show="*"，用户输入字符后将显示为*号，用于密码输入
entry_addr_list = Entry(l_frame_2, bd=2, state='readonly')
def btn_view2_click():
entry_addr_list.delete(0, END)
entry_addr_list.insert(0, tkinter.filedialog.askopenfilename())
def getLastMonday():
monday = datetime.date.today()
# monday = datetime.date(2022, 6, 27)
one_day = datetime.timedelta(days=1)
while monday.weekday() != 0:
monday -= one_day
return datetime.datetime.strftime(monday, "%Y-%m-%d")
def btn_send_click():
sender_account = "[email protected]"
sender_pwd = "password"
sender_name = "营销财管部<[email protected]>" # sender_name用于指示发件人名称，该名称一定要包含发件人邮箱地址，否则程序会报错
# sender_account = "[email protected]"
# sender_pwd = "pppppp"
# sender_name = "[email protected]"
smtp = smtplib.SMTP_SSL(host="smtp.exmail.qq.com", port=465)
smtp.login(sender_account, sender_pwd)
pgrs_bar['value'] = 5 # 更新进度条进度
root.update() # 更新进度条进度后，需要刷新主窗体，才能正常显示进度条的变化
global overdue_date
try:
pgrs_step = 0
for dept in dept_arr_mail:
pgrs_step = pgrs_step + 1
msg = MIMEMultipart()
msg['From'] = sender_name
msg['To'] = ";".join(to_list[dept]) # 此处正式上线时改为正式的收件人
msg['Cc'] = ";".join(cc_list[dept]) # 此处正式上线时改为正式的抄送人
msg['Subject'] = '逾期货款统计月报' + overdue_date
# message_xlsx = pandas.read_excel(dept + ".xlsx").to_html()
message_tips = "<hr /><p>温馨提示：</p><p>本邮件由系统自动发出，有可能遇到附件名称出现乱码的情况，此时双击附件，会提示选择用什么程序打开，此时选择wps或者Office " \
"Excel即可正常打开。</p>" \
"<p>建议将Foxmail升级到最新版客户端，或者使用网页邮箱查看邮件，附件名称都会正常显示</p>" \
"<p>最新版Foxmail下载地址：<a href='https://www.foxmail.com/'>https://www.foxmail.com/</a></p>" \
"<p>网页版邮箱登录地址：<a href='https://exmail.qq.com/'>https://exmail.qq.com/</a></p>"
message_sign = '<hr />Best regards<br/>' \
'[email protected]<br/>' \
' <br/>' \
'深圳市优博讯科技股份有限公司（股票代码：300531）<br/>' \
'UROVO TECHNOLOGY CO., LTD. (Stock Code: 300531.SZ)<br/>' \
'深圳市南山区学府路63号高新区联合总部大厦36-37楼<br/>' \
'Floor 36-37, Hi-tech Zone Union Tower,No.63 Xuefu Road, Nanshan District, Shenzhen,' \
'Guangdong,<br/>' \
'ChinaTel：+86-755-86186300,<br/>' \
'Web：http://www.urovo.com<br/>' \
'------------------------------------<br/>' \
'本邮件包含信息归优博讯所有,优博讯对该邮件拥有所有权利。请收件人注意保密,未经发件人书面许可,不得向任何第三方组织和个人透露本邮件所含全部或部分信息。<br/>' \
'CONFIDENTIALITY NOTICE. This message is intended exclusively for the named addressee and ' \
'may contain confidential information. Unless you are the named addressee (or authorised ' \
'to receive for the addressee) you may not copy, use or disclose this message. If this ' \
'e-mail was sent to you by mistake please notify the sender immediately and delete this ' \
'e-mail. '
message = name_list[dept] + ':<br/><p>您好！截至' + overdue_date + '， ' + dept + '的应收账款为' + str(
format(ar_balance[
dept], ",")) + '元，逾期金额为' + str(format(overdue_amount[dept], ",")) + '元，逾期率为' + str(
overdue_percent[
dept]) + '</p><p>附件为应收款和逾期款明细，请查收，谢谢！</p><p>后续若有疑问，请及时与财务中心相关同事联系。</p><p>海外--李媛媛</p><p' \
'>国内--王博、段春雪、李堤。</p>' + message_sign
msg.attach(MIMEText(message, 'html', 'utf-8'))
# print(message)
xlsx_file = MIMEApplication(open(dept + '.xlsx', 'rb').read())
xlsx_file['Content-type'] = 'application/octet-stream'
xlsx_file.add_header('Content-Disposition', 'attachment', filename=Header(dept + '.xlsx',
'utf-8').encode()) # 添加到header信息，此处filename必须用Header编码，不然会出现乱码
msg.attach(xlsx_file)
# 正式上线时修改为正式的收件人和抄送人
# 此处sender_name参数的值必须包含发件人地址，否则会报错
smtp.sendmail(sender_name, to_list[dept] + cc_list[dept], msg.as_string())
pgrs_bar['value'] = 5 + pgrs_step / len(dept_arr) * 95
root.update()
pgrs_bar['value'] = 5 + pgrs_step / len(dept_arr_mail) * 95
root.update()
smtp.quit()
messagebox.showinfo('发送成功', str(len(dept_arr_mail)) + '封邮件发送成功，请登录邮箱查看已发送邮件')
except Exception as e:
# smtp.quit()
messagebox.showerror('错误', e)
btn_view2 = Button(l_frame_2, text='从文件导入...')
btn_send = Button(l_frame_2, text='发送邮件', command=btn_send_click)
def show_mail_area():
l_frame_2.pack(pady=20)
label_name.grid(row=2, column=0, padx=10, pady=10)
label_pwd.grid(row=3, column=0, padx=10, pady=10)
label_addr_list.grid(row=4, column=0, padx=10, pady=10)
entry_name.grid(row=2, column=1, padx=10, pady=10)
entry_pwd.grid(row=3, column=1, padx=10, pady=10)
entry_addr_list.grid(row=4, column=1, padx=10, pady=10)
btn_view2.grid(row=4, column=2, padx=10, pady=10)
btn_send.grid(row=5, column=1, padx=10, pady=10)
def btn_split_click():
file_name = text_box.get()
if file_name != "":
# try:
# 显示进度条
pgrs_bar.pack(padx=100, pady=10)
pgrs_bar['length'] = 300
pgrs_bar['maximum'] = 100
pgrs_bar['value'] = 3
root.update()
wb = load_workbook(file_name, data_only=True)
# print(wb.sheetnames)
sheet1 = wb["应收汇总"]
global overdue_date # 声明接下来使用的是外部的变量overdue_date，就是代码开头在函数外面声明的那个overdue_date
overdue_date = re.search("[0-9]+\.[0-9]+\.[0-9]+", sheet1.cell(1, 1).value).group() # 正则表达式的使用
pgrs_bar['value'] = 5
root.update()
# print(sheet1.title)
max_row_B = max(bb.row for bb in sheet1['B'] if bb.value) # 获取B列有数据的最大行号
pgrs_bar['value'] = 8
root.update()
# print(max_row_B)
# 将B列的值全部作为key保存到dept_dict字典中，利用字典的key值唯一的特性，获取B列所有不重复的值
dept_dict = dict()
for row in sheet1["B4:B" + str(max_row_B)]:
for cell in row:
if cell.value in all_dept_arr:
dept_dict[cell.value] = ""
for key in dept_dict.keys():
dept_arr.append(key)
if key in sales_dept_arr:
current_sales_dept.append(key)
else:
dept_arr_mail.append(key)
del dept_dict
dept_arr_mail.append("营销中心")
# print(dept_arr)
# print(current_sales_dept)
# print(dept_arr_mail)
pgrs_bar['value'] = 10
root.update()
pgrs_step = 0
for dept in dept_arr_mail:
pgrs_step = pgrs_step + 1
new_wb = Workbook()
new_ws = new_wb.active
new_ws.title = dept
header1 = ["合并客户", "部门", "业务员", "应收金额（RMB）", "逾期（含发货超过90天", "逾期1：发货超过90天", "逾期2：超账期", "逾期期间", "", "",
"", "",
"6月回款（RMB）", "周回款情况", "", "", ""]
header2 = ["", "", "", "", "", "", "", "1-30天", "31-90天", "91-180天", "181-365天", "1年以上",
"6月回款（RMB）", "W1", "W2", "W3", "W4"]
new_ws.append(header1)
new_ws.append(header2)
new_ws.merge_cells("A1:A2")
new_ws.merge_cells("B1:B2")
new_ws.merge_cells("C1:C2")
new_ws.merge_cells("D1:D2")
new_ws.merge_cells("E1:E2")
new_ws.merge_cells("F1:F2")
new_ws.merge_cells("G1:G2")
new_ws.merge_cells("H1:L1")
new_ws.merge_cells("M1:M2")
new_ws.merge_cells("N1:Q1")
for row3 in new_ws["A1:Q2"]:
for cell4 in row3:
cell4.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
cell4.fill = PatternFill(start_color='C0C0C0', fill_type='solid')
if dept != "营销中心":
for row in sheet1["B4:B" + str(max_row_B)]:
for cell in row:
if cell.value == dept:
data_arr = []
for cell2 in sheet1[cell.row]:
data_arr.append(cell2.value)
new_ws.append(data_arr)
else:
for row in sheet1["B4:B" + str(max_row_B)]:
for cell in row:
if cell.value in current_sales_dept:
data_arr = []
for cell2 in sheet1[cell.row]:
data_arr.append(cell2.value)
new_ws.append(data_arr)
max_row_B2 = max(bb.row for bb in new_ws['B'] if bb.value)
# 设置合计行，get_column_letter(int)函数能获取列标字母
for row4 in new_ws["D" + str(max_row_B2 + 1) + ":Q" + str(max_row_B2 + 1)]:
for cell5 in row4:
cell5.value = '=sum(' + get_column_letter(cell5.column) + '3:' + get_column_letter(
cell5.column) + str(max_row_B2) + ')'
new_ws.cell(max_row_B2 + 1, 1).value = '合计'
# 计算部门的应收余额和逾期金额
ar_balance[dept] = 0
overdue_amount[dept] = 0
# 累加D列的应收金额
for row5 in new_ws["D3:D" + str(max_row_B2)]:
for cell6 in row5:
# 有些单元格为空，Value不能直接做加法，空值做加法会报错，所以做个判断
if cell6.value:
ar_balance[dept] += cell6.value
# 累加E列的逾期金额
for row6 in new_ws["E3:E" + str(max_row_B2)]:
for cell7 in row6:
# 有些单元格为空，Value不能直接做加法，空值做加法会报错，所以做个判断
if cell7.value:
overdue_amount[dept] += cell7.value
ar_balance[dept] = round(ar_balance[dept], 2)
overdue_amount[dept] = round(overdue_amount[dept], 2)
print(dept)
print(ar_balance[dept])
# 计算逾期率，并将结果转化为保留两位小数的百分数
overdue_percent[dept] = "%.2f%%" % round(overdue_amount[dept] / ar_balance[dept] * 100, 2)
for row2 in new_ws[new_ws.dimensions]:
for cell3 in row2:
cell3.font = Font(size=9)
cell3.number_format = "#,##0.00"
cell3.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'),
bottom=Side(style='thin'))
new_ws.column_dimensions[get_column_letter(cell3.column)].width = 15
new_ws.delete_cols(18)
new_ws.cell(max_row_B2 + 2, 5).value = '=E' + str(max_row_B2 + 1) + '/D' + str(max_row_B2 + 1)
new_ws.cell(max_row_B2 + 2, 5).number_format = "0.00%"
new_ws.cell(max_row_B2 + 2, 5).font = Font(size=9)
new_wb.save(dept + ".xlsx")
pgrs_bar['value'] = 10 + pgrs_step / len(dept_arr_mail) * 90
root.update()
messagebox.showinfo('拆分成功', '文件已拆分成功，请查看程序所在文件夹')
# print(ar_balance)
# print(overdue_amount)
# print(overdue_percent)
# 拆分完成后才显示发送邮件的功能区域
show_mail_area()
# pgrs_bar.destroy()
# except Exception as e:
# messagebox.showerror('错误', e)
# pgrs_bar.destroy()
else:
messagebox.showerror('错误', '请先选择需要拆分的文件')
btn_split = Button(l_frame_1, text='开始拆分', command=btn_split_click)
btn_split.grid(row=0, column=3, padx=5, pady=10)
root.mainloop()
ganfan.py
从今日诗词API拉取一句随机诗词，并通过企业微信群机器人发送到企业微信群
import requests
import datetime
import json
weekend_workdays = ["2023-02-25", "2023-03-25", "2023-04-22", "2023-04-23", "2023-05-06", "2023-05-27",
"2023-06-25", "2023-07-29", "2023-08-26", "2023-10-07", "2023-10-08",
"2023-10-28", "2023-11-25", "2023-12-30"]
weekend = [5, 6]
holidays = ["2023-04-05", "2023-04-29", "2023-04-30", "2023-05-01", "2023-05-02", "2023-05-03",
"2023-06-22", "2023-06-23", "2023-06-24", "2023-09-29", "2023-09-30", "2023-10-01",
"2023-10-02", "2023-10-03", "2023-10-04", "2023-10-05", "2023-10-06"]
url = "https://v2.jinrishici.com/sentence"
headers1 = {"Content-Type": "text/html;charset=utf-8", "X-User-Token":token}
r = requests.get(url, headers=headers1)
poem = json.loads(r.text)["data"]["content"]
headers = {
'Content-Type': 'application/json'
}
json_data = {
"msgtype": "text",
"text": {
"content": poem + "兄弟姐妹们点外卖啦！忘点的中午要饿肚子啦！",
"mentioned_list": ["@all"]
}
}
def get_today():
return datetime.datetime.strftime(datetime.datetime.today(), "%Y-%m-%d")
if get_today() in weekend_workdays:
response = requests.post(
'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=' + key,
headers=headers, json=json_data)
elif datetime.datetime.today().weekday() in weekend:
print("今天不提醒")
elif get_today() in holidays:
print("今天不提醒")
else:
response = requests.post(
'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=' +
key,
headers=headers, json=json_data)
getlibor.py
从优财网拉取Libor利率，并通过企业微信群机器人发送到企业微信群
import requests
from bs4 import BeautifulSoup
url = "http://www.ucai123.com/datas-libor"
headers1 = {"Content-Type": "text/html;charset=utf-8"}
r = requests.post(url, headers=headers1)
soup = BeautifulSoup(r.text, "lxml")
data_content = "每日Libor播报\n" + "货币：USD " + "\n" \
"日期：" + soup.select("#times")[0].string + "\n" \
+ "隔夜期：" + soup.select("#datas2")[0].string + "\n" \
+ "1周期：" + soup.select("#datas2")[1].string + "\n" \
+ "2周期：" + soup.select("#datas2")[2].string + "\n" \
+ "1个月：" + soup.select("#datas2")[3].string + "\n" \
+ "2个月：" + soup.select("#datas2")[4].string + "\n" \
+ "3个月：" + soup.select("#datas2")[5].string + "\n" \
+ "4个月：" + soup.select("#datas2")[6].string + "\n" \
+ "5个月：" + soup.select("#datas2")[7].string + "\n" \
+ "6个月：" + soup.select("#datas2")[8].string + "\n" \
+ "7个月：" + soup.select("#datas2")[9].string + "\n" \
+ "8个月：" + soup.select("#datas2")[10].string + "\n" \
+ "9个月：" + soup.select("#datas2")[11].string + "\n" \
+ "10个月：" + soup.select("#datas2")[12].string + "\n" \
+ "11个月：" + soup.select("#datas2")[13].string + "\n" \
+ "12个月：" + soup.select("#datas2")[14].string
headers2 = {
'Content-Type': 'application/json'
}
json_data = {
"msgtype": "markdown",
"markdown": {
"content": data_content,
}
}
response = requests.post('https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=' + key,
headers=headers2, json=json_data)
grab_data5.py
从国家外汇管理局抓取汇率并保存到EXCEL文件，并制作折线图
import pandas
import requests
import datetime
import calendar
from openpyxl import load_workbook
from openpyxl.chart import LineChart, Reference
from openpyxl.chart.label import DataLabelList
from openpyxl.chart.shapes import GraphicalProperties
from openpyxl.chart.text import RichText
from openpyxl.chart.trendline import Trendline
from openpyxl.drawing.line import LineProperties
from openpyxl.drawing.text import Paragraph, ParagraphProperties, CharacterProperties
from openpyxl.styles import Font, Border, Side, Alignment
# 获取上周一
def getLastMonday():
monday = datetime.date.today() # 获取今天的日期
one_day = datetime.timedelta(days=1) # 获取日期单位增量：一天
monday -= (7 * one_day) # 将今天的日期减去7天，得到上周的今天
# 以下代码，将上周的今天每次减1天，直到monday.weekday()为0为止。monday.weekday()是获取星期几，周一到周日依次是0到6
while monday.weekday() != 0:
monday -= one_day
# 按格式输出字符串，可以理解为将日期格式转化为字符串格式
return datetime.datetime.strftime(monday, "%Y-%m-%d")
# 获取上周五
def getLastFriday():
friday = datetime.date.today()
one_day = datetime.timedelta(days=1)
friday -= (7 * one_day)
if friday.weekday() > 4:
while friday.weekday() != 4:
friday -= one_day
else:
while friday.weekday() != 4:
friday += one_day
return datetime.datetime.strftime(friday, "%Y-%m-%d")
# 获取并格式化输出今年第一天
def get_first_day_of_this_year():
this_year_start = datetime.datetime(datetime.datetime.today().year, 1, 1)
return datetime.datetime.strftime(this_year_start, "%Y-%m-%d")
# 获取并格式化输出今天
def get_today():
return datetime.datetime.strftime(datetime.datetime.today(), "%Y-%m-%d")
# 获取上月第一天,并格式化输出
def get_last_month_start():
year = datetime.datetime.today().year
month = datetime.datetime.today().month
if month == 1:
month = 12
year -= 1
else:
month -= 1
last_month_start = datetime.datetime(year, month, 1)
return datetime.datetime.strftime(last_month_start, "%Y-%m-%d")
# 获取上月最后一天，并格式化输出
def get_last_month_end():
year = datetime.datetime.today().year
month = datetime.datetime.today().month
if month == 1:
month = 12
year -= 1
else:
month -= 1
end_day = calendar.monthrange(int(year), int(month))[1] # 获取年月的最后一天
last_month_end = datetime.datetime(year, month, end_day)
return datetime.datetime.strftime(last_month_end, "%Y-%m-%d")
# post方法访问网页链接，以下是国家外汇局网站获取汇率的链接
url = "http://www.safe.gov.cn/AppStructured/hlw/RMBQuery.do"
headers = {"Content-Type": "text/html;charset=utf-8"}
r = requests.post(url, params={'startDate': getLastMonday(), 'endDate': getLastFriday()}, headers=headers)
# pandas.read_html方法从网页返回结果中拉取表格数据，保存在DataFrame对象中
df = pandas.read_html(r.text)[4].sort_index(ascending=False)
df1 = pandas.to_datetime(df["日期"], format="%Y-%m-%d") # 由于df["日期"]是文本格式的，要先转化成日期格式，再写入excel文件时才能正常显示为日期格式
df2 = df["美元"].div(100).round(4) # 除以100，并保留4位小数
df_eur = df["欧元"].div(100).round(4)
df3 = pandas.concat([df1, df2], axis=1).T # 拼接多个DataFrame对象，并.T将结果转置
df3_1 = pandas.concat([df1, df_eur], axis=1).T
df3.to_excel("数据、图表.xlsx", header=False) # 将DataFrame对象写入excel文件
# 向excel文件中追加写入数据
filepath = "数据、图表.xlsx"
wb = load_workbook(filepath, data_only=True)
writer = pandas.ExcelWriter(filepath)
writer.book = wb
writer.sheets = {sheets.title: sheets for sheets in writer.book.worksheets}
df3_1.to_excel(writer, sheet_name="Sheet1", startrow=0, startcol=11, header=False) # 指定写入的sheet名称，以及从第几行、第几列开始写
writer.save()
writer.close()
ws = wb.active
ws.cell(1, 7).value = "本周平均"
ws.cell(2, 7).value = df2.mean().round(4) # mean()方法用于DataFrame求平均数
ws.cell(1, 18).value = "本周平均"
ws.cell(2, 18).value = df_eur.mean().round(4)
for row in ws['A1:G2']:
for cell in row:
if cell.row == 1:
cell.number_format = 'yyyy/m/d;@' # 将单元格调整为日期格式，前提是写入的dataframe也是日期格式，最终excel里面才会正常显示为日期格式
cell.font = Font(size=9) # 调整单元格字体
cell.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'),
bottom=Side(style='thin')) # 调整单元格边框
cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) # 调整单元格居中，自动换行
# 以下代码与上一段趋同，就不详细注释了
for row in ws['L1:R2']:
for cell in row:
if cell.row == 1:
cell.number_format = 'yyyy/m/d;@'
cell.font = Font(size=9)
cell.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'),
bottom=Side(style='thin'))
cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
# 以下代码添加折线图
lc = LineChart()
# lc.title = ""
lc.style = 13 # 设置表格样式
data = Reference(ws, min_col=1, min_row=2, max_col=6, max_row=2) # 设置数据区域
line_data = Reference(ws, min_col=2, min_row=1, max_col=6, max_row=1) # 设置标题区域
lc.add_data(data, from_rows=True, titles_from_data=True)
lc.set_categories(line_data)
line1 = lc.series[0]
# line1.graphicalProperties.line.solidFill = 'FF0000' #设置线条颜色
line1.graphicalProperties.line.width = 30000 # 设置线条粗细
line1.dLbls = DataLabelList()
line1.dLbls.showVal = True # 显示数据标签
axis = CharacterProperties(b=True, solidFill="FF0000") # 与下一句一起设置数据标签字体加粗、红色
line1.dLbls.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=axis), endParaRPr=axis)])
line1.trendline = Trendline(
spPr=GraphicalProperties(ln=LineProperties(w=20000, prstDash='sysDot', solidFill='5082BE'))) # 显示蓝色点状趋势线
# line1.trendline = Trendline(spPr=GraphicalProperties(ln=LineProperties(solidFill='FF0000'))) # 显示红色趋势线
lc.legend = None # 隐藏图例
lc1 = LineChart()
# lc1.title = ""
lc1.style = 13
data1 = Reference(ws, min_col=12, min_row=2, max_col=17, max_row=2)
line_data1 = Reference(ws, min_col=13, min_row=1, max_col=17, max_row=1)
lc1.add_data(data1, from_rows=True, titles_from_data=True)
lc1.set_categories(line_data1)
line1_1 = lc1.series[0]
# line1.graphicalProperties.line.solidFill = 'FF0000'
line1_1.graphicalProperties.line.width = 30000
line1_1.dLbls = DataLabelList()
line1_1.dLbls.showVal = True # 显示数据标签
axis = CharacterProperties(b=True, solidFill="FF0000") # 与下一句一起设置数据标签字体加粗、红色
line1_1.dLbls.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=axis), endParaRPr=axis)])
# print(line1.dLbls.graphicalProperties)
line1_1.trendline = Trendline(
spPr=GraphicalProperties(ln=LineProperties(w=20000, prstDash='sysDot', solidFill='5082BE'))) # 显示蓝色点状趋势线
# line1_1.trendline = Trendline(spPr=GraphicalProperties(ln=LineProperties(solidFill='FF0000'))) # 显示红色趋势线
lc1.legend = None # 隐藏图例
ws.add_chart(lc, "A6")
ws.add_chart(lc1, "L6")
wb.create_sheet("Sheet2", 1) # 创建一个新的工作表
wb.save("数据、图表.xlsx")
# post方法访问网页链接，以下是国家外汇局网站获取汇率的链接
r3 = requests.post(url, params={'startDate': get_last_month_start(), 'endDate': get_last_month_end()}, headers=headers)
# r3 = requests.post(url, params={'startDate': '2022-12-01', 'endDate': '2022-12-31'}, headers=headers)
df6 = pandas.read_html(r3.text)[4].sort_index(ascending=False)
df7 = pandas.to_datetime(df6["日期"], format="%Y-%m-%d") # 由于df["日期"]是文本格式的，要先转化成日期格式，再写入excel文件时才能正常显示为日期格式
df8 = df6["美元"].div(100).round(4) # 除以100，并保留4位小数
df9 = pandas.concat([df7, df8], axis=1).T # 拼接多个DataFrame对象，并.T将结果转置
# 向excel文件中追加写入数据
filepath = "数据、图表.xlsx"
wb = load_workbook(filepath, data_only=True)
writer = pandas.ExcelWriter(filepath)
writer.book = wb
writer.sheets = {sheets.title: sheets for sheets in writer.book.worksheets}
df9.to_excel(writer, sheet_name="Sheet1", startrow=22, startcol=0, header=False) # 指定写入的sheet名称，以及从第几行、第几列开始写
writer.save()
writer.close()
ws = wb.active
if not (ws['AF23'].value is None):
max_col = "AF"
max_col_num = 32
elif not (ws['AE23'].value is None):
max_col = "AE"
max_col_num = 31
elif not (ws['AD23'].value is None):
max_col = "AD"
max_col_num = 30
elif not (ws['AC23'].value is None):
max_col = "AC"
max_col_num = 29
elif not (ws['AB23'].value is None):
max_col = "AB"
max_col_num = 28
elif not (ws['AA23'].value is None):
max_col = "AA"
max_col_num = 27
elif not (ws['Z23'].value is None):
max_col = "Z"
max_col_num = 26
elif not (ws['Y23'].value is None):
max_col = "Y"
max_col_num = 25
elif not (ws['X23'].value is None):
max_col = "X"
max_col_num = 24
elif not (ws['W23'].value is None):
max_col = "W"
max_col_num = 23
elif not (ws['V23'].value is None):
max_col = "V"
max_col_num = 22
elif not (ws['U23'].value is None):
max_col = "U"
max_col_num = 21
elif not (ws['T23'].value is None):
max_col = "T"
max_col_num = 20
elif not (ws['S23'].value is None):
max_col = "S"
max_col_num = 19
elif not (ws['R23'].value is None):
max_col = "R"
max_col_num = 18
elif not (ws['Q23'].value is None):
max_col = "Q"
max_col_num = 17
elif not (ws['P23'].value is None):
max_col = "P"
max_col_num = 16
elif not (ws['O23'].value is None):
max_col = "O"
max_col_num = 15
elif not (ws['N23'].value is None):
max_col = "N"
max_col_num = 14
elif not (ws['M23'].value is None):
max_col = "M"
max_col_num = 13
elif not (ws['L23'].value is None):
max_col = "L"
max_col_num = 12
elif not (ws['K23'].value is None):
max_col = "K"
max_col_num = 11
elif not (ws['J23'].value is None):
max_col = "J"
max_col_num = 10
elif not (ws['I23'].value is None):
max_col = "I"
max_col_num = 9
elif not (ws['H23'].value is None):
max_col = "H"
max_col_num = 8
elif not (ws['G23'].value is None):
max_col = "G"
max_col_num = 7
elif not (ws['F23'].value is None):
max_col = "F"
max_col_num = 6
elif not (ws['E23'].value is None):
max_col = "E"
max_col_num = 5
elif not (ws['D23'].value is None):
max_col = "D"
max_col_num = 4
elif not (ws['C23'].value is None):
max_col = "C"
max_col_num = 3
elif not (ws['B23'].value is None):
max_col = "B"
max_col_num = 2
elif not (ws['A23'].value is None):
max_col = "A"
max_col_num = 1
for row in ws['A23:' + max_col + '24']:
for cell in row:
if cell.row == 23:
cell.number_format = 'yyyy/m/d;@' # 将单元格调整为日期格式，前提是写入的dataframe也是日期格式，最终excel里面才会正常显示为日期格式
cell.font = Font(size=9) # 调整单元格字体
cell.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'),
bottom=Side(style='thin')) # 调整单元格边框
cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) # 调整单元格居中，自动换行
# 以下代码添加折线图
lc4 = LineChart()
# lc.title = ""
lc4.style = 13 # 设置表格样式
lc4.width = 20
data = Reference(ws, min_col=1, min_row=24, max_col=max_col_num, max_row=24) # 设置数据区域
line_data = Reference(ws, min_col=2, min_row=23, max_col=max_col_num, max_row=23) # 设置标题区域
lc4.add_data(data, from_rows=True, titles_from_data=True)
lc4.set_categories(line_data)
line5 = lc4.series[0]
# line1.graphicalProperties.line.solidFill = 'FF0000' #设置线条颜色
line5.graphicalProperties.line.width = 30000 # 设置线条粗细
line5.dLbls = DataLabelList()
line5.dLbls.showVal = False # 显示数据标签
axis = CharacterProperties(b=True, solidFill="FF0000") # 与下一句一起设置数据标签字体加粗、红色
line5.dLbls.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=axis), endParaRPr=axis)])
line5.trendline = Trendline(
spPr=GraphicalProperties(ln=LineProperties(w=20000, prstDash='sysDot', solidFill='5082BE'))) # 显示蓝色点状趋势线
# line1.trendline = Trendline(spPr=GraphicalProperties(ln=LineProperties(solidFill='FF0000'))) # 显示红色趋势线
lc4.legend = None # 隐藏图例
ws.add_chart(lc4, "A27")
wb.save("数据、图表.xlsx")
# 用DataFrame.to_excel()和ExcelWriter一起将数据追加到现有的工作簿中
filepath = "数据、图表.xlsx"
writer = pandas.ExcelWriter(filepath)
writer.book = wb
writer.sheets = {sheets.title: sheets for sheets in writer.book.worksheets}
r2 = requests.post(url, params={'startDate': get_first_day_of_this_year(), 'endDate': get_today()}, headers=headers)
df4 = pandas.read_html(r2.text)[4].sort_index(ascending=False)
df5 = pandas.concat(
[pandas.to_datetime(df4['日期'], format="%Y-%m-%d"), df4['美元'].div(100).round(4), df4['欧元'].div(100).round(4), df4['港元'].div(100).round(4)],
axis=1)
df5.to_excel(writer, sheet_name="Sheet2", index=False)
writer.save()
writer.close()
# 调整sheet2的格式，调整前要重新load一遍工作簿，以获取最新的工作簿
wb = load_workbook("数据、图表.xlsx", data_only=True)
ws2 = wb["Sheet2"]
for row in ws2[ws2.dimensions]:
for cell in row:
if cell.column == 1:
cell.number_format = 'yyyy/m/d;@'
cell.font = Font(size=9)
cell.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'),
bottom=Side(style='thin'))
cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
lc2 = LineChart()
# lc2.title = "今年外汇走势图"
lc2.style = 1
data = Reference(ws2, min_col=2, min_row=1, max_col=2, max_row=ws2.max_row)
line_data = Reference(ws2, min_col=1, min_row=2, max_col=1, max_row=ws2.max_row)
lc2.add_data(data, titles_from_data=True)
lc2.set_categories(line_data)
line3 = lc2.series[0]
line3.graphicalProperties.line.solidFill = '5082BE'
line3.graphicalProperties.line.width = 20000
line3.trendline = Trendline(
spPr=GraphicalProperties(ln=LineProperties(w=20000, prstDash='sysDot', solidFill='5082BE'))) # 显示蓝色点状趋势线
#lc2.legend = None # 隐藏图例
lc3 = LineChart()
# lc2.title = "今年外汇走势图"
lc3.style = 1
data2 = Reference(ws2, min_col=3, min_row=1, max_col=3, max_row=ws2.max_row)
line_data2 = Reference(ws2, min_col=1, min_row=2, max_col=1, max_row=ws2.max_row)
lc3.add_data(data2, titles_from_data=True)
lc3.set_categories(line_data2)
line4 = lc3.series[0]
line4.graphicalProperties.line.solidFill = '5082BE'
line4.graphicalProperties.line.width = 20000
line4.trendline = Trendline(
spPr=GraphicalProperties(ln=LineProperties(w=20000, prstDash='sysDot', solidFill='5082BE'))) # 显示蓝色点状趋势线
#lc3.legend = None # 隐藏图例
lc_hkd = LineChart()
# lc2.title = "今年外汇走势图"
lc_hkd.style = 1
data2 = Reference(ws2, min_col=4, min_row=1, max_col=4, max_row=ws2.max_row)
line_data2 = Reference(ws2, min_col=1, min_row=2, max_col=1, max_row=ws2.max_row)
lc_hkd.add_data(data2, titles_from_data=True)
lc_hkd.set_categories(line_data2)
line_hkd = lc_hkd.series[0]
line_hkd.graphicalProperties.line.solidFill = '5082BE'
line_hkd.graphicalProperties.line.width = 20000
line_hkd.trendline = Trendline(
spPr=GraphicalProperties(ln=LineProperties(w=20000, prstDash='sysDot', solidFill='5082BE'))) # 显示蓝色点状趋势线
#lc_hkd.legend = None # 隐藏图例
ws2.add_chart(lc2, "F2")
ws2.add_chart(lc3, "F20")
ws2.add_chart(lc_hkd, "F40")
wb.save("数据、图表.xlsx")
kd_auto8.py
登录金蝶并导出所选公司的科目余额表，有python抓取窗口信息、模拟键盘输入等方面的知识
import tkinter
from tkinter import messagebox
import win32gui, win32api, win32con, subprocess, time, os
from tkinter import *
import datetime
from dateutil.relativedelta import relativedelta
from tkinter.ttk import Combobox
accounts = ["001优博讯",
"002蓝云达",
"003江南正鼎",
"004武汉优博讯",
"005优软",
"006优金",
"007正达",
"008香港优博讯",
"009云栖",
"010桐庐",
"011博数",
"012厦门优博讯",
"013珠海优博讯"
]
currency_index1 = {"人民币": 0, "美元": 1, "港元": 2, "欧元": 3, "(综合本位币)": 4, "(所有币别)": 5}
currency_index2 = {"人民币": 0, "美元": 1, "港元": 2, "(综合本位币)": 3, "(所有币别)": 4}
current_year = datetime.datetime.now().strftime('%Y') # 获取当前年份
current_month = (datetime.datetime.now().date() - relativedelta(months=1)).strftime('%m') # 获取上个月月份
# 启动K3
def getK3LoginHwnd():
sysLoginWnd = win32gui.FindWindow('ThunderRT6Form', "金蝶K/3系统登录") # K3系统登录窗
if sysLoginWnd == 0:
subprocess.Popen(r'C:\Program Files (x86)\Kingdee\K3ERP\KdMain.exe')
# subprocess发送指令启动后需要等K3登录窗彻底可见后，再进行后续操作
while sysLoginWnd == 0:
time.sleep(0.3)
sysLoginWnd = win32gui.FindWindow('ThunderRT6Form', "金蝶K/3系统登录") # K3系统登录窗
isSysLoginWndVisible = 0
while isSysLoginWndVisible == 0:
time.sleep(0.3)
sysLoginWnd = win32gui.FindWindow('ThunderRT6Form', "金蝶K/3系统登录") # K3系统登录窗
isSysLoginWndVisible = win32gui.IsWindowVisible(sysLoginWnd) # 判断窗口是否已经对用户可见
return sysLoginWnd
# 登录K3
def k3login(lg_wind, account_id, account_name, account_pwd):
mainHwnd1 = win32gui.FindWindowEx(lg_wind, 0, None, '') # ThunderRT6PictureBoxDC
mainHwnd2 = win32gui.FindWindowEx(lg_wind, mainHwnd1, None, '') # ThunderRT6PictureBoxDC
organizationHwnd = win32gui.FindWindowEx(mainHwnd2, 0, "ThunderRT6ComboBox", '') # 组织机构
accountHwnd = win32gui.FindWindowEx(mainHwnd2, organizationHwnd, "ThunderRT6ComboBox", '') # 当前账套
win32gui.SendMessage(accountHwnd, win32con.CB_SETCURSEL, account_id, 0) # 修改下拉框的值
time.sleep(0.1)
passwordHwnd = win32gui.FindWindowEx(mainHwnd2, 0, "ThunderRT6TextBox", None) # 密码框，基于父元素和其类名找到的第一个即可
userNameHwnd = win32gui.FindWindowEx(mainHwnd2, passwordHwnd, None, None) # 用户名框，基于【当前账套】控件句柄找到下一个即可
win32gui.SendMessage(userNameHwnd, win32con.WM_SETTEXT, None, account_name)
time.sleep(0.1)
win32gui.SendMessage(passwordHwnd, win32con.WM_SETTEXT, None, account_pwd)
'''根据登录界面句柄sysLoginWnd，发送回车键实现登录'''
win32gui.PostMessage(lg_wind, win32con.WM_KEYDOWN, win32con.VK_RETURN, 0)
time.sleep(0.01)
win32gui.PostMessage(lg_wind, win32con.WM_KEYUP, win32con.VK_RETURN, 0)
'''启动金蝶后会有一个消息提示窗，定位并关闭它'''
time.sleep(10)
k3msgWnd = win32gui.FindWindow(None, "金蝶提示") # K3系统登录窗
if k3msgWnd > 0:
win32gui.PostMessage(k3msgWnd, win32con.WM_KEYDOWN, win32con.VK_RETURN, 0)
time.sleep(0.01)
win32gui.PostMessage(k3msgWnd, win32con.WM_KEYUP, win32con.VK_RETURN, 0)
# 通过助记码启动科目余额表
def sendAssistCode(mainK3Hwnd, assistCode):
"""假定已经找到K3主界面的句柄且作为入口参数，然后找到助记码窗口，发送特定助记码，直接去对应的功能报表"""
assistCodeHwnd = 0
while assistCodeHwnd == 0:
time.sleep(0.2)
''''''
ABSActiveBarDockHWnd = win32gui.FindWindowEx(mainK3Hwnd, 0, "ABSActiveBarDockWnd", "DockTop")
ThunderRT6PictureBoHwnd1 = win32gui.FindWindowEx(ABSActiveBarDockHWnd, 0, "ThunderRT6PictureBoxDC", "")
ThunderRT6PictureBoHwnd2 = win32gui.FindWindowEx(ABSActiveBarDockHWnd, ThunderRT6PictureBoHwnd1,
"ThunderRT6PictureBoxDC", "")
assistCodeHwnd = win32gui.FindWindowEx(ThunderRT6PictureBoHwnd2, 0, "ThunderRT6TextBox", "")
'''在k3主界面输入助记码并登录特定报表窗'''
win32gui.SendMessage(assistCodeHwnd, win32con.WM_SETTEXT, None, assistCode)
win32gui.PostMessage(assistCodeHwnd, win32con.WM_KEYDOWN, win32con.VK_RETURN, 0)
time.sleep(0.01)
win32gui.PostMessage(assistCodeHwnd, win32con.WM_KEYUP, win32con.VK_RETURN, 0)
# 科目余额表过滤条件
def subjectBalanceFilter(conditionFlag1, conditionFlag2, conditionFlag3, conditionFlag4, conditionFlag5,
conditionFlag6, conditionFlag7, conditionFlag8, subjectLevel, subjectYearFrom,
subjectMonthFrom, subjectYearTo, subjectMonthTo, currency_index, subjectCodeFrom,
subjectCodeTo):
'''设置科目余额表过滤条件，等待不超过10秒，捕获【过滤条件】窗口，如果仍未出现，可能是出现了【异常弹窗】'''
filterConditionHwnd = 0
while filterConditionHwnd == 0:
time.sleep(0.3)
filterConditionHwnd = win32gui.FindWindow('ThunderRT6FormDC', "过滤条件")
IsWindowVisible = 0
while IsWindowVisible == 0:
time.sleep(0.3)
filterConditionHwnd = win32gui.FindWindow('ThunderRT6FormDC', "过滤条件")
IsWindowVisible = win32gui.IsWindowVisible(filterConditionHwnd)
print("已找到【科目余额表】过滤条件窗口")
time.sleep(0.5)
subjectLevelHwnd = 0
while subjectLevelHwnd == 0:
time.sleep(0.3)
userControlDcHwnd = win32gui.FindWindowEx(filterConditionHwnd, 0, "ThunderRT6UserControlDC",
None) # ThunderRT6UserControlDC
condition1Hwnd = win32gui.FindWindowEx(userControlDcHwnd, 0, "ThunderRT6CheckBox",
"显示核算项目明细") # 显示核算项目明细 checkBox
condition2Hwnd = win32gui.FindWindowEx(userControlDcHwnd, 0, "ThunderRT6CheckBox",
"包括未过账凭证") # 包括未过账凭证 checkBox
condition3Hwnd = win32gui.FindWindowEx(userControlDcHwnd, 0, "ThunderRT6CheckBox",
"包括余额为零的科目") # 包括余额为零的科目 checkBox
condition4Hwnd = win32gui.FindWindowEx(userControlDcHwnd, 0, "ThunderRT6CheckBox",
"包括余额借贷方合计") # 包括余额借贷方合计 checkBox
condition5Hwnd = win32gui.FindWindowEx(userControlDcHwnd, 0, "ThunderRT6CheckBox",
"包括没有业务发生的科目(期初、本年累计)") # 包括没有业务发生的科目(期初、本年累计) checkBox
condition6Hwnd = win32gui.FindWindowEx(userControlDcHwnd, 0, "ThunderRT6CheckBox",
"包括本期没有发生额的科目") # 包括本期没有发生额的科目 checkBox
condition7Hwnd = win32gui.FindWindowEx(userControlDcHwnd, 0, "ThunderRT6CheckBox",
"包括本年没有发生额的科目") # 包括本年没有发生额的科目 checkBox
condition8Hwnd = win32gui.FindWindowEx(userControlDcHwnd, 0, "ThunderRT6CheckBox", "显示禁用科目") # 显示禁用科目 checkBox
AdvancedBtn = win32gui.FindWindowEx(userControlDcHwnd, 0, "ThunderRT6CommandButton", "高级>>") # 高级按钮
accountingPeriodPreviousHwnd = win32gui.FindWindowEx(userControlDcHwnd, 0, "ThunderRT6Frame", None) # 凭证期间
accountingPeriodHwnd = win32gui.FindWindowEx(userControlDcHwnd, accountingPeriodPreviousHwnd, "ThunderRT6Frame",
None) # 凭证期间
parentYearFromHwnd = win32gui.FindWindowEx(accountingPeriodHwnd, 0, "ThunderRT6UserControlDC", None) # YearFrom
yearFromHwnd = win32gui.FindWindowEx(parentYearFromHwnd, 0, "ThunderRT6TextBox", None) # YearFrom
parentMonthToHwnd = win32gui.FindWindowEx(accountingPeriodHwnd, parentYearFromHwnd, "ThunderRT6UserControlDC",
None) # MonthTo
monthToHwnd = win32gui.FindWindowEx(parentMonthToHwnd, 0, "ThunderRT6TextBox", None) # MonthTo
parentMonthFromHwnd = win32gui.FindWindowEx(accountingPeriodHwnd, parentMonthToHwnd, "ThunderRT6UserControlDC",
None) # MonthFrom
monthFromHwnd = win32gui.FindWindowEx(parentMonthFromHwnd, 0, "ThunderRT6TextBox", None) # MonthFrom
parentYearToHwnd = win32gui.FindWindowEx(accountingPeriodHwnd, parentMonthFromHwnd, "ThunderRT6UserControlDC",
None) # YearTo
yearToHwnd = win32gui.FindWindowEx(parentYearToHwnd, 0, "ThunderRT6TextBox", None) # YearTo
granpaSubjectLevelHwnd = win32gui.FindWindowEx(userControlDcHwnd, accountingPeriodHwnd, "ThunderRT6Frame",
None) # 科目级别
subjectToHwnd = win32gui.FindWindowEx(granpaSubjectLevelHwnd, 0, "ThunderRT6TextBox",
None) # 科目代码到
subjectFromHwnd = win32gui.FindWindowEx(granpaSubjectLevelHwnd, subjectToHwnd, "ThunderRT6TextBox",
None) # 科目代码从
currencyHwnd = win32gui.FindWindowEx(granpaSubjectLevelHwnd, 0, "ThunderRT6ComboBox",
None) # 币别
parentSubjectLevelHwnd = win32gui.FindWindowEx(granpaSubjectLevelHwnd, 0, "ThunderRT6UserControlDC",
None) # 科目级别
subjectLevelHwnd = win32gui.FindWindowEx(parentSubjectLevelHwnd, 0, "ThunderRT6TextBox", None) # 科目级别
print("已找到【科目余额表】过滤条件下各个控件元素")
'''点击 高级，展开更多checkbox项'''
time.sleep(0.2)
win32gui.SendMessage(AdvancedBtn, win32con.BM_CLICK, 0, 0)
time.sleep(0.1)
conditionHwndDic = {condition1Hwnd: conditionFlag1, condition2Hwnd: conditionFlag2, condition3Hwnd: conditionFlag3,
condition4Hwnd: conditionFlag4,
condition5Hwnd: conditionFlag5, condition6Hwnd: conditionFlag6, condition7Hwnd: conditionFlag7,
condition8Hwnd: conditionFlag8} # 字典，key是conditionHwnd，value则是对应的checkbox状态，为布尔值
time.sleep(0.2)
'''根据checkbox配置，设置K3对应各个checkbox值'''
for conditionHwnd in conditionHwndDic:
conditionFlag = conditionHwndDic[conditionHwnd]
currentCheckFlag = win32gui.SendMessage(conditionHwnd, win32con.BM_GETCHECK) # 显示K3系统当前特定checkbox的布尔值
while currentCheckFlag != conditionFlag:
time.sleep(0.2)
win32gui.PostMessage(conditionHwnd, win32con.BM_SETCHECK, conditionFlag, 0)
time.sleep(0.1)
currentCheckFlag = win32gui.SendMessage(conditionHwnd, win32con.BM_GETCHECK) # 显示K3系统当前特定checkbox的布尔值
win32api.SendMessage(yearFromHwnd, win32con.WM_SETTEXT, None, subjectYearFrom)
time.sleep(0.2)
win32api.SendMessage(monthFromHwnd, win32con.WM_SETTEXT, None, subjectMonthFrom)
time.sleep(0.2)
win32api.SendMessage(yearToHwnd, win32con.WM_SETTEXT, None, subjectYearTo)
time.sleep(0.2)
win32api.SendMessage(monthToHwnd, win32con.WM_SETTEXT, None, subjectMonthTo)
time.sleep(0.2)
win32gui.SendMessage(subjectFromHwnd, win32con.WM_SETTEXT, None, subjectCodeFrom) # 设置科目级别
time.sleep(0.3)
win32gui.SendMessage(subjectToHwnd, win32con.WM_SETTEXT, None, subjectCodeTo) # 设置科目级别
time.sleep(0.3)
win32gui.SendMessage(subjectLevelHwnd, win32con.WM_SETTEXT, None, subjectLevel) # 设置科目级别
time.sleep(0.3)
win32gui.SendMessage(currencyHwnd, win32con.CB_SETCURSEL, currency_index, 0) # 修改币别下拉框
time.sleep(0.3)
'''给过滤条件窗口发送回车，代表确定'''
time.sleep(1)
okBtnHwnd = win32gui.FindWindowEx(filterConditionHwnd, 0, "ThunderRT6CommandButton", "确定")
win32gui.PostMessage(okBtnHwnd, win32con.BM_CLICK, 0, 0)
# 程序主窗口
root = Tk()
root.title("科目余额表批量导出") # 主窗口的标题
root.wm_geometry('550x600+500+250') # 主窗口的大小及位置，大小为500*450，距离屏幕左边500px，距离屏幕顶部250px
root.resizable(False, False) # 这只主窗口不可以拉动改变大小
l_frame_5 = LabelFrame(root, text="选择账套", padx=10, pady=10, width=200, height=550)
l_frame_5.pack(padx=(0, 20), pady=(10, 163), side=tkinter.RIGHT) # 使用pack才算将该控件绑定到窗口上，距离顶部20像素
l_frame_1 = LabelFrame(root, text="金蝶账号密码", padx=10, pady=10)
l_frame_1.pack(pady=10) # 使用pack才算将该控件绑定到窗口上，距离顶部20像素
l_frame_2 = LabelFrame(root, text="会计期间", padx=10, pady=10)
l_frame_2.pack(pady=10) # 使用pack才算将该控件绑定到窗口上，距离顶部20像素
l_frame_3 = LabelFrame(root, text="科目设置", padx=10, pady=10, width=200)
l_frame_3.pack(pady=10) # 使用pack才算将该控件绑定到窗口上，距离顶部20像素
l_frame_4 = LabelFrame(root, text="高级选项", padx=10, pady=10)
l_frame_4.pack(pady=10) # 使用pack才算将该控件绑定到窗口上，距离顶部20像素
label_name = Label(l_frame_1, text="账号")
label_pwd = Label(l_frame_1, text='密码')
entry_name = Entry(l_frame_1, bd=2)
entry_pwd = Entry(l_frame_1, bd=2, show='*')
label_name.grid(row=0, column=0, padx=30, pady=5)
label_pwd.grid(row=1, column=0, padx=30, pady=5)
entry_name.grid(row=0, column=1, padx=10, pady=5)
entry_pwd.grid(row=1, column=1, padx=10, pady=5)
label_from = Label(l_frame_2, text='从:')
label_year_from = Label(l_frame_2, text='年')
label_month_from = Label(l_frame_2, text='月')
entry_year_from = Entry(l_frame_2, bd=2, width=5, justify=RIGHT)
entry_year_from.insert(0, current_year)
entry_month_from = Entry(l_frame_2, bd=2, width=3, justify=RIGHT)
entry_month_from.insert(0, current_month)
label_from.grid(row=0, column=0, padx=23, pady=5)
label_year_from.grid(row=0, column=2, padx=5, pady=5)
label_month_from.grid(row=0, column=4, padx=(5, 40), pady=5)
entry_year_from.grid(row=0, column=1, padx=5, pady=5)
entry_month_from.grid(row=0, column=3, padx=5, pady=5)
label_to = Label(l_frame_2, text='到:')
label_year_to = Label(l_frame_2, text='年')
label_month_to = Label(l_frame_2, text='月')
entry_year_to = Entry(l_frame_2, bd=2, width=5, justify=RIGHT)
entry_year_to.insert(0, current_year)
entry_month_to = Entry(l_frame_2, bd=2, width=3, justify=RIGHT)
entry_month_to.insert(0, current_month)
label_to.grid(row=1, column=0, padx=23, pady=5)
label_year_to.grid(row=1, column=2, padx=5, pady=5)
label_month_to.grid(row=1, column=4, padx=(5, 40), pady=5)
entry_year_to.grid(row=1, column=1, padx=5, pady=5)
entry_month_to.grid(row=1, column=3, padx=5, pady=5)
label_subject_from = Label(l_frame_3, text='科目代码从:')
entry_subject_from = Entry(l_frame_3, bd=2, width=16)
label_subject_to = Label(l_frame_3, text='科目代码到:')
entry_subject_to = Entry(l_frame_3, bd=2, width=16)
label_subject_level = Label(l_frame_3, text='科目级别:')
v1 = StringVar()
v1.set(1)
combobox_subject_level = Combobox(l_frame_3, height=4, width=3, state="readonly", values=[1, 2, 3, 4], textvariable=v1)
label_currency = Label(l_frame_3, text='币别:')
v2 = StringVar()
v2.set("(综合本位币)")
combox_currency = Combobox(l_frame_3, height=4, width=12, state="readonly",
values=["人民币", "美元", "港元", "欧元", "(综合本位币)", "(所有币别)"], textvariable=v2)
label_subject_from.grid(row=0, column=0, padx=30, pady=5)
entry_subject_from.grid(row=0, column=1, padx=5, pady=5)
label_subject_to.grid(row=1, column=0, padx=30, pady=5)
entry_subject_to.grid(row=1, column=1, padx=5, pady=5)
label_subject_level.grid(row=2, column=0, padx=30, pady=5)
combobox_subject_level.grid(row=2, column=1, padx=(0, 70), pady=5)
label_currency.grid(row=3, column=0, padx=30, pady=5)
combox_currency.grid(row=3, column=1, padx=(0, 10), pady=5)
v3 = BooleanVar()
check_btn1 = Checkbutton(l_frame_4, text="显示核算项目明细", variable=v3)
check_btn1.grid(row=0, column=0, padx=(30, 100), pady=5)
va1 = BooleanVar()
check_btn_a1 = Checkbutton(l_frame_5, text="001 优博讯", variable=va1)
check_btn_a1.grid(row=0, column=0, padx=(30, 30), pady=0, sticky=W, columnspan=2)
va2 = BooleanVar()
check_btn_a2 = Checkbutton(l_frame_5, text="002 蓝云达", variable=va2)
check_btn_a2.grid(row=1, column=0, padx=(30, 30), pady=0, sticky=W, columnspan=2)
va3 = BooleanVar()
check_btn_a3 = Checkbutton(l_frame_5, text="003 江南正鼎", variable=va3)
check_btn_a3.grid(row=2, column=0, padx=(30, 30), pady=0, sticky=W, columnspan=2)
va4 = BooleanVar()
check_btn_a4 = Checkbutton(l_frame_5, text="004 武汉优博讯", variable=va4)
check_btn_a4.grid(row=3, column=0, padx=(30, 30), pady=0, sticky=W, columnspan=2)
va5 = BooleanVar()
check_btn_a5 = Checkbutton(l_frame_5, text="005 优软", variable=va5)
check_btn_a5.grid(row=4, column=0, padx=(30, 30), pady=0, sticky=W, columnspan=2)
va6 = BooleanVar()
check_btn_a6 = Checkbutton(l_frame_5, text="006 优金", variable=va6)
check_btn_a6.grid(row=5, column=0, padx=(30, 30), pady=0, sticky=W, columnspan=2)
va7 = BooleanVar()
check_btn_a7 = Checkbutton(l_frame_5, text="007 正达", variable=va7)
check_btn_a7.grid(row=6, column=0, padx=(30, 30), pady=0, sticky=W, columnspan=2)
va8 = BooleanVar()
check_btn_a8 = Checkbutton(l_frame_5, text="008 香港优博讯", variable=va8)
check_btn_a8.grid(row=7, column=0, padx=(30, 30), pady=0, sticky=W, columnspan=2)
va9 = BooleanVar()
check_btn_a9 = Checkbutton(l_frame_5, text="009 云栖", variable=va9)
check_btn_a9.grid(row=8, column=0, padx=(30, 30), pady=0, sticky=W, columnspan=2)
va10 = BooleanVar()
check_btn_a10 = Checkbutton(l_frame_5, text="010 桐庐", variable=va10)
check_btn_a10.grid(row=9, column=0, padx=(30, 30), pady=0, sticky=W, columnspan=2)
va11 = BooleanVar()
check_btn_a11 = Checkbutton(l_frame_5, text="011 博数", variable=va11)
check_btn_a11.grid(row=10, column=0, padx=(30, 30), pady=0, sticky=W, columnspan=2)
va12 = BooleanVar()
check_btn_a12 = Checkbutton(l_frame_5, text="012 厦门优博讯", variable=va12)
check_btn_a12.grid(row=11, column=0, padx=(30, 30), pady=0, sticky=W, columnspan=2)
va13 = BooleanVar()
check_btn_a13 = Checkbutton(l_frame_5, text="013 珠海优博讯", variable=va13)
check_btn_a13.grid(row=12, column=0, padx=(30, 30), pady=0, sticky=W, columnspan=2)
def btn_select_all_click():
check_btn_a1.select()
check_btn_a2.select()
check_btn_a3.select()
check_btn_a4.select()
check_btn_a5.select()
check_btn_a6.select()
check_btn_a7.select()
check_btn_a8.select()
check_btn_a9.select()
check_btn_a10.select()
check_btn_a11.select()
check_btn_a12.select()
check_btn_a13.select()
def btn_deselect_all_click():
check_btn_a1.deselect()
check_btn_a2.deselect()
check_btn_a3.deselect()
check_btn_a4.deselect()
check_btn_a5.deselect()
check_btn_a6.deselect()
check_btn_a7.deselect()
check_btn_a8.deselect()
check_btn_a9.deselect()
check_btn_a10.deselect()
check_btn_a11.deselect()
check_btn_a12.deselect()
check_btn_a13.deselect()
btn_select_all = Button(l_frame_5, text="全选", command=btn_select_all_click)
btn_select_all.grid(row=13, column=0, padx=(30, 0), pady=5, sticky=W)
btn_deselect_all = Button(l_frame_5, text="清除选择", command=btn_deselect_all_click)
btn_deselect_all.grid(row=13, column=1, padx=(0, 30), pady=5, sticky=W)
btn_select_all_click()
def btn_export_click():
account_name = entry_name.get()
account_pwd = entry_pwd.get()
year_from = entry_year_from.get()
year_to = entry_year_to.get()
month_from = entry_month_from.get()
month_to = entry_month_to.get()
subject_from = entry_subject_from.get()
subject_to = entry_subject_to.get()
subject_level = combobox_subject_level.get()
currency = combox_currency.get()
is_project_visible = v3.get()
selected_account = []
if va1.get():
selected_account.append(0)
if va2.get():
selected_account.append(1)
if va3.get():
selected_account.append(2)
if va4.get():
selected_account.append(3)
if va5.get():
selected_account.append(4)
if va6.get():
selected_account.append(5)
if va7.get():
selected_account.append(6)
if va8.get():
selected_account.append(7)
if va9.get():
selected_account.append(8)
if va10.get():
selected_account.append(9)
if va11.get():
selected_account.append(10)
if va12.get():
selected_account.append(11)
if va13.get():
selected_account.append(12)
if (account_name.strip() == "") or (account_pwd.strip() == ""):
messagebox.showinfo(title="提示", message="请先输入金蝶账号和密码！")
else:
if len(selected_account) > 0:
for i in selected_account:
filename = accounts[i] + "科目余额表" + year_from + "年" + month_from + "月-" + year_to + "年" + month_to + "月"
#print(filename)
# time.sleep(1)
lg_wind = getK3LoginHwnd() # 启动K3，得到登录窗口
k3login(lg_wind, i, account_name, account_pwd) # 登录K3
'''找到主窗口，并通过助记码启动科目余额表功能'''
mainK3Hwnd = win32gui.FindWindow("ThunderRT6MDIForm", None)
sendAssistCode(mainK3Hwnd, "01016")
# 确定过滤条件
# ci = 0
if "优软" in filename or ("优金" in filename) or ("博数" in filename):
ci = currency_index2[currency]
else:
ci = currency_index1[currency]
subjectBalanceFilter(is_project_visible, True, True, False, False, True, True, True, subject_level,
year_from,
month_from, year_to, month_to, ci, subject_from, subject_to)
time.sleep(10)
SubjectBalanceHwnd = 0
while SubjectBalanceHwnd == 0:
time.sleep(0.3)
SubjectBalanceHwnd = win32gui.FindWindow('ThunderRT6MDIForm', "总账系统 - [科目余额表]")
IsWindowVisible = 0
while IsWindowVisible == 0:
time.sleep(0.3)
SubjectBalanceHwnd = win32gui.FindWindow('ThunderRT6MDIForm', "总账系统 - [科目余额表]")
IsWindowVisible = win32gui.IsWindowVisible(SubjectBalanceHwnd)
print("已找到【科目余额表】主窗口")
# 使窗口获得焦点
# win32gui.SendMessage(SubjectBalanceHwnd, win32con.WM_SYSCOMMAND, win32con.SC_RESTORE, 0)
win32gui.SetForegroundWindow(SubjectBalanceHwnd)
# root.clipboard_clear()
# root.clipboard_append(filename)
# 按下Alt键
win32api.keybd_event(18, 0, 0, 0)
time.sleep(0.01)
win32api.keybd_event(18, 0, win32con.KEYEVENTF_KEYUP, 0)
time.sleep(0.5)
# 按下→键
win32api.keybd_event(39, 0, 0, 0)
time.sleep(0.01)
win32api.keybd_event(39, 0, win32con.KEYEVENTF_KEYUP, 0)
time.sleep(0.5)
# 按下↓键
win32api.keybd_event(40, 0, 0, 0)
time.sleep(0.01)
win32api.keybd_event(40, 0, win32con.KEYEVENTF_KEYUP, 0)
time.sleep(0.5)
# 按下回车键
win32api.keybd_event(13, 0, 0, 0)
time.sleep(0.01)
win32api.keybd_event(13, 0, win32con.KEYEVENTF_KEYUP, 0)
time.sleep(0.5)
# 按下←键
win32api.keybd_event(37, 0, 0, 0)
time.sleep(0.01)
win32api.keybd_event(37, 0, win32con.KEYEVENTF_KEYUP, 0)
time.sleep(0.5)
# 按下回车键
win32api.keybd_event(13, 0, 0, 0)
time.sleep(0.01)
win32api.keybd_event(13, 0, win32con.KEYEVENTF_KEYUP, 0)
time.sleep(0.5)
# 找到“选择文件”窗口并填写待保存的文件名称
Hwnd1 = win32gui.FindWindow(None, "选择 EXCEL 文件")
Hwnd2 = win32gui.FindWindowEx(Hwnd1, 0, "DUIViewWndClassName", "")
Hwnd3 = win32gui.FindWindowEx(Hwnd2, 0, "DirectUIHWND", "")
Hwnd4 = win32gui.FindWindowEx(Hwnd3, 0, "FloatNotifySink", "")
Hwnd5 = win32gui.FindWindowEx(Hwnd4, 0, "ComboBox", "")
Hwnd6 = win32gui.FindWindowEx(Hwnd5, 0, "Edit", "")
win32gui.SendMessage(Hwnd6, win32con.WM_SETTEXT, None, filename)
time.sleep(2)
# 按下回车键保存文件
win32api.keybd_event(13, 0, 0, 0)
time.sleep(0.01)
win32api.keybd_event(13, 0, win32con.KEYEVENTF_KEYUP, 0)
time.sleep(1)
# 按下回车键
win32api.keybd_event(13, 0, 0, 0)
time.sleep(0.01)
win32api.keybd_event(13, 0, win32con.KEYEVENTF_KEYUP, 0)
time.sleep(1)
# 按下回车键
win32api.keybd_event(13, 0, 0, 0)
time.sleep(0.01)
win32api.keybd_event(13, 0, win32con.KEYEVENTF_KEYUP, 0)
time.sleep(1)
'''找到主窗口,关闭主窗口'''
mainK3Hwnd = win32gui.FindWindow("ThunderRT6MDIForm", None)
win32gui.PostMessage(mainK3Hwnd, win32con.WM_CLOSE, 0, 0)
else:
messagebox.showinfo(title="提示", message="请先选择账套！")
btn_export = Button(root, text='开始导出', command=btn_export_click)
btn_export.pack()
root.mainloop()
maoning_mail.py
import os
import smtplib
import tkinter
from email.header import Header
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from tkinter import Tk, Entry, Label, Button, END, messagebox
import tkinter.filedialog
dept_list = ["营销中心",
"战略大客户部",
"华东区大客户部",
]
to_list = {
"营销中心": ["[email protected]"],
"战略大客户部": ["[email protected]"],
"华东区大客户部": ["[email protected]"],
}
to_list_test = {
"营销中心": ["[email protected]"],
"战略大客户部": ["[email protected]"],
"华东区大客户部": ["[email protected]"],
}
cc_list = {
"营销中心": ["[email protected]", "[email protected]", "[email protected]","[email protected]","[email protected]"],
"战略大客户部": ["[email protected]", "[email protected]", "[email protected]", "[email protected]","[email protected]"],
"华东区大客户部": ["[email protected]", "[email protected]", "[email protected]", "[email protected]","[email protected]"],
}
cc_list_test = {
"营销中心": ["[email protected]"],
"战略大客户部": ["[email protected]"],
"华东区大客户部": ["[email protected]"],
}
name_list = {
"营销中心": ["郭总"],
"战略大客户部": ["张总"],
"华东区大客户部": ["李总"]
}
bp_list = {
"营销中心": "冷莹、王博、段春雪",
"战略大客户部": "冷莹、李堤",
"华东区大客户部": "冷莹、李堤"
}
root = Tk()
root.title("批量发送邮件@毛宁 v1.0")
root.wm_geometry('400x250+500+250')
root.resizable(False, False)
label_name = Label(root, text="发件人邮箱")
label_pwd = Label(root, text='发件人密码')
label_addr_list = Label(root, text='待发送文件所在目录')
label_mail_title = Label(root, text='邮件标题年份月份')
entry_name = Entry(root, bd=2)
entry_pwd = Entry(root, bd=2, show='*')
entry_addr_list = Entry(root, bd=2)
entry_mail_title = Entry(root, bd=2)
def btn_view_click():
entry_addr_list.delete(0, END)
entry_addr_list.insert(0, tkinter.filedialog.askdirectory())
def btn_send_click():
sender_name = entry_name.get()
sender_pwd = entry_pwd.get()
file_path = entry_addr_list.get()
mail_title = entry_mail_title.get()
smtp = smtplib.SMTP_SSL(host="smtp.exmail.qq.com", port=465)
smtp.login(sender_name, sender_pwd)
try:
for file_name in os.listdir(file_path):
ex_file_name = os.path.splitext(file_name)[0]
print(ex_file_name)
if ex_file_name in dept_list:
msg = MIMEMultipart()
msg['From'] = sender_name
msg['To'] = ';'.join(to_list[ex_file_name])
msg['Cc'] = ';'.join(cc_list[ex_file_name])
msg['Subject'] = mail_title + '预算执行情况表：' + ex_file_name
message_sign = '<hr />Best regards<br/>' \
'毛宁/财务分析/财务中心Morning/FP&A/Financial Center<br/>' \
'M.P:+86-15737900857<br/>' \
'E-mail:[email protected] <br/>'\
' <br/>' \
'深圳市优博讯科技股份有限公司（股票代码：300531）<br/>' \
'UROVO TECHNOLOGY CO., LTD. (Stock Code: 300531.SZ)<br/>' \
'深圳市南山区学府路63号高新区联合总部大厦36-37楼<br/>' \
'Floor 36-37, Hi-tech Zone Union Tower,No.63 Xuefu Road, Nanshan District, Shenzhen,Guangdong,<br/>' \
'ChinaTel：+86-755-86186300,<br/>' \
'Web：http://www.urovo.com<br/>' \
'------------------------------------<br/>' \
'本邮件包含信息归优博讯所有,优博讯对该邮件拥有所有权利。请收件人注意保密,未经发件人书面许可,不得向任何第三方组织和个人透露本邮件所含全部或部分信息。<br/>' \
'CONFIDENTIALITY NOTICE. This message is intended exclusively for the named addressee and ' \
'may contain confidential information. Unless you are the named addressee (or authorised ' \
'to receive for the addressee) you may not copy, use or disclose this message. If this ' \
'e-mail was sent to you by mistake please notify the sender immediately and delete this ' \
'e-mail. '
msg.attach(MIMEText('<p>Dear' + name_list[ex_file_name][
0] + '，</p><p>附件为' + ex_file_name + '的预算执行情况表，供您参考！</p><p>若有疑问可咨询财务BP' + bp_list[
ex_file_name] + '</p>' + message_sign, 'html', 'utf-8'))
xlsx_file = MIMEApplication(open(file_path + "\\" + file_name, 'rb').read())
xlsx_file['Content-type'] = 'application/octet-stream'
xlsx_file.add_header('Content-Disposition', 'attachment', filename=Header(file_name,'utf-8').encode()) # 添加到header信息，filename使用Header编码以防止乱码
msg.attach(xlsx_file)
smtp.sendmail(sender_name, to_list[ex_file_name] + cc_list[ex_file_name], msg.as_string())
#smtp.sendmail(sender_name, to_list[ex_file_name], msg.as_string())
smtp.quit()
messagebox.showinfo('发送成功', '邮件发送成功，请登录邮箱查看已发送邮件')
except Exception as e:
messagebox.showerror('错误', e)
btn_view = Button(root, text='浏览...', command=btn_view_click)
btn_send = Button(root, text='发送邮件', command=btn_send_click)
label_name.grid(row=2, column=0, padx=10, pady=10)
label_pwd.grid(row=3, column=0, padx=10, pady=10)
label_addr_list.grid(row=4, column=0, padx=10, pady=10)
label_mail_title.grid(row=5, column=0, padx=10, pady=10)
entry_name.grid(row=2, column=1, padx=10, pady=10)
entry_pwd.grid(row=3, column=1, padx=10, pady=10)
entry_addr_list.grid(row=4, column=1, padx=10, pady=10)
entry_mail_title.grid(row=5, column=1, padx=10, pady=10)
btn_view.grid(row=4, column=2, padx=10, pady=10)
btn_send.grid(row=6, column=1, padx=10, pady=10)
root.mainloop()
recon_mail4.py
批量发送对账单文件
# 将收件人改为可以添加任意收件人
import calendar
import os
import re
import smtplib
import time
import tkinter
import tkinter.filedialog
from email.header import Header
from email.mime.application import MIMEApplication
from email.mime.image import MIMEImage
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from tkinter import Tk, LabelFrame, Label, Entry, Button, END, messagebox
import win32com.client
import win32com
import win32timezone
from PIL import ImageGrab
salesman = dict()
to_list = dict()
cc_list = dict()
bp_name = {
"[email protected]": "李堤",
"[email protected]": "段春雪",
"[email protected]": "李媛媛",
"[email protected]": "王博"
}
root = Tk()
root.title("对账邮件发送工具")
root.wm_geometry('500x350+500+250')
root.resizable(False, False)
l_frame_1 = LabelFrame(root, text="对账邮件发送", padx=10, pady=10)
l_frame_1.pack(pady=20)
label_1 = Label(l_frame_1, text="请选择待发送的对账单文件所在路径：")
text_box = Entry(l_frame_1, bd=2)
label_1.grid(row=0, column=0, padx=5, pady=10)
text_box.grid(row=0, column=1, padx=5, pady=10)
label_2 = Label(l_frame_1, text="请选择收件人配置文件：")
text_box2 = Entry(l_frame_1, bd=2)
label_2.grid(row=1, column=0, padx=5, pady=10)
text_box2.grid(row=1, column=1, padx=5, pady=10)
def btn_view_click():
text_box.delete(0, END)
text_box.insert(0, tkinter.filedialog.askdirectory())
def btn_view2_click():
text_box2.delete(0, END)
text_box2.insert(0, tkinter.filedialog.askopenfilename(filetypes=[("EXCEL", ".xlsx")]))
btn_view = Button(l_frame_1, text='浏览...', command=btn_view_click)
btn_view.grid(row=0, column=2, padx=5, pady=10)
btn_view2 = Button(l_frame_1, text='浏览...', command=btn_view2_click)
btn_view2.grid(row=1, column=2, padx=5, pady=10)
label_email = Label(l_frame_1, text="请输入你的邮箱地址：")
label_pwd = Label(l_frame_1, text='请输入你的邮箱密码：')
entry_email = Entry(l_frame_1, bd=2)
entry_pwd = Entry(l_frame_1, bd=2, show='*')
label_email.grid(row=2, column=0, padx=10, pady=10)
label_pwd.grid(row=3, column=0, padx=10, pady=10)
entry_email.grid(row=2, column=1, padx=10, pady=10)
entry_pwd.grid(row=3, column=1, padx=0, pady=10)
info_string = tkinter.StringVar()
info_string.set("提示：请先关闭所有EXCEL文档，再使用本程序！")
label_info = Label(l_frame_1, textvariable=info_string, fg="red")
label_info.grid(row=5, column=0, padx=10, pady=10, sticky="w", columnspan=3)
def btn_send_click():
file_path = text_box.get()
list_settings = text_box2.get()
sender_name = entry_email.get()
sender_pwd = entry_pwd.get()
# 读取收件人配置
info_string.set("状态：正在读取收件人配置...")
root.update()
xcl1 = win32com.client.Dispatch("ket.Application")
xcl1.DisplayAlerts = False # 不显示警告，保存文件时直接覆盖保存，不弹出提示
xcl1.Visible = False # 后台运行
wb1 = xcl1.Workbooks.Open(list_settings)
ws1 = wb1.Worksheets(1)
# wb.SaveAs(full_path, None, open_pwd, edit_pwd)
# print(ws.Cells(1,2).Value)
# max_row = ws.UsedRange.Rows.Count # 获取已使用区域的最大行数
max_row1 = ws1.Range("A1000").End(-4162).Row # 相当于ctrl + ↑
max_col1 = ws1.UsedRange.Columns.Count # 获取已使用区域的最大列数
print(list_settings + ":" + str(max_row1) + "," + str(max_col1))
range_data1 = ws1.Range(ws1.Cells(1, 1), ws1.Cells(max_row1, max_col1)).Value # 获取表格数据保存为二维元组
# print(range_data[0][0])
wb1.Close(SaveChanges=False)
xcl1.Quit()
#print(range_data1[1][3])
for i in range(1, max_row1):
salesman[range_data1[i][0]] = range_data1[i][2]
to_list[range_data1[i][0]] = range_data1[i][4].split("; ") # 此处range外围一定要加[ ]以明确这是个list类型，方便后面与cc_list做合并
cc_list[range_data1[i][0]] = range_data1[i][5].split("; ")
# print(salesman.items())
# print(to_list.items())
print(to_list.items())
print(cc_list.items())
try:
smtp = smtplib.SMTP_SSL(host="smtp.exmail.qq.com", port=465)
smtp.login(sender_name, sender_pwd)
except Exception as e:
messagebox.showerror('错误', "邮箱或密码错误！")
try:
for file_name in os.listdir(file_path):
company = file_name.split("-")[0] # 以-符号分割获取到的文件名，获取分割结果的第1部分
ar_end_balance = 0
sales_amount = 0
# 如果文件名截取到的公司在销售员字典的键中，并且文件名以.xlsx或.xls结尾，并且不以~$开头
if (company in salesman.keys()) and (file_name.endswith(".xlsx") or file_name.endswith(".xls")) and (
not file_name.startswith("~$")):
info_string.set("状态：" + company + " 邮件发送中...")
root.update()
ex_file_name = os.path.splitext(file_name)[0] # 获取文件名不含后缀的部分
year = re.search("\d+", re.search("\d+年", file_name).group()).group()
month = re.search("\d+", re.search("\d+月", file_name).group()).group()
last_day = str(calendar.monthrange(int(year), int(month))[1]) # 获取年月的最后一天
deadline = year + "-" + month + "-" + last_day
firstday = year + "-" + month + "-" + "1"
print(year + "年" + month + "月" + str(last_day) + "日")
# 调用win32com读取对账单excel文件数据
full_path = file_path + "\\" + file_name # 获取完整路径+文件名
xcl = win32com.client.Dispatch("ket.Application")
# xcl.DisplayAlerts = False # 不显示警告，保存文件时直接覆盖保存，不弹出提示
xcl.Visible = False # 后台运行
wb = xcl.Workbooks.Open(full_path)
ws = wb.Worksheets(1)
# wb.SaveAs(full_path, None, open_pwd, edit_pwd)
# print(ws.Cells(1,2).Value)
# max_row = ws.UsedRange.Rows.Count # 获取已使用区域的最大行数
max_row = ws.Range("B1000").End(-4162).Row # 相当于ctrl + ↑
max_col = ws.UsedRange.Columns.Count # 获取已使用区域的最大列数
print(file_name + ":" + str(max_row) + "," + str(max_col))
range_data = ws.Range(ws.Cells(1, 2), ws.Cells(max_row, 12)).Value # 获取表格数据保存为二维元组
# print(range_data[0][0])
# 遍历二维元组，获得需要的数据
for i in range(0, max_row):
for j in range(0, 10):
if ("月末应收余额" in str(range_data[i][j])) or ("期末应收余额" in str(range_data[i][j])):
ar_end_balance = range_data[i][j + 1]
if "本期发货金额" in str(range_data[i][j]):
sales_amount = range_data[i][j + 1]
print("月末应收余额：" + str(ar_end_balance))
print("本期发货金额：" + str(sales_amount))
# for r in ws.Range(ws.Cells(1,1),ws.Cells(max_row,12)):
# print(r.Value)
# 将excel转换成图片
selection = ws.Range(ws.Cells(1, 2), ws.Cells(max_row, 12))
selection.CopyPicture()
ws.Paste()
# ws.CutCopyMode = False
os.system("echo off | clip") # 清空剪切板
ws.Shapes(1).Copy()
time.sleep(3) # 此处必须sleep 3秒以上，否则访问剪贴板会出错
img = ImageGrab.grabclipboard()
img.save(file_path + "\\" + ex_file_name + ".png")
wb.Close(SaveChanges=False)
xcl.Quit()
# 准备邮件数据，发送邮件
msg = MIMEMultipart()
msg['From'] = sender_name
msg['To'] = ";".join(to_list[company]) # 此处正式上线时改为正式的收件人
msg['Cc'] = ";".join(cc_list[company]) # 此处正式上线时改为正式的抄送人
msg['Subject'] = ex_file_name
message_sign = '<hr />Best regards<br/>' + bp_name[sender_name] + '/营销财管部 财务BP<br/>' \
'深圳市优博讯科技股份有限公司（股票代码：300531）<br/>' \
'UROVO TECHNOLOGY CO., LTD. (Stock Code: 300531.SZ)<br/>' \
'深圳市南山区学府路63号高新区联合总部大厦36-37楼<br/>' \
'Floor 36-37, Hi-tech Zone Union Tower,No.63 Xuefu Road, Nanshan District, Shenzhen,' \
'Guangdong,<br/>' \
'ChinaTel：+86-755-86186300,<br/>' \
'Web：http://www.urovo.com<br/>' \
'------------------------------------<br/>' \
'本邮件包含信息归优博讯所有,优博讯对该邮件拥有所有权利。请收件人注意保密,未经发件人书面许可,不得向任何第三方组织和个人透露本邮件所含全部或部分信息。<br/>' \
'CONFIDENTIALITY NOTICE. This message is intended exclusively for the named addressee and ' \
'may contain confidential information. Unless you are the named addressee (or authorised ' \
'to receive for the addressee) you may not copy, use or disclose this message. If this ' \
'e-mail was sent to you by mistake please notify the sender immediately and delete this ' \
'e-mail. '
message = salesman[
company] + ':<br/><p>您好！<br />截至' + deadline + '， 您名下 ' + company + ' 客户与我公司未结算的货款总额 RMB ' + str(
format(ar_end_balance,
",")) + '元，' + firstday + '至' + deadline + '期间，与我公司的交易总额：RMB ' + str(
format(sales_amount, ",")) + '元， 具体明细如下。敬请尽快确认，如有问题请与财务中心 ' + bp_name[
sender_name] + ' 联系，谢谢~</p><p><img src="cid:testimage" ' \
'alt="testimage"></p>' + message_sign
msg.attach(MIMEText(message, 'html', 'utf-8'))
# 向邮件正文添加图片
img_file = MIMEImage(open(file_path + "\\" + ex_file_name + ".png", 'rb').read()) # ’rb‘不能少，否则会报编码错误
img_file.add_header('Content-ID', 'testimage') # 邮件正文中需要用img标签来引用这个Content-ID，图片才能显示在正文中
msg.attach(img_file)
# 为邮件添加表格文件做附件
xlsx_file = MIMEApplication(open(full_path, 'rb').read())
xlsx_file['Content-type'] = 'application/octet-stream'
xlsx_file.add_header('Content-Disposition', 'attachment', filename=Header(file_name,
'utf-8').encode()) # 添加到header信息，此处filename必须用Header编码，不然会出现乱码
msg.attach(xlsx_file)
# 正式上线时修改为正式的收件人和抄送人
# 此处sender_name参数的值必须包含发件人地址，否则会报错
smtp.sendmail(sender_name, to_list[company] + cc_list[company], msg.as_string())
smtp.quit()
info_string.set("状态：对账邮件已全部发送完成。")
messagebox.showinfo('发送成功', '邮件发送成功，请登录邮箱查看已发送邮件')
except Exception as e:
messagebox.showerror('错误', e)
btn_send = Button(l_frame_1, text='发送邮件', command=btn_send_click)
btn_send.grid(row=4, column=1, padx=10, pady=10)
root.mainloop()
weekly_report_data3.py
企业微信中，检查未提交周报的人员，并用企业微信群机器人在群里面发消息提示
import datetime
import json
import time
import requests
user_ids = [
"803999",
"804044",
"804155",
"WuJiaLi",
"LiDi"
]
users = {
"803999": "曾祖琪",
"804044": "杨智森",
"804155": "王博",
"WuJiaLi": "吴佳丽",
"LiDi": "李堤"
}
report_names = []
unreport_names = []
mention_list = []
# 获取上周二时间戳
def getLastTuesday():
tuesday = datetime.date.today() # 获取今天的日期
one_day = datetime.timedelta(days=1) # 获取日期单位增量：一天
tuesday -= (7 * one_day) # 将今天的日期减去7天，得到上周的今天
# 以下代码，将上周的今天每次减1天，直到monday.weekday()为0为止。monday.weekday()是获取星期几，周一到周日依次是0到6
if tuesday.day == 0:
tuesday += one_day
else:
while tuesday.weekday() != 1:
tuesday -= one_day
# 按格式输出字符串，可以理解为将日期格式转化为字符串格式
# print(monday)
return time.mktime((tuesday.year, tuesday.month, tuesday.day, 0, 0, 0, 0, 0, 0))
# 获取上周五时间戳
def getLastFriday():
friday = datetime.date.today()
one_day = datetime.timedelta(days=1)
friday -= (7 * one_day)
if friday.weekday() > 4:
while friday.weekday() != 4:
friday -= one_day
else:
while friday.weekday() != 4:
friday += one_day
# return datetime.datetime.strftime(friday, "%Y-%m-%d")
print(friday)
return time.mktime((friday.year, friday.month, friday.day, 0, 0, 0, 0, 0, 0))
# 获取明天时间戳
def getNextday():
nextday = datetime.date.today()
one_day = datetime.timedelta(days=1)
nextday += one_day
# return datetime.datetime.strftime(friday, "%Y-%m-%d")
print(nextday)
return time.mktime((nextday.year, nextday.month, nextday.day, 0, 0, 0, 0, 0, 0))
# 获取上周日时间戳
def getLastSunday():
sunday = datetime.date.today()
one_day = datetime.timedelta(days=1)
sunday -= (7 * one_day)
while sunday.weekday() != 6:
sunday += one_day
# print(sunday)
return time.mktime((sunday.year, sunday.month, sunday.day, 0, 0, 0, 0, 0, 0))
corID = "abcde"
corSecret = "fghijk"
headers = {"Content-Type": "text/html;charset=utf-8"}
# get方法访问网页链接
url_getToken = "https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=" + corID + "&corpsecret=" + corSecret
r = requests.get(url_getToken, headers=headers)
# print(r.text)
access_token = json.loads(r.text)["access_token"]
t1 = getLastFriday()
t2 = getNextday()
# t1 = time.mktime(time.strptime("2023-3-20","%Y-%m-%d"))
#t2 = time.mktime(time.strptime("2023-4-30", "%Y-%m-%d"))
print(t1)
print(t2)
params = {
"starttime": int(t1),
"endtime": int(t2),
"cursor": 0,
"limit": 10,
"filters": [{
"key": "creator",
"value": ""
},
{
"key": "template_id",
"value": "3TkagsJtKMEtA6C2zBZZcYmF3N1TXVUPg1J5ufFu"
}]
}
url_report_list = "https://qyapi.weixin.qq.com/cgi-bin/oa/journal/get_record_list?access_token=" + access_token
for u in user_ids:
params["filters"][0]["value"] = u
# print(params)
r = requests.post(url_report_list, json=params)
#print(r.text)
record = json.loads(r.text)
journaluuid_list = record["journaluuid_list"]
if journaluuid_list:
report_names.append(users[u])
else:
unreport_names.append(users[u])
mention_list.append(u)
msg = ""
if unreport_names:
msg = "旺仔注意到这些同事还没提交周报，请大家抓紧时间提交哦：" + ",".join(unreport_names)
else:
msg = "旺仔报告：上周的周报大家已全部提交完成，给优秀的自己点个赞哦！"
mention_list.append("@all")
print(report_names)
print(unreport_names)
print(mention_list)
print(msg)
json_data = {
"msgtype": "text",
"text": {
"content": msg,
"mentioned_list": mention_list
}
}
response = requests.post(
'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=' + key,
headers=headers, json=json_data)
win32_print.py
# 这是为了方便批量打印报关单，使用本程序前需要先设置好电脑的默认打印机，并且报关单文件名要包含“报关单”
import os
import time
import tkinter
from tkinter import messagebox
import win32api
from tkinter import *
import tkinter.filedialog
import win32timezone
root = Tk()
root.title("批量打印报关单工具")
root.wm_geometry('430x50+500+250')
root.attributes('-alpha', 0.9)
# root.config(background="black")
root.resizable(False, False)
label_1 = Label(root, text="请选择报关单所在目录：")
text_box = Entry(root, bd=2)
label_1.grid(row=0, column=0, padx=5, pady=10)
text_box.grid(row=0, column=1, padx=5, pady=10)
def btn_view_click():
text_box.delete(0, END)
text_box.insert(0, tkinter.filedialog.askdirectory())
btn_view = Button(root, text='浏览...', command=btn_view_click)
btn_view.grid(row=0, column=2, padx=5, pady=10)
def btn_print_click():
file_path_1 = text_box.get()
file_list_1 = os.listdir(file_path_1)
printed_file = []
unprinted_file = []
for file in file_list_1:
found_result = False
sub_path = file_path_1 + "/" + file
if os.path.isdir(sub_path):
file_list_2 = os.listdir(sub_path)
for sub_file in file_list_2:
if "报关单" in sub_file and ((".pdf" in sub_file) or (".jpg" in sub_file) or (".png" in sub_file)):
found_result = True
print(sub_path + "/" + sub_file)
win32api.ShellExecute(
0,
"print", # 这个参数为open可以调用默认程序打开指定文件，为
sub_path + "/" + sub_file,
# If this is None, the default printer will
# be used anyway.
# '/d:"%s"' % win32print.GetDefaultPrinter(), 表示使用默认打印机
None, # None也是使用默认打印机
".",
0
)
time.sleep(4)
if found_result:
printed_file.append(file)
else:
unprinted_file.append(file)
messagebox.showinfo('打印完成', '共' + str(len(printed_file)) + '份文件打印成功，请查看日志')
s = "\n"
log_file = open('报关单打印日志.txt', 'w')
log_file.write("已打印的清单如下：\n")
log_file.write(s.join(printed_file))
log_file.write("\n")
log_file.write("未打印的清单如下：\n")
if unprinted_file:
log_file.write(s.join(unprinted_file))
else:
log_file.write("不存在未打印的清单")
btn_print = Button(root, text='开始打印', command=btn_print_click)
btn_print.grid(row=0, column=3, padx=5, pady=10)
root.mainloop()
标签：10,python,max,代码,搜集,text,btn,com,row From： https://www.cnblogs.com/dige1993/p/17298736.html