How to Append New Sheets to Existing Excel Files Using Pandas?

Adding New Sheets to Existing Excel Files with Pandas

Pandas provides a convenient interface for working with Excel files. However, when saving new sheets to an existing file, users often encounter challenges. This article addresses the issue and provides a comprehensive solution.

Existing Code:

The provided code saves two DataFrames to two sheets but fails to add more sheets without losing the original data due to closing the ExcelWriter object.

Best Approach:

The ideal workflow involves creating an Excel file, saving some data, closing the ExcelWriter, and then reopening it to add more sheets. This approach ensures data preservation.

Openpyxl Integration:

To append sheets without losing data, Pandas can utilize Openpyxl. Openpyxl allows for the manipulation of Excel files directly. By loading the existing file using load_workbook and setting it to be the ExcelWriter's book, we can add new sheets without affecting the existing ones.

Complete Example:

Here's a complete example:

<code class="python">import pandas as pd
import numpy as np
from openpyxl import load_workbook

path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"

# Generate initial data
x1 = np.random.randn(100, 2)
df1 = pd.DataFrame(x1)

x2 = np.random.randn(100, 2)
df2 = pd.DataFrame(x2)

# Write initial sheets
writer = pd.ExcelWriter(path, engine='xlsxwriter')
df1.to_excel(writer, sheet_name='x1')
df2.to_excel(writer, sheet_name='x2')
writer.close()

# Open existing file and add new sheets
book = load_workbook(path)
writer = pd.ExcelWriter(path, engine='openpyxl')
writer.book = book

# Generate new data
x3 = np.random.randn(100, 2)
df3 = pd.DataFrame(x3)

x4 = np.random.randn(100, 2)
df4 = pd.DataFrame(x4)

# Append new sheets
df3.to_excel(writer, sheet_name='x3')
df4.to_excel(writer, sheet_name='x4')

writer.close()</code>

This code first generates two sheets and closes the ExcelWriter. Then, it reopens the file and utilizes Openpyxl to add two more sheets, preserving the original data.

The above is the detailed content of How to Append New Sheets to Existing Excel Files Using Pandas?. For more information, please follow other related articles on the PHP Chinese website!