elab_mvp/xlwt_util.py

#coding:utf-8
import xlwt
import os
from file_util import FileUtil
import datetime


class XlwtUtil(object):
    """
        参考链接：https://www.cnblogs.com/xiao-apple36/p/9603499.html
        官方文档：https://xlwt.readthedocs.io/en/latest/
    """
    title = {'day': {
        1: '日数据概览',
        2: '项目数据排行榜',
        3: '项目历史累计总数',
        4: '单个项目小程序数据排行榜',
        5: '集团项目数据排行榜',
        6: '项目获客来源场景分析',
        7: '单个项目小程序获客来源场景分析',
        8: '集团项目获客来源场景分析'
    },
        "week": {
            1: '周数据概览',
            2: '项目数据排行榜',
            3: '项目历史累计总数',
            4: '单个项目小程序数据排行榜',
            5: '集团项目数据排行榜',
            6: '项目获客来源场景分析',
            7: '单个项目小程序获客来源场景分析',
            8: '集团项目获客来源场景分析'
        }

    }

    header_2 = [['排名', '项目名称', '总浏览量', '总浏览人数', '新增获客', '新增获电']]
    header_6 = [['项目', '合计', '长按识别二维码', '会话', '公众号菜单', '公众号文章',
                 '小程序历史列表', '扫一扫二维码', '搜索', '相册选取二维码',
                 '其他小程序', '其他']]

    dir_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    save_path = r'{}/elab_mvp/resources/report_data'.format(dir_path)

    def __init__(self):
        self.styleOK = xlwt.easyxf()

    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, rows, 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,
            2 + rows - 1,
            start_col,
            end_col,
            content,
            self.set_style(
                'Times New Roman',
                320,
                bold=True,
                format_str=''))  # 合并单元格

    def get_file_name(self, task_key, time_range, name):
        """
            移动案场订阅日报【日报日期】| 移动案场订阅周报【数据开始时间】至【数据结束时间】
        :param task_key:
        :param time_range:
        :return:
        """
        day = '移动案场订阅日报_{}_{}.xls'
        week = '移动案场订阅周报_{}_{}至{}.xls'
        new_file_name = '/tmp'
        if task_key in (1, 4):
            day = day.format(name, time_range[0])
            return ['{}/{}'.format(new_file_name, day), day]
        elif task_key in (2, 3, 21):
            week = week.format(name, time_range[0], time_range[1])
            return ['{}/{}'.format(new_file_name, week), week]

    def save_path_create(self):
        tm = datetime.datetime.now().strftime('%Y-%m-%d')
        new_path = '{}/{}'.format(self.save_path, tm)
        FileUtil.mkdir_folder(new_path)
        return new_path

    def create_excel(self, data_dict, time_rang, task_key):
        self.default_style = self.set_style('Times New Roman',
                       200,
                       bold=False,
                       format_str='', align='')
        result = []
        for key in data_dict.keys():
            self.wb = xlwt.Workbook()
            values = data_dict.get(key)
            data_1 = values.get(1) if values.get(1) is not None else values.get('1')
            data_2 = values.get(2) if values.get(2) is not None else values.get('2')
            data_3 = values.get(3) if values.get(3) is not None else values.get('3')
            data_4 = values.get(4) if values.get(4) is not None else values.get('4')
            data_5 = values.get(5) if values.get(5) is not None else values.get('5')
            data_6 = values.get(6) if values.get(6) is not None else values.get('6')
            data_7 = values.get(7) if values.get(7) is not None else values.get('7')
            data_8 = values.get(8) if values.get(8) is not None else values.get('8')
            mail = values.get(0) if values.get(0) is not None else values.get('0')
            start_row = 0
            global title
            global sheet_name
            if task_key in (2, 3, 21):
                # 周报
                sheet_name = '周报'
                title = self.title.get('week')
            elif task_key in (1, 4):
                # 日报
                sheet_name = '日报'
                title = self.title.get('day')
            ws = self.wb.add_sheet(sheet_name, cell_overwrite_ok=True)  # 增加sheet
            title_1 = title.get(1)
            if key == '金晶':
                data_name_1 = [['总浏览量', 'CNP小程序总浏览量', '单项目小程序总浏览量'], ['总浏览人数', 'CNP小程序总浏览人数', '单项目小程序总浏览人数']
                    , ['新增获客', 'CNP小程序新增获客', '单项目小程序新增获客'], ['新增获电', 'CNP小程序新增获电', '单项目小程序新增获电']]
            else:
                data_name_1 = [
                    ['总浏览量', '集团小程序总浏览量', '单项目小程序总浏览量'],
                    ['总浏览人数', '集团小程序总浏览人数', '单项目小程序总浏览人数']
                    , ['新增获客', '集团小程序新增获客', '单项目小程序新增获客'],
                    ['新增获电', '集团小程序新增获电', '单项目小程序新增获电']
                ]
            # 1
            self.horizontal_cell_merge(ws, 0, 0, 0, 5, title_1)

            self.sceptical_insert_cells(ws, data_name_1, 1, [0, 2, 4])

            col_index = 0
            for col in [1, 3, 5]:
                for row in [1, 2, 3, 4]:
                    ws.col(col).width = 150 * 30  # 定义列宽
                    ws.write(row, col, str(data_1[col_index]),
                    style=self.set_style('Times New Roman',
                                           200,
                                           bold=False,
                                           format_str='', align=''))
                    col_index += 1

            # 插入空白
            self.horizontal_space_cells(ws, 5, 5, 0, 5)

            start_row += 5

            #   2
            title_2 = title.get(2)
            start_row += 1
            self.horizontal_cell_merge(ws, start_row, start_row, 0, 5, title_2)

            start_row += 1
            self.insert_cells(ws, self.header_2, start_row, [y for y in range(0, 6)])

            start_row += 1
            self.insert_cells(ws, data_2, start_row, [y for y in range(0, 6)])
            start_row += len(data_2)

            self.horizontal_space_cells(ws, start_row, start_row, 0, 5)
            start_row += 1

            # 3
            title_3 = title.get(3)

            self.horizontal_cell_merge(ws, start_row, start_row, 0, 5, title_3)
            start_row += 1

            self.insert_cells(ws, self.header_2, start_row, [y for y in range(0, 6)])
            start_row += 1

            self.insert_cells(ws, data_3, start_row, [y for y in range(0, 6)])
            start_row += len(data_3)

            self.horizontal_space_cells(ws, start_row, start_row, 0, 5)

            start_row += 1
            if len(data_4) > 0 and len(data_5) > 0:
                if len(data_4) > 0:
                    # 4
                    title_4 = title.get(4)
                    # self.insert_module_data(ws, title_4, self.header_2, data_4, start_row, 0, 5)
                    self.horizontal_cell_merge(ws, start_row, start_row, 0, 5, title_4)
                    start_row += 1

                    self.insert_cells(ws, self.header_2, start_row, [y for y in range(0, 6)])
                    start_row += 1

                    self.insert_cells(ws, data_4, start_row, [y for y in range(0, 6)])
                    start_row += len(data_4)
                    self.horizontal_space_cells(ws, start_row, start_row, 0, 5)
                    start_row += 1
                if len(data_5) > 0:
                    # 5
                    title_5 = title.get(5)
                    # self.insert_module_data(ws, title_5,  self.header_2, data_5, start_row, 0, 5)
                    self.horizontal_cell_merge(ws, start_row, start_row, 0, 5, title_5)
                    start_row += 1

                    self.insert_cells(ws, self.header_2, start_row, [y for y in range(0, 6)])
                    start_row += 1

                    self.insert_cells(ws, data_5, start_row, [y for y in range(0, 6)])
                    start_row += len(data_5)
                    self.horizontal_space_cells(ws, start_row, start_row, 0, 5)
                    start_row += 1
            else:
                pass
            # 6
            if len(data_6) > 0:
                title_6 = title.get(6)
                # self.insert_module_data(ws, title_6, self.header_6, data_6, start_row, 0, 11)
                self.horizontal_cell_merge(ws, start_row, start_row, 0, 11, title_6)
                start_row += 1

                self.insert_cells(ws, self.header_6, start_row, [y for y in range(0, 12)])
                start_row += 1

                self.insert_cells(ws, data_6, start_row, [y for y in range(0, 12)])
                start_row += len(data_6)
                self.horizontal_space_cells(ws, start_row, start_row, 0, 11)
                start_row += 1
            if len(data_7) > 0 and len(data_8) > 0:
                if len(data_7) > 0:
                    # 7
                    title_7 = title.get(7)
                    # self.insert_module_data(ws, title_7, self.header_6, data_7, start_row, 0, 11)
                    self.horizontal_cell_merge(ws, start_row, start_row, 0, 11, title_7)
                    start_row += 1

                    self.insert_cells(ws, self.header_6, start_row, [y for y in range(0, 12)])
                    start_row += 1

                    self.insert_cells(ws, data_7, start_row, [y for y in range(0, 12)])
                    start_row += len(data_7)
                    self.horizontal_space_cells(ws, start_row, start_row, 0, 11)
                    start_row += 1
                if len(data_8) > 0:
                    # 8
                    title_8 = title.get(8)
                    # self.insert_module_data(ws, title_8, self.header_6, data_8, start_row, 0, 11)
                    self.horizontal_cell_merge(ws, start_row, start_row, 0, 11, title_8)
                    start_row += 1

                    self.insert_cells(ws, self.header_6, start_row, [y for y in range(0, 12)])
                    start_row += 1

                    self.insert_cells(ws, data_8, start_row, [y for y in range(0, 12)])
                    start_row += len(data_8)
                    self.horizontal_space_cells(ws, start_row, start_row, 0, 11)
                    start_row += 1
            else:
                pass
            file_path = self.get_file_name(task_key, time_rang, key)
            self.wb.save(file_path[0])  # 保存xls
            result.append([key, mail, file_path[0], file_path[1]])
        return result

    def insert_module_data(self, ws, title, header, data, start_row, start_col, end_col):
        self.horizontal_cell_merge(ws, start_row, start_row, start_col, end_col, title)
        start_row += 1
        self.insert_cells(ws, header, start_row, [y for y in range(start_col, (end_col + 1))])
        start_row += 1
        self.insert_cells(ws, data, start_row, [y for y in range(start_col, (end_col + 1))])
        start_row += len(data)
        self.horizontal_space_cells(ws, start_row, start_row, start_col, end_col)
        start_row += 1

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

    def sceptical_insert_cells(self, ws, data, start_row, cols):
        for x in data:
            col_index = 0
            for col in cols:
                ws.col(col).width = 150 * 30  # 定义列宽
                ws.write(start_row, col, str(x[col_index]),
                style=self.default_style)
                col_index += 1
            start_row += 1


if __name__ == '__main__':
    xu = XlwtUtil()
    result = {}
    data = result.get('data')
    for x in xu.create_excel(data, ['2020-03-12', '2020-03-13'], 2):
        print(x)