Python3.8 使用 Openpyxl 库拆分表格，并保留原格式

以下是实现该功能的代码：

import os
from openpyxl import load_workbook, Workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import PatternFill

# 设置工作目录
os.chdir(os.path.expanduser('~/Desktop'))

# 新建文件夹
folder_name = 'split_excel'
if not os.path.exists(folder_name):
    os.mkdir(folder_name)

# 加载工作簿
wb = load_workbook('example.xlsx')

# 获取第一个工作表
ws = wb.worksheets[0]

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

# 获取表头列
header = [cell.value for cell in ws[1]]

# 遍历表格，拆分成多个工作表
for row in ws.iter_rows(min_row=2):
    row_data = [cell.value for cell in row]
    row_format = [cell.fill for cell in row]

    # 根据表头列中的内容生成新的工作表
    for i, col_name in enumerate(header):
        if row_data[i] is not None:
            if not os.path.exists(os.path.join(folder_name, f'{col_name}.xlsx')):
                wb_new = Workbook()
                ws_new = wb_new.active
                ws_new.title = col_name
                ws_new.append(header)
                for cell in ws_new[1]:
                    cell.fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
            else:
                wb_new = load_workbook(os.path.join(folder_name, f'{col_name}.xlsx'))
                ws_new = wb_new[col_name]

            ws_new.append(row_data)
            for j, cell in enumerate(ws_new[-1]):
                cell.fill = row_format[j]

            # 保存新的工作表
            wb_new.save(os.path.join(folder_name, f'{col_name}.xlsx'))

# 写入窗格A1的内容到每个新工作表
for filename in os.listdir(folder_name):
    wb_new = load_workbook(os.path.join(folder_name, filename))
    ws_new = wb_new.active
    ws_new['A1'] = ws['A1'].value
    wb_new.save(os.path.join(folder_name, filename))

注意：需要先安装 openpyxl 库，可以使用以下命令进行安装：

pip install openpyxl