|
|
#!/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', 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 = [15, 15, 10, 15, 15, 5, 20, 10, 10]
|
|
|
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 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 = 50
|
|
|
# self.ws.row_dimensions[row_number].width = 80
|
|
|
# 遍历每一个对象的长度
|
|
|
col_ = 1
|
|
|
for key_ in keys_list:
|
|
|
# 写入值
|
|
|
try:
|
|
|
value = data_list[row_number - start_row][key_]
|
|
|
except:
|
|
|
value = "/"
|
|
|
self.ws.cell(
|
|
|
row=row_number, column=col_).value = value
|
|
|
# 设置文本水平居中, 垂直居中
|
|
|
if key_ == "acceptace":
|
|
|
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)
|
|
|
# self.ws.row_dimensions[row_number].width = 256*width_list[col_-1]
|
|
|
# self.ws.cell(row=row_number, column=col_).width = int(width_list[col_-1])
|
|
|
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")
|
|
|
if data_list[0].get("gg_ph"):
|
|
|
self.set_style(title="Sheet", ty=2)
|
|
|
else:
|
|
|
self.set_style(title="Sheet")
|
|
|
file_name = kwargs.pop("file_name")
|
|
|
if len(key_list) < 7:
|
|
|
for _ in range(7-len(key_list)):
|
|
|
key_list.append("/")
|
|
|
finds_name.append("/")
|
|
|
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 "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
|
|
|
|
|
|
|
|
|
|
|
|
# # 导出用户数据报表接口
|
|
|
# @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)))
|
