如何将Python中Excel处理循环封装为函数并在主文件调用

函数封装实现 #

将遍历逻辑封装为独立函数，明确传入依赖参数并返回处理结果：

def process_excel_worksheet(ws, excluded_cells, merged_cell_map):
    data_list = []
    total_col = 0
    total_row = 0
    
    for row_i, row in enumerate(ws.iter_rows()):
        for col_i, cell in enumerate(row):
            row_dim = ws.row_dimensions[cell.row]
            if cell.coordinate in excluded_cells or row_dim.hidden:
                continue

            cell_data = {
                "id": "",
                "row": cell.row,
                "col": cell.column,
                "row_span": 1,
                "col_span": 1,
                "label": "",
                "xmin": "",
                "ymin": "",
                "xmax": "",
                "ymax": "",
                "score": "",
                "text": "" if cell.value is None else cell.value,
                "rowlabel": "",
                "verification_status": "",
                "status": "",
                "failed_validation": "",
                "label_id": "",
                "Merged_cell": None,
            }
            merged_cell_info = merged_cell_map.get(
                cell.coordinate, {"attrs": {"row_span": 1, "col_span": 1}}
            )
            if merged_cell_info:
                cell_data["row_span"] = merged_cell_info["attrs"]["row_span"]
                cell_data["col_span"] = merged_cell_info["attrs"]["col_span"]
                cell_data["Merged_cell"] = not (cell_data["row_span"] <= 1 and cell_data["col_span"] <= 1)
            
            if col_i + cell_data["col_span"] > total_col:
                total_col = col_i + cell_data["col_span"]

            if row_i + cell_data["row_span"] > total_row:
                total_row = row_i + cell_data["row_span"]

            data_list.append(cell_data)
    
    return data_list, total_col, total_row

参数说明 #

• ws: openpyxl库的Worksheet对象，代表要处理的Excel工作表
• excluded_cells: 集合类型，存储需要跳过的单元格坐标（如{"A1", "C3"}）
• merged_cell_map: 字典类型，键为单元格坐标，值为包含合并单元格行列跨度的信息（格式参考原代码中的结构）

主文件调用示例 #

在主脚本中加载Excel文件、准备依赖参数，然后调用封装好的函数：

from openpyxl import load_workbook

# 加载Excel文件并获取工作表
wb = load_workbook("your_excel_file.xlsx")
ws = wb.active  # 或指定工作表：wb["Sheet1"]

# 准备排除单元格集合（根据实际需求定义）
excluded_cells = {"A1", "B2"}

# 准备合并单元格映射（需根据实际合并情况生成，示例结构）
merged_cell_map = {
    "A3": {"attrs": {"row_span": 2, "col_span": 1}},
    "C2": {"attrs": {"row_span": 1, "col_span": 3}}
}

# 调用函数处理工作表
processed_data, total_cols, total_rows = process_excel_worksheet(ws, excluded_cells, merged_cell_map)

# 处理返回结果（示例：打印前3条数据）
for item in processed_data[:3]:
    print(item)
print(f"总列数: {total_cols}, 总行数: {total_rows}")

内容的提问来源于stack exchange，提问作者saurabh daund