学生资助辅助工具python开发

上面是完成后的效果图

下面是具体代码

import xlwings as xw
from xlwings.utils import rgb_to_int
from tkinter import *
from tkinter.filedialog import asksaveasfilename, askopenfilename
from tkinter import messagebox
from tkinter.ttk import *
import re
import datetime


class myxszz():
    def __init__(self):
        self.app = None
        self.wb = None
        self.dataSh = None
        self.gsSh = None
        self.faSh = None
        # self.sysConfig = None
        self.xd = None
        self.top = None
        self.my_year = datetime.datetime.now().year
        self.my_month = datetime.datetime.now().month
        self.my_day = datetime.datetime.now().day

        self.myGUI()

    def myGUI(self):
        self.root = Tk()
        self.root.title('资助工具1.0')
        self.root.geometry('600x70-100+200')
        self.root.resizable(False, False)
        self.root.attributes('-toolwindow', True)
        self.root.attributes('-topmost', True)
        # self.root.attributes('-alpha', 0.95)
        # self.root.overrideredirect(True)
        self.root.rowconfigure(0, weight=1)
        for i in range(7):
            self.root.columnconfigure(i, weight=1)
        self.but1 = Button(self.root, text='新建项目', state='normal', command=self.addProject)
        self.but1.grid(row=0, column=0, pady=2, sticky=N + S + W + E)
        self.but11 = Button(self.root, text='打开项目', state='normal', command=self.openProject)
        self.but11.grid(row=0, column=1, pady=2, sticky=N + S + W + E)
        self.but2 = Button(self.root, text='数据校验', state='disabled', command=lambda: self.top_win(self.dataCheck))
        self.but2.grid(row=0, column=2, pady=2, sticky=N + S + W + E)
        self.but3 = Button(self.root, text='生成公示', state='disabled',
                           command=lambda: self.top_win(self.makeProclamation))
        self.but3.grid(row=0, column=3, pady=2, sticky=N + S + W + E)
        self.but4 = Button(self.root, text='生成发放', state='disabled', command=lambda: self.top_win(self.makeTabel))
        self.but4.grid(row=0, column=4, pady=2, sticky=N + S + W + E)
        self.but5 = Button(self.root, text='退出当前项目', state='disabled', command=self.colseProject)
        self.but5.grid(row=0, column=5, pady=2, sticky=N + S + W + E)
        self.but6 = Button(self.root, text='退出工具', command=self.colseWin)
        self.but6.grid(row=0, column=6, pady=2, sticky=N + S + W + E)
        self.lab = Label(self.root, text='工作进度:')
        self.lab.grid(row=1, column=0, padx=2, pady=2, sticky=N + S + W + E)
        self.pb = Progressbar(self.root, mode='determinate', orient=HORIZONTAL)

        self.pb['value'] = 0
        self.pb.grid(row=1, column=1, columnspan=6, padx=2, pady=2, sticky=N + S + W + E)
        self.root.protocol('WM_DELETE_WINDOW', self.colseWin)
        self.root.mainloop()

    '''###################询问学段######################'''

    def myfun(self, myFun):
        self.top.withdraw()
        self.xd = self.rax.get()
        myFun()
        self.top.destroy()

    def myClose(self):
        self.xd = None
        self.top.destroy()

    def top_win(self, myF):
        self.top = Toplevel(self.root)
        self.top.title('请选择学段')
        self.top.geometry(f"200x100+{self.root.winfo_rootx() + 30}+{self.root.winfo_rooty() + 30}")
        self.top.resizable(False, False)
        self.top.attributes('-toolwindow', True)
        self.top.attributes('-topmost', True)
        # self.root.attributes('-alpha', 0.95)
        self.rax = StringVar()  # 配合Radiobutton的variable参数使用
        self.rax.set("学前")
        self.top.rowconfigure(0, weight=1)
        self.top.rowconfigure(1, weight=1)
        self.top.rowconfigure(2, weight=1)
        self.top.columnconfigure(0, weight=1)
        self.top.columnconfigure(1, weight=1)
        rb1 = Radiobutton(self.top, text="学前", variable=self.rax, value="学前")
        rb2 = Radiobutton(self.top, text="义务", variable=self.rax, value="义务")
        rb3 = Radiobutton(self.top, text="高中", variable=self.rax, value="高中")
        rb4 = Radiobutton(self.top, text="中职", variable=self.rax, value="中职")
        btn = Button(self.top, text='确定', command=lambda: self.myfun(myF))
        rb1.grid(row=0, column=0, padx=2, pady=2, sticky=N + S + W + E)
        rb2.grid(row=0, column=1, padx=2, pady=2, sticky=N + S + W + E)
        rb3.grid(row=1, column=0, padx=2, pady=2, sticky=N + S + W + E)
        rb4.grid(row=1, column=1, padx=2, pady=2, sticky=N + S + W + E)
        btn.grid(row=2, column=0, columnspan=2, padx=2, pady=4)
        self.top.protocol('WM_DELETE_WINDOW', self.myClose)
        self.top.grab_set()

    '''#################新建工程#################'''

    def addProject(self):
        self.but1.config(state='disable')
        self.but11.config(state='disable')
        self.but2.config(state='disable')
        self.but3.config(state='disable')
        self.but4.config(state='disable')
        self.but5.config(state='disable')
        self.but6.config(state='disable')
        wbName = asksaveasfilename(defaultextension='.xls', filetypes=[('excel文件', '.xls')], title='新建资助项目')
        if wbName:
            self.pb['maximum'] = 100
            self.lab.config(text='正在新建项目:')
            self.pb_update(10)
            self.app = xw.App(visible=True, add_book=False)
            # self.app.display_alerts = False
            # self.app.screen_updating = False
            self.pb_update(20)
            self.wb = self.app.books.add()
            self.pb_update(30)
            self.dataSh = self.wb.sheets.add(name='数据源')
            self.gsSh = self.wb.sheets.add(name='公示表', after='数据源')
            self.faSh = self.wb.sheets.add(name='发放表', after='公示表')
            self.pb_update(40)
            # self.sysConfig = self.wb.sheets.add(name='配置')
            self.dataSh.select()
            try:
                self.wb.sheets['Sheet1'].delete()
            except Exception:
                pass

            newTitle = ["序号(必填)", "姓  名", "性别", "学段", "年级", "班级", "身份证号", "贫困类型", "是否寄宿", "补助金额(元)", "家庭住址", "银行卡号",
                        "家长姓名", "联系电话"]
            colWidth = [5.77, 7, 3.7, 7, 7.13, 9, 18.67, 7.9, 7.9, 9.75, 24, 18.9, 7.7, 13]
            self.gsSh.visible = False
            self.faSh.visible = False
            newTitle = ["序号(必填)", "姓  名", "性别", "学段", "年级", "班级", "身份证号", "贫困类型", "是否寄宿", "补助金额(元)", "家庭住址", "银行卡号",
                        "家长姓名", "联系电话"]
            colWidth = [5.77, 7, 3.7, 7, 7.13, 9, 18.67, 7.9, 7.9, 9.75, 24, 18.9, 7.7, 13]

            for index, item in enumerate(newTitle):
                self.dataSh.range(1, index + 1).column_width = colWidth[index]
                self.dataSh.range(1, index + 1).value = newTitle[index]
            self.pb_update(50)
            area = self.dataSh.range('A1:N10000')
            area.row_height = 30
            area.wrap_text = True
            area.number_format = '@'
            area.api.HorizontalAlignment = -4108
            area.api.VerticalAlignment = -4108
            self.pb_update(70)
            for i in range(7, 13):
                area.api.Borders(i).LineStyle = 1
                area.api.Borders(i).Weight = 2
                area.api.Borders(i).Color = rgb_to_int((0, 0, 0))
            self.pb_update(80)
            self.dataSh.range('A1:N1').color = (255, 218, 185)
            self.dataSh.range('C1').api.AddComment('限填:“男”/"女"')
            self.dataSh.range('D1').api.AddComment('限填:“学前"/"小学"/"初中"/"高中"/"中职"')
            self.dataSh.range('H1').api.AddComment('限填:"脱贫户子女"/"低保"/"残疾"/"监测户子女"/"其他"')
            self.dataSh.range('I1').api.AddComment('限填:"是"/"否"')
            self.pb_update(90)
            self.wb.api.Protect(Password='zk@2461761', Structure=True, Windows=True)
            # self.wb.api.Password = 'zk@2461761'
            self.wb.save(wbName)
            # self.app.display_alerts = True
            # self.app.screen_updating = True
            self.pb['value'] = 100
            self.lab.config(text='新建项目完成!')
            self.but1.config(state='disable')
            self.but11.config(state='disable')
            self.but2.config(state='normal')
            self.but3.config(state='normal')
            self.but4.config(state='normal')
            self.but5.config(state='normal')
            self.but6.config(state='normal')
        else:
            self.but1.config(state='disable')
            self.but11.config(state='disable')
            self.but2.config(state='normal')
            self.but3.config(state='normal')
            self.but4.config(state='normal')
            self.but5.config(state='normal')
            self.but6.config(state='normal')

    def openProject(self):
        self.but1.config(state='disable')
        self.but11.config(state='disable')
        self.but2.config(state='disable')
        self.but3.config(state='disable')
        self.but4.config(state='disable')
        self.but5.config(state='disable')
        self.but6.config(state='disable')
        wbName = askopenfilename(defaultextension='.xls', filetypes=[('excel文件', '.xls')], title='打开资助项目')
        if wbName:
            try:
                self.pb['maximum'] = 100
                self.lab.config(text='正在打开项目:')
                self.pb_update(10)
                self.app = xw.App(visible=True, add_book=False)
                self.pb_update(20)
                self.wb = self.app.books.open(fullname=wbName)
                self.pb_update(50)
                self.dataSh = self.wb.sheets['数据源']
                self.pb_update(70)
                self.gsSh = self.wb.sheets['公示表']
                self.pb_update(90)
                self.faSh = self.wb.sheets['发放表']
                self.pb_update(100)
                self.dataSh.select()
                self.lab.config(text='项目打开成功!')
                self.but1.config(state='disable')
                self.but11.config(state='disable')
                self.but2.config(state='normal')
                self.but3.config(state='normal')
                self.but4.config(state='normal')
                self.but5.config(state='normal')
                self.but6.config(state='normal')

            except Exception as e:
                self.lab.config(text='项目打开失败!')
                messagebox.showerror(title='失败', message='非资助项目文件')
                self.app.quit()
                self.colseBefor()
                self.but1.config(state='normal')
                self.but11.config(state='normal')
                self.but2.config(state='disable')
                self.but3.config(state='disable')
                self.but4.config(state='disable')
                self.but5.config(state='disable')
                self.but6.config(state='normal')
        else:
            self.but1.config(state='normal')
            self.but11.config(state='normal')
            self.but2.config(state='disable')
            self.but3.config(state='disable')
            self.but4.config(state='disable')
            self.but5.config(state='disable')
            self.but6.config(state='normal')

    '''#####################数据校验#############'''

    def dataCheck(self):
        try:
            self.but1.config(state='disable')
            self.but11.config(state='disable')
            self.but2.config(state='disable')
            self.but3.config(state='disable')
            self.but4.config(state='disable')
            self.but5.config(state='disable')
            self.but6.config(state='disable')
            self.dataSh.select()
            allData = self.dataSh.range("A1:N1").expand('down')
            maxRow = allData.shape[0]

            if maxRow < 2:
                messagebox.showwarning(title="警告", message="你的数据源里没有数据,请检查!!")
                self.but1.config(state='disable')
                self.but11.config(state='disable')
                self.but2.config(state='normal')
                self.but3.config(state='normal')
                self.but4.config(state='normal')
                self.but5.config(state='normal')
                self.but6.config(state='normal')
                return

            myDataRng = self.dataSh.range("A2:N2").expand('down')
            myDataRng.number_format = '@'
            myDataRng.font.color = (0, 0, 0)
            mydata = allData.value[1:]
            if mydata is None:
                self.but1.config(state='disable')
                self.but11.config(state='disable')
                self.but2.config(state='normal')
                self.but3.config(state='normal')
                self.but4.config(state='normal')
                self.but5.config(state='normal')
                self.but6.config(state='normal')
                return
            data_count = len(mydata)
            self.pb['maximum'] = data_count * 2
            self.lab.config(text='正在数据校验:')
            pb_sum = 0
            id_rng = self.dataSh.range("G2").expand('down').value
            bank_rng = self.dataSh.range('L2').expand('down').value
            r = r'^([1-9]\d{5}[12]\d{3}(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01])\d{3}[0-9X])$'

            errs = []
            for index, item in enumerate(mydata):
                # 检查性别
                if str(item[2]) not in ['男', '女']:
                    errs.append([index + 2, 3, '性别限填:”男“/”女“'])
                # 检查学段
                if str(item[3]) != self.xd:
                    if self.xd == '义务':
                        if str(item[3]) not in ['小学', '初中']:
                            errs.append([index + 2, 4, '学段限填:"小学"/"初中"'])
                    else:
                        errs.append([index + 2, 4, f'学段限填:"{self.xd}"'])
                # 年级检查
                if self.xd == '学前':
                    if str(item[4]) not in ['大班', '中班', '小班']:
                        errs.append([index + 2, 5, '年级限填:”大班“/”中班“/"小班"'])
                if self.xd == '义务':
                    if str(item[4]) not in ['一年级', '二年级', '三年级', '四年级', '五年级', '六年级', '七年级', '八年级', '九年级']:
                        errs.append(
                            [index + 2, 5, "年级限填:'一年级', '二年级', '三年级', '四年级', '五年级', '六年级', '七年级', '八年级', '九年级'"])
                if self.xd == '高中':
                    if str(item[4]) not in ['高一', '高二', '高三']:
                        errs.append([index + 2, 5, '年级限填:”高一“/”高二“/"高三"'])
                if self.xd == '中职':
                    if str(item[4]) not in ['中职一', '中职二', '中职三']:
                        errs.append([index + 2, 5, '年级限填:”中职一“/”中职二“/"中职三"'])
                # 身份证号校验
                if re.match(r, str(item[6]).strip()):
                    if self.check_id_length(str(item[6]).strip()):
                        temdata = self.check_id_data(str(item[6]).strip())
                        if temdata:
                            if temdata[1] != str(item[2]):
                                errs.append([index + 2, 7, '身份证号与性别不符'])
                            else:
                                if index < len(mydata) - 1:
                                    try:
                                        row_id = id_rng.index(item[6], index + 1)
                                        errs.append([index + 2, 7, f'与第{row_id + 2}行身份证号重复。'])
                                    except Exception:
                                        pass
                        else:
                            errs.append([index + 2, 7, '身份证号校验错误!'])
                    else:
                        errs.append([index + 2, 7, '身份证号长度错误!'])
                else:
                    errs.append([index + 2, 7, '身份证号有误,请检查!'])

                # 贫困类型检查"脱贫户子女"/"低保"/"残疾"/"监测户子女"/"其他"
                if str(item[7]) not in ['脱贫户子女', '低保', '残疾', '监测户子女', '其他']:
                    errs.append([index + 2, 8, "贫困类型限填:'脱贫户子女', '低保','残疾','监测户子女','其他'"])
                # 是否寄宿
                if str(item[8]) not in ['是', '否']:
                    errs.append([index + 2, 9, "是否寄宿限填:'是'/'否'"])
                # 补助金额
                moneyinfo = ['小学是500.0', '小学否250.0', '初中是625.0', '初中否312.5', '学前是375.0',
                             '学前否375.0', '中职是750.0', '中职否750.0', '中职是1250.0', '中职否1250.0',
                             '高中是750.0', '高中否750.0', '高中是1250.0', '高中否1250.0']
                try:
                    item[9] = round(float(item[9]), 1)
                    if f'{str(item[3])}{str(item[8])}{str(item[9])}' not in moneyinfo:
                        errs.append([index + 2, 10, '补助金额与学段、是否寄宿不匹配。'])
                except Exception:
                    errs.append([index + 2, 10, '补助金额有误,请检查!'])

                # 银行卡号检查
                if len(str(item[11]).strip()) != 19:
                    errs.append([index + 2, 12, '银行卡号长度不正确,请检查!'])
                else:
                    if not str(item[11]).isdigit():
                        errs.append([index + 2, 12, '银行卡号不能存在数字外其他符号!'])
                    else:
                        if index < len(mydata) - 1:
                            try:
                                row_id = bank_rng.index(item[11], index + 1)
                                errs.append([index + 2, 12, f'与第{row_id + 2}行银行卡号重复。'])
                            except Exception:
                                pass
                # 电话话吗检查
                try:
                    item[13] = int(item[13])
                except Exception:
                    pass
                if len(str(item[13])) != 11:
                    errs.append([index + 2, 14, '电话码长度有误,请检查!'])
                else:
                    if not str(item[13]).isdigit():
                        errs.append([index + 2, 14, '电话号码不能存在数字外其他符号!'])
                pb_sum += 1
                self.pb_update(pb_sum)

            self.dataSh.range('A2:N10000').api.ClearComments()
            self.dataSh.range('A2:N10000').color = None
            self.pb['maximum'] = data_count + len(errs)
            for err in errs:
                self.dataSh.range(err[0], err[1]).color = (222, 0, 0)
                self.dataSh.range(err[0], err[1]).api.AddComment(err[2])
                pb_sum += 1
                self.pb_update(pb_sum)
            self.lab.config(text='数据校验完成!')
            messagebox.showinfo(title='提示', message='数据校验完成!')
            self.but1.config(state='disable')
            self.but11.config(state='disable')
            self.but2.config(state='normal')
            self.but3.config(state='normal')
            self.but4.config(state='normal')
            self.but5.config(state='normal')
            self.but6.config(state='normal')
        except Exception as e:
            messagebox.showerror(title='错误!', message=f'工作簿非法关闭:{e}')
            self.but1.config(state='normal')
            self.but11.config(state='normal')
            self.but2.config(state='disable')
            self.but3.config(state='disable')
            self.but4.config(state='disable')
            self.but5.config(state='disable')
            self.but6.config(state='normal')

    '''#################制作公示#################'''

    def makeProclamation(self):
        try:
            self.but1.config(state='disable')
            self.but11.config(state='disable')
            self.but2.config(state='disable')
            self.but3.config(state='disable')
            self.but4.config(state='disable')
            self.but5.config(state='disable')
            self.but6.config(state='disable')
            gsData = self.dataSh.range("A1:N1").expand('down')
            maxRow = gsData.shape[0]
            if maxRow < 2:
                messagebox.showwarning(title="警告", message="你的数据源里没有数据,请检查!!")
                self.but1.config(state='disable')
                self.but11.config(state='disable')
                self.but2.config(state='normal')
                self.but3.config(state='normal')
                self.but4.config(state='normal')
                self.but5.config(state='normal')
                self.but6.config(state='normal')
                return
            self.pb['maximum'] = 100
            self.lab.config(text='正在制作公示:')
            self.wb.api.Unprotect(Password='zk@2461761')
            self.gsSh.visible = True
            self.gsSh.select()
            self.gsSh['A:O'].delete()
            self.pb_update(5)
            self.gsSh.cells.api.WrapText = True
            self.gsSh.cells.api.NumberFormat = "@"
            self.gsSh.cells.row_height = 18
            self.gsSh.cells.api.Font.Size = 10
            self.pb_update(10)
            pb_num = 10
            columnW = {"A1": 5, "B1": 10, "C1": 8, "D1": 10, "E1": 10, "F1": 15, "G1": 12, "H1": 13}
            for x, y in columnW.items():
                self.gsSh.range(x).column_width = y
                pb_num += 1
                self.pb_update(pb_num)

            rowH = [80, 40, 40, 80, 40, 40, 40, 150, 150, 40, 40, 30, 30]
            for inx, val in enumerate(rowH):
                self.gsSh.range(f"A{inx + 1}").row_height = val
                pb_num += 1
                self.pb_update(pb_num)

            mergeList = ["A1:H1", "A2:B2", "C2:H2", "A3:B3", "C3:D3", "E3:F3", "C3:D3", "G3:H3", "A4:B4", "C4:H4",
                         "A5:B5",
                         "C5:D5", "E5:F5", "G5:H5", "A6:B6", "C6:D6", "E6:F6", "G6:H6", "A7:B7", "C7:H7", "A8:B8",
                         "C8:H8",
                         "A9:B9", "C9:H9", "A10:B10", "C10:H10", "A11:H11", "A12:H12"]
            for xx in mergeList:
                self.gsSh.range(xx).api.Merge()
                pb_num += 1
                self.pb_update(pb_num)
            jiJie = ""
            if self.my_month >= 9:
                jiJie = f"{self.my_year}年秋季"
            else:
                jiJie = f"{self.my_year}年春季"
            moneyBz = {"学前": "375元/生/学期",
                       "义务": "寄宿制:小学:500/生/学期      中学:625/生/学期       非寄宿制:小学:250/生/学期     中学:312.5/生/学期",
                       "高中": "特别困难:1250元/生/学期    750元/生/学期", "中职": "特别困难:1250元/生/学期    750元/生/学期"}
            self.gsSh.range("A1").value = f"三原县{jiJie}{self.xd}教育阶段家庭经济困难学生资助公示表"
            self.gsSh.range("A2").value = "学校名称"
            self.gsSh.range("A3").value = "发放时段"
            self.gsSh.range("C3").value = jiJie
            self.gsSh.range("E3").value = "公示时间"
            self.gsSh.range("A4").value = "资助标准"
            self.gsSh.range("C4").value = moneyBz[self.xd]
            self.gsSh.range("A5").value = "应发人数"
            self.gsSh.range("C5").value = f"{maxRow - 1}人"
            self.gsSh.range("E5").value = "实发人数"
            self.gsSh.range("G5").value = f"{maxRow - 1}人"
            self.gsSh.range("A6").value = "应发金额"
            self.pb_update(70)
            moneyRng = self.dataSh.range(f"J2:J{maxRow}").value
            try:
                moneySum = [float(x) for x in moneyRng]
                moneySum = sum(moneySum)
            except Exception:
                self.wb.api.Protect(Password='zk@2461761', Structure=True, Windows=True)
                self.lab.config(text='制作失败!')
                messagebox.showwarning(title='警告', message='请查看补助金额数据是否异常!')
                self.but1.config(state='disable')
                self.but11.config(state='disable')
                self.but2.config(state='normal')
                self.but3.config(state='normal')
                self.but4.config(state='normal')
                self.but5.config(state='normal')
                self.but6.config(state='normal')
                self.dataSh.select()
                return
            self.gsSh.range("C6").value = moneySum
            self.gsSh.range("E6").value = "实发金额"
            self.gsSh.range("G6").value = moneySum
            self.gsSh.range("A7").value = "监督电话"
            self.gsSh.range("C7").value = "市资助中心:33328288  县资助中心:32368700"
            self.gsSh.range("A8").value = "评审领导小组成员审核"
            self.gsSh.range("A9").value = "县(市)区级学生资助管理中心审核"
            self.gsSh.range("A10").value = "备  注"
            self.gsSh.range("C10").value = f"公示表后附:{jiJie}{self.xd}教育阶段国家助学金受助花名册"
            self.gsSh.range("A11").value = f"{jiJie}{self.xd}教育阶段国家助学金受助花名册"
            self.gsSh.range("A12").value = "学校名称:(盖章)        资助中心:(盖章)           年    月    日"
            self.pb_update(80)
            huaMinCe1 = self.dataSh.range("A1:E1").expand("down").value
            huaMinCe2 = self.dataSh.range("H1:J1").expand("down").value
            self.gsSh.range("A13").value = huaMinCe1
            self.gsSh.range("F13").value = huaMinCe2
            self.gsSh.range("A13").value = "序号"
            self.gsSh.range("A1:H10000").api.HorizontalAlignment = -4108
            self.gsSh.range("A1:H10000").api.VerticalAlignment = -4108
            self.gsSh.range("A1:H1").api.Font.Size = 16
            self.gsSh.range("A1:H1").api.Font.Bold = True
            self.gsSh.range("A2:H10").api.Font.Size = 15
            self.gsSh.range("C10:H10").api.Font.Size = 13
            self.gsSh.range("A11:H11").api.Font.Size = 14
            self.gsSh.range("A11:H12").api.Font.Bold = True
            self.gsSh.range("A12:H12").api.Font.Size = 12
            self.gsSh.range("A13:H10000").api.Font.Size = 10
            self.pb_update(85)

            for num in range(7, 13):
                self.gsSh.range("A2:H10").api.Borders(num).LineStyle = 1
                self.gsSh.range("A2:H10").api.Borders(num).Weight = 2
            self.pb_update(90)
            for num in range(7, 13):
                self.gsSh.range("A13:H13").expand("down").api.Borders(num).LineStyle = 1
                self.gsSh.range("A13:H13").expand("down").api.Borders(num).Weight = 2
            self.myPageSet(self.gsSh)
            self.wb.save()
            self.pb_update(100)
            self.lab.config(text='公示制作完成!')
            messagebox.showinfo(title='提示', message='公示表制作完成,请完善公示信息!')
            self.wb.api.Protect(Password='zk@2461761', Structure=True, Windows=True)
            self.but1.config(state='disable')
            self.but11.config(state='disable')
            self.but2.config(state='normal')
            self.but3.config(state='normal')
            self.but4.config(state='normal')
            self.but5.config(state='normal')
            self.but6.config(state='normal')
        except Exception as e:
            messagebox.showerror(title='错误!', message=f'工作簿非法关闭:{e}')
            self.but1.config(state='normal')
            self.but11.config(state='normal')
            self.but2.config(state='disable')
            self.but3.config(state='disable')
            self.but4.config(state='disable')
            self.but5.config(state='disable')
            self.but6.config(state='normal')

    '''#################制作发放#################'''

    def makeTabel(self):
        try:
            self.but1.config(state='disable')
            self.but11.config(state='disable')
            self.but2.config(state='disable')
            self.but3.config(state='disable')
            self.but4.config(state='disable')
            self.but5.config(state='disable')
            self.but6.config(state='disable')
            self.wb.api.Unprotect(Password='zk@2461761')
            self.faSumMoney = 0
            self.pb['maximum'] = 100
            self.lab.config(text='正在制作发放:')
            faData = self.dataSh.range("A1:N1").expand('down')
            maxRow = faData.shape[0]
            self.pb_update(5)
            if maxRow < 2:
                messagebox.showwarning(title='警告', message='数据源里没有数据,请检查!!')
                self.lab.config(text='制作失败!')
                self.pb_update(0)
                self.dataSh.select()
                self.but1.config(state='disable')
                self.but11.config(state='disable')
                self.but2.config(state='normal')
                self.but3.config(state='normal')
                self.but4.config(state='normal')
                self.but5.config(state='normal')
                self.but6.config(state='normal')
                return
            self.wb.api.Unprotect(Password='zk@2461761')
            self.faSh.visible = True
            self.faSh.select()
            self.faSh['A:O'].delete()
            self.faSh.cells.api.WrapText = True
            self.faSh.cells.api.NumberFormat = "@"
            self.faSh.cells.row_height = 25
            self.faSh.cells.api.Font.Size = 10
            self.pb_update(10)
            self.faSh.api.PageSetup.PaperSize = 9  # 设置纸张大小
            # faSh.api.PageSetup.PrintTitleRows = "$1:$3"  # 设置顶端标题
            self.faSh.api.PageSetup.Orientation = 2  # 设置页面方向 1 纵向 2横向
            self.faSh.api.PageSetup.centerHorizontally = True  # 设置水平居中
            self.faSh.api.PageSetup.LeftMargin = 18  # 设置左边距
            self.faSh.api.PageSetup.RightMargin = 18  # 设置右边距
            self.faSh.api.PageSetup.TopMargin = 60  # 设置上边距
            self.faSh.api.PageSetup.BottomMargin = 60  # 设置下边距
            self.faSh.range("A1:S10000").api.HorizontalAlignment = -4108
            self.faSh.range("A1:S10000").api.VerticalAlignment = -4108
            self.pb_update(15)
            faList = {"A1": 4, "B1": 7, "C1": 3.7, "D1": 6.5, "E1": 7.5, "F1": 4, "G1": 19, "H1": 7.5, "I1": 9,
                      "j1": 10,
                      "K1": 15, "L1": 19, "M1": 11, "N1": 9}
            for r, w in faList.items():
                self.faSh.range(r).column_width = w
            self.faSh.range("A1:N1").api.Merge()
            self.faSh.range("A2:N2").api.Merge()
            self.pb_update(20)
            faTitle1 = ""
            if self.my_month >= 9:
                faTitle1 = f"三原县{self.my_year}年秋季{self.xd}教育阶段国家助学金发放表"
            else:
                faTitle1 = f"三原县{self.my_year}年春季{self.xd}教育阶段国家助学金发放表"
            faTitle2 = "学校名称:(盖章)             学生总数:   人               享受资助人数:   人                 年    月    日"

            faTitle3 = self.dataSh.range("A1:N1").value
            faTitle3[0] = "序号"
            faTitle3.append("学生/家长签名")
            if self.xd == '学前' or self.xd == '义务':
                self.faSh.range("B1").column_width = 6
                self.faSh.range('D1').column_width = 3.7
                self.faSh.range('I1').column_width = 3.7
                self.faSh.range('O1').column_width = 9.1
                faTitle3[-1] = '学生签名'
                faTitle3.append('家长签名')
            faTitle3.pop(-3)
            faData = faData.value
            for item in faData:
                item.pop(-2)
            self.pb_update(25)
            sNum, yNum = divmod(len(faData) - 1, 13)
            pageNum = 0
            if yNum == 0:
                pageNum = sNum
            else:
                pageNum = sNum + 1
            fenData = []
            for p in range(pageNum):
                if (p == (pageNum - 1)) and (yNum != 0):
                    fenData.append(faData[(1 + p * 13):(yNum + 1 + p * 13)])
                else:
                    fenData.append(faData[(1 + p * 13):(14 + p * 13)])
            self.pb_update(30)
            dataLen = len(fenData)
            pb_num = 50 + dataLen
            self.pb['maximum'] = pb_num
            pb_s = 40
            for xPage in range(dataLen):
                startNum = 4 + xPage * 18
                # if xPage == (dataLen - 1):
                #     end1 = startNum + yNum
                # else:
                #     end1 = startNum + 13
                end1 = startNum + 13
                end2 = end1 + 1
                self.faSh.range(f"A{startNum}").value = fenData[xPage]
                xjSum = self.moneySum(fenData[xPage])
                if xjSum == "err":
                    self.lab.config(text='制作发放失败!')
                    self.pb_update(0)
                    messagebox.showwarning(title='警告', message='补助金额有误,请先检查数据!,然后再制作发放!')
                    self.but1.config(state='disable')
                    self.but11.config(state='disable')
                    self.but2.config(state='normal')
                    self.but3.config(state='normal')
                    self.but4.config(state='normal')
                    self.but5.config(state='normal')
                    self.but6.config(state='normal')
                    return
                if self.xd == '学前' or self.xd == '义务':
                    self.faSh.range(f"A{startNum - 3}:O{startNum - 3}").api.Merge()
                    self.faSh.range(f"A{startNum - 3}:O{startNum - 3}").api.Font.Size = 18
                else:
                    self.faSh.range(f"A{startNum - 3}:N{startNum - 3}").api.Merge()
                    self.faSh.range(f"A{startNum - 3}:N{startNum - 3}").api.Font.Size = 18
                self.faSh.range(f"A{startNum - 3}").row_height = 40
                self.faSh.range(f"A{startNum - 3}").value = faTitle1
                if self.xd == '学前' or self.xd == '义务':
                    self.faSh.range(f"A{startNum - 3}:O{startNum - 3}").api.Font.Bold = True
                    self.faSh.range(f"A{startNum - 2}:O{startNum - 2}").api.Merge()
                    self.faSh.range(f"A{startNum - 2}:O{startNum - 2}").api.Font.Size = 12
                    self.faSh.range(f"A{startNum - 2}:O{startNum - 2}").api.Font.Bold = True
                    self.faSh.range(f"A{startNum - 2}").value = faTitle2
                    self.faSh.range(f"A{startNum - 1}:O{startNum - 1}").value = faTitle3
                    for num in range(7, 13):
                        self.faSh.range(f"A{startNum - 1}:O{end2}").api.Borders(num).LineStyle = 1
                        self.faSh.range(f"A{startNum - 1}:O{end2}").api.Borders(num).Weight = 2
                    self.faSh.range(f"A{end1}:O{end1}").api.Merge()
                    self.faSh.range(f"A{end1}:O{end1}").value = f"本页小计:{xjSum}              大写:{self.numToBig(xjSum)}整"
                    self.faSh.range(f"A{end2}:O{end2}").api.Merge()
                    self.faSh.range(f"A{end2}").api.HorizontalAlignment = -4131
                    self.faSh.range(
                        f"A{end2}").value = "学校资助工作人员签名:                                                          校长签名"
                else:
                    self.faSh.range(f"A{startNum - 3}:N{startNum - 3}").api.Font.Bold = True
                    self.faSh.range(f"A{startNum - 2}:N{startNum - 2}").api.Merge()
                    self.faSh.range(f"A{startNum - 2}:N{startNum - 2}").api.Font.Size = 12
                    self.faSh.range(f"A{startNum - 2}:N{startNum - 2}").api.Font.Bold = True
                    self.faSh.range(f"A{startNum - 2}").value = faTitle2
                    self.faSh.range(f"A{startNum - 1}:N{startNum - 1}").value = faTitle3
                    for num in range(7, 13):
                        self.faSh.range(f"A{startNum - 1}:N{end2}").api.Borders(num).LineStyle = 1
                        self.faSh.range(f"A{startNum - 1}:N{end2}").api.Borders(num).Weight = 2
                    self.faSh.range(f"A{end1}:N{end1}").api.Merge()
                    self.faSh.range(f"A{end1}:N{end1}").value = f"本页小计:{xjSum}              大写:{self.numToBig(xjSum)}整"
                    self.faSh.range(f"A{end2}:N{end2}").api.Merge()
                    self.faSh.range(f"A{end2}").api.HorizontalAlignment = -4131
                    self.faSh.range(
                        f"A{end2}").value = "学校资助工作人员签名:                                                          校长签名"
                pb_s += 1
                self.pb_update(pb_s)

            endStrNum = 4 + dataLen * 18
            if self.xd == '学前' or self.xd == '义务':
                self.faSh.range(f"A{endStrNum}:O{endStrNum}").api.Merge()
                self.faSh.range(
                    f"A{endStrNum}").value = f"    合计:   {self.faSumMoney}元          大写: {self.numToBig(self.faSumMoney)}整"
                self.faSh.range(f"A{endStrNum}").api.Font.Size = 12
                self.faSh.range(f"A{endStrNum}").api.Font.Bold = True
                self.faSh.range(f"A{endStrNum + 1}:O{endStrNum + 1}").api.Merge()
                self.faSh.range(f"A{endStrNum + 1}").value = "    领导签字: "
                self.faSh.range(f"A{endStrNum + 1}").api.Font.Size = 12
                self.faSh.range(f"A{endStrNum + 1}").api.Font.Bold = True
                self.faSh.range(f"A{endStrNum + 2}:O{endStrNum + 2}").api.Merge()
                self.faSh.range(f"A{endStrNum + 2}").api.Font.Size = 12
                self.faSh.range(f"A{endStrNum + 2}").value = "    学生资助工作人员签字"
                self.faSh.range(f"A{endStrNum + 2}").api.Font.Bold = True
                self.faSh.range(f"A{endStrNum + 3}:O{endStrNum + 3}").api.Merge()
                self.faSh.range(
                    f"A{endStrNum + 3}").value = "    备注:此表由学校组织,经困难学生本人或家长签字确认后,原件入账,复印件一式两份,学校、县(市)区学生资助中心各留存一份备查。"
                self.faSh.range(f"A{endStrNum}:O{endStrNum + 3}").api.HorizontalAlignment = -4131
                self.faSh.range(f"A{endStrNum}:O{endStrNum + 3}").row_height = 30
            else:
                self.faSh.range(f"A{endStrNum}:N{endStrNum}").api.Merge()
                self.faSh.range(
                    f"A{endStrNum}").value = f"    合计:   {self.faSumMoney}元          大写: {self.numToBig(self.faSumMoney)}整"
                self.faSh.range(f"A{endStrNum}").api.Font.Size = 12
                self.faSh.range(f"A{endStrNum}").api.Font.Bold = True
                self.faSh.range(f"A{endStrNum + 1}:N{endStrNum + 1}").api.Merge()
                self.faSh.range(f"A{endStrNum + 1}").value = "    领导签字: "
                self.faSh.range(f"A{endStrNum + 1}").api.Font.Size = 12
                self.faSh.range(f"A{endStrNum + 1}").api.Font.Bold = True
                self.faSh.range(f"A{endStrNum + 2}:N{endStrNum + 2}").api.Merge()
                self.faSh.range(f"A{endStrNum + 2}").api.Font.Size = 12
                self.faSh.range(f"A{endStrNum + 2}").value = "    学生资助工作人员签字"
                self.faSh.range(f"A{endStrNum + 2}").api.Font.Bold = True
                self.faSh.range(f"A{endStrNum + 3}:N{endStrNum + 3}").api.Merge()
                self.faSh.range(
                    f"A{endStrNum + 3}").value = "    备注:此表由学校组织,经困难学生本人或家长签字确认后,原件入账,复印件一式两份,学校、县(市)区学生资助中心各留存一份备查。"
                self.faSh.range(f"A{endStrNum}:N{endStrNum + 3}").api.HorizontalAlignment = -4131
                self.faSh.range(f"A{endStrNum}:N{endStrNum + 3}").row_height = 30
            self.wb.save()
            self.pb_update(pb_num)
            self.lab.config(text='制作发放完成!')
            messagebox.showinfo(title='提示', message='制作公示完成,请确认无误后打印!!')
            self.wb.api.Protect(Password='zk@2461761', Structure=True, Windows=True)
            self.but1.config(state='disable')
            self.but11.config(state='disable')
            self.but2.config(state='normal')
            self.but3.config(state='normal')
            self.but4.config(state='normal')
            self.but5.config(state='normal')
            self.but6.config(state='normal')
        except Exception as e:
            messagebox.showerror(title='错误!', message=f'工作簿非法关闭:{e}')
            self.but1.config(state='normal')
            self.but11.config(state='normal')
            self.but2.config(state='disable')
            self.but3.config(state='disable')
            self.but4.config(state='disable')
            self.but5.config(state='disable')
            self.but6.config(state='normal')

    # 计算补助金额小计
    def moneySum(self, moneyList):
        mSum = 0
        for mm in moneyList:
            try:
                mSum += float(mm[9])
            except Exception:
                return "err"
        self.faSumMoney += mSum
        return mSum

    def numToBig(self, num):
        dict1 = {1: '壹', 2: '贰', 3: '叁', 4: '肆', 5: '伍', 6: '陆', 7: '柒', 8: '捌', 9: '玖', 0: '零'}
        dict2 = {2: '拾', 3: '佰', 4: '仟', 5: '万', 6: '拾', 7: '佰', 8: '仟', 1: '元', 9: '角', 10: '分', 11: '整'}
        money = ''  # 最终大写数字
        flag = False  # 去掉多余的十百千
        flag2 = False  # 增加零
        ifint = False  # 整
        count = 0
        count2 = 8
        # num = 11324
        strnum = str(num)
        aa = strnum.split('.')
        bb = list(str(aa[:1])[2:-2])
        cc = list(str(aa[1:])[2:-2])
        # 此处控制:无小数时输出xxx元整
        # 若要求一位小数也带整,即xxx元整并且xxx元xx角整,则修改下方0为1
        if len(cc) <= 0:
            ifint = True
        else:
            ifint = False
        # 整数部分
        for i in reversed(bb):
            count = count + 1
            if int(i) == 0:
                if flag == True:
                    if count != 5:
                        continue
                    else:
                        money = dict2[count] + money
                else:
                    if flag2 == False:
                        money = dict2[count] + money
                    else:
                        if count != 5:
                            money = '零' + money
                        else:
                            money = dict2[count] + '零' + money
                flag = True
            else:
                flag = False
                flag2 = True
                money = dict1[int(i)] + dict2[count] + money
        # 小数部分
        for i in cc:
            count2 = count2 + 1
            money = money + dict1[int(i)] + dict2[count2]
        if ifint == True:
            money = money + '整'
        return money

    '''#################退出当前项目#################'''

    def colseProject(self):
        self.but1.config(state='disable')
        self.but11.config(state='disable')
        self.but2.config(state='disable')
        self.but3.config(state='disable')
        self.but4.config(state='disable')
        self.but5.config(state='disable')
        self.but6.config(state='disable')
        self.lab.config(text='正在关闭项目:')
        self.pb['value'] = 10
        self.root.update()
        try:
            self.wb.save()
            self.wb.close()
            self.pb['value'] = 20
            self.root.update()
        except Exception as e:
            pass
        try:
            self.app.quit()
            self.pb['value'] = 30
            self.root.update()
        except Exception as e:
            pass
        self.colseBefor()
        self.pb['value'] = 40
        self.root.update()
        self.pb['value'] = 100
        self.lab.config(text='项目已关闭!')
        self.but1.config(state='normal')
        self.but11.config(state='normal')
        self.but2.config(state='disable')
        self.but3.config(state='disable')
        self.but4.config(state='disable')
        self.but5.config(state='disable')
        self.but6.config(state='normal')

    '''#################退出工具#################'''

    def colseWin(self):
        self.but1.config(state='disable')
        self.but11.config(state='disable')
        self.but2.config(state='disable')
        self.but3.config(state='disable')
        self.but4.config(state='disable')
        self.but5.config(state='disable')
        self.but6.config(state='disable')
        self.lab.config(text='正在关闭系统')
        self.pb_update(10)
        try:
            self.wb.save()
            self.wb.close()
            self.pb_update(30)
        except Exception as e:
            pass
        try:
            self.app.quit()
            self.pb_update(60)
        except Exception as e:
            pass
        self.colseBefor()
        self.pb_update(90)
        self.root.destroy()

    # 关闭前工作
    def colseBefor(self):
        self.dataSh = None
        self.gsSh = None
        self.faSh = None
        # self.sysConfig = None
        self.wb = None
        self.app = None

    '''###############进度条窗口#############'''

    def pb_update(self, num):
        self.pb['value'] = num
        self.root.update()

    # 身份证号码校验
    def check_id_length(self, n):  # 判断身份证号长度是否正确
        if len(str(n)) != 18:
            return False
        else:
            return True

    def check_id_data(self, n):  # 检查数据
        factor = (7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2)
        last = ("1", "O", "X", "9", "8", "7", "6", "5", "4", "3", "2")
        n = str(n)
        sum = 0
        if int(n[16]) % 2 == 0:  # 判断第17位是否为偶数
            gender = "女"
        else:
            gender = "男"
        for i in range(0, 17):
            sum += int(n[i]) * factor[i]  # 求前17位与加权数相乘的和
        sum %= 11  # 取余,计算余数对应的第18位身份证号
        if (last[sum]) == str(n[17]):  # 第18位相同
            return [True, gender]
        else:
            return 0

    '''####################单元格区域字体、大小 背景色######################'''

    def rngGs(self, my_rng, f_size, back_color=(255, 255, 255), f_bold=False):
        my_rng.font.size = f_size
        my_rng.font.bold = f_bold
        my_rng.color = back_color
        my_rng.wrap_text = True
        my_rng.api.HorizontalAlignment = -4108
        my_rng.api.VerticalAlignment = -4108
        for i in range(7, 13):
            b = my_rng.api.Borders(i)
            b.LineStyle = 1
            b.Weight = 2
            b.Color = rgb_to_int((0, 0, 0))

    '''#########################页面设置########################################'''

    def myPageSet(self, my_sh):
        my_sh.api.PageSetup.PaperSize = 9  # 设置纸张大小
        my_sh.api.PageSetup.PrintTitleRows = "$11:$13"  # 设置顶端标题
        my_sh.api.PageSetup.Orientation = 1  # 设置页面方向 1 纵向 2横向
        my_sh.api.PageSetup.centerHorizontally = True  # 设置水平居中
        my_sh.api.PageSetup.LeftMargin = 50  # 设置左边距
        my_sh.api.PageSetup.RightMargin = 50  # 设置右边距
        my_sh.api.PageSetup.TopMargin = 50  # 设置上边距
        my_sh.api.PageSetup.BottomMargin = 50  # 设置下边距


if __name__ == '__main__':
    myxszz()

  • 6
    点赞
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值