如何用Pandas将含合并单元格的Excel表头插入至另一Excel首行？

Hey there! I totally get this frustration—Pandas isn't built to preserve Excel's formatting like merged cells when you're combining files. The append() method only cares about stacking raw data, so it completely ignores any merged cell structure from your original spreadsheets. Let's walk through how to fix this properly.

Why Pandas Fails Here #

Pandas reads Excel files into dataframes, which are tabular data structures with no concept of merged cells. When you use append(), it just concatenates the data rows, losing all formatting metadata in the process. So if you need to keep merged cells intact, you'll need to use a library that works directly with Excel's file structure instead of just the data.

Solution 1: Using openpyxl (Free, No Excel Installation Required) #

openpyxl lets you manipulate Excel files at the cell and sheet level, so you can copy both values and formatting (including merged cells) directly. Here's how to insert your merged row into the first position:

from openpyxl import load_workbook

# Load your source (with merged cells) and target Excel files
source_workbook = load_workbook('merge1.xlsx')
target_workbook = load_workbook('test1.xlsx')

source_sheet = source_workbook['Sheet1']
target_sheet = target_workbook['Sheet1']

# Insert an empty row at the top of the target sheet to make space
target_sheet.insert_rows(1)

# Copy cell values and styles from the source's first row to the target's new first row
for col_num, source_cell in enumerate(source_sheet[1], 1):
    target_cell = target_sheet.cell(row=1, column=col_num)
    target_cell.value = source_cell.value
    target_cell._style = source_cell._style  # Copy cell formatting (font, alignment, etc.)

# Replicate merged cell ranges from the source's first row to the target
for merged_range in source_sheet.merged_cells.ranges:
    # Only process merged ranges that are entirely in the first row
    if merged_range.min_row == 1 and merged_range.max_row == 1:
        target_sheet.merge_cells(
            start_row=1,
            start_column=merged_range.min_col,
            end_row=1,
            end_column=merged_range.max_col
        )

# Save the modified target file
target_workbook.save('test3.xlsx')

Solution 2: Using xlwings (Requires Excel Installed, Easier Syntax) #

If you have Excel installed on your machine, xlwings is even simpler—it uses Excel's native copy/paste functionality, which preserves all formatting automatically:

import xlwings as xw

# Run Excel in the background (visible=False keeps it hidden)
with xw.App(visible=False) as app:
    # Open both workbooks
    source_wb = xw.Book('merge1.xlsx')
    target_wb = xw.Book('test1.xlsx')
    
    source_sheet = source_wb.sheets['Sheet1']
    target_sheet = target_wb.sheets['Sheet1']
    
    # Copy the first row from the source and insert it at the top of the target
    source_sheet.range('1:1').copy()
    target_sheet.range('1:1').insert()
    
    # Save the result and clean up
    target_wb.save('test3.xlsx')
    source_wb.close()
    target_wb.close()

Final Notes #

• Stick with openpyxl if you need a cross-platform, Excel-free solution.
• Use xlwings if you want the most reliable formatting preservation (since it uses Excel's own engine).
• Pandas is great for data manipulation, but it's not the right tool when you need to retain Excel-specific formatting like merged cells.

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