Copying a worksheet with xlwings and python

Question

I have been using xlwings in Python, but have not been able to figure out how to copy a worksheet. I want to treat a particular worksheet as a template, and copy that worksheet each time before making modifications.

I am using version 0.11.4 of xlwings. If such functionality is not built in, I am okay with going outside of xlwings to use pywin32 functions in order to accomplish this.

This .Copy() is not in V0.11 and we have no solution to copy a worksheet now. — Joylove, Jul 30 '18 at 04:26
@Enfield I did this on version V.011. Did you ensure that your code looked like worksheet.api.Copy()? Another snag I believe would be copying the entire workbook as opposed to copying the worksheet. — P. Nokes, Jul 31 '18 at 12:45
@P.Nokes the worksheet.api. does not offer and the instruction worksheet.api.Copy() raises the error raise AttributeError("Unknown property, element or command: {!r}".format(name)) from e AttributeError: Unknown property, element or command: 'Copy' — Joylove, Aug 01 '18 at 17:33

score 7 · Answer 1 · edited Nov 18 '21 at 15:10

After poking around in several places and reading the pywin32 documentation, I found a solution to copy the worksheet:

import xlwings as xw
wb = xw.Book('filename.xlsx')
sheet = wb.sheets['Sheet1']

#copy within the same sheet
sheet.api.Copy(Before=sheet.api)

#copy to a new workbook
sheet.api.Copy()

#copy a third time at the beginning of the sheets
sheet2 = wb.sheets['sheet1 (2)']
sheet.api.Copy(Before=sheet2.api)

#copy to an existing workbook by putting it in front of a worksheet object
sheet.api.Copy(Before=existingSheet.api)

This does go outside of the native functionality provided by xlwings. Because xlwings is a wrapper around pywin32, the .api() call allows access to those pywin32 functions that are undocumented in xlwings.

Also note that the 'After' command does not work within the worksheet; it will open a new workbook with the sheet copied. This shouldn't pose too big of an issue, as I believe the indexes can be reordered if needed.

Thanks! to `sheet.api.Copy()` I added `xw.Books.active.save('filename)'` to save it — Nabla, Mar 12 '20 at 11:24
The `After` parameter does work, it's just a little different to write. You just have to use `None` before the `After` parameter. For example, `sheet.api.Copy(None, After=existingSheet.api)`. See [here](https://stackoverflow.com/questions/64820928/move-worksheet-within-a-workbook-to-the-end). — mouwsy, Dec 23 '20 at 21:03

score 3 · Answer 2 · answered Mar 11 '22 at 10:55

Note that in recent versions of xlwings you can now always use the simple copy() function instead of api.Copy() - even for copying between different workbooks:

# Create two books and add a value to the first sheet of the first book
first_book = xw.Book()
second_book = xw.Book()
first_book.sheets[0]['A1'].value = 'some value'

# Copy to same Book with the default location and name
first_book.sheets[0].copy()

# Copy to same Book with custom sheet name
first_book.sheets[0].copy(name='copied')

# Copy to second Book requires to use before or after
first_book.sheets[0].copy(after=second_book.sheets[0])

See the documentation here.

score 2 · Answer 3 · answered Jul 18 '20 at 15:07

For anyone struggling on how to copy sheet to an another workbook, here is an example:

import xlwings as xw

wb = xw.Book("spravoc" + "/" + "spravoc.xlsx")  # file FROM you want to copy sheet
sht = wb.sheets["Dictionary"]  #select sheet you want to copy
new_wb = xw.Book("spravoc" + "/" + "spravoc_new.xlsx")     # file where you want TO copy
new_wb.sheets.add("Temp", after=1) # add temp sheet, if you want to copy sheet after some other sheet (after=1 - after first sheet)
print(new_wb.sheets)
# copy needed sheet to the new_wb *before* Temp:
sht.api.Copy(Before=new_wb.sheets['Temp'].api)
# now, remove Temp from new_wb
new_wb.sheets['Temp'].delete()
new_wb.save("spravoc" + "/" + "spravoc_new.xlsx")

Instead of adding a sheet and deleting it later, the code could be simplyfied by using the `After` parameter of `Copy`. For example, `sht.api.Copy(None, After=wb.sheets[-1].api)`. See [here](https://stackoverflow.com/questions/64820928/move-worksheet-within-a-workbook-to-the-end). — mouwsy, Dec 23 '20 at 21:07

score 1 · Answer 4 · answered Nov 18 '21 at 15:08

When copying to a different workbook you can use:

import xlwings as xw

wb_from = xw.Book(r"test1.xlsx")
wb_to = xw.Book(r"test2.xlsx")

ws_from = wb_from.sheets['Sheet1']
ws_to = wb_to.sheets['Sheet1']

# Copy the sheet BEFORE the sheet of another book.
ws_from.api.Copy(Before=ws_to.api)

# Copy the sheet AFTER the sheet of another book.
ws_from.api.Copy(None, After=ws_to.api)

If you want to copy the worksheet within the current workbook you can also use the built-in .copy() since xlwings version 0.22:

# Copy the sheet BEFORE ws_from.
sheet.copy(before=ws_from, name="name_of_copied_sheet")
# Copy the sheet AFTER ws_from.
sheet.copy(after=ws_from, name="name_of_copied_sheet")

score 0 · Answer 5 · answered May 16 '22 at 08:39

I needed to copy multiple sheets so I gave up and used a super ugly hybrid macro approach...

Create a 'utility' sheet called 'my_macros.xlsm' with the following macro in Module1

Sub CopySheetsToAnotherSheet(fromName, toName)
    Workbooks(fromName).Sheets(Array("SheetA", "SheetB")).Copy Before:=Workbooks(toName).Sheets(1)
End Sub

Then make sure the source and target sheet are open and execute the following:-

my_macros = xw.Book('my_macros.xlsm')
a_macro = my_macros.macro('CopySheetsToAnotherSheet')
a_macro('source_sheet.xlsm', 'target_sheet.xlsx')

Copying a worksheet with xlwings and python

5 Answers5

Linked