|
|
#!/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)))
|
