将 xlsx 文件中的所有 worksheet 单独保存一份新的 xlsx 文件

split_worksheet_as_a_new_xlsx.py

import os.path
import shutil

import openpyxl

original_filepath = 'assets/a.xlsx'
original_folder = os.path.dirname(original_filepath)
original_filename = os.path.splitext(os.path.basename(original_filepath))[0]
original_workbook = openpyxl.load_workbook(original_filepath)

dst_folder = os.path.join(original_folder, original_filename)
os.makedirs(dst_folder, exist_ok=True)

for current_sheet_name in original_workbook.sheetnames:
    # 1 复制一份 xlsx 文件
    dst_filepath = os.path.join(dst_folder, f'{current_sheet_name}.xlsx')
    shutil.copy(original_filepath, dst_filepath)

    # 2 删除其它 worksheet
    dst_workbook = openpyxl.load_workbook(dst_filepath)
    for dst_worksheet in dst_workbook.worksheets:
        if dst_worksheet.title != current_sheet_name:
            dst_workbook.remove(dst_worksheet)

    # 3 保存单 worksheet 文件
    dst_workbook.save(dst_filepath)