从目录文件复制数据至模板文件时列映射错位问题排查

问题根源 #

原代码的核心问题在于：

• 直接用append按列表顺序追加数据，没有匹配模板的列名，导致数据无法对应到正确列；
• 错误地将行号作为第一列数据添加，进一步打乱了列的对应关系；
• 没有处理模板中存在但目录文件没有的列，无法自动留空。

修正后的代码 #

import pandas as pd
import openpyxl

# 加载目录和模板文件
catalogue_file = r"catalogue.xlsx"
catalogue_df = pd.read_excel(catalogue_file, sheet_name="Sheet_all")

template_file = r'template.xlsx'
# 不要用data_only=True，避免丢失表头文本（如果表头是公式的话）
template_workbook = openpyxl.load_workbook(template_file)

# 目录列与模板列的映射
column_mapping = {
    'PRODUCT ID': 'External ID',
    'model': 'Model Number',
    'Attribute1': 'Characteristic', 
    'Description': 'Model Name',
    'PRODUCT TYPE': 'Product Type', 
}

# 产品类型与模板工作表的映射
type_to_sheet_mapping = {
    'PRODUCT_TYPE_1': 'Template-type1',
    'PRODUCT_TYPE_2': 'Template-type2',
    'PRODUCT_TYPE_3': 'Template-type3',
}

# 遍历目录每一行数据
for index, row in catalogue_df.iterrows():
    product_type = row['PRODUCT TYPE']
    # 重命名列，只保留映射中存在的列
    mapped_row = row.rename(column_mapping)[column_mapping.values()]
    
    if product_type in type_to_sheet_mapping:
        sheet_name = type_to_sheet_mapping[product_type]
        try:
            template_sheet = template_workbook[sheet_name]
            # 获取模板第3行的表头，建立列名到列索引的映射（列索引从1开始）
            header_row = 3
            col_mapping = {}
            for col in template_sheet.iter_cols(min_row=header_row, max_row=header_row):
                col_name = col[0].value
                if col_name:
                    col_mapping[col_name] = col[0].column
            
            # 找到模板工作表的最后一行，从下一行开始写入
            last_row = template_sheet.max_row
            write_row = last_row + 1
            
            # 遍历模板的每一列，填充对应数据，无对应则留空
            for col_name, col_idx in col_mapping.items():
                # 检查当前列是否有对应的数据
                if col_name in mapped_row:
                    template_sheet.cell(row=write_row, column=col_idx, value=mapped_row[col_name])
                # 无对应数据则默认留空，无需额外操作
                
        except Exception as e:
            print(f"{product_type} 对应的模板工作表不存在，错误信息：{str(e)}")

# 保存修改后的模板
template_workbook.save(r"updated_template.xlsx")

关键修改说明 #

1. 表头映射与列匹配：

   • 读取模板第3行的表头，建立「列名→列索引」的映射，确保数据写入正确的列位置。
   • 遍历模板的每一列，根据列名从映射后的行数据中取值，无对应数据则自动留空。

2. 移除错误的行号添加：

   • 删除了原代码中[last_row + 1] + data_to_copy.tolist()的行号前缀，避免列错位。

3. 优化数据处理：

   • 重命名列后只保留映射中定义的模板列，避免多余数据干扰。
   • 去掉data_only=True，防止模板表头是公式时丢失文本内容。

4. 异常处理优化：

   • 捕获具体异常并输出错误信息，便于排查问题。

内容的提问来源于stack exchange，提问作者Maria Parellada Peco