Python从含数据透视表Excel提取值至多文件的技术优化问询

我有一份含数据透视表的Excel文件，需根据目标文件的工作表名称提取值并写入多个目标Excel文件，目前存在三个技术问题，以下是针对性解决方法：

问题1：读取文件的两列数据直至非空行 #

指定读取Row Labels和Count of Name两列，同时过滤掉末尾空行，确保只读取有效数据：

# 读取源文件的指定两列，跳过前2行，保留有效行
df = pd.read_excel(
    "C:\\Users\\614155097\\OneDrive - BT Plc\\Documents\\UPS-P03\\FTE 2223 P03_v1.xlsx",
    skiprows=2,
    header=0,
    usecols=["Row Labels", "Count of Name"]  # 指定只读取需要的两列
)
# 填充空值并过滤全空行
df = df.fillna(method='ffill').dropna(how='all').reset_index(drop=True)
# 设置索引方便后续查找
df.set_index("Row Labels", inplace=True)

问题2：写入指定单元格时保留文件原有内容 #

使用openpyxl直接操作Excel文件，避免pandas覆盖整个工作表，完整保留原有格式和内容：

from openpyxl import load_workbook

# 打开目标文件，保留原有格式和内容
wb = load_workbook(working_file)
ws = wb.active  # 取第一个工作表（可根据需求指定表名）

# 找到"Total Center FTEs reported"所在行
target_row = None
for row in ws.iter_rows(min_row=4):  # 从第4行开始（对应原代码header=3）
    if row[1].value == "Total Center FTEs reported":  # B列对应原代码的"UPS"列
        target_row = row[0].row
        break

if target_row:
    # 获取最后一列的列号
    last_col = ws.max_column
    # 修改指定单元格的值
    ws.cell(row=target_row, column=last_col).value = data[0]

# 保存文件（可选择另存为新文件避免覆盖原文件）
wb.save(working_file)

问题3：支持批量处理多个目标文件 #

将单个文件的处理逻辑封装为函数，传入文件路径列表批量执行：

def process_target_file(source_df, target_file_path):
    # 获取目标文件第一个工作表名称并格式化
    try:
        wb = load_workbook(target_file_path)
        tab_name = wb.sheetnames[0].capitalize()
        wb.close()
    except Exception as e:
        print(f"打开文件失败 {target_file_path}: {str(e)}")
        return

    # 从源数据中提取对应值
    if tab_name not in source_df.index:
        print(f"源数据中无匹配标签 {tab_name}，跳过 {target_file_path}")
        return
    data = source_df.loc[tab_name, "Count of Name"]

    # 写入目标文件指定单元格
    try:
        wb = load_workbook(target_file_path)
        ws = wb.active
        target_row = None
        # 查找目标行
        for row in ws.iter_rows(min_row=4, values_only=False):
            if row[1].value == "Total Center FTEs reported":
                target_row = row[0].row
                break
        if not target_row:
            print(f"文件 {target_file_path} 未找到目标行")
            wb.close()
            return
        
        last_col = ws.max_column
        ws.cell(row=target_row, column=last_col).value = data
        wb.save(target_file_path)
        print(f"处理完成: {target_file_path}")
        wb.close()
    except Exception as e:
        print(f"写入文件失败 {target_file_path}: {str(e)}")
        wb.close()

# 批量处理的目标文件列表
target_files = [
    "C:\\Users\\614155097\\OneDrive - BT Plc\\Documents\\UPS-P04\\Final Working Files\\AMEA_Dalian_Workflow and Capacity Review - 2223 P04.xlsx",
    # 添加更多文件路径
]

# 遍历处理所有目标文件
for file in target_files:
    process_target_file(df, file)

完整整合代码 #

import pandas as pd
from openpyxl import load_workbook

def process_target_file(source_df, target_file_path):
    # 获取目标文件第一个工作表名称
    try:
        wb = load_workbook(target_file_path)
        tab_name = wb.sheetnames[0].capitalize()
        wb.close()
    except Exception as e:
        print(f"打开文件失败 {target_file_path}: {str(e)}")
        return

    # 提取源数据对应值
    if tab_name not in source_df.index:
        print(f"源数据中无匹配标签 {tab_name}，跳过 {target_file_path}")
        return
    data = source_df.loc[tab_name, "Count of Name"]

    # 写入目标文件指定单元格
    try:
        wb = load_workbook(target_file_path)
        ws = wb.active
        target_row = None
        # 查找目标行（从第4行开始，对应原header=3）
        for row in ws.iter_rows(min_row=4, values_only=False):
            if row[1].value == "Total Center FTEs reported":
                target_row = row[0].row
                break
        if not target_row:
            print(f"文件 {target_file_path} 未找到目标行")
            wb.close()
            return
        
        last_col = ws.max_column
        ws.cell(row=target_row, column=last_col).value = data
        wb.save(target_file_path)
        print(f"处理完成: {target_file_path}")
        wb.close()
    except Exception as e:
        print(f"写入文件失败 {target_file_path}: {str(e)}")
        wb.close()

if __name__ == "__main__":
    # 读取源文件数据（两列，直至非空行）
    source_file = "C:\\Users\\614155097\\OneDrive - BT Plc\\Documents\\UPS-P03\\FTE 2223 P03_v1.xlsx"
    df = pd.read_excel(
        source_file,
        skiprows=2,
        header=0,
        usecols=["Row Labels", "Count of Name"]
    )
    df = df.fillna(method='ffill').dropna(how='all').reset_index(drop=True)
    df.set_index("Row Labels", inplace=True)

    # 批量处理目标文件列表
    target_files = [
        "C:\\Users\\614155097\\OneDrive - BT Plc\\Documents\\UPS-P04\\Final Working Files\\AMEA_Dalian_Workflow and Capacity Review - 2223 P04.xlsx",
        # 在这里添加更多目标文件路径
    ]

    for file_path in target_files:
        process_target_file(df, file_path)

内容的提问来源于stack exchange，提问作者manmeet singh