Boost Your Productivity: Automate Excel with Python and xlwings

Batch Create and Save Workbooks

Use xlwings to launch Excel without a default workbook, then create multiple workbooks in a loop and save them to a specified folder.

import xlwings as xw
app = xw.App(visible=True, add_book=False)
for i in range(6):
    wb = app.books.add()
    wb.save(f'e:\\file\\test{i}.xlsx')
    wb.close()
app.quit()

List Files in a Directory

Standard Python os module can enumerate all files and subfolders in a given directory, which helps you quickly review the contents.

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

Batch Rename Worksheets in a Workbook

Open an existing workbook, iterate over its sheets, and replace a specific substring in each sheet name before saving a new copy.

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

Batch Print Worksheets

Open each workbook in a folder, skip temporary files, and send the workbook to the default printer using the COM PrintOut method.

import os, 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
    wb = app.books.open(os.path.join(file_path, i))
    wb.api.PrintOut()
app.quit()

Split a Workbook into Separate Files

Iterate through each sheet of a source workbook, copy it into a new workbook, and save the new file using the original sheet name.

import xlwings as xw
source = 'e:\\table\\产品销售表.xlsx'
app = xw.App(visible=False, add_book=False)
wb = app.books.open(source)
for sheet in wb.sheets:
    new_wb = app.books.add()
    target = new_wb.sheets[0]
    sheet.api.Copy(Before=target.api)
    new_wb.save(f'{sheet.name}.xlsx')
app.quit()