Python pandas实现excel工作表合并功能详解

import os,pandas as pd,re#1.获取文件夹下要合并的文件名dirpath = '文件夹地址'#工作表3特殊处理 需要开始下标和结束下标begin = 231end = 238excel_names = os.listdir(dirpath)#2.获取文件内容sheet_1_merge = []sheet_2_merge = []sheet_3_merge = pd.DataFrame([0,0,0,0,0,0,0])for excel_name in excel_names:#绝对路径excel_path = dirpath+excel_name#dropna(axis=0, how='all')行全部为空得删除#获取工作表1内容sheet_1=pd.read_excel(excel_path, sheet_name=1, header=None)[2:][range(9)].dropna(axis=0, how='all')sheet_1_merge.append(sheet_1)

sheet_2=pd.read_excel(excel_path, sheet_name=2, header=None)[2:][range(1,7)].dropna(axis=0, how='all')sheet_2_merge.append(sheet_2)# 获取工作表3内容sheet_3 = list(pd.read_excel(excel_path,sheet_name=3,header=None)[begin:end][1])excel_name = re.findall('/d-(.*).xlsx', excel_name)[0]sheet_3_merge.insert(1,excel_name,sheet_3)print(sheet_1,'/n',sheet_2,'/n',sheet_3)

#保存合并内容#axis=0,ignore_index=True合并行 忽略原来的下标sheet_1_merge = pd.DataFrame(pd.concat(sheet_1_merge,axis=0,ignore_index=True))sheet_2_merge = pd.DataFrame(pd.concat(sheet_2_merge,axis=0,ignore_index=True))#columns主要是为了按照已有得排序，方便整理sheet_3_merge = pd.DataFrame(sheet_3_merge,columns=['李1','李2,'李3','彭4','杨5','夏6',...,'杜34','叶35'])writer = pd.ExcelWriter('C:/Users/zhuozhe/Desktop/merge.xlsx')sheet_1_merge.to_excel(excel_writer=writer,sheet_name='merge_1')sheet_2_merge.to_excel(excel_writer=writer,sheet_name='merge_2')sheet_3_merge.to_excel(excel_writer=writer,sheet_name='merge_3')writer.save()writer.close()