需求

将Excel根据列进行拆分，且保留原有的表格格式
如将500名学生根据班级拆分成不同的Sheet或Excel表格

image.png

结果如：

image.png

image.png

原理

使用微软的COM，可以方便的进行Excel拆分

拿走即用代码

测试文件链接链接：
https://pan.baidu.com/s/1NbmoVu5ZRIG-UHMj8q0Gmw?pwd=92t5
提取码：92t5

import win32com.client.gencache as gc
import os

def split_toExcel_by_column(file_path, column_index, title_row=1, flag=False):
    """
    file_path (str): Excel文件的路径。
    column_index (int): 拆分的列索引。
    title_row (int, optional): 标题行的行号,默认为1。若标题行多行,则为最后一行的行号。
    flag (bool, optional): 是否显示Excel窗体,默认为False。
    """
    excel_app = gc.EnsureDispatch("Excel.Application")
    excel_app.Visible = flag
    file_path = os.path.abspath(file_path)
    workbook = excel_app.Workbooks.Open(file_path)
    sheet = workbook.ActiveSheet
    data_range = sheet.UsedRange
    rows_count = data_range.Rows.Count
    columns_count = data_range.Columns.Count
    data_range = sheet.Range(sheet.Cells(title_row+1, 1), sheet.Cells(rows_count, columns_count))
    column_data = data_range.Columns(column_index).Value
    column_data_unique = set(column_data)
    data_range = sheet.Range(sheet.Cells(title_row, 1), sheet.Cells(rows_count, columns_count))
    for value in column_data_unique:
        data_range.AutoFilter(Field=column_index, Criteria1=value)
        new_workbook = excel_app.Workbooks.Add()
        new_sheet = new_workbook.ActiveSheet
        new_sheet.Name = value[0]
        title_range = sheet.Range(sheet.Cells(1, 1), sheet.Cells(title_row, columns_count))
        title_range.Copy()
        new_sheet.Range("A1").PasteSpecial()
        data_range = sheet.Range(sheet.Cells(title_row+1, 1), sheet.Cells(rows_count, columns_count))
        data_range.Copy()
        site = f"A{title_row+1}"
        new_sheet.Range(site).PasteSpecial()
        for i in range(1, columns_count+1):
            new_sheet.Columns(i).ColumnWidth = sheet.Columns(i).ColumnWidth
        new_workbook.SaveAs(os.path.abspath(value[0] + ".xlsx"))
        new_workbook.Close()
    workbook.Close(SaveChanges=False)
    excel_app.Quit()

def split_toSheet_by_column(file_path, column_index, title_row=1, flag=False):
    """
    file_path (str): Excel文件的路径。
    column_index (int): 拆分的列索引。
    title_row (int, optional): 标题行的行号,默认为1。若标题行多行,则为最后一行的行号。
    flag (bool, optional): 是否显示Excel窗体,默认为False。
    """
    # 获取 Excel 应用程序对象
    excel_app = gc.EnsureDispatch("Excel.Application")
    excel_app.Visible = flag
    file_path = os.path.abspath(file_path)
    workbook = excel_app.Workbooks.Open(file_path)
    sheet = workbook.ActiveSheet
    data_range = sheet.UsedRange
    rows_count = data_range.Rows.Count
    columns_count = data_range.Columns.Count
    data_range = sheet.Range(sheet.Cells(title_row+1, 1), sheet.Cells(rows_count, columns_count))
    column_data = data_range.Columns(column_index).Value
    column_data_unique = set(column_data)
    column_data_unique = sorted(column_data_unique)
    data_range = sheet.Range(sheet.Cells(title_row, 1), sheet.Cells(rows_count, columns_count))
    new_workbook = excel_app.Workbooks.Add()
    for value in column_data_unique:
        data_range.AutoFilter(Field=column_index, Criteria1=value)
        new_sheet = new_workbook.Worksheets.Add()
        new_sheet.Name = value[0]
        title_range = sheet.Range(sheet.Cells(1, 1), sheet.Cells(title_row, columns_count))
        title_range.Copy()
        new_sheet.Range("A1").PasteSpecial()
        data_range = sheet.Range(sheet.Cells(title_row+1, 1), sheet.Cells(rows_count, columns_count))
        data_range.Copy()
        site = f"A{title_row+1}"
        new_sheet.Range(site).PasteSpecial()
        for i in range(1, columns_count+1):
            new_sheet.Columns(i).ColumnWidth = sheet.Columns(i).ColumnWidth
    new_workbook.Worksheets(new_workbook.Worksheets.Count).Delete()
    if os.path.exists(os.path.abspath("拆分.xlsx")):
        print("请先手动删除“拆分.xlsx”文件")
    else:
        new_workbook.SaveAs(os.path.abspath("拆分.xlsx"))
    workbook.Close(SaveChanges=False)
    excel_app.Quit()



# 示例调用
split_toExcel_by_column(r"测试表格.xlsx", 3, 4, False)
split_toSheet_by_column(r"测试表格.xlsx", 3, 4, False)