import openpyxl import sqlite3 import os import time from openpyxl.styles import Alignment, NamedStyle from PySide6.QtWidgets import QMessageBox def Arrange_Data1(list1): # 最终return的 list2 = [] for data1 in list1: # 点名 # 存每一行的数据 resultlist = [] pn = data1[0].decode('utf-8') resultlist.append(pn) resultlist.append(data1[1]) resultlist.append(data1[2]) resultlist.append(data1[3]) resultlist.append(data1[4]) resultlist.append(data1[5]) resultlist.append(data1[6]) resultlist.append(data1[7]) resultlist.append(data1[8]) resultlist.append(data1[9]) resultlist.append(data1[10]) resultlist.append(data1[11]) # 判定1 an1 = data1[12].decode('utf-8') resultlist.append(an1) resultlist.append(data1[13]) resultlist.append(data1[14]) resultlist.append(data1[15]) # 判定2 an2 = data1[16].decode('utf-8') resultlist.append(an2) # 3个名字（首，上，新） name1 = data1[17].decode('utf-8') resultlist.append(name1) name2 = data1[18].decode('utf-8') resultlist.append(name2) name3 = data1[19].decode('utf-8') resultlist.append(name3) list2.append(resultlist) return list2 def export_combined_data(ui, db_path, utf_en, export_folder, file_name): try: # 解码文件名并去掉后缀名 decoded_utf_en = utf_en.decode('utf-8') if isinstance(utf_en, bytes) else utf_en excel_path = os.path.join(export_folder, file_name) # 创建一个新的工作簿 wb = openpyxl.Workbook() # 创建三个Sheet ws_initial = wb.create_sheet('原始数据') ws_stability = wb.create_sheet('稳定性分析成果表') ws_model = wb.create_sheet('改算模型') # 删除默认的Sheet ws_default = wb['Sheet'] wb.remove(ws_default) # 连接到数据库 conn = sqlite3.connect(db_path) cursor = conn.cursor() # 查询WD_Input_Param表中的数据 query_param = """ SELECT New_ResultName, Last_ResultName, Avg_SL, Ms_Dir, SL_Count, Dir_Count, Scale_Value, First_ResultName FROM WD_Input_Param WHERE TableName = ? """ cursor.execute(query_param, (utf_en,)) result_param = cursor.fetchone() # 创建样式来保留指定的小数位数 decimal_style_3 = NamedStyle(name="decimal_style_3") decimal_style_3.number_format = '0.0000' decimal_style_3.alignment = Alignment(horizontal='center', vertical='center') decimal_style_1 = NamedStyle(name="decimal_style_1") decimal_style_1.number_format = '0.0' decimal_style_1.alignment = Alignment(horizontal='center', vertical='center') decimal_style_4 = NamedStyle(name="decimal_style_4") decimal_style_4.number_format = '0' decimal_style_4.alignment = Alignment(horizontal='center', vertical='center') if result_param: new_result_name, last_result_name, avg_sl, ms_dir, sl_count, dir_count, scale_value, first_result_name = result_param # 填充数据到“原始数据”Sheet并应用样式 ws_initial['B1'] = new_result_name ws_initial['B1'].alignment = Alignment(horizontal='center', vertical='center') ws_initial['D1'] = last_result_name ws_initial['D1'].alignment = Alignment(horizontal='center', vertical='center') ws_initial['G1'] = first_result_name ws_initial['G1'].alignment = Alignment(horizontal='center', vertical='center') ws_initial['J1'] = avg_sl ws_initial['J1'].style = decimal_style_3 ws_initial['J2'] = ms_dir ws_initial['J2'].alignment = Alignment(horizontal='center', vertical='center') ws_initial['J3'] = sl_count ws_initial['J3'].style = decimal_style_4 ws_initial['J4'] = dir_count ws_initial['J4'].style = decimal_style_4 ws_initial['J5'] = scale_value ws_initial['J5'].style = decimal_style_4 # 合并单元格 ws_initial.merge_cells('B1:C1') ws_initial.merge_cells('D1:E1') ws_initial.merge_cells('G1:H1') # 设置列宽 ws_initial.column_dimensions['B'].width = 12.5 ws_initial.column_dimensions['C'].width = 12.5 ws_initial.column_dimensions['D'].width = 12.5 ws_initial.column_dimensions['E'].width = 12.5 ws_initial.column_dimensions['F'].width = 3 ws_initial.column_dimensions['G'].width = 12.5 ws_initial.column_dimensions['H'].width = 12.5 ws_initial.column_dimensions['I'].width = 14 ws_initial.column_dimensions['J'].width = 10 # 设置表头 headers = [ ("A2", "点名"), ("B2", "高斯坐标x(m)"), ("C2", "高斯坐标y(m)"), ("D2", "高斯坐标x(m)"), ("E2", "高斯坐标y(m)"), ("F2", "权"), ("G2", "高斯坐标x(m)"), ("H2", "高斯坐标y(m)"), ("I1", "平均边长"), ("I2", "方向值中误差"), ("I3", "总边数"), ("I4", "总方向数"), ("I5", "缩放值") ] for cell, value in headers: ws_initial[cell] = value ws_initial[cell].alignment = Alignment(horizontal='center', vertical='center') # 查询WD_Input_Point表中的数据 query_point = """ SELECT PointName, New_X, New_Y, Last_X, Last_Y, Wight, First_X, First_Y FROM WD_Input_Point WHERE TableName = ? """ cursor.execute(query_point, (utf_en,)) results_point = cursor.fetchall() # 创建一个样式来强制保留四位小数 decimal_style = NamedStyle(name="decimal_style") decimal_style.number_format = '0000.0000' # 填充数据到“原始数据”Sheet for idx, (point_name, new_x, new_y, last_x, last_y, wight, first_x, first_y) in enumerate(results_point, start=3): ws_initial[f'A{idx}'] = point_name ws_initial[f'B{idx}'] = round(new_x, 4) ws_initial[f'C{idx}'] = round(new_y, 4) ws_initial[f'D{idx}'] = round(last_x, 4) ws_initial[f'E{idx}'] = round(last_y, 4) ws_initial[f'F{idx}'] = round(wight, 0) ws_initial[f'G{idx}'] = round(first_x, 4) ws_initial[f'H{idx}'] = round(first_y, 4) # 应用样式以保留四位小数 ws_initial[f'B{idx}'].style = decimal_style ws_initial[f'C{idx}'].style = decimal_style ws_initial[f'D{idx}'].style = decimal_style ws_initial[f'E{idx}'].style = decimal_style ws_initial[f'G{idx}'].style = decimal_style ws_initial[f'H{idx}'].style = decimal_style # 提取“稳定性分析成果表”数据 sqlstr1 = 'select PointName,First_X,First_Y,Last_X,Last_Y,Last_Wight,Result_X,Result_Y,New_Wight,New_FirstX,New_FirstY,New_FirstP,NFDis_Ass,New_LastX,New_LastY,New_LastP,NLDis_Ass,First_ResultName,Last_ResultName,New_ResultName from WD_Result_Point WHERE TableName = ?' cursor.execute(sqlstr1, (utf_en,)) result1 = cursor.fetchall() plist1 = Arrange_Data1(result1) # 设置“稳定性分析成果表”列宽 ws_stability.column_dimensions['B'].width = 15 ws_stability.column_dimensions['C'].width = 15 ws_stability.column_dimensions['D'].width = 15 ws_stability.column_dimensions['E'].width = 15 ws_stability.column_dimensions['F'].width = 5 ws_stability.column_dimensions['G'].width = 15 ws_stability.column_dimensions['H'].width = 15 ws_stability.column_dimensions['I'].width = 5 ws_stability.column_dimensions['M'].width = 5 ws_stability.column_dimensions['Q'].width = 5 ws_stability.column_dimensions['J'].width = 8 ws_stability.column_dimensions['K'].width = 8 ws_stability.column_dimensions['L'].width = 8 ws_stability.column_dimensions['N'].width = 8 ws_stability.column_dimensions['O'].width = 8 ws_stability.column_dimensions['P'].width = 8 # 设置“稳定性分析成果表”表头 ws_stability.merge_cells(start_row=1, end_row=1, start_column=1, end_column=17) ws_stability.cell(1, 1).value = '稳定性分析成果表' ws_area = ws_stability["A1:Q2"] alignment_center = Alignment(horizontal='center', vertical='center') for i in ws_area: for j in i: j.alignment = alignment_center alignment_right = Alignment(horizontal='right', vertical='center') ws_stability.merge_cells(start_row=2, end_row=3, start_column=1, end_column=1) ws_stability.cell(2, 1).value = '点号' ws_stability.merge_cells(start_row=2, end_row=2, start_column=2, end_column=3) ws_stability.cell(2, 2).value = plist1[0][17] ws_stability.merge_cells(start_row=2, end_row=2, start_column=4, end_column=6) ws_stability.cell(2, 4).value = plist1[0][18] ws_stability.merge_cells(start_row=2, end_row=2, start_column=7, end_column=9) ws_stability.cell(2, 7).value = plist1[0][19] ws_stability.merge_cells(start_row=2, end_row=2, start_column=10, end_column=12) ws_stability.cell(2, 10).value = '本期-首期(mm)' ws_stability.merge_cells(start_row=2, end_row=3, start_column=13, end_column=13) ws_stability.cell(2, 13).value = '变形判定' ws_stability.merge_cells(start_row=2, end_row=2, start_column=14, end_column=16) ws_stability.cell(2, 14).value = '本期-上期(mm)' ws_stability.merge_cells(start_row=2, end_row=3, start_column=17, end_column=17) ws_stability.cell(2, 17).value = '变形判定' ws_stability.cell(2, 13).alignment = Alignment(wrap_text=True) ws_stability.cell(2, 17).alignment = Alignment(wrap_text=True) ws_stability.cell(3, 2).value = 'X(m)' ws_stability.cell(3, 3).value = 'Y(m)' ws_stability.cell(3, 4).value = 'X(m)' ws_stability.cell(3, 5).value = 'Y(m)' ws_stability.cell(3, 6).value = '权' ws_stability.cell(3, 7).value = 'X(m)' ws_stability.cell(3, 8).value = 'Y(m)' ws_stability.cell(3, 9).value = '权' ws_stability.cell(3, 10).value = '△X' ws_stability.cell(3, 11).value = '△Y' ws_stability.cell(3, 12).value = '△XY' ws_stability.cell(3, 14).value = '△X' ws_stability.cell(3, 15).value = '△Y' ws_stability.cell(3, 16).value = '△XY' row1 = 4 for data1 in plist1: ws_stability.cell(row1, 1).value = data1[0] ws_stability.cell(row1, 2).value = round(data1[1], 4) ws_stability.cell(row1, 3).value = round(data1[2], 4) ws_stability.cell(row1, 4).value = round(data1[3], 4) ws_stability.cell(row1, 5).value = round(data1[4], 4) ws_stability.cell(row1, 6).value = int(data1[5]) ws_stability.cell(row1, 7).value = round(data1[6], 4) ws_stability.cell(row1, 8).value = round(data1[7], 4) ws_stability.cell(row1, 9).value = int(data1[8]) ws_stability.cell(row1, 10).value = round(data1[9], 1) ws_stability.cell(row1, 11).value = round(data1[10], 1) ws_stability.cell(row1, 12).value = round(data1[11], 1) if data1[12] == '稳定': ws_stability.cell(row1, 13).value = '' else: ws_stability.cell(row1, 13).value = data1[12] ws_stability.cell(row1, 14).value = round(data1[13], 1) ws_stability.cell(row1, 15).value = round(data1[14], 1) ws_stability.cell(row1, 16).value = round(data1[15], 1) if data1[16] == '稳定': ws_stability.cell(row1, 17).value = '' else: ws_stability.cell(row1, 17).value = data1[16] row1 += 1 # 提取“改算模型”数据 sqlstr2 = 'select Last_ResultName,New_ResultName,Formula_X1,Formula_X2,Formula_X3,Formula_Y1,Formula_Y2,Formula_Y3 from WD_Result_Param WHERE TableName = ?' cursor.execute(sqlstr2, (utf_en,)) result3 = cursor.fetchall() newname = result3[0][1].decode('utf-8') lastname = result3[0][0].decode('utf-8') # 设置“改算模型”列宽 ws_model.column_dimensions['A'].width = 75 # 设置“改算模型”表头 ws_area = ws_model["A1:A5"] alignment_center = Alignment(horizontal='left', vertical='center') for i in ws_area: for j in i: j.alignment = alignment_center # 填充“改算模型”数据 str1 = newname + '--' + lastname + '已知系统转换公式:' ws_model.cell(1, 1).value = str1 str2 = 'X=(' + str(round(result3[0][2], 14)) + ')·x+(' + str(round(result3[0][3], 14)) + ')·y+(' + str( round(result3[0][4], 11)) + ')' ws_model.cell(2, 1).value = str2 str3 = 'Y=(' + str(round(result3[0][5], 14)) + ')·x+(' + str(round(result3[0][6], 14)) + ')·y+(' + str( round(result3[0][7], 11)) + ')' ws_model.cell(3, 1).value = str3 str4 = '式中:x、y为' + newname ws_model.cell(4, 1).value = str4 str5 = ' X、Y为' + lastname + '已知系统的' + newname + '归算坐标' ws_model.cell(5, 1).value = str5 # 保存工作簿 wb.save(excel_path) QMessageBox.information(ui, '成功', f'数据文件已导出到 {export_folder}') except PermissionError as e: if e.errno == 13: QMessageBox.critical(ui, '错误', '请确认文件没有被其他程序（如Excel、文本编辑器等）打开') else: QMessageBox.critical(ui, '错误', f'导出过程中发生错误: {str(e)}') except Exception as e: QMessageBox.critical(ui, '错误', f'导出过程中发生错误: {str(e)}') def export_example_data(ui, db_path, utf_en, export_folder, file_name): # 解码文件名并去掉后缀名 decoded_utf_en = utf_en.decode('utf-8') if isinstance(utf_en, bytes) else utf_en # excel_file_name = f"{os.path.splitext(decoded_utf_en)[0]}.xlsx" excel_path = os.path.join(export_folder, file_name) # 创建一个新的工作簿和工作表 wb = openpyxl.Workbook() ws = wb.active # 连接到数据库 conn = sqlite3.connect(db_path) cursor = conn.cursor() # 查询WD_Input_Param表中的数据 query = """ SELECT New_ResultName, Last_ResultName, Avg_SL, Ms_Dir, SL_Count, Dir_Count, Scale_Value, First_ResultName FROM WD_Input_Param WHERE TableName = ? """ cursor.execute(query, (utf_en,)) result = cursor.fetchone() # 创建样式来保留指定的小数位数 decimal_style_3 = NamedStyle(name="decimal_style_3") decimal_style_3.number_format = '0.0000' decimal_style_3.alignment = Alignment(horizontal='center', vertical='center') decimal_style_1 = NamedStyle(name="decimal_style_1") decimal_style_1.number_format = '0.0' decimal_style_1.alignment = Alignment(horizontal='center', vertical='center') decimal_style_4 = NamedStyle(name="decimal_style_4") decimal_style_4.number_format = '0' decimal_style_4.alignment = Alignment(horizontal='center', vertical='center') if result: new_result_name, last_result_name, avg_sl, ms_dir, sl_count, dir_count, scale_value, first_result_name = result # 填充数据到Excel并应用样式 ws['B1'] = new_result_name ws['B1'].alignment = Alignment(horizontal='center', vertical='center') ws['D1'] = last_result_name ws['D1'].alignment = Alignment(horizontal='center', vertical='center') ws['G1'] = first_result_name # 新增：填入First_ResultName ws['G1'].alignment = Alignment(horizontal='center', vertical='center') # 新增：设置居中对齐 ws['J1'] = avg_sl ws['J1'].style = decimal_style_3 ws['J2'] = ms_dir ws['J2'].alignment = Alignment(horizontal='center', vertical='center') ws['J3'] = sl_count ws['J3'].style = decimal_style_4 ws['J4'] = dir_count ws['J4'].style = decimal_style_4 ws['J5'] = scale_value ws['J5'].style = decimal_style_4 # 合并单元格 ws.merge_cells('B1:C1') ws.merge_cells('D1:E1') ws.merge_cells('G1:H1') # 设置列宽 ws.column_dimensions['B'].width = 12.5 ws.column_dimensions['C'].width = 12.5 ws.column_dimensions['D'].width = 12.5 ws.column_dimensions['E'].width = 12.5 ws.column_dimensions['F'].width = 3 ws.column_dimensions['F'].alignment = Alignment(horizontal='center', vertical='center') # 新增：设置F列居中对齐 ws.column_dimensions['G'].width = 12.5 ws.column_dimensions['H'].width = 12.5 ws.column_dimensions['I'].width = 14 ws.column_dimensions['J'].width = 10 # 设置表头 headers = [ ("A2", "点名"), ("B2", "高斯坐标x(m)"), ("C2", "高斯坐标y(m)"), ("D2", "高斯坐标x(m)"), ("E2", "高斯坐标y(m)"), ("F2", "权"), ("G2", "高斯坐标x(m)"), ("H2", "高斯坐标y(m)"), ("I1", "平均边长"), ("I2", "方向值中误差"), ("I3", "总边数"), ("I4", "总方向数"), ("I5", "缩放值") ] for cell, value in headers: ws[cell] = value ws[cell].alignment = Alignment(horizontal='center', vertical='center') # 查询WD_Input_Point表中的数据 query_point = """ SELECT PointName, New_X, New_Y, Last_X, Last_Y, Wight, First_X, First_Y FROM WD_Input_Point WHERE TableName = ? """ cursor.execute(query_point, (utf_en,)) results_point = cursor.fetchall() # 创建一个样式来强制保留四位小数 decimal_style = NamedStyle(name="decimal_style") decimal_style.number_format = '0000.0000' # 填充数据到Excel for idx, (point_name, new_x, new_y, last_x, last_y, wight, first_x, first_y) in enumerate(results_point, start=3): ws[f'A{idx}'] = point_name ws[f'B{idx}'] = round(new_x, 4) ws[f'C{idx}'] = round(new_y, 4) ws[f'D{idx}'] = round(last_x, 4) ws[f'E{idx}'] = round(last_y, 4) ws[f'F{idx}'] = round(wight, 0) # 新增 ws[f'G{idx}'] = round(first_x, 4) # 新增 ws[f'H{idx}'] = round(first_y, 4) # 新增 # 应用样式以保留四位小数 ws[f'B{idx}'].style = decimal_style ws[f'C{idx}'].style = decimal_style ws[f'D{idx}'].style = decimal_style ws[f'E{idx}'].style = decimal_style ws[f'G{idx}'].style = decimal_style # 新增 ws[f'H{idx}'].style = decimal_style # 新增 # 保存工作簿 wb.save(excel_path) # 数据文件的导出 def main_function_combined(ui, dbpath, excelname, export_folder, file_name): export_combined_data(ui, dbpath, excelname, export_folder, file_name) # 示例文件的导出 def main_function_example(ui, dbpath, excelname_utf8, export_folder, file_name): try: export_example_data(ui, dbpath, excelname_utf8, export_folder, file_name) QMessageBox.information(ui, '成功', f'示例文件已成功导出到 {export_folder}') except PermissionError as e: if e.errno == 13: QMessageBox.critical(ui, '错误', '请确认文件没有被其他程序（如Excel、文本编辑器等）打开') else: QMessageBox.critical(ui, '错误', f'导出过程中发生错误: {str(e)}') except Exception as e: QMessageBox.critical(ui, '错误', f'导出过程中发生错误: {str(e)}')