Python实现Excel拆分与合并

pip install pandas openpyxl

import pandas as pd
def split_excel_by_rows(file_path, rows_per_file, output_prefix):  # 读取Excel文件  df = pd.read_excel(file_path)  # 计算总行数  total_rows = len(df)  # 计算需要拆分的文件数量  num_files = (total_rows // rows_per_file) + (1 if total_rows % rows_per_file != 0 else 0)  for i in range(num_files):      start_row = i * rows_per_file      end_row = (i + 1) * rows_per_file      split_df = df.iloc[start_row:end_row]      # 保存拆分后的文件      output_file = f"{output_prefix}_{i+1}.xlsx"      split_df.to_excel(output_file, index=False)      print(f"文件 {output_file} 已保存")
# 示例使用split_excel_by_rows('data.xlsx', 100, 'split_data')

import pandas as pd
def split_excel_by_sheets(file_path, output_prefix):  # 读取Excel文件  xls = pd.ExcelFile(file_path)  for sheet_name in xls.sheet_names:      df = pd.read_excel(file_path, sheet_name=sheet_name)      output_file = f"{output_prefix}_{sheet_name}.xlsx"      df.to_excel(output_file, index=False)      print(f"工作表 {sheet_name} 已拆分为文件 {output_file}")
# 示例使用split_excel_by_sheets('data_with_sheets.xlsx', 'split_data')

import pandas as pd
def merge_excels_to_sheets(file_list, output_file):  with pd.ExcelWriter(output_file) as writer:      for file in file_list:          df = pd.read_excel(file)          sheet_name = file.split('.')[0] # 使用文件名作为工作表名          df.to_excel(writer, sheet_name=sheet_name, index=False)          print(f"文件 {file} 已合并到 {output_file} 的工作表 {sheet_name}")
# 示例使用files_to_merge = ['data1.xlsx', 'data2.xlsx', 'data3.xlsx']merge_excels_to_sheets(files_to_merge, 'merged_data.xlsx')

import pandas as pd
def merge_excels_to_one_sheet(file_list, output_file):  merged_df = pd.DataFrame()  for file in file_list:      df = pd.read_excel(file)      merged_df = pd.concat([merged_df, df], ignore_index=True)      print(f"文件 {file} 的数据已合并")  merged_df.to_excel(output_file, index=False)  print(f"所有文件已合并到 {output_file}")
# 示例使用files_to_merge = ['data1.xlsx', 'data2.xlsx', 'data3.xlsx']merge_excels_to_one_sheet(files_to_merge, 'merged_data.xlsx')

import pandas as pd
def split_excel_by_rows(file_path, rows_per_file, output_prefix):  df = pd.read_excel(file_path)  total_rows = len(df)  num_files = (total_rows // rows_per_file) + (1 if total_rows % rows_per_file != 0 else 0)  for i in range(num_files):      start_row = i * rows_per_file      end_row = (i + 1) * rows_per_file      split_df = df.iloc[start_row:end_row]      output_file = f"{output_prefix}_{i+1}.xlsx"      split_df.to_excel(output_file, index=False)      print(f"文件 {output_file} 已保存")
def merge_excels_to_one_sheet(file_list, output_file):  merged_df = pd.DataFrame()  for file in file_list:      df = pd.read_excel(file)      merged_df = pd.concat([merged_df, df], ignore_index=True)      print(f"文件 {file} 的数据已合并")  merged_df.to_excel(output_file, index=False)  print(f"所有文件已合并到 {output_file}")
# 示例使用# 拆分大文件split_excel_by_rows('data.xlsx', 100, 'split_data')
# 假设我们已经将文件拆分成了多个文件split_files = ['split_data_1.xlsx', 'split_data_2.xlsx', 'split_data_3.xlsx']
# 合并拆分后的文件merge_excels_to_one_sheet(split_files, 'final_merged_data.xlsx')