List of articles

• Batch copy all worksheets of a workbook to other workbooks
• Batch copy the data of a worksheet to the specified worksheet of another workbook

Batch copy all worksheets of a workbook to other workbooks

import os # Import os modular 
import xlwings as xw # Import xlwings modular 
app = xw.App(visible=False,add_book=False)
file_path = 'e:/table/test' # Give the folder path where the target workbook is located 
file_list = os.listdir(file_path) # List the names of all files and subfolders under the folder 
workbook = app.books.open('e:/table/text1.xlsx') # Open source workbook 
worksheet = workbook.sheets # Get all worksheets in the source Workbook 
for i in file_list:
if os.path.splitext(i)[1] == '.xlsx':
workbooks = app.books.open(file_path+'\\'+i) # If it is a workbook, open it 
for j in worksheet:
contents = j.range('A1').expand('table').value # Read the worksheet data to be copied from the source workbook 
name = j.name # Get the name of the worksheet in the source workbook 
workbooks.sheets.add(name = name,after = len(workbooks,sheets)) # Add a new worksheet with the same name in the target workbook 
workbooks.sheet[name].range('A1').value = contents # Write the worksheet data read from the source workbook to the new worksheet 
workbooks.save() # Save the target workbook 
app.quit()

The extension of knowledge ：

The first 12 In line code expand() yes xlwings Functions in modules , Used to expand the selection range , Its syntax format and common parameter meanings are as follows .

Batch copy the data of a worksheet to the specified worksheet of another workbook

import os # Import os modular 
import xlwings as xw # Import xlwings modular 
app = xw.App(visible=False,add_book=False)
file_path = 'e:/table/test' # Give the folder path where the target workbook is located 
file_list = os.listdir(file_path) # List the names of all files and subfolders under the folder 
workbook = app.books.open('e:/table/textAdd.xlsx')
worksheet = workbook.sheets['sheetadd'] # Select sheet “sheetadd”
value = worksheet.range('A1').expend(table) # Read sheet “sheetadd” All data in 
start_cell = (2,1) # Give the starting cell of the cell range to copy data 
end_cell = (value.shape[0],value.shape[1]) # Give the end cell of the cell range to copy data 
cell_area = worksheet.range(start_cell,end_cell).value # Select the data to be copied according to the previously set cell range 
for i in file_list:
if os.path.splitext(i)[1] == '.xlsx':
try:
workbooks = xw.Book(file_path+'\\'+i)
sheet = workbooks.sheets['sheetnew'] # Select the worksheet to paste data “sheetnew”
scope = sheet.range('A1').expand() # Select the cell range where you want to paste data 
sheet.range(scope.shape[0]+1,1).value = cell_area # Paste data 
workbooks.save() # Save the target workbook 
finally:
workbooks.close() # Close the target workbook 
workbook.close() # Close source Workbook 
app.quit()