如何使用DataFrame替换Excel Worksheet的部分数据？指定行列条件下的编码实现问询

Got it, here's a straightforward way to replace a specific 30x5 region in your existing Excel worksheet with a pandas DataFrame while keeping the rest of the data intact. We’ll use openpyxl (since pandas' default to_excel would overwrite the whole sheet) to handle the existing workbook:

First, make sure you have the required libraries installed:

pip install pandas openpyxl

Then follow this step-by-step code:

import pandas as pd
from openpyxl import load_workbook

# 1. Load your existing Excel workbook and target worksheet
# Replace 'your_excel_file.xlsx' with your actual file path
wb = load_workbook('your_excel_file.xlsx')
ws = wb.active  # Or specify the sheet name with wb['Sheet1'] if needed

# 2. Prepare your DataFrame (replace this with your actual 30x5 dataset)
# Example: Create a sample 30x5 DataFrame for testing
df = pd.DataFrame([[f'NewVal_{r}_{c}' for c in range(1,6)] for r in range(1,31)])

# 3. Define the starting position for the replacement (openpyxl uses 1-based indexing!)
# Adjust these values to match where you want the DataFrame to go
start_row = 2  # Start replacing from row 2
start_col = 3  # Start replacing from column 3

# 4. Write DataFrame values to the specified worksheet region
for row_idx, row_data in enumerate(df.values, start=start_row):
    for col_idx, cell_value in enumerate(row_data, start=start_col):
        ws.cell(row=row_idx, column=col_idx, value=cell_value)

# 5. Save the modified workbook
# Save to a new file to avoid overwriting the original accidentally, or use the original path
wb.save('modified_excel_file.xlsx')

Key Details to Keep in Mind: #

• 1-Based Indexing: Openpyxl uses 1-based numbering for rows and columns (unlike pandas' 0-based). So if you want to start replacing at cell A1, set start_row=1 and start_col=1.
• Data Type Handling: Openpyxl automatically handles most common data types (strings, numbers, dates), but if you need special formatting (like currency or date styles), you can add cell styling logic after writing values.
• Backup First: Always save to a new file initially, or make a backup of your original Excel file, to avoid losing data if something goes wrong.
• Target Sheet Selection: If your data isn’t in the active sheet, replace wb.active with wb['YourSheetName'] to select the correct worksheet.

That’s all—this will only overwrite the 30x5 region you specify, leaving every other cell in the worksheet exactly as it was.

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