Copy worksheet from one workbook to another one using Openpyxl

Question

I have a large amount of EXCEL files (i.e. 200) I would like to copy one specific worksheet from one workbook to another one. I have done some investigations and I couldn't find a way of doing it with Openpyxl

This is the code I have developed so far

def copy_sheet_to_different_EXCEL(path_EXCEL_read,Sheet_name_to_copy,path_EXCEL_Save,Sheet_new_name):
''' Function used to copy one EXCEL sheet into another file.

    def path_EXCEL_read,Sheet_name_to_copy,path_EXCEL_Save,Sheet_new_name


Input data:
    1.) path_EXCEL_read: the location of the EXCEL file along with the name where the information is going to be saved
    2.) Sheet_name_to_copy= The name of the EXCEL sheet to copy
    3.) path_EXCEL_Save: The path of the EXCEL file where the sheet is going to be copied
    3.) Sheet_new_name: The name of the new EXCEL sheet

Output data:
    1.) Status= If 0, everything went OK. If 1, one error occurred.

Version History:
1.0 (2017-02-20): Initial version.

'''

status=0

if(path_EXCEL_read.endswith('.xls')==1): 
    print('ERROR - EXCEL xls file format is not supported by openpyxl. Please, convert the file to an XLSX format')
    status=1
    return status

try:
   wb = openpyxl.load_workbook(path_EXCEL_read,read_only=True)
except:
    print('ERROR - EXCEL file does not exist in the following location:\n  {0}'.format(path_EXCEL_read))
    status=1
    return status


Sheet_names=wb.get_sheet_names()    # We copare against the sheet name we would like to cpy

if ((Sheet_name_to_copy in Sheet_names)==0):
    print('ERROR - EXCEL sheet does not exist'.format(Sheet_name_to_copy))
    status=1
    return status   

# We checking if the destination file exists


if (os.path.exists(path_EXCEL_Save)==1):
    #If true, file exist so we open it

    if(path_EXCEL_Save.endswith('.xls')==1): 
        print('ERROR - Destination EXCEL xls file format is not supported by openpyxl. Please, convert the file to an XLSX format')
        status=1
    return status

    try:
        wdestiny = openpyxl.load_workbook(path_EXCEL_Save)
    except:
        print('ERROR - Destination EXCEL file does not exist in the following location:\n  {0}'.format(path_EXCEL_read))
        status=1
    return status

    #we check if the destination sheet exists. If so, we will delete it

    destination_list_sheets = wdestiny.get_sheet_names()

    if((Sheet_new_name in destination_list_sheets) ==True):
        print('WARNING - Sheet "{0}" exists in: {1}. It will be deleted!'.format(Sheet_new_name,path_EXCEL_Save))
        wdestiny.remove_sheet(Sheet_new_name) 

else:
    wdestiny=openpyxl.Workbook()
# We copy the Excel sheet

try:
    sheet_to_copy = wb.get_sheet_by_name(Sheet_name_to_copy) 
    target = wdestiny.copy_worksheet(sheet_to_copy)
    target.title=Sheet_new_name
except:
    print('ERROR - Could not copy the EXCEL sheet. Check the file')
    status=1
    return status

try:
    wdestiny.save(path_EXCEL_Save)
except:
    print('ERROR - Could not save the EXCEL sheet. Check the file permissions')
    status=1
    return status

#Program finishes
return status    

Any suggestions?

Cheers

Answer 1

You cannot use copy_worksheet() to copy between workbooks because it depends on global constants that may vary between workbooks. The only safe and reliable way to proceed is to go row-by-row and cell-by-cell.

You might want to read the discussions about this feature