#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 = self.save_path_create() 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)