#!/usr/bin/env python # -*- encoding: utf-8 -*- ''' @Date:2022/08/27 11:05:06 ''' import sys from types import CellType 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 # 生成ascii_uppercase生成所有大写字母 self.upper_string = string.ascii_uppercase # 水平对齐, 居中对齐 self.alignment_style = Alignment( horizontal='center', vertical='center') # 定义字体 self.font_size = Font(size=9) for col in self.upper_string: self.ws.column_dimensions[col].width = 10 # 创建第三行, 需要传入一个列表用来写入单元格的值 def create_row3(self, data_list): for data in range(1, len(data_list) + 1): # 第三行写入值 self.ws.cell(row=1, column=data).value = data_list[data - 1] # 创建多行固定样式 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: value = data_list[row_number - start_row][key_] except: value = "" try: value = int(value) self.ws.cell(row=row_number, column=col_).number_format = '0' except: pass self.ws.cell(row=row_number, column=col_).value = value 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: 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 == 2: self.ws[col_ + str(row_)].value = '出库' elif b == 3: 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 if b: if len(b) == 19: self.ws[col_ + str(row_)].value = b.replace('T', ' ') else: self.ws[col_ + str(row_)].value = "/" # 编辑用户管理列为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") self.create_row3(finds_name) self.create_multiple_rows(2, data_list, key_list) for i in key_list: if "date" in i: self.editor_time(key_list, i) if "status" in i: self.editor_status(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 if __name__ == '__main__': obj = ReportData() data_dict = { "data_list":[ {"name": "申购单编号", "purity":"11~15%", "export_count":"2", "production_date":"2023/01/01", "shelf_life": '5'} ], "key_list": [ "remark7", "remark8", "category", "remark9", "name", "english_name","purity","cas_number","standard_code", "unit_code","speci","net_weight_unit", "export_count","remark6", "production_date", "shelf_life", "expiration_date", "price", "is_supervise", "remain", "manufacturer", "storage_condition", "remark10" ], "finds_name": [ "申购单编号","采购单编号", "药剂类别", "所属项目", "药剂名称", "英文名称", "纯度等级", "CAS码", "批号", "编号", "规格", "规格单位", "导入数量", "导入单位", "生产日期", "保质期（天）", "到期日期", "价格（元）", "重点监管", "预估质量(g/ml)", "生产厂商", "储存条件", "包装是否完好、标签是否清晰" ], } obj = obj.build_file(**data_dict) obj.save("测试表")