该需求背景是有一个应收逾期表格，里面有很多部门的数据，要把表格按部门拆分成每个部门单独一个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()

程序运行效果：

python的tkinter读excel实例 python tkinter 表格_python