|
|
import urllib.parse
|
|
|
import psutil
|
|
|
import os
|
|
|
import time
|
|
|
import random
|
|
|
import getpass
|
|
|
import platform
|
|
|
from io import BytesIO
|
|
|
from os import path
|
|
|
from app.conf.Setting import settings
|
|
|
import openpyxl
|
|
|
from openpyxl import Workbook
|
|
|
from openpyxl.styles import Alignment
|
|
|
from openpyxl.drawing.image import Image
|
|
|
from openpyxl.styles import Font
|
|
|
from datetime import datetime
|
|
|
import matplotlib.pyplot as plt
|
|
|
from app.lib.Utils import Utils
|
|
|
from openpyxl.styles import PatternFill, Border, Side
|
|
|
|
|
|
system_name = platform.system()
|
|
|
|
|
|
|
|
|
class ExcelExport():
|
|
|
def __init__(self, file_name, data, download_type):
|
|
|
self.file_name = file_name
|
|
|
self.data = data
|
|
|
self.download_type = download_type
|
|
|
self.book = Workbook()
|
|
|
self.side = Side(style='thin', color='000000') # 颜色可以是RGB值或者颜色名称
|
|
|
self.border = Border(left=self.side, right=self.side, top=self.side, bottom=self.side)
|
|
|
|
|
|
def set_title(self, sheet):
|
|
|
"""设置标题"""
|
|
|
for i in range(1, 13):
|
|
|
sheet.column_dimensions[openpyxl.utils.get_column_letter(i)].width = 15
|
|
|
sheet.merge_cells(f'A1:L1')
|
|
|
# sheet.merge_cells(f'A1:{chr(64 + self.culumn)}1')
|
|
|
cell = sheet.cell(row=1, column=1)
|
|
|
cell.value = self.file_name
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
sheet.row_dimensions[1].height = 40
|
|
|
# 设置背景色
|
|
|
fill = PatternFill(start_color="D9E1F4", end_color="D9E1F4", fill_type="solid")
|
|
|
sheet['A1'].fill = fill
|
|
|
# sheet['A1'].border = self.border
|
|
|
|
|
|
def set_column_title(self, sheet, experiment_type):
|
|
|
sheet.merge_cells(f'A2:A8')
|
|
|
cell = sheet.cell(row=2, column=1)
|
|
|
cell.value = '基本信息'
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
if experiment_type == '1':
|
|
|
sheet.merge_cells(f'A9:A12')
|
|
|
else:
|
|
|
sheet.merge_cells(f'A9:A13')
|
|
|
cell = sheet.cell(row=9, column=1)
|
|
|
cell.value = '报告信息'
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
fill = PatternFill(start_color="D9D9D9", end_color="D9D9D9", fill_type="solid")
|
|
|
sheet['A2'].fill = fill
|
|
|
sheet['A9'].fill = fill
|
|
|
# sheet['A2'].border = self.border
|
|
|
# sheet['A9'].border = self.border
|
|
|
|
|
|
def set_columns(self, sheet, title_list):
|
|
|
"""设置列名"""
|
|
|
for i in range(len(title_list)):
|
|
|
sheet.merge_cells(f'B{i + 2}:E{i + 2}')
|
|
|
sheet.merge_cells(f'F{i + 2}:I{i + 2}')
|
|
|
cell = sheet.cell(row=i + 2, column=2)
|
|
|
cell.value = title_list[i]
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
|
|
|
def set_temp(self, sheet, data_item):
|
|
|
"""设置40,60,80,100"""
|
|
|
temp = ['校准物质40℃升温速率', '校准物质60℃升温速率', '校准物质80℃升温速率', '校准物质100℃升温速率']
|
|
|
temp1 = ['校准物质40℃降温速率', '校准物质60℃降温速率', '校准物质80℃降温速率', '校准物质100℃降温速率']
|
|
|
for i in range(len(temp)):
|
|
|
sheet.merge_cells(f'B{i + 9}:C{i + 9}')
|
|
|
sheet.merge_cells(f'D{i + 9}:E{i + 9}')
|
|
|
sheet.merge_cells(f'F{i + 9}:G{i + 9}')
|
|
|
sheet.merge_cells(f'H{i + 9}:I{i + 9}')
|
|
|
cell = sheet.cell(row=i + 9, column=2)
|
|
|
cell.value = temp[i]
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
cell = sheet.cell(row=i + 9, column=4)
|
|
|
cell.value = data_item["temp_data"][i]
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
cell1 = sheet.cell(row=i + 9, column=6)
|
|
|
cell1.value = temp1[i]
|
|
|
cell1.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
cell = sheet.cell(row=i + 9, column=8)
|
|
|
cell.value = data_item["temp_data1"][i]
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
|
|
|
def insert_data(self, sheet, data_item, key_list):
|
|
|
"""插入数据"""
|
|
|
|
|
|
for i in range(len(key_list)):
|
|
|
cell = sheet.cell(row=i + 2, column=6)
|
|
|
cell.value = data_item[key_list[i]]
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
|
|
|
# def insert_chart(self, sheet,chart_result,ro):
|
|
|
# """插入图表"""
|
|
|
#
|
|
|
#
|
|
|
# plt.rcParams['xtick.direction'] = 'in' # 将x周的刻度线方向设置向内
|
|
|
# plt.rcParams['ytick.direction'] = 'in' # 将y轴的刻度方向设置向内
|
|
|
# ax1_color = '#1f77b4'
|
|
|
# ax2_color = '#d62728'
|
|
|
# ax3_color = '#573627'
|
|
|
#
|
|
|
# data = chart_result
|
|
|
#
|
|
|
# x_data = [int(float(i[-1])) for i in data] # 时间
|
|
|
# y_data = [round(float(i[2]), 2) for i in data] # 压力
|
|
|
# y_data2 = [round(float(i[3]), 2) for i in data] # 温度
|
|
|
# # y_data3 = [round(float(i[-5] or 0) + 250, 2) for i in data] # 热表面温度 + 250
|
|
|
# # 创建主轴的图表
|
|
|
# fig, ax1 = plt.subplots()
|
|
|
# ax1.plot(x_data, y_data, 'b-', label='["压力","时间"]', linewidth=1, color=ax1_color)
|
|
|
# ax2 = ax1.twinx()
|
|
|
# ax1.plot(x_data, y_data2, 'b-', label='["温度","时间"]', linewidth=1, color=ax2_color)
|
|
|
# # ax1.plot(x_data, y_data3, 'b-', label='["高热表面温度250","时间"]', linewidth=1, color=ax3_color)
|
|
|
# plt.title('试验数据')
|
|
|
#
|
|
|
# ax1.set_xlabel('时间')
|
|
|
# ax1.set_ylabel('压力')
|
|
|
# ax1.legend(loc='upper left')
|
|
|
# ax2.legend(loc='upper right')
|
|
|
#
|
|
|
#
|
|
|
# # 图表标题
|
|
|
# # 设置中文字体
|
|
|
# plt.rcParams['font.sans-serif'] = ['SimHei']
|
|
|
# # 设置负号字体
|
|
|
# plt.rcParams['axes.unicode_minus'] = False
|
|
|
# # 保存 Matplotlib 图表为图像文件
|
|
|
# chart_file_path = path.join(settings.BASE_PATH, f'resource/{int(time.time() * 1000)}.png')
|
|
|
# print(chart_file_path)
|
|
|
# plt.savefig(chart_file_path, dpi=400)
|
|
|
# # 将图像插入到 Excel 工作表中
|
|
|
# img = Image(chart_file_path)
|
|
|
# img.width = 960
|
|
|
# img.height = 400
|
|
|
# sheet.add_image(img, 'B' + str(ro+2))
|
|
|
|
|
|
def insert_chart(self, sheet,chart_result,ro,type):
|
|
|
"""插入图表"""
|
|
|
|
|
|
|
|
|
plt.rcParams['xtick.direction'] = 'in' # 将x周的刻度线方向设置向内
|
|
|
plt.rcParams['ytick.direction'] = 'in' # 将y轴的刻度方向设置向内
|
|
|
# ax1_color = '#1f77b4'
|
|
|
# ax2_color = '#d62728'
|
|
|
# ax3_color = '#573627'
|
|
|
|
|
|
data = chart_result
|
|
|
|
|
|
x_data = [int(float(i.time)) for i in data] # 时间
|
|
|
if type == 1:
|
|
|
y_data = [round(float(i.surface_temperature), 2) for i in data] # 温度1 表面温度
|
|
|
ax_color = '#1f77b4'
|
|
|
label='["表面温度","时间"]'
|
|
|
ylabel = '表面温度'
|
|
|
elif type == 2:
|
|
|
y_data = [round(float(i.battery_voltage), 2) for i in data] # 温度2 电池电压
|
|
|
ax_color = '#d62728'
|
|
|
label='["电池电压","时间"]'
|
|
|
ylabel = '电池电压'
|
|
|
else:
|
|
|
y_data = [round(float(i.battery_electricity), 2) for i in data] # 温度3 电池电流
|
|
|
ax_color = '#573627'
|
|
|
label='["电池电流","时间"]'
|
|
|
ylabel = '电池电流'
|
|
|
# y_data = [round(float(i.surface_temperature), 2) for i in data] # 温度1 表面温度
|
|
|
# y_data2 = [round(float(i.battery_voltage), 2) for i in data] # 温度2 电池电压
|
|
|
# y_data3 = [round(float(i.battery_electricity), 2) for i in data] # 温度3 电池电流
|
|
|
# 创建主轴的图表
|
|
|
fig, ax1 = plt.subplots()
|
|
|
ax1.plot(x_data, y_data, 'b-', label=label, linewidth=1, color=ax_color)
|
|
|
# ax1.plot(x_data, y_data2, 'b-', label='["电池电压","时间"]', linewidth=1, color=ax2_color)
|
|
|
# ax1.plot(x_data, y_data3, 'b-', label='["电池电流","时间"]', linewidth=1, color=ax3_color)
|
|
|
plt.title('试验数据')
|
|
|
|
|
|
ax1.set_xlabel('时间')
|
|
|
ax1.set_ylabel(ylabel)
|
|
|
ax1.legend(loc='upper left')
|
|
|
|
|
|
|
|
|
# 图表标题
|
|
|
# 设置中文字体
|
|
|
plt.rcParams['font.sans-serif'] = ['SimHei']
|
|
|
# 设置负号字体
|
|
|
plt.rcParams['axes.unicode_minus'] = False
|
|
|
# 保存 Matplotlib 图表为图像文件
|
|
|
chart_file_path = path.join(settings.BASE_PATH, f'resource/{int(time.time() * 1000)}.png')
|
|
|
print(chart_file_path)
|
|
|
plt.savefig(chart_file_path, dpi=400)
|
|
|
# 将图像插入到 Excel 工作表中
|
|
|
img = Image(chart_file_path)
|
|
|
img.width = 960
|
|
|
img.height = 400
|
|
|
sheet.add_image(img, 'E' + str(ro+2))
|
|
|
|
|
|
ro+=20
|
|
|
|
|
|
# def insert_chart(self, sheet,chart_result,ro):
|
|
|
#
|
|
|
#
|
|
|
#
|
|
|
#
|
|
|
# # 设置全局字体和刻度线方向
|
|
|
# plt.rcParams['xtick.direction'] = 'in' # x轴刻度线方向
|
|
|
# plt.rcParams['ytick.direction'] = 'in' # y轴刻度线方向
|
|
|
# plt.rcParams['font.sans-serif'] = ['SimHei'] # 中文字体
|
|
|
# plt.rcParams['axes.unicode_minus'] = False # 负号显示
|
|
|
#
|
|
|
# # 假设这是你获取的实验数据
|
|
|
# data = chart_result
|
|
|
# x_data = [int(float(i.time)) for i in data] # 时间
|
|
|
# y_data = [round(float(i.surface_temperature), 2) for i in data] # 表面温度
|
|
|
# y_data2 = [round(float(i.battery_voltage), 2) for i in data] # 电池电压
|
|
|
# y_data3 = [round(float(i.battery_electricity), 2) for i in data] # 电池电流
|
|
|
#
|
|
|
# # 创建图表并设置主轴
|
|
|
# fig, ax1 = plt.subplots()
|
|
|
#
|
|
|
# # 绘制第一个数据系列：表面温度
|
|
|
# ax1.set_xlabel('时间')
|
|
|
# ax1.set_ylabel('表面温度 (°C)', color='#1f77b4')
|
|
|
# ax1.plot(x_data, y_data, label='表面温度', color='#1f77b4', linewidth=1)
|
|
|
# ax1.tick_params(axis='y', labelcolor='#1f77b4')
|
|
|
#
|
|
|
# # 设置第一个 y 轴的范围（根据数据调整）
|
|
|
# ax1.set_ylim([min(y_data) - 1, max(y_data) + 1]) # 适当调整范围，避免线条挤压
|
|
|
#
|
|
|
# # 创建第二个 y 轴：电池电压
|
|
|
# ax2 = ax1.twinx() # 创建第二个 y 轴
|
|
|
# ax2.set_ylabel('电池电压 (V)', color='#d62728')
|
|
|
# ax2.plot(x_data, y_data2, label='电池电压', color='#d62728', linewidth=1)
|
|
|
# ax2.tick_params(axis='y', labelcolor='#d62728')
|
|
|
#
|
|
|
# # 设置第二个 y 轴的范围（根据数据调整）
|
|
|
# ax2.set_ylim([min(y_data2) - 0.5, max(y_data2) + 0.5]) # 适当调整范围
|
|
|
#
|
|
|
# # 将第二个 y 轴移到左侧
|
|
|
# ax2.spines['left'].set_position(('outward', 60)) # 将第二个 y 轴移到外面
|
|
|
#
|
|
|
# # 创建第三个 y 轴：电池电流
|
|
|
# ax3 = ax1.twinx() # 创建第三个 y 轴
|
|
|
# ax3.spines['left'].set_position(('outward', 320)) # 将第三个 y 轴移到外面
|
|
|
# ax3.set_ylabel('电池电流 (A)', color='#573627')
|
|
|
# ax3.plot(x_data, y_data3, label='电池电流', color='#573627', linewidth=1)
|
|
|
# ax3.tick_params(axis='y', labelcolor='#573627')
|
|
|
#
|
|
|
# # 设置第三个 y 轴的范围（根据数据调整）
|
|
|
# ax3.set_ylim([min(y_data3) - 0.1, max(y_data3) + 0.1]) # 适当调整范围
|
|
|
#
|
|
|
# # 图表标题
|
|
|
# plt.title('试验数据')
|
|
|
#
|
|
|
# # 图例
|
|
|
# fig.tight_layout() # 自动调整子图间距
|
|
|
# ax1.legend(loc='upper left')
|
|
|
#
|
|
|
# # 保存图表为图片
|
|
|
# chart_file_path = path.join(settings.BASE_PATH, f'resource/{int(time.time() * 1000)}.png')
|
|
|
# print(chart_file_path)
|
|
|
# plt.savefig(chart_file_path, dpi=400)
|
|
|
#
|
|
|
# # 将图像插入到 Excel 工作表中
|
|
|
# img = Image(chart_file_path)
|
|
|
# img.width = 960
|
|
|
# img.height = 400
|
|
|
# sheet.add_image(img, 'D' + str(ro + 2))
|
|
|
|
|
|
|
|
|
|
|
|
def experiment_table(self):
|
|
|
"""试验报表"""
|
|
|
for item in self.data:
|
|
|
base_data_title = item['base_data_title']
|
|
|
base_data_list = item['base_data_list']
|
|
|
sheet_title = item['sheet_title']
|
|
|
title_list = item['title_list']
|
|
|
specimen_data = item['specimen_data']
|
|
|
sheet = self.book.create_sheet(str(sheet_title)) # 创建工作表用试验时间作工作表名称
|
|
|
|
|
|
self.set_title(sheet) # 设置标题
|
|
|
for i in range(len(title_list)):
|
|
|
sheet.merge_cells(f'A{i + 1}:L{i + 1}')
|
|
|
cell = sheet.cell(row=i+1, column=1)
|
|
|
cell.value = title_list[i]
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
if i == 0:
|
|
|
cell.font = Font(size=22, bold=True)
|
|
|
ro = 2
|
|
|
|
|
|
for key, value in specimen_data.items():
|
|
|
cell = sheet.cell(row=ro, column=1)
|
|
|
cell.value = key
|
|
|
# sheet.merge_cells(f'B{ro + 1}:E{ro + 1}')
|
|
|
cell_b = sheet.cell(row=ro, column=2)
|
|
|
cell_b.value = value
|
|
|
ro += 1
|
|
|
ro +=1
|
|
|
for index,value in enumerate(base_data_title):
|
|
|
if value:
|
|
|
cell = sheet.cell(row=ro, column=index+1)
|
|
|
cell.value = value
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
ro += 1
|
|
|
for i in base_data_list:
|
|
|
for index, value in enumerate(base_data_title):
|
|
|
cell = sheet.cell(row=ro, column=index + 1)
|
|
|
cell.value = i[base_data_title[index]]
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center')
|
|
|
ro += 1
|
|
|
|
|
|
# 在数据行下方增加一行，并填充新的列标题（'实验时间', '表面温度', '电池电压', '电池电流'）
|
|
|
new_row_title = ['实验时间', '表面温度', '电池电压', '电池电流']
|
|
|
|
|
|
# 填充新的一行数据标题，紧接数据区域下方
|
|
|
for index, value in enumerate(new_row_title):
|
|
|
cell = sheet.cell(row=ro+4, column=index + 1)
|
|
|
cell.value = value
|
|
|
cell.alignment = Alignment(horizontal='center', vertical='center') # 可选，居中对齐标题
|
|
|
|
|
|
fill_ro = ro+5
|
|
|
# 填充额外数据（'实验时间', '表面温度', '电池电压', '电池电流'）
|
|
|
for i in base_data_list[0]['other_data']:
|
|
|
cell_css= Alignment(horizontal='center', vertical='center') # 可选，居中对齐数据
|
|
|
time_cell = sheet.cell(row=fill_ro, column= 1)
|
|
|
time_cell.value = i.time # 时间
|
|
|
time_cell.alignment = cell_css
|
|
|
hxwd_cell = sheet.cell(row=fill_ro, column= 2)
|
|
|
hxwd_cell.value = i.surface_temperature # 表面温度
|
|
|
hxwd_cell.alignment = cell_css
|
|
|
ypwd_cell = sheet.cell(row=fill_ro, column= 3)
|
|
|
ypwd_cell.value = i.battery_voltage # 电池电压
|
|
|
ypwd_cell.alignment = cell_css
|
|
|
dcdl_cell = sheet.cell(row=fill_ro, column= 4)
|
|
|
dcdl_cell.value = i.battery_electricity # 电池电流
|
|
|
dcdl_cell.alignment = cell_css
|
|
|
fill_ro += 1 # 每填充一行数据后，增加行号
|
|
|
|
|
|
for i in base_data_list:
|
|
|
result_row_data = i['other_data']
|
|
|
if result_row_data:
|
|
|
char_data =[]
|
|
|
for row in result_row_data:
|
|
|
# if row[0] in [4,'4']:
|
|
|
char_data.append(row)
|
|
|
# 循环三次、表面温度、电池电压、电池电流
|
|
|
type = 1
|
|
|
for i in range(3):
|
|
|
self.insert_chart(sheet, char_data, ro,type)
|
|
|
type += 1
|
|
|
ro += 20
|
|
|
|
|
|
|
|
|
# 获取所有工作表的列表
|
|
|
sheets = self.book.sheetnames
|
|
|
# 删除第一个工作表
|
|
|
first_sheet_name = sheets[0]
|
|
|
self.book.remove(self.book[first_sheet_name])
|
|
|
|
|
|
# 导出
|
|
|
return self.export()
|
|
|
|
|
|
def export(self):
|
|
|
"""
|
|
|
导出
|
|
|
"""
|
|
|
binary = BytesIO()
|
|
|
self.book.save(binary)
|
|
|
self.book.close()
|
|
|
binary.seek(0)
|
|
|
filename = f"{datetime.now().strftime('%Y%m%d%H%M%S')}-{self.file_name}-报表.xlsx"
|
|
|
if self.download_type == 'usb':
|
|
|
try:
|
|
|
self.put_in(filename, binary)
|
|
|
return 200, "导出成功"
|
|
|
except Exception as e:
|
|
|
print(e)
|
|
|
return 404, "U盘未找到"
|
|
|
encoded_filename = urllib.parse.quote(filename)
|
|
|
|
|
|
return binary, encoded_filename
|
|
|
|
|
|
def put_in(self, filename: str, bin: BytesIO):
|
|
|
if system_name == 'Windows':
|
|
|
usbs = [disk.device for disk in psutil.disk_partitions() if disk.opts == 'rw,removable']
|
|
|
if not usbs:
|
|
|
raise Exception()
|
|
|
# with open(f"D:\\{filename}", 'wb') as f:
|
|
|
# f.write(bin.getbuffer())
|
|
|
# return
|
|
|
path_file = path.join(usbs[0], filename)
|
|
|
else:
|
|
|
usbs = [item for item in psutil.disk_partitions() if item.mountpoint.startswith(f'/media/{getpass.getuser()}')]
|
|
|
if not usbs:
|
|
|
raise Exception()
|
|
|
path_file = path.join(usbs[0].mountpoint, filename)
|
|
|
with open(path_file, 'wb') as f:
|
|
|
f.write(bin.getbuffer())
|
