16. 3Sum Closest (M)

本文深入探讨3SumClosest算法,旨在寻找数组中三个数的组合,其和最接近给定目标值。通过排序与双指针技巧,有效解决重复值问题,实现高效查找。

3Sum Closest (M)

Given an array nums of n integers and an integer target, find three integers in nums such that the sum is closest to target. Return the sum of the three integers. You may assume that each input would have exactly one solution.

Example:

Given array nums = [-1, 2, 1, -4], and target = 1.

The sum that is closest to the target is 2. (-1 + 2 + 1 = 2).

题意

求一三元组,使其和最接近给定的值。

思路

15. 3Sum 类似,只是修改了判定的标准。先排序,后利用two pointers并去除重复值即可。


代码实现

class Solution {
    public int threeSumClosest(int[] nums, int target) {
        int min = Integer.MAX_VALUE;
        int ans = 0;
        Arrays.sort(nums);
        for (int i = 0; i < nums.length - 2; i++) {
            // 去除重复的i
            if (i > 0 && nums[i] == nums[i - 1]) {
                continue;
            }
            int j = i + 1, k = nums.length - 1;
            while (j < k) {
                int sum = nums[i] + nums[j] + nums[k];
                int diff = Math.abs(sum - target);
                if (diff < min) {
                    min = diff;
                    ans = sum;
                }
                // 移动j、k的标准是使sum更加接近target
                if (sum < target) {
                    j++;
                    while (j < k && nums[j] == nums[j - 1]) {
                        j++;
                    }
                } else if (sum > target) {
                    k--;
                    while (j < k && nums[k] == nums[k + 1]) {
                        k--;
                    }
                } else {
                    return sum;		// 特殊情况,sum正好为target
                }
            }
        }
        return ans;
    }
}
简化之后实现不了分析复杂颜色,还是继续分析为什么第一行能正确读取颜色,后续行不行吧,并且只有特殊颜色判断的巧克力黄和钢蓝都会读取成钢蓝 class EnhancedColorDetector: “”“增强版颜色检测器,支持精确颜色匹配和调试”“” def init(self, tolerance=30): “”" 初始化颜色检测器 :param tolerance: 颜色匹配容差 (0-442) “”" self.tolerance = tolerance self.known_colors = { “猩红”: “E54C5E”, “钢蓝”: “B4C6E7”, “巧克力黄”: “F9CBAA”, “无填充”: None } self.logger = logging.getLogger(‘EnhancedCellColorDetector’) # 颜色统计 self.color_stats = defaultdict(int) self.color_matches = defaultdict(int) self.color_mismatches = defaultdict(int) # 颜色缓存 self.color_cache = {} def reset_for_new_file(self): """为处理新文件重置状态""" self.color_stats.clear() self.color_matches.clear() self.color_mismatches.clear() self.color_cache.clear() self.logger.info("颜色检测器已重置,准备处理新文件") def hex_to_rgb(self, hex_color): """将十六进制颜色转换为RGB元组,支持多种格式""" try: if not hex_color: return (0, 0, 0) hex_str = str(hex_color).strip().upper().replace("#", "") # 检查缓存 if hex_str in self.color_cache: return self.color_cache[hex_str] # 处理带Alpha通道的颜色 if len(hex_str) == 8: # 提取Alpha值 alpha = int(hex_str[0:2], 16) / 255.0 # 提取RGB分量 r = int(hex_str[2:4], 16) g = int(hex_str[4:6], 16) b = int(hex_str[6:8], 16) # 混合白色背景 (255,255,255) # 使用更精确的混合算法 r = int(r * alpha + 255 * (1 - alpha) + 0.5) g = int(g * alpha + 255 * (1 - alpha) + 0.5) b = int(b * alpha + 255 * (1 - alpha) + 0.5) result = (r, g, b) self.color_cache[hex_str] = result return result # 处理标准6位HEX if len(hex_str) == 6: result = ( int(hex_str[0:2], 16), int(hex_str[2:4], 16), int(hex_str[4:6], 16) ) self.color_cache[hex_str] = result return result # 处理3位简写HEX if len(hex_str) == 3: result = ( int(hex_str[0]*2, 16), int(hex_str[1]*2, 16), int(hex_str[2]*2, 16) ) self.color_cache[hex_str] = result return result return (0, 0, 0) except Exception as e: self.logger.error(f"颜色转换失败: {hex_color} - {str(e)}") return (0, 0, 0) def rgb_to_hex(self, rgb): """RGB元组转换为HEX字符串""" if not rgb or len(rgb) != 3: return None return f"{rgb[0]:02X}{rgb[1]:02X}{rgb[2]:02X}" def color_distance(self, color1, color2): """ 计算两个颜色之间的感知距离(改进的CIE94公式) 参考: https://en.wikipedia.org/wiki/Color_difference """ if not color1 or not color2: return float(&#39;inf&#39;) # 无穷大表示完全不匹配 # 使用缓存提高性能 cache_key = f"{color1}_{color2}" if cache_key in self.color_cache: return self.color_cache[cache_key] # 转换为RGB r1, g1, b1 = self.hex_to_rgb(color1) r2, g2, b2 = self.hex_to_rgb(color2) # 转换为Lab颜色空间(更符合人眼感知) lab1 = self.rgb_to_lab((r1, g1, b1)) lab2 = self.rgb_to_lab((r2, g2, b2)) # 计算CIE94颜色差异 L1, a1, b1 = lab1 L2, a2, b2 = lab2 delta_L = L1 - L2 delta_a = a1 - a2 delta_b = b1 - b2 c1 = math.sqrt(a1*a1 + b1*b1) c2 = math.sqrt(a2*a2 + b2*b2) delta_c = c1 - c2 delta_h = math.sqrt(max(delta_a*delta_a + delta_b*delta_b - delta_c*delta_c, 0)) sl = 1.0 kc = 1.0 kh = 1.0 sc = 1.0 + 0.045 * c1 sh = 1.0 + 0.015 * c1 distance = math.sqrt( (delta_L/(sl))**2 + (delta_c/(sc*kc))**2 + (delta_h/(sh*kh))**2 ) self.color_cache[cache_key] = distance return distance def rgb_to_lab(self, rgb): """将RGB颜色转换为Lab颜色空间""" # 先将RGB转换为XYZ r, g, b = [x / 255.0 for x in rgb] # 伽马校正 r = self.gamma_correction(r) g = self.gamma_correction(g) b = self.gamma_correction(b) # 转换为XYZ x = r * 0.4124564 + g * 0.3575761 + b * 0.1804375 y = r * 0.2126729 + g * 0.7151522 + b * 0.0721750 z = r * 0.0193339 + g * 0.1191920 + b * 0.9503041 # D65白点参考值 ref_x = 95.047 ref_y = 100.000 ref_z = 108.883 # 标准化 x = x / ref_x y = y / ref_y z = z / ref_z # 转换为Lab x = self.lab_transform(x) y = self.lab_transform(y) z = self.lab_transform(z) L = max(0, 116 * y - 16) a = 500 * (x - y) b = 200 * (y - z) return (L, a, b) def gamma_correction(self, value): """伽马校正""" if value > 0.04045: return math.pow((value + 0.055) / 1.055, 2.4) else: return value / 12.92 def lab_transform(self, t): """Lab转换函数""" if t > 0.008856: return math.pow(t, 1/3) else: return (7.787 * t) + (16/116) def is_no_fill(self, cell): """检查单元格是否无填充(增强版)""" try: # 检查单元格是否有填充定义 if not hasattr(cell, &#39;fill&#39;) or not cell.fill: return True # 检查填充类型 if hasattr(cell.fill, &#39;fill_type&#39;) and cell.fill.fill_type is None: return True # 检查特定填充类型 if cell.fill.fill_type == &#39;none&#39;: return True # 检查前景色是否自动(默认) if (hasattr(cell.fill, &#39;fgColor&#39;) and hasattr(cell.fill.fgColor, &#39;type&#39;) and cell.fill.fgColor.type == &#39;auto&#39;): return True # 检查背景色是否自动(默认) if (hasattr(cell.fill, &#39;bgColor&#39;) and hasattr(cell.fill.bgColor, &#39;type&#39;) and cell.fill.bgColor.type == &#39;auto&#39;): return True # 检查是否有实际颜色值 if hasattr(cell.fill, &#39;fgColor&#39;) and cell.fill.fgColor.rgb: return False if hasattr(cell.fill, &#39;bgColor&#39;) and cell.fill.bgColor.rgb: return False return True except Exception as e: self.logger.error(f"检查无填充失败: {str(e)}") return True def get_cell_color(self, cell): """获取单元格填充颜色的十六进制表示(增强版)""" try: # 检查是否无填充 if self.is_no_fill(cell): return None color_str = None # 尝试获取前景色 if hasattr(cell.fill, &#39;fgColor&#39;) and cell.fill.fgColor: if hasattr(cell.fill.fgColor, &#39;rgb&#39;) and cell.fill.fgColor.rgb: color_str = str(cell.fill.fgColor.rgb).upper() # 如果前景色无效,尝试获取背景色 if not color_str and hasattr(cell.fill, &#39;bgColor&#39;) and cell.fill.bgColor: if hasattr(cell.fill.bgColor, &#39;rgb&#39;) and cell.fill.bgColor.rgb: color_str = str(cell.fill.bgColor.rgb).upper() return color_str except Exception as e: self.logger.error(f"获取填充颜色失败: {str(e)}") return None def normalize_color(self, color_str): """规范化颜色字符串 - 优化版""" if not color_str: return None # 转换为字符串并去除空格和# color_str = str(color_str).strip().upper().replace("#", "") # 1. 处理3位简写HEX if len(color_str) == 3: return color_str[0]*2 + color_str[1]*2 + color_str[2]*2 # 2. 处理6位HEX - 直接返回 if len(color_str) == 6: return color_str # 3. 处理8位带Alpha通道的颜色 if len(color_str) == 8: alpha_hex = color_str[:2] rgb_hex = color_str[2:] try: alpha = int(alpha_hex, 16) / 255.0 except ValueError: # 无法解析透明度,当作不透明处理 return rgb_hex # 透明度高于99%当作不透明处理 if alpha >= 0.99: return rgb_hex # 只在透明度低于99%时进行混合计算 r = int(rgb_hex[0:2], 16) g = int(rgb_hex[2:4], 16) b = int(rgb_hex[4:6], 16) # 混合白色背景 r = int(r * alpha + 255 * (1 - alpha) + 0.5) g = int(g * alpha + 255 * (1 - alpha) + 0.5) b = int(b * alpha + 255 * (1 - alpha) + 0.5) return f"{r:02X}{g:02X}{b:02X}" # 4. 其他格式直接返回原值 return color_str def match_color_name(self, hex_color): """匹配颜色名称,返回最接近的已知颜色""" if not hex_color: return "无填充" closest_name = "其他" min_distance = float(&#39;inf&#39;) for name, target in self.known_colors.items(): if target is None: # 跳过无填充 continue distance = self.color_distance(hex_color, target) if distance < min_distance: min_distance = distance closest_name = name return closest_name def is_specific_color(self, cell, target_color, tolerance=None, log_details=False): """ 检查单元格是否为特定颜色,允许容差 :param cell: 单元格对象 :param target_color: 目标颜色HEX :param tolerance: 容差值 :param log_details: 是否记录匹配详情 :return: 是否匹配 """ if tolerance is None: tolerance = self.tolerance # 获取单元格颜色(原始值) raw_cell_color = self.get_cell_color(cell) # 规范化单元格颜色和目标颜色 norm_cell_color = self.normalize_color(raw_cell_color) if raw_cell_color else None norm_target_color = self.normalize_color(target_color) if target_color else None # 统计颜色出现次数 self.color_stats[raw_cell_color] += 1 if not norm_cell_color: if log_details: self.logger.debug(f"无填充单元格") return False if not norm_target_color: if log_details: self.logger.debug(f"未提供目标颜色") return False # 当容差为0时,直接比较规范化后的颜色值 if tolerance == 0: matched = (norm_cell_color == norm_target_color) else: # 计算颜色距离(使用规范化后的颜色) distance = self.color_distance(norm_cell_color, norm_target_color) matched = distance <= tolerance # 记录匹配详情 - 使用原始颜色值显示 if log_details: actual_rgb = self.hex_to_rgb(norm_cell_color) target_rgb = self.hex_to_rgb(norm_target_color) closest_name = self.match_color_name(norm_cell_color) log_msg = (f"颜色匹配: 原始 #{raw_cell_color} -> 规范 #{norm_cell_color}({actual_rgb}) vs " f"目标 #{norm_target_color}({target_rgb}) - " f"容差: {tolerance}") if tolerance != 0: log_msg += f", 距离: {distance:.2f} {&#39;≤&#39; if matched else &#39;>&#39;} {tolerance}" if matched: self.logger.debug(log_msg) self.color_matches[target_color] += 1 else: self.logger.warning(log_msg + f" | 最接近: {closest_name}") self.color_mismatches[target_color] += 1 return matched def get_color_stats(self): """获取颜色统计信息""" stats = { "total_cells": sum(self.color_stats.values()), "unique_colors": len(self.color_stats), "color_distribution": dict(self.color_stats), "matches": dict(self.color_matches), "mismatches": dict(self.color_mismatches) } return stats def generate_color_report(self): """生成颜色分析报告""" report = ["颜色分析报告:"] report.append(f"总单元格数: {sum(self.color_stats.values())}") report.append(f"唯一颜色数: {len(self.color_stats)}") report.append(f"颜色匹配容差: {self.tolerance}") if self.color_stats: report.append("\n颜色分布:") for color, count in sorted(self.color_stats.items(), key=lambda x: x[1], reverse=True): if color is None: report.append(f" 无填充: {count}") else: rgb = self.hex_to_rgb(color) closest = self.match_color_name(color) report.append(f" #{color} (RGB: {rgb}) - {count}次 - 分类: {closest}") if self.color_matches: report.append("\n成功匹配:") for color, count in self.color_matches.items(): name = next((k for k, v in self.known_colors.items() if v == color), "未知") report.append(f" {name} (#{color}): {count}次") if self.color_mismatches: report.append("\n匹配失败:") for color, count in self.color_mismatches.items(): name = next((k for k, v in self.known_colors.items() if v == color), "未知") report.append(f" {name} (#{color}): {count}次") # 添加颜色匹配率统计 if self.color_matches or self.color_mismatches: total_matches = sum(self.color_matches.values()) total_mismatches = sum(self.color_mismatches.values()) total_attempts = total_matches + total_mismatches match_rate = (total_matches / total_attempts * 100) if total_attempts > 0 else 0 report.append("\n匹配率统计:") report.append(f"总匹配尝试: {total_attempts}") report.append(f"成功匹配: {total_matches} ({match_rate:.2f}%)") report.append(f"匹配失败: {total_mismatches} ({100 - match_rate:.2f}%)") return "\n".join(report) def add_custom_color(self, name, hex_value): """添加自定义颜色""" if not hex_value or len(hex_value) not in (3, 6, 8): self.logger.warning(f"无效的颜色值: {hex_value}") return False # 规范化颜色值 hex_value = self.normalize_color(hex_value) if not hex_value: return False self.known_colors[name] = hex_value self.logger.info(f"已添加自定义颜色: {name} (#{hex_value})") return True def auto_calibrate_tolerance(self): """自动校准颜色匹配容差""" if not self.color_stats: self.logger.warning("没有足够的颜色数据来自动校准") return # 计算所有颜色与目标颜色的平均距离 distances = [] for color in self.color_stats: if color is None: continue min_dist = float(&#39;inf&#39;) for target in self.known_colors.values(): if target is None: continue dist = self.color_distance(color, target) if dist < min_dist: min_dist = dist if min_dist < float(&#39;inf&#39;): distances.append(min_dist) if not distances: return # 计算平均距离和标准差 avg_dist = sum(distances) / len(distances) std_dev = math.sqrt(sum((d - avg_dist)**2 for d in distances) / len(distances)) # 设置新的容差(平均值 + 1.5倍标准差) new_tolerance = min(100, max(10, avg_dist + 1.5 * std_dev)) self.tolerance = new_tolerance self.logger.info(f"自动校准完成: 新容差 = {new_tolerance:.2f}") return new_tolerance class SCLRuleProcessor: “”“SCL规则处理器,包含详细的颜色映射逻辑”“” def init(self, color_detector=None): self.color_detector = color_detector or EnhancedColorDetector(tolerance=30) # 创建专用日志记录器 self.logger = logging.getLogger(‘SCLProcessor.RuleProcessor’) if not self.logger.handlers: # 如果没有处理器,添加控制台处理器 console_handler = logging.StreamHandler() console_handler.setLevel(logging.INFO) formatter = logging.Formatter( ‘%(asctime)s - %(name)s - %(levelname)s - %(message)s’, datefmt=‘%Y-%m-%d %H:%M:%S’ ) console_handler.setFormatter(formatter) self.logger.addHandler(console_handler) self.logger.setLevel(logging.INFO) # 固定列位置 self.COLUMN_MAPPING = { "差分種別": 12, # L列 "变更内容": 13, # M列 "判断列": 14, # N列 "判断理由": 15, # O列 "变更背景": 16, # P列 "备注": 17 # Q列 } # 新增规则的关键词 - 添加更多变体 self.keywords = [ "波形図の尾の延長、非機能変更と判定されました。", "波形図の尾の延長", "非機能変更", "選択肢的补充说明,不属于功能变更。", "补充说明", "不属于功能变更", "空白行が追加され、非機能変更と判定された", "空白行追加", "空行が削除され、非機能変更と判定された", "空行削除", "无效更改,判定为无功能变更。", "无效更改", "書式変更で機能変更ではないと判断されました。", "書式変更", "仅修改了背景色,不影响软件设计,判定为无功能变更。", "仅修改背景色", "背景色修改", "非功能变更", "无功能变更", "書式調整", "非機能修正" ] # 创建关键词正则表达式模式 self.keyword_pattern = self._create_keyword_pattern() # 规则映射到目标列 self.RULE_MAPPING = { # 规则1-18: 备注值统计 "diff_no_fill": 16, # P列 "diff_fill": 23, # W列 "diff_add_no_fill": 27, # AA列 "diff_add_fill": 30, # AD列 "diff_change_no_fill": 34, # AH列 "diff_change_fill": 37, # AK列 "diff_delete_no_fill": 42, # AP列 "diff_delete_fill": 45, # AS列 "valid_yes_no_fill": 50, # AX列 "valid_yes_fill": 53, # BA列 "valid_no_no_fill": 57, # BE列 "valid_no_fill": 60, # BH列 "valid_yes_reason_no_fill": 64, # BL列 "valid_yes_reason_fill": 67, # BO列 "valid_no_reason_no_fill": 71, # BS列 "valid_no_reason_fill": 74, # BV列 "background_no_fill": 78, # BZ列 "background_fill": 85, # CG列, # 规则19-59: 计数统计 "rule19": 14, # N列 "rule20": 15, # O列 "rule21": 17, # Q列 "rule22": 18, # R列 "rule23": 19, # S列 "rule24": 20, # T列 "rule25": 21, # U列 "rule26": 22, # V列 "rule27": 25, # Y列 "rule28": 26, # Z列 "rule29": 28, # AB列 "rule30": 29, # AC列 "rule31": 32, # AF列 "rule32": 33, # AG列 "rule33": 35, # AI列 "rule34": 36, # AJ列 "rule35": 47, # AU列 "rule36": 48, # AV列 "rule37": 49, # AW列 "rule38": 51, # AY列 "rule39": 52, # AZ列 "rule40": 55, # BC列 "rule41": 56, # BD列 "rule42": 58, # BF列 "rule43": 59, # BG列 "rule44": 62, # BJ列 "rule45": 63, # BK列 "rule46": 65, # BM列 "rule47": 66, # BN列 "rule48": 69, # BQ列 "rule49": 70, # BR列 "rule50": 72, # BT列 "rule51": 73, # BU列 "rule52": 76, # BX列 "rule53": 77, # BY列 "rule54": 79, # CA列 "rule55": 80, # CB列 "rule56": 81, # CC列 "rule57": 82, # CD列 "rule58": 83, # CE列 "rule59": 84, # CF列 "rule60": 40, # AF列 "rule61": 41, # AG列 "rule62": 43, # AI列 "rule63": 44, # AJ列 "rule64": 7, # G列 "rule65": 8, # H列 "rule66": 9, # I列 } self.HEADER_ROW = 3 # 表头固定在第三行 self.DATA_START_ROW = 4 # 数据从第四行开始 def get_cell_color_status(self, cell, column_name=None): """ 获取单元格的颜色状态(增强版) 返回: (颜色名称, 颜色HEX值, 是否无填充) column_name: 列名,用于确定是否进行特殊颜色判断 """ try: # 检查是否无填充 if self.color_detector.is_no_fill(cell): return "无填充", None, True # 获取单元格颜色 hex_color = self.color_detector.get_cell_color(cell) if not hex_color: return "未知", None, False # 只有M列和P列需要特殊颜色判断 if column_name in ["变更内容", "变更背景"]: # 检查是否为特定颜色 if self.color_detector.is_specific_color(cell, self.color_detector.known_colors["猩红"]): return "猩红", self.color_detector.known_colors["猩红"], False if self.color_detector.is_specific_color(cell, self.color_detector.known_colors["钢蓝"]): return "钢蓝", self.color_detector.known_colors["钢蓝"], False if self.color_detector.is_specific_color(cell, self.color_detector.known_colors["巧克力黄"]): return "巧克力黄", self.color_detector.known_colors["巧克力黄"], False # 匹配最接近的已知颜色 closest_name = self.color_detector.match_color_name(hex_color) return closest_name, hex_color, False except Exception as e: self.logger.error(f"获取单元格颜色状态失败: {str(e)}") return "错误", None, False def _create_keyword_pattern(self): """创建关键词匹配的正则表达式模式""" # 转义特殊字符并创建模式 escaped_keywords = [re.escape(kw) for kw in self.keywords] # 创建不区分大小写的模式 pattern_str = "|".join(escaped_keywords) return re.compile(pattern_str, re.IGNORECASE) def contains_keyword(self, text): """ 使用正则表达式检查文本是否包含任意关键词(更健壮的匹配) 返回: 匹配结果和匹配到的关键词列表 """ if not text: return False, [] text = str(text).strip() matches = self.keyword_pattern.findall(text) return bool(matches), matches def split_note_values(self, note_value): """拆分备注值,支持多种分隔符""" if not note_value: return [] # 支持的分隔符:中文顿号、全角逗号、半角逗号、分号、空格 separators = [&#39;、&#39;, &#39;,&#39;, &#39;,&#39;, &#39;;&#39;, &#39;;&#39;, &#39; &#39;] # 替换所有分隔符为统一的分隔符 normalized = str(note_value) for sep in separators: normalized = normalized.replace(sep, &#39;|&#39;) # 分割并过滤空值 values = [v.strip() for v in normalized.split(&#39;|&#39;) if v.strip()] # 过滤掉值为“-”的项 values = [v for v in values if v != "-"] return values def format_note_stats(self, data_dict): """格式化备注统计结果,确保每个值一行""" try: # 尝试按数值排序 sorted_items = sorted( data_dict.items(), key=lambda x: int(x[0]) if x[0].isdigit() else x[0] ) except Exception: # 如果无法转换为数字,则按字符串排序 sorted_items = sorted(data_dict.items()) # 格式化为每行一个值 return "\n".join([f"{value},{count}" for value, count in sorted_items]) def create_column_letter_map(self): """创建列号到列字母的映射""" column_letter_map = {} # 生成A-Z列 for i in range(1, 27): column_letter_map[i] = chr(64 + i) # 生成AA-AZ列 for i in range(1, 27): column_letter_map[26 + i] = f"A{chr(64 + i)}" # 生成BA-BZ列 for i in range(1, 27): column_letter_map[52 + i] = f"B{chr(64 + i)}" # 生成CA-CZ列 for i in range(1, 27): column_letter_map[78 + i] = f"C{chr(64 + i)}" # 添加已知的特殊列 column_letter_map.update({ 16: "P", 23: "W", 27: "AA", 30: "AD", 34: "AH", 37: "AK", 42: "AP", 45: "AS", 50: "AX", 53: "BA", 57: "BE", 60: "BH", 62: "BL", 65: "BO", 71: "BS", 74: "BV", 78: "BZ", 85: "CG" }) return column_letter_map def log_cell_value_counts(self, rule_name, cell_stats): """记录规则对应的每个单元格值的计数""" total_count = sum(cell_stats.values()) self.logger.info(f"{rule_name}: 详细单元格统计 (共 {total_count} 个单元格)") # 按计数值降序排序 sorted_items = sorted(cell_stats.items(), key=lambda x: x[1], reverse=True) # 记录每个值的计数 for value, count in sorted_items: # 处理空值 display_value = "()" if value is None or value == "" else value self.logger.info(f" - {display_value}: {count} 次") # 记录唯一值计数 unique_values = len(cell_stats) self.logger.info(f" 共 {unique_values} 个唯一值") def process_file(self, file_path): """ 处理单个SCL文件并返回所有统计结果 返回: (results, color_report, missing_data) - results: 规则统计结果 - color_report: 颜色分析报告 - missing_data: 缺失数据信息 """ # 重置颜色检测器状态 self.color_detector.reset_for_new_file() results = {} missing_data = [] # 存储缺失数据信息 try: self.logger.info(f"开始处理SCL文件: {file_path}") # 加载SCL文件 scl_wb = openpyxl.load_workbook(file_path) scl_sheet = scl_wb.active self.logger.info(f"工作表加载成功: {scl_sheet.title}, 总行数: {scl_sheet.max_row}") # 检查是否有足够的数据行 if scl_sheet.max_row < self.DATA_START_ROW: self.logger.info("文件没有数据行,跳过处理") return results, "", missing_data # 初始化统计结果 stats = { # 规则1-18: 备注值统计 "diff_no_fill": defaultdict(int), # 规则1: 変更内容无颜色填充 "diff_fill": defaultdict(int), # 规则2: 変更内容有颜色填充 "diff_add_no_fill": defaultdict(int), # 规则3: 差分种别="追加"且无颜色填充 "diff_add_fill": defaultdict(int), # 规则4: 差分种别="追加"且有颜色填充 "diff_change_no_fill": defaultdict(int), # 规则5: 差分种别="変更"且无颜色填充 "diff_change_fill": defaultdict(int), # 规则6: 差分种别="変更"且有颜色填充 "diff_delete_no_fill": defaultdict(int), # 规则7: 差分种别="削除"且无颜色填充 "diff_delete_fill": defaultdict(int), # 规则8: 差分种别="削除"且有颜色填充 "valid_yes_no_fill": defaultdict(int), # 规则9: 判断="有意"且无颜色填充 "valid_yes_fill": defaultdict(int), # 规则10: 判断="有意"且有颜色填充 "valid_no_no_fill": defaultdict(int), # 规则11: 判断="無効"且无颜色填充 "valid_no_fill": defaultdict(int), # 规则12: 判断="無効"且有颜色填充 "valid_yes_reason_no_fill": defaultdict(int), # 规则13: 判断="有意"且理由无颜色填充 "valid_yes_reason_fill": defaultdict(int), # 规则14: 判断="有意"且理由有颜色填充 "valid_no_reason_no_fill": defaultdict(int), # 规则15: 判断="無効"且理由无颜色填充 "valid_no_reason_fill": defaultdict(int), # 规则16: 判断="無効"且理由有颜色填充 "background_no_fill": defaultdict(int), # 规则17: 背景无颜色填充 "background_fill": defaultdict(int), # 规则18: 背景有颜色填充, # 规则19-59: 计数统计 "rule19": 0, # 规则19: 包含关键词且无颜色填充 "rule20": 0, # 规则20: 不包含关键词且无颜色填充 "rule21": 0, # 规则21: 包含关键词且猩红填充 "rule22": 0, # 规则22: 不包含关键词且猩红填充 "rule23": 0, # 规则23: 包含关键词且钢蓝填充 "rule24": 0, # 规则24: 不包含关键词且钢蓝填充 "rule25": 0, # 规则25: 包含关键词且巧克力黄填充 "rule26": 0, # 规则26: 不包含关键词且巧克力黄填充 "rule27": 0, # L列="追加"且无颜色填充且M列包含关键词 "rule28": 0, # L列="追加"且无颜色填充且M列不包含关键词 "rule29": 0, # L列="追加"且有颜色填充且M列包含关键词 "rule30": 0, # L列="追加"且有颜色填充且M列不包含关键词 "rule31": 0, # L列="変更"且无颜色填充且M列包含关键词 "rule32": 0, # L列="変更"且无颜色填充且M列不包含关键词 "rule33": 0, # L列="変更"且有颜色填充且M列包含关键词 "rule34": 0, # L列="変更"且有颜色填充且M列不包含关键词 "rule35": 0, # L列="差分無し"的计数 "rule36": 0, # N列="有意"且无颜色填充且M列包含关键词 "rule37": 0, # N列="有意"且无颜色填充且M列不包含关键词 "rule38": 0, # N列="有意"且有颜色填充且M列包含关键词 "rule39": 0, # N列="有意"且有颜色填充且M列不包含关键词 "rule40": 0, # N列="無効"且无颜色填充且M列包含关键词 "rule41": 0, # N列="無効"且无颜色填充且M列不包含关键词 "rule42": 0, # N列="無効"且有颜色填充且M列包含关键词 "rule43": 0, # N列="無効"且有颜色填充且M列不包含关键词 "rule44": 0, # N列="有意"且O列无颜色填充且M列包含关键词 "rule45": 0, # N列="有意"且O列无颜色填充且M列不包含关键词 "rule46": 0, # N列="有意"且O列有颜色填充且M列包含关键词 "rule47": 0, # N列="有意"且O列有颜色填充且M列不包含关键词 "rule48": 0, # N列="無効"且O列无颜色填充且M列包含关键词 "rule49": 0, # N列="無効"且O列无颜色填充且M列不包含关键词 "rule50": 0, # N列="無効"且O列有颜色填充且M列包含关键词 "rule51": 0, # N列="無効"且O列有颜色填充且M列不包含关键词 "rule52": 0, # P列无颜色填充且M列包含关键词 "rule53": 0, # P列无颜色填充且M列不包含关键词 "rule54": 0, # P列颜色为猩红且M列包含关键词 "rule55": 0, # P列颜色为猩红且M列不包含关键词 "rule56": 0, # P列颜色为钢蓝且M列包含关键词 "rule57": 0, # P列颜色为钢蓝且M列不包含关键词 "rule58": 0, # P列颜色为巧克力黄且M列包含关键词 "rule59": 0, # P列颜色为巧克力黄且M列不包含关键词 "rule60": 0, # L列="削除"且无颜色填充且M列包含关键词 "rule61": 0, # L列="削除"且无颜色填充且M列不包含关键词 "rule62": 0, # L列="削除"且有颜色填充且M列包含关键词 "rule63": 0, # L列="削除"且有颜色填充且M列不包含关键词 "rule64": 0, # M列有内容 "rule65": 0, # N列="有意" "rule66": 0, # 0 } all_notes_empty = True # 新增:标记整列备注是否全为空 # 遍历所有数据行 for row_idx in range(self.DATA_START_ROW, scl_sheet.max_row + 1): # 获取所有需要的单元格 diff_content_cell = scl_sheet.cell(row_idx, self.COLUMN_MAPPING["变更内容"]) diff_cell = scl_sheet.cell(row_idx, self.COLUMN_MAPPING["差分種別"]) note_cell = scl_sheet.cell(row_idx, self.COLUMN_MAPPING["备注"]) valid_cell = scl_sheet.cell(row_idx, self.COLUMN_MAPPING["判断列"]) reason_cell = scl_sheet.cell(row_idx, self.COLUMN_MAPPING["判断理由"]) background_cell = scl_sheet.cell(row_idx, self.COLUMN_MAPPING["变更背景"]) # 获取单元格值 diff_value = str(diff_cell.value).strip() if diff_cell.value else "" note_value = str(note_cell.value).strip() if note_cell.value else "" valid_value = str(valid_cell.value).strip() if valid_cell.value else "" # 获取备注值并处理多值情况 note_values = self.split_note_values(note_value) # 获取M列值 m_value = str(diff_content_cell.value) if diff_content_cell.value else "" # 检查是否包含关键词(使用增强版匹配) contains_keyword, matched_keywords = self.contains_keyword(m_value) # 记录匹配结果(调试用) if contains_keyword: self.logger.debug(f"行 {row_idx} 匹配到关键词: {&#39;, &#39;.join(matched_keywords)}") else: self.logger.debug(f"行 {row_idx} 未匹配到关键词") # 获取单元格颜色状态 l_color_name, l_color_hex, l_no_fill = self.get_cell_color_status(diff_cell) m_color_name, m_color_hex, m_no_fill = self.get_cell_color_status(diff_content_cell) n_color_name, n_color_hex, n_no_fill = self.get_cell_color_status(valid_cell) o_color_name, o_color_hex, o_no_fill = self.get_cell_color_status(reason_cell) p_color_name, p_color_hex, p_no_fill = self.get_cell_color_status(background_cell) # 检查备注列是否有数据 if note_value: all_notes_empty = False # 规则1-18处理 # 规则1: 変更内容无颜色填充 if m_no_fill and note_values: for nv in note_values: stats["diff_no_fill"][nv] += 1 # 记录到规则统计 # 规则2: 変更内容有颜色填充 if not m_no_fill and note_values: for nv in note_values: stats["diff_fill"][nv] += 1 # 规则3: 差分种别="追加"且无颜色填充 if diff_value == "追加" and l_no_fill and note_values: for nv in note_values: stats["diff_add_no_fill"][nv] += 1 # 规则4: 差分种别="追加"且有颜色填充 if diff_value == "追加" and not l_no_fill and note_values: for nv in note_values: stats["diff_add_fill"][nv] += 1
08-21
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值