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