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 ----#
标签：map,scenario,晶圆,value,source,print,input,工具 From： https://www.cnblogs.com/caochucheng/p/17904108.html