晶圆 map 类工具
1.开发思路
1.1 先分场景类
1.2 对excel的内容的操作，模仿手工的操作流程
2.关键点
2.1 openpyxl 模块的使用为主
2.2 根据需求重新的整理
#---- 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 # import pandas as pd 15 import codecs 16 17 # from openpyxl.utils import get_column_letter 18 19 ''' 20 //==========================================================================================================// 21 // get_file_relative( ) 22 //==========================================================================================================// 23 ''' 24 25 26 def get_file_relative(): 27 print("#------------ 获取文件相对路径 ------------------#") 28 # """1、读取文件""" 29 get_work_list_a1 = os.getcwd() 30 print("\n当前工作路径: ", get_work_list_a1) 31 print("\n当前工作脚本: ", os.path.abspath(__file__)) 32 # 路径合成 33 get_work_list_b1 = get_work_list_a1 + r'\scenario_1' 34 print("\n当前数据路径: ", get_work_list_b1) 35 36 # 路径下文件 存储到列表中 37 get_work_list_c1 = [os.path.join(get_work_list_b1, file) for file in os.listdir(get_work_list_b1)] 38 39 # 遍历文件列表，输出文件名 40 print('\n#------- 当前文件夹下文件 --------# [提示：起始地址=0]') 41 42 # print(get_work_list_c1) # enumerate 43 for index_a1, index_a1_value in enumerate(get_work_list_c1): 44 print('文件夹第 --->%d <---个文件: ' % index_a1, index_a1_value) 45 46 print("\n请输入处理的文件为:_______ [提示：输入序号即可]") 47 input_a1 = input() 48 input_a2 = int(input_a1) 49 get_work_name_c1 = get_work_list_c1[input_a2] 50 print("\n输入序号为 < %s > ,处理的文件为: " % input_a1, get_work_name_c1) 51 52 result = get_work_name_c1 53 return result 54 55 56 ''' 57 //==========================================================================================================// 58 // txt_to_excel_s1_d1 （s1） 59 //==========================================================================================================// 60 ''' 61 62 63 def txt_to_excel_s1_d1(filename_s, outfile_s): 64 obj_f_read = codecs.open(filename_s, 'r') 65 wb = openpyxl.Workbook() 66 ws = wb.active 67 row_x1 = 0 68 for line_list in obj_f_read: 69 row_x1 = row_x1 + 1 70 if row_x1 > 11: 71 for index_a1, index_a1_value in enumerate(line_list): 72 # print('文件中第 -->%d <--个字符件: ' % index_a1, type(index_a1_value),index_a1_value) 73 index_a1_y1 = index_a1 + 1 74 ws.cell(row=row_x1, column=index_a1_y1, value=index_a1_value) 75 else: 76 ws.cell(row=row_x1, column=1, value=line_list) 77 wb.save(outfile_s) 78 outfile_s2 = "./scenario_1/2_txt_to_excel_source/general.xlsx" 79 wb.save(outfile_s2) 80 81 82 ''' 83 //==========================================================================================================// 84 // sigma_handle_scan 85 //==========================================================================================================// 86 ''' 87 88 89 def compare_handle_s1_d2(s_file_path, s_temp_path, t_file_path): 90 datetime_object_s = datetime.datetime.now() 91 print("\n datetime_object_s :", datetime_object_s) 92 93 source_map = s_file_path 94 source_temp = s_temp_path 95 source_target = t_file_path 96 97 # 源文件 98 # 预处理原点 99 def_origin_start_rows = 11 100 def_origin_start_cols = 1 101 h1_workbook = load_workbook(filename=source_map) 102 worksheet_h1 = h1_workbook.active 103 target_rows_x1 = worksheet_h1.max_row 104 target_cols_y1 = worksheet_h1.max_column 105 print('\n 源坐标范围：', target_rows_x1, target_cols_y1) 106 107 # ----------------------------------------------------------- # 108 # 目标文件 109 # 预处理原点 110 def_origin_start_rows_t1 = 4 111 def_origin_start_cols_t1 = 3 112 h2_workbook = load_workbook(filename=source_temp) 113 worksheet_h2 = h2_workbook.active 114 target_rows_x2 = worksheet_h2.max_row 115 target_cols_y2 = worksheet_h2.max_column 116 print('\n 目标坐标范围：', target_rows_x2, target_cols_y2) 117 # max_row_x3 = max(target_rows_x1,target_rows_x2) 118 # max_cols_y3 = max(target_cols_y1, target_cols_y2) 119 max_row_x3 = target_rows_x1 120 max_cols_y3 = target_cols_y1 121 print("\n 取范围最大值:", max_row_x3, max_cols_y3) 122 123 # # ----------------------------------------------------------- # 124 # # 合成新文件 125 #fill0 = PatternFill('solid', fgColor='aaff32') 126 fill1 = PatternFill('solid', fgColor='FFFF00') 127 fill2 = PatternFill('solid', fgColor='ceb301') 128 fill3 = PatternFill('solid', fgColor='ff796c') 129 fill4 = PatternFill('solid', fgColor='f97306') 130 # fill_w1 = PatternFill('solid', fgColor='9a0eea') 131 fill_red2 = PatternFill('solid', fgColor='FF6347') 132 # fill_red3 = PatternFill('lightUp', fgColor='FF6347') 133 fill_blue3 = PatternFill('solid', fgColor='ADD8E6') 134 135 # # 创建一个新的 Excel 文件 136 workbook = openpyxl.Workbook() 137 one_sheet = workbook.active 138 test_result_v0 = [" 1 ", " 2 ", " 3 ", " 4 ", " 5 ", " 6 ", " 7 ", " 8 ", " 9 ", " 10 ", 139 " 11 ", " 12 ", " 13 ", " 14 ", " 15 ", " 16 ", " 17 ", " 18 ", " 19 ", " 20 ", 140 " 21 ", " 22 ", " 23 ", " 24 ", " 25 ", " 26 ", " 27 ", " 28 ", " 29 ", " 30 ", 141 " 31 ", " 32 ", " 33 ", " 34 ", " 35 ", " 36 ", " 37 ", " 38 ", " 39 ", " 40 ", 142 " 41 ", " 42 ", " 43 ", " 44 ", " 45 ", " 46 ", " 47 ", " 48 ", " 49 ", " 50 ", 143 " 51 ", " 52 ", " 53 ", " 54 ", " 55 ", " 56 ", " 57 ", " 58 ", " 59 ", " 60 ", 144 " 61 ", " 62 ", " 63 ", " 64 ", " 65 ", " 66 ", " 67 ", " 68 ", " 69 ", " 70 ", 145 " 71 ", " 72 ", " 73 ", " 74 ", " 75 ", " 76 ", " 77 ", " 78 ", " 79 ", " 80 ", 146 " 81 ", " 82 ", " 83 ", " 84 ", " 85 ", " 86 ", " 87 ", " 88 ", " 89 ", " 90 ", 147 " 91 ", " 92 ", " 93 ", " 94 ", " 95 ", " 96 ", " 97 ", " 98 ", " 99 ", " 100", 148 " 101", " 102", " 103", " 104", " 105", " 106", " 107", " 108", " 109", " 110", 149 " 111", " 112", " 113", " 114", " 115", " 116", " 117", " 118", " 119", " 120", 150 " 121", " 122", " 123", " 124", " 125", " 126", " 127", " 128", " 129", " 130", 151 " 131", " 132", " 133", " 134", " 135", " 136", " 137", " 138", " 139", " 140", 152 " 141", " 142", " 143", " 144", " 145", " 146", " 147", " 148", " 149", " 150", ] 153 one_sheet.append(test_result_v0) # 追加一行数据 154 155 for map_x0 in range(def_origin_start_rows, max_row_x3): # max_row_x3 156 map_xx = map_x0 + 1 157 for map_y0 in range(def_origin_start_cols, max_cols_y3): # max_cols_y3 158 map_yy = map_y0 + 1 159 # print("\n遍历源坐标1 : ", map_xx, map_yy) 160 source_table1 = worksheet_h1.cell(map_xx, (map_yy - 1)).value 161 # print("\n遍历目标坐标 : ", map_xx - def_origin_start_rows + def_origin_start_rows_t1, map_yy - def_origin_start_cols + 162 # def_origin_start_cols_t1 -1) 163 source_table2 = worksheet_h2.cell(map_xx - def_origin_start_rows + def_origin_start_rows_t1, 164 map_yy - def_origin_start_cols + def_origin_start_cols_t1 - 1).value 165 # print(" 源文件值1 : ", type(source_table1), source_table1) 166 # print(" 目标文件值2 : ", type(source_table2), source_table2) 167 int_type_t1 = 1 168 169 if source_table2 == ".": 170 source_table3 = source_table2 171 elif type(source_table2) == type(int_type_t1): 172 source_table3 = str(source_table2) 173 else: 174 source_table3 = source_table2 175 print(" 目标转换3 : ", type(source_table3), source_table3) 176 # 新增 177 if source_table3 == '3' or source_table3 == '4' == 1: 178 source_table3 = '.' 179 180 if source_table1 == source_table3: 181 # print("--- pass") 182 one_sheet.cell(map_xx, map_y0).value = source_table1 183 184 else: 185 # print("----------- fail") 186 # one_sheet.cell(map_xx, map_y0).value = source_table1 187 # D1 = one_sheet.cell(map_xx, map_y0) 188 # D1.fill = fill_blue3 # 蓝色 189 if source_table3 == '1': 190 one_sheet.cell(map_xx, map_y0).value = source_table1 191 else: 192 one_sheet.cell(map_xx, map_y0).value = "." 193 D1 = one_sheet.cell(map_xx, map_y0) 194 D1.fill = fill_blue3 # 蓝色 195 196 # 优化 197 if source_table3 == '5': 198 one_sheet.cell(map_xx, map_y0).value = "M" 199 D1 = one_sheet.cell(map_xx, map_y0) 200 D1.fill = fill2 201 202 203 204 205 workbook.save(source_target) 206 207 datetime_object_e = datetime.datetime.now() 208 print("\n datetime_object_e :", datetime_object_e) 209 210 print("总时间：", datetime_object_e - datetime_object_s) 211 212 213 ''' 214 //==========================================================================================================// 215 # # -*- encoding: utf-8 -*- 216 # # ------------------------------------------------------------------------------- 217 # # Purpose: txt转换成Excel(xlsx格式) 218 # # Created: 2023-11-30 219 # # ------------------------------------------------------------------------------- 220 221 //==========================================================================================================// 222 ''' 223 224 def txt_to_xlsx(filename, outfile, sheet_name): 225 obj_f_read = codecs.open(filename, 'r') 226 wb = openpyxl.Workbook() 227 ws = wb.active 228 ws = wb.create_sheet() 229 ws.title = sheet_name 230 row_x1 = 0 231 for line_list in obj_f_read: 232 233 row_x1 = row_x1 + 1 234 if row_x1 > 11: 235 for index_a1, index_a1_value in enumerate(line_list): 236 # print('文件中第 -->%d <--个字符件: ' % index_a1, index_a1_value) 237 index_a1_y1 = index_a1 + 1 238 # TODO 是否需要转换统一字符 239 # if index_a1_value != ".": 240 # if index_a1_value=="M": 241 # print(type(index_a1_value)) 242 # index_a1_value = "11" 243 # elif index_a1_value=="X": 244 # print(type(index_a1_value)) 245 # index_a1_value = "22" 246 # elif index_a1_value=="\n": 247 # print(type(index_a1_value)) 248 # index_a1_value = "" 249 # else: 250 # print(float(index_a1_value)) 251 # else: 252 # print(index_a1_value) 253 ws.cell(row=row_x1, column=index_a1_y1, value=index_a1_value) 254 else: 255 ws.cell(row=row_x1, column=1, value=line_list) 256 wb.save(outfile) 257 258 259 260 ''' 261 //==========================================================================================================// 262 // txt_to_xlsx3 （2 -> 3） 263 //==========================================================================================================// 264 ''' 265 266 267 def txt_to_xlsx3(filename, outfile, sheet_name): 268 obj_f_read = codecs.open(filename, 'r') 269 workbook = openpyxl.Workbook() 270 one_sheet = workbook.active 271 one_sheet = workbook.create_sheet() 272 one_sheet.title = sheet_name 273 row_x1 = 0 274 for line_list in obj_f_read: 275 row_x1 = row_x1 + 1 276 if row_x1 > 11: 277 test_result_v0 = [] 278 for index_a1, index_a1_value in enumerate(line_list): 279 # print('文件中第 -->%d <--个字符件: ' % index_a1, index_a1_value) 280 index_a1_y1 = index_a1 + 1 281 # one_sheet.cell(row=row_x1, column=index_a1_y1, value=index_a1_value) 282 test_result_v0.append(row_x1) 283 test_result_v0.append(index_a1_y1) 284 test_result_v0.append(index_a1_value) 285 one_sheet.append(test_result_v0) # 追加一行数据 286 else: 287 one_sheet.cell(row=row_x1, column=1, value=line_list) 288 outfile_result1 = "./scenario_2/3_txt_to_excel_modify/excel_modify_display_coordinates.xlsx" 289 workbook.save(outfile_result1) 290 291 292 ''' 293 //==========================================================================================================// 294 // txt_to_xlsx3_2 （2 -> 0） 295 //==========================================================================================================// 296 ''' 297 298 299 def txt_to_xlsx3_2(filename, outfile, sheet_name): 300 obj_f_read = codecs.open(filename, 'r') 301 # ------------- 302 workbook2 = openpyxl.Workbook() 303 one_sheet2 = workbook2.active 304 # one_sheet2 = workbook.create_sheet() 305 # one_sheet2.title = sheet_name 306 row_x2 = 0 307 for line_list2 in obj_f_read: 308 row_x2 = row_x2 + 1 309 if row_x2 > 11: 310 test_result_v0 = [] 311 for index_a2, index_a2_value in enumerate(line_list2): 312 # print('文件中第 -->%d <--个字符件: ' % index_a1, index_a1_value) 313 index_a2_y1 = index_a2 + 1 314 one_sheet2.cell(row=row_x2, column=index_a2_y1, value=index_a2_value) 315 else: 316 one_sheet2.cell(row=row_x2, column=1, value=line_list2) 317 outfile_result2 = "./scenario_2/3_txt_to_excel_modify/excel_modify.xlsx" 318 workbook2.save(outfile_result2) 319 320 321 ''' 322 //==========================================================================================================// 323 // get_xlsx_coordinate （ 2.1） 324 //==========================================================================================================// 325 ''' 326 327 328 def get_xlsx_coordinate(x4_m, y4_m, s_temp): 329 # print("#------- get_xlsx_coordinate ------------#") 330 get_x2_m = x4_m 331 get_y2_m = y4_m 332 target2_workbook = load_workbook(filename=s_temp) 333 new2_worksheet = target2_workbook.active 334 target_rows = new2_worksheet.max_row 335 target_cols = new2_worksheet.max_column 336 # print('修改文件坐标范围: 行x = %d 列y = %d '%( target_rows, target_cols)) 337 # 从左到右遍历 338 for sigma_x in range(0, target_rows): # 103 339 sigma_xx = sigma_x + 1 340 for sigma_y in range(0, target_cols): # 330 341 sigma_yy = sigma_y + 1 342 if (get_x2_m == sigma_xx) and (get_y2_m == sigma_yy) == 1: 343 # print(sigma_xx, sigma_yy, new2_worksheet.cell(sigma_xx, sigma_yy).value) 344 get_value_coor = new2_worksheet.cell(sigma_xx, sigma_yy).value 345 346 # print(type(get_value_coor)) 347 return get_value_coor 348 349 350 ''' 351 //==========================================================================================================// 352 // modify_xlsx_value （ 2.2） 353 //==========================================================================================================// 354 ''' 355 356 357 def modify_xlsx_value(x4_m, y4_m, v4_m, s_temp): 358 # print("#------- get_xlsx_coordinate ------------#") 359 get_x3_m = x4_m 360 get_y3_m = y4_m 361 modify_value = v4_m 362 target_file = s_temp 363 364 target3_workbook = load_workbook(filename=s_temp) 365 new3_worksheet = target3_workbook.active 366 target_rows3 = new3_worksheet.max_row 367 target_cols3 = new3_worksheet.max_column 368 print('修改文件坐标: 行x = %d 列y = %d ' % (get_x3_m, get_y3_m)) 369 # 从左到右遍历 370 for sigma_x in range(0, target_rows3): # 103 371 sigma_xx = sigma_x + 1 372 for sigma_y in range(0, target_cols3): # 330 373 sigma_yy = sigma_y + 1 374 if (get_x3_m == sigma_xx) and (get_y3_m == sigma_yy) == 1: 375 print("old >>>", sigma_xx, sigma_yy, new3_worksheet.cell(sigma_xx, sigma_yy).value) 376 # new3_worksheet.cell(sigma_xx, sigma_yy).value = modify_value 377 new3_worksheet.cell(row=sigma_xx, column=sigma_yy, value=modify_value) 378 print("new >>>", sigma_xx, sigma_yy, new3_worksheet.cell(sigma_xx, sigma_yy).value) 379 target3_workbook.save(target_file) 380 381 382 ''' 383 //==========================================================================================================// 384 // get_xlsx_coordinate （ 2.3） 385 //==========================================================================================================// 386 ''' 387 388 389 def get_xlsx_list(s_temp): 390 # print("#------- get_xlsx_list ------------#") 391 target2_workbook = load_workbook(filename=s_temp) 392 new2_worksheet = target2_workbook.active 393 target_rows = new2_worksheet.max_row 394 target_cols = new2_worksheet.max_column 395 # print('修改文件坐标范围: 行x = %d 列y = %d '%( target_rows, target_cols)) 396 # 从左到右遍历 397 def_read_start_x1 = 1 398 def_read_start_y1 = 1 399 line_all = [] 400 for sigma_x in range(def_read_start_x1, target_rows): 401 sigma_xx = sigma_x + 1 402 line_each_x1 = [] 403 for sigma_y in range(def_read_start_y1, target_cols): 404 sigma_yy = sigma_y + 1 405 line_each_x1.append(new2_worksheet.cell(sigma_xx, sigma_yy).value) 406 # print(sigma_xx, sigma_yy, new2_worksheet.cell(sigma_xx, sigma_yy).value) 407 # print("每行数值: x, y,value --->",line_each_x1) 408 line_all.append(line_each_x1) 409 # print("所有数值:--->",line_all) 410 # print(type(line_all)) 411 return line_all 412 413 414 ''' 415 //==========================================================================================================// 416 // txt_to_xlsx_c1 （C1） 417 //==========================================================================================================// 418 ''' 419 420 421 def txt_to_xlsx_c1(filename, outfile, sheet_name): 422 obj_f_read = codecs.open(filename, 'r') 423 wb = openpyxl.Workbook() 424 ws = wb.active 425 # ws = wb.create_sheet() 426 # ws.title = sheet_name 427 row_x1 = 0 428 for line_list in obj_f_read: 429 430 row_x1 = row_x1 + 1 431 if row_x1 > 11: 432 for index_a1, index_a1_value in enumerate(line_list): 433 # print('文件中第 -->%d <--个字符件: ' % index_a1, index_a1_value) 434 index_a1_y1 = index_a1 + 1 435 # TODO 是否需要转换统一字符 436 # if index_a1_value != ".": 437 # if index_a1_value=="M": 438 # print(type(index_a1_value)) 439 # index_a1_value = "11" 440 # elif index_a1_value=="X": 441 # print(type(index_a1_value)) 442 # index_a1_value = "22" 443 # elif index_a1_value=="\n": 444 # print(type(index_a1_value)) 445 # index_a1_value = "" 446 # else: 447 # print(float(index_a1_value)) 448 # else: 449 # print(index_a1_value) 450 ws.cell(row=row_x1, column=index_a1_y1, value=index_a1_value) 451 else: 452 ws.cell(row=row_x1, column=1, value=line_list) 453 wb.save(outfile) 454 455 456 ''' 457 //==========================================================================================================// 458 // compare_xlsx_h1 （h1） 459 //==========================================================================================================// 460 ''' 461 462 463 def compare_xlsx_h1(s1_file, o1_file,name1,name2): 464 pass 465 folder_name = s1_file 466 target_compare_h1 = o1_file 467 folder_list1 = os.listdir(folder_name) 468 print('\n excel文件根目录', folder_list1) 469 list_f1 = [] 470 for e_index2 in range(len(folder_list1)): 471 folder_name21 = folder_name + folder_list1[e_index2] 472 list_f1.append(folder_name21) 473 # print("显示每个excel文件路径: ",folder_name21) 474 print("显示文件夹下所有excel文件路径名称: ", list_f1) 475 476 # 创建一个新的 Excel 文件 477 workbook = openpyxl.Workbook() 478 one_sheet = workbook.active 479 # 固定格式抬头 480 # 动态显示文件 #TODO 需要修改 481 value1 = name1 482 value2 = name2 483 test_result_v0 = ["project_name", "X", "Y", value1, value2, "result"] 484 one_sheet.append(test_result_v0) # 追加一行数据 485 486 # TODO 实现自动读取文件 487 # 读取源文件 488 compare_workbook = load_workbook(filename=list_f1[0]) 489 compare_w1 = compare_workbook.active 490 w1_x_rows = compare_w1.max_row 491 w1_y_cols = compare_w1.max_column 492 print('\n target坐标：', w1_x_rows, w1_y_cols) 493 # 第2个 494 compare2_workbook = load_workbook(filename=list_f1[1]) 495 compare_w2 = compare2_workbook.active 496 497 def_start_compare = 5 498 def_start_line = 4 499 def_reduce_line = 11 500 w1_xx_t1 = def_start_compare 501 for w1_x in range(0, w1_x_rows): 502 w1_xx = w1_x + 1 503 if w1_xx > def_reduce_line: 504 for w1_y in range(0, w1_y_cols): 505 w1_yy = w1_y + 1 506 w1_yy_b = w1_yy + 1 507 result_c1 = compare_w1.cell(w1_xx, w1_yy).value 508 result_c2 = compare_w2.cell(w1_xx, w1_yy).value 509 # print(result_c1) 510 w1_x_write1 = (w1_x - def_reduce_line) * w1_y_cols + w1_yy_b 511 def_start_line_b1 = def_start_line - 1 512 def_start_line_b2 = def_start_line - 2 513 def_start_line_p1 = def_start_line + 1 514 def_start_line_p2 = def_start_line + 2 515 516 one_sheet.cell(row=w1_x_write1, column=def_start_line_b2, value=w1_yy) # Y 轴 w1_xx 517 one_sheet.cell(row=w1_x_write1, column=def_start_line_b1, value=w1_xx) # X 轴 w1_yy 518 519 one_sheet.cell(row=w1_x_write1, column=def_start_line, value=result_c1) 520 one_sheet.cell(row=w1_x_write1, column=def_start_line_p1, value=result_c2) 521 if result_c1 == result_c2: 522 one_sheet.cell(row=w1_x_write1, column=def_start_line_p2, value="P") 523 else: 524 one_sheet.cell(row=w1_x_write1, column=def_start_line_p2, value="F") 525 526 target_compare_h2 = target_compare_h1 + "compare_result.xlsx" 527 workbook.save(target_compare_h2) 528 529 530 ''' 531 //--------------------------------------------------------------------------------// 532 // map_excel_handle5 533 //--------------------------------------------------------------------------------// 534 ''' 535 536 537 def map_excel_handle5(source_file_path, source_temp_path, target_file_path): 538 print('\n\t 打印源目标路径：', source_file_path) 539 print('\n\t 打印源目标路径：', source_temp_path) 540 print('\n\t 打印源目标路径：', target_file_path) 541 542 source_map = source_file_path 543 source_temp = source_temp_path 544 source_target = target_file_path 545 546 # 源文件 547 source_map1_object = xlrd.open_workbook(source_map) 548 print('\n\t 读取源目标map对象:', source_map1_object) 549 550 target_workbook = openpyxl.Workbook() 551 552 print("\n请输入边沿去除宽度：\n") 553 mask_count_str = input() 554 print(f"边沿去除宽度为: {mask_count_str} !") 555 mask_count = int(mask_count_str) 556 for sheet in range(len(source_map1_object.sheets())): 557 source_table1 = source_map1_object.sheet_by_index(sheet) 558 source_rows = source_table1.nrows 559 source_cols = source_table1.ncols 560 print('\n\t 源文件:行数%d 列数%d' % (source_rows, source_cols)) 561 562 new_worksheet = target_workbook.active 563 fill0 = PatternFill('solid', fgColor='aaff32') 564 fill1 = PatternFill('solid', fgColor='FFFF00') 565 fill2 = PatternFill('solid', fgColor='ceb301') 566 fill3 = PatternFill('solid', fgColor='ff796c') 567 fill4 = PatternFill('solid', fgColor='f97306') 568 fill_w1 = PatternFill('solid', fgColor='9a0eea') 569 rty = '.' # empty die 570 571 # mask_count = 5 572 partition_area_row = 23 573 # 第一部分 574 for map_x1 in range(0, source_rows): # source_rows 575 map_x1_rt = map_x1 + 1 576 critical_value = 0 # 寻找每行临界值 --# left 577 critical_value_right = 0 # 寻找每行临界值 --# right 578 first_departure_right_flag = 1 # 右边_中间首次触发的标记 579 first_departure_right_up_flag = 1 580 first_departure_right_down_flag = 1 581 right_up_flag = 1 # 左边_上,首次触发的标记 582 right_down_flag = 1 # 左边边_上,首次触发的标记 583 584 old_count1 = rty 585 old_count2 = rty 586 587 M_W1 = [] 588 M_W2 = [] 589 M_W3 = [] 590 591 # 从左到右扫描 592 for map_y1 in range(0, source_cols): 593 map_y1_rt = map_y1 + 1 594 # print(map_x1,map_y1,source_table1.cell_value(rowx=(map_x1-1),colx=(map_y1-1))) 595 new_worksheet.cell(map_x1_rt, map_y1_rt).value = source_table1.cell_value(rowx=map_x1, colx=map_y1) 596 597 # print('\n\t坐标信息:',map_x1_rt,map_y1_rt,map_x1,map_y1) 598 new_count2 = new_worksheet.cell(map_x1_rt, map_y1_rt).value 599 if old_count1 == new_count2: 600 old_count1 = new_count2 601 critical_value = critical_value + 1 602 D1 = new_worksheet.cell(map_x1_rt, map_y1_rt) 603 D1.fill = fill0 # 左边背景色 604 M_W1.append(map_y1_rt) 605 else: 606 old_count1 = 123456789 # 异步停止 607 if old_count2 == new_count2: 608 old_count2 = new_count2 609 critical_value_right = critical_value_right + 1 610 D1 = new_worksheet.cell(map_x1_rt, map_y1_rt) 611 D1.fill = fill0 # 右边背景色 612 M_W3.append(map_y1_rt) 613 if map_x1_rt <= partition_area_row: 614 pass # 上半部分 615 elif map_x1_rt >= (partition_area_row * 3): 616 pass # 下半部分 617 else: 618 pass # 中间部分 619 # 基于临界点push 覆盖 620 if first_departure_right_flag == 1: 621 first_departure_right_flag = 2 # 异步赋值 停止 622 # print('\t 右边触发位置', map_x1_rt, map_y1_rt) 623 for push_count in range(mask_count): 624 pass # 右边 mask 625 D6 = new_worksheet.cell(map_x1_rt, (map_y1_rt - push_count - 1)) 626 D6.fill = fill4 627 else: 628 pass 629 else: 630 M_W2.append(map_y1_rt) 631 if map_x1_rt <= partition_area_row: 632 pass # 上半部分 633 elif map_x1_rt >= (partition_area_row * 3): 634 pass # 下半部分 635 else: 636 pass # 中间部分 637 if (map_y1_rt > critical_value) and (map_y1_rt <= (critical_value + mask_count)) == 1: 638 pass # 左边 mask 639 D6 = new_worksheet.cell(map_x1_rt, map_y1_rt) 640 D6.fill = fill4 641 642 # ----------------------------------------------------------------------------# 643 # 逐行扫描 644 # print('第 %d行 开始临界值: '%(map_x1_rt ), critical_value ,critical_value_right) 645 # print('M1 前背景: ', M_W1) 646 # print('M2 预期区: ', M_W2) 647 # print('M3 后背景: ', M_W3) 648 649 # # 基于每行结果填充颜色 650 # # 第二部分 651 # # ----------------------------------------------------------------------------# 652 # old_count3 = rty 653 # old_count4 = rty 654 # critical_up = 0 655 # critical_down = 0 656 657 for map_y11 in range(0, source_cols): 658 map_y11_xx = map_y11 + 1 659 660 old_count3 = rty 661 old_count4 = rty 662 M_Z1 = [] 663 M_Z2 = [] 664 M_Z3 = [] 665 # M_Z4 = [] 666 critical_up = 0 667 critical_down = 0 668 trigger_left_up_flag = 1 669 trigger_left_down_flag = 1 670 671 for map_x11 in range(0, source_rows): # source_rows 672 map_x11_zz = map_x11 + 1 673 new_count4 = new_worksheet.cell(map_x11_zz, map_y11_xx).value 674 # print(map_x11_zz,map_y11_xx,new_count4) 675 if old_count3 == new_count4: 676 old_count3 = new_count4 677 critical_up = critical_up + 1 678 M_Z1.append(map_x11_zz) 679 else: 680 old_count3 = 987654321 # 异步停止 681 682 if trigger_left_up_flag == 1: 683 trigger_left_up_flag = 2 684 for left_up_count in range(mask_count): 685 D1 = new_worksheet.cell(critical_up + left_up_count + 1, map_y11_xx) 686 D1.fill = fill4 # 左边-上-预期色 687 688 if old_count4 == new_count4: 689 old_count4 = new_count4 690 critical_down = critical_down + 1 691 M_Z3.append(map_x11_zz) 692 693 if trigger_left_down_flag == 1: 694 trigger_left_down_flag = 2 695 for left_down_count in range(mask_count): 696 D1 = new_worksheet.cell(map_x11_zz - left_down_count - 1, map_y11_xx) 697 D1.fill = fill4 # 左边-下-背景色 698 else: 699 M_Z2.append(map_x11_zz) 700 # 逐列扫描 701 # print('第 %d列 开始临界值: '%(map_y11_xx ), critical_up ,critical_down) 702 # print('M_Z1 前背景: ', M_Z1) 703 # print('M_Z2 预期区: ', M_Z2) 704 # print('M_Z3 后背景: ', M_Z3) 705 target_workbook.save(source_temp) 706 707 708 # ====================================================================================================================== 709 ''' 710 #--------------------------------------------------------------# 711 # sop_s1_a1 712 #--------------------------------------------------------------# 713 ''' 714 715 716 def sop_s1_a1(): 717 print("#------ 进行txt转excel操作 -------#") 718 source_file_path = "./scenario_1/1_raw_data_txt/" 719 target_file_path = "./scenario_1/2_txt_to_excel_source/" 720 fileList1 = os.listdir(source_file_path) 721 # print('\n txt文件根目录', fileList3) 722 for index_k1, index_k1_value in enumerate(fileList1): 723 print('第 >_%d_< 个文件: ' % index_k1, index_k1_value) 724 print(">>> 请选择文件:") 725 # file_select_k1 = input() 726 file_select_k1 = 0 727 input_file_txt = os.path.join(source_file_path, fileList1[file_select_k1]) 728 outfileExcel = os.path.join(target_file_path, fileList1[file_select_k1]).replace('.txt', '.xlsx') 729 sheet_name = fileList1[file_select_k1].replace(".txt", '') 730 print("obj_file:", sheet_name) 731 # print(input_file_txt) 732 # print(outfileExcel) 733 txt_to_excel_s1_d1(input_file_txt, outfileExcel) 734 735 736 ''' 737 #--------------------------------------------------------------# 738 # sop_s1_a2 739 #--------------------------------------------------------------# 740 ''' 741 742 743 def sop_s1_a2(): 744 print("源文件excel与目标excel对比") 745 s1_file_path = "./scenario_1/2_txt_to_excel_source/general.xlsx" 746 s1_temp_path = "./scenario_1/3_target_file/目标模版_test1.xlsx" 747 t1_file_path = "./scenario_1/4_compare_result/target_result_v1.xlsx" 748 compare_handle_s1_d2(s1_file_path, s1_temp_path, t1_file_path) 749 750 751 ''' 752 #--------------------------------------------------------------# 753 # sop_s2_a1 754 #--------------------------------------------------------------# 755 ''' 756 757 758 def sop_s2_a1(): 759 inputdir_path = r'./scenario_2/1_raw_data' 760 outputdir_path = r'./scenario_2/2_txt_to_excel' 761 fileList = os.listdir(inputdir_path) 762 print('\n txt文件根目录', fileList) 763 for name in fileList: 764 input_file_txt = os.path.join(inputdir_path, name) 765 outfileExcel = os.path.join(outputdir_path, name).replace('.txt', '.xlsx') 766 sheet_name = name.replace(".txt", '') 767 print(sheet_name) 768 # TXT直接转换excel 769 txt_to_xlsx(input_file_txt, outfileExcel, sheet_name) 770 # txt转换为excel显示坐标 771 txt_to_xlsx3(input_file_txt, outfileExcel, sheet_name) 772 txt_to_xlsx3_2(input_file_txt, outfileExcel, sheet_name) 773 774 775 ''' 776 #--------------------------------------------------------------# 777 # sop_s2_a2 778 #--------------------------------------------------------------# 779 ''' 780 781 782 def sop_s2_a2(): 783 print("\n#------- 请输入需要修改的坐标和修改值 ------------#") 784 True_continue = 1 785 while True_continue: 786 print("请输入坐标: x ") 787 input_x4_m = input() 788 print("请输入坐标: y ") 789 input_y4_m = input() 790 # 添加函数 791 source_temp1 = "./scenario_2/3_txt_to_excel_modify/excel_modify.xlsx" 792 x4_m = int(input_x4_m) 793 y4_m = int(input_y4_m) 794 get_value = get_xlsx_coordinate(x4_m, y4_m, source_temp1) 795 print("源值为: %s " % get_value) 796 797 print("\n请输入修改值: ") 798 input_v4_m = input() 799 print("修改值确认:", input_x4_m, input_y4_m, input_v4_m) 800 print("确认输入:y 取消输入: n") 801 input_execute = input() 802 if input_execute == "y": 803 v4_m = input_v4_m 804 # modify_xlsx_value(x4_m, y4_m, v4_m, source_temp1) 805 modify_xlsx_value(x4_m, y4_m, v4_m, source_temp1) 806 print("#----- Modified successfully -----#") 807 else: 808 print("#----- Cancel modification -----#") 809 print(">>>是否继续修改...") 810 print(">>>请输入 y or n") 811 input_continue = input() 812 if input_continue == "n": 813 True_continue = 0 814 815 816 ''' 817 #--------------------------------------------------------------# 818 # sop_s2_a3 819 #--------------------------------------------------------------# 820 ''' 821 822 823 def sop_s2_a3(): 824 print("\n#------- 请确认批量需要修改的坐标和修改值 ------------#") 825 source_temp0 = "./scenario_2/4_modify_excel_info/info_m.xlsx" 826 list_all_m1 = get_xlsx_list(source_temp0) 827 # print("显示获取的批量修改坐标",list_all_m1) 828 for index_f1, index_f1_value in enumerate(list_all_m1): 829 print('第 >_%d_< 个变更项: ' % index_f1, index_f1_value) 830 for index_f2, index_f2_value in enumerate(index_f1_value): 831 # print('第 >_%d_< 个变更数值: ' % index_f2, type(index_f2_value),index_f2_value) 832 type_data1 = "str" 833 type_data2 = 123 834 if type(index_f2_value) == type(type_data1): 835 stf_f2_value = str(index_f2_value) 836 elif type(index_f2_value) == type(type_data2): 837 stf_f2_value = str(index_f2_value) 838 # print("转换数据类型后:",type(stf_f2_value)) 839 if index_f2 == 0: 840 input_x4_m = stf_f2_value 841 elif index_f2 == 1: 842 input_y4_m = stf_f2_value 843 elif index_f2 == 2: 844 input_v4_m = stf_f2_value 845 846 source_temp1 = "./scenario_2/3_txt_to_excel_modify/excel_modify.xlsx" 847 x4_m = int(input_x4_m) 848 y4_m = int(input_y4_m) 849 get_value = get_xlsx_coordinate(x4_m, y4_m, source_temp1) 850 print("源值为: %s " % get_value) 851 v4_m = input_v4_m 852 # modify_xlsx_value(x4_m, y4_m, v4_m, source_temp1) 853 modify_xlsx_value(x4_m, y4_m, v4_m, source_temp1) 854 print("#----- Modified successfully -----#") 855 856 857 ''' 858 #--------------------------------------------------------------# 859 # sop_s3_a1 860 #--------------------------------------------------------------# 861 ''' 862 863 864 def sop_s3_a1(): 865 source_temp3 = "./scenario_3/1_raw_data/" 866 outputdir_path3 = "./scenario_3/3_data_result/" 867 source_temp33 = "./scenario_3/3_data_result/" 868 outputdir_path4 = "./scenario_3/5_compare_result/" 869 fileList3 = os.listdir(source_temp3) 870 print('\n txt文件根目录', fileList3) 871 for name in fileList3: 872 input_file_txt = os.path.join(source_temp3, name) 873 outfileExcel = os.path.join(outputdir_path3, name).replace('.txt', '.xlsx') 874 sheet_name = name.replace(".txt", '') 875 print(sheet_name) 876 # TXT直接转换excel 877 txt_to_xlsx_c1(input_file_txt, outfileExcel, sheet_name) 878 # excel比较处理 879 compare_xlsx_h1(source_temp33, outputdir_path4,fileList3[0],fileList3[1]) 880 881 882 ''' 883 #--------------------------------------------------------------# 884 # sop_s4_a1 885 #--------------------------------------------------------------# 886 ''' 887 888 889 def sop_s4_a1(): 890 source_file = "./scenario_4/1_raw_data_excel/map_22_1.xlsx" 891 source_temp = "./scenario_4/2_map_result/map_22_1_temp.xlsx" 892 target_file = "./scenario_4/2_map_result/map_22_1_result.xlsx" 893 map_excel_handle5(source_file, source_temp, target_file) 894 895 896 # ====================================================================================================================== # 897 ''' 898 //==========================================================================================================// 899 // scenario_1_subset 900 //==========================================================================================================// 901 ''' 902 903 904 def scenario_1_subset(): 905 sop_s1_a1() 906 sop_s1_a2() 907 908 # 1. 3/4 变成 。 909 # 2. 5 变成 M 910 # 3. 绿色的1范围，与源文件保持一致，1/X 911 # 4. 统计绿色的1范围的良率， 源文件1/1+x 912 # 5. 结果excel转txt 913 914 915 ''' 916 //==========================================================================================================// 917 // scenario_2_subset 918 //==========================================================================================================// 919 ''' 920 921 922 def scenario_2_subset(): 923 sop_s2_a1() 924 sop_s2_a3() 925 926 # print(">>> 是否需要进行txt转excel操作 请输入: y or n") 927 # txt_to_excel_a1 = input() 928 # if txt_to_excel_a1 == "y": 929 # sop_s2_a1() 930 # else: 931 # print("此操作忽略") 932 # print(">>>请输入: 1 - 自定义修改 2 - 批量修改 ") 933 # m_batch_one = input() 934 # if m_batch_one == "1": 935 # pass 936 # elif m_batch_one == "2": 937 # pass 938 # else: 939 # print("输入不正确") 940 # print(">>>是否返回上一级: b-返回上一级 c-继续循环") 941 # back_s1 = input() 942 943 944 ''' 945 //==========================================================================================================// 946 // scenario_3_subset 947 //==========================================================================================================// 948 ''' 949 950 951 def scenario_3_subset(): 952 pass 953 954 955 ''' 956 //==========================================================================================================// 957 // scenario_4_subset 958 //==========================================================================================================// 959 ''' 960 961 962 def scenario_4_subset(): 963 pass 964 965 966 # ====================================================================================================================== # 967 968 ''' 969 # ====================================================================================================================== 970 # 测试专用 971 # ====================================================================================================================== 972 ''' 973 974 if __name__ == "__main__": 975 print('测试开始') 976 # sop_s1_a1() 977 # sop_s1_a2() # 需要修改 978 # sop_s2_a1() 979 # sop_s2_a3() 980 # sop_s3_a1() 981 # sop_s4_a1() 982 # sop_s1_a2() 983 run_big_cycle1 = 1 984 while run_big_cycle1: 985 print("请选择以下场景: [提示 0:表示退出]") 986 print("scenario 1: 源文件与目标文件比较，高亮输出差异点") 987 print("scenario 2: 源文件生成map，根据坐标自定义修改值") 988 print("scenario 3: 不同源文件的对应坐标比对，输入比对列表") 989 print("scenario 4: 输入源文件和边沿去除宽度生成新的excel") 990 print(">>> 请输入功能:") 991 function_order = input() 992 if function_order == "0": 993 print("退出运行") 994 run_big_cycle1 = 0 995 elif function_order == "1": 996 print("scenario 1") 997 run_s1 = 1 998 while run_s1: 999 print("scenario_1场景: [提示 0:表示退出]") 1000 print("scenario_1 1: 源文件生成excel文件") 1001 print("scenario_1 2: 生成excel文件与目标文件对比生成excel结果") 1002 print("scenario_1 3: auto 输入源文件自动生成excel结果") 1003 print(">>> 请输入功能:") 1004 function_order_s1 = input() 1005 if function_order_s1 == "0": 1006 print("退出运行") 1007 run_s1 = 0 1008 elif function_order_s1 == "1": 1009 sop_s1_a1() 1010 print("完成s1 1功能") 1011 elif function_order_s1 == "2": 1012 sop_s1_a2() 1013 print("完成s1 2功能") 1014 elif function_order_s1 == "3": 1015 scenario_1_subset() 1016 print("完成s1 3功能") 1017 else: 1018 print("输入无效数值！") 1019 1020 print("继续执行: y 返回上一级:b") 1021 mean_m = input() 1022 if mean_m == "b": 1023 function_order_s1 = "0" 1024 run_s1 = 0 1025 # --------------------------------------------- 2 # 1026 elif function_order == "2": 1027 print("scenario 2") 1028 run_s2 = 1 1029 while run_s2: 1030 print("scenario_2场景: [提示 0:表示退出]") 1031 print("scenario_2 1: 源文件生成excel文件") 1032 print("scenario_2 2: 输入坐标自定义修改excel") 1033 print("scenario_2 3: 坐标导入批量修改excel") 1034 print("scenario_2 4: auto 输入源文件和坐标导入批量自动生成excel结果") 1035 print(">>> 请输入功能:") 1036 function_order_s2 = input() 1037 if function_order_s2 == "0": 1038 print("退出运行") 1039 run_s2 = 0 1040 elif function_order_s2 == "1": 1041 sop_s2_a1() 1042 print("完成s2 1功能") 1043 elif function_order_s2 == "2": 1044 sop_s2_a2() 1045 print("完成s2 2功能") 1046 elif function_order_s2 == "3": 1047 sop_s2_a3() 1048 print("完成s2 3功能") 1049 elif function_order_s2 == "4": 1050 scenario_2_subset() 1051 print("完成s2 4功能") 1052 else: 1053 print("输入无效数值！") 1054 1055 print("继续执行: y 返回上一级:b") 1056 mean_m = input() 1057 if mean_m == "b": 1058 function_order_s2 = "0" 1059 run_s2 = 0 1060 # --------------------------------------------- 3 # 1061 elif function_order == "3": 1062 print("scenario 3") 1063 run_s3 = 1 1064 while run_s3: 1065 print("scenario_3场景: [提示 0:表示退出]") 1066 print("scenario_3 1: 二个源文件生成excel结果") 1067 print(">>> 请输入功能:") 1068 function_order_s3 = input() 1069 if function_order_s3 == "0": 1070 print("退出运行") 1071 run_3 = 0 1072 elif function_order_s3 == "1": 1073 sop_s3_a1() 1074 print("完成32 1功能") 1075 else: 1076 print("输入无效数值！") 1077 1078 print("继续执行: y 返回上一级:b") 1079 mean_m = input() 1080 if mean_m == "b": 1081 function_order_s3 = "0" 1082 run_s3 = 0 1083 # --------------------------------------------- 4 # 1084 elif function_order == "4": 1085 print("scenario 4") 1086 run_s4 = 1 1087 while run_s4: 1088 print("scenario_4场景: [提示 0:表示退出]") 1089 print("scenario_4 1: 边沿去除宽度生成excel结果") 1090 print(">>> 请输入功能:") 1091 function_order_s4 = input() 1092 if function_order_s4 == "0": 1093 print("退出运行") 1094 run_s2 = 0 1095 elif function_order_s4 == "1": 1096 sop_s4_a1() 1097 print("完成s4 1功能") 1098 else: 1099 print("输入无效数值！") 1100 1101 print("继续执行: y 返回上一级:b") 1102 mean_m = input() 1103 if mean_m == "b": 1104 function_order_s4 = "0" 1105 run_s4 = 0 1106 else: 1107 print("没有此功能请重新选择") 1108 1109 # ===================================================# 1110 # # scenario_1 1111 1112 # # =============================================================================================================# 1113 # # scenario_2 1114 # TODO 显示坐标原点 1115 1116 # # =============================================================================================================# 1117 # # scenario_3
#----code end ----#
愿：
可以帮助到您，
感谢您的阅读。
