245 lines
10 KiB
Plaintext
245 lines
10 KiB
Plaintext
"""Excel 集成单处理程序
|
||
|
||
功能：
|
||
- 读取输入 Excel，处理指定工作表（如 bug/cve）
|
||
- 按源码包全名和关联产品排序并合并相同来源行
|
||
- 自动补齐并排序头部列：审核人、审核意见、备注、技术经理审核意见
|
||
- 设置列宽、表头样式、筛选、冻结首行
|
||
- 条件格式：D、E、F 为空时显示浅黄色，D/F 包含“不”时显示浅红色
|
||
|
||
0707更新：
|
||
- 表头前 6 列文字已调整为指定标题
|
||
- 表头底色改为天蓝色
|
||
- 添加筛选和冻结首行
|
||
- 修正列宽写入逻辑，确保宽度生效
|
||
|
||
Usage:
|
||
python3 run3.py input.xlsx output.xlsx [--mapping_file mapping.txt] [--sheets bug cve]
|
||
"""
|
||
|
||
import pandas as pd
|
||
import os
|
||
from openpyxl import load_workbook
|
||
from openpyxl.styles import Alignment, PatternFill, Font
|
||
from openpyxl.formatting.rule import FormulaRule
|
||
import xlsxwriter
|
||
import re
|
||
import argparse
|
||
|
||
def normalize_product_name(product):
|
||
"""彻底规范化产品名：去除所有空白/不可见字符，统一全角半角"""
|
||
if product is None:
|
||
return ''
|
||
name = str(product)
|
||
name = re.sub(r'[\s\u3000]+', '', name) # 去掉所有空白和全角空格
|
||
name = name.replace('\n', '').replace('\r', '')
|
||
name = name.replace('（', '(').replace('）', ')') # 全角括号转半角
|
||
return name.strip()
|
||
|
||
def load_product_reviewer_mapping(mapping_file):
|
||
mapping = {}
|
||
try:
|
||
with open(mapping_file, 'r', encoding='utf-8') as f:
|
||
for line in f:
|
||
line = line.strip()
|
||
if line and ':' in line:
|
||
product, reviewer = line.split(':', 1)
|
||
norm_product = normalize_product_name(product)
|
||
mapping[norm_product] = reviewer.strip()
|
||
except FileNotFoundError:
|
||
print(f"警告: 未找到映射文件 {mapping_file}")
|
||
return mapping
|
||
|
||
def prepare_sheet(df, sheet_name, reviewer_mapping):
|
||
"""准备工作表数据，并填充审核人"""
|
||
# 按源码包全名和关联产品排序
|
||
df['源码包全名'] = df['源码包全名'].astype(str).str.strip()
|
||
df['关联产品'] = df['关联产品'].astype(str).str.strip()
|
||
df.sort_values(by=['源码包全名', '关联产品'], inplace=True)
|
||
|
||
# 添加新列（如果不存在）
|
||
new_cols = ['审核人', '审核意见（可发布、不可发布）', '备注：审核人+备注信息（不可发布的关键信息可适当加红）', '技术经理审核意见（可发布、不可发布）']
|
||
for col in new_cols:
|
||
if col not in df.columns:
|
||
df[col] = ''
|
||
|
||
# 用规范化产品名填充审核人
|
||
df['关联产品_规范化'] = df['关联产品'].apply(normalize_product_name)
|
||
df['审核人'] = df['关联产品_规范化'].map(reviewer_mapping).fillna('')
|
||
# print(df[df['审核人']=='']['关联产品'])
|
||
|
||
# 根据工作表名称设置不同的列顺序
|
||
if sheet_name == 'bug':
|
||
columns_order = [
|
||
'源码包全名', '关联产品', '审核人', '审核意见（可发布、不可发布）', '备注：审核人+备注信息（不可发布的关键信息可适当加红）',
|
||
'技术经理审核意见（可发布、不可发布）', '涉及架构', '责任人', '任务类型', '禅道编号',
|
||
'紧急程度', '修改描述信息-对外', '复现方法', '修复方法', '测试方法',
|
||
'测试结果', '登记日期', '更新时间', '二进制包名'
|
||
]
|
||
elif sheet_name == 'cve':
|
||
columns_order = [
|
||
'源码包全名', '关联产品', '审核人', '审核意见（可发布、不可发布）', '备注：审核人+备注信息（不可发布的关键信息可适当加红）',
|
||
'技术经理审核意见（可发布、不可发布）', '涉及架构', '责任人', '任务类型', '风险等级',
|
||
'漏洞编号', '登记日期', '更新时间', '验证情况', '修改内容',
|
||
'二进制包名'
|
||
]
|
||
else:
|
||
columns_order = df.columns.tolist()
|
||
|
||
existing_columns = [col for col in columns_order if col in df.columns]
|
||
extra_columns = [col for col in df.columns if col not in existing_columns and col != '关联产品_规范化']
|
||
final_columns = existing_columns + extra_columns
|
||
df = df[final_columns]
|
||
|
||
return df
|
||
|
||
def process_sheet(ws):
|
||
"""视觉合并单元格，不处理审核人"""
|
||
# 设置列宽
|
||
col_widths = {
|
||
'A': 35, 'B': 35, 'C': 15, 'D': 15, 'E': 25,
|
||
'F': 15, 'G': 10, 'H': 10, 'I': 10, 'J': 5,
|
||
'K': 10, 'L': 10, 'M': 10, 'N': 10, 'O': 10,
|
||
'P': 10, 'Q': 10, 'R': 10, 'S': 20
|
||
}
|
||
for col, width in col_widths.items():
|
||
ws.column_dimensions[col].width = width
|
||
|
||
# 文本自动换行 顶部对齐
|
||
for row in ws.iter_rows():
|
||
for cell in row:
|
||
cell.alignment = Alignment(wrap_text=False, vertical='top')
|
||
|
||
# 表头行高和对齐
|
||
ws.row_dimensions[1].height = 28
|
||
|
||
# 合并逻辑
|
||
current_package = None
|
||
start_row = 2
|
||
package_merge_ranges = {}
|
||
|
||
# 合并A列
|
||
for row in range(2, ws.max_row + 1):
|
||
package = ws[f'A{row}'].value
|
||
if package != current_package:
|
||
if current_package is not None:
|
||
end_row = row - 1
|
||
if start_row < end_row:
|
||
ws.merge_cells(f'A{start_row}:A{end_row}')
|
||
package_merge_ranges[current_package] = (start_row, end_row)
|
||
current_package = package
|
||
start_row = row
|
||
if current_package is not None and start_row < ws.max_row:
|
||
ws.merge_cells(f'A{start_row}:A{ws.max_row}')
|
||
package_merge_ranges[current_package] = (start_row, ws.max_row)
|
||
|
||
# 合并B、C、D、E、F列
|
||
for package, (start, end) in package_merge_ranges.items():
|
||
current_product = None
|
||
product_start = start
|
||
for row in range(start, end + 1):
|
||
product = ws[f'B{row}'].value
|
||
if product != current_product:
|
||
if current_product is not None:
|
||
product_end = row - 1
|
||
if product_start < product_end:
|
||
for col in ['B', 'C', 'D', 'E', 'F']:
|
||
ws.merge_cells(f'{col}{product_start}:{col}{product_end}')
|
||
current_product = product
|
||
product_start = row
|
||
if current_product is not None and product_start <= end:
|
||
if product_start < end:
|
||
for col in ['B', 'C', 'D', 'E', 'F']:
|
||
ws.merge_cells(f'{col}{product_start}:{col}{end}')
|
||
|
||
return ws
|
||
|
||
def process_integration_sheet(input_file, output_file, sheet_names, mapping_file):
|
||
reviewer_mapping = load_product_reviewer_mapping(mapping_file)
|
||
xl = pd.ExcelFile(input_file)
|
||
temp_file = "temp_output.xlsx"
|
||
writer = pd.ExcelWriter(temp_file, engine='xlsxwriter')
|
||
|
||
for sheet_name in xl.sheet_names:
|
||
df = xl.parse(sheet_name)
|
||
if sheet_name in sheet_names:
|
||
df = prepare_sheet(df, sheet_name, reviewer_mapping)
|
||
df.to_excel(writer, sheet_name=sheet_name, index=False)
|
||
writer.close()
|
||
|
||
wb = load_workbook(temp_file)
|
||
for sheet_name in wb.sheetnames:
|
||
if sheet_name in sheet_names:
|
||
ws = wb[sheet_name]
|
||
process_sheet(ws)
|
||
apply_header_and_conditional_format(ws)
|
||
wb.save(output_file)
|
||
print(f"处理完成，结果已保存到: {output_file}")
|
||
try:
|
||
os.remove(temp_file)
|
||
except OSError:
|
||
pass
|
||
|
||
|
||
def apply_header_and_conditional_format(ws):
|
||
# 表头样式
|
||
header_fill = PatternFill(start_color='FFB7E1FF', end_color='FFB7E1FF', fill_type='solid')
|
||
header_font = Font(bold=True, color='FF000000')
|
||
header_titles = [
|
||
'源码包全名（红色遗留包、紫色灰名单暂不发布包）',
|
||
'关联产品',
|
||
'审核人',
|
||
'审核意见（可发布、不可发布）',
|
||
'备注：审核人+备注信息（不可发布的关键信息可适当加红）',
|
||
'技术经理审核意见（可发布、不可发布）'
|
||
]
|
||
for col_idx, cell in enumerate(ws[1], start=1):
|
||
if col_idx <= len(header_titles):
|
||
cell.value = header_titles[col_idx - 1]
|
||
cell.fill = header_fill
|
||
cell.font = header_font
|
||
cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
|
||
|
||
# 添加筛选与冻结首行
|
||
ws.auto_filter.ref = ws.dimensions
|
||
ws.freeze_panes = 'A2'
|
||
|
||
if ws.max_row < 2:
|
||
return
|
||
|
||
# 条件格式：D、E、F 为空时，浅黄色背景 + 深黄色文本
|
||
empty_fill = PatternFill(start_color='FFFFF2CC', end_color='FFFFF2CC', fill_type='solid')
|
||
empty_font = Font(color='FF9C6500')
|
||
empty_rule = FormulaRule(formula=['LEN(TRIM($D2))=0'], stopIfTrue=True, fill=empty_fill, font=empty_font)
|
||
ws.conditional_formatting.add(f'D2:D{ws.max_row}', empty_rule)
|
||
empty_rule = FormulaRule(formula=['LEN(TRIM($E2))=0'], stopIfTrue=True, fill=empty_fill, font=empty_font)
|
||
ws.conditional_formatting.add(f'E2:E{ws.max_row}', empty_rule)
|
||
empty_rule = FormulaRule(formula=['LEN(TRIM($F2))=0'], stopIfTrue=True, fill=empty_fill, font=empty_font)
|
||
ws.conditional_formatting.add(f'F2:F{ws.max_row}', empty_rule)
|
||
|
||
# 条件格式：D、F 中包含“不”时，浅红色背景 + 深红色文本
|
||
negative_fill = PatternFill(start_color='FFFFCCCC', end_color='FFFFCCCC', fill_type='solid')
|
||
negative_font = Font(color='FF9C0006')
|
||
negative_rule = FormulaRule(formula=['ISNUMBER(SEARCH("不", $D2))'], stopIfTrue=True, fill=negative_fill, font=negative_font)
|
||
ws.conditional_formatting.add(f'D2:D{ws.max_row}', negative_rule)
|
||
negative_rule = FormulaRule(formula=['ISNUMBER(SEARCH("不", $F2))'], stopIfTrue=True, fill=negative_fill, font=negative_font)
|
||
ws.conditional_formatting.add(f'F2:F{ws.max_row}', negative_rule)
|
||
|
||
if __name__ == "__main__":
|
||
|
||
|
||
parser = argparse.ArgumentParser(description="Excel集成单处理程序")
|
||
parser.add_argument('input_file', help='输入文件路径')
|
||
parser.add_argument('output_file', help='输出文件路径')
|
||
parser.add_argument('--mapping_file', default='product_responsible_mapping.txt', help='产品审核人映射文件路径')
|
||
parser.add_argument('--sheets', nargs='+', default=['bug', 'cve'], help='要处理的工作表名称(可多个)')
|
||
|
||
args = parser.parse_args()
|
||
|
||
process_integration_sheet(
|
||
input_file=args.input_file,
|
||
output_file=args.output_file,
|
||
sheet_names=args.sheets,
|
||
mapping_file=args.mapping_file
|
||
)
|