【pandas】结合xlsxwriter和groupby实现xls导出合并单元格

仅记录

1. 问题描述：
想自动实现xlsx合并同类项

2. 实现代码
 

    
    df = pd.DataFrame(dict_w)
    out = io.BytesIO()
    wb2007 = xlsxwriter.Workbook(out)
    worksheet2007 = wb2007.add_worksheet()
    format_top = wb2007.add_format({'border': 1, 'bold': True, 'align': 'center', 'valign': 'vcenter'})
    format_other = wb2007.add_format({'border': 1, 'align': 'center', 'valign': 'vcenter'})
    self_copy = df
    cols = list(self_copy.columns.values)
    for i, value in enumerate(cols):
        worksheet2007.write(0, i, value, format_top)

    worksheet2007.write(1, 0, df.values[0, 0], format_other)
    worksheet2007.write(1, 6, df.values[0, 6], format_other)
    if len(jiluxiang) > 1:
        worksheet2007.merge_range(1, 0, len(jiluxiang), 0,  df.values[0, 0],
                                  format_other)
        worksheet2007.merge_range(1, 6, len(jiluxiang), 6,  df.values[0, 6],
                                  format_other)
    worksheet2007.set_column('C:C', 30)
    worksheet2007.set_column('D:D', 25)
    groups = df.groupby(["模块"])
    last_begin = 1
    last_begin_module = 1
    for group1 in groups:
        num_in_group1 = group1[1].values.shape[0]
        worksheet2007.write(last_begin_module, 1, group1[0], format_other)
        worksheet2007.write(last_begin_module, 5, group1[1]["记分"].sum(), format_other)
        if num_in_group1 > 1:
            worksheet2007.merge_range(last_begin_module, 1, last_begin_module + num_in_group1 - 1, 1,
                                      group1[0],
                                      format_other)
            worksheet2007.merge_range(last_begin_module, 5, last_begin_module + num_in_group1 - 1, 5,
                                      group1[1]["记分"].sum(),
                                      format_other)
        last_begin_module += num_in_group1
        groups2 = group1[1].groupby(["事项"])
        for ctc_group in groups2:
            num_in_group = ctc_group[1].values.shape[0]
            worksheet2007.write(last_begin, 2, ctc_group[0], format_other)
            for i in range(num_in_group):
                for j in [3, 4]:
                    worksheet2007.write(last_begin+i, j, ctc_group[1].values[i, j], format_other)
            if num_in_group > 1:
                worksheet2007.merge_range(last_begin, 2, last_begin+num_in_group-1, 2,
                                          ctc_group[0],
                                          format_other)
            last_begin += num_in_group
    wb2007.close()

3. 后记
groupby把每一列聚类得到参数传递给merge_range函数