Python 自动化办公：基于 openpyxl 实现 Excel 数据汇总与填充

Python 自动化办公：基于 openpyxl 实现 Excel 数据汇总与填充

一、背景与需求

在日常的安全运营或数据分析工作中，我们经常需要处理大量的 Excel 表格数据。例如，在漏洞管理场景中，需要将分散在不同行的漏洞信息（如漏洞名称、IP 地址、误报证明状态等）进行汇总，并填充到特定的统计行中。手动操作不仅效率低下，而且容易出错。

本文介绍如何使用 Python 的 openpyxl 库，编写脚本自动读取 Excel 文件，根据特定条件筛选数据，并将结果汇总填充至指定单元格，从而实现办公自动化。

原始场景

假设我们有一个包含漏洞扫描结果的 Excel 文件，其中每一行代表一个具体的漏洞实例。我们需要针对特定的漏洞名称（如 OpenSSH 相关漏洞），统计以下信息：

1. 是否提供了误报证明。
2. 如果提供，对应的 IP 地址列表。
3. 是否提供了整改证明。
4. 如果提供，对应的 IP 地址列表。
5. 既无误报证明也无整改证明的 IP 地址列表。

最终目标是将这些统计结果写入表格底部的汇总行中。

二、环境准备

在使用脚本之前，请确保您的开发环境中已安装 Python 以及必要的第三方库。

1. 安装 Python：建议安装 Python 3.7 及以上版本。
2. 安装 openpyxl：该库用于读写 Excel (.xlsx) 文件。

   pip install openpyxl
   

3. 准备数据文件：创建一个名为 测试.xlsx 的文件，确保其结构与代码逻辑中的行列对应一致。

三、核心逻辑解析

本方案的核心在于遍历 Excel 的每一行，提取关键字段，并根据预设规则进行分类和聚合。

数据结构说明

假设 Excel 文件的列结构如下：

• A 列 (Column 1): 系统名称
• B 列 (Column 2): 漏洞名称
• C 列 (Column 3): IP 地址
• D 列 (Column 4): 是否提供误报证明 ('是'/'否')
• E 列 (Column 5): 是否提供整改证明 ('是'/'否')

处理流程

1. 初始化列表：为不同类型的 IP 地址创建空列表，分别存储有证明和无证明的数据。
2. 遍历行数据：从第 2 行开始遍历（假设第 1 行为表头），读取每一行的关键字段。
3. 条件判断：
   • 匹配指定的漏洞名称。
   • 根据误报证明和整改证明的状态，将 IP 地址归类到不同的列表中。
4. 写入结果：
   • 判断是否有误报证明，填写 '是' 或 '否'。
   • 将误报证明的 IP 列表用逗号连接后填入指定单元格。
   • 对整改证明执行相同操作。
   • 处理剩余未证明的 IP 列表。

四、完整代码实现

以下是优化后的完整代码示例，增加了异常处理和更清晰的注释。

import openpyxl
from typing import List, Optional

def fill_table():
    
    
    ip_list_misreport = []      
    ip_list_fixed_prove = []    
    ip_list_no_proof = []       

    has_misreport = 
    has_fixed_prove = 

    
    
     row  (, sheet.max_row + ):
        :
            system_name = sheet.cell(row=row, column=).value
            vulnerability_name = sheet.cell(row=row, column=).value
            ip = sheet.cell(row=row, column=).value
            is_provided_misreport = sheet.cell(row=row, column=).value
            is_provided_fixed_prove = sheet.cell(row=row, column=).value

            
              ([vulnerability_name, ip]):
                

            
             vulnerability_name == bug_name:
                
                 is_provided_misreport == :
                    has_misreport = 
                    ip_list_misreport.append(ip)
                
                
                 is_provided_fixed_prove == :
                    has_fixed_prove = 
                    ip_list_fixed_prove.append(ip)
                
                
                :
                    ip_list_no_proof.append(ip)
         Exception  e:
            ()
            

    
    sheet.cell(row=row_number, column=).value =   has_misreport  

    
    sheet.cell(row=row_number, column=).value = .join(ip_list_misreport)  ip_list_misreport  

    
    sheet.cell(row=row_number, column=).value =   has_fixed_prove  

    
    sheet.cell(row=row_number, column=).value = .join(ip_list_fixed_prove)  ip_list_fixed_prove  

    
      ip_list_no_proof:
        sheet.cell(row=row_number, column=).value = 
    :
        sheet.cell(row=row_number, column=).value = .join(ip_list_no_proof)

 __name__ == :
    
    file_path = 
    output_path = 
    target_rows = [
        (, ),
        (, )
    ]

    :
        
        workbook = openpyxl.load_workbook(file_path)
        sheet = workbook.active

        
         bug_name, row_num  target_rows:
            fill_table(bug_name, sheet, row_num)

        
        workbook.save(output_path)
        ()

     FileNotFoundError:
        ()
     PermissionError:
        ()
     Exception  e:
        ()