import openpyxl

打开Excel文件

wb = openpyxl.load_workbook(r'C:\Users\Administrator\Desktop\工作簿1.xlsx')

获取第一个工作表

sheet = wb.worksheets[0]

冻结A1窗格

sheet.freeze_panes = 'A2'

获取第二行所有单元格的内容

row2_values = [cell.value for cell in sheet[2]]

获取第一列所有单元格的内容

col1_values = [cell.value for cell in sheet['A']] col1_values = col1_values[1:] # 排除A1单元格

将第一列单元格中所有行拆分到新的工作表中

new_sheets = {} for value in col1_values: if value in new_sheets: new_sheets[value].append([]) else: new_sheets[value] = [[]]

for row in range(2, sheet.max_row+1): cell_value = sheet.cell(row=row, column=1).value for i in range(len(new_sheets[cell_value])): if row == 2: new_sheets[cell_value][i].append(row2_values[i]) new_sheets[cell_value][i].append(sheet.cell(row=row, column=i+2).value)

创建新的工作表

for key, value in new_sheets.items(): sheet_name = str(key) + '(' + str(len(value)) + ')人' new_sheet = wb.create_sheet(sheet_name) new_sheet.freeze_panes = 'A2' for row in range(len(value)): for col in range(len(value[row])): new_sheet.cell(row=row+2, column=col+2).value = value[row][col] for col in range(len(row2_values)): new_sheet.cell(row=2, column=col+2).value = row2_values[col]

保存Excel文件

wb.save(r'C:\Users\Administrator\Desktop\工作簿1.xlsx')