import pandas as pdimport selectUsecols as suimport os,xlwt,datetime,calendar,timeimport xlsxwriterworkbook = xlwt.Workbook(encoding = 'ascii')wenjianname=r'C:\Users\caoming\Desktop\数据处理'filename='SAMPLE061901.xlsx'test1=os.path.join(wenjianname,filename)df = pd.read_excel(test1, sheet_name='Sheet1')contractnums=su.hee(test1,[1])for contractnum in contractnums:contract_list=df[df['项目编号']==contractnum]write1=pd.ExcelWriter(wenjianname+'\\新建文件夹\\'+contractnum+'.xls')contract_list.to_excel(write1,index=0)write1.save()write1.close()projectnames = su.hee(write1, [2])df1=pd.read_excel(write1)ff=0for project in projectnames:workbook = xlsxwriter.Workbook(wenjianname+'\\新建文件夹\\'+contractnum+'-'+str(ff)+'.xls')style = workbook.add_format({'bold': True, 'font_size': 14, 'font_name': u'宋体'})style.set_align('center') # 水平对齐style.set_align('vcenter') # 垂直对齐style.set_text_wrap() # 内容换行style1 = workbook.add_format({'bold': True, 'font_size': 12, 'font_name': u'宋体'})style1.set_align('center') # 水平对齐style1.set_align('vcenter') # 垂直对齐style1.set_text_wrap() # 内容换行style2 = workbook.add_format({'bold': True, 'font_size': 10, 'font_name': u'宋体'})style2.set_align('center') # 水平对齐style2.set_align('vcenter') # 垂直对齐style2.set_text_wrap() # 内容换行style3 = workbook.add_format({'font_size': 9, 'font_name': u'宋体', 'border': 1, 'border_color': 'black'})style3.set_align('center') # 水平对齐style3.set_align('vcenter') # 垂直对齐style3.set_text_wrap() # 内容换行style4 = workbook.add_format({'bold': True, 'fg_color': '#D9D9D9', 'font_size': 9, 'font_name': u'宋体', 'border': 1,'border_color': 'black'})style4.set_align('center') # 水平对齐style4.set_align('vcenter') # 垂直对齐style4.set_text_wrap() # 内容换行style5 = workbook.add_format({'bold': True, 'font_size': 9, 'font_name': u'宋体', 'border': 1, 'border_color': 'black'})style5.set_align('center') # 水平对齐style5.set_align('vcenter') # 垂直对齐style5.set_text_wrap() # 内容换行style6 = workbook.add_format({'bold': True, 'font_size': 12, 'font_name': u'宋体', 'bottom': 1, 'border_color': 'black'})style7 = workbook.add_format({'bold': True, 'font_size': 10, 'font_name': u'宋体'})style7.set_align('vcenter') # 垂直对齐style7.set_text_wrap() # 内容换行project_list=df1[df1['项目名称']==project]write2=pd.ExcelWriter(wenjianname+'\\新建文件夹\\'+contractnum+'-'+str(ff)+'zhongjian'+'.xls')project_list.to_excel(write2,index=0)write2.save()write2.close()ff+=1df2=pd.read_excel(write2)riqis=su.hee(write2,[3])riqis.sort()write3=pd.ExcelWriter(wenjianname+'\\新建文件夹\\'+contractnum+'-'+str(ff)+'time'+'.xls')for riqi in riqis:riqi_list1=df2[df2['日期']==riqi]# riqi_list.to_excel(write3,sheet_name=riqi,index=0)riqi_list=riqi_list1.values.tolist()n=len(riqi_list)worksheet = workbook.add_worksheet(riqi)# 页边距worksheet.set_margins(left=0.5, right=0.5, top=0.75, bottom=0.75)# 页眉 页脚worksheet.set_header("", {'margin': 0.3})worksheet.set_footer("", {'margin': 0.3})# worksheet.set_v_pagebreaks([5,6])worksheet.set_column('A:A', 16)# 设置列宽worksheet.set_column('B:B', 16)worksheet.set_column('C:C', 24)worksheet.set_column('D:D', 10)worksheet.set_column('E:E', 12.75)worksheet.set_column('F:F', 10)worksheet.set_row(0, 24.95)# 设置行宽worksheet.set_row(1, 24.95)worksheet.set_row(2, 24.95)worksheet.set_row(3, 24.95)worksheet.set_row(4, 24.95)worksheet.merge_range('A2:F2','北京捷通华声科技股份有限公司', style)worksheet.merge_range('A3:F3', '项目成本工时归集表', style1)worksheet.write('A4', '项目名称：', style2)worksheet.write('E5', '更新时间：', style2)worksheet.write('A7', '合同编号', style4)worksheet.write('B7', '项目编号', style4)worksheet.write('C7', '项目名称', style4)worksheet.write('D7', '所属年月', style4)worksheet.write('E7', '员工姓名', style4)worksheet.write('F7', '本月工时合计（小时）', style4)worksheet.merge_range('B4:F4', project, style7)sum=0for i in range(n):worksheet.write(i + 7, 0, riqi_list[i][0], style3)worksheet.write(i + 7, 1, riqi_list[i][1], style3)worksheet.write(i + 7, 2, riqi_list[i][2], style3)worksheet.write(i + 7, 3, riqi_list[i][3], style3)worksheet.write(i + 7, 4, riqi_list[i][4], style3)worksheet.write(i + 7, 5, riqi_list[i][5], style3)sum=riqi_list[i][5]+sumworksheet.write(n + 7, 4, '工时合计', style5)worksheet.write(n + 7, 5,sum, style5)worksheet.write(n + 9, 4, '项目经理：', style1)worksheet.write(n + 9, 5, None, style6)worksheet.merge_range((':'.join(['E'+str(n+12), 'F' +str(n+12)])),'年 月 日', style1)worksheet.set_row(n + 7, 24.95) # 设置行宽worksheet.set_row(n + 8, 24.95) # 设置行宽worksheet.set_row(n + 9, 24.95) # 设置行宽worksheet.set_row(n + 10, 24.95) # 设置行宽worksheet.set_row(n + 11, 24.95) # 设置行宽worksheet.set_row(n + 12, 24.95) # 设置行宽nn = time.strftime("%Y-%m-%d", time.strptime(str(riqi_list[0][6]), "%Y%m%d"))createtime = datetime.datetime.strptime(nn, '%Y-%m-%d').date()result = calendar.monthrange(createtime.year, createtime.month)lastday = (datetime.datetime(createtime.year, createtime.month, result[1])).strftime("%Y-%m-%d")worksheet.write('F5', lastday, style2)print(contractnum+'$$$'+str(ff)+'$$$'+project+'$$$'+riqi+'$$$'+str(sum))worksheet.set_paper(9) # A4 papperworksheet.repeat_rows(6)workbook.close()os.remove(write1)os.remove(write2)
