Python Excel 拆分工具 - 按第一列内容拆分工作表

import os import openpyxl import threading import platform import tkinter as tk from tkinter import filedialog, messagebox

def split_excel_file(file_path): '打开Excel文件' wb = openpyxl.load_workbook(file_path) sheet = wb.worksheets[0] # 获取第一个工作表

'获取第二行所有单元格的内容'
row2_values = [cell.value for cell in sheet[2]]

'获取第一列所有单元格的内容'
col1_values = [cell.value for cell in sheet['A']]
'排除A1单元格'
col1_values = col1_values[1:]

'拆分第一列单元格内容到新的工作表'
unique_values = set(col1_values)

'创建线程列表'
threads = []

for value in unique_values:
    count = col1_values.count(value)
    new_sheet_name = f'{value} ({count} 人)'
    new_sheet = wb.create_sheet(title=new_sheet_name)

    '在新工作表的第一行插入原工作表A1单元格的内容'
    new_sheet.insert_rows(1)
    for col_num, cell in enumerate(sheet[1], 1):
        new_sheet.cell(row=1, column=col_num, value=cell.value)

    '添加第二行单元格内容到新工作表'
    new_sheet.append(row2_values)

    '添加第一列单元格内容到新工作表'
    rows = []
    for row_num, cell_value in enumerate(col1_values):
        if cell_value == value:
            rows.append([cell.value for cell in sheet[row_num + 2]])

    '创建线程'
    t = threading.Thread(target=append_rows, args=(new_sheet, rows))
    threads.append(t)

    '合并第一行单元格并居中'
    num_cols = new_sheet.max_column
    new_sheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=num_cols)
    new_sheet.cell(row=1, column=1).alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')

'启动所有线程'
for t in threads:
    t.start()

'等待所有线程结束'
for t in threads:
    t.join()

'删除工作表名字为表头（1）人的工作表'
if '表头 (1 人)' in wb.sheetnames:
    wb.remove(wb['表头 (1 人)'])

'删除新工作表中的空行'
for sheet in wb.worksheets:
    rows = sheet.max_row
    cols = sheet.max_column
    for row in range(rows, 0, -1):
        empty = True
        for col in range(1, cols+1):
            if sheet.cell(row=row, column=col).value is not None:
                empty = False
                break
        if empty:
            sheet.delete_rows(row)

'保存为新的excel文件'
new_file_path = os.path.splitext(file_path)[0] + '_拆分结果.xlsx'
wb.save(new_file_path)

'获取所有工作表'
sheets = wb.sheetnames
'排除第一个工作表'
sheets = sheets[1:]

'创建拆分结果文件夹'
if platform.system() == 'Windows':
    if platform.release() == '7':
        desktop_path = os.path.join(os.path.expanduser('~'), '桌面')
    else:
        desktop_path = os.path.join(os.path.expanduser('~'), 'Desktop')
else:
    desktop_path = os.path.join(os.path.expanduser('~'), '桌面')
folder_name = os.path.splitext(file_path)[0] + '_拆分结果'
folder_path = os.path.join(desktop_path, folder_name)
if not os.path.exists(folder_path):
    os.mkdir(folder_path)

'遍历每个工作表'
for sheet_name in sheets:
    '创建新的Excel文件'
    new_wb = openpyxl.Workbook()
    new_sheet = new_wb.active

    '复制工作表内容到新的Excel文件'
    sheet = wb[sheet_name]
    for row in sheet.iter_rows():
        for cell in row:
            new_sheet[cell.coordinate].value = cell.value

    '合并第一行单元格并居中'
    num_cols = new_sheet.max_column
    new_sheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=num_cols)
    new_sheet.cell(row=1, column=1).alignment = openpyxl.styles.Alignment(horizontal='center',
                                                                                  vertical='center')
    '保存新的Excel文件'
    file_name = f'{sheet_name}.xlsx'
    file_path = os.path.join(folder_path, file_name)
    new_wb.save(file_path)

    '关闭新的Excel文件'
    new_wb.close()

'关闭原Excel文件'
wb.close()

'提示拆分完成'
messagebox.showinfo(title='提示', message='Excel文件拆分完成！')

def main(): '创建GUI界面' root = tk.Tk() root.withdraw()

'打开文件选择对话框'
file_path = filedialog.askopenfilename(title='选择需要拆分的Excel文件', filetypes=[('Excel 文件', '*.xlsx')])

if file_path:
    '提示开始拆分'
    messagebox.showinfo(title='提示', message='Excel文件拆分开始，请稍等...')

    '创建新线程并启动'
    t = threading.Thread(target=split_excel_file, args=(file_path,))
    t.start()

    '返回主函数，程序在后台运行'
    return

if name == 'main': main()