Python自动创建jira问题单（提取XML到excel，分析excel提交jira问题）

问题描述：

将一个含有代码报错信息的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())