Python Excel自动化：按列拆分表格并保存

import pandas as pd
import os
from datetime import datetime

# 读取Excel文件
df = pd.read_excel(r'C:\Users\Administrator\Desktop\工作簿1.xlsx')

# 拆分第一列单元格内容到新的DataFrame
unique_values = df.iloc[:, 0].unique()
for value in unique_values:
    count = (df.iloc[:, 0] == value).sum()
    new_df = df[df.iloc[:, 0] == value]

    # 保存拆分结果到新的Excel文件
    folder_name = '拆分结果'
    desktop_path = os.path.join(os.path.expanduser('~'), 'Desktop')
    folder_path = os.path.join(desktop_path, folder_name)
    if not os.path.exists(folder_path):
        os.mkdir(folder_path)

    date_str = datetime.now().strftime('%Y-%m-%d')
    file_name = f'{value} ({count} 人)-{date_str}.xlsx'
    file_path = os.path.join(folder_path, file_name)
    new_df.to_excel(file_path, index=False)

# 删除工作表名字为'表头（1）人'的行
df = df.loc[df.iloc[:, 0] != '表头 (1 人)']

# 保存拆分结果到新的Excel文件
folder_name = '拆分表格结果'
desktop_path = os.path.join(os.path.expanduser('~'), 'Desktop')
folder_path = os.path.join(desktop_path, folder_name)
if not os.path.exists(folder_path):
    os.mkdir(folder_path)

file_path = os.path.join(folder_path, '拆分表格结果.xlsx')
df.to_excel(file_path, index=False)