CODEWITHSUNDEEP
pythonexcelpython-3.xpandasopenpyxl
steveJ
steveJ

Reputation: 2441

Add new .xlsx to an existing .xlsx in sheet(tab)

I have one code that goes like below..

#After performing some operation using pandas I have written df to the .xlsx

df.to_excel('file5.xlsx',index=False) # This excel has a single tab(sheet) inside

Then I have another .xlsx file (already provided) Final.xlsx , that has multiple tab(sheet) inside it like file1,file2,file3,file4 . I want to add the newly create file5.xls to the Final.xlsx as new sheet after sheet file4 .

Below answer provided by Anky, it is adding sheet the xlsx file5.xlsx to 'Final.xlsx' but the content inside sheets file1 2 3 4 is getting missed, format broken and also data is missing ...

import pandas
from openpyxl import load_workbook

book = load_workbook('foo.xlsx')
writer = pandas.ExcelWriter('foo.xlsx', engine='openpyxl') 
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df1=pd.read_excel('file5.xlsx')
df1.to_excel(writer, "new",index=False)

writer.save()

Need help to fix this..

I have asked this in separate question - Data missing, format changed in .xlsx file having multiple sheets using pandas, openpyxl while adding new sheet in existing .xlsx file

Upvotes: 0

Views: 569

Answers (1)

anky
anky

Reputation: 75150

import pandas
from openpyxl import load_workbook

book = load_workbook('foo.xlsx')
writer = pandas.ExcelWriter('foo.xlsx', engine='openpyxl') 
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

df.to_excel(writer, "file5",index=False)

writer.save()

Sheetname can be whatever you want to keep ex: file5

Upvotes: 1

Related Questions