class SCLStatsProcessor:
"""SCL统计处理器 - 优化版:固定列位置"""
def __init__(self, color_detector):
self.color_detector = color_detector
# 固定列位置
self.COLUMN_MAPPING = {
"差分種別": 12, # L列
"变更内容": 13, # M列
"判断列": 14, # N列
"判断理由": 15, # O列
"变更背景": 16, # P列
"备注": 17 # Q列
}
# 新增规则19-59的关键词
self.keywords = [
"波形図の尾の延長、非機能変更と判定されました。",
"選択肢的补充说明,不属于功能变更。",
"空白行が追加され、非機能変更と判定された",
"空行が削除され、非機能変更と判定された",
"无效更改,判定为无功能变更。",
"書式変更で機能変更ではないと判断されました。",
"仅修改了背景色,不影响软件设计,判定为无功能变更。"
]
# 统计结果列映射
self.target_columns = {
"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": 62, # BL列
"valid_yes_reason_fill": 65, # BO列
"valid_no_reason_no_fill": 71, # BS列
"valid_no_reason_fill": 74, # BV列
"background_no_fill": 78, # BZ列
"background_fill": 85, # CG列
"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列
}
# 定义特定颜色
self.COLOR_CRIMSON = "E54C5E" # 猩红
self.COLOR_STEEL_BLUE = "B5C6EA" # 钢蓝
self.COLOR_CHOCOLATE = "F9CBAA" # 巧克力黄
self.COLOR_TOLERANCE = 5 # 颜色容差
self.HEADER_ROW = 3 # 表头固定在第三行
self.DATA_START_ROW = 4 # 数据从第四行开始
def get_fill_color(self, cell):
"""获取单元格填充颜色的字符串表示(6位大写,无Alpha通道)"""
try:
# 检查是否无填充
if self.color_detector.is_no_fill(cell):
return None
# 尝试获取前景色
if hasattr(cell.fill, 'fgColor') and cell.fill.fgColor:
if hasattr(cell.fill.fgColor, 'rgb') and cell.fill.fgColor.rgb:
color_str = str(cell.fill.fgColor.rgb).upper()
# 处理8位带Alpha通道的颜色
if len(color_str) == 8 and color_str.startswith("FF"):
color_str = color_str[2:] # 去掉Alpha通道
return color_str
return None
except Exception as e:
logger.error(f"获取填充颜色失败: {str(e)}")
return None
def is_specific_color(self, cell, target_color, tolerance=None):
"""
检查单元格是否为特定颜色,允许一定的容差
"""
if tolerance is None:
tolerance = self.COLOR_TOLERANCE
cell_color = self.get_fill_color(cell)
if not cell_color or not target_color:
return False
# 转换为RGB分量
def hex_to_rgb(hex_color):
try:
return (
int(hex_color[0:2], 16),
int(hex_color[2:4], 16),
int(hex_color[4:6], 16)
)
except:
return (0, 0, 0)
try:
cell_rgb = hex_to_rgb(cell_color)
target_rgb = hex_to_rgb(target_color.upper())
# 计算颜色差异
diff = sum(abs(c - t) for c, t in zip(cell_rgb, target_rgb))
return diff <= tolerance
except:
return False
def contains_keyword(self, text):
"""检查文本是否包含任意关键词(不区分大小写)"""
if not text:
return False
text = str(text).strip().lower()
for keyword in self.keywords:
if keyword.lower() in text:
return True
return False
def process_scl_file(self, file_path):
"""
处理单个SCL文件并返回所有统计结果
返回: (results, missing_data)
- results: 规则统计结果
- missing_data: 缺失数据列表 [{'row': 行号, 'col': 列号, 'col_name': 列名, 'message': 错误信息}, ...]
"""
results = {}
missing_data = [] # 存储缺失数据信息
CRIMSON = self.COLOR_CRIMSON
STEEL_BLUE = self.COLOR_STEEL_BLUE
CHOCOLATE = self.COLOR_CHOCOLATE
try:
logger.info(f"开始处理SCL文件: {file_path}")
# 加载SCL文件
scl_wb = openpyxl.load_workbook(file_path)
scl_sheet = scl_wb.active
logger.info(f"工作表加载成功: {scl_sheet.title}, 总行数: {scl_sheet.max_row}")
# 检查是否有足够的数据行
if scl_sheet.max_row < self.DATA_START_ROW:
logger.info("文件没有数据行,跳过处理")
return results, missing_data
# 初始化统计结果
stats = {
"diff_no_fill": {}, # 规则1: 変更内容无颜色填充
"diff_fill": {}, # 规则2: 変更内容有颜色填充
"diff_add_no_fill": {}, # 规则3: 差分种别="追加"且无颜色填充
"diff_add_fill": {}, # 规则4: 差分种别="追加"且有颜色填充
"diff_change_no_fill": {}, # 规则5: 差分种别="変更"且无颜色填充
"diff_change_fill": {}, # 规则6: 差分种别="変更"且有颜色填充
"diff_delete_no_fill": {}, # 规则7: 差分种别="削除"且无颜色填充
"diff_delete_fill": {}, # 规则8: 差分种别="削除"且有颜色填充
"valid_yes_no_fill": {}, # 规则9: 判断="有意"且无颜色填充
"valid_yes_fill": {}, # 规则10: 判断="有意"且有颜色填充
"valid_no_no_fill": {}, # 规则11: 判断="無効"且无颜色填充
"valid_no_fill": {}, # 规则12: 判断="無効"且有颜色填充
"valid_yes_reason_no_fill": {}, # 规则13: 判断="有意"且理由无颜色填充
"valid_yes_reason_fill": {}, # 规则14: 判断="有意"且理由有颜色填充
"valid_no_reason_no_fill": {}, # 规则15: 判断="無効"且理由无颜色填充
"valid_no_reason_fill": {}, # 规则16: 判断="無効"且理由有颜色填充
"background_no_fill": {}, # 规则17: 背景无颜色填充
"background_fill": {}, # 规则18: 背景有颜色填充
# 规则19-26
"rule19": 0, # 规则19: 包含关键词且无颜色填充
"rule20": 0, # 规则20: 不包含关键词且无颜色填充
"rule21": 0, # 规则21: 包含关键词且猩红填充
"rule22": 0, # 规则22: 不包含关键词且猩红填充
"rule23": 0, # 规则23: 包含关键词且钢蓝填充
"rule24": 0, # 规则24: 不包含关键词且钢蓝填充
"rule25": 0, # 规则25: 包含关键词且巧克力黄填充
"rule26": 0, # 规则26: 不包含关键词且巧克力黄填充
# 规则27-34
"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列="差分無し"的计数
# 规则36-43
"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列不包含关键词
# 规则44-51
"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列不包含关键词
# 规则52-59
"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列不包含关键词
}
# 遍历所有数据行
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 and note_cell.value else None
# 获取M列值
m_value = str(diff_content_cell.value) if diff_content_cell.value else ""
valid_value = str(valid_cell.value).strip() if valid_cell.value else ""
# 检查是否包含关键词
contains_keyword = self.contains_keyword(m_value)
# 获取单元格颜色状态 - 使用统一的方法
l_no_fill = self.color_detector.is_no_fill(diff_cell) # L列无填充
m_no_fill = self.color_detector.is_no_fill(diff_content_cell) # M列无填充
n_no_fill = self.color_detector.is_no_fill(valid_cell) # N列无填充
o_no_fill = self.color_detector.is_no_fill(reason_cell) # O列无填充
p_no_fill = self.color_detector.is_no_fill(background_cell) # P列无填充
p_crimson = self.is_specific_color(background_cell, CRIMSON)
p_steel_blue = self.is_specific_color(background_cell, STEEL_BLUE)
p_chocolate = self.is_specific_color(background_cell, CHOCOLATE)
# 规则1-18处理(使用统一的方法)
# 规则1-18处理(使用字典统计备注值)
# 规则1: 変更内容无颜色填充
if self.color_detector.is_no_fill(diff_content_cell) and note_value:
stats["diff_no_fill"][note_value] = stats["diff_no_fill"].get(note_value, 0) + 1
# 规则2: 変更内容有颜色填充
if not self.color_detector.is_no_fill(diff_content_cell) and note_value:
stats["diff_fill"][note_value] = stats["diff_fill"].get(note_value, 0) + 1
# 规则3: 差分种别="追加"且无颜色填充
if (diff_value == "追加" and l_no_fill and note_value):
stats["diff_add_no_fill"][note_value] = stats["diff_add_no_fill"].get(note_value, 0) + 1
# 规则4: 差分种别="追加"且有颜色填充
if (diff_value == "追加" and not l_no_fill and note_value):
stats["diff_add_fill"][note_value] = stats["diff_add_fill"].get(note_value, 0) + 1
# 规则5: 差分种别="変更"且无颜色填充
if (diff_value == "変更" and l_no_fill and note_value):
stats["diff_change_no_fill"][note_value] = stats["diff_change_no_fill"].get(note_value, 0) + 1
# 规则6: 差分种别="変更"且有颜色填充
if (diff_value == "変更" and not l_no_fill and note_value):
stats["diff_change_fill"][note_value] = stats["diff_change_fill"].get(note_value, 0) + 1
# 规则7: 差分种别="削除"且无颜色填充
if (diff_value == "削除" and l_no_fill and note_value):
stats["diff_delete_no_fill"][note_value] = stats["diff_delete_no_fill"].get(note_value, 0) + 1
# 规则8: 差分种别="削除"且有颜色填充
if (diff_value == "削除" and not l_no_fill and note_value):
stats["diff_delete_fill"][note_value] = stats["diff_delete_fill"].get(note_value, 0) + 1
# 规则9: 判断="有意"且无颜色填充
if (valid_value == "有意" and n_no_fill and note_value):
stats["valid_yes_no_fill"][note_value] = stats["valid_yes_no_fill"].get(note_value, 0) + 1
# 规则10: 判断="有意"且有颜色填充
if (valid_value == "有意" and not n_no_fill and note_value):
stats["valid_yes_fill"][note_value] = stats["valid_yes_fill"].get(note_value, 0) + 1
# 规则11: 判断="無効"且无颜色填充
if (valid_value == "無効" and n_no_fill and note_value):
stats["valid_no_no_fill"][note_value] = stats["valid_no_no_fill"].get(note_value, 0) + 1
# 规则12: 判断="無効"且有颜色填充
if (valid_value == "無効" and not n_no_fill and note_value):
stats["valid_no_fill"][note_value] = stats["valid_no_fill"].get(note_value, 0) + 1
# 规则13: 判断="有意"且理由无颜色填充
if (valid_value == "有意" and o_no_fill and note_value):
stats["valid_yes_reason_no_fill"][note_value] = stats["valid_yes_reason_no_fill"].get(note_value, 0) + 1
# 规则14: 判断="有意"且理由有颜色填充
if (valid_value == "有意" and not o_no_fill and note_value):
stats["valid_yes_reason_fill"][note_value] = stats["valid_yes_reason_fill"].get(note_value, 0) + 1
# 规则15: 判断="無効"且理由无颜色填充
if (valid_value == "無効" and o_no_fill and note_value):
stats["valid_no_reason_no_fill"][note_value] = stats["valid_no_reason_no_fill"].get(note_value, 0) + 1
# 规则16: 判断="無効"且理由有颜色填充
if (valid_value == "無効" and not o_no_fill and note_value):
stats["valid_no_reason_fill"][note_value] = stats["valid_no_reason_fill"].get(note_value, 0) + 1
# 规则17: 背景无颜色填充
if p_no_fill and note_value:
stats["background_no_fill"][note_value] = stats["background_no_fill"].get(note_value, 0) + 1
# 规则18: 背景有颜色填充
if not p_no_fill and note_value:
stats["background_fill"][note_value] = stats["background_fill"].get(note_value, 0) + 1
# 规则19-59处理(使用统一的方法)
# ...(规则19-59的处理代码保持不变)...
# 规则19-26处理
if contains_keyword and m_no_fill:
stats["rule19"] += 1
if not contains_keyword and m_no_fill:
stats["rule20"] += 1
if contains_keyword and self.is_specific_color(diff_content_cell, CRIMSON):
stats["rule21"] += 1
if not contains_keyword and self.is_specific_color(diff_content_cell, CRIMSON):
stats["rule22"] += 1
if contains_keyword and self.is_specific_color(diff_content_cell, STEEL_BLUE):
stats["rule23"] += 1
if not contains_keyword and self.is_specific_color(diff_content_cell, STEEL_BLUE):
stats["rule24"] += 1
if contains_keyword and self.is_specific_color(diff_content_cell, CHOCOLATE):
stats["rule25"] += 1
if not contains_keyword and self.is_specific_color(diff_content_cell, CHOCOLATE):
stats["rule26"] += 1
# 规则27-34处理
if diff_value == "追加":
if l_no_fill and contains_keyword:
stats["rule27"] += 1
if l_no_fill and not contains_keyword:
stats["rule28"] += 1
if not l_no_fill and contains_keyword:
stats["rule29"] += 1
if not l_no_fill and not contains_keyword:
stats["rule30"] += 1
if diff_value == "変更":
if l_no_fill and contains_keyword:
stats["rule31"] += 1
if l_no_fill and not contains_keyword:
stats["rule32"] += 1
if not l_no_fill and contains_keyword:
stats["rule33"] += 1
if not l_no_fill and not contains_keyword:
stats["rule34"] += 1
# 规则35: L列="差分無し"的计数
if diff_value == "差分無し":
stats["rule35"] += 1
# 规则36-43处理
if valid_value == "有意":
if n_no_fill and contains_keyword:
stats["rule36"] += 1
if n_no_fill and not contains_keyword:
stats["rule37"] += 1
if not n_no_fill and contains_keyword:
stats["rule38"] += 1
if not n_no_fill and not contains_keyword:
stats["rule39"] += 1
if valid_value == "無効":
if n_no_fill and contains_keyword:
stats["rule40"] += 1
if n_no_fill and not contains_keyword:
stats["rule41"] += 1
if not n_no_fill and contains_keyword:
stats["rule42"] += 1
if not n_no_fill and not contains_keyword:
stats["rule43"] += 1
# 规则44-51处理
if valid_value == "有意":
if o_no_fill and contains_keyword:
stats["rule44"] += 1
if o_no_fill and not contains_keyword:
stats["rule45"] += 1
if not o_no_fill and contains_keyword:
stats["rule46"] += 1
if not o_no_fill and not contains_keyword:
stats["rule47"] += 1
if valid_value == "無効":
if o_no_fill and contains_keyword:
stats["rule48"] += 1
if o_no_fill and not contains_keyword:
stats["rule49"] += 1
if not o_no_fill and contains_keyword:
stats["rule50"] += 1
if not o_no_fill and not contains_keyword:
stats["rule51"] += 1
# 规则52-59处理
if p_no_fill and contains_keyword:
stats["rule52"] += 1
if p_no_fill and not contains_keyword:
stats["rule53"] += 1
if p_crimson and contains_keyword:
stats["rule54"] += 1
if p_crimson and not contains_keyword:
stats["rule55"] += 1
if p_steel_blue and contains_keyword:
stats["rule56"] += 1
if p_steel_blue and not contains_keyword:
stats["rule57"] += 1
if p_chocolate and contains_keyword:
stats["rule58"] += 1
if p_chocolate and not contains_keyword:
stats["rule59"] += 1
# 处理统计结果
for rule, data in stats.items():
# 对于规则1-18,输出备注值及其出现次数
if rule in ["diff_no_fill", "diff_fill", "diff_add_no_fill", "diff_add_fill",
"diff_change_no_fill", "diff_change_fill", "diff_delete_no_fill",
"diff_delete_fill", "valid_yes_no_fill", "valid_yes_fill",
"valid_no_no_fill", "valid_no_fill", "valid_yes_reason_no_fill",
"valid_yes_reason_fill", "valid_no_reason_no_fill",
"valid_no_reason_fill", "background_no_fill", "background_fill"]:
# 检查是否有数据
if not data: # 字典为空
results[rule] = "/"
logger.info(f"{rule}: 无数据,输出 '/'")
continue
# 尝试按数值排序
try:
# 按数值排序(如果备注值是数字)
sorted_items = sorted(data.items(), key=lambda x: int(x[0]) if x[0].isdigit() else x[0])
except Exception:
# 如果无法转换为数字,则按字符串排序
sorted_items = sorted(data.items())
# 格式化为"值,计数"的字符串
result_str = "\n".join([f"{value},{count}" for value, count in sorted_items])
results[rule] = result_str
else:
# 对于规则19-59,直接输出计数值
results[rule] = str(data)
logger.info(f"{rule}: 结果 {results[rule]}")
return results, missing_data
except Exception as e:
error_msg = f"处理SCL文件失败: {str(e)}"
logger.exception(f"处理SCL文件失败: {file_path} - {str(e)}")
# 返回错误信息
return {rule: f"错误: {str(e)}" for rule in self.target_columns}, [{
'row': 0,
'col': 0,
'col_name': '错误',
'message': error_msg
}]
备注列读取到14、15或者14,15这样的数据都不应该直接输出14,15,而是应该输出14,1(换行)15,1
最新发布