1.开发思路：
第一部分：数据规整，目的整理出数据统一的raw data
第二部分：数据处理，读取规整后的数据，进行处理，最终生成sigma
2.开发技术点：
2.1 读取excel的相关知识点（csv 数据提取与xlsx格式）
2.2遍历行列的逻辑处理
2.3筛选出数据存储到列表，基于列表计算sigma值
2.4生成值如何保存到新的excel中
2.5 支持CP 、FT数据处理（43xx）
# ---- code start ----#
1 # -*- coding:utf-8 -*- 2 # @Author: Alex 3 import os 4 # import time 5 # import math 6 # import string 7 import datetime 8 import xlrd 9 # import xlwt 10 import openpyxl 11 from openpyxl import load_workbook 12 # from openpyxl.styles import PatternFill 13 import numpy as np 14 15 import pandas as pd 16 import zipfile 17 from openpyxl import Workbook 18 19 ''' 20 //==========================================================================================================// 21 // sigma_handle_scan 22 //==========================================================================================================// 23 ''' 24 25 26 def sigma_handle_scan(s_file_path, s_temp_path, t_file_path): 27 datetime_object_s = datetime.datetime.now() 28 print("\n datetime_object_s :", datetime_object_s) 29 source_map = s_file_path 30 source_temp = s_temp_path 31 source_target = t_file_path 32 print('\n\t 打印源目标路径1：', source_map) 33 print('\n\t 打印源目标路径2：', source_temp) 34 print('\n\t 打印源目标路径3：', source_target) 35 36 # 源文件 37 source_map1_object = xlrd.open_workbook(source_map) 38 print('\n\t 读取源目标map对象:', source_map1_object) 39 print('\n\t 源目标map对象sheet:', len(source_map1_object.sheets()), source_map1_object.sheets()) 40 41 for sheet in range(len(source_map1_object.sheets())): 42 source_table1 = source_map1_object.sheet_by_index(sheet) 43 source_rows = source_table1.nrows 44 source_cols = source_table1.ncols 45 print('\n\t 源文件:行数%d 列数%d' % (source_rows, source_cols)) 46 empty = "" 47 # 预处理 48 for map_x0 in range(8, source_rows): # source_rows 49 map_x0_rt = map_x0 + 1 50 for map_y0 in range(20, source_cols): 51 map_y0_rt = map_y0 + 1 52 print(map_x0, map_y0, source_table1.cell_value(rowx=(map_x0 - 1), colx=(map_y0 - 1)), 53 type(source_table1.cell_value(rowx=(map_x0 - 1), colx=(map_y0 - 1)))) 54 if source_table1.cell_value(rowx=map_x0, colx=map_y0) == empty: 55 print("需要处理:empty") 56 print(map_x0_rt, map_y0_rt, source_table1.cell_value(rowx=map_x0, colx=map_y0)) 57 else: 58 if type(source_table1.cell_value(rowx=map_x0, colx=map_y0)) == type(1.1): 59 pass 60 else: 61 print("需要处理:非数字") 62 print(map_x0, map_y0, source_table1.cell_value(rowx=map_x0, colx=map_y0)) 63 datetime_object_e = datetime.datetime.now() 64 print("\n datetime_object_e :", datetime_object_e) 65 66 print("总时间：", datetime_object_e - datetime_object_s) 67 68 69 ''' 70 //==========================================================================================================// 71 // sigma_handle2 72 //==========================================================================================================// 73 ''' 74 75 76 def sigma_handle2(s_file_path, s_temp_path, t_file_path): 77 datetime_object1 = datetime.datetime.now() 78 print("datetime_object1 :", datetime_object1) 79 80 source_map = s_file_path 81 source_temp = s_temp_path 82 source_target = t_file_path 83 print('打印源目标路径1：', source_map) 84 print('打印源目标路径2：', source_temp) 85 print('打印源目标路径3：', source_target) 86 87 # 源文件 88 source_map1_object = xlrd.open_workbook(source_map) 89 target_workbook = openpyxl.Workbook() 90 91 for sheet in range(len(source_map1_object.sheets())): 92 source_table1 = source_map1_object.sheet_by_index(sheet) 93 source_rows = source_table1.nrows 94 source_cols = source_table1.ncols 95 print('\n\t 源文件:行数%d 列数%d' % (source_rows, source_cols)) 96 datetime_object2 = datetime.datetime.now() 97 print("\n datetime_object2 :", datetime_object2) 98 new_worksheet = target_workbook.active 99 # fill0 = PatternFill('solid', fgColor='AAFF32') 100 # fill1 = PatternFill('solid', fgColor='FFFF00') 101 # fill2 = PatternFill('solid', fgColor='ceb301') 102 # fill3 = PatternFill('solid', fgColor='ff796c') 103 # fill4 = PatternFill('solid', fgColor='f97306') 104 # fill_w1 = PatternFill('solid', fgColor='9a0eea') 105 rty = 'P' # empty die 106 empty = "" 107 reduce_count = 0 108 109 # 第一部分 筛选BIN1 110 for map_x1 in range(0, source_rows): # source_rows 111 map_x1_rt = map_x1 + 1 112 datetime_object2_1 = datetime.datetime.now() 113 print("datetime_object2_1 :", datetime_object2_1) 114 # 从左到右扫描 115 # print(map_x1_rt, 13, source_table1.cell_value(rowx=(map_x1 - 1), colx=(13))) 116 if map_x1 > 6: 117 if rty == source_table1.cell_value(rowx=map_x1, colx=13): 118 for map_y1 in range(0, source_cols): 119 map_y1_rt = map_y1 + 1 120 # print(map_x1,map_y1,source_table1.cell_value(rowx=(map_x1-1),colx=(map_y1-1))) 121 new_worksheet.cell((map_x1_rt - reduce_count), map_y1_rt).value = source_table1.cell_value(rowx=map_x1, colx=map_y1) 122 else: 123 reduce_count = reduce_count + 1 124 else: 125 for map_y0 in range(0, source_cols): 126 map_y0_rt = map_y0 + 1 127 new_worksheet.cell(map_x1_rt, map_y0_rt).value = source_table1.cell_value(rowx=map_x1, colx=map_y0) 128 # print('\n\t坐标信息:',map_x1_rt,map_y1_rt,map_x1,map_y1) 129 target_workbook.save(source_temp) 130 datetime_object3 = datetime.datetime.now() 131 print("\n datetime_object3 :", datetime_object3) 132 # 第二部分 筛选计算sigma source_temp 133 target2_workbook = load_workbook(filename=source_temp) 134 new2_worksheet = target2_workbook.active 135 136 # 创建一个新的 Excel 文件 137 workbook = openpyxl.Workbook() 138 one_sheet = workbook.active 139 # 固定格式抬头 140 test_result_v0 = ["project_name", "down_limit", "up_limit", "unit", "mean", "std", 141 "3sigma_down", "3sigma_up", "3sigma_percentage", 142 "4sigma_down", "4sigma_up", "4sigma_percentage", 143 "5sigma_down", "5sigma_up", "5sigma_percentage", 144 "6sigma_down", "6sigma_up", "6sigma_percentage", ] 145 one_sheet.append(test_result_v0) # 追加一行数据 146 147 target_rows = new2_worksheet.max_row 148 target_cols = new2_worksheet.max_column 149 print('\n target坐标：', target_rows, target_cols) 150 151 def_rows_test = 8 152 # def_columns_test = 19 153 def_columns_test = 20 # 20 154 155 for sigma_y in range(0, target_cols): # 368 156 sigma_yy = sigma_y + 1 157 each_column = [] 158 each_column2 = [] 159 test_result_v1 = [] 160 datetime_object4 = datetime.datetime.now() 161 print("datetime_object4 :", datetime_object4) 162 for sigma_x in range(0, target_rows): # 37 163 sigma_xx = sigma_x + 1 164 # new2_worksheet.cell(sigma_xx, sigma_yy).value 165 # print('\n 每列值：',new_worksheet.cell(sigma_xx, sigma_yy).value) 166 each_column.append(new2_worksheet.cell(sigma_xx, sigma_yy).value) 167 # # 单独增加抬头 168 if sigma_yy >= def_columns_test: 169 if (sigma_xx > 1) and (sigma_xx < 3) == 1: 170 test_result_v1.append(new2_worksheet.cell(sigma_xx, sigma_yy).value) 171 if (sigma_xx > 3) and (sigma_xx < 7) == 1: 172 test_result_v1.append(new2_worksheet.cell(sigma_xx, sigma_yy).value) 173 # 单独增加有效数据 174 if (sigma_xx >= def_rows_test) and (sigma_yy >= def_columns_test) == 1: 175 each_column2.append(new2_worksheet.cell(sigma_xx, sigma_yy).value) 176 # print('\n 每列数据1：', each_column) 177 # print('\n\t 有效数据2：', each_column2 ) 178 print('列数 >>>: ', sigma_yy) 179 each_column3 = each_column[7:len(each_column)] 180 181 # 去除前8个值 182 if (sigma_xx >= def_rows_test) and (sigma_yy >= def_columns_test) == 1: 183 # print('\n\t 有效数据3：', each_column3) 184 sigma_yy_effective = sigma_yy - def_columns_test + 1 185 test_MEAN = np.mean(each_column3) 186 test_STD = np.std(each_column3) 187 # print('test_MEAN:', test_MEAN) 188 # print('test_STD:', test_STD) 189 190 sigma3_up = test_MEAN + 3 * test_STD 191 sigma3_down = test_MEAN - 3 * test_STD 192 sigma4_up = test_MEAN + 4 * test_STD 193 sigma4_down = test_MEAN - 4 * test_STD 194 sigma5_up = test_MEAN + 5 * test_STD 195 sigma5_down = test_MEAN - 5 * test_STD 196 sigma6_up = test_MEAN + 6 * test_STD 197 sigma6_down = test_MEAN - 6 * test_STD 198 print('sigma3: %.2f %.2f' % (sigma3_down, sigma3_up), ) 199 print('sigma4: %.2f %.2f' % (sigma4_down, sigma4_up), ) 200 print('sigma5: %.2f %.2f' % (sigma5_down, sigma5_up), ) 201 print('sigma6: %.2f %.2f' % (sigma6_down, sigma6_up), ) 202 sigma3_count = 0 203 sigma4_count = 0 204 sigma5_count = 0 205 sigma6_count = 0 206 # sigma3_proportion = 0 207 # sigma4_proportion = 0 208 # sigma5_proportion = 0 209 # sigma6_proportion = 0 210 for index_v2 in range(len(each_column3)): 211 # print(each_column3[index_v2]) 212 if (sigma3_up >= each_column3[index_v2]) and (each_column3[index_v2] >= sigma3_down): 213 sigma3_count = sigma3_count + 1 214 # print(sigma3_count,test_get_data_v1[index_v2]) 215 if (sigma4_up >= each_column3[index_v2]) and (each_column3[index_v2] >= sigma4_down): 216 sigma4_count = sigma4_count + 1 217 if (sigma5_up >= each_column3[index_v2]) and (each_column3[index_v2] >= sigma5_down): 218 sigma5_count = sigma5_count + 1 219 if (sigma6_up >= each_column3[index_v2]) and (each_column3[index_v2] >= sigma6_down): 220 sigma6_count = sigma6_count + 1 221 sigma3_proportion = (sigma3_count / len(each_column3)) * 100 222 sigma4_proportion = (sigma4_count / len(each_column3)) * 100 223 sigma5_proportion = (sigma5_count / len(each_column3)) * 100 224 sigma6_proportion = (sigma6_count / len(each_column3)) * 100 225 print("平均值: %.12f" % test_MEAN) 226 print("标准差: %.12f" % test_STD) 227 print("每列总数量为: ", len(each_column3)) 228 print('sigma3: %.2f %.2f' % (sigma3_down, sigma3_up), ' percent: {:.2f}%'.format(sigma3_proportion), sigma3_count) 229 print('sigma4: %.2f %.2f' % (sigma4_down, sigma4_up), ' percent: {:.2f}%'.format(sigma4_proportion), sigma4_count) 230 print('sigma5: %.2f %.2f' % (sigma5_down, sigma5_up), ' percent: {:.2f}%'.format(sigma5_proportion), sigma5_count) 231 print('sigma6: %.2f %.2f' % (sigma6_down, sigma6_up), ' percent: {:.2f}%'.format(sigma6_proportion), sigma6_count) 232 print('*****第%d笔数据***********\n' % sigma_yy_effective) 233 234 test_result_v1.append(test_MEAN) 235 test_result_v1.append(test_STD) 236 test_result_v1.append(round(sigma3_down, 2)) 237 test_result_v1.append(round(sigma3_up, 2)) 238 test_result_v1.append(round(sigma3_proportion, 2)) 239 test_result_v1.append(round(sigma4_down, 2)) 240 test_result_v1.append(round(sigma4_up, 2)) 241 test_result_v1.append(round(sigma4_proportion, 2)) 242 test_result_v1.append(round(sigma5_down, 2)) 243 test_result_v1.append(round(sigma5_up, 2)) 244 test_result_v1.append(round(sigma5_proportion, 2)) 245 test_result_v1.append(round(sigma6_down, 2)) 246 test_result_v1.append(round(sigma6_up, 2)) 247 test_result_v1.append(round(sigma6_proportion, 2)) 248 # pass 一行处理结束 249 250 # 新增数据到excel中 251 datetime_object5 = datetime.datetime.now() 252 print("datetime_object5 :", datetime_object5) 253 if sigma_yy >= def_columns_test: 254 # print(test_result_v1) 255 one_sheet.append(test_result_v1) # 追加一行数据 256 # for row in new2_worksheet.values: # 输出所有数据 257 # print(row) 258 datetime_object6 = datetime.datetime.now() 259 print("datetime_object6 :", datetime_object6) 260 workbook.save(source_target) 261 262 print('总时间：', datetime_object6 - datetime_object1) 263 264 265 ''' 266 //==========================================================================================================// 267 // sigma_handle3(仅处理生成的BIN1文件) 268 //==========================================================================================================// 269 ''' 270 271 272 def sigma_handle3(s_file_path, s_temp_path, t_file_path): 273 datetime_object1 = datetime.datetime.now() 274 print("\n datetime_object1 :", datetime_object1) 275 276 print('\n\t 打印源目标路径1：', s_file_path) 277 print('\n\t 打印源目标路径2：', s_temp_path) 278 print('\n\t 打印源目标路径3：', t_file_path) 279 280 source_map = s_file_path 281 source_temp = s_temp_path 282 source_target = t_file_path 283 284 # 第二部分 筛选计算sigma source_temp 285 target2_workbook = load_workbook(filename=source_temp) 286 new2_worksheet = target2_workbook.active 287 288 # 创建一个新的 Excel 文件 289 workbook = openpyxl.Workbook() 290 one_sheet = workbook.active 291 # 固定格式抬头 292 test_result_v0 = ["project_name", "down_limit", "up_limit", "unit", "mean", "std", 293 "3sigma_down", "3sigma_up", "3sigma_percentage", 294 "4sigma_down", "4sigma_up", "4sigma_percentage", 295 "5sigma_down", "5sigma_up", "5sigma_percentage", 296 "6sigma_down", "6sigma_up", "6sigma_percentage", ] 297 one_sheet.append(test_result_v0) # 追加一行数据 298 299 target_rows = new2_worksheet.max_row 300 target_cols = new2_worksheet.max_column 301 print('\n target坐标：', target_rows, target_cols) 302 303 def_rows_test = 8 304 # def_columns_test = 19 305 def_columns_test = 20 # 20 306 307 for sigma_y in range(0, target_cols): # 368 308 sigma_yy = sigma_y + 1 309 each_column = [] 310 each_column2 = [] 311 test_result_v1 = [] 312 datetime_object4 = datetime.datetime.now() 313 print("\n datetime_object4 :", datetime_object4) 314 for sigma_x in range(0, target_rows): # 37 315 sigma_xx = sigma_x + 1 316 # new2_worksheet.cell(sigma_xx, sigma_yy).value 317 # print('\n 每列值：',new_worksheet.cell(sigma_xx, sigma_yy).value) 318 each_column.append(new2_worksheet.cell(sigma_xx, sigma_yy).value) 319 # # 单独增加抬头 320 if sigma_yy >= def_columns_test: 321 if (sigma_xx > 1) and (sigma_xx < 3) == 1: 322 test_result_v1.append(new2_worksheet.cell(sigma_xx, sigma_yy).value) 323 if (sigma_xx > 3) and (sigma_xx < 7) == 1: 324 test_result_v1.append(new2_worksheet.cell(sigma_xx, sigma_yy).value) 325 # 单独增加有效数据 326 if (sigma_xx >= def_rows_test) and (sigma_yy >= def_columns_test) == 1: 327 each_column2.append(new2_worksheet.cell(sigma_xx, sigma_yy).value) 328 # print('\n 每列数据1：', each_column) 329 # print('\n\t 有效数据2：', each_column2 ) 330 print('\n\t 列数--->：', sigma_yy) 331 each_column3 = each_column[7:len(each_column)] 332 333 # 去除前8个值 334 if (sigma_xx >= def_rows_test) and (sigma_yy >= def_columns_test) == 1: 335 # print('\n\t 有效数据3：', each_column3) 336 sigma_yy_effective = sigma_yy - def_columns_test + 1 337 # test_SUM = np.sum(each_column3) 338 test_MEAN = np.mean(each_column3) 339 test_STD = np.std(each_column3) 340 # print('\n test_SUM:', test_SUM) 341 print('\n test_MEAN:', test_MEAN) 342 print('\n test_STD:', test_STD) 343 344 sigma3_up = test_MEAN + 3 * test_STD 345 sigma3_down = test_MEAN - 3 * test_STD 346 sigma4_up = test_MEAN + 4 * test_STD 347 sigma4_down = test_MEAN - 4 * test_STD 348 sigma5_up = test_MEAN + 5 * test_STD 349 sigma5_down = test_MEAN - 5 * test_STD 350 sigma6_up = test_MEAN + 6 * test_STD 351 sigma6_down = test_MEAN - 6 * test_STD 352 print('sigma3: %.2f %.2f' % (sigma3_down, sigma3_up), ) 353 print('sigma4: %.2f %.2f' % (sigma4_down, sigma4_up), ) 354 print('sigma5: %.2f %.2f' % (sigma5_down, sigma5_up), ) 355 print('sigma6: %.2f %.2f' % (sigma6_down, sigma6_up), ) 356 sigma3_count = 0 357 sigma4_count = 0 358 sigma5_count = 0 359 sigma6_count = 0 360 # sigma3_proportion = 0 361 # sigma4_proportion = 0 362 # sigma5_proportion = 0 363 # sigma6_proportion = 0 364 for index_v2 in range(len(each_column3)): 365 # print(each_column3[index_v2]) 366 if (sigma3_up >= each_column3[index_v2]) and (each_column3[index_v2] >= sigma3_down): 367 sigma3_count = sigma3_count + 1 368 # print(sigma3_count,test_get_data_v1[index_v2]) 369 if (sigma4_up >= each_column3[index_v2]) and (each_column3[index_v2] >= sigma4_down): 370 sigma4_count = sigma4_count + 1 371 if (sigma5_up >= each_column3[index_v2]) and (each_column3[index_v2] >= sigma5_down): 372 sigma5_count = sigma5_count + 1 373 if (sigma6_up >= each_column3[index_v2]) and (each_column3[index_v2] >= sigma6_down): 374 sigma6_count = sigma6_count + 1 375 sigma3_proportion = (sigma3_count / len(each_column3)) * 100 376 sigma4_proportion = (sigma4_count / len(each_column3)) * 100 377 sigma5_proportion = (sigma5_count / len(each_column3)) * 100 378 sigma6_proportion = (sigma6_count / len(each_column3)) * 100 379 # print("求和值: %.12f" % test_SUM) 380 print("平均值: %.12f" % test_MEAN) 381 print("标准差: %.12f" % test_STD) 382 print("每列总数量为: ", len(each_column3)) 383 print('sigma3: %.2f %.2f' % (sigma3_down, sigma3_up), ' percent: {:.2f}%'.format(sigma3_proportion), sigma3_count) 384 print('sigma4: %.2f %.2f' % (sigma4_down, sigma4_up), ' percent: {:.2f}%'.format(sigma4_proportion), sigma4_count) 385 print('sigma5: %.2f %.2f' % (sigma5_down, sigma5_up), ' percent: {:.2f}%'.format(sigma5_proportion), sigma5_count) 386 print('sigma6: %.2f %.2f' % (sigma6_down, sigma6_up), ' percent: {:.2f}%'.format(sigma6_proportion), sigma6_count) 387 print('*****第%d笔数据***********\n' % sigma_yy_effective) 388 389 test_result_v1.append(test_MEAN) 390 test_result_v1.append(test_STD) 391 test_result_v1.append(round(sigma3_down, 2)) 392 test_result_v1.append(round(sigma3_up, 2)) 393 test_result_v1.append(round(sigma3_proportion, 2)) 394 test_result_v1.append(round(sigma4_down, 2)) 395 test_result_v1.append(round(sigma4_up, 2)) 396 test_result_v1.append(round(sigma4_proportion, 2)) 397 test_result_v1.append(round(sigma5_down, 2)) 398 test_result_v1.append(round(sigma5_up, 2)) 399 test_result_v1.append(round(sigma5_proportion, 2)) 400 test_result_v1.append(round(sigma6_down, 2)) 401 test_result_v1.append(round(sigma6_up, 2)) 402 test_result_v1.append(round(sigma6_proportion, 2)) 403 # pass 一行处理结束 404 405 # 新增数据到excel中 406 datetime_object5 = datetime.datetime.now() 407 print("\n datetime_object5 :", datetime_object5) 408 if sigma_yy >= def_columns_test: 409 print(test_result_v1) 410 one_sheet.append(test_result_v1) # 追加一行数据 411 # for row in new2_worksheet.values: # 输出所有数据 412 # print(row) 413 datetime_object6 = datetime.datetime.now() 414 print("\n datetime_object6 :", datetime_object6) 415 workbook.save(source_target) 416 417 print('总时间：', datetime_object6 - datetime_object1) 418 419 420 ''' 421 //==========================================================================================================// 422 // get_file_relative( 获取文件相对路径 ) 423 //==========================================================================================================// 424 ''' 425 426 427 def get_file_relative(file_path): 428 # print("#------------ 获取文件相对路径 ------------------#") 429 # """1、读取文件""" 430 get_work_list_a1 = os.getcwd() 431 print("当前工作路径: ", get_work_list_a1) 432 print("当前工作脚本: ", os.path.abspath(__file__)) 433 # 路径合成 434 # \3_vi4302_cp\3_vi4302_cp_data 435 get_work_list_b1 = get_work_list_a1 + file_path 436 print("当前数据路径: ", get_work_list_b1) 437 # 路径下文件 存储到列表中 438 get_work_list_c1 = [os.path.join(get_work_list_b1, file) for file in os.listdir(get_work_list_b1)] 439 # 遍历文件列表，输出文件名 440 print('#------- 当前文件夹下文件 --------# [提示：起始地址=0]') 441 # print(get_work_list_c1) # enumerate 442 for index_a1, index_a1_value in enumerate(get_work_list_c1): 443 print('文件夹第--> %d <--个文件: ' % index_a1, index_a1_value) 444 print("请输入处理的文件为: [提示：输入序号即可]") 445 input_a1 = input() 446 input_a2 = int(input_a1) 447 get_work_name_c1 = get_work_list_c1[input_a2] 448 print("输入序号为 < %s > ,处理的文件为: " % input_a1, get_work_name_c1) 449 450 result = get_work_name_c1 451 return result 452 453 454 455 ''' 456 //==========================================================================================================// 457 // sigma_4302_ft_handle1( 4302 FT ) 458 //==========================================================================================================// 459 ''' 460 def sigma_4302_ft_handle1(): 461 datetime_object1 = datetime.datetime.now() 462 print("dsigma_4302_ft_handle1_object :", datetime_object1) 463 464 # source_temp1 = "./4_vi4302_ft/3_vi4302_ft_data/" 465 outputdir_path4 = "./4_vi4302_ft/4_vi4302_ft_data_temp/" 466 result_path4 = "./4_vi4302_ft/5_vi4302_ft_data_result/vi4302_ft_sigma_result.xlsx" 467 468 469 #---处理有效数据 470 file_list1 = os.listdir(outputdir_path4) 471 print('excel文件根目录: ', file_list1) 472 for name in file_list1: 473 input_file_txt = os.path.join(outputdir_path4, name) 474 # output_file_excel = os.path.join(outputdir_path3, name).replace('.txt', '.xlsx') 475 # sheet_name = name.replace(".txt", '') 476 print("input_file_txt 路径: ",input_file_txt) 477 # print("文件名称: ",sheet_name) 478 datetime_object2 = datetime.datetime.now() 479 print("\n datetime_object2 :", datetime_object2) 480 481 # 创建一个新的 Excel 文件 482 n3_workbook = openpyxl.Workbook() 483 n3_sheet = n3_workbook.active 484 test_result_v2 = ["project_name", "up_limit", "down_limit", "unit", "mean", "std", 485 "3sigma_down", "3sigma_up", "3sigma_percentage", 486 "4sigma_down", "4sigma_up", "4sigma_percentage", 487 "5sigma_down", "5sigma_up", "5sigma_percentage", 488 "6sigma_down", "6sigma_up", "6sigma_percentage", ] 489 n3_sheet.append(test_result_v2) # 追加一行数据 490 491 492 # 导入源文件 493 s1_workbook = load_workbook(filename=input_file_txt, read_only=True) 494 sigma_f1 = s1_workbook.active 495 f1_x_rows = sigma_f1.max_row 496 f1_y_cols = sigma_f1.max_column 497 print('\n 源文件坐标范围: 行%d 列%d' % (f1_x_rows, f1_y_cols)) 498 499 datetime_object3 = datetime.datetime.now() 500 print("datetime_object3 :", datetime_object3) 501 502 # 预处理坐标点 503 def_start_line = 0 504 def_start_vertical = 3 505 list_data_all = [] 506 # 大循环 507 for ft_y1 in range(def_start_vertical, f1_y_cols): # f1_y_cols columns 508 w1_yy = ft_y1 + 1 509 sigma_yy_effective = ft_y1 510 each_data_line_all = [] 511 each_data_line = [] 512 test_result_v2 = [] 513 datetime_object4 = datetime.datetime.now() 514 print("datetime_object4 :", datetime_object4) 515 for ft_x1 in range(def_start_line, 20): # f1_y_cols columns 516 w1_xx = ft_x1 + 1 517 result_f1 = sigma_f1.cell(w1_xx, w1_yy).value 518 each_data_line_all.append(result_f1) 519 # print("result_f1 :", result_f1) 520 datetime_object5 = datetime.datetime.now() 521 # print("datetime_object5 :", datetime_object5) 522 print("\n每列的值:",each_data_line_all) 523 # "project_name", "down_limit", "up_limit", "unit", 524 test_result_v2.append(each_data_line_all[1]) 525 test_result_v2.append(each_data_line_all[3]) 526 test_result_v2.append(each_data_line_all[4]) 527 test_result_v2.append(each_data_line_all[5]) 528 each_data_line = each_data_line_all[6:len(each_data_line_all)] 529 print("\n每列计算值:", each_data_line) 530 531 test_MEAN = np.mean(each_data_line) 532 test_STD = np.std(each_data_line) 533 # print('\n test_SUM:', test_SUM) 534 print('test_MEAN:', test_MEAN) 535 print('test_STD:', test_STD) 536 537 sigma3_up = test_MEAN + 3 * test_STD 538 sigma3_down = test_MEAN - 3 * test_STD 539 sigma4_up = test_MEAN + 4 * test_STD 540 sigma4_down = test_MEAN - 4 * test_STD 541 sigma5_up = test_MEAN + 5 * test_STD 542 sigma5_down = test_MEAN - 5 * test_STD 543 sigma6_up = test_MEAN + 6 * test_STD 544 sigma6_down = test_MEAN - 6 * test_STD 545 print('sigma3: %.2f %.2f' % (sigma3_down, sigma3_up), ) 546 print('sigma4: %.2f %.2f' % (sigma4_down, sigma4_up), ) 547 print('sigma5: %.2f %.2f' % (sigma5_down, sigma5_up), ) 548 print('sigma6: %.2f %.2f' % (sigma6_down, sigma6_up), ) 549 550 sigma3_count = 0 551 sigma4_count = 0 552 sigma5_count = 0 553 sigma6_count = 0 554 # sigma3_proportion = 0 555 # sigma4_proportion = 0 556 # sigma5_proportion = 0 557 # sigma6_proportion = 0 558 for index_v2 in range(len(each_data_line)): 559 # print(each_column3[index_v2]) 560 if (sigma3_up >= each_data_line[index_v2]) and (each_data_line[index_v2] >= sigma3_down): 561 sigma3_count = sigma3_count + 1 562 # print(sigma3_count,test_get_data_v1[index_v2]) 563 if (sigma4_up >= each_data_line[index_v2]) and (each_data_line[index_v2] >= sigma4_down): 564 sigma4_count = sigma4_count + 1 565 if (sigma5_up >= each_data_line[index_v2]) and (each_data_line[index_v2] >= sigma5_down): 566 sigma5_count = sigma5_count + 1 567 if (sigma6_up >= each_data_line[index_v2]) and (each_data_line[index_v2] >= sigma6_down): 568 sigma6_count = sigma6_count + 1 569 sigma3_proportion = (sigma3_count / len(each_data_line)) * 100 570 sigma4_proportion = (sigma4_count / len(each_data_line)) * 100 571 sigma5_proportion = (sigma5_count / len(each_data_line)) * 100 572 sigma6_proportion = (sigma6_count / len(each_data_line)) * 100 573 # print("求和值: %.12f" % test_SUM) 574 print("平均值: %.12f" % test_MEAN) 575 print("标准差: %.12f" % test_STD) 576 print("每列总数量为: ", len(each_data_line)) 577 print('sigma3: %.2f %.2f' % (sigma3_down, sigma3_up), ' percent: {:.2f}%'.format(sigma3_proportion), sigma3_count) 578 print('sigma4: %.2f %.2f' % (sigma4_down, sigma4_up), ' percent: {:.2f}%'.format(sigma4_proportion), sigma4_count) 579 print('sigma5: %.2f %.2f' % (sigma5_down, sigma5_up), ' percent: {:.2f}%'.format(sigma5_proportion), sigma5_count) 580 print('sigma6: %.2f %.2f' % (sigma6_down, sigma6_up), ' percent: {:.2f}%'.format(sigma6_proportion), sigma6_count) 581 582 test_result_v2.append(test_MEAN) 583 test_result_v2.append(test_STD) 584 test_result_v2.append(round(sigma3_down, 2)) 585 test_result_v2.append(round(sigma3_up, 2)) 586 test_result_v2.append(round(sigma3_proportion, 2)) 587 test_result_v2.append(round(sigma4_down, 2)) 588 test_result_v2.append(round(sigma4_up, 2)) 589 test_result_v2.append(round(sigma4_proportion, 2)) 590 test_result_v2.append(round(sigma5_down, 2)) 591 test_result_v2.append(round(sigma5_up, 2)) 592 test_result_v2.append(round(sigma5_proportion, 2)) 593 test_result_v2.append(round(sigma6_down, 2)) 594 test_result_v2.append(round(sigma6_up, 2)) 595 test_result_v2.append(round(sigma6_proportion, 2)) 596 print(test_result_v2) 597 n3_sheet.append(test_result_v2) # 追加一行数据 598 # pass 一行处理结束 599 print('#*****第%s笔数据-结束***********#\n' % sigma_yy_effective) 600 601 n3_workbook.save(result_path4) 602 datetime_object_e = datetime.datetime.now() 603 print("datetime_object_e :", datetime_object_e) 604 print("总时间: ",datetime_object_e - datetime_object1) 605 606 607 # def lie_time(): 608 # 609 # data = np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]) 610 # for column in range(data.shape[1]): 611 # col_data = data[:, column] 612 # print(f"Column {column}: {col_data}") 613 614 # def excel_mas(): 615 # # 用于excel合并 616 # pass 617 # # wb = load_workbook(filename=input_file_txt, read_only=True) 618 # # ws = wb.active 619 # # data = [] 620 # # # for row in ws.rows: 621 # # for row in ws.rows: 622 # # neRow = [] 623 # # for cell in 100: 624 # # neRow.append(cell.value) 625 # # print(neRow) 626 # # # if len(neRow): 627 # # # data.append(neRow) 628 # 629 630 631 632 ''' 633 //==========================================================================================================// 634 // csv_save_as_xlsx( 4302 FT ) 635 //==========================================================================================================// 636 ''' 637 def csv_save_as_xlsx(): 638 data_path = r'./4_vi4302_ft/1_vi4302_ft_data_csv/20231201_csv/' 639 print("源文件路径：", data_path) 640 # for dirpath,dirname,filenames in os.walk(data_path): #os.walk()遍历文件 641 # for fname in filenames: 642 # file_name=os.path.join(dirpath,fname) #os.path.join()合并路径 643 # print(file_name) 644 # # C:\Users\chucheng.cao\Desktop\work\python\test_data_sigma\handle_sigma_vi4302\4_vi4302_ft\1_vi4302_ft_data_csv\20231201_csv 645 # sd = r"C:\Users\chucheng.cao\Desktop\work\python\test_data_sigma\handle_sigma_vi4302\4_vi4302_ft\1_vi4302_ft_data_csv\20231201_csv\1.csv" 646 # df = pd.DataFrame(pd.read_csv(file_name, header=1)) 647 # print(df) 648 # # if file_name.endswith('.csv'): 649 # # df=pd.read_csv(file_name, encoding='utf-8',header=None,sep = None) 650 # # print(df) 651 # # floder,name=os.path.splitext(file_name) #分割后缀 652 # # df.to_excel(f"{floder}.xlsx",index=False) 653 # # print(f'{file_name} 转换成功啦！O(∩_∩)O哈哈~') 654 # # print('-----------------------------------------------------------------------------------------------------------') 655 # # os.remove(file_name) 656 # print('finished...') 657 658 # dir_path = r'./4_vi4302_ft/1_vi4302_ft_data_csv/20231201_zip/' 659 # print('\n总目录-路径：', dir_path) 660 # 661 # file_ls = os.listdir(dir_path) # 获取所有文件名称 662 # for index_a1 in range(len(file_ls)): 663 # print('第一级子目录-内容：', file_ls[index_a1]) 664 # dir_path_s4 = dir_path 665 # print("传入路径：",dir_path_s4) 666 # csv_zip_decompress(dir_path_s4, file_ls[index_a1]) 667 668 # # TODO 1.解析源压缩文件zip, 获取文件夹中的FT文件夹中.csv格式文件 2.对.csv 格式文件转换成xlsx格式，各自保存文件 669 # # TODO 目前进展，可以加解压文件，周一需要开发后续衔接文件 670 671 # s_csv_data = r'C:\Users\chucheng.cao\Desktop\work\python\test_data_sigma\handle_sigma_vi4302\4_vi4302_ft\handle_data\VI4302LCT-AAAZ-(5.26)20230513004\NABX48.00_FT1\VI4302LCT-AAAZ_NABX48.00_FT1_ND05CJ488_FT20230513004_2023MAY26004744_dlogTDO.csv' 672 # r_csv_data = r'C:\Users\chucheng.cao\Desktop\work\python\test_data_sigma\handle_sigma_vi4302\4_vi4302_ft\1_vi4302_ft_data_csv\20231201_csv\1.xlsx' 673 # # csv_to_xlsx_pd2(s_csv_data, r_csv_data) 674 # 675 # print('开始处理%s' % s_csv_data) 676 # curr_time = datetime.datetime.now() 677 # print(curr_time) 678 # 679 # f = open(s_csv_data, 'r', encoding='utf-8') 680 # # 创建一个workbook 设置编码 681 # workbook = Workbook() 682 # # 创建一个worksheet 683 # worksheet = workbook.active 684 # workbook.title = 'sheet' 685 # 686 # for line in f: 687 # row = line.split(',') 688 # worksheet.append(row) 689 # if row[0].endswith('000'): # 每一百行打印一次 690 # print(line, end="") 691 # 692 # workbook.save(r_csv_data) 693 # print('处理完毕') 694 # curr_time2 = datetime.datetime.now() 695 # print(curr_time2 - curr_time) 696 697 698 699 700 701 702 703 ''' 704 #---------------------------------------------# 705 # csv_zip_decompress 解压函数 706 # 输入：相对路径 和 文件名称 707 #---------------------------------------------# 708 ''' 709 710 def csv_zip_decompress(dir_path_s4,file_name_value_s4): 711 #TODO 以下可提炼成函数处理 712 path_s4_a1 = dir_path_s4 713 name_value_a1 = file_name_value_s4 714 file_name = path_s4_a1 + name_value_a1 715 # print('\n file_name-相对路径+文件名拼接:', file_name) 716 file_name2 = file_name.replace('./', '/') 717 # print('\n file_name2-相对路径替换:', file_name2) 718 file_name3 = file_name2.replace('/', '\\') 719 # print('\n file_name3-相对路径替换:', file_name3) 720 path = os.path.dirname(__file__) if len(os.path.dirname(__file__)) != 0 else '.' 721 file_name4 = path + file_name3 722 # print('\n file_name4-拼接绝对路径： ', file_name4) 723 file_name5 = file_name4.replace('\\', '\\\\') 724 # print('\n file_name5-拼接绝对路径：', file_name5) 725 if os.path.exists(file_name5): 726 print('\n\t\t 文件路径存在!') 727 pass 728 else: 729 print('文件路径不存在') 730 # 传入压缩文件zfile.zip获取相关信息 731 zip_file = zipfile.ZipFile(file_name4, 'r') # file_path path_jd2 732 print('\n 传入压缩文件:',zip_file) 733 # 获取压缩文件中的内容 734 f_content = zip_file.namelist() 735 print('\n 获取压缩文件中的内容:', f_content) 736 737 # 获取当前文件上级路径，再拼接路径 738 CSV_handle1 = os.path.dirname(os.path.abspath(__file__)) 739 print('\n\t\t CSV_handle1', CSV_handle1) 740 # TODO 指定解压到特定路径 741 # C:\Users\chucheng.cao\Desktop\work\python\test_data_sigma\handle_sigma_vi4302\4_vi4302_ft\handle_data 742 # CSV_handle2 = CSV_handle1 + '\handle_data' 743 # C:\Users\chucheng.cao\Desktop\work\python\test_data_sigma\handle_sigma_vi4302\4_vi4302_ft\1_vi4302_ft_data_csv\20231201_zip\handle_data 744 # C:\Users\chucheng.cao\Desktop\work\python\test_data_sigma\handle_sigma_vi4302\4_vi4302_ft\1_vi4302_ft_data_csv\20231201_zip 745 # \4_vi4302_ft\1_vi4302_ft_data_csv\20231201_csv\handle_data 746 CSV_handle2 = CSV_handle1 + r'\4_vi4302_ft\1_vi4302_ft_data_csv\20231201_csv\handle_data' 747 # C:\Users\chucheng.cao\Desktop\work\python\test_data_sigma\handle_sigma_vi4302\4_vi4302_ft\1_vi4302_ft_data_csv\20231201_csv\handle_data 748 CSV_handle2 = r'C:\Users\chucheng.cao\Desktop\work\python\test_data_sigma\handle_sigma_vi4302\4_vi4302_ft\handle_data' 749 print('\n\t\t CSV_handle2', CSV_handle2) 750 751 print("解压开始") 752 zip_extract = zip_file.extractall(CSV_handle2) 753 print(zip_extract) 754 # # zip_extract.close() 755 print("结束解压") 756 757 758 ''' 759 #---------------------------------------------# 760 # csv_to_xlsx_pd 函数 CSV转EXCEL 761 # 输入：相对路径 和 文件名称 762 #---------------------------------------------# 763 ''' 764 def csv_to_xlsx_pd(sourcePath: str, savePath: str, encode='utf-8'): 765 """将csv 转为 excel（.xlsx格式） 766 如果不需要可以把计时相关代码删除 767 Args: 768 sourcePath:str 来源文件路径 769 savePath:str 保存文件路径，需要包含保存的文件名，文件名需要是 xlsx 格式的 770 encode='utf-8' 默认编码，可以改为需要的编码如gbk 771 """ 772 print('开始处理%s' % sourcePath) 773 curr_time = datetime.datetime.now() 774 print(curr_time) 775 776 f = open(sourcePath, 'r', encoding=encode) 777 # 创建一个workbook 设置编码 778 workbook = Workbook() 779 # 创建一个worksheet 780 worksheet = workbook.active 781 workbook.title = 'sheet' 782 783 for line in f: 784 row = line.split(',') 785 worksheet.append(row) 786 if row[0].endswith('000'): # 每一百行打印一次 787 print(line, end="") 788 789 workbook.save(savePath) 790 print('处理完毕') 791 curr_time2 = datetime.datetime.now() 792 print(curr_time2 - curr_time) 793 794 ''' 795 #---------------------------------------------# 796 # csv_to_xlsx_pd2 函数 CSV转EXCEL 797 # 输入：相对路径 和 文件名称 798 #---------------------------------------------# 799 ''' 800 801 def csv_to_xlsx_pd2(s_csv_data1, r_csv_data2): 802 input_data_csv = s_csv_data1 803 output_data_excel = r_csv_data2 804 csv = pd.read_csv(input_data_csv, encoding='utf-8') 805 csv.to_excel(output_data_excel, sheet_name='sheet') 806 807 808 809 810 ''' 811 //==========================================================================================================// 812 // sigma_4302_ft_handle2( 4302 FT ) 813 //==========================================================================================================// 814 ''' 815 def sigma_4302_ft_handle2(): 816 datetime_object1 = datetime.datetime.now() 817 print("\n 开始：dsigma_4302_ft_handle1_object :", datetime_object1) 818 819 # source_temp1 = "./4_vi4302_ft/3_vi4302_ft_data/" 820 outputdir_path4 = "./4_vi4302_ft/4_vi4302_ft_data_temp/Summary/" 821 result_path4 = "./4_vi4302_ft/5_vi4302_ft_data_result/vi4302_ft_sigma_result.xlsx" 822 823 #---处理有效数据 824 file_list1 = os.listdir(outputdir_path4) 825 print('excel文件根目录: ', file_list1) 826 for name in file_list1: 827 datetime_object2 = datetime.datetime.now() 828 print("\n处理汇总文件开始 : datetime_object2：", datetime_object2) 829 830 input_file_txt = os.path.join(outputdir_path4, name) 831 # output_file_excel = os.path.join(outputdir_path3, name).replace('.txt', '.xlsx') 832 # sheet_name = name.replace(".txt", '') 833 print("input_file_txt 路径: ",input_file_txt) 834 # print("文件名称: ",sheet_name) 835 datetime_object2 = datetime.datetime.now() 836 print("datetime_object2 :", datetime_object2) 837 838 # 创建一个新的 Excel 文件 839 n3_workbook = openpyxl.Workbook() 840 n3_sheet = n3_workbook.active 841 test_result_v2 = ["project_name", "up_limit", "down_limit", "unit", "mean", "std", 842 "3sigma_down", "3sigma_up", "3sigma_percentage", 843 "4sigma_down", "4sigma_up", "4sigma_percentage", 844 "5sigma_down", "5sigma_up", "5sigma_percentage", 845 "6sigma_down", "6sigma_up", "6sigma_percentage", ] 846 n3_sheet.append(test_result_v2) # 追加一行数据 847 # 时间戳 848 datetime_object3 = datetime.datetime.now() 849 print("\n模版导入完成 : datetime_object3：", datetime_object3, '\t耗时：',(datetime_object3 - datetime_object2)) 850 851 # # 导入源文件 852 s2_workbook = load_workbook(filename=input_file_txt, read_only=False) # read_only=True read_only=False 853 sigma_f2 = s2_workbook.active 854 f2_x_rows = sigma_f2.max_row 855 f2_y_cols = sigma_f2.max_column 856 print('源文件坐标范围: 行: %d 列: %d' % (f2_x_rows, f2_y_cols)) 857 # 按列获取值 858 def_min_row = 0 # 7 859 def_min_col = 4 860 sigma_yy_effective = 0 861 # 按照从上到下扫描 862 for erect_y2 in sigma_f2.iter_cols(min_row=def_min_row, max_row=f2_x_rows, min_col=def_min_col, max_col=f2_y_cols): # f2_x_rows--- f2_y_cols 863 erect_list_s2_all = [] 864 erect_list_s2 =[] 865 sigma_yy_effective = sigma_yy_effective + 1 866 # 时间戳 867 datetime_object4_1 = datetime.datetime.now() 868 # print("\n目标解析-每列开始 : datetime_object2：", datetime_object4_1, ) 869 test_result_v2 = [] 870 871 for erect_line_y in erect_y2: 872 # print("单元格的值：",erect_line_y.value) # 每个单元格的值 873 table2_s2 = erect_line_y.value 874 erect_list_s2_all.append(table2_s2) 875 # print("\n每列数值： ", erect_list_s2_all) 876 test_result_v2.append(erect_list_s2_all[1]) 877 test_result_v2.append(erect_list_s2_all[3]) 878 test_result_v2.append(erect_list_s2_all[4]) 879 test_result_v2.append(erect_list_s2_all[5]) 880 # 时间戳 881 datetime_object4 = datetime.datetime.now() 882 # print("目标解析-每列完成 : datetime_object4：", datetime_object4, '\t耗时：', (datetime_object4 - datetime_object4_1)) 883 # 计算值 884 erect_list_s2 = erect_list_s2_all[7:len(erect_list_s2_all)] 885 test_SUM = np.sum(erect_list_s2) 886 test_MEAN = np.mean(erect_list_s2) 887 test_STD = np.std(erect_list_s2) 888 # print('test_SUM:', test_SUM) 889 # print('test_MEAN:', test_MEAN) 890 # print('test_STD:', test_STD) 891 892 # -sigma 893 sigma3_up = test_MEAN + 3 * test_STD 894 sigma3_down = test_MEAN - 3 * test_STD 895 sigma4_up = test_MEAN + 4 * test_STD 896 sigma4_down = test_MEAN - 4 * test_STD 897 sigma5_up = test_MEAN + 5 * test_STD 898 sigma5_down = test_MEAN - 5 * test_STD 899 sigma6_up = test_MEAN + 6 * test_STD 900 sigma6_down = test_MEAN - 6 * test_STD 901 # print('sigma3: %.2f %.2f' % (sigma3_down, sigma3_up), ) 902 # print('sigma4: %.2f %.2f' % (sigma4_down, sigma4_up), ) 903 # print('sigma5: %.2f %.2f' % (sigma5_down, sigma5_up), ) 904 # print('sigma6: %.2f %.2f' % (sigma6_down, sigma6_up), ) 905 906 sigma3_count = 0 907 sigma4_count = 0 908 sigma5_count = 0 909 sigma6_count = 0 910 sigma3_proportion = 0 911 sigma4_proportion = 0 912 sigma5_proportion = 0 913 sigma6_proportion = 0 914 for index_v2 in range(len(erect_list_s2)): 915 # print(each_column3[index_v2]) 916 if (sigma3_up >= erect_list_s2[index_v2]) and (erect_list_s2[index_v2] >= sigma3_down): 917 sigma3_count = sigma3_count + 1 918 # print(sigma3_count,test_get_data_v1[index_v2]) 919 if (sigma4_up >= erect_list_s2[index_v2]) and (erect_list_s2[index_v2] >= sigma4_down): 920 sigma4_count = sigma4_count + 1 921 if (sigma5_up >= erect_list_s2[index_v2]) and (erect_list_s2[index_v2] >= sigma5_down): 922 sigma5_count = sigma5_count + 1 923 if (sigma6_up >= erect_list_s2[index_v2]) and (erect_list_s2[index_v2] >= sigma6_down): 924 sigma6_count = sigma6_count + 1 925 sigma3_proportion = (sigma3_count / len(erect_list_s2)) * 100 926 sigma4_proportion = (sigma4_count / len(erect_list_s2)) * 100 927 sigma5_proportion = (sigma5_count / len(erect_list_s2)) * 100 928 sigma6_proportion = (sigma6_count / len(erect_list_s2)) * 100 929 # print("求和值: %.12f" % test_SUM) 930 # print("平均值: %.12f" % test_MEAN) 931 # print("标准差: %.12f" % test_STD) 932 # print("每列总数量为: ", len(erect_list_s2)) 933 # print('sigma3: %.2f %.2f' % (sigma3_down, sigma3_up), ' percent: {:.2f}%'.format(sigma3_proportion), sigma3_count) 934 # print('sigma4: %.2f %.2f' % (sigma4_down, sigma4_up), ' percent: {:.2f}%'.format(sigma4_proportion), sigma4_count) 935 # print('sigma5: %.2f %.2f' % (sigma5_down, sigma5_up), ' percent: {:.2f}%'.format(sigma5_proportion), sigma5_count) 936 # print('sigma6: %.2f %.2f' % (sigma6_down, sigma6_up), ' percent: {:.2f}%'.format(sigma6_proportion), sigma6_count) 937 938 test_result_v2.append(test_MEAN) 939 test_result_v2.append(test_STD) 940 test_result_v2.append(round(sigma3_down, 2)) 941 test_result_v2.append(round(sigma3_up, 2)) 942 test_result_v2.append(round(sigma3_proportion, 2)) 943 test_result_v2.append(round(sigma4_down, 2)) 944 test_result_v2.append(round(sigma4_up, 2)) 945 test_result_v2.append(round(sigma4_proportion, 2)) 946 test_result_v2.append(round(sigma5_down, 2)) 947 test_result_v2.append(round(sigma5_up, 2)) 948 test_result_v2.append(round(sigma5_proportion, 2)) 949 test_result_v2.append(round(sigma6_down, 2)) 950 test_result_v2.append(round(sigma6_up, 2)) 951 test_result_v2.append(round(sigma6_proportion, 2)) 952 # print("每行结果数据：",test_result_v2) 953 n3_sheet.append(test_result_v2) # 追加一行数据 954 955 # pass 一行处理结束 956 # 时间戳 957 datetime_object5 = datetime.datetime.now() 958 # print("目标计算-每列完成 : datetime_object5：", datetime_object5, '\t耗时：', (datetime_object5 - datetime_object4)) 959 print("目标文件-每列完成耗时 ", (datetime_object5 - datetime_object4_1)) 960 print('#*****第%s笔数据-结束***********#\n' % sigma_yy_effective) 961 962 # 每行耗时 963 datetime_object6 = datetime.datetime.now() 964 test_result_v2=[] 965 print("目标文件-每列完成 : datetime_object6：", datetime_object6, '\t耗时：', (datetime_object6 - datetime_object3)) 966 print("整个文件完成生成") 967 968 n3_workbook.save(result_path4) 969 datetime_object_e = datetime.datetime.now() 970 print("datetime_object_e :", datetime_object_e) 971 print("总时间: ",datetime_object_e - datetime_object1) 972 973 974 975 ''' 976 //==========================================================================================================// 977 // sigma_4302_ft_get_bin1( 4302 FT ) 978 //==========================================================================================================// 979 ''' 980 def sigma_4302_ft_get_bin1(): 981 # 3_vi4302_ft_data 982 datetime_object_s = datetime.datetime.now() 983 print("datetime_object_s :", datetime_object_s) 984 source_path4 = "./4_vi4302_ft/3_vi4302_ft_data/" 985 outputdir_path5 = "./4_vi4302_ft/4_vi4302_ft_data_temp/Summary_Template1.xlsx" 986 outputdir_path9 = "./4_vi4302_ft/4_vi4302_ft_data_temp/Summary.xlsx" 987 # C:\Users\chucheng.cao\Desktop\work\python\test_data_sigma\handle_sigma_vi4302\4_vi4302_ft\4_vi4302_ft_data_temp 988 # outputdir_path4 = "./4_vi4302_ft/4_vi4302_ft_data_temp/" 989 # result_path4 = "./4_vi4302_ft/5_vi4302_ft_data_result/vi4302_ft_sigma_result.xlsx" 990 991 992 # # 创建一个新的 Excel 文件 993 h1_workbook = openpyxl.Workbook() 994 h1_sheet = h1_workbook.active 995 996 # 导入已经存在的模版文件 997 h2_workbook = load_workbook(filename= outputdir_path5, read_only=True) 998 h2_work = h2_workbook.active 999 w2_x_rows = h2_work.max_row 1000 w2_y_cols = h2_work.max_column 1001 print('\n 源文件坐标范围: ', w2_x_rows, w2_y_cols) 1002 1003 # 目的是导入头文件 --典型模版 1004 for h2_x2 in h2_work.iter_rows(min_row=0, max_row=w2_x_rows, min_col=0, max_col=w2_y_cols): # w1_x_rows ----- w1_y_cols 1005 each_h2_x2_s1 = [] 1006 for each_call_y1 in h2_x2: 1007 cell_u2 = each_call_y1.value # 单元格值 1008 # print(type(cell_url),cell_url) 1009 each_h2_x2_s1.append(cell_u2) 1010 # print('遍历行值:',each_h2_x2_s1) 1011 h1_sheet.append(each_h2_x2_s1) # 追加一行数据 1012 1013 datetime_object_1 = datetime.datetime.now() 1014 print("完成模版导入 -- datetime1 :", datetime_object_1 , "\t 耗时：",(datetime_object_1 - datetime_object_s)) 1015 1016 # ---处理有效数据 1017 file_list2 = os.listdir(source_path4) 1018 print('excel文件根目录: ', file_list2) 1019 file_list2_path = [] 1020 for name in file_list2: 1021 input_file_txt = os.path.join(source_path4, name) 1022 file_list2_path.append(input_file_txt) 1023 print(input_file_txt) 1024 # print(name) 1025 print("获取合并文件列表：", file_list2_path) 1026 1027 datetime_object_2 = datetime.datetime.now() 1028 print("获取文件完成 -- datetime2 :", datetime_object_2, "\t 耗时：", (datetime_object_2 - datetime_object_1)) 1029 1030 for file_s1 in range(len(file_list2_path)): 1031 # 导入文件夹已经存在的文件 1032 datetime_object_3_1 = datetime.datetime.now() 1033 print("获取目标文件开始 -- datetime3_1 :", datetime_object_3_1, ) 1034 f2_workbook = load_workbook(filename=file_list2_path[file_s1], read_only=True) 1035 f2_work = f2_workbook.active 1036 # work_sheet = work_book[work_book.sheetnames[0]] # 选中第一个sheet 1037 w1_x_rows = f2_work.max_row 1038 w1_y_cols = f2_work.max_column 1039 print('源文件坐标范围: ', w1_x_rows, w1_y_cols) 1040 # 遍历行 1041 def_min_row = 97 1042 def_min_col = 0 # 4 1043 BIN1 = 1 1044 each_line_all = [] 1045 # min_row=1, max_row=3, min_col=1,max_col=3 iter_cols 1046 # for line_x1 in f2_work.iter_rows(min_row=def_min_row, max_row=109, min_col=def_min_col, max_col=10): 1047 for line_x1 in f2_work.iter_rows(min_row=def_min_row, max_row=w1_x_rows, min_col=def_min_col, max_col=w1_y_cols): # w1_x_rows ---- w1_y_cols 1048 # print("遍历行(元祖): ",type(row_x1)) 1049 each_line_s2 = [] 1050 for each_call_y1 in line_x1: 1051 cell_url = each_call_y1.value # 单元格值 1052 # print("--",cell_url) 1053 each_line_s2.append(cell_url) 1054 # print('遍历行值:',each_line_s2) 1055 if each_line_s2[1] == BIN1: 1056 h1_sheet.append(each_line_s2) # 追加一行数据 1057 1058 datetime_object_3_2 = datetime.datetime.now() 1059 print("获取目标文件完成 -- datetime3_2 :", datetime_object_3_2, "\t 耗时：", (datetime_object_3_2 - datetime_object_3_1)) 1060 datetime_object_4 = datetime.datetime.now() 1061 print("\n数据合并完成 -- datetime4 :", datetime_object_4, "\t 耗时：", (datetime_object_4 - datetime_object_2)) 1062 1063 # print("整个列表：",each_line_all) 1064 #结果保存新文件 1065 h1_workbook.save(outputdir_path9) 1066 1067 # sigma_4302_ft_handle2() 1068 1069 1070 1071 ''' 1072 //==========================================================================================================// 1073 // sop_s1_d1(vi4302 cp 原始数据扫描，识别异常数据 ) 1074 //==========================================================================================================// 1075 ''' 1076 1077 1078 def sop_s1_d1(): 1079 sub_file_path = r'\3_vi4302_cp\3_vi4302_cp_data' 1080 source_file_path = get_file_relative(sub_file_path) 1081 source_temp_path = './3_vi4302_cp/3_vi4302_cp_data/module_3.xlsx' 1082 target_file_path = './3_vi4302_cp/4_vi4302_cp_data_result/module_3_result.xlsx' 1083 sigma_handle_scan(source_file_path, source_temp_path, target_file_path) 1084 1085 1086 ''' 1087 //==========================================================================================================// 1088 // sop_s1_d2(vi4302 cp 原始数据扫描，识别异常数据 ) 1089 //==========================================================================================================// 1090 ''' 1091 1092 1093 def sop_s1_d2(): 1094 sub_file_path = r'\3_vi4302_cp\3_vi4302_cp_data' 1095 source_file_path = get_file_relative(sub_file_path) 1096 source_temp_path = './3_vi4302_cp/3_vi4302_cp_data/module_3.xlsx' 1097 target_file_path = './3_vi4302_cp/4_vi4302_cp_data_result/module_3_result.xlsx' 1098 sigma_handle2(source_file_path, source_temp_path, target_file_path) 1099 1100 1101 1102 ''' 1103 //==========================================================================================================// 1104 // sop_s2_d1(vi4302 FT 原始数据 ) 1105 //==========================================================================================================// 1106 ''' 1107 1108 1109 def sop_s2_d1(): 1110 # csv_save_as_xlsx() # CSV -> xlsx 1111 # sigma_4302_ft_get_bin1() # 合并数据，只保留BIN1 1112 sigma_4302_ft_handle2() # 汇总数据，生成sigma 1113 1114 1115 1116 1117 # ====================================================================================================================== 1118 ''' 1119 # ====================================================================================================================== 1120 # 测试专用 1121 # ====================================================================================================================== 1122 ''' 1123 if __name__ == "__main__": 1124 print('测试开始') 1125 # sop_s1_d1() 1126 # sop_s1_d2() 1127 # sop_s2_d1() 1128 # ---------------------------------------------# 1129 run_big_cycle1 = 1 1130 while run_big_cycle1: 1131 print("请选择以下场景: [提示 0:表示退出]") 1132 print("scenario 1: VI4302 CP sigma 场景") 1133 print("scenario 2: VI4302 FT sigma 场景") 1134 print(">>> 请输入功能:") 1135 function_order = input() 1136 if function_order == "0": 1137 print("退出运行") 1138 run_big_cycle1 = 0 1139 elif function_order == "1": 1140 print("scenario 1") 1141 run_s1 = 1 1142 while run_s1: 1143 print("scenario_1场景: [提示 0:表示退出]") 1144 print("scenario_1 1: VI4302 CP sigma raw data scan (识别出异常数据)") 1145 print("scenario_1 2: VI4302 CP sigma raw data to result (excel格式结果)") 1146 print(">>> 请输入功能:") 1147 function_order_s1 = input() 1148 if function_order_s1 == "0": 1149 print("退出运行") 1150 run_s1 = 0 1151 elif function_order_s1 == "1": 1152 sop_s1_d1() 1153 print("完成s1 1功能") 1154 elif function_order_s1 == "2": 1155 sop_s1_d2() 1156 print("完成s1 2功能") 1157 else: 1158 print("输入无效数值！") 1159 1160 print("继续执行: y 返回上一级:b") 1161 mean_m = input() 1162 if mean_m == "b": 1163 function_order_s1 = "0" 1164 run_s1 = 0 1165 1166 elif function_order == "2": 1167 print("scenario 2") 1168 1169 1170 else: 1171 print("没有此功能请重新选择") 1172 1173 # source_file_path = get_file_relative() 1174 # source_temp_path = './3_vi4302_cp_data/module_3.xlsx' 1175 # target_file_path = './4_vi4302_cp_data_result/module_3_result.xlsx' 1176 # 1177 # print("#----- 是否规整原始数据 -------# [提示输入: y or n ]") 1178 # scan_data = input() 1179 # if scan_data == 'y': 1180 # sigma_handle_scan(source_file_path, source_temp_path, target_file_path) 1181 # else: 1182 # print("处理数据中") 1183 # sigma_handle2(source_file_path, source_temp_path, target_file_path)
# ---- code start ----#
界面交互功能套用模型