0

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

steveJ
  • 2,171
  • 3
  • 11
  • 16
  • what is "an excel"? Excel is the name of a spreadsheet software. With this software you can create **workbooks** that have one or more **worksheets** to organize data and perform calculations. There is no such thing as "an excel". Please use accurate terms, not brand names, for things you need help with. – teylyn Aug 29 '18 at 08:45
  • @teylyn I mean `.xlsx ` file , updated / – steveJ Aug 29 '18 at 08:54

1 Answers1

1
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

anky
  • 74,114
  • 11
  • 41
  • 70
  • you have to read your xlsx file as a df using read_excel and then write out using the code above or you can directly write the df which you have instead of saving it to file5 – anky Aug 29 '18 at 09:21
  • foo.xlsx is the excel file which has multiple sheets, you want to write your df into foo.xlsx , so dont save it as an excel file file5, instead use the code and name the sheetname as file5 – anky Aug 29 '18 at 09:24
  • Just a question , how this code will detect `file5.xlsx` here , if not provided that was already created .. – steveJ Aug 29 '18 at 09:30
  • so, you dont need to create the file5.xlsx , you are putting the df into file5.xlsx, instead you create a sheet named file5 write the df directly into foo.xlsx. If making the file5.xlsx is absolutely mandatory, you can write the df into file5.xlsx and read it back again using read_excel, then use that dfnew = pd.read_excel('file5.xlsx') in my code, replace dfnew with df in that case. – anky Aug 29 '18 at 09:32
  • Its working, `file5` is getting added in sheet, however `file1 to file4` content inside it is getting colored with different color, strange ? – steveJ Aug 29 '18 at 09:42
  • Yeah, shouldnot be the case, may be some formatting issues with excel. You can close all excel and open and check :) . however if you are satisfied with the answer, pl accept. :) Happy coding – anky Aug 29 '18 at 09:44
  • Even Lot of content gets missing from `file to file5`, Any way it can be fixed? – steveJ Aug 29 '18 at 09:45
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/179007/discussion-between-anky-91-and-stevej). – anky Aug 29 '18 at 09:46