Python batch processing workbook and worksheet implementation examples

Batch create and save workbooks

coding

import xlwings as xw
# Start Excel without creating a new workbook
app = (visible=True,add_book=False)

for i in range(5):
 #New Workbook
    workbook = ()
    #SaveWorkbook
    (f'test{i}.xlsx')
    # Close the workbook
    ()

Batch open open workbooks in a folder

import xlwings as xw
import os
# Give the path to the folder where the workbook is located
path_file = r'E:/python1/python_module'
# List all subfiles or subfolders in the folder
file_list = (path_file)
# Launch Excel
app = (visible=True,add_book=False)

for i in file_list:
    # Determine if a file is an Excel file
    if (i)[1] =='.xlsx':
        #Open
        (i)

Batch rename all sheets of a workbook

import xlwings as xw

# Launch Excel
app = (visible=True,add_book=False)
# Open the workbook
workbook = ('')
# Get all worksheets of a workbook
worhsheets = 

for i in range(len(worhsheets)):
    # Rename worksheets
    worhsheets[i].name = worhsheets[i].('Sales','')
# Save as renamed workbook
('')
# Exit the Excel program
()

Batch rename multiple workbooks

This is a prerequisite, however, the name of the workbook to be renamed must be
Must be regular, such as Table 1, Table 2, Table 3; or contain the same keywords.

import xlwings as xw
import os
# Give the path to the folder where the workbook is located
path_file = r'E:/python1/python_module'
# List all subfiles or subfolders in the folder
file_list = (path_file)
old_book_name = 'Sales table'
new_book_name = 'Segment sales table'
# Launch Excel
app = (visible=True,add_book=False)

for i in file_list:
    if ('~$'):
        continue
    # Perform a find and replace to generate a new workbook name
    new_file = (old_book_name,new_book_name)
    # Construct the full path of the workbook that needs to be renamed
    old_path_filr = (path_file,i)
    # Build the full path to the renamed workbook
    new_path_file = (path_file,new_file)
    # Rename
    (old_path_filr,new_path_file)

if ('~$'):
continue

Because Excel will be used in the process of generating a number of file names to "~ $" at the beginning of the temporary files, if there are these files on the skip.

Batch rename worksheets with the same name in multiple workbooks

move

Print out the names of all the sub-files in the folder
After splicing with the folder path to form a complete filename, open the
Iterate through all the worksheets in the file and change them if they have the same name
Save Worksheet Catalog

Code:

import xlwings as xw
import os
# Give the path to the folder where the workbook is located
path_file = r'E:/python1/python_module'
# List all subfiles or subfolders in the folder
file_list = (path_file)
old_sheet = 'sheet1'
new_sheet = 'Employee Information'
app = (visible=True,add_book= False)
 # Traverse the workbook
for i in path_file:
    if ('~$'):
        continue
    # Splice out the full path
    old_path_file = (path_file,i)
    # Open the workbook
    workbook = (old_path_file)
    # Traverse the worksheet
    for j in :
        if  == old_sheet:
             = new_sheet
    # Save the workbook
    ()
()

Batch copy all worksheets from one workbook to other workbooks

Steps:

Get all sub-files of the target (copied-to) folder
Open the source file (that was copied) and get all its worksheet information.
Iterate through all the subfiles and open them if they are Excel files
Add a new worksheet to the target workbook
Write data from the source worksheet to the new worksheet

coding

import xlwings as xw
import os
# Give the path to the folder where the workbook is located
path_file = r'E:/python1/python_module'
# List all subfiles or subfolders in the folder
file_list = (path_file)
app = (visible=True,add_book= False)
workbook = ('Source workbook path')
worksheet = 
 # Subdocuments
for i in path_file:
    if (i)[1] =='.xlsx':
        # Open the workbook
        workbooks = (path_file+'/'+i)
        # Traverse the worksheet
        for j in worksheet:
            # Read the information in the worksheet
            contents = ('A1').expand('table').value
            # Read the name of the worksheet
            name = 
            # Add worksheet with same name
            (name = name,after = len())
            # Write data
            [name].range('A1').value = contents
        # Save the workbook
        ()
()

.expand() is a function in the xlwings module used to expand the selection range. The syntax format is as follows

expand(mode) defaults to 'table', which means expand to the whole table. It can also be 'down' (below) or 'right' (right)

Split a worksheet into multiple workbooks by conditions

import  os
import xlwings as xw

filr_path = 'e:\\\table\\\\product statistics table.xlsx'
sheet_name = 'Statistical tables'

app = (visible = True ,add_book= False)
# Open the workbook
workbooh = (filr_path)
# Get the specified worksheet
worksheet = [sheet_name]
# Read all the information in the worksheet
value = ('A2').expand('table').value
# Create an empty dictionary for categorizing data by product name
data = dict()
# Iterate over worksheet data by rows
for i in range(len(value)):
    # Get the data in the first space of the current line
    product_name = value[i][1]
    # If the product is not available
    if product_name not in data:
        # Create an empty list corresponding to the current row name
        data[product_name] = []
    # Append the current data to the current list
    data[product_name].append(value[i])

for key,value in ():
    # New target workbook created
    new_workbook = ()
    #New Worksheet
    new_sheet = new_workbook.(key)
    # Copy the column headings of the worksheet to be split to the newly created worksheet
    new_sheet['A1'].value = worksheet['A1:H1'].value
    # Replicate the data
    new_sheet['A2'].value = value
    new_workbook.save('{}.xlsx'.format(key))

()

This article on Python batch processing of workbooks and worksheets to achieve the example of the article is introduced to this, more related Python batch processing of workbooks and worksheets content, please search for my previous posts or continue to browse the following related articles I hope that you will support me in the future more!