问题描述:
将一个含有代码报错信息的XML文件(包含报错位置路径,行列,报错信息,错误码等),先转化为excel文件,经人为审核(检查该错误是否需要提交,备注,经办人等)后,提取excel表格的报错信息内容提交jira问题。
解决流程:
1.XML到excel
代码如下:
def convert_xml_to_excel(xml_path):
"""
转换含有错误信息的xml文件到excel
:param xml_path: xml文件路径
:return: 转换后的dataframe
"""
# 加载并解析 XML 文件
tree = ET.parse(xml_path)
root = tree.getroot()
# 初始化用于填充数据的列表
errors = []
# 按照信息名称提取信息(错误id,严重性等),列出表格,根据需要自己修改
for error in root.findall('.//error'):
file_path = error.get('file0', '')
file_name = os.path.split(file_path)[-1]
error_data = {
'file_path': file_path,
'file_name': file_name,
'id': error.get('id', ''),
'severity': error.get('severity', ''),
'msg': error.get('msg', ''),
'verbose': error.get('verbose', ''),
'cwe': error.get('cwe', ''),
'location': [],
'line': '',
'column': '',
'assignee': '', # 填充经办人
'issue_key': '', # 用于填充jira问题创建后返回的问题键
'submit': '', # 该错误是否需要提交
'note': '',
}
# 一下是收集错误位置信息,因为错误位置可能是跳转的,且从下到上排放,根据需要可自行修改
# 为当前错误收集所有位置信息
for location in error.findall('.//location'):
loc_info = f"{location.get('file', '')}:{location.get('line', '')}:{location.get('column', '')}"
if location.get('info'):
loc_info += f" (Info: {location.get('info')})"
error_data['location'].append(loc_info)
# 设置错误的行和列为第一个位置的行和列
if 'line' not in error_data or not error_data['line']:
error_data['line'] = location.get('line', '')
error_data['column'] = location.get('column', '')
# 将位置信息反转并加入到错误数据中
error_data['location'] = '\n'.join(reversed(error_data['location']))
# 将该错误的数据加入到列表中
errors.append(error_data)
# 将数据转换为 DataFrame
df = pd.DataFrame(errors)
return df
def jira_xml2excel(xml_path, excel_path, screen_width_pixels=1920):
"""
转换单个xmltoexcel
:param xml_path: xml文件路径
:param excel_path: excel文件路径
:param screen_width_pixels: 展示屏幕分辨率宽度
:return: 0
"""
# 创建一个 Excel 工作簿
wb = Workbook()
ws = wb.active
df = convert_xml_to_excel(xml_path)
for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=True), 1):
for c_idx, value in enumerate(row, 1):
cell = ws.cell(row=r_idx, column=c_idx, value=value)
if r_idx == 1: # 格式化标题行
cell.font = Font(bold=True, size=20)
else:
cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
# 一下是修改列宽,不需要可以不要
# 屏幕宽度转换为Excel列宽(假设每个字符平均宽度为10像素)
total_chars = screen_width_pixels / 10
# 定义宽列
wide_columns = ['file_name', 'msg', 'verbose', 'location']
narrow_width_total = 0
# 首先设置所有列的宽度适应其列名
for col in ws.columns:
header_cell = ws.cell(row=1, column=col[0].column)
col_width = len(header_cell.value) * 2 # 给列名的宽度留一点额外空间,可自行修改
ws.column_dimensions[col[0].column_letter].width = col_width
if header_cell.value not in wide_columns:
narrow_width_total += col_width
# 计算剩余宽度并分配给宽列
remaining_width = total_chars - narrow_width_total
wide_column_width = remaining_width / len(wide_columns)
for col in ws.columns:
if ws.cell(row=1, column=col[0].column).value in wide_columns:
ws.column_dimensions[col[0].column_letter].width = wide_column_width
excel_path = excel_path
# 保存工作簿
wb.save(excel_path)
print(f"Excel file has been saved to {excel_path}")
def batch_convert_xml_to_excel(input_folder, output_folder, screen_width_pixels=1920):
"""
避免转换文件夹内的xml文件到excel文件
:param input_folder: 输入的xml文件夹
:param output_folder: 输出的excel文件夹
:param screen_width_pixels: 展示屏幕分辨率宽度
:return: 0
"""
# 确保输出文件夹存在
if not os.path.exists(output_folder):
os.makedirs(output_folder)
# 遍历输入文件夹中的所有文件
for file_name in os.listdir(input_folder):
if file_name.endswith('.xml'):
# 构建完整的文件路径
input_path = os.path.join(input_folder, file_name)
# 构建输出的 Excel 文件路径
output_file_name = file_name.replace('.xml', '.xlsx')
output_path = os.path.join(output_folder, output_file_name)
# 调用前面定义的函数来转换 XML 到 DataFrame
jira_xml2excel(input_path, output_path, screen_width_pixels=screen_width_pixels)
if __name__ == '__main__':
# 参数
xml_path = r''
xml_file_name = os.path.split(xml_path)[-1]
excel_path = f''
screen_width_pixels = 1920
# 转换单文件
jira_xml2excel(xml_path, excel_path, screen_width_pixels=screen_width_pixels)
# # 批量转换文件夹所有文件
# input_folder = r''
# output_folder = r''
# batch_convert_xml_to_excel(input_folder, output_folder)
2. excel提交到jira
代码如下:
import pandas as pd
from jira import JIRA
def auto_commit_bug(jira_server, jira_user, jira_password, jira_issue_dict):
# 连接到 JIRA
jira = JIRA(server=jira_server, basic_auth=(jira_user, jira_password))
# 读取 Excel 文件
df = pd.read_excel(JiraIssueDict.excel_path)
# 按 file_name 分组
grouped = df.groupby('file_path')
for file_path, group in grouped:
if 1 in group['submit'].values: # 如果该分组中有需要提交的错误
# 这里是一些填写格式,根据需要自己修改
description = f"【文件】\n {file_path} \n [问题] \n"
# 一个问题单的错误个数(因为是按照一个文件提交一个问题单,里面可能有多个错误)
error_num = 0
error_id = ''
file_name = ''
for _, row in group.iterrows():
if row['submit'] == 1:
error_num = error_num + 1
file_name = row['file_name']
if row['id'] not in error_id:
error_id += ' ' + row['id']
description += f"问题{error_num}: \n id: {row['id']} \n cwe: https://cwe.mitre.org/data/definitions/{row['cwe']}.html \n " \
f"severity: {row['severity']} \n verbose: {row['verbose']} \n location(line: {row['line']}, column: {row['column']}): \n" \
f"{row['location']} \n"
if description: # 如果有需要提交的错误
issue_dict = {
'project': {'key': JiraIssueDict.project_key}, # 替换为你的项目键
'summary': f"[CodeCheck][{error_num}][{file_name}] {error_id}",
'assignee': {'name': JiraIssueDict.assignee},
'description': description,
'issuetype': {'name': JiraIssueDict.issue_type},
'priority': {'name': JiraIssueDict.priority},
'labels': [JiraIssueDict.label],
'customfield_10351': JiraIssueDict.branch,
}
issue = jira.create_issue(fields=issue_dict)
issue_key = issue.key
print(f"创建问题{issue_key}")
# 更新 DataFrame,将问题键填入对应的行
for idx in group[group['submit'] == 1].index:
df.at[idx, 'issue_key'] = issue_key
# 循环结束后,将更新后的 DataFrame 保存回 Excel 文件
df.to_excel(JiraIssueDict.excel_path, index=False)
return 0
if __name__ == '__main__':
# 存放问题单的信息,包含项目键,经办人,问题类型,优先级,标签,分支等等,根据需要自行添加和修改
class JiraIssueDict():
excel_path = r''
project_key = ''
assignee = ''
issue_type = ''
priority = ''
label = ''
branch = ''
# JIRA 服务器信息
jira_server = ''
jira_user = ''
jira_password = ''
auto_commit_bug(jira_server=jira_server, jira_user=jira_user, jira_password=jira_password, jira_issue_dict=JiraIssueDict())