copy worksheet function

Created originally on Bitbucket by tinup_onto5_tor (Jared Thompson)

I thought it might be useful to have a copy worksheet function. This functionality would be similar to the functionality in excel where you can right-click on a worksheet and select copy, then select the worksheet you want to copy.

My current workaround is to create a workbook that is pre-populated with lots of worksheets (of the same type (as in this usecase scenario I am just populating similar sheets with different sales rep data) then I just remove the ones I don't need/are left over.

I am using this workaround as my attempt to copy the sheets in using deepcopy and openpyxl resulting in all the memory being consumed on my computer.

Here is the code I used(made generic to illustrate the memory consumption):

This is the original code I submitted to the mailing list:

#!python

import openpyxl
import shutil
import copy

distinct_stores = ['willy']



for each in range(len(distinct_stores)):
    workbook_iter_name = str(distinct_stores[each])+'.xlsx'
    shutil.copyfile('basic_trial.xlsx',workbook_iter_name)
    primary = openpyxl.load_workbook(workbook_iter_name)
    reps_per_store = ['one','two','three','four','five','six','seven','eight','nine','ten','eleven','twelve','thirteen']
    for eacho in range(len(reps_per_store)): 
        ws = primary.get_sheet_by_name('rep')
        primary.add_sheet(copy.deepcopy(ws),eacho+1)
        wss=primary.worksheets[eacho+1]
        wss.title=str(reps_per_store[eacho])
    primary.save(workbook_iter_name)

That would just consume all the ram.

Charlie Clark on the mailing list suggested to use enumerate and some other cleanups. After having cleaned up the code per his excellent suggestions I have this:

#!python

import openpyxl
import shutil
import copy

distinct_stores = ['willy']



for idx,store in enumerate(distinct_stores):
    workbook_iter_name = store+'.xlsx'
    #using a blank, single worksheet xlsx file for illustration purposes
    shutil.copyfile('blank.xlsx',workbook_iter_name)
    primary = openpyxl.load_workbook(workbook_iter_name)
    reps_per_store = ['one','two','three','four','five','six','seven','eight','nine','ten','eleven','twelve','thirteen']
    for ido,reps in enumerate(reps_per_store): 
        ws = primary.get_sheet_by_name('rep')
        primary.add_sheet(copy.deepcopy(ws),ido+1)
        wss=primary.worksheets[ido+1]
        wss.title = reps
    primary.save(workbook_iter_name)

This block of code no longer consumes all the ram, but it is very cpu intensive.

As a comparison when i pre-populate the workbook with 55 tabs (generally use 30-45), then I can generate 5 workbooks each with 30-45 tabs while populating them with their own rep data - in under 15 seconds.

So my workaround works but I thought it might be nice to have that copy ability in openpyxl instead of having to pre-populate the workbooks.

Thanks for the awesome openpyxl software, I hope this idea may be useful.