openpyxl处理excel表格文件；python窗体应用程序tkinter的使用；python进度条ProgressBar
该需求背景是有一个应收逾期表格，里面有很多部门的数据，要把表格按部门拆分成每个部门单独一个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.10.19 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 = ["营销中心-华南区", "营销中心-西部区"] key_customer_dept = ["战略大客户部","华东区大客户部","金融大客户部","金融及大客户部"] to_list = { "营销中心-华南区": ["abc@123.com"], "营销中心-西部区": ["abc@123.com"],"金融及大客户部": ["abc@123.com"],"海外事业部": ["abc@123.com"], } name_list = { "营销中心-华南区": "周总", "营销中心-西部区": "胡总","金融及大客户部": "邓总","海外事业部": "王洋总", "营销中心": "陈阳总" } cc_list = { "营销中心-华南区": ["abc@123.com", "def@123.com"], "营销中心-西部区": ["abc@123.com", "def@123.com"],"金融及大客户部": ["abc@123.com", "def@123"],"营销中心": ["abc@123.com", "abc@def.com"] } 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') 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.askopenfilename()) btn_view = Button(l_frame_1, text='浏览...', command=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') entry_pwd = Entry(l_frame_2, bd=2, show='*', state='readonly') 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 = "abc@456.com" sender_pwd = "123456" sender_name = "ABC<abc@456.com>" 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_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/>' \ '李堤 营销财管部 财务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 = 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 = 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) # 遍历工作表数据 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) elif key in key_customer_dept: 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：超账期", "逾期期间", "", "", "", "", "9-10月份回款额（含售后）", "9月份回款额（含售后）", "10月W1", "10月W2", "10月W3","10月回款合计"] header2 = ["", "", "", "", "", "", "","", "1-30天", "31-90天", "91-180天", "181-365天", "1年以上"] 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:H2") new_ws.merge_cells("I1:M1") new_ws.merge_cells("N1:N2") new_ws.merge_cells("O1:O2") new_ws.merge_cells("P1:P2") new_ws.merge_cells("Q1:Q2") new_ws.merge_cells("R1:R2") new_ws.merge_cells("S1:S2") for row3 in new_ws["A1:S2"]: 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 row_temp in sheet1["A" + str(cell.row) + ":S" + str(cell.row)]: for cell2 in row_temp: 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) for row4 in new_ws["E" + str(max_row_B2 + 1) + ":S" + str(max_row_B2 + 1)]: for cell5 in row4: # get_column_letter，已知列号，获取列标字母， 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["E3:E" + str(max_row_B2)]: for cell6 in row5: # 有些单元格为空，Value不能直接做加法，空值做加法会报错，所以做个判断 if cell6.value: print(dept) ar_balance[dept] += cell6.value # 累加E列的逾期金额 for row6 in new_ws["F3:F" + 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, 6).value = '=F' + str(max_row_B2 + 1) + '/E' + str(max_row_B2 + 1) new_ws.cell(max_row_B2 + 2, 6).number_format = "0.00%" # 百分号格式 new_ws.cell(max_row_B2 + 2, 6).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()
程序运行效果：
