# -*- coding: utf-8 -*- # @Time : 2023/9/26 10:58 # @Author : tx # @File : base.py # @Description : import string from io import BytesIO import urllib.parse from openpyxl.workbook import Workbook from openpyxl.styles import Alignment, Side, Border, Font, PatternFill from helper.utils import timezone_now from helper import usb from datetime import datetime, timezone class ReportExport: def __init__(self): self.title = "" self.stateDict = {} # 新建一个workbook self.wb = Workbook() # 定义设置样式 self.a = 0 self.styleTrue = '\u2611' # 打勾 self.styleFalse = '\u25A1' # 空框 def set_style(self, title='sheet1', ty=1): # 如果是第一次调用则删除默认创建的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_style2 = Alignment( horizontal='left', vertical='top', wrapText=True) self.alignment_style = Alignment( horizontal='center', vertical='center', wrapText=True) # 定义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) width_list = [20, 15, 10, 15, 15, 22, 15, 22, 15, 15] for col in self.upper_string: try: width = width_list[self.upper_string.index(col)] except: width = 20 self.ws.column_dimensions[col].width = width def editor_state(self, col_): """ 状态解析 :param col_: :return: """ self.max_lines = self.ws.max_row for row_ in range(2, self.max_lines + 1): b = self.ws[col_ + str(row_)].value if "状态" in str(b): continue self.ws[col_ + str(row_)].value = self.stateDict.get(b) def create_row(self, data_list,title=''): if title: n=2 self.ws.merge_cells(start_row=1, end_row=1, start_column=1, end_column=len(data_list)) # 写入值 self.ws.cell(row=1, column=1).value = title self.ws['A1'].alignment = self.alignment_style self.ws['A1'].font = Font(size=16, bold=True) else: n=1 for data in range(n, len(data_list) + n): if n ==2: data =data-1 # 第一行写入值 self.ws.cell(row=n, column=data).value = data_list[data - 1] # 设置文本水平居中, 垂直居中 self.ws.cell(row=n, column=data).alignment = self.alignment_style # 设置字体加粗 self.ws.cell(row=n, column=data).font = Font(bold=True, size=9) # 边框样式 self.ws.cell(row=n, column=data).border = self.border_style def create_multiple_rows(self, start_row, data_list, keys_list): # 从第2行创建 for row_number in range(start_row, len(data_list) + start_row): # 设置每一行的行高 self.ws.row_dimensions[row_number].height = 60 # 遍历每一个对象的长度 col_ = 1 for key_ in keys_list: # 写入值 try: if key_ =='ysr': value ='李世光' elif key_ =='info': value=f'{self.styleTrue}标识清晰\r\n{self.styleTrue}外观完整、无破损\r\n{self.styleTrue}形状无明显改变\r\n{self.styleTrue}符合申购要求\r\n{self.styleFalse}其他' elif key_ =='jg': value=f'{self.styleTrue}合格\r\n{self.styleFalse}不合格' else: value = data_list[row_number - start_row][key_] if isinstance(value, datetime) and value.tzinfo is not None: # 如果是带有时区信息的datetime对象，则移除时区信息 value = value.replace(tzinfo=None) except: value = "/" self.ws.cell(row=row_number, column=col_).value = value # 设置文本水平居中, 垂直居中 if key_ == "acceptace" or key_ == "info" or key_ == "jg": self.ws.cell(row=row_number, column=col_).alignment = self.alignment_style2 else: 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 build_file(self,title='', **kwargs): self.set_style(title="Sheet") # 数据条目 data_list = kwargs.pop("data_list") # 英文列名 key_list = kwargs.pop("key_list") # 中文列名 finds_name = kwargs.pop("finds_list") # 创建标题 self.create_row(finds_name,title) # 创建数据 if title: n=3 else: n=2 self.create_multiple_rows(n, data_list, key_list) for i in key_list: if "state" in i: # 如果行超过25行异常处理 if key_list.index(i) <= 25: self.editor_state(string.ascii_uppercase[key_list.index(i)]) else: # AA AB AC AD... divisor = key_list.index(i) // 25 - 1 string_divisor = string.ascii_uppercase[divisor] remainder = key_list.index(i) % 25 - 1 string_remainder = string.ascii_uppercase[remainder] self.editor_state(f"{string_divisor}{string_remainder}") def export(self, data, download_type='usb'): """ 导出 :param data: :param download_type: :return: """ binary = BytesIO() self.wb.save(binary) self.wb.close() binary.seek(0) filename = f"{timezone_now().strftime('%Y%m%d%H%M%S')}-{self.title}-报表.xlsx" if download_type == 'usb': try: usb.put_in(filename, binary) return 200, "导出成功" except usb.DeviceNotFound: return 404, "U盘未找到" encoded_filename = urllib.parse.quote(filename) return binary, encoded_filename