本文共 6115 字，大约阅读时间需要 20 分钟。
from tkinter import *from tkinter import filedialogfrom openpyxl import Workbook from openpyxl import load_workbookfrom openpyxl.styles import PatternFillstartWork = '9:00'endWork = '18:00'ColorRed = PatternFill("solid", fgColor="FF4500")ColorGreen = PatternFill("solid", fgColor="ADFF2F")def read1(): filename= filedialog.askopenfilename() LineTxt.delete(0, END) LineTxt.insert(0,filename)def CompareStartTime(times,peopleName): if times == None: return False else: timeList = times.split(':') if not timeList[0].isnumeric(): AreaTxt.insert(INSERT,"时间格式不对 !\n") return False if int(timeList[0]) >= 9: AreaTxt.insert(INSERT,peopleName+':StartTime > 9:00, '+times+'\n') return False else : return Truedef CompareEndTime(times,peopleName): if times == None: return False else: timeList = times.split(':') if not timeList[0].isnumeric(): AreaTxt.insert(INSERT,"时间格式不对 !\n") return False if int(timeList[0]) < 18: AreaTxt.insert(INSERT,peopleName+':EndTime < 18:00, '+times+'\n') return False else : return Truedef praseCell(context,peopleName): if context == None: return False else: timeList = context.split() start = CompareStartTime(timeList[0],peopleName) end = CompareEndTime(timeList[-1],peopleName) if start and end: return True else: return Falsedef fun(): AreaTxt.delete(1.0,END) # clear text AreaTxt.insert(INSERT,"已选文件："+LineTxt.get()+"\n",'g') outWb = Workbook() #输出wb oWbsheet = outWb.active #获取活动表 oWbsheet.title = "sheet1" #命名表 try: inWb = load_workbook(LineTxt.get()) wbSheet0 = inWb['刷卡记录'] AreaTxt.insert(INSERT,"已经打开文件，当前 sheet: "+inWb.sheetnames[0]+'\n','g') except : AreaTxt.insert(INSERT,"Error: 请选择格式为 .xlsx 的文件\n",'red') return colNum = 0 try: #copy line 4 dayLine = 4 col = 1 oWbsheet.cell(1,1).value = '工号' oWbsheet.cell(1,2).value = '人名\\日期' while wbSheet0.cell(dayLine, col).value != None: oWbsheet.cell(1,col+2).value = wbSheet0.cell(dayLine,col).value col = col + 1 colNum = col - 1 print(colNum) except : AreaTxt.insert(INSERT,"Error: #copy line 4\n",'red') return try: outLine = 2 lineNum = 5 while wbSheet0.cell(lineNum, 1).value != None: oWbsheet.cell(outLine,1).value = wbSheet0.cell(lineNum,3).value oWbsheet.cell(outLine,2).value = wbSheet0.cell(lineNum,11).value lineNum = lineNum+1 for j in range(1,colNum+1): #第二列到 31 的时间 做处理 try: res = praseCell(wbSheet0.cell(lineNum, j).value,wbSheet0.cell(lineNum-1,11).value) except : AreaTxt.insert(INSERT,"Error: 比较函数出错\n",'red') return if res == True: oWbsheet.cell(outLine,2+j).value = "正常" oWbsheet.cell(outLine,2+j).fill = ColorGreen else: oWbsheet.cell(outLine,2+j).value = "异常" oWbsheet.cell(outLine,2+j).fill = ColorRed lineNum = lineNum+1 outLine = outLine+1 AreaTxt.insert(INSERT,"开始保存···\n") outWb.save('out.xlsx') #保存输出 AreaTxt.insert(INSERT,"Process finish !\n") except : AreaTxt.insert(INSERT,"Error: 处理过程遇到错误\n",'red') returnroot= Tk() root.title("处理excel") frame= Frame(root) frame.pack(padx=25,pady=25) #set areaAreaTxt = Text(frame,width=45,height=16,font =("微软雅黑",12))AreaTxt.tag_config('red', foreground='red')AreaTxt.tag_config('g', foreground='green') AreaTxt.grid(row=3,column=0,padx=30,pady=5,columnspan=2) LineTxt = Entry(frame,foreground = 'blue',font = ('Helvetica', '12'))SelectFileBut = Button(frame,text="选择表格",font=("宋体",15),command=read1)ProcBut = Button(frame ,text="处理表格",font=("宋体",15),width=10,command= fun )LineTxt.grid(row=0,ipadx=5,pady=5,columnspan=2)SelectFileBut.grid(row=1,column=0,padx=5) ProcBut.grid(row=1,column=1,padx=5,pady=5) root.mainloop() from xlrd import open_workbookimport xlwtstartWork = '9:00'endWork = '18:00'red = xlwt.easyxf('font: name Times New Roman, color-index red, bold on')gre = xlwt.easyxf('font: name Times New Roman, color-index green, bold on')def read1(): filename= filedialog.askopenfilename()def CompareStartTime(times,peopleName): if times == None: return False else: timeList = times.split(':') if not timeList[0].isnumeric(): return False if int(timeList[0]) >= 9: return False else : return Truedef CompareEndTime(times,peopleName): if times == None: return False else: timeList = times.split(':') if not timeList[0].isnumeric(): return False if int(timeList[0]) < 18: return False else : return Truedef praseCell(context,peopleName): if context.isspace(): return False else: print('cxt',context) timeList = context.split() print('tlist',timeList) if len(timeList)==0: return False start = CompareStartTime(timeList[0],peopleName) end = CompareEndTime(timeList[-1],peopleName) if start and end: return True else: return Falsedef fun(): print("wt") wt_book = xlwt.Workbook() wt_sheet = wt_book.add_sheet('A Test Sheet') in_book = open_workbook('20190201.xls') rd_sheet = in_book.sheet_by_name("刷卡记录") print (rd_sheet.nrows) print (rd_sheet.ncols) print (in_book.sheet_names()) colNum = 0 #copy line 4 dayLine = 3 col = 0 wt_sheet.write(0,0,'工号') wt_sheet.write(0,1,'人名日期') while col<31: wt_sheet.write(0,col+2,rd_sheet.cell(dayLine,col).value) col = col + 1 colNum = col - 1 print(colNum) outLine = 1 lineNum = 4 while lineNum 转载地址：http://crqii.baihongyu.com/