#!/usr/bin/env python # -*- encoding: utf-8 -*- ''' @Date:2022/08/27 11:05:06 ''' import sys sys.path.append('.') import string import os import datetime from openpyxl.workbook import Workbook from openpyxl.styles import Alignment, Side, Border, Font, PatternFill class ReportData: def __init__(self): # 新建一个workbook self.wb = Workbook() # 定义设置样式 self.a = 0 def set_style(self, title='sheet1'): # 如果是第一次调用则删除默认创建的sheet表 if self.a == 0: self.wb.remove(self.wb['Sheet']) self.a += 1 # 创建第几个sheet self.ws = self.wb.create_sheet() # 设置sheet的标题 self.ws.title = title # 设置1 2 3 行固定的高度 self.ws.row_dimensions[1].height = 35 self.ws.row_dimensions[2].height = 25 self.ws.row_dimensions[3].height = 28 # 水平对齐, 居中对齐 self.alignment_style = Alignment( horizontal='center', vertical='center') # 定义Border边框样式 left, right, top, bottom = [Side(style='thin', color='000000')] * 4 self.border_style = Border( left=left, right=right, top=top, bottom=bottom) # 设置列宽 # 生成前14个大写字母 ascii_uppercase生成所有大写字母 self.upper_string = string.ascii_uppercase[:15] # 定义字体 self.font_size = Font(size=9) for col in self.upper_string: self.ws.column_dimensions[col].width = 20 # 创建第一行 def create_row1(self, value, length): # 合并第1行1-14列单元格 self.ws.merge_cells(start_row=1, end_row=1, start_column=1, end_column=length) # 写入值 self.ws.cell(row=1, column=1).value = value self.ws['A1'].alignment = self.alignment_style self.ws['A1'].font = Font(size=16, bold=True) self.create_row2(length) def create_row2(self, length): # 第2行写入值 now_time = datetime.datetime.now().strftime('%Y%m%d %H%M') # 格式化时间为中文年月日 now_time = now_time[:4] + '年' + now_time[4:6] + '月' + \ now_time[6:8] + '日' + now_time[8:11] + '时' + now_time[11:13] + '分' self.ws.cell(row=2, column=1).value = '报表导出时间：{}'.format(now_time) self.ws.cell(row=2, column=3).value = '终端系统版本:3.1' self.ws.cell(row=2, column=5).value = '' self.ws.cell(row=2, column=7).value = '报表导出位置：终端' # 合并单元格 x = 1 while x < 6: self.ws.merge_cells(start_row=2, end_row=2, start_column=x, end_column=x + 1) x += 2 self.ws.merge_cells(start_row=2, end_row=2, start_column=7, end_column=length) # 遍历取1, 3, 5, 7为第二行添加样式 for x in range(1, 8, 2): self.ws.cell(row=2, column=x).font = Font(size=9) # 水平对齐 垂直对齐 self.ws.cell(row=2, column=x).alignment = self.alignment_style if x < 7: # 边框样式 self.ws.cell(row=2, column=x).border = self.border_style self.ws.cell(row=2, column=x + 1).border = self.border_style else: # 因为第2行第7-14列是合并的单元格 所以需要循环添加边框样式 while x < length + 1: self.ws.cell(row=2, column=x).border = self.border_style x += 1 # 创建第三行, 需要传入一个列表用来写入单元格的值 def create_row3(self, data_list): for data in range(1, len(data_list) + 1): # 第三行写入值 self.ws.cell(row=3, column=data).value = data_list[data - 1] # 设置文本水平居中, 垂直居中 self.ws.cell(row=3, column=data).alignment = self.alignment_style # 设置字体加粗 self.ws.cell(row=3, column=data).font = Font(bold=True, size=9) # 背景颜色 self.ws.cell(row=3, column=data).fill = PatternFill( fill_type='solid', fgColor='EE9A49') # 边框样式 self.ws.cell(row=3, column=data).border = self.border_style # 创建多行固定样式 start_row从第几行开始有规律 传入一个数据库获取的列表对象的值, 传入一个数据对象keys列表 def create_multiple_rows(self, start_row, data_list, keys_list): # 从第4行创建 for row_number in range(start_row, len(data_list) + start_row): # 设置每一行的行高 self.ws.row_dimensions[row_number].height = 18 # 遍历每一个对象的长度 col_ = 1 for key_ in keys_list: # 写入值 try: # 判断是否时datetime 类型, if isinstance(dict(data_list[row_number - start_row]).get(key_), datetime.datetime): value = dict(data_list[row_number - start_row]).get(key_).replace(tzinfo=None) # 判断是否时state elif key_=='state': value = dict(data_list[row_number - start_row]).get(key_).value else: value = dict(data_list[row_number - start_row]).get(key_) except : value = "/" self.ws.cell(row=row_number, column=col_).value = value # 设置文本水平居中, 垂直居中 self.ws.cell(row=row_number, column=col_).alignment = self.alignment_style # 设置边框样式 self.ws.cell(row=row_number, column=col_).border = self.border_style # 设置字体大小 self.ws.cell(row=row_number, column=col_).font = Font(size=9) col_ += 1 # 保存文件 def save(self, file_path): self.wb.save('{}.xlsx'.format(file_path)) self.close() # 关闭文件 def close(self): self.wb.close() # 替换空白 def replace_space(self, length): self.max_lines = self.ws.max_row upper_letter_str = string.ascii_uppercase[:length] for upper_letter in upper_letter_str: print(upper_letter) for row_ in range(1, self.max_lines + 1): b = self.ws[upper_letter + str(row_)].value # print(b) if not b: self.ws[upper_letter + str(row_)].value = 'null' # 编辑excel表格中列药剂状态1 2 3 替换为在库 出库 def editor_status(self, col_): self.max_lines = self.ws.max_row for row_ in range(4, self.max_lines + 1): b = self.ws[col_ + str(row_)].value if b == 1: self.ws[col_ + str(row_)].value = '在库' elif b == 0: self.ws[col_ + str(row_)].value = '待入库' elif b == 2: self.ws[col_ + str(row_)].value = '出库' elif b == 3: self.ws[col_ + str(row_)].value = '空瓶' elif b == 4: self.ws[col_ + str(row_)].value = '销毁' # 编辑 温度报警类型 0，1 替换为正常和异常 def eidtor_temperature_type(self,col_): self.max_lines = self.ws.max_row for row_ in range(4,self.max_lines +1): b = self.ws[col_ + str(row_)].value if b==0: self.ws[col_ + str(row_)].value = "正常" elif b ==1 : self.ws[col_ + str(row_)].value = "异常" # 编辑药剂重点监管列为1 0 替换为是 否 def editor_isSupervise(self, col_): self.max_lines = self.ws.max_row for row_ in range(4, self.max_lines + 1): b = self.ws[col_ + str(row_)].value if b == 1: self.ws[col_ + str(row_)].value = '是' elif b == 0: self.ws[col_ + str(row_)].value = '否' # 编辑操作类型 1 2 3 入库 领用 归还 def editor_RecordType(self, col_): self.max_lines = self.ws.max_row for row_ in range(4, self.max_lines + 1): b = self.ws[col_ + str(row_)].value if b == 1: self.ws[col_ + str(row_)].value = '入库' elif b == 2: self.ws[col_ + str(row_)].value = '领用' elif b == 3: self.ws[col_ + str(row_)].value = '归还' # 替换sqlAlchemy时间格式 def editor_time(self, keys_list, params): self.max_lines = self.ws.max_row for row_ in range(4, self.max_lines + 1): col_ = string.ascii_uppercase[keys_list.index(params)] b = self.ws[col_ + str(row_)].value # print('sssssssssssssssssssssssssssssssssssssssssassssssssssssssss:::', b,type(b)) try: if len(b) == 19: self.ws[col_ + str(row_)].value = b.replace('T', ' ') except Exception as e : pass # print(e) # 编辑用户管理列为1 0 替换为正常 禁用 def editor_user_status(self, col_): self.max_lines = self.ws.max_row for row_ in range(4, self.max_lines + 1): b = self.ws[col_ + str(row_)].value if b == 1: self.ws[col_ + str(row_)].value = '正常' elif b == 0: self.ws[col_ + str(row_)].value = '禁用' # 构建文件内容 def build_file(self,**kwargs): data_list = kwargs.pop("data_list") key_list = kwargs.pop("key_list") finds_name = kwargs.pop("finds_name") self.set_style(title="Sheet") file_name = kwargs.pop("file_name") self.create_row1(file_name, len(finds_name)) self.create_row3(finds_name) self.create_multiple_rows(4, data_list, key_list) for i in key_list: if "date" in i: self.editor_time(key_list, i) if "state" in i: self.editor_status(string.ascii_uppercase[key_list.index(i)]) if "temperature_type" in i: self.eidtor_temperature_type(string.ascii_uppercase[key_list.index(i)]) if "is_supervise" in i: self.editor_isSupervise(string.ascii_uppercase[key_list.index(i)]) if "record_type" == i: self.editor_RecordType( string.ascii_uppercase[key_list.index(i)]) return self # 下载文件 @staticmethod def download_excel(filename, chunk_size=512): try: f = open(filename, 'rb') except: return while True: c = f.read(chunk_size) if c: yield c else: f.close() os.remove(filename) break # # 导出用户数据报表接口 # @dataReport.route('/downloadUserReport', methods=["GET", "POST"]) # def downloadUserReport(): # try: # uPath = Utils.getUDiskPath() # if (uPath == ''): # return jsonify(Utils.resultData(1, '未检测到U盘！')) # else: # data_user_list = BllUser().findList().all() # data_user_list = json.loads( # Utils.resultAlchemyData(data_user_list)) # # 创建一个报表类实例 # a = ReportData() # a.set_style(title='Sheet') # data_list = ['工号', '角色', '姓名', '性别', 'QQ', '手机', '邮箱', # '条码', '状态', '最后一次登录时间', ] # a.create_row1('用户角色数据统计表', len(data_list)) # a.create_row3(data_list) # keys_list = ['UserCode', 'RoleName', 'RealName', 'Sex', 'QQ', 'Mobile', 'Email', # 'BarCode', 'IsEnabled', 'LastVisitDate', ] # a.create_multiple_rows(4, data_user_list, keys_list) # a.replace_space(len(data_list)) # a.editor_time(keys_list, 'LastVisitDate') # # 判断用户角色 # for row_ in range(4, a.max_lines + 1): # col_ = string.ascii_uppercase[keys_list.index('UserCode')] # b = a.ws[col_ + str(row_)].value # if b == 'admin': # for col_value in range(1, len(keys_list) + 1): # a.ws.cell(row=row_, column=col_value).fill = PatternFill( # fill_type='solid', fgColor='FF0000') # elif b == 'yanyi': # for col_value in range(1, len(keys_list) + 1): # a.ws.cell(row=row_, column=col_value).fill = PatternFill( # fill_type='solid', fgColor='FFFF00') # # 优化用户性别 # for row_ in range(4, a.max_lines + 1): # col_ = string.ascii_uppercase[keys_list.index('Sex')] # b = a.ws[col_ + str(row_)].value # if b == 0: # a.ws[col_ + str(row_)].value = '女' # else: # a.ws[col_ + str(row_)].value = '男' # a.editor_user_status('I') # file_name = '用户角色数据统计表{}'.format(Utils.UUID()) # a.save(uPath + '/' + file_name) # returnData = Utils.resultData(0, '导出成功') # return jsonify(returnData) # except Exception as e: # return jsonify(Utils.resultData(2, str(e)))