该需求背景是有一个应收逾期表格，里面有很多部门的数据，要把表格按部门拆分成每个部门单独一个EXCEL表格文件，并把拆分出来的各部门文件邮件发送给各部门领导，涉及到的python知识点想当多，大致包括：
# python 图形界面程序tkinter的使用
# python messagebox的使用
# python 进度条ProgressBar的使用
# openpyxl 对Excel表格的操作
# python 正则表达式的使用
# python发 送带附件的邮件
# openpyxl 知道表格列号，获取列标字母的方法get_column_letter(int)
# openpyxl 获取表格有数据的区域的最大行号的方法
# openpyxl 遍历表格指定区域
# openpyxl 合并单元格
# openpyxl 新建、保存表格
# openpyxl 设置单元格数值格式
# openpyxl 遍历表格所有有数据的区域，并调整字体、字号、边框、列宽
# python 将小数格式调整为保留两位小数的百分数形式
# 这个程序是用于周钊铌每周拆分应收逾期表格文件，并发送邮件给各部门# 营销中心各二级部门的数据不拆分成单独文件# 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]"],
"营销中心-渠道部": ["[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]", "[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 = "123456"
sender_name = "测试账号<[email protected]>" # sender_name用于指示发件人名称，该名称一定要包含发件人邮箱地址，否则程序会报错
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()
test标签：urovo,tkinter,营销中心,python,dept,窗体,new,com,row From： https://www.cnblogs.com/dige1993/p/16808209.html