Automating Excel with Python xlwings: Batch Create, Rename, Print, and Split Workbooks

Python is a powerful and popular language, and many users seek ways to automate Excel to boost productivity. This article provides step‑by‑step examples using the xlwings library to perform common Excel operations.

Batch create and save workbooks

import xlwings as xw  # import library
app = xw.App(visible=True, add_book=False)  # start Excel without a new workbook
for i in range(6):
    workbook = app.books.add()  # create a new workbook
    workbook.save(f'e:\\file\\test{i}.xlsx')  # save the workbook
    workbook.close()  # close the workbook
app.quit()  # quit Excel

The code above creates multiple workbooks, saves them to a specified folder, and then closes each one.

List all files in a directory

import os
file_path = 'table'
file_list = os.listdir(file_path)
for i in file_list:
    print(i)

This snippet prints the names of all files and sub‑folders under the given directory, helping you quickly review folder contents.

Batch rename worksheets in a workbook

import xlwings as xw
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('e:\\table\\统计表.xlsx')
worksheets = workbook.sheets  # get all sheets
for i in range(len(worksheets)):
    worksheets[i].name = worksheets[i].name.replace('销售', '')  # rename sheet
workbook.save('e:\\table\\统计表1.xlsx')  # save as new file
app.quit()

This code opens an existing workbook, removes the word "销售" from each sheet name, and saves the modified workbook under a new filename.

Batch print worksheets

import xlwings as xw
file_path = 'e:\\table\\公司'
file_list = os.listdir(file_path)
app = xw.App(visible=False, add_book=False)
for i in file_list:
    if i.startswith('~$'):
        continue  # skip temporary files
    file_paths = os.path.join(file_path, i)
    workbook = app.books.open(file_paths)  # open workbook
    workbook.api.PrintOut()  # send to printer
app.quit()

The script opens each workbook in a specified folder and sends it to the default printer, ignoring temporary Excel files.

Split a workbook into separate files per worksheet

import xlwings as xw
workbook_name = 'e:\\table\\产品销售表.xlsx'  # source workbook
app = xw.App(visible=False, add_book=False)
workbook = app.books.open(workbook_name)
for sheet in workbook.sheets:
    new_wb = app.books.add()  # create new workbook
    target_sheet = new_wb.sheets[0]
    sheet.api.Copy(Before=target_sheet.api)  # copy sheet to new workbook
    new_wb.save(f"{sheet.name}.xlsx")  # save with sheet name
app.quit()

This example iterates over all worksheets in the source workbook, copies each one into a newly created workbook, and saves each as an individual file named after the original worksheet.

All code snippets are provided unchanged to allow direct copying and execution.