|
|
本帖最后由 sheeboard 于 2018-10-8 15:37 编辑
用python写的，不知道工时如何算。
- import openpyxl
- import xlsxwriter
- import datetime
- wb=openpyxl.load_workbook('考勤记录.xlsx')
- ws=wb['员工刷卡记录表']
- wb1=openpyxl.Workbook()
- ws1=wb1.active
- length=ws.max_row
- width=ws.max_column
- refbase=[]
- for row in ws.iter_rows(min_row=1,max_col=width,max_row=length):
- for cell in row:
- if isinstance(cell.value,str):
- if cell.value.startswith('工号'):
- refbase.append(cell.row)
- col_lab=[xlsxwriter.utility.xl_col_to_name(x) for x in range(1,width)]
- def process(Tlist):
- temp_list=[]
- Tlist=[datetime.datetime.strptime(x,'%H:%M') for x in Tlist if x !=' ']
- for t,next_t in zip(Tlist,Tlist[1:]):
- if next_t-t<datetime.timedelta(minutes=10):
- temp_list.append(next_t)
- Tlist=[el for el in Tlist if el not in temp_list]
- Tlist=[datetime.datetime.strftime(x,'%H:%M') for x in Tlist]
- return Tlist
- def get_value(data):
- time_list=[]
- for item in data:
- for cell in item:
- if cell.value is not None:
- time_list.extend(cell.value.split('\n'))
- time_list=process(time_list)
- return time_list
- for i,j in zip(refbase,refbase[1:]):
- for col in col_lab:
- name=ws["L"+str(i)].value
- day=ws[col+str(i+1)].value
- d=ws[col+str(i+2):col+str(j-1)]
- result_data=get_value(d)
- row_data=[name,day]
- row_data.extend(result_data)
- ws1.append(row_data)
- wb1.save('结果.xlsx')
|
|
楼主: 18089790912
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2018-10-8 15:17
楼主
