Boost Your Productivity: Automate Excel with Python and xlwings
This guide demonstrates how to use Python's xlwings library to programmatically create, rename, list, print, and split Excel workbooks and worksheets, enabling batch processing and automation that significantly improves efficiency for repetitive spreadsheet tasks.
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()Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
