python xlwings 复制工作表

其实xlwings在早期并没有提供复制工作表的相应函数，要实现复制工作表功能，需要调用excel的vba里面的相应方法worksheet.Copy。

在xlwings0.22版本以后，xlwings开始原生支持copy方法进行工作表复制，先说版本在0.22以下的旧办法：

首先看excel vba的帮助文档：

将指定工作表复制到工作簿的另一位置。

expression.Copy(Before, After)

*expression * 必需。该表达式返回上面的对象之一。

Before Variant 类型，可选。指定某工作表，复制的工作表将置于此工作表之前。如果已经指定了 After，则不能指定 Before。

After Variant 类型，可选。指定某工作表，复制的工作表将置于此工作表之后。如果已经指定了 Before，则不能指定 After。

说明

如果既未指定 Before 参数也未指定 After 参数，则 Microsoft Excel 将新建一个工作簿，其中将包含复制的工作表。

本示例复制工作表 Sheet1，并将其放置在工作表 Sheet3 之后。

Worksheets("Sheet1").**Copy** After:=Worksheets("Sheet3")

那么在清楚了原理后，就可以在python里面码代码了

import xlwings as xw

wb = xw.Book('test.xls')
sheet = wb.sheets['Sheet1']

#将sheet1工作表复制到该工作簿的同一工作表的前面
sheet.api.Copy(Before=sheet.api)

#将sheet1工作表复制到新建工作簿中
sheet.api.Copy()

#将sheet1工作表复制到该工作簿的最后一个工作表后面
sheet2 = wb.sheets[-1]
sheet.api.Copy(After=sheet2.api)

#将sheet1工作表复制到另外一个工作簿dst.xls中最后个工作表之后
wb_dst=xw.Book("dst.xls")
ws_dst=wb_dst.sheets[-1]
sheet.api.Copy(After=ws_dst.api)

记住最后一个案例：

import xlwings as xw

wb = xw.Book('test.xls')
sheet = wb.sheets['Sheet1']

#将sheet1工作表复制到另外一个工作簿dst.xls中最后个工作表之后
wb_dst=xw.Book("dst.xls")
ws_dst=wb_dst.sheets[-1]
sheet.api.Copy(After=ws_dst.api)

最后说第二个方法：xlwings0.22以上版本的原生copy方法，贴上官方链接

https://docs.xlwings.org/en/stable/whatsnew.html#v0-22-0-jan-29-2021

v0.22.0 (Jan 29, 2021)

• [Feature] While it’s always been possible to somehow create your own xlwings-based add-ins, this release adds a toolchain to make it a lot easier to create your own white-labeled add-in, see Custom Add-ins (GH1488).
• [Enhancement] xw.view now formats the pandas DataFrames as Excel table and with the new xw.load function, you can easily load a DataFrame from your active workbook into a Jupyter notebook. See Jupyter Notebooks: Interact with Excel for a full tutorial (GH1487).
• [Feature] New method mysheet.copy() (GH123).
• PRO [Feature]: in addition to xw.create_report(), you can now also work within a workbook by using the new mysheet.render_template() method, see also xlwings Reports (GH1478).

https://docs.xlwings.org/en/stable/api.html#xlwings.Sheet.copy

Sheet

class

xlwings.

Sheet

(sheet=None, impl=None)

A sheet object is a member of the sheets collection:

copy

(before=None, after=None, name=None)

Copy a sheet to the current or a new Book. By default, it places the copied sheet after all existing sheets in the current Book. Returns the copied sheet.

New in version 0.22.0.
Parameters:

• before (sheet object*, *default None) – The sheet object before which you want to place the sheet
• after (sheet object*, *default None) – The sheet object after which you want to place the sheet, by default it is placed after all existing sheets
• name (str*, *default None) – The sheet name of the copy
  Returns:

Sheet object – The copied sheet
Return type:
Sheet

Examples

# 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])