每月需汇总审批超时人员次数名单，文档为从数据库导出。由于审批时间在文本中，需对文本进行正则截取、格式化拆分，并判断审批时间是否超时并生成超时列表，再写入工作簿。最后对超时名单做汇总求和。由于存在假日调休，对假日、调休，写死在代码中缺乏灵活性。待改正

#!/usr/bin/python
# _*_coding：utf-8_*_
'''
@author:jam
@time 2019
'''
import re
import os
from datetime import datetime
from openpyxl import load_workbook
from openpyxl.styles import Font, Fill, Alignment, Border, Side, PatternFill,Color
import pandas as pd
import numpy as np
from openpyxl import Workbook
def read_excel(file,txt):##清理意见栏并格式化审批人审批时间wb=load_workbook(filename=file)#打开文件ws=wb.activerow_max = ws.max_row#print(row_max)#总行数h=1while h<=row_max :h=h+1col2=ws.cell(row=h,column=4)yj=col2.valueif yj == None:continueelse:with open(txt,'w', encoding='utf-8') as f:#将意见栏内容写入TXT文件，再读取正则匹配f.write(yj)i = 5for lines in open(txt, 'r', encoding='utf-8'):#mat = re.search(r"(\w*(\s\d{4}-\d{1,2}-\d{1,2}\s\d{1,2}:\d{1,2}))", lines)#匹配字符+时间(无法匹配中括号）#mat = re.search(r"((\w*)(\s\d{4}-\d{1,2}-\d{1,2}\s\d{1,2}:\d{1,2})|(\w*[\(（][^\(（]+[\)）])(\s\d{4}-\d{1,2}-\d{1,2}\s\d{1,2}:\d{1,2}))", lines)#带中文括号字符+日期时间匹配wmat = re.search(r"(\w*(?=(\s\d{4}-\d{1,2}-\d{1,2}\s\d{1,2}:\d{1,2}))|\w*[\(（][^\(（]+[\)）](?=(\s\d{4}-\d{1,2}-\d{1,2}\s\d{1,2}:\d{1,2})))",lines)#匹配时间前面的字符人员tmat = re.search(r"((\d{4}-\d{1,2}-\d{1,2}))", lines)  # 匹配时间if wmat != None:i += 1col = ws.cell(row=h, column=i)col.value=wmat.groups(0)[0]if tmat != None:i+=1col = ws.cell(row=h, column=i)col.value = datetime.strptime(tmat.groups(0)[0],'%Y-%m-%d')#格式化时间并写入单元格else:continueelse:continuewb.save(file)wb.close()print('数据清理完成')
def start_time(file):#OA发起时间格式化写入5列wb=load_workbook(filename=file)#打开文件ws=wb.activerow_max = ws.max_row#最大行数h=1while h <= row_max:  # 遍历C列所有行h+=1startcell=ws.cell(row=h,column=3)#OA发起时间所在单元格if startcell.value !=None:stime=ws.cell(row=h,column=5)#h行第5列单元格，用来存放格式化后的OA发起时间用于后面计算用#print(startcell.value)#print(type(startcell.value))datare=re.search(r"((\d{4}-\d{1,2}-\d{1,2}))", startcell.value)#OA发起时间所在单元格去特殊字符stime.value=datetime.strptime(datare.groups(0)[0],'%Y-%m-%d')#转换成时间类型并写入H行第5列#print(stime.value)#print(type(stime.value))else:print('空')passwb.save(file)wb.close()print('起始时间标识完成')
def time_diff(file):#计算时间差，查出超时时间标红并返回审批人员列表wb=load_workbook(filename=file)#打开文件ws=wb.activerowmax = ws.max_row#最大行数#print(rowmax)colmax = ws.max_column#最大列数h=2#起始行time_out = []hday = '2020-01-01'hday = datetime.strptime(hday,'%Y-%m-%d')#假日时间格式化wday6 ='2020-01-18'wday6 = datetime.strptime(wday6, '%Y-%m-%d')  # 上班时间格式化wday7 ='2020-01-19'wday7 = datetime.strptime(wday7, '%Y-%m-%d')  # 上班时间格式化while h<= rowmax:#行循环i = 5  # 起始列while i <= colmax :#列循环a = ws.cell(row=h,column=i)#前一审批时间b = ws.cell(row=h, column=(i + 2))#后一审批时间d = ws.cell(row=b.row, column=(b.column - 1))#后时间审批人aw = a.valuebw = b.valuered_fill = PatternFill("solid", fgColor='FF0000')#填充红色if bw == None:#如果结束审批为NONE则退出该循环passelif bw == wday6 or bw == wday7:#判断结束审批日期是否为在上班c=bw-awc=int(c.total_seconds()/3600)if c >int(48):b.fill=red_fill#超时标红#print(d.value)time_out.append(d.value)#超时的审批人加入列表#print(time_out)#print(a.value,b.value,c,bw.isoweekday(),'超时4567')#如果审批日期为4560并且相差超过48小时#print(b.column)else:passelif aw <= hday <= bw:  # 判断假日是否在起始时间结束时间范围内,在则减去24小时n=1if bw.isoweekday() in (3,4,5,6):  # 判断审批日期是否为周456c=bw-awc=int(c.total_seconds()/3600)-n*24if c >int(48):b.fill=red_fill#超时标红#print(d.value)time_out.append(d.value)#超时的审批人加入列表else:passelse:c=b.value-a.valuec = int(c.total_seconds() / 3600)-n*24if c >int(72):#如果审批日期为123并且相差超过72小时b.fill = red_fill#超时标红time_out.append(d.value)#超时的审批人加入列表else:#如果审批日不包含假日if  bw.isoweekday() in (3,4,5,6):#判断审批日期是否为周3456c=bw-awc=int(c.total_seconds()/3600)if c >int(48):b.fill=red_fill#超时标红#print(d.value)time_out.append(d.value)#超时的审批人加入列表#print(time_out)#print(a.value,b.value,c,bw.isoweekday(),'超时4567')#如果审批日期为4560并且相差超过48小时#print(b.column)else:pass#print(a.value,b.value,c,bw.isoweekday(),'正常4567')#print(b.column)else:c=b.value-a.valuec = int(c.total_seconds() / 3600)if c >int(72):#如果审批日期为012并且相差超过72小时b.fill = red_fill#超时标红time_out.append(d.value)#超时的审批人加入列表#print(time_out)#print(a.value, b.value, c,bw.isoweekday(), '超时012')#print(b.column)else:passi+=2h+=1#print(h)wb.save(file)wb.close()print('时间差异标识完成')return (time_out)#
def sum(arr,file):#汇总统计超时人员次数写入timeouts工作簿wb=load_workbook(filename=file)#打开文件#ws=wb.activews=wb.create_sheet(title='timeouts')h=2#行ws.cell(1, 1).value = '审批人'ws.cell(1, 2).value = '超时次数'todic={}#将传入列表统计超时人员名字次数并生成字典for i in set(arr):todic[i]=arr.count(i)for key,value in todic.items():#遍历字典键值对name = ws.cell(row=h,column=1)num = ws.cell(row=h,column=2)name.value=keynum.value=valueh+=1wb.save(file)wb.close()#print(key,value)
def oadir(df):#遍历SQL导出xlsx文件目录，并将文件绝对路径装入列表并返回filedir=[]for root, dirs, files in os.walk(df):for file in files:# 获取文件所属目录#print(root)# 获取文件路径filedir.append(os.path.join(root, file))return (filedir)
def pivot_timeout(tofile):#将各单据超时明细列表做透视表df = pd.read_excel(tofile,sheet_name='各单据人员超时')res = pd.pivot_table(df, index=['审批人'], aggfunc=[np.sum], values=['超时次数'], margins=True, margins_name='合计')  # 按审批人汇总求和book = load_workbook(tofile)  # 实例化现有文件writer = pd.ExcelWriter(tofile, engine='openpyxl')writer.book = bookwriter.sheets = dict((ws.title, ws) for ws in book.worksheets)  # 遍历工作簿名称res.to_excel(writer, "汇总")  # 写入透视表writer.save()if __name__ == '__main__':df = 'd:\pyoa\doc'txt='d:\\pyoa\\yj.txt'tofile='D:\\pyoa\\doc\\超时汇总.xlsx'wb = Workbook()  # 创建文件ws = wb.create_sheet(title='各单据人员超时',index=0)h=2#行ws.cell(1, 1).value = '审批人'ws.cell(1, 2).value = '超时次数'for name in oadir(df):#遍历目录下excel文件file=nameprint(file)read_excel(file,txt)##清理意见栏并格式化审批人审批时间#time.sleep(1)start_time(file)#OA开始时间写入arr=time_diff(file)##计算时间差，查出超时时间标红并返回超时审批人员列表sum(arr,file)#汇总统计超时人员次数写入timeouts工作簿#print(arr)todic = {}  # 将传入列表统计超时人员名字次数并生成字典for i in set(arr):todic[i] = arr.count(i)#汇总统计超时人员次数print(todic)for key, value in todic.items():  # 遍历字典键值对写入excelname = ws.cell(row=h, column=1)num = ws.cell(row=h, column=2)name.value = keynum.value = valueh+=1wb.save(tofile)pivot_timeout(tofile)