How to Automate Excel Vulnerability Reporting with Python: A Step‑by‑Step Guide

In this article, the author addresses a Python automation task raised in a community group, showing how to process an Excel file to extract and fill vulnerability information.

Introduction

The goal is to manipulate rows highlighted in yellow in the original data to produce a target dataset.

The second image shows the original and desired data.

Implementation

The script uses the openpyxl library to read the workbook, iterate over rows, categorize IP addresses based on whether a mis‑report or fix proof is provided, and write the results into specific cells (C15‑G15). The full code is presented below.

import openpyxl

def fill_table(bug_name, sheet, row_number):
    # 初始化IP地址列表，已提供误报证明ip
    ip_list1 = []
    # 初始化IP地址列表，已提供无法整改证明ip
    ip_list2 = []
    # 初始化IP地址列表，没有误报和无法整改证明的IP
    ip_list3 = []

    is_provided_misreport_list = []
    is_provided_fixed_prove_list = []

    # 遍历每一行
    for row in range(2, sheet.max_row + 1):
        system_name = sheet.cell(row=row, column=1).value
        vulnerability_name = sheet.cell(row=row, column=2).value
        ip = sheet.cell(row=row, column=3).value
        is_provided_misreport = sheet.cell(row=row, column=4).value
        is_provided_fixed_prove = sheet.cell(row=row, column=5).value

        # 判断漏洞名称和是否提供误报证明，针对OpenSSH 'schnorr.c'远程内存破坏漏洞(CVE-2014-1692)漏洞
        if vulnerability_name == bug_name and is_provided_misreport == '是':
            is_provided_misreport_list.append(is_provided_misreport)
            ip_list1.append(ip)

        if vulnerability_name == bug_name and is_provided_fixed_prove == '是':
            is_provided_fixed_prove_list.append(is_provided_fixed_prove)
            ip_list2.append(ip)

        if vulnerability_name == bug_name and is_provided_misreport == '否' and is_provided_fixed_prove == '否':
            ip_list3.append(ip)

    # 填写C15单元格
    if '是' in is_provided_misreport_list:
        sheet.cell(row=row_number, column=3).value = '是'
    else:
        sheet.cell(row=row_number, column=3).value = '否'

    # 填写D15单元格
    sheet.cell(row=row_number, column=4).value = ','.join(ip_list1)

    # 填写E15单元格
    if '是' in is_provided_fixed_prove_list:
        sheet.cell(row=row_number, column=5).value = '是'
    else:
        sheet.cell(row=row_number, column=5).value = '否'

    # 填写F15单元格
    sheet.cell(row=row_number, column=6).value = ','.join(ip_list2)

    # 填写G15单元格
    sheet.cell(row=row_number, column=7).value = ','.join(ip_list3)

if __name__ == '__main__':
    # 打开Excel文件
    workbook = openpyxl.load_workbook('测试.xlsx')
    sheet = workbook.active
    bug_name1 = "OpenSSH 'schnorr.c'远程内存破坏漏洞(CVE-2014-1692)"
    bug_name2 = "OpenSSH 'x11_open_helper()'函数安全限制绕过漏洞(CVE-2015-5352)"
    fill_table(bug_name1, sheet, row_number=15)
    fill_table(bug_name2, sheet, row_number=16)
    # 保存修改后的Excel文件
    workbook.save('updated_excel_file.xlsx')

After running the script, the resulting Excel sheet looks like the third image.

The script can be adapted by changing the bug_name and the target row number.

Conclusion

The article demonstrates a concrete Python automation solution for Excel‑based vulnerability reporting, providing both explanation and reusable code.