Copying to a specific sheet: openpyxl - destination sheet ignored when using copy_worksheet

Per this answer and these documents I tried to specify a source and target sheet to write to, but when I do, the results are the same as if I hadn't specified a target:

source

target

from openpyxl import load_workbook
wb = load_workbook('MyFile.xlsx')
ws = 'Sheet1'


idx = book.index(ws)
new_ws = 'Test'
book.create_sheet(new_ws, idx+1)
source = book[ws]
target = book[new_ws]
target = book.copy_worksheet(source)
wb.save('Output.xlsx')


vs

source = book[ws]
book.copy_worksheet(source)
wb.save('Output.xlsx')


Both result in a new worksheet called Sheet1 Copy added to the end of the workbook. How to a copy a sheet into another empty sheet, or specific location within the workbook?

Sheet1 Copy

1 Answer
1

I'm still not sure if there's a bug in the ability for copy_workbook to write to a source. However, this is the code I wrote to get around this limitation:

from openpyxl import load_workbook

wb = load_workbook('MyFile.xlsx')
ws = 'Sheet1'
ws_new_name = 'original_sheet'
new_ws = 'Test'

idx = wb.index(ws)
#print (ws.title, 'index is', idx)

wb.copy_worksheet(wb[ws.title])

#Get the position of the new sheet
n = len(wb.sheetnames) #number of worksheets
copied_ws = wb.worksheets[n-1]
# print ('Final sheet is:', wb.sheetnames[n-1])

#rename worksheets
copied_ws.title = new_ws
ws.title = ws_new_name

#reorder the sheet
sheetorder = [i for i in range(n)] #create an array equal to number of worksheets
for j in range(n-1-idx): #shift sheet order after copied sheet by 1
sheetorder[j+idx+1] = sheetorder[j+idx+1]-1
sheetorder[idx+1] = n-1 #last sheet gets inserted directly after copied sheet
wb._sheets =[wb._sheets[i] for i in sheetorder]


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.