Python Openpyxl教程：拆分Excel表格，冻结窗格，提取数据

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']]

# 新建字典存储重复内容的行号
duplicates = {}
for i, value in enumerate(col1_values):
    if value in duplicates:
        duplicates[value].append(i+1)
    else:
        duplicates[value] = [i+1]

# 新建工作表存储拆分后的数据
for key, value in duplicates.items():
    if len(value) > 1:
        new_sheet = wb.create_sheet(title=str(key))
        for row in value:
            for col in range(1, sheet.max_column+1):
                new_sheet.cell(row=row, column=col).value = sheet.cell(row=row, column=col).value

# 删除原工作表中重复内容的行
for key, value in duplicates.items():
    if len(value) > 1:
        for row in reversed(value):
            sheet.delete_rows(row)

# 保存Excel文件
wb.save(r'C:\Users\Administrator\Desktop\工作簿1.xlsx')