月度成本分摊表分表处理

import pandas as pd
import selectUsecols as su
import os,xlwt,datetime,calendar,time
import xlsxwriter

workbook = xlwt.Workbook(encoding = 'ascii')




wenjianname=r'C:\Users\caoming\Desktop\财务使用'
filename='项目底表.xlsx'
test1=os.path.join(wenjianname,filename)
df = pd.read_excel(test1, sheet_name='17-19年')
yuedus=su.hee(test1,[0])





for yudu in yuedus:
    workbook = xlsxwriter.Workbook(wenjianname + '\\新建文件夹1016\\'+yudu+'成本分摊表.xls')
    style = workbook.add_format(
        {'bold': True, 'font_size': 12, 'font_name': u'宋体', 'border': 1, 'border_color': 'black'})
    style.set_align('center')  # center:水平对齐,left:靠左对齐,right:靠右对齐,fill:填满对齐,justify:两端对齐,center_across,distributed:分散式对齐
    style.set_align('vcenter')  # vcenter:垂直对齐，top:对齐，bottom:向上对齐，vjustify:，vdistributed:
    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({'font_size': 8, 'font_name': u'宋体'})
    style2.set_align('vcenter')  # 垂直对齐
    style2.set_text_wrap()  # 内容换行

    style3 = workbook.add_format({'font_size': 8, 'font_name': u'宋体', 'border': 1, 'border_color': 'black'})
    style3.set_align('center')  # 水平对齐
    style3.set_align('vcenter')  # 垂直对齐
    style3.set_text_wrap()  # 内容换行

    style4 = workbook.add_format({'font_size': 6, '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': 10, 'font_name': u'宋体', 'border': 1, 'border_color': 'black'})
    style5.set_align('center')  # 水平对齐
    style5.set_align('vcenter')  # 垂直对齐
    style5.set_text_wrap()  # 内容换行

    style6 = workbook.add_format(
        {'font_size': 6, 'font_name': u'宋体', 'border': 1, 'border_color': 'black'})
    style6.set_align('vcenter')  # 垂直对齐
    style6.set_text_wrap()  # 内容换行

    style7 = workbook.add_format({'bold': True, 'font_size': 10, 'font_name': u'宋体'})
    style7.set_align('vcenter')  # 垂直对齐
    style7.set_text_wrap()  # 内容换行
    # print(df)

    worksheet = workbook.add_worksheet('Sheet1')

    # 页边距
    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', 33)  # 设置列宽
    worksheet.set_column('B:B', 8.6)
    worksheet.set_column('C:C', 4.5)
    worksheet.set_column('D:D', 6.3)
    worksheet.set_column('E:E', 6.3)
    worksheet.set_column('F:F', 6.3)
    worksheet.set_column('G:G', 6.3)
    worksheet.set_column('H:H', 6.3)
    worksheet.set_column('I:I', 6.3)
    worksheet.set_column('J:J', 6.3)
    worksheet.set_column('K:K', 6.3)
    worksheet.set_column('L:L', 6.3)
    worksheet.set_column('M:M', 6.3)
    worksheet.set_column('N:N', 6.3)
    worksheet.set_column('O:O', 6.3)
    worksheet.set_column('P:P', 6.3)

    worksheet.set_row(0, 22.5)  # 设置行宽
    worksheet.set_row(1, 13.5)


    worksheet.write('A2', '项目名称', style3)
    worksheet.write('B2', '合同号', style3)
    worksheet.write('C2', '工时', style3)
    worksheet.write('D2', '工资', style3)
    worksheet.write('E2', '养老保险', style3)
    worksheet.write('F2', '医疗', style3)
    worksheet.write('G2', '失业', style3)
    worksheet.write('H2', '工伤', style3)
    worksheet.write('I2', '生育', style3)
    worksheet.write('J2', '公积金', style3)
    worksheet.write('K2', '差旅', style3)
    worksheet.write('L2', '房租', style3)
    worksheet.write('M2', '物业', style3)
    worksheet.write('N2', '水电', style3)
    worksheet.write('O2', '折旧', style3)
    worksheet.write('P2', '合计', style3)

    worksheet.merge_range('A1:P1', yudu+'成本分摊表', style)

    contract_list=df[df['月份']==yudu]
    riqi_list = contract_list.values.tolist()
    # print(riqi_list)

    gongshi=0
    gongzi=0
    yanglao=0
    yiliao=0
    shiye=0
    gongshang=0
    shengyu=0
    gongjijin=0
    chailv=0
    fangzu=0
    wuye=0
    shuidian=0
    zhejiu=0
    zongji=0
    n=len(riqi_list)
    for i in range(n):
        worksheet.write(i + 2, 0, riqi_list[i][1], style4)
        worksheet.write(i + 2, 1, riqi_list[i][15], style4)
        worksheet.write(i + 2, 2, riqi_list[i][2], style4)
        worksheet.write(i + 2, 3, round(riqi_list[i][3],2), style6)
        worksheet.write(i + 2, 4, round(riqi_list[i][4],2), style6)
        worksheet.write(i + 2, 5,round( riqi_list[i][5],2), style6)
        worksheet.write(i + 2, 6,round( riqi_list[i][6],2), style6)
        worksheet.write(i + 2, 7, round( riqi_list[i][7],2) ,style6)
        worksheet.write(i + 2, 8, round( riqi_list[i][8],2), style6)
        worksheet.write(i + 2, 9, round( riqi_list[i][9],2), style6)
        worksheet.write(i + 2, 10,round( riqi_list[i][10],2), style6)
        worksheet.write(i + 2, 11, round(riqi_list[i][11],2), style6)
        worksheet.write(i + 2, 12, round(riqi_list[i][12],2), style6)
        worksheet.write(i + 2, 13, round(riqi_list[i][13],2), style6)
        worksheet.write(i + 2, 14,round(riqi_list[i][14],2), style6)
        zongji=zongji+riqi_list[i][3]+riqi_list[i][4]+riqi_list[i][5]+riqi_list[i][6]+riqi_list[i][7]+riqi_list[i][8]+riqi_list[i][9]+riqi_list[i][10]+riqi_list[i][11]+riqi_list[i][12]+riqi_list[i][13]+riqi_list[i][14]
        worksheet.write(i + 2, 15, round(riqi_list[i][3]+riqi_list[i][4]+riqi_list[i][5]+riqi_list[i][6]+riqi_list[i][7]+riqi_list[i][8]+riqi_list[i][9]+riqi_list[i][10]+riqi_list[i][11]+riqi_list[i][12]+riqi_list[i][13]+riqi_list[i][14],2), style6)

        gongshi =gongshi+riqi_list[i][2]
        gongzi = gongzi+riqi_list[i][3]
        yanglao = yanglao+riqi_list[i][4]
        yiliao = yiliao+riqi_list[i][5]
        shiye = shiye+riqi_list[i][6]
        gongshang = gongshang+riqi_list[i][7]
        shengyu = shengyu+riqi_list[i][8]
        gongjijin = gongjijin+riqi_list[i][9]
        chailv = chailv+riqi_list[i][10]
        fangzu = fangzu+riqi_list[i][11]
        wuye = wuye+riqi_list[i][12]
        shuidian = shuidian+riqi_list[i][13]
        zhejiu = zhejiu+riqi_list[i][14]

    worksheet.merge_range((':'.join(['A' + str(n + 3), 'B' + str(n + 3)])), '合计', style5)

    worksheet.write(n + 2, 2, gongshi, style4)
    worksheet.write(n + 2, 3, round(gongzi,2) , style6)
    worksheet.write(n + 2, 4, round(yanglao,2) , style6)
    worksheet.write(n + 2, 5, round(yiliao,2) , style6)
    worksheet.write(n + 2, 6, round(shiye,2) , style6)
    worksheet.write(n + 2, 7, round(gongshang,2) , style6)
    worksheet.write(n + 2, 8, round(shengyu,2) , style6)
    worksheet.write(n + 2, 9, round(gongjijin,2) , style6)
    worksheet.write(n + 2, 10, round(chailv,2) , style6)
    worksheet.write(n + 2, 11, round(fangzu,2) , style6)
    worksheet.write(n + 2, 12, round(wuye,2) , style6)
    worksheet.write(n + 2, 13, round(shuidian,2) , style6)
    worksheet.write(n + 2, 14, round(zhejiu,2) , style6)
    worksheet.write(n + 2, 15, round(zongji,2) , style6)



    worksheet.write(n + 5, 0, '制表：', style2)
    worksheet.write(n + 5, 2, '复核：', style2)
    worksheet.write(n + 5, 6, 'PMO:', style2)
    worksheet.write(n + 5, 10,  '日期：', style2)

    worksheet.merge_range((':'.join(['L' + str(n + 6), 'O' + str(n + 6)])), '            年          月         日', style2)



    # 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)  # 设置行宽


    worksheet.set_paper(9)  # A4 papper
    worksheet.repeat_rows(0,1)
    worksheet.set_landscape()#设置横向打印
    # worksheet.set_portrait()#设置纵向打印（默认纵向打印）：
    worksheet.set_print_scale(100)#设置页面缩放比例为90% （默认100%）：

    workbook.close()