0

I have a Final.xlsx that contains multiple sheet - shee1, sheet2 ,sheet3 , each having some graphs and data. I have another file file5.xlsx that i want to add in Final.xlsx in tab . The below code is working but the Final.xlsx existing sheets data is getting missed(contents,formats, grpahs, and others) . need help to fix this. 

    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()
steveJ
  • 2,171
  • 3
  • 11
  • 16
  • found something, may be helpful: https://stackoverflow.com/questions/46386143/python-library-to-open-existing-xlsx-workbook-with-charts/46390899#46390899 – anky Aug 29 '18 at 10:10
  • https://openpyxl.readthedocs.io/en/stable/pandas.html – Charlie Clark Aug 29 '18 at 10:53
  • @CharlieClark I understood little bit from that link, but still its not clear to me how to apply for the code i pasted above for the changes .. Any hint or snippet can be helpful, please. – steveJ Aug 29 '18 at 11:00

1 Answers1

1

Internally Pandas uses the xlrd library to read xlsx files. This library is fast but, because it is essentially bolted onto support for the BIFF format, it's support for OOXML is limited. Seeing as Pandas doesn't know anything about charts, it couldn't keep them anyway.

openpyxl provides utilities in openpyxl.utils.dataframe for going between XLSX's rows and Pandas Dataframes giving you full control when working, while keeping nearly everything else in your file. In your case, however, you don't even need Pandas as you can simply loop over the cells from "file5.xlsx" and copy them to your other file.

Charlie Clark
  • 18,477
  • 4
  • 49
  • 55
  • So You are saying, I can add a new sheet(file5.xls) in `Final.xlsx` in new tab without using pandas ? Sorry for this question, first time I am working with excel .. – steveJ Aug 29 '18 at 12:27