使用Python Openpyxl 库拆分 Excel 表格，并按列内容生成多个独立文件

以下代码使用 Python 3.8 版本的 Openpyxl 库，实现了将 Excel 表格根据指定列的内容进行拆分，并生成多个独立的 Excel 文件的功能。

import os
from openpyxl import load_workbook, Workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import NamedStyle, Font

# 定义表头列名称
HEADER_COLUMN_NAME = '姓名'

# 加载原始excel文件
file_path = 'path/to/your/excel/file.xlsx'
workbook = load_workbook(file_path)

# 获取第一个工作表
worksheet = workbook.worksheets[0]

# 冻结窗格A1行
worksheet.freeze_panes = 'A2'

# 获取表头列的位置
header_column_index = None
for col in worksheet.iter_cols(min_row=1, max_row=1):
    for cell in col:
        if cell.value == HEADER_COLUMN_NAME:
            header_column_index = cell.column
            break
    if header_column_index is not None:
        break

# 如果找不到表头列，则退出程序
if header_column_index is None:
    print(f'Cannot find column 'HEADER_COLUMN_NAME' in the first worksheet.')
    exit()

# 创建新的工作表和命名样式
new_worksheets = {}
for row in worksheet.iter_rows(min_row=2):
    header_value = row[header_column_index - 1].value
    if header_value not in new_worksheets:
        # 创建新的工作表
        new_worksheet = Workbook().active
        new_worksheet.title = header_value
        new_worksheet['A1'] = worksheet['A1'].value
        new_worksheet['A1'].style = 'Headline 1'
        new_worksheet.freeze_panes = 'A2'
        new_worksheets[header_value] = new_worksheet

        # 复制表头行
        for col in worksheet.iter_cols(min_row=1, max_row=1):
            for cell in col:
                new_cell = new_worksheet.cell(row=2, column=cell.column, value=cell.value)
                new_cell.style = cell.style

# 复制数据行
for row in worksheet.iter_rows(min_row=2):
    header_value = row[header_column_index - 1].value
    new_worksheet = new_worksheets.get(header_value)
    if new_worksheet is not None:
        new_row_index = new_worksheet.max_row + 1
        for col in worksheet.iter_cols(min_row=row[0].row, max_row=row[0].row):
            for cell in col:
                new_cell = new_worksheet.cell(row=new_row_index, column=cell.column, value=cell.value)
                new_cell.style = cell.style

# 创建文件夹并保存新的工作表为单独的excel文件
desktop_path = os.path.join(os.path.expanduser('~'), 'Desktop')
folder_path = os.path.join(desktop_path, 'Split Excel')
if not os.path.exists(folder_path):
    os.mkdir(folder_path)

for header_value, new_worksheet in new_worksheets.items():
    file_name = f'{header_value}.xlsx'
    file_path = os.path.join(folder_path, file_name)
    new_worksheet.save(file_path)

print('Done.')

这段代码会在桌面上创建一个名为 “Split Excel” 的文件夹，其中包含拆分后的单独excel文件。请注意，如果表头列中存在重复的值，则会生成多个工作表和对应的excel文件。