其实xlwings在早期并没有提供复制工作表的相应函数,要实现复制工作表功能,需要调用excel的vba里面的相应方法worksheet.Copy。
在xlwings0.22版本以后,xlwings开始原生支持copy方法进行工作表复制,先说版本在0.22以下的旧办法:
首先看excel vba的帮助文档:
应用于Chart、Charts、Sheets、Worksheet 和Worksheets 对象的 Copy 方法。
将指定工作表复制到工作簿的另一位置。
expression.Copy(Before,After)
expression 必需。该表达式返回上面的对象之一。
BeforeVariant 类型,可选。指定某工作表,复制的工作表将置于此工作表之前。如果已经指定了After,则不能指定Before。
AfterVariant 类型,可选。指定某工作表,复制的工作表将置于此工作表之后。如果已经指定了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 tosomehow 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, seeCustom Add-ins (GH1488).
- [Enhancement]
xw.view
now formats the pandas DataFrames as Excel table and with the newxw.load
function, you can easily load a DataFrame from your active workbook into a Jupyter notebook. SeeJupyter 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 newmysheet.render_template()
method, see alsoxlwings Reports (GH1478).
https://docs.xlwings.org/en/stable/api.html#xlwings.Sheet.copy
Sheet
classxlwings.
Sheet
(sheet=None,impl=None)
A sheet object is a member of thesheets
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: |
|
---|---|
Returns: | Sheet object – The copied sheet |
Return type: |
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])