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())