close

How to save a new sheet in an existing excel file, using Pandas?

Hello Guys, How are you all? Hope You all Are Fine. Today We Are Going To learn about How to save a new sheet in an existing excel file, using Pandas in Python. So Here I am Explain to you all the possible Methods here.

Without wasting your time, Let’s start This Article.

Table of Contents

How to save a new sheet in an existing excel file, using Pandas?

  1. How to save a new sheet in an existing excel file, using Pandas?

    Here I generate an excel file, from my understanding it does not really matter whether it is generated via the “xslxwriter” or the “openpyxl” engine.

  2. save a new sheet in an existing excel file, using Pandas

    Here I generate an excel file, from my understanding it does not really matter whether it is generated via the “xslxwriter” or the “openpyxl” engine.

Method 1

Thank you. I believe that a complete example could be good for anyone else who have the same issue:

import pandas as pd
import numpy as np

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

x1 = np.random.randn(100, 2)
df1 = pd.DataFrame(x1)

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

writer = pd.ExcelWriter(path, engine = 'xlsxwriter')
df1.to_excel(writer, sheet_name = 'x1')
df2.to_excel(writer, sheet_name = 'x2')
writer.save()
writer.close()

Here I generate an excel file, from my understanding it does not really matter whether it is generated via the “xslxwriter” or the “openpyxl” engine.

When I want to write without loosing the original data then

import pandas as pd
import numpy as np
from openpyxl import load_workbook

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

book = load_workbook(path)
writer = pd.ExcelWriter(path, engine = 'openpyxl')
writer.book = book

x3 = np.random.randn(100, 2)
df3 = pd.DataFrame(x3)

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

df3.to_excel(writer, sheet_name = 'x3')
df4.to_excel(writer, sheet_name = 'x4')
writer.save()
writer.close()

this code do the job!

Method 2

In the example you shared you are loading the existing file into book and setting the writer.book value to be book. In the line writer.sheets = dict((ws.title, ws) for ws in book.worksheets) you are accessing each sheet in the workbook as ws. The sheet title is then ws so you are creating a dictionary of {sheet_titles: sheet} key, value pairs. This dictionary is then set to writer.sheets. Essentially these steps are just loading the existing data from 'Masterfile.xlsx' and populating your writer with them.

Now let’s say you already have a file with x1 and x2 as sheets. You can use the example code to load the file and then could do something like this to add x3 and x4.

path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"
writer = pd.ExcelWriter(path, engine='openpyxl')
df3.to_excel(writer, 'x3', index=False)
df4.to_excel(writer, 'x4', index=False)
writer.save()

That should do what you are looking for.

Summery

It’s all About this issue. Hope all Methods helped you a lot. Comment below Your thoughts and your queries. Also, Comment below which Method worked for you? Thank You.

Also, Read