CODEWITHSUNDEEP
pythonexcelpandasdataframeopenpyxl
rcheeks23
rcheeks23

Reputation: 15

Adding data from different data frame to excel

Currently what I want to do is take data I have from a data frame list and add them to an existing excel file as their own tabs.

To test this out, I have tried it with one data frame. There are no error but when I go to open the excel file it says it is corrupt. I proceed to recover the information but I rather not have to do that every time. I believe it would fail if I looped through my list to make this happen.

    import os,glob
    import pandas as pd
    from openpyxl import load_workbook
     
    master_file='combined_csv.xlsx'
    #set the directory
    os.chdir(r'C:\Users\test') 
    #set the type of file
    extension = 'csv' 
    #take all files with the csv extension into an array
    all_filenames = [i for i in glob.glob('*.{}'.format(extension))]
    col_to_keep=["Name",
                 "Area (ft)",
                 "Length (ft)",
                 "Center (ft)",
                 "ID",
                 "SyncID"]
        
    combine_csv = pd.concat([pd.read_csv(f, delimiter=';', usecols=col_to_keep) for f in all_filenames])
    combine_csv.to_excel(master_file, index=False,sheet_name='All')
    # Defining the path which excel needs to be created
    # There must be a pre-existing excel sheet which can be updated
    FilePath = r'C:\Users\test'
     
    # Generating workbook
    ExcelWorkbook = load_workbook(FilePath)
     
    # Generating the writer engine
    writer = pd.ExcelWriter(FilePath, engine = 'openpyxl')
     
    # Assigning the workbook to the writer engine
    writer.book = ExcelWorkbook
     
     
    # Creating first dataframe
    drip_file = pd.read_csv(all_filenames[0], delimiter = ';', usecols=col_to_keep)
    SimpleDataFrame1=pd.DataFrame(data=drip_file)
    print(SimpleDataFrame1)
     
     
    # Adding the DataFrames to the excel as a new sheet
    SimpleDataFrame1.to_excel(writer, sheet_name = 'Drip')
    
    writer.save()
    writer.close()

It seems like it runs fine with no errors but when I open the excel file I get the error shown below.

enter image description here

Does anyone see something wrong with the code that would cause excel to give me this error?

Thank you in advance

Upvotes: 0

Views: 82

Answers (1)

Rasmus
Rasmus

Reputation: 136

Your code knows its printing data to the same workbook, but to use writer you will also need to tell python what the sheet names are:

book = load_workbook(your_destination_file)
writer = pd.ExcelWriter(your_destination_file, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)  # tells 
pandas/python what the sheet names are

Your_dataframe.to_excel(writer, sheet_name=DesiredSheetname)

writer.save()

Also, if you have pivots, pictures, external connections in the document they will be deleted and could be what is causing the corruption.

Upvotes: 1

Related Questions