如何将DataFrame插入已调整单元格尺寸的现有Excel文件

Got it, I see exactly what you're trying to do—you’ve already set up custom column widths and row heights in an Excel file using openpyxl, and now you want to add your Pandas DataFrame to it without wiping out that hard-won formatting. The main pitfall here is that Pandas’ default to_excel() will overwrite the entire file, so we need a way to overlay the data onto your existing formatted sheet instead.

Let’s break this down step by step:

Step 1: Why Your Initial Approach Needs Adjustment #

If you use Pandas’ standard df.to_excel() directly, it’ll create a brand new file (or overwrite your existing one), deleting all your custom column widths and row heights. Instead, we need to:

• Open your already-formatted workbook
• Use Pandas’ ExcelWriter with the openpyxl engine in append mode
• Overlay the DataFrame onto your existing sheet without replacing the formatting

Step 2: Full Working Code #

Here’s the complete code that builds on what you’ve already written, including inserting the DataFrame while preserving your formatting:

from openpyxl import Workbook
import pandas as pd
from openpyxl import load_workbook

# Your original code to create the formatted Excel file
wb = Workbook()
ws = wb.active
ws.column_dimensions['A'].width = 10
ws.column_dimensions['B'].width = 10
ws.column_dimensions['C'].width = 50
ws.column_dimensions['D'].width = 130
ws.column_dimensions['E'].width = 30
ws.column_dimensions['F'].width = 10
# Cleaned up row height setup with a loop
for row_num in range(1, 7):
    ws.row_dimensions[row_num].height = 150
wb.save(filename='t.xlsx')

# Your DataFrame
data = {'first': [1], 'second': [2], 'third': [3], 'fourth': [4]}
t = pd.DataFrame(data, columns=['first', 'second', 'third','fourth'])

# Now insert the DataFrame into the formatted file
# Load the existing workbook
wb = load_workbook('t.xlsx')
# Create ExcelWriter with critical parameters to preserve formatting
with pd.ExcelWriter('t.xlsx', engine='openpyxl', mode='a', if_sheet_exists='overlay') as writer:
    # Write the DataFrame starting at row 7 (avoids overwriting your first 6 formatted rows)
    # Change startrow to 0 if you want to start at the very top
    t.to_excel(writer, sheet_name='Sheet', startrow=6, index=False)

Key Details to Note: #

• mode='a': Tells Pandas to append to your existing file instead of creating a new one
• if_sheet_exists='overlay': This is the magic parameter—it means we’ll write the DataFrame over the existing sheet without replacing the entire sheet (which would erase formatting). It only updates the cells containing your data, leaving column widths and row heights untouched.
• startrow=6: I set this to start writing at row 7 (since the parameter uses 0-indexing) to avoid overwriting the first 6 rows you set custom heights for. Adjust this number if you want the data to start elsewhere.
• index=False: Prevents Pandas from writing the DataFrame’s index column into Excel, keeping your column alignment clean and matching your original setup.

Check the Result #

After running this code, open t.xlsx—you’ll see your original column widths and row heights are fully preserved, and your DataFrame data is neatly inserted starting at row 7 (or whatever row you specified).

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