elab_mvp/xlwt_util.py

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.styleOK)
            # 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 + len(rows) - 1,
            start_col,
            end_col,
            content,
            self.styleOK)
            # 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:
        """
        new_file_name = self.save_path_create()
        if task_key == 1:
            return '{}/移动案场订阅日报{}_{}.xlsx'.format(new_file_name, time_range[0], name)
        elif task_key in (2, 3):
            return '{}/移动案场订阅周报_{}至{}.xlsx'.format(new_file_name, time_range[0], time_range[1])

    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):
        result = []
        for key in data_dict.keys():
            self.wb = xlwt.Workbook()
            values = data_dict.get(key)
            data_1 = values.get('1')
            data_2 = values.get('2')
            data_3 = values.get('3')
            data_4 = values.get('4')
            data_5 = values.get('5')
            data_6 = values.get('6')
            data_7 = values.get('7')
            data_8 = values.get('8')
            mail = values.get('0')
            start_row = 0
            global title
            global sheet_name
            if task_key in (2, 3):
                # 周报
                sheet_name = '周报'
                title = self.title.get('week')
            elif task_key == 1:
                # 日报
                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, data_1[col_index], self.styleOK)
                    # 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)  # 保存xls
            result.append([key, mail, file_path])
        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:
                value = data[index][col]
                if value is None:
                    value = 0
                ws.col(col).width = 150 * 30  # 定义列宽
                ws.write(start_row, col, value, self.styleOK)
                # style = self.set_style('Times New Roman',
                #                        200,
                #                        bold=False,
                #                        format_str='', align='')
            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, x[col_index], self.styleOK)
                # style = self.set_style('Times New Roman',
                #                        200,
                #                        bold=False,
                #                        format_str='', align='')
                col_index += 1
            start_row += 1


if __name__ == '__main__':
    xu = XlwtUtil()
    data = {"周菲菲": {"1": [281.0, 31, 16, 6, 54.0, 9.0, 4.0, 1.0, 227.0, 22.0, 12.0, 6.0], "2": [[52, "龙城金茂府", 281.0, 31.0, 16.0, 7.0]], "3": [[27, "龙城金茂府", 79769.0, 13972.0, 4412.0, 1745.0]], "4": [[26, "龙城金茂府", 227.0, 22.0, 12.0, 6.0]], "5": [[52, "龙城金茂府", 54.0, 9.0, 4.0, 1.0]], "6": [["龙城金茂府", 9, 0, 1, 0, 7, 0, 0, 1, 0, 0, 0]], "7": [["龙城金茂府", 9, 0, 1, 0, 7, 0, 0, 1, 0, 0, 0]], "8": []}, "王志娟": {"1": [281.0, 31, 16, 6, 54.0, 9.0, 4.0, 1.0, 227.0, 22.0, 12.0, 6.0], "2": [[52, "龙城金茂府", 281.0, 31.0, 16.0, 7.0]], "3": [[27, "龙城金茂府", 79769.0, 13972.0, 4412.0, 1745.0]], "4": [[26, "龙城金茂府", 227.0, 22.0, 12.0, 6.0]], "5": [[52, "龙城金茂府", 54.0, 9.0, 4.0, 1.0]], "6": [["龙城金茂府", 9, 0, 1, 0, 7, 0, 0, 1, 0, 0, 0]], "7": [["龙城金茂府", 9, 0, 1, 0, 7, 0, 0, 1, 0, 0, 0]], "8": []}, "黄文娟": {"1": [281.0, 31, 16, 6, 54.0, 9.0, 4.0, 1.0, 227.0, 22.0, 12.0, 6.0], "2": [[52, "龙城金茂府", 281.0, 31.0, 16.0, 7.0]], "3": [[27, "龙城金茂府", 79769.0, 13972.0, 4412.0, 1745.0]], "4": [[26, "龙城金茂府", 227.0, 22.0, 12.0, 6.0]], "5": [[52, "龙城金茂府", 54.0, 9.0, 4.0, 1.0]], "6": [["龙城金茂府", 9, 0, 1, 0, 7, 0, 0, 1, 0, 0, 0]], "7": [["龙城金茂府", 9, 0, 1, 0, 7, 0, 0, 1, 0, 0, 0]], "8": []}, "孟蓓": {"1": [11.0, 8, 5, 4, 11.0, 8.0, 5.0, 3.0, 0, 0, 0, 0], "2": [[68, "朝阳金茂中心", 11.0, 8.0, 5.0, 3.0]], "3": [[68, "朝阳金茂中心", 1766.0, 401.0, 400.0, 266.0]], "4": [], "5": [[68, "朝阳金茂中心", 11.0, 8.0, 5.0, 3.0]], "6": [], "7": [], "8": []}, "宋雨霏": {"1": [10.0, 8, 5, 6, 9.0, 7.0, 5.0, 4.0, 0, 0, 0, 0], "2": [[71, "丰台金茂广场", 9.0, 7.0, 5.0, 4.0]], "3": [[65, "丰台金茂广场", 2759.0, 499.0, 498.0, 329.0]], "4": [], "5": [[71, "丰台金茂广场", 9.0, 7.0, 5.0, 4.0]], "6": [], "7": [], "8": []}, "周会芳": {"1": [561.0, 31, 12, 10, 559.0, 30.0, 12.0, 9.0, 2.0, 2.0, 0, 0], "2": [[7, "滨湖金茂悦", 561.0, 32.0, 12.0, 9.0]], "3": [[7, "滨湖金茂悦", 33978.0, 2859.0, 2456.0, 1967.0]], "4": [[59, "滨湖金茂悦", 2.0, 2.0, None, None]], "5": [[7, "滨湖金茂悦", 559.0, 30.0, 12.0, 9.0]], "6": [["滨湖金茂悦", 4, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0]], "7": [], "8": [["滨湖金茂悦", 4, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0]]}, "孙闯": {"1": [255.0, 76, 51, 7, 252.0, 76.0, 51.0, 7.0, 3.0, 1.0, 0, 0], "2": [[21, "福田金茂府", 255.0, 77.0, 51.0, 7.0]], "3": [[29, "福田金茂府", 12413.0, 1075.0, 979.0, 432.0]], "4": [[56, "福田金茂府", 3.0, 1.0, None, None]], "5": [[21, "福田金茂府", 252.0, 76.0, 51.0, 7.0]], "6": [["福田金茂府", 38, 0, 18, 0, 20, 0, 0, 0, 0, 0, 0]], "7": [], "8": [["福田金茂府", 38, 0, 18, 0, 20, 0, 0, 0, 0, 0, 0]]}, "路颖颖": {"1": [62.0, 15, 6, 5, 37.0, 13.0, 6.0, 4.0, 25.0, 2.0, 0, 0], "2": [[60, "新城虹口金茂府", 62.0, 15.0, 6.0, 4.0]], "3": [[30, "新城虹口金茂府", 30507.0, 2489.0, 2724.0, 1315.0]], "4": [[42, "新城虹口金茂府", 25.0, 2.0, None, None]], "5": [[60, "新城虹口金茂府", 37.0, 13.0, 6.0, 4.0]], "6": [], "7": [], "8": []}, "陈博华": {"1": [62.0, 15, 6, 5, 37.0, 13.0, 6.0, 4.0, 25.0, 2.0, 0, 0], "2": [[60, "新城虹口金茂府", 62.0, 15.0, 6.0, 4.0]], "3": [[30, "新城虹口金茂府", 30507.0, 2489.0, 2724.0, 1315.0]], "4": [[42, "新城虹口金茂府", 25.0, 2.0, None, None]], "5": [[60, "新城虹口金茂府", 37.0, 13.0, 6.0, 4.0]], "6": [], "7": [], "8": []}, "夏伟春": {"1": [23.0, 4, 3, 2, 22.0, 3.0, 3.0, 2.0, 1.0, 1.0, 0, 0], "2": [[64, "Tod国际新城", 23.0, 4.0, 3.0, 2.0]], "3": [[70, "Tod国际新城", 1637.0, 142.0, 105.0, 73.0]], "4": [[66, "Tod国际新城", 1.0, 1.0, None, None]], "5": [[64, "Tod国际新城", 22.0, 3.0, 3.0, 2.0]], "6": [], "7": [], "8": []}, "段丽娟": {"1": [827.0, 99, 75, 16, 93.0, 19.0, 12.0, 10.0, 734.0, 80.0, 63.0, 8.0], "2": [[44, "长安金茂府", 827.0, 99.0, 75.0, 18.0]], "3": [[39, "长安金茂府", 373491.0, 22491.0, 23357.0, 9478.0]], "4": [[9, "长安金茂府", 734.0, 80.0, 63.0, 8.0]], "5": [[44, "长安金茂府", 93.0, 19.0, 12.0, 10.0]], "6": [["长安金茂府", 27, 5, 12, 1, 3, 0, 0, 6, 0, 0, 0]], "7": [["长安金茂府", 26, 5, 11, 1, 3, 0, 0, 6, 0, 0, 0]], "8": [["长安金茂府", 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0]]}, "韩梦圆": {"1": [827.0, 99, 75, 16, 93.0, 19.0, 12.0, 10.0, 734.0, 80.0, 63.0, 8.0], "2": [[44, "长安金茂府", 827.0, 99.0, 75.0, 18.0]], "3": [[39, "长安金茂府", 373491.0, 22491.0, 23357.0, 9478.0]], "4": [[9, "长安金茂府", 734.0, 80.0, 63.0, 8.0]], "5": [[44, "长安金茂府", 93.0, 19.0, 12.0, 10.0]], "6": [["长安金茂府", 27, 5, 12, 1, 3, 0, 0, 6, 0, 0, 0]], "7": [["长安金茂府", 26, 5, 11, 1, 3, 0, 0, 6, 0, 0, 0]], "8": [["长安金茂府", 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0]]}, "金晶": {"1": [26083.0, 3069, 296, 167, 10088.0, 932.0, 485.0, 298.0, 1140.0, 174.0, 80.0, 28.0], "2": [[2, "公园大道", 1009.0, 119.0, 70.0, 44.0], [3, "壹号半岛", 607.0, 50.0, 26.0, 20.0], [4, "重庆林山郡", 719.0, 102.0, 66.0, 40.0], [5, "壹江郡", 444.0, 79.0, 67.0, 39.0], [6, "长嘉汇", 388.0, 28.0, 11.0, 9.0], [7, "约克郡", 128.0, 10.0, 8.0, 3.0], [8, "江与城", 107.0, 8.0, 3.0, 3.0], [8, "CNP", 5111.0, 369.0, 189.0, 118.0], [9, "前湾", 810.0, 90.0, 44.0, 20.0], [10, "天湖湾", 107.0, 8.0, 3.0, 3.0], [11, "云山岳", 95.0, 21.0, 11.0, 3.0], [12, "嘉景湾", 98.0, 22.0, 14.0, 6.0], [13, "越城天地", 60.0, 5.0, 3.0, 2.0], [14, "新江与城", 58.0, 2.0, 1.0, 0.0], [15, "香港置地商业商务", 32.0, 7.0, 4.0, 3.0], [16, "梦想特区", 47.0, 4.0, 3.0, 1.0], [17, "天誉·环球汇", 14.0, 3.0, 2.0, 2.0]], "3": [[0, "CNP", 364570.0, 20577.0, 20496.0, 3944.0], [1, "品牌层", 147373.0, 14857.0, 11047.0, 882.0], [2, "壹号半岛", 46971.0, 3988.0, 2065.0, 1347.0], [3, "公园大道", 31162.0, 2529.0, 1540.0, 946.0], [4, "长嘉汇", 28837.0, 2435.0, 962.0, 605.0], [5, "前湾", 48547.0, 4388.0, 2705.0, 911.0], [6, "壹江郡", 21859.0, 2112.0, 1036.0, 660.0], [7, "江与城", 15927.0, 1772.0, 417.0, 231.0], [8, "云山岳", 15604.0, 2153.0, 896.0, 474.0], [9, "重庆林山郡", 80098.0, 11137.0, 11636.0, 5580.0], [10, "梦想特区", 10805.0, 1054.0, 557.0, 272.0], [11, "嘉景湾", 7931.0, 1203.0, 487.0, 274.0], [12, "约克郡", 6157.0, 975.0, 322.0, 182.0], [13, "天湖湾", 6108.0, 690.0, 337.0, 162.0], [13, "CNP", 364927.0, 20860.0, 20496.0, 3944.0], [14, "越城天地", 5091.0, 1282.0, 304.0, 182.0], [15, "新江与城", 3364.0, 591.0, 169.0, 135.0], [16, "天誉·环球汇", 2486.0, 463.0, 219.0, 129.0], [17, "香港置地商业商务", 678.0, 129.0, 151.0, 75.0]], "4": [[0, "前湾", 707.0, 82.0, 41.0, 18.0], [1, "重庆林山郡", 241.0, 32.0, 18.0, 8.0], [2, "壹号半岛", 74.0, 9.0, 3.0, 2.0], [3, "梦想特区", 29.0, 3.0, 2.0, None], [4, "嘉景湾", 23.0, 8.0, 2.0, None], [5, "云山岳", 18.0, 6.0, 6.0, None], [6, "公园大道", 16.0, 15.0, None, None], [7, "壹江郡", 15.0, 8.0, 5.0, None], [8, "CNP", 9.0, 5.0, None, None], [9, "天湖湾", 5.0, 3.0, None, None], [10, "约克郡", 2.0, 2.0, 3.0, None], [11, "越城天地", 1.0, 1.0, None, None]], "5": [[0, "CNP", 5102.0, 364.0, 189.0, 118.0], [1, "品牌层", 1394.0, 179.0, 40.0, 10.0], [2, "公园大道", 993.0, 104.0, 70.0, 44.0], [3, "壹号半岛", 533.0, 41.0, 23.0, 18.0], [4, "重庆林山郡", 478.0, 70.0, 48.0, 32.0], [5, "壹江郡", 429.0, 71.0, 62.0, 39.0], [6, "长嘉汇", 388.0, 28.0, 11.0, 9.0], [7, "约克郡", 126.0, 8.0, 5.0, 3.0], [8, "江与城", 107.0, 8.0, 3.0, 3.0], [9, "前湾", 103.0, 8.0, 3.0, 2.0], [10, "天湖湾", 102.0, 5.0, 3.0, 3.0], [11, "云山岳", 77.0, 15.0, 5.0, 3.0], [12, "嘉景湾", 75.0, 14.0, 12.0, 6.0], [13, "越城天地", 59.0, 4.0, 3.0, 2.0], [14, "新江与城", 58.0, 2.0, 1.0, 0.0], [15, "香港置地商业商务", 32.0, 7.0, 4.0, 3.0], [16, "梦想特区", 18.0, 1.0, 1.0, 1.0], [17, "天誉·环球汇", 14.0, 3.0, 2.0, 2.0]], "6": [["CNP", 186, 47, 101, 8, 23, 0, 2, 3, 1, 1, 0], ["公园大道", 54, 30, 21, 0, 2, 0, 0, 0, 1, 0, 0], ["壹江郡", 52, 1, 50, 0, 1, 0, 0, 0, 0, 0, 0], ["品牌层", 44, 3, 21, 8, 1, 1, 1, 3, 5, 1, 0], ["重庆林山郡", 42, 1, 20, 0, 19, 0, 2, 0, 0, 0, 0], ["前湾", 39, 26, 11, 1, 1, 0, 0, 0, 0, 0, 0], ["壹号半岛", 10, 8, 1, 0, 1, 0, 0, 0, 0, 0, 0], ["嘉景湾", 6, 5, 1, 0, 0, 0, 0, 0, 0, 0, 0], ["云山岳", 3, 0, 1, 2, 0, 0, 0, 0, 0, 0, 0], ["约克郡", 3, 0, 1, 0, 0, 0, 0, 2, 0, 0, 0], ["长嘉汇", 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0]], "7": [["前湾", 38, 26, 10, 1, 1, 0, 0, 0, 0, 0, 0], ["重庆林山郡", 15, 1, 13, 0, 0, 0, 1, 0, 0, 0, 0], ["云山岳", 3, 0, 1, 2, 0, 0, 0, 0, 0, 0, 0], ["约克郡", 2, 0, 0, 0, 0, 0, 0, 2, 0, 0, 0], ["壹号半岛", 2, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0], ["嘉景湾", 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0]], "8": [["CNP", 186, 47, 101, 8, 23, 0, 2, 3, 1, 1, 0], ["公园大道", 54, 30, 21, 0, 2, 0, 0, 0, 1, 0, 0], ["壹江郡", 52, 1, 50, 0, 1, 0, 0, 0, 0, 0, 0], ["品牌层", 44, 3, 21, 8, 1, 1, 1, 3, 5, 1, 0], ["重庆林山郡", 27, 0, 7, 0, 19, 0, 1, 0, 0, 0, 0], ["壹号半岛", 8, 8, 0, 0, 0, 0, 0, 0, 0, 0, 0], ["嘉景湾", 5, 5, 0, 0, 0, 0, 0, 0, 0, 0, 0], ["前湾", 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0], ["约克郡", 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0], ["长嘉汇", 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0]]}, "魏总": {"1": [1979.0, 140, 79, 32, 723.0, 36.0, 22.0, 16.0, 1256.0, 109.0, 59.0, 20.0], "2": [[5, "金茂国际新城", 1979.0, 145.0, 81.0, 36.0]], "3": [[22, "金茂国际新城", 156175.0, 8121.0, 10137.0, 4491.0]], "4": [[1, "金茂国际新城", 1256.0, 109.0, 59.0, 20.0]], "5": [[5, "金茂国际新城", 723.0, 36.0, 22.0, 16.0]], "6": [["金茂国际新城", 59, 50, 6, 0, 1, 0, 0, 1, 1, 0, 0]], "7": [["金茂国际新城", 52, 46, 3, 0, 1, 0, 0, 1, 1, 0, 0]], "8": [["金茂国际新城", 7, 4, 3, 0, 0, 0, 0, 0, 0, 0, 0]]}, "李欣凌": {"1": [1979.0, 140, 79, 32, 723.0, 36.0, 22.0, 16.0, 1256.0, 109.0, 59.0, 20.0], "2": [[5, "金茂国际新城", 1979.0, 145.0, 81.0, 36.0]], "3": [[22, "金茂国际新城", 156175.0, 8121.0, 10137.0, 4491.0]], "4": [[1, "金茂国际新城", 1256.0, 109.0, 59.0, 20.0]], "5": [[5, "金茂国际新城", 723.0, 36.0, 22.0, 16.0]], "6": [["金茂国际新城", 59, 50, 6, 0, 1, 0, 0, 1, 1, 0, 0]], "7": [["金茂国际新城", 52, 46, 3, 0, 1, 0, 0, 1, 1, 0, 0]], "8": [["金茂国际新城", 7, 4, 3, 0, 0, 0, 0, 0, 0, 0, 0]]}}

    for x in xu.create_excel(data, ['2020-03-01', '2020-03-07'], 1):
        print(x)