elab_mvp/report_file_utils.py

import xlwt


class ReportFileUtils:
    def __init__(self):
        self.wb = xlwt.Workbook()
        self.styleOK = xlwt.easyxf()
        self.default_style = self.set_style('Times New Roman',
                       200,
                       bold=False,
                       format_str='', align='')
    pass

    def set_style(self, name, height, bold=False, format_str='', align='center'):
        style = xlwt.XFStyle()  # 初始化样式

        font = xlwt.Font()  # 为样式创建字体
        font.name = name  # 'Times New Roman'
        font.bold = bold
        font.height = height

        borders = xlwt.Borders()  # 为样式创建边框
        borders.left = 2
        borders.right = 2
        borders.top = 0
        borders.bottom = 2

        alignment = xlwt.Alignment()  # 设置排列
        if align == 'center':
            alignment.horz = xlwt.Alignment.HORZ_CENTER
            alignment.vert = xlwt.Alignment.VERT_CENTER
        else:
            alignment.horz = xlwt.Alignment.HORZ_LEFT
            alignment.vert = xlwt.Alignment.VERT_BOTTOM

        style.font = font
        style.borders = borders
        style.num_format_str = format_str
        style.alignment = alignment

        return style

    def horizontal_cell_merge(self, ws, start_row, end_row, start_col, end_col, content):
        """
            横向单元格合并
        :param ws:
        :param start_row:
        :param end_row:
        :param start_col:
        :param end_col:
        :param content:
        :return:
        """
        ws.write_merge(
            start_row,
            end_row,
            start_col,
            end_col,
            content,
            self.set_style(
                'Times New Roman',
                320,
                bold=True,
                format_str=''))

    def horizontal_space_cells(self, ws, start_row, end_row, start_col, end_col):
        self.horizontal_cell_merge(ws, start_row, end_row, start_col, end_col, '')

    def vertical_cell_merge(self, ws, start_row, end_row, start_col, end_col, content):
        """
            纵向单元格合并
        :param ws:
        :param start_row:
        :param rows:
        :param start_col:
        :param end_col:
        :param content:
        :return:
        """
        ws.write_merge(
            start_row,
            end_row,
            start_col,
            end_col,
            content,
            self.set_style(
                'Times New Roman',
                320,
                bold=True,
                format_str=''))  # 合并单元格

    def insert_cells(self, ws, data, start_row, start_col, end_col):
        for index, v in enumerate(data):
            y = 0
            for col in range(start_col, end_col):
                value = v[y]
                if value is None:
                    value = 0
                ws.col(col).width = 150 * 30  # 定义列宽
                ws.write(start_row, col, str(value),
                         style=self.default_style)
                y += 1
            start_row += 1

    def horizontal_space_cells(self, ws, start_row, end_row, start_col, end_col):
        """
            空白占位
        :param ws:
        :param start_row:
        :param end_row:
        :param start_col:
        :param end_col:
        :return:
        """
        self.horizontal_cell_merge(ws, start_row, end_row, start_col, end_col, '')

    def create_excel_file(self, data, sheet_names, title, headers, save_path):
        """
            生成excel文件， data, sheet_names,headers 三者在数量上要对应
        :param data: 数据
        :param sheet_names: 工作簿名称
        :param title: 文件名称
        :param headers: 每个工作簿表头
        :return:
        """
        for index, d in enumerate(data):
            # 创建工作簿
            sheet_name = sheet_names[index]
            header = headers[index]
            ws = self.wb.add_sheet(sheet_name, cell_overwrite_ok=True)
            # 写入数据
            # 区域
            start_row = 0
            # 写入标题
            self.horizontal_cell_merge(ws, start_row, start_row, 0, len(header) - 1, sheet_name)
            start_row += 1

            # 写入表头
            self.insert_cells(ws, [headers[index]], start_row, 0, len(header))
            start_row += 1
            city_info = {}
            insert_data = []
            region_info = {}
            data_size = 0
            for key in d.keys():
                region_rows = 0
                value = d.get(key)
                region_name = key
                # 城市
                for key_1 in value.keys():
                    value_1 = value.get(key_1)
                    city_name = key_1
                    region_rows += len(value_1)
                    insert_data.extend(value_1)
                    city_info[city_name] = len(value_1)
                    data_size = len(value_1[0])
                region_info[region_name] = region_rows
            # 合并区域
            for key in region_info.keys():
                print(key)
                self.vertical_cell_merge(ws, start_row, start_row + region_info.get(key) - 1, 0, 0, key)
                start_row += region_info.get(key)
            # 城市合并
            start_row = 2
            for key in city_info.keys():
                 self.vertical_cell_merge(ws, start_row, start_row + city_info.get(key) - 1, 1, 1, key)
                 start_row += city_info.get(key)

            # 写入数据
            start_row = 2
            self.insert_cells(ws, insert_data, start_row, 2, 2 + data_size)
        self.wb.save(save_path + '/' + title)


if __name__ == '__main__':
    rf = ReportFileUtils()
    data = [
                {"华北": {"上dfsd海": [['建业1', 2, 3, 4, 5, 6, 7], ['建业2', 2, 3, 4, 5, 6, 7]],
                        "北dfsdf京": [['建东1', 2, 3, 4, 5, 6, 7], ['建东2', 2, 3, 4, 5, 6, 7]]
                        },
                 "嘻嘻哈哈": {"上dfssd海": [['建业1', 2, 3, 4, 5, 6, 7], ['建业2', 2, 3, 4, 5, 6, 7]],
                        "北dfsdssf京": [['建东1', 2, 3, 4, 5, 6, 7], ['建东2', 2, 3, 4, 5, 6, 7]]
                        }
                 },
                {"华北": {"上海ss": [['建业1', 21, 31, 41, 5, 6, 7], ['建业2', 2, 3, 4, 5, 6, 7]],
                        "北京ss": [['建东1', 2, 3, 4, 5, 6, 7], ['建东2', 2, 3, 4, 5, 6, 7]]
                        }
                 },

                {"华北": {"上sdsfs海": [['建业1', 2, 3, 4, 5, 6, 7], ['建业2', 2, 3, 4, 5, 6, 7]],
                        "sddss": [['建东1', 2, 3, 4, 5, 6, 7], ['建东2', 2, 3, 4, 5, 6, 7]]
                        }
                 }

        ]

    sheet_names = ['前一日数据', '本月数据', '上线后所有']
    title = "测ss试.xlsx"
    headers = [['区域', '城市', '项目名称', '推荐人id', '姓名', '手机号', '火电', '注册', '宝贝'],
               ['区域', '城市', '项目名称', '推荐人id', '姓名', '手机号', '火电', '注册', '宝贝'],
               ['区域', '城市', '项目名称', '推荐人id', '姓名', '手机号', '火电', '注册', '宝贝']]
    rf.create_excel_file(data, sheet_names, title, headers, r'D:\elab\elab_mvp\test')