|
|
# -*- 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
|
