How to Append DataFrames to Existing Excel Sheets with Pandas and Openpyxl

Hello, I'm Pi Pi. Recently I was asked in a Python community about a Pandas data analysis issue, and here's the solution.

1. Problem Overview

The original code attempted to write a DataFrame to an existing Excel file using Openpyxl and Pandas:

import openpyxl
import pandas as pd

df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

book = openpyxl.load_workbook('D:\\对照表.xlsx')
writer = pd.ExcelWriter('D:\\对照表.xlsx', engine='openpyxl')
writer.book = book

df.to_excel(writer, startrow=writer.sheets['Sheet1'].min_row, index=False, header=False)

writer.close()

The issue was that the data could not be appended correctly.

2. Solution

A community member provided a revised approach that works:

import pandas as pd

df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
writer = pd.ExcelWriter('对照表.xlsx', engine='openpyxl',
                         if_sheet_exists='overlay', mode="a")
df.to_excel(writer, startrow=writer.sheets['Sheet1'].min_row,
            index=False, header=False)
writer.close()

This code successfully writes the DataFrame into the existing sheet.

3. Additional Question

When trying to add a new row to a second sheet (Sheet2), the following call can be used:

df1.to_excel(writer, startrow=writer.sheets['Sheet2'].min_row,
             index=False, header=False)

4. Summary

The article demonstrates how to manipulate Excel files with Pandas and Openpyxl, providing concrete code examples and tips for handling multiple sheets.

Tips for asking questions: provide a small, de‑identified sample file, include the exact error screenshot, and share the full code or a .py file if it exceeds 50 lines.