Python Openpyxl 拆分 Excel 文件

python/nimport os/nfrom datetime import datetime/nimport openpyxl/n/ndef split_excel_file(file_path):/n # 打开Excel文件/n wb = openpyxl.load_workbook(file_path)/n sheet = wb.worksheets[0] # 获取第一个工作表/n/n # 获取第二行所有单元格的内容/n row2_values = [cell.value for cell in sheet[2]]/n/n # 获取第一列所有单元格的内容/n col1_values = [cell.value for cell in sheet['A']]/n # 排除A1单元格/n col1_values = col1_values[1:]/n/n # 拆分第一列单元格内容到新的工作表/n unique_values = set(col1_values)/n for value in unique_values:/n count = col1_values.count(value)/n new_sheet_name = f'{value} ({count} 人)'/n new_sheet = wb.create_sheet(title=new_sheet_name)/n/n # 在新工作表的第一行插入原工作表A1单元格的内容/n new_sheet.insert_rows(1)/n for col_num, cell in enumerate(sheet[1], 1):/n new_sheet.cell(row=1, column=col_num, value=cell.value)/n/n # 添加第二行单元格内容到新工作表/n new_sheet.append(row2_values)/n/n # 添加第一列单元格内容到新工作表/n for row_num, cell_value in enumerate(col1_values):/n if cell_value == value:/n new_sheet.append([cell.value for cell in sheet[row_num + 2]])/n/n # 删除新工作表中的空行/n new_sheet.delete_rows(1) # 删除第一行空行/n for row in new_sheet.iter_rows():/n if all([cell.value is None for cell in row]):/n new_sheet.delete_rows(row[0].row)/n/n # 合并第一行单元格并居中/n num_cols = new_sheet.max_column/n new_sheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=num_cols)/n new_sheet.cell(row=1, column=1).alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')/n/n # 删除工作表名字为表头（1）人的工作表/n if '表头 (1 人)' in wb.sheetnames:/n wb.remove(wb['表头 (1 人)'])/n/n # 保存为新的excel文件/n new_file_path = os.path.splitext(file_path)[0] + '_拆分结果.xlsx'/n wb.save(new_file_path)/n/n # 获取所有工作表/n sheets = wb.sheetnames/n # 排除第一个工作表/n sheets = sheets[1:]/n/n # 创建拆分结果文件夹/n folder_name = '拆分结果'/n desktop_path = os.path.join(os.path.expanduser('~'), 'Desktop')/n folder_path = os.path.join(desktop_path, folder_name)/n os.makedirs(folder_path, exist_ok=True)/n/n # 遍历每个工作表/n for sheet_name in sheets:/n # 创建新的Excel文件/n new_wb = openpyxl.Workbook()/n new_sheet = new_wb.active/n/n # 复制工作表内容到新的Excel文件/n sheet = wb[sheet_name]/n for row in sheet.iter_rows():/n for cell in row:/n new_sheet[cell.coordinate].value = cell.value/n/n # 保存新的Excel文件/n date_str = datetime.now().strftime('%Y-%m-%d')/n file_name = f'{sheet_name}-{date_str}.xlsx'/n file_path = os.path.join(folder_path, file_name)/n new_wb.save(file_path)/n/n # 关闭新的Excel文件/n new_wb.close()/n/n # 关闭原Excel文件/n wb.close()/n/nif __name__ == '__main__':/n file_path = r'C:/Users/Administrator/Desktop/工作簿1.xlsx'/n split_excel_file(file_path)/n print('拆分完成！！')/n