How Can I Add New Sheets to an Existing Excel File Without Overwriting Data Using Pandas?

Saving New Sheets in an Existing Excel File with Pandas

The goal is to write multiple sheets into an existing Excel file without overwriting the existing data.

Original Code:

The provided code saves two DataFrames to two sheets in an Excel file using the 'xlsxwriter' engine. However, attempting to add new sheets results in the loss of original data.

Using the 'openpyxl' Engine:

To preserve existing data, use the 'openpyxl' engine. The code below demonstrates how to do that:

import pandas as pd<br>import numpy as np<br>from openpyxl import load_workbook</p>
<p>path = r"C:UsersfedelDesktopexcelDataPhD_data.xlsx"</p>
<h1>Create the Excel file with initial sheets</h1>
<p>x1 = np.random.randn(100, 2)<br>df1 = pd.DataFrame(x1)</p>
<p>x2 = np.random.randn(100, 2)<br>df2 = pd.DataFrame(x2)</p>
<p>writer = pd.ExcelWriter(path, engine='xlsxwriter')<br>df1.to_excel(writer, sheet_name='x1')<br>df2.to_excel(writer, sheet_name='x2')<br>writer.close()</p>
<h1>Load the workbook and attach it to the writer</h1>
<p>book = load_workbook(path)<br>writer = pd.ExcelWriter(path, engine='openpyxl')<br>writer.book = book</p>
<h1>Add new sheets and DataFrames</h1>
<p>x3 = np.random.randn(100, 2)<br>df3 = pd.DataFrame(x3)</p>
<p>x4 = np.random.randn(100, 2)<br>df4 = pd.DataFrame(x4)</p>
<p>df3.to_excel(writer, sheet_name='x3')<br>df4.to_excel(writer, sheet_name='x4')<br>writer.close()<br>

Understanding the Code:

• load_workbook loads the existing Excel file.
• Attach the loaded workbook to the writer object using writer.book = book.
• Add new sheets and DataFrames as before using to_excel.
• Close the writer to save the changes.

The code loops through the worksheets in the workbook and assigns them to a dictionary where the key is the sheet title and the value is the sheet object. This allows the writer to append new sheets and data to the existing file without overwriting existing sheets.

The above is the detailed content of How Can I Add New Sheets to an Existing Excel File Without Overwriting Data Using Pandas?. For more information, please follow other related articles on the PHP Chinese website!