Python pandas写入Excel格式被覆盖：如何实现数据追加？

使用Python 3.11.5结合os与pandas开发项目，需求如下：

• 读取9个源文件，将每个文件中File NameX和File CategoryX（X为1-26）对应的数据，迁移至对应dest (X).xlsx文件的ITEM_DOCUMENT和ITEM_DOCUMENT_TYPE列
• 目标文件第一行为分析师说明信息，第二行为表头，数据从第三行开始，初始无数据

当前代码运行后出现问题：目标文件虽保留表头，但第一行分析师信息丢失、部分字体样式（如红粗变黑粗）被修改、列宽改变，并非单纯追加数据。核心代码如下：

for i, (file_name_col, file_category_col) in \
                enumerate(zip(file_name_cols, file_category_cols), start=1):
                dest_file = os.path.join(dest_folder, f"dest ({i}).xlsx")

                # Check Column Existence:
                file_name_col = f'File Name{i}'
                file_category_col = f'File Category{i}'

                if file_name_col in source_data.columns and \
                    file_category_col in source_data.columns:
                    # Create destination DataFrame with specified headers if the file doesn't exist
                    if not os.path.isfile(dest_file):
                        dest_columns = ['PART_NUMBER', 'LANGUAGE_CODE', 'MANUFACTURER_NAME',
                                        'BRAND_NAME', 'ITEM_DOCUMENT', 'ITEM_DOCUMENT_TYPE']
                        dest_data = pd.DataFrame(columns=dest_columns)
                        dest_data.to_excel(dest_file, index=False)

                    # Read the existing destination data or
                    # create an empty DataFrame if the file doesn't exist
                    dest_data = pd.read_excel(dest_file, header=1) \
                        if os.path.isfile(dest_file) else pd.DataFrame()

                    dest_columns = ['PART_NUMBER', 'LANGUAGE_CODE', 'MANUFACTURER_NAME',
                                    'BRAND_NAME', 'ITEM_DOCUMENT', 'ITEM_DOCUMENT_TYPE']

                    # Ensure that the destination file has the required columns
                    for col in dest_columns:
                        if col not in dest_data.columns:
                            dest_data[col] = ''

                    new_data = source_data[['PART_NUMBER', 'LANGUAGE_CODE', \
                        'MANUFACTURER_NAME', 'BRAND_NAME']].copy()
                    new_data['ITEM_DOCUMENT'] = source_data[file_name_col].copy()
                    new_data['ITEM_DOCUMENT_TYPE'] = \
                        new_data['ITEM_DOCUMENT'].apply(determine_document_type)

                    # Append new data to the existing destination file
                    dest_data = pd.concat([dest_data, new_data], ignore_index=True)

                    # Write the combined data to the destination file
                    dest_data.to_excel(dest_file, index=False, sheet_name='Sheet1', engine='openpyxl')
                else:
                    # Handle the case where the columns don't exist
                    raise ValueError(f"Columns '{file_name_col}' \
                                    and/or '{file_category_col}' do not exist in source_data.")

问题根源在于pandas.to_excel()会重新生成整个Excel文件，覆盖原有格式和非数据行（第一行的分析师说明）。解决思路是用openpyxl直接操作现有工作簿，仅追加数据行，完全保留原有样式。

步骤说明 #

1. 处理目标文件不存在的情况：如果文件未创建，先通过openpyxl生成包含分析师说明、表头的模板文件，并设置好所需格式（如表头字体红粗）
2. 打开现有工作簿：保留所有原有格式、列宽、说明行
3. 定位数据起始行：找到当前数据的最后一行，从下一行开始写入新数据
4. 逐行写入新数据：将pandas处理好的新数据逐行写入工作簿，不修改原有格式

修改后的核心代码 #

import openpyxl
from openpyxl.styles import Font, Alignment

# ... 其他原有代码（读取源数据、处理new_data的部分保持不变）

for i, (file_name_col, file_category_col) in enumerate(zip(file_name_cols, file_category_cols), start=1):
    dest_file = os.path.join(dest_folder, f"dest ({i}).xlsx")
    file_name_col = f'File Name{i}'
    file_category_col = f'File Category{i}'

    if file_name_col in source_data.columns and file_category_col in source_data.columns:
        # 处理目标文件不存在的情况：创建带格式的模板
        if not os.path.isfile(dest_file):
            wb = openpyxl.Workbook()
            ws = wb.active
            ws.title = 'Sheet1'
            
            # 写入第一行：分析师说明信息（根据实际内容修改）
            ws['A1'] = "分析师说明：此文件用于存放对应类文档数据，请勿随意修改格式"
            ws['A1'].font = Font(bold=True)
            ws.merge_cells('A1:F1')  # 合并第一行所有列
            ws['A1'].alignment = Alignment(horizontal='left')
            
            # 写入第二行：表头
            dest_columns = ['PART_NUMBER', 'LANGUAGE_CODE', 'MANUFACTURER_NAME',
                            'BRAND_NAME', 'ITEM_DOCUMENT', 'ITEM_DOCUMENT_TYPE']
            for col_idx, col_name in enumerate(dest_columns, start=1):
                cell = ws.cell(row=2, column=col_idx, value=col_name)
                cell.font = Font(bold=True, color='FF0000')  # 设置红粗字体
                # 自定义列宽（根据实际需求调整）
                ws.column_dimensions[openpyxl.utils.get_column_letter(col_idx)].width = 20
            
            wb.save(dest_file)
        
        # 打开现有工作簿，保留格式
        wb = openpyxl.load_workbook(dest_file)
        ws = wb['Sheet1']
        
        # 找到当前数据的最后一行（数据从第3行开始）
        last_row = ws.max_row
        start_row = last_row + 1 if last_row >=2 else 3
        
        # 处理新数据（原有逻辑不变）
        new_data = source_data[['PART_NUMBER', 'LANGUAGE_CODE', 
                                'MANUFACTURER_NAME', 'BRAND_NAME']].copy()
        new_data['ITEM_DOCUMENT'] = source_data[file_name_col].copy()
        new_data['ITEM_DOCUMENT_TYPE'] = new_data['ITEM_DOCUMENT'].apply(determine_document_type)
        
        # 逐行写入新数据
        for _, row in new_data.iterrows():
            for col_idx, col_name in enumerate(new_data.columns, start=1):
                ws.cell(row=start_row, column=col_idx, value=row[col_name])
            start_row += 1
        
        # 保存工作簿
        wb.save(dest_file)
    else:
        raise ValueError(f"Columns '{file_name_col}' and/or '{file_category_col}' do not exist in source_data.")

关键注意点 #

• 若目标文件是预先设置好格式的模板，可直接跳过“创建模板”的步骤，直接打开模板文件进行追加
• 列宽、字体样式等可根据实际需求调整，代码中已给出示例
• 使用openpyxl.load_workbook()时默认保留原有格式，不会覆盖任何非数据内容

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