上面是完成后的效果图
下面是具体代码
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()
959
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
