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.

Answer 1

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")

Answer 2

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.

Answer 3

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')

Answer 4

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.

Answer 5

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")